【JavaLearn】#（17）MySQL基础知识、DML及DDL语句、外键及非外键约束、外键策略、DQL语句（单表、多表）、连接查询、子查询、索引、事务、视图、存储过程、用户权限及角色管理

LRcoding

已于 2022-03-28 13:58:18 修改

阅读量1.6k

点赞数

分类专栏： JavaLearn 文章标签： mysql 数据库 sqlserver

于 2021-12-26 21:11:50 首次发布

本文链接：https://blog.csdn.net/qq_38134242/article/details/122160907

版权

JavaLearn 专栏收录该内容

31 篇文章 3 订阅

订阅专栏

1. 基础知识

1.1 数据库基础知识

数据：计算机可以处理的数据，字母、文字、文本、图形、音频、视频等
数据库：Database，以一定格式存放数据，能够实现多个用户共享，与应用程序彼此独立的数据集合
数据库管理系统：DBMS，用来定义和管理数据的软件，MySQL、Oracle、SQL server等
数据库应用系统：在数据库管理系统的基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，学生管理系统等
数据库管理员：DBA，是指对数据库管理系统进行操作的人员
最终用户：数据库应用程序的使用者，并不会直接与数据库打交道
数据库系统：DBS，一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成

在这里插入图片描述

数据库类型：
- 网状数据库、层次数据库
- 关系型数据库
  - Oracle、DB2、SQL server、MySQL等
  - 用二元关系（二维表）来表示数据
非关系型数据库 NOSQL（Not Only SQL）
- 适用于超大规模和高并发，大数据问题
常见的关系型数据库
- Oracle，甲骨文公司，一般用在银行、金融等大型数据库
- DB2， IDM公司
- SQL server，微软公司，只能在windows上运行
- MySQL，开源、轻量型

1.2 MySQL基础知识

优点：开源、跨平台、轻量级、成本低（企业级收费）
默认端口：3306，Oracle是1521，SQL server是1433
Oracle：数据库orcl ---- 很多用户（system、scott） ----- 数据库表。很少创建数据库
MySQL：用户（root、zhangsan） ---- 很多数据库 ----- 数据库表。经常创建数据库
MySQL自带数据库
- information_schema ：提供了访问数据库元数据的方式。保存着数据库名、表名、列的数据类型、访问权限等
- mysql ：核心数据库。存储数据库的用户、权限设置、关键字等
- performance_schema ：收集数据库服务器性能参数，锁、互斥变量等；保存历史的事件汇总信息
- sys ： MySQL5.7新增的数据库系统，该库通过视图将information_schema 和 performance_schema 结合起来
- sakila ：模拟 DVD 租赁信息管理的数据库，测试库
SQL语言分类
- 数据查询语言 (Data Query Language，DQL) ==》 SELECT子句，FROM子句，WHERE子句
- 数据操作语言 (Data manipulation Language，DML) ==》 INSERT， UPDATE， DELETE
- 数据定义语言 (Data Definition Language，DDL) ==》数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）操作，CREATE、DROP、ALTER
- 数据控制语言 (Data Control Language，DCL) ==》授权或回收操作， GRANT、REVOKE
- 事务控制语言 (Transaction Control Language，TCL) ==》事务的控制， START TRANSACTION、COMMIT、ROLLBACK、SET TRANSACTION

1.3 SQL语句入门

1.3.1 连接MySQL相关命令

开启服务：net start mysql80（安装的MySQL版本为 8.0，如果是5.7，则服务名为mysql57）
停止服务：net stop mysql80
连接MySQL： mysql -h 127.0.0.1 -u root -p，输入密码
退出MySQL： exit、quit、\q

1.3.2 操作数据库命令

显示所有数据库： SHOW DATABASES;
删除数据库： DROP DATABASE 数据库名;
创建数据库： CREATE DATABASE 数据库名;
切换到数据库： USE 数据库名;
显示数据库里的所有表： SHOW TABLES;
查看表的结构： DESC 表名;

2. DDL 和 DML

2.1 创建数据库表

表（table）是数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表表示

MySQL提供了一个伪表（dual）

在这里插入图片描述

表中的行称为记录，表中的列称为这些记录的字段

