自贡网站开发公司,设计头条官网,淘客的手机网站,24小时24元网站建设第一章 多表关系实战 1.1 实战1#xff1a;省和市 方案1#xff1a;多张表#xff0c;一对多 方案2#xff1a;一张表#xff0c;自关联一对多 id1 name‘北京’ p_id null; id2 name‘昌平’ p_id1 id3 name‘大兴’ p_id1 id3 name‘上海’ p_idnull id4 name‘浦东’…第一章 多表关系实战 1.1 实战1省和市 方案1多张表一对多 方案2一张表自关联一对多 id1 name‘北京’ p_id null; id2 name‘昌平’ p_id1 id3 name‘大兴’ p_id1 id3 name‘上海’ p_idnull id4 name‘浦东’ p_id3 1.2 实战2用户和角色 多对多关系 第二章 多表查询之子查询 提供表结构如下
-- 部门表
create table department (id int primary key auto_increment,name varchar(50)
);
-- 员工表
create table worker (id int primary key auto_increment,name varchar(50), -- 名字sex char(2), -- 性别money double, -- 工资inWork_date date, -- 入职时间depart_id int, -- 部门foreign key(depart_id) references department(id)
);2.1 初始化数据
-- 插入部门数据
insert into department(name)
values(技术研发), (市场营销), (行政财务);
-- 插入员工数据
insert into worker(name, sex, money, inWork_date,depart_id)
values(cuihua, 女, 10000, 2019-5-5, 1);
insert into worker(name, sex, money, inWork_date,depart_id)
values(guoqing, 男, 20000, 2018-5-5, 2);
insert into worker(name, sex, money, inWork_date,depart_id)
values(qiangge, 男, 30000, 2018-7-5, 3);
insert into worker(name, sex, money, inWork_date,depart_id)
values(huahua, 女, 10000, 2019-5-5, 1);2.2 什么是子查询 一个查询的结果作为另一个查询的条件。 有查询的嵌套内部的查询就是子查询。 子查询需要使用小括号包含起来。
-- 查询技术研发部门有哪些员工
-- 1. 先查询所有的员工
select * from worker where depart_id 1;
-- 2. 查询部门
select id from department where name 技术研发;
-- 使用子查询的方式来统一查询对应的数据
select * from worker where depart_id (select id from department where name技术研发);2.3 常见三种做法 1单行单列 也就是结果只有一个
select 指定的字段 from 表 where 字段 (子查询)
-- 谁的工资最高
-- 1. 先把最高工资找出来
select MAX(money) from worker;
-- 2. 再去员工表中把对应的员工信息查出来
select * from worker where money (select MAX(money) from worker);
-- 谁的工资少于平均工资
-- 1. 先把平均工资算出来
select AVG(money) from worker;
-- 2. 再去员工表中把对应的员工信息查出来
select * from worker where money (select AVG(money) from worker);2多行单列 当我们在处理多行单列的时候有可能会出现多个值这时候可以类似数组或集合一样处理在 SQL中使用 in 关键字即可。
-- 查询那些工资大于 12000 的人都来自哪些部门
-- 1. 先查大于 5000 的员工对应的部门 id
select depart_id from worker where money 12000;
-- 2. 根据部门的编号再找出部门的名字
-- 你查找到的记录多于 1 行了 Subquery returns more than 1 row
-- select name from department where id (select depart_id from worker where money 12000);
select name from department where id in (select depart_id from worker where money 12000);
-- 查询行政财务和技术研发中的所有员工的信息
-- 1. 先根据名字来查找 id
select id from department where name in (行政财务, 技术研发);
-- 2. 再去查询相关的员工
select * from worker where depart_id in (select id from department where name in (行政财务, 技术研发));3多行多列 当你的子查询只要是多列那么它肯定在 from 后面是以一张表存在的。
select 字段 from (子查询) 表别名 where 条件; 子查询在这里要作为表然后还要给一个别名如果不这样处理的话就没办法访问到表中的字段。
-- 从 2019 年后入职的员工和相关部门信息
-- 1. 2019-1-1 后的时间
select * from worker where inWork_date 2019-01-01;
-- 2. 当我们从上面查找到对应的员工则可以通过员工的 depart_id 找到对应的部门信息
select * from department d, (select * from worker where inWork_date
2019-01-01) w
where d.id w.depart_id;
-- 将上面的例子换成内连接实现
select * from worker inner join department on worker.depart_id department.id where inWork_date 2019-01-01;第三章 事务 3.1 事务原理 事务要么成功要么不成功。 转账A - 500B 500。转账的过程中有可能会有一些突发的情况导致转账操作会出现一些意料不到的问题。所以在这里我们需要建立一个通道在通道中完成的操作要么成功要么不成功的时候及时回滚数据避免造成大面积的业务混乱。
-- 账户表
create table bankCount(id int primary key auto_increment,name varchar(50),money double
);
-- 添加数据
insert into bankCount(name, money)
values (cuihua, 1000), (banban, 2000);
-- 翠花给班班转钱 500
update bankCount set money money - 500 where name cuihua;
update bankCount set money money 500 where name banban;
-- 提供一个事务通道让转账的操作稳妥一些如果中途出现问题及时回滚数据不要造成数据丢失事务的原理 当我们在 MySQL 中如果开启了事务那么你所有的操作都会临时被保存在事务日志中只有遇上commit提交命令的时候才会同步到数据表中。如果遇上 rollback 和 断开连接那么它都会去清空你的事务日志。 3.2 手动提交 1核心 SQL 语句 开启事务start transaction 提交事务commit 回滚事务rollback 2实现过程 第一步开启事务 第二步执行你的 SQL 语句 第三步提交事务 第四步如果出现问题的话则回滚事务数据 3事务提交
第一步开启事务
start transaction;
第二步执行你的 SQL 语句
update bankCount set money money - 500 where name cuihua;
update bankCount set money money 500 where name banban;
第三步如果出现问题的话则回滚事务数据
commit;4事务回滚
第一步开启事务
start transaction;
第二步执行你的 SQL 语句
update bankCount set money money - 500 where name cuihua;
update bankCount set money money 500 where name banban;
第三步提交事务
rollback;3.3 自动提交 MySQL 默认情况下每一条 DML 语句都是一个单独的事务都会对应的开启一个事务当你执行的时候同时自动默认提交事务。 1自动提交事务
-- 随便一个 DML 语句都是会自动提交事务的
update bankCount set money money 500 where name banban;
2取消自动提交 autocommit 原来是 1 的如果你要取消的话则设置为 0 即可但不建议
set autocommit 0; 3.4 事务 1事务的特性 ACID a. 原子性Atomicity 在每一个事务中都可以看成是一个整体不能将其再度分解所有的操作要么一起成功要么一起失败。 b. 一致性Consistency 在事务执行前数据的所有状态跟执行后的数据状态应该是一样的。比如转账前两个账户的金额总和应该跟转账后两个账户的总金额都是一样的。 c. 隔离性Isolation 多个事务之间不能相互影响必须保证其操作的单独性否则会出现一些串改的情况执行的时候应该保持隔离的状态。 d. 持久性Durability 如果我们的事务执行成功之后它将把数据永久性存储到数据库中哪怕设备关机之后也是能够保存下来的。 2隔离级别可能会出现的问题 a. 脏读其中一个事务读取到了另外一个事务中的数据尚未提交的数据。 b. 不可重复读一个事务中两次读取数据的时候发现数据的内容不一样。要求多次读取数据的时候在一个事务中读出的都应该是一样的。一般是由于 update 操作引发所以将来执行的时候要特别注意。 c. 幻读一般都是 insert 或者 delete 操作的时候会出现这个问题。一个事务中两次读取数据的时候发现数据的数量不一样。要求在一个事务中多次去读取数据的时候都应该是一样的。 3MySQL 的四种隔离级别 如果你将来使用 命令行 来设置隔离级别的时候只有在当次会话中是有效的。只要你关掉了窗口隔离级别会即时恢复到默认状况 — RR。 read uncommitted 读未提交 read committed 读已提交 repeatable read 可重复读默认 serializable 串行化 3.5 案例演示了解 1脏读 先打开窗口设置隔离级别
set global transaction isolation level read uncommitted; 如果设置的级别是“读未提交”其实会造成一个脏读的问题。脏读会导致一个事务读取到了另外一个事务中的数据其实非常危险。 解决方案提升你的隔离级别。
set global transaction isolation level read committed; 当你设置成 read commintted 就不能读取到另外一个事务中的数据了。 只有当第一个事务提交了数据之后第二个事务才能够去读取到数据。 read committed 可以避免数据的脏读。
2不可重复读 如果将来你写的 SQL 语句发现第一次查询的时候是一个结果第二次查询的时候又是另外一个结果。一般都是最后一次查询的才是正确的有时候第一次不正确的结果会被误用就会给用户不好的体验。 订票PC 端 — App 端 — 短信 如果说每次查询的结果不一样的话则会导致推送用户信息的时候呈现的数据不同步。 解决方案将你的级别设置为 repeatable read 可重复读也就是 mysql 默认的级别不建议修改。 3幻读课后自己演示
set global transaction isolation level serializable ; 第四章 DCL DDL create、alter、drop DML insert、update、delete DQL select、show DCL grant、revoke 4.1 创建用户 如果将来创建一个新的用户它并不会拥有与 root 用户一样的权限root 是超级管理员所有的权限它都有。
create user 用户名主机名 identified by 密码;
CREATE USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 密码;
create user cuihualocalhost identified by 1234;
create user huahualocalhost identified by 1234;4.2 授权 如果想要使用这些新增的用户则需要授予一定的权限。
grant 权限1, 权限2,..., 权限N on 数据库名.表名 to 用户主机名
-- cuihua 的权限
-- 如果希望在某个数据库下所有的表都能用的话则建议写成 数据库名.*
grant create, alter, insert, update, select on hello.* to
cuihualocalhost;
-- 简单的赋权限方法
grant all on *.* to huahualocalhost;4.3 撤销授权
revoke all on hello.* from cuihualocalhost; 4.4 查看权限
-- 查看权限
show grants for 用户名主机名;4.5 删除用户
drop user 用户名主机名; 4.6 修改用户的密码
ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码;
