DCL管理用户、授权

:::tips

管理用户：

  • 添加用户：
    • 语法：create user ‘用户名’ @ ‘主机名’ identified by ‘密码’;
  • 删除用户：
    • 语法：drop user ‘用户名’ @ ‘主机名’;
  • 修改用户名密码：
    • 语法：update user set password = password(‘新密码’) where user = ‘用户名’;
    • set password for ‘用户名’ @ ‘主机名’ = password(‘新密码’);
  • 查询用户：
    • use mysql;
    • select * from user;
      [通配符：% 表示可以在任意主机使用用户登录数据库]

权限管理：

  • 查询权限：
    • 语法：show grants for ‘用户名’ @ ‘主机名’;
  • 授予权限：
    • 语法：grant 权限列表 on 数据库名.表名 to ‘用户名’ @ ‘主机名’;

  • 撤销权限：

    • 语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’ @ ‘主机名’; :::

      约束

      :::tips

      概念：

  • 对表中的数据进行限定，保证数据的正确性、有效性和完整性

    分类：

  • 主键约束： primary key

    • alter table 表名 add primary key(字段名); 添加主键约束
    • alter table 表名 drop primary key; 删除主键约束

  • 自动增长： auto_increment (配合主键使用，必须是数值类型)

  • 非空约束： not null

  • 唯一约束： unique

  • 外键约束： foreign key

    • 添加外键
      • create table 表名(
      • 字段名 字段类型
      • …
      • [constraint 外键名称] foreign key (外键列名称) references 主表名称(主表列名称)
      • )
    • 关键字描述
      • constyaint: 表示约束外键约束名： 给外键约束取个名字,将来通过约束名可以删除这个约束
      • foreign key(外键字段名): 指定某个字段作为外键
      • references主表(主键字段名) : 引用主表的主键的值或者具有unique约束的值
    • 删除外键
      • {alter table 表名 drop foreign key 外键名称;}
    • 已有表添加外键
      • alter table 从表 add[constyaint 外键约束名称] foreign key (外键字段名) references主表(主键字段名);

  • 级联操作

    • 这是数据库外键定义的⼀个可选项，⽤来设置当主键表中的被参考列的数据发⽣变化时，外键表中响应字段的变换规则的
    • [constyaint 外键约束名称] foreign key (外键字段名) references主表(主键字段名) on update cascade;

      分类：

  • 级联更新：on update cascade 操作外键时，相连表一同更新

  • 级联删除：on delete cascade 操作外键时，相连表一同删除 ::: ```sql /* 约束使用方式： 一、可以在建完表之后再添加（很少这么干，了解） 二、一般都是在创建表的时候直接加约束（经常）

为什么呢？ 因为当表创建完毕之后，有可能会添加数据。如果已添加的数据跟约束冲突了，那么此时约束就加不上去了。所以，我们一般是在创建表的时候就直接加约束了，因为在创建表的时候里面肯定是没有任何数据的。 */

/ =========== 主键约束 =========== / — 创建表学生表st1, 包含字段(id, name, age)将id做为主键 — 创建表时添加主键 CREATE TABLE st1 ( id INT PRIMARY KEY, NAME VARCHAR(10), age INT );

INSERT INTO st1 VALUES (1, ‘刘德华’, 60); INSERT INTO st1 VALUES (2, ‘郭富城’, 58);

— 删除主键约束(了解) ALTER TABLE st1 DROP PRIMARY KEY;

— 在已有表中添加主键约束(了解) ALTER TABLE st1 ADD PRIMARY KEY(id);

/ =========== 主键自动增长 =========== / — 创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长 CREATE TABLE st2 ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), age INT );

— 主键默认从1开始自动增长: i++ INSERT INTO st2 (NAME, age) VALUES (‘貂蝉’, 28); INSERT INTO st2 (NAME, age) VALUES (‘西施’, 18); INSERT INTO st2 (NAME, age) VALUES (‘王昭君’, 26); INSERT INTO st2 (NAME, age) VALUES (‘杨玉环’, 22);

— 修改自动增长的开始值(面试题) ALTER TABLE st2 AUTO_INCREMENT = 1000; INSERT INTO st2 (NAME, age) VALUES (‘李白’, 18);

/ =========== 唯一约束 =========== / / =========== 非空约束 =========== / / =========== 默认值约束 =========== / CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, — 员工id，主键且自增长 ename VARCHAR(50) NOT NULL UNIQUE, — 员工姓名，非空并且唯一 joindate DATE NOT NULL, — 入职日期，非空 salary DOUBLE(7,2) NOT NULL, — 工资，非空 bonus DOUBLE(7,2) DEFAULT 1000 — 奖金，如果没有奖金默认为1000 );

INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(1, ‘张三’, ‘1999-11-11’, 8800, 5000); INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(2, ‘李四’, ‘1999-11-11’, 8800, 5000);

— 演示非空约束 INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(3, NULL, ‘1999-11-11’, 8800, 5000);

— 演示唯一约束 INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(3, ‘李四’,’1999-11-11’, 8800, 5000);

— 演示默认约束 INSERT INTO emp(id, ename, joindate, salary) VALUES(3, ‘王五’, ‘1999-11-11’, 8800);

— 面试题: 主键是唯一和非空，普通的字段我们也可以添加唯一和非空,有区别吗? / 一张表只有一个主键 一张表可以有多个唯一非空的字段 /

/ =========== 外键约束约束 =========== / — 准备数据: 见PPT — 创建部门表 CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(20) );

— 创建员工表 CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT );

— 添加2个部门 INSERT INTO department (dep_name, dep_location) VALUES (‘研发部’, ‘广州’), (‘销售部’, ‘深圳’);

— 添加员工,dep_id表示员工所在的部门 INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 1), (‘李四’, 21, 1), (‘王五’, 20, 1), (‘老王’, 20, 2), (‘大王’, 22, 2), (‘小王’, 18, 2);

/ =========== 外键约束约束 =========== / — 删除从表 employee DROP TABLE employee;

— 创建 employee 并添加外键约束 CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT, CONSTRAINT fk_dep FOREIGN KEY(dep_id) REFERENCES department(id) );

— 正常添加数据 INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 1), (‘李四’, 21, 1), (‘王五’, 20, 1), (‘老王’, 20, 2), (‘大王’, 22, 2), (‘小王’, 18, 2);

— 添加不正常的数据,报错: Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 6);

— 删除外键约束(了解) ALTER TABLE employee DROP FOREIGN KEY fk_dep;

— 在已有表添加外键约束, 外键约束可以省略: CONSTRAINT 外键约束名 (了解) — 省略CONSTRAINT外键约束名 数据库会自动设置外键约束的名字,我们要到 3信息 中查找 ALTER TABLE employee ADD FOREIGN KEY(dep_id) REFERENCES department(id);

  1. <a name="MDjmb"></a>
  2. ### 多表查询
  3. :::tips
  4. <a name="ah9jH"></a>
  5. #### 分类：
  6. <a name="RJzZQ"></a>
  7. ##### 内连接查询：
  9. - 隐式内连接：
  10.    - 语法：select 列名列表 from 表名列表 where... 使用where条件来消除无用的数据(消除笛卡尔积)；
  11. - 显式内连接：
  12.    - 语法：select 字段列表 from 表名1 inner join 表名2 on 条件;
  13. <a name="T8Idg"></a>
  14. ##### 外连接查询：
  16. - 左外连接：
  17.    - 语法：select 字段列表 from 表名1 left [outer] join 表名2 on 条件;
  18.    - 查询的是左边所有数据以及其交集部分
  19. - 右外连接：
  20.    - 语法：select 字段列表 from 表名1 right [outer] join 表名2 on 条件;
  21.    - 查询的是右边所有数据以及其交集部分
  22. <a name="yVACG"></a>
  23. ##### 子查询：
  25. - 概念：查询中嵌套查询，称嵌套查询为子查询
  26. <a name="wwa39"></a>
  27. ##### 子查询结果是单行单列的：
  29. - 概念：子查询可以作为条件，使用运算符去运算
  30.    - [查询工资(salary)最高的员工信息]
  31.       - 查询最高工资：select max(salary) from 表名;
  32.       - 查询员工信息：select * from 表名 where 表名.salary = 最高工资;
  33.       - 合并：子查询：select * from 表名 where 表名.salary = (select max(salary) from 表名;)
  34. <a name="spc0g"></a>
  35. ##### 子查询结果是多行单列的：
  37. - 概念：子查询可以作为条件，使用运算符in/any/all来判断
  38. - [查询财务部和市场部的所有员工信息]
  39.    - 查询表1的'财务部'与'市场部'(name)的dept_id信息：
  40.       - select id from 表名1 where name = '财务部' or name = '市场部';
  41.    - 根据id查询表2对应的员工信息
  42.       - select * from 表名2 where dept_id = 1 or dept_id = 3;
  43.    - 合并：子查询：
  44.       - select * from 表名2 where dept_id in (select id from 表名1 where name = '财务部' or name = '市场部';)
  45. <a name="KhwJH"></a>
  46. ##### 子查询结果是多行多列的：
  48. - 概念：子查询可以作为一张虚拟表参与查询
  49. - [查询员工入职日期(date)是xxxx-xx-xx之后的员工信息和部门信息]
  50.    - 合并：子查询：
  51.       - select * from 表1 t1,(select * from 表2 where date > xxxx-xx-xx) t2 where ti.id = t2.id;(id 设置相等，则不会出现数据冗余)
  52. <a name="wFu1X"></a>
  53. #### union 合并查找结果集
  55. - 将两个 select 语句的结果作为一个整体显示出来
  56. - union在进行结果集合并的时候，要求两个结果集的列数相同
  57. - 结果集合并时列和列的数据类型也要一致
  58. :::
  59. ```sql
  60. SELECT s_name, s_post FROM staff WHERE s_post = '产品销售' OR s_post = '产品研发';
  61. /*等同于*/
  62. SELECT s_name, s_post FROM staff WHERE s_post IN ( '产品销售', '产品研发');
  63. /*等同于*/
  64. SELECT s_name, s_post FROM staff WHERE s_post = '产品销售'
  65. UNION
  66. SELECT s_name, s_post FROM staff WHERE s_post = '产品研发';
  1. /*  多表查询  */
  3. -- 准备数据
  4. -- 创建部门表
  5. CREATE TABLE tb_dept (
  6.   id INT PRIMARY KEY AUTO_INCREMENT,
  7.   NAME VARCHAR(20)
  8. );
  10. INSERT INTO tb_dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');
  12. -- 创建员工表
  13. CREATE TABLE tb_emp (
  14.   id INT PRIMARY KEY AUTO_INCREMENT,
  15.   NAME VARCHAR(10),
  16.   gender CHAR(1),   -- 性别
  17.   salary DOUBLE,   -- 工资
  18.   join_date DATE,  -- 入职日期
  19.   dept_id INT
  20. );
  22. INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
  23. INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
  24. INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
  25. INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
  26. INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
  27. INSERT INTO tb_emp VALUES (NULL, '白龙马', '男', 1, '2020-02-02', NULL);
  29. /* ===========隐式内连接=========== */
  30. -- 查询孙悟空员工的信息, 包括所在的部门名称
  31. -- 一次查询多张表
  32. -- 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积。
  33. SELECT * FROM tb_emp, tb_dept;
  35. -- 去掉笛卡尔积
  36. -- 去掉笛卡尔积的条件称为: 表连接条件
  37. SELECT * FROM tb_emp, tb_dept WHERE tb_emp.`dept_id` = tb_dept.`id`;
  39. -- 在加上查询员工名字为孙悟空
  40. SELECT * FROM tb_emp INNER JOIN tb_dept ON tb_emp.`dept_id` = tb_dept.`id` WHERE tb_emp.`NAME` = '孙悟空';
  42. -- 扩展:给表取别名,表取了别名后,只能使用别名啦!
  43. SELECT * FROM tb_emp AS e INNER JOIN tb_dept AS d ON e.`dept_id` = d.`id` WHERE e.`NAME` = '孙悟空';
  45. /* ===========显式内连接=========== */
  46. -- 显式内连接 INNER JOIN...ON
  47. SELECT * FROM tb_emp INNER JOIN tb_dept ON tb_emp.`dept_id` = tb_dept.`id`;
  49. -- INNER可以省略,初学者不建议省略
  51. /* ===========左外连接查询=========== */
  52. -- 左外连接查询 (满足要求的显示,保证左表不满足要求的也显示)
  53. SELECT * FROM tb_emp LEFT OUTER JOIN tb_dept ON tb_emp.`dept_id` = tb_dept.`id`;
  55. /* ===========右外连接=========== */
  56. -- 右外连接
  57. SELECT * FROM tb_emp RIGHT OUTER JOIN tb_dept ON tb_emp.`dept_id` = tb_dept.`id`;
  59. -- 一般在工作中我们都使用左外, 右外可以转成左外, 我们中国人的书写顺序,从左到右
  60. SELECT * FROM tb_dept RIGHT OUTER JOIN tb_emp ON tb_emp.`dept_id` = tb_dept.`id`;
  62. /* ===========子查询结果=========== */
  63. -- 子查询结果的三种情况
  64. -- 1.单行单列(一个值)
  65. -- 2.多行单列(多个值)
  66. -- 3.多行多列 (虚拟的表)
  68. /* ===========子查询的结果是单行单列=========== */
  69. -- 查询工资最高的员工是谁？
  70. -- 1.查询最高工资
  71. -- 2.通过最高工资查询员工姓名
  72. SELECT MAX(salary) FROM tb_emp;
  73. SELECT * FROM tb_emp WHERE salary = (SELECT MAX(salary) FROM tb_emp);
  74. -- 子查询心得:建议先写好一条SQL,再复制到另一个SQL语句中
  76. /* ===========子查询的结果是多行单列的时候=========== */
  77. -- 查询工资大于5000的员工, 来自于哪些部门的名字
  78. -- 1.查询工资大于5000的员工所在部门id
  79. -- 2.根据部门id查找部门名称
  80. SELECT dept_id FROM tb_emp WHERE salary > 5000;
  81. SELECT * FROM tb_dept WHERE id IN (SELECT dept_id FROM tb_emp WHERE salary > 5000);
  83. /* ===========子查询的结果是多行多列=========== */
  84. -- 查询出2011年以后入职的员工信息, 包括部门名称
  85. -- 1.查询出2011年以后入职的员工信息
  86. SELECT * FROM tb_emp WHERE join_date > '2011-01-01';
  87. -- 2.找到对应的部门信息
  88. SELECT * FROM (SELECT * FROM tb_emp WHERE join_date > '2011-01-01') AS e LEFT OUTER JOIN tb_dept ON e.dept_id = tb_dept.`id`;
  90. /* ===========多表查询练习=========== */
  91. CREATE DATABASE day172;
  92. USE day172;
  94. -- 准备数据
  95. -- 部门表
  96. CREATE TABLE dept (
  97.   id INT PRIMARY KEY, -- 部门id
  98.   dname VARCHAR(50), -- 部门名称
  99.   loc VARCHAR(50) -- 部门位置
  100. );
  102. -- 添加4个部门
  103. INSERT INTO dept(id,dname,loc) VALUES 
  104. (10,'教研部','北京'),
  105. (20,'学工部','上海'),
  106. (30,'销售部','广州'),
  107. (40,'财务部','深圳');
  109. -- 职务表, 职务名称, 职务描述
  110. CREATE TABLE job (
  111.   id INT PRIMARY KEY,
  112.   jname VARCHAR(20),
  113.   description VARCHAR(50)
  114. );
  116. -- 添加4个职务
  117. INSERT INTO job (id, jname, description) VALUES
  118. (1, '董事长', '管理整个公司, 接单'),
  119. (2, '经理', '管理部门员工'),
  120. (3, '销售员', '向客人推销产品'),
  121. (4, '文员', '使用办公软件');
  123. -- 员工表
  124. CREATE TABLE emp (
  125.   id INT PRIMARY KEY, -- 员工id
  126.   ename VARCHAR(50), -- 员工姓名
  127.   job_id INT, -- 职务id
  128.   mgr INT , -- 上级领导
  129.   joindate DATE, -- 入职日期
  130.   salary DECIMAL(7,2), -- 工资
  131.   bonus DECIMAL(7,2), -- 奖金
  132.   dept_id INT, -- 所在部门编号
  133.   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  134.   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
  135. );
  137. -- 添加员工
  138. INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
  139. (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
  140. (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
  141. (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
  142. (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
  143. (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
  144. (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
  145. (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
  146. (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
  147. (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
  148. (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
  149. (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
  150. (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
  151. (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
  152. (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
  154. -- 工资等级表
  155. CREATE TABLE salarygrade (
  156.   grade INT PRIMARY KEY,
  157.   losalary INT,
  158.   hisalary INT
  159. );
  161. -- 添加5个工资等级
  162. INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
  163. (1,7000,12000),
  164. (2,12010,14000),
  165. (3,14010,20000),
  166. (4,20010,30000),
  167. (5,30010,99990);
  169. -- 多表查询规律
  170. -- 1.根据需求明确查询哪些表
  171. -- 2.明确表连接条件去掉笛卡尔积
  172. -- 3.后续的查询
  174. -- 练习1
  175. -- 查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述
  176. -- 1.根据需求明确查询哪些表: emp, job
  177. SELECT * FROM emp INNER JOIN job;
  178. -- 2.明确表连接条件去掉笛卡尔积
  179. SELECT * FROM emp INNER JOIN job ON emp.`job_id` = job.`id`;
  180. -- 3.后续的查询
  181. SELECT
  182.     emp.`id` AS 员工编号,
  183.     emp.`ename` AS 员工姓名,
  184.     emp.`salary` AS 工资,
  185.     job.`jname` AS 职务名称,
  186.     job.`description` AS 职务描述
  187. FROM 
  188.     emp INNER JOIN job ON emp.`job_id` = job.`id`;
  190. -- 练习2
  191. -- 查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置
  192. -- 1.根据需求明确查询哪些表: emp, job, dept
  193. SELECT * FROM emp INNER JOIN job INNER JOIN dept;
  194. -- 2.明确表连接条件去掉笛卡尔积
  195. SELECT
  196.     *
  197. FROM
  198.     emp INNER JOIN job ON emp.`job_id` = job.`id`
  199.     INNER JOIN dept ON emp.`dept_id` = dept.`id`;
  200. -- 3.后续的查询
  201. SELECT
  202.     emp.`id` AS 员工编号,
  203.     emp.`ename` AS 员工姓名,
  204.     emp.`salary` AS 工资,
  205.     job.`jname` AS 职务名称,
  206.     job.`description` AS 职务描述,
  207.     dept.`dname` AS 部门名称,
  208.     dept.`loc` AS 部门位置
  210. FROM
  211.     emp INNER JOIN job ON emp.`job_id` = job.`id`
  212.     INNER JOIN dept ON emp.`dept_id` = dept.`id`;
  214. -- 练习3
  215. -- 查询经理的信息。显示员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置, 工资等级
  216. -- 1.根据需求明确查询哪些表: emp, job, dept, salarygrade
  217. SELECT * FROM emp INNER JOIN job INNER JOIN dept INNER JOIN salarygrade;
  218. -- 2.明确表连接条件去掉笛卡尔积
  219. SELECT 
  220.     *
  221. FROM
  222.     emp INNER JOIN job ON emp.`job_id` = job.`id`
  223.     INNER JOIN dept ON emp.`dept_id` = dept.`id`
  224.     INNER JOIN salarygrade ON emp.`salary` BETWEEN salarygrade.`losalary` AND salarygrade.`hisalary`;
  225. -- 3.后续的查询
  226. SELECT 
  227.     emp.`ename` AS 员工姓名,
  228.     emp.`salary` AS 工资,
  229.     job.`jname` AS 职务名称,
  230.     job.`description` AS 职务描述,
  231.     dept.`dname` AS 部门名称,
  232.     dept.`loc` AS 部门位置,
  233.     salarygrade.`grade` AS 工资等级
  234. FROM
  235.     emp INNER JOIN job ON emp.`job_id` = job.`id`
  236.     INNER JOIN dept ON emp.`dept_id` = dept.`id`
  237.     INNER JOIN salarygrade ON emp.`salary` BETWEEN salarygrade.`losalary` AND salarygrade.`hisalary`
  238. WHERE job.`jname` = '经理';
  240. -- 练习4
  241. -- 查询出部门编号、部门名称、部门位置、部门人数
  242. -- 1.根据需求明确查询哪些表: emp, dept
  243. SELECT * FROM emp INNER JOIN dept;
  244. -- 2.明确表连接条件去掉笛卡尔积
  245. SELECT 
  246.     * 
  247. FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
  248. -- 3.后续的查询
  249. SELECT 
  250.     dept.`dname`,
  251.     COUNT(emp.`id`)
  252. FROM emp RIGHT OUTER JOIN dept ON emp.`dept_id` = dept.`id`
  253. GROUP BY dept.`dname`
  254. ORDER BY COUNT(emp.`id`) DESC;
  256. -- 练习5
  257. -- 列出所有员工的姓名及其直接上级领导的姓名, 没有上级领导的员工也需要显示,显示自己的名字和领导的名字
  258. -- 1.根据需求明确查询哪些表: emp pt, emp ld
  259. SELECT * FROM emp staff INNER JOIN emp boss;
  260. -- 2.明确表连接条件去掉笛卡尔积
  261. -- 3.后续的查询
  262. SELECT 
  263.   staff.`ename` AS '普通员工',
  264.     boss.`ename` AS '老板'
  265. FROM emp AS staff LEFT OUTER JOIN emp AS boss ON staff.`mgr` = boss.`id`;

事务

:::tips

概念：

  • 如果一个包含多个步骤的业务操作，被事务管理，那么操作要么同时成功，要么同时失败

    操作：

  • 开启事务：start transition 或 begin;

  • 回滚：rollback;

  • 提交：commit;

    案例：

  • start transition;

  • 添加数据
  • 发现出问题：rollback;

  • 发现没问题：commit;

    mysql数据库中事务默认是自动提交

    事务提交的两种方式：

  • 自动提交:

    • mysql就是自动提交的
    • 一条DML(增删改)语句会自动提交一次事务
  • 手动提交：
    • Oracle 数据库就是手动提交事务
    • 需要先开启事务，再提交

  • 修改事务的默认提交方式：

    • 查看事务的默认提交方式：select @@autocommit; ‘1’代表自动提交，’0’代表手动提交
    • 修改默认提交方式：set @@autocommit = 0;

      事务的四大特征：

  • 原子性(Atomicity)：是不可分割的最小操作单位，要么同时成功，要么同时失败

  • 持久性(Durability)：当事务提交或回滚后，数据库会持久化的保存数据
  • 隔离性(isolation)：多个事务之间相互独立

  • 一致性(Consistency)：事务操作前后，数据总量不变

    事务的隔离级别：

    概念：

  • 多个事务之间的隔离，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

    存在问题：

  • 脏读：一个事务，读取到另一个事务中没有提交的数据

  • 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样

  • 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加一条数据，则第一个事务查询不到自己的修改

    隔离级别：

  • read uncommitted：读未提交

    • 产生的问题：脏读、不可重复读、幻读
  • read committed：读已提交(Oracle默认)
    • 产生的问题：不可重复读、幻读
  • repeatable read：可重复读(mysql默认)
    • 产生的问题：幻读

  • serializable：串行化

    • 可以解决所有问题
      [注意：隔离级别从小到大安全性越来越大，但是效率越来越低]

      数据库查询隔离级别：

  • select @@tx_isolation;

    数据库设置隔离级别：

  • set global transaction isolation level 级别字符串; :::

    存储引擎

    添加存储引擎

    • 在建表的时候可以在最后小括号的”)”的右边使用
    • ENGINE来指定存储引擎
    • CHARSET来指定这张表的字符编码方式

      MyISAM存储引擎

      特征 : 使用三个文件表示每个表
    • 格式文件 — 存储表结构的定义（mytable.frm）
    • 数据文件 — 存储表行的内容（mytable.MYD）
    • 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制
      优势
    • 可被转换为压缩、只读表来节省空间
    • 存储引擎的优势
    • MyISAM不支持事务机制，安全性低

      InnoDB存储引擎(默认)

      优势

    • InnoDB支持事务，支持数据库崩溃后自动恢复机制, 非常安全
    • 不能压缩，不能转换为只读, 不能节省存储空间, 效率不高
      特征:
    • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
    • InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
    • 提供一组用来记录事务性活动的日志文件
    • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
    • 提供全 ACID 兼容
    • 在 MySQL 服务器崩溃后提供自动恢复
    • 多版本（MVCC）和行级锁定
    • 支持外键及引用的完整性，包括级联删除和更新