【示例一】创建数据库表（无限制版）

-- •	建立一张用来存储学生信息的表 
-- •	字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
CREATE TABLE student (
sno INT ( 6 ),    -- 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
sname VARCHAR ( 10 ),
sex CHAR ( 1 ),
age INT ( 3 ),
enterdate DATE,  -- 写入当前的时间 now()  sysdate()  CURRENT_DATE()  方式多样'1256-12-23'  "1256/12/23"  "1256.12.23"
classname VARCHAR ( 10 ),
email VARCHAR ( 15 ) -- 最后一个字段没有，
);


-- 查看表的结构
DESC student;

-- 查看完整的建表语句
SHOW CREATE TABLE student;

2.2 DML语句

【示例二】添加、更新、删除数据（基础版）

-- 添加数据
INSERT INTO student VALUES ( 1, '张三丰', '男', 34, '1256-12-23', '武当一班', 'zhsf@wd.cn' );
INSERT INTO student VALUES ( 1, '张三丰', '男', 34, '1256-12-23', '武当一班', 'zhsf@wd.cn' );  -- 可以插入，因为还没做主键约束
INSERT INTO student ( sno, NAME, enterdate ) VALUES ( 1, '张三丰', sysdate( ) );  -- 插入几个字段， 时间还可用 now()


-- 修改数据
UPDATE student SET sex = '男', age = 64 WHERE sno = 12345678 -- 需要用where子句限制修改哪一条记录
UPDATE student SET sno = 2 WHERE sno = 12345678 

-- 删除数据
DELETE FROM student WHERE email IS NULL -- delete必须后跟from      is null  不是 = null
DELETE FROM student; -- 删除所有数据

-- 清空数据
TRUNCATE student -- 不可回滚，属于DDL，执行效率高，自增值回到1

2.3 DDL语句

【示例三】修改、删除表结构（不是数据）

-- 查询表的结构
DESC student


-- 修改表的结构 
	-- 增加一列
	ALTER TABLE student ADD score DOUBLE(5, 2);   -- 5 总的位数，  2  小数位数
	ALTER TABLE student ADD score DOUBLE(5, 2) FIRST;
	ALTER TABLE student ADD score DOUBLE(5, 2) AFTER enterdate;
	
	-- 修改一列
	ALTER TABLE student MODIFY score FLOAT(3, 1) -- 修改score列的数据类型
	ALTER TABLE student CHANGE score scoreNew DOUBLE(4, 1)  -- 修改score列的列名，及数据类型
	
	-- 删除一列
	ALTER TABLE student DROP score;
	
	
-- 修改表名
ALTER TABLE student RENAME TO stu


-- 删除表
DROP TABLE student

3. 完整性约束

3.1 非外键约束

约束条件	约束描述
`PRIMARY KEY`	主键约束，约束字段的值可唯一地标识对应的记录
`NOT NULL`	非空约束，约束字段的值不能为空
`UNIQUE`	唯一约束，约束字段的值是唯一的
`CHECK`	检查约束，限制某个字段的取值范围
`DEFAULT`	默认值约束，约束字段的默认值
`AUTO_INCREMENT`	自动增加约束，约束字段的值自动递增

（一）在建表的时候加入约束条件

-- •	学号是主键 = 不能为空 +  唯一 
-- •	姓名不能为空 
-- •	性别默认值是男
-- •	年龄限制 18 —— 30 之间
-- •	Email唯一

CREATE TABLE student (
sno INT ( 4 ) PRIMARY KEY AUTO_INCREMENT,        -- 主键(PRIMARY KEY) = 不能为空 +  唯一     int类型的主键，可以让其自增(AUTO_INCREMENT)
sname VARCHAR ( 10 ) NOT NULL,                   -- 不能为空(NOT NUL)
sex CHAR ( 1 ) DEFAULT '男',                     -- 默认值(DEFAULT)
age INT ( 2 ) CHECK ( age >= 18 AND age <= 30 ), -- 限制取值(CHECK)
enterdate DATE,
classname VARCHAR ( 15 ),
email VARCHAR ( 20 ) UNIQUE                      -- 唯一(UNIQUE)，可以为空
)

（二）建表时加入约束，可以自定义约束的名字

CREATE TABLE student(
  sno INT(4) AUTO_INCREMENT,
  sname VARCHAR(10) NOT NULL,
  sex CHAR(1) DEFAULT '男',
  age INT(3),
  enterdate DATE,
  classname VARCHAR(10),
  email VARCHAR(15),
  CONSTRAINT pk_stu PRIMARY KEY(sno),                     -- 主键约束
  CONSTRAINT ck_stu_age CHECK(age >= 18 AND age <= 30),   -- 检查约束
  CONSTRAINT un_stu_email UNIQUE(email)                   -- 唯一约束
);

（三）建表之后，增加约束

ALTER TABLE student ADD CONSTRAINT pk_stu PRIMARY KEY(sno);
ALTER TABLE student MODIFY sno INT(4) AUTO_INCREMENT;

3.2 外键约束

3.2.1 创建外键

外键约束（FOREIGN KEY，FK）用来实现数据库表的参数完整性。可以使两张表紧密的结合起来，针对修改或者删除的级联操作时，会保证数据的完整性

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。

定义外键：

-- 班级表
CREATE TABLE t_class (
  cno INT(4) PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(12) NOT NULL
);

CREATE TABLE t_student (
  sno INT(4) PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(10) NOT NULL,
  age INT(3) CHECK(age >= 18 AND age <= 30),
  sex CHAR(1) DEFAULT '男',
  classno INT(4),
    
  -- 定义 classno 字段为外键，参照 t_class 表的 cno
  CONSTRAINT fk_stu_classno FOREIGN KEY(classno) REFERENCES t_class(cno) 
);

或者在定义完表之后：

ALTER TABLE t_student ADD CONSTRAINT fk_stu_classno FOREIGN KEY(classno) REFERENCES t_class(cno);

3.2.2 外键策略

建立外键之后，修改或删除数据时（例如 zhangsan 同学所在的班级编号为 5，此时如果直接删除编号为5 的班级时，会因为外键的存在而报错），相关联的记录有几种外键策略：

NO ACTION：不采取操作，想要删除班级，要么将 zhangsan 的班级编号置为 null，要么先删除 zhangsan 这条记录

CASCADE：级联更新，需要在定义外键的时候指定

ALTER TABLE t_student ADD CONSTRAINT fk_stu_classno FOREIGN KEY(classno) REFERENCES t_class(cno)
ON UPDATE CASCADE ON DELETE CASCADE
-- 此时直接删除或修改班级编号时，zhangsan 的班级编号会随之更新，或直接记录被删除

SET NULL：值置为 null

ALTER TABLE t_student ADD CONSTRAINT fk_stu_classno FOREIGN KEY(classno) REFERENCES t_class(cno)
ON UPDATE SET NULL ON DELETE SET NULL
-- 此时直接删除或修改班级编号时， zhangsan 的班级编号会被置为 null

4. 快速创建表

用已有的表来快速创建表

CREATE TABLE newStu
AS
SELECT * FROM student -- 既要结构也要数据

SELECT * FROM student WHERE 1=2 -- 只要结构

SELECT sno, sname FROM student -- 只要部分字段

5. DQL 语句—单表

5.1 准备数据

-- 部门表
create table DEPT (  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)   -- 地址
);  
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);  

-- 员工表
create table EMP  (  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),   -- 岗位
  MGR      int(4),       -- 上级编号，类似于外键，参考当前表的 EMPNO
  HIREDATE DATE,         -- 入职时间
  SAL      double(7,2),  -- 工资
  COMM     double(7,2),  -- 补助
  DEPTNO   int(2)        -- 外键，参考 DEPT 表的 DEPTNO
);  
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);  

-- 工资等级
create table SALGRADE  (  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
commit;  


insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');  
commit;  


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
commit;  


insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);  
commit;

5.2 最简单的SQL语句

-- 最简单的查询语句
SELECT * FROM dept 
SELECT * FROM emp 


-- 显示部分列
SELECT empno, ename, comm, deptno FROM emp 


-- 显示部分行
SELECT empno, ename, comm, deptno FROM emp WHERE sal < 2500 


-- 别名
SELECT empno '编号', ename '姓名', comm '补助', deptno '部门编号' FROM emp WHERE sal < 2500


-- 算术运算
SELECT empno, ename, sal, sal*1.1 '新工资' FROM emp WHERE sal < 2500
SELECT empno, ename, sal, comm, sal + IFNULL(comm, 0) FROM emp WHERE sal < 2500  -- 使用IFNULL，避免为 null时，计算结果不正确的问题


-- 去重，对后面所有列去重
SELECT DISTINCT job FROM emp


-- 排序
SELECT * FROM emp ORDER BY empno     -- 默认升序 ASC
SELECT * FROM emp ORDER BY sal DESC  -- 降序排列
SELECT * FROM emp ORDER BY sal DESC, hiredate  -- 先按工资降序，再按入职时间升序

5.3 WHERE 子句

不允许在 WHERE 子句中使用多行函数

-- 最简单的条件
SELECT * FROM emp WHERE deptno <= 10
SELECT * FROM emp WHERE deptno <> 10
SELECT * FROM emp WHERE BINARY job = 'clerk' -- BINARY区分大小写


-- 逻辑运算符 and
SELECT * FROM emp WHERE sal > 1500 AND sal < 3000
SELECT * FROM emp WHERE sal > 1500 && sal < 3000
SELECT * FROM emp WHERE sal >= 1500 AND sal <= 3000 ORDER BY sal


-- 逻辑运算符 or
SELECT * FROM emp WHERE deptno = 10 OR deptno = 20
SELECT * FROM emp WHERE deptno = 10 || deptno = 20

SELECT * FROM emp WHERE job IN ('MANAGER', 'PRESIDENT')


-- 模糊匹配 % 
-- (%代表任意多个字符0, 1, 2......)
-- ( _ 代表一个字符)
SELECT * FROM emp WHERE ename LIKE '%m%'
SELECT * FROM emp WHERE ename NOT LIKE '%m%'


-- IS NULL
SELECT * FROM emp WHERE comm IS NULL
SELECT * FROM emp WHERE comm IS NOT NULL


-- 小括号
SELECT * FROM emp WHERE job = 'SALESMAN' OR job = 'MANAGER' AND sal > 1290   -- 先 AND 再 OR
SELECT * FROM emp WHERE (job = 'SALESMAN' OR job = 'MANAGER') AND sal > 1290 -- 改变运算优先级
SELECT * FROM emp WHERE job = 'SALESMAN' OR (job = 'MANAGER' AND sal > 1290) -- 等于第一条语句

5.4 单行函数

函数作用：提高 SELECT 查询数据的能力，函数不会修改数据库表的数据

字符串函数

函数	描述
CONCAT(str1, str2, ···, strn)	将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr)	将字符串str从第index位置开始的n个字符替换成字符串newstr
LENGTH(str)	获取字符串str的长度
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换为大写
LEFT(str, n)	获取字符串str最左边的n个字符
RIGHT(str, n)	获取字符串str最右边的n个字符
LPAD(str, n, pad)	使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad)	使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str,index,n)	获取从字符串str的index位置开始的n个字符

数值函数

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

日期与时间函数

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
SYSDATE()	返回该函数执行时的日期和时间
DAYOFYEAR(date)	返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周
DATE_FORMAT(date, format)	返回按字符串format格式化后的日期date
DATE_ADD(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit)	返回date减去一个时间间隔后的新时间值
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数

流程函数

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END	如果value等于value1，则返回result1，···，否则返回result `类似于switch`
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result `类似于 if -- else if -- else`

JSON函数

函数	描述
JSON_APPEND()	在JSON文档中追加数据
JSON_INSERT ()	在JSON文档中插入数据
JSON_REPLACE ()	替换JSON文档中的数据
JSON_REMOVE ()	从JSON文档的指定位置移除数据
JSON_CONTAINS()	判断JSON文档中是否包含某个数据
JSON_SEARCH()	查找JSON文档中给定字符串的路径

其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作，MySQL8中不再支持
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

5.5 多行函数

多行函数会自动忽略 NULL 值
MAX(), MIN(), COUNT() 针对所有数据类型
SUM(), AVG() 只针对数值类型

函数	描述
COUNT()	统计表中记录的数目 `COUNT(1)`
SUM()	计算指定字段值的总和
AVG()	计算指定字段值的平均值
MAX()	统计指定字段值的最大值
MIN()	统计指定字段值的最小值

5.6 GROUP BY 和 HAVING 子句

字段、多行函数不能共存，除非使用分组

-- 统计各个部门的平均工资（只显示平均工资2000以上的）
SELECT deptno, AVG( sal ) FROM emp GROUP BY deptno HAVING AVG( sal ) >= 2000


-- 统计各个岗位的平均工资，除了 MANAGER
SELECT job, AVG( sal ) FROM emp GROUP BY job HAVING job != 'MANAGER'
SELECT job, AVG( sal ) FROM emp WHERE job != 'MANAGER' GROUP BY job

5.7 总结

SELECT 语句的执行顺序： FROM – WHERE – GROUP BY – SELECT – HAVING – ORDER BY

-- 列出工资最小值小于2000的职位
SELECT job, MIN( sal ) FROM emp GROUP BY job HAVING MIN( sal ) < 2000


-- 列出平均工资大于1200元的部门和工作搭配组合
SELECT deptno, job, AVG( sal ) FROM emp GROUP BY deptno, job HAVING AVG( sal ) > 1200


-- 统计 [人数小于4] 的部门的平均工资
SELECT deptno, AVG( sal ), COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1) < 4


-- 统计各部门的最高工资，排除最高工资小于 3000 的部门
SELECT deptno, MAX( sal ) FROM emp GROUP BY deptno HAVING MAX( sal ) >= 3000

6. DQL 语句—多表

6.1 内连接查询

只显示匹配的数据，同名的列，一个表中为null，另一个表有值，也不显示

-- 查询员工的编号、姓名和部门编号、部门名称
SELECT empno, ename, deptno FROM emp


-- 【交叉连接】 CROSS JOIN      （笛卡尔积，没有实际意义，有理论意义）
SELECT * FROM emp CROSS JOIN dept


-- 【自然连接】 NATURAL JOIN    （自动的匹配所有的同名列，简单强大，但无法只匹配部分的同名列）
SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname FROM emp e NATURAL JOIN dept d


-- 【USING 子句】               （同名的列可以使用，但是不同名的无法使用）
SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname FROM emp e JOIN dept d USING(deptno)


-- 【ON 子句】                   ！！！！推荐使用
SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname FROM emp e JOIN dept d ON (d.deptno = e.deptno)

6.2 外连接查询

除了显示匹配的数据之外，还可以显示部分或者全部不匹配的数据

-- 【左外连接】    LEFT JOIN       (除了显示匹配的数据，还要显示左表的不匹配数据 -- 【显示左表的所有数据】)
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
	
	
-- 【右外连接】    RIGHT JOIN     （显示右表的所有数据）
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno
	
	
-- 【全外连接】    FULL JOIN      （显示左、右表的所有数据）  MySQL还没支持全外连接
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
UNION  -- 并集，去除重复的
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
UNION ALL -- 并集，没有去重 【效率高，如果没有去重要求，建议使用】
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno

6.3 三表查询

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
SELECT
	e.empno,
	e.ename,
	e.sal,
	e.deptno,
	d.dname,
	sg.grade 
FROM
	emp e
	LEFT JOIN dept d ON e.deptno = d.deptno
	LEFT JOIN salgrade sg ON e.sal >= sg.losal AND e.sal <= sg.hisal  -- ON 里面写条件

6.4 自连接查询

自己关联自己，一般有一个列的值可以关联到主键

-- 查询员工的编号、姓名、上级编号、上级的姓名
SELECT
	e.empno,
	e.ename,
	e.mgr,
	e1.ename 
FROM
	emp e
	LEFT JOIN emp e1 ON e.mgr = e1.empno

7. 子查询

7.1 不相关子查询

子查询可以独立运行，先运行子查询，再运行外查询

-- 查询所有比 CLARK 工资高的员工信息
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK')



-- 单行子查询
	-- 查询工资高于平均工资的雇员名字和工资
	SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)
	
	-- 查询和CLARK同一部门且比他工资低的雇员名字和工资
	SELECT ename, sal FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'CLARK') AND sal < (SELECT sal FROM emp WHERE ename = 'CLARK')
	
	-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
	SELECT ename, hiredate FROM emp WHERE hiredate < (SELECT hiredate FROM emp WHERE ename = 'SCOTT' ) AND job = (SELECT job FROM emp WHERE ename = 'SCOTT')
	
	
	
-- 多行子查询!！！！！ 【使用IN】
	-- 查询工资低于【任意一个】“CLERK”的工资的雇员信息
	SELECT ename, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')
	
	-- 查询工资比【所有的】“SALESMAN”都高的雇员的编号、名字和工资
	SELECT empno, ename, sal FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE job = 'SALESMAN')
	
	-- 查询部门20中职务同部门10的雇员一样的雇员信息
	SELECT ename, job FROM emp WHERE deptno = 20 and job IN (SELECT job FROM emp WHERE deptno = 10)

7.2 相关子查询

子查询不可以独立运行，先运行外查询，再运行子查询

-- 查询最高工资的员工  （不相关子查询）
SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)



-- 查询【本部门】【最高工资】的员工
SELECT * FROM emp e WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = e.deptno)  -- 【子查询依赖于外边的   ！！！ deptno = e.deptno 】
-- ==》 SELECT * FROM emp e WHERE e.deptno = 10 AND sal = (SELECT MAX(sal) FROM emp WHERE deptno = 10)
--      UNION SELECT * FROM emp e WHERE e.deptno = 20 AND sal = (SELECT MAX(sal) FROM emp WHERE deptno = 20)
--      UNION SELECT * FROM emp e WHERE e.deptno = 30 AND sal = (SELECT MAX(sal) FROM emp WHERE deptno = 30)



-- 查询[工资高于其所在部门的平均工资的]那些员工
SELECT * FROM emp e WHERE sal >= (SELECT AVG(sal) FROM emp WHERE job = e.job)



-- 查询每个部门平均薪水的等级 【子查询与连接查询结合】
SELECT dsa.*, sg.grade FROM (
SELECT deptno, AVG(sal) asl FROM emp GROUP BY deptno
) dsa
LEFT JOIN salgrade sg ON dsa.asl BETWEEN sg.losal AND sg.hisal

8. 数据库对象

8.1 索引 index

提高查询速度，降低了添加、修改、删除的速度，占用小部分空间

数据库会自动给主键、唯一键、外键建立索引，其他经常在 WHERE 子句和 ORDER BY 子句中出现的手动创建索引

索引类型：
- 单列索引和多列索引
- 唯一索引和非唯一索引
- 存储结构：B-Tree，R-Tree 和 Hash

-- 获取所有数据，是否用索引，意义不大
SELECT * FROM emp


-- 数据库自动给主键列 empno 创建索引，查询条件中出现 empno 则自动使用索引查询
SELECT * FROM emp WHERE empno = 7654


-- 因为没有给 ename 列创建索引，此时是逐个查询比较，效率低下
SELECT * FROM emp WHERE ename = 'BLAKE'


-- 创建一个索引，再进行查询则效率显著提升    【单个索引】
CREATE INDEX index_emp_ename ON emp(ename)
-- 显示索引
SHOW INDEX FROM emp
-- 删除索引
DROP INDEX index_emp_ename ON emp


-- 没有给 sal、hiredate 创建索引
SELECT * FROM emp ORDER BY sal, hiredate
-- 创建一个索引
CREATE INDEX index_emp_sal_hiredate ON emp(sal, hiredate DESC)

8.2 事务

用来维护数据库完整性的，一系列的 SQL 操作，要么全部执行完成，要么都不执行

默认事务自动提交、一条 DML 语句是一个事务

事务的特性：
- A（Atomicity、原子性）: 不可分割的，要么都成功，要么都不成功
- C（Consistency、一致性）：事务执行的结果必须使数据库从一个一致性状态变为另一个一致性状态
- I（Isolation、隔离性）：各个事务的执行互不干扰
- D（Durability、持久性）：对数据所作的任何改变都会存到物理硬盘中

-- 创建 account 账户表
create table account(  
    id int primary key auto_increment,  
    username varchar(30) not null,  
    balance double  
); 

-- 加入两条数据
insert into account (username, balance) values('张三', 2000),('李四', 2000); 

-- 如果要多个操作是一个事务，需要事务手动的提交
-- 事务开始， 成功不会自动提交，失败也不会自动回滚
START TRANSACTION

UPDATE account SET balance = balance - 1000 WHERE id = 1

UPDATE account SET balance = balance - 1000 WHERE id = 2


-- 手动提交，上面都成功之后，可以提交
COMMIT

-- 手动回滚，上面有一个失败之后，回滚操作
ROLLBACK

事务的并发问题：
- 脏读（Dirty Read）：一个事务读到了另一个事务还没有提交的数据
- 不可重复读（Unrepeatable Read）: 在一个事务中，多次读同一个数据，两次读到的结果不一样，重点在修改，锁的是记录
- 幻读（Phantom Read）: 与不可重复读类似，重点在新增或删除，锁的是表
事务的隔离级别

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED √ √ √
READ COMMITTED × √ √
REPEATABLE READ （MySQL默认） × × √
SERIALIZABLE × × ×

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ （MySQL默认）	×	×	√
SERIALIZABLE	×	×	×

-- 查询隔离级别
SELECT @@transaction_isolation


-- 修改隔离级别，SESSION 表示只影响本次访问 
SET SESSION TRANSACTION ISOLATION LEVEL    READ COMMITTED

8.3 视图

视图是从一张或多张基础表或其他视图中，构建出来的虚拟表，就是动态检索数据的查询语句

真正的数据还是存在基础表中，简化了操作，对机密数据提供安全保护

-- 创建单表视图
CREATE OR REPLACE VIEW singleView  -- 创建或替换
AS 
SELECT empno, ename, job, hiredate, deptno FROM emp WHERE hiredate < '1981-09-28'
WITH CHECK OPTION  -- 【创建时，加上这条语句，代表不符合要求的无法进行插入操作】

-- 操作视图
SELECT empno, ename, job FROM singleView WHERE job = 'MANAGER'
INSERT INTO singleView VALUES(9999, 'zhangsan', 'demo', 7839, '1980-12-22', 30)  -- 底层还是插入了 emp 表，时间符合要求，可以插入



-- 创建多表视图
CREATE OR REPLACE VIEW multipleView
AS
SELECT e.empno, e.ename, e.sal, e.comm, d.deptno
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE sal > 1000



-- 创建统计视图
CREATE OR REPLACE VIEW statisticalView
AS
SELECT e.deptno, d.dname, AVG(sal) avgSal, MAX(sal) maxSal, COUNT(1) num
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
GROUP BY e.deptno



-- 创建基于视图的视图
CREATE OR REPLACE VIEW viewView
AS
SELECT * FROM statisticalView WHERE maxSal > 3000

SELECT * FROM viewView

8.4 存储过程

数据库中保存的一系列 SQL 命令的集合，Stored Procedure

-- 定义一个没有返回值的存储过程
CREATE PROCEDURE myProc(params VARCHAR(20))
BEGIN
	IF params IS NULL OR params = '' THEN
		SELECT * FROM emp;
	ELSE
		SELECT * FROM emp WHERE ename LIKE CONCAT('%', params, '%');
	END IF;
END

-- 删除存储过程
DROP PROCEDURE myProc

-- 调用存储过程
CALL myProc(NULL)
CALL myProc('AR')



-- 定义一个有返回值的存储过程
-- FOUND_ROWS() 是 MySQL 中定义的一个函数，作用是返回【查询结果条数】
CREATE PROCEDURE myProc2(IN params VARCHAR(20), OUT num INT(3))
BEGIN
	IF params IS NULL OR params = '' THEN
	  SELECT * FROM emp;
	ELSE
		SELECT * FROM emp WHERE ename LIKE CONCAT('%', params, '%');
	END IF;
	
	SELECT FOUND_ROWS() INTO num;
END

-- 调用存储过程
CALL myProc2('AR', @num)
SELECT @num

8.5 用户管理

MySQL可以创建不同的用户，赋予不同的权限，MySQL 8中提供了角色，是权限的集合，可以直接赋予用户以角色

MySQL自带的数据库 mysql 中，有多个和用户权限有关的数据库表

user 表：存储了允许连接到服务器的用户信息以及全局级（适用于所有数据库）的权限信息
db表：存储了某个用户对相关数据库的权限（数据库级权限）信息
tables_priv表：实现单张表的权限设置
columns_priv表：实现单个字段的权限设计

登录成功后，权限分配时：

先检查全局级权限表user，如果user表中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db表、tables_priv表、columns_priv表；
如果 user表中对应的权限为 N，则去 db表中查询，并作对应处理

-- 查询用户
SELECT * FROM user


-- 创建用户
-- CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'         主机名省略则为 % ，所有主机上都可以登录
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'pass123'


-- 修改用户
ALTER USER 'zhangsan'@'localhost' IDENTIFIED BY '123pass'


-- 修改安全插件，MySQL 8 中默认的是 caching_sha2_password
ALTER USER 'zhangsan'@'localhost' IDENTIFIED WITH mysql_native_password BY '123pass'


-- 删除用户
DROP USER 'zhangsan'@'%'

8.6 权限管理

权限	作用范围	作用
`all [privileges]`	`服务器`	`所有权限`
select	表、列	选择行
insert	表、列	插入行
update	表、列	更新行
delete	表	删除行
create	数据库、表、索引	创建
drop	数据库、表、视图	删除
reload	服务器	允许使用flush语句
shutdown	服务器	关闭服务
process	服务器	查看线程信息
file	服务器	文件操作
grant option	数据库、表、存储过程	授权
references	数据库、表	外键约束的父表
index	表	创建/删除索引
alter	表	修改表结构
show databases	服务器	查看数据库名称
super	服务器	超级权限
create temporary tables	表	创建临时表
lock tables	数据库	锁表
execute	存储过程	执行
replication client	服务器	允许查看主/从/二进制日志状态
replication slave	服务器	主从复制
create view	视图	创建视图
show view	视图	查看视图
create routine	存储过程	创建存储过程
alter routine	存储过程	修改/删除存储过程
create user	服务器	创建用户
event	数据库	创建/更改/删除/查看事件
trigger	表	触发器
create tablespace	服务器	创建/更改/删除表空间/日志文件
proxy	服务器	代理成为其它用户
`usage`	`服务器`	`没有权限`

-- 权限管理
	-- 查询权限
	SHOW GRANTS
	SHOW GRANTS FOR 'zhangsan'@'localhost'
	
	
	-- 分配权限
	-- GRANT 权限列表 ON 数据库名.表名 TO '用户'@'主机名'
	GRANT SELECT ON mydb.emp TO 'zhangsan'@'localhost'
	
	
	-- 撤销权限  【FROM】
	REVOKE ALL ON mydb.* FROM 'zhangsan'@'localhost'
	
	
	-- 刷新权限
	FLUSH PRIVILEGES

8.7 角色管理

MySQL 8 中新增的功能

-- 创建角色
CREATE ROLE 'role_read'@'%', 'role_write'@'localhost'


-- 给角色分配权限
GRANT SELECT ON mydb.* TO 'role_read'@'%'
GRANT UPDATE, INSERT ON mydb.* TO 'role_write'@'localhost'  -- 主机名必须和创建角色时的一样


-- 创建用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'user123'
CREATE USER 'lisi'@'%' IDENTIFIED BY 'user456'


-- 给用户分配角色
GRANT 'role_read' TO 'zhangsan'@'localhost'
GRANT 'role_write'@'localhost' TO 'zhangsan'@'localhost'


-- 激活角色!!!!!!!
SET DEFAULT ROLE ALL TO 'zhangsan'@'localhost'


-- 撤销角色
REVOKE 'role_read' FROM 'zhangsan'@'localhost'


-- 删除角色
DROP ROLE 'role_write'@'localhost'
DROP ROLE 'role_read'


-- 删除用户
DROP USER 'zhangsan'@'localhost'