SQL语言的分类
注意事项
SQL中的注释
创建逻辑库
CREATE DATABASE 逻辑库名称
展示当前所有逻辑库
SHOW DATABASES
删除逻辑库
DROP DATABASE
创建数据表
CREATE TABLE 数据表(列名1 数据类型 [约束] [COMMENT 注释],列名1 数据类型 [约束] [COMMENT 注释],......
)[COMMENT=注释];
DROP TABLE 数据表
CREATE TABLE student (id INT UNSIGNED PRIMARY KEY,name VARCHAR(20) NOT NULL,sex CHAR(1) NOT NULL,birthday DATE NOT NULL,tel CHAR(11) NOT NULL,remark VARCHAR(200)
);
数据表的其他操作
SHOW TABLES # 查看当前逻辑库里的所有数据表名
DESC 数据表名 # 查看数据表属性的具体情况
SHOW CREATE TABLE 数据表名 # 查看建立数据表时执行的SQL语句
DROP TABLE 数据表名 # 删除数据表
数据类型：数字
| 类型 | 大小 | 说明 |
|---|---|---|
| TINYINT | 1字节 | 小整数 |
| SMALLINT | 2字节 | 普通整数 |
| MEDIUMINT | 3字节 | 普通整数 |
| INT | 4字节 | 较大整数 |
| BIGINT | 8字节 | 大整数 |
| FLOAT | 4字节 | 单精度浮点数 |
| DOUBLE | 9字节 | 双精度浮点数 |
| DECIMAL | ------- | DECIMAL(10, 2) |
十进制的浮点数转成二进制会导致精度的丢失
DECIMAL为精确的小数类型
DECIMAL(10, 2)：整数部分加小数部分一共10位，精确到小数点后2位
保存重要数据的时候一定要选择DECIMAL类型
数据类型：字符串
| 类型 | 大小 | 说明 |
|---|---|---|
| CHAR | 1-255字符 | 固定长度字符串 |
| VARCHAR | 1-65535字符 | 不固定长度字符串 |
| TEXT | 1-65535字符 | 不确定长度字符串 |
| MEDIUMTEXT | 1-1千6百万字符 | 不确定长度字符串 |
| LONGTEXT | 1-42亿字符 | 不确定长度字符串 |
CHAR后面要加上(len)，表示这个字符串的长度只可以为len
VARCHAR后面加上(len)，表示这个字符串长度最大为len
数据类型：日期类型
| 类型 | 大小 | 说明 |
|---|---|---|
| DATE | 3字节 | 日期 |
| TIME | 3字节 | 时间 |
| YEAR | 1字节 | 年份 |
| DATETIME | 8字节 | 日期时间 |
| TIMESTAMP | 4字节 | 时间戳 |
日期DATE是一个特殊的字符串，一定要加上引号，中间要有-分隔
时间戳类型只能为1970-01-01之后的
DATEDIFF(date1, date2)可以计算两个日期查了多少天
NOW()可以获取当前日期
修改表结构：添加字段
ALTER TABLE 表名称
ADD 列1 数据类型 [约束] [COMMENT 注释],
ADD 列2 数据类型 [约束] [COMMENT 注释],
......;
修改表结构：修改字段类型和约束
ALTER TABLE 表名称
MODIFY 列1 数据类型 [约束] [COMMENT 注释],
MODIFY 列2 数据类型 [约束] [COMMENT 注释],
......;
修改表结构：修改字段名称
ALTER TABLE 表名称
CHANGE 列1 新列名1 数据类型 [约束] [COMMENT 注释],
CHANGE 列2 新列名2 数据类型 [约束] [COMMENT 注释],
......;
修改表结构：删除字段
ALTER TABLE 表名称
DROP 列1,
DROP 列2,
......;
修改表结构：修改表名
ALTER TABLE 表名 RENAME 新表名;
字段约束
数据库的范式
构造数据库必须遵循一定的规则，这种规则就是范式
目前关系数据库有6种范式，一般情况下，只满足第三范式即可
第一范式：原子性
第一范式是数据库的基本要求，不满足这一点就不是关系数据库
数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值，也不能存在重复的属性
第二范式：唯一性
数据表中的每条记录必须是唯一的
为了实现区分，通常要为表加上一个列用来存储唯一标识，这个唯一属性列被称作主键列
第三范式：关联性
每列都与主键有直接关系，不存在传递依赖
依照第三范式，数据可以拆分保存到不同的数据表，彼此保持关联
字段约束
| 约束名称 | 关键字 | 描述 |
|---|---|---|
| 主键约束 | PRIMARY KEY | 字段值唯一，且不能为NULL |
| 非空约束 | NOT NULL | 字段值不能为NULL |
| 唯一约束 | UNIQUE | 字段值唯一，且可以为NULL |
| 外键约束 | FOREIGN KEY | 保持关联数据的逻辑性 |
主键约束
CREATE TABLE t_teacher(id INT PRIMARY KEY AUTO_INCREMENT,......
);
主键约束要求字段的值在全表必须唯一，而且不能为NULL值
建议主键一定要使用数字类型，因为数字的检索速度会非常快
如果主键是数字类型，还可以设置自动增长(AUTO_INCREMENT)
非空约束
CREATE TABLE t_teacher(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(200) NOT NULL,married BOOLEAN NOT NULL DEFAULT FALSE
);
非空约束要求字段的值不能为NULL值
NULL值以为没有值，而不是 “” 空字符串
注意：MySQL里面是没有boolean类型的，false会变成TINYINT，FALSE映射成0，TRUE映射成1
唯一约束
CREATE TABLE t_teacher(......tel CHAR(11) NOT NULL UNIQUE
);
唯一约束要求字段值如果不为NULL，那么在全表必须唯一
外键约束
CREATE TABLE t_dept(deptno INT UNSIGNED PRIMARY KEY,dname VARCHAR(20) NOT NULL UNIQUE,tel CHAR(4) UNIQUE
);
CREATE TABLE t_emp(empno INT UNSIGNED PRIMARY KEY,ename VARCHAR(20) NOT NULL,sex ENUM("男", "女") NOT NULL,deptno INT UNSIGNED,hiredate DATE NOT NULL,FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);
外键约束用来保证关联数据的逻辑关系
外键约束的定义是写在子表上的
子表的生成依赖于父表，也就是有了父表才可以有子表
将子表的数据删除，才能删除父表的
外键约束的闭环问题：
如果形成外键闭环，我们将无法删除任何一张表的记录因此不建议使用
索引
数据排序的好处
一旦数据排序之后，查找的速度就会翻倍
数据表的索引
MySQL利用二叉树结构，对数据表的记录排序，从而加速数据的检索速度
创建索引
CREATE TABLE 表名称(......,INDEX [索引名称] (字段),......
);
写了索引名称就用自己写的，没写就使用字段名作为索引名称
添加与删除索引
数据表已经存在，添加/删除索引的方式
# 添加索引
CREATE INDEX 索引名称 ON 表名(字段);
ALTER TABLE 表名称 ADD INDEX [索引名](字段);# 查看数据表的所有索引
SHOW INDEX FROM 表名;# 删除索引
DROP INDEX 索引名称 ON 表名;
索引的使用原则
普通查询
记录查询
最基本的查询语句是由SELECT和FROM关键字组成的
SELECT * FROM t_student; # 查询所有字段
SELECT id, name, sex FROM t_student; # 只查询三个字段
SELECT语句屏蔽了物理层的操作，用户不必关心数据的真实存储，交由数据库高效的查询数据
使用列别名
通常情况下，SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列名重命名的机制
重命名只在显示输出的时候使用，数据表列名不会真的改变
SELECTename,# sal为一个月的收入，查询一年的收入# 不使用 AS 重命名就会直接显示成 sal*12sal * 12 AS "annual_salary"
FROM emp;
查询语句的子句执行顺序
数据分页
比如查看朋友圈，只会加载少量部分信息，不用一次性加载全部朋友圈
如果结果集的记录很多，则可以使用 LIMIT 关键字限定结果集数量
SELECT ...... FROM ...... LIMIT 起始位置, 偏移量;
第一条数据为起始位置0
注意是偏移量而不是结束位置
执行顺序：FROM -> SELECT -> LIMIT
SELECT empno, ename FROM t_emp LIMIT 0, 20;
# 如果LIMIT子句只有一个参数，它表示的是偏移量，起始值默认为0
SELECT empno, ename FROM t_emp LIMIT 20;
结果集排序
排序
如果没有设置，查询语句不会对结果集进行排序
如果想让结果集按照某种顺序排列，就必须使用 ORDER BY子句
SELECT ...... FROM ...... ORDER BY 列名 [ASC|DESC];
排序关键字
ASC 代表升序（默认），DESC代表降序
如果排序列是数字类型，数据库就按照数字大小排序，如果是日期类型就按照日期大小排序，如果是字符串就按照字符集序号排序
SELECT ename, sal FROM t_emp ORDER BY hiredate DESC;
排序字段内容相同的情况
默认情况下，如果两条数据排序字段内容相同，MySQL会按照主键大小来排序两条数据
多个排序字段
我们可以使用 ORDER BY 规定首要排序条件和次要排序条件
数据库会先按照首要条件排序，如果遇到排序内容相同的记录，那么就会启用次要排序条件接着排序
SELECT empno, ename, sal, hiredate
FROM t_emp
ORDER BY sal DESC, hiredate ASC;
排序+分页
ORDER BY 子句书写的时候放在 LIMIT 子句的前面
FROM -> SELECT -> ORDER BY -> LIMIT
去除重复记录
去除
如果需要去除重复数据，可以使用 DISTINCT 关键字来实现
SELECT DISTINCT 字段 FROM ......;
注意事项
使用 DISTINCT 的SELECT 子句种只能查询一列数据，如果查询多列，去除重复记录就会失效
SELECT DISTINCT job, ename FROM t_tmp; # 无效
DISTINCT 关键字只能在 SELECT 子句中使用一次
SELECT DISTINCT job, DISTINCT ename FROM t_emp; # 无效
SELECT ename, DISTINCT job FROM t_emp; # 无效
条件查询
很多时候，用户感兴趣的并不是逻辑表里的全部记录，而只是它们当中能满足某一种或某几种条件的记录，这时候需要WHERE子句来实现数据的筛选
SELECT ...... FROM ...... WHERE 条件 [AND|OR] 条件 ......;
算数运算符
| 序号 | 表达式 | 意义 | 例子 |
|---|---|---|---|
| 1 | + | 加法 | 1 + 2 + 3 |
| 2 | - | 减法 | 1 - 2 - 3 |
| 3 | * | 乘法 | 5 * 35 |
| 4 | / | 除法 | 231 / 15 |
| 5 | % | 求模 | 10 % 3 |
与null做算术运算，结果一定仍为null
如果想让含有NULL的列参与运算，可以使用
IFNULL(null, 0)函数，第一个参数为null时就会被替换成第二个参数0
比较运算符
| 序号 | 表达式 | 意义 | 例子 |
|---|---|---|---|
| 1 | > | 大于 | age > 18 |
| 2 | >= | 大于等于 | age >= 18 |
| 3 | < | 小于 | sal < 3000 |
| 4 | <= | 小于等于 | sal <= 3000 |
| 5 | = | 等于 | deptno = 10 |
| 6 | != | 不等于 | deptno != 30 |
| 7 | IN | 包含 | deptno IN (10, 30, 40) |
| 8 | IS NULL | 为空 | comm IS NULL |
| 9 | IS NOT NULL | 不为空 | comm IS NOT NULL |
| 10 | BETWEEN AND | 范围 | sal BETWEEN 2000 AND 3000 |
| 11 | LIKE | 模糊查询 | ename LIKE “A%” |
| 12 | REGEXP | 正则表达式 | ename REGEXP “[a-zA-Z]{4}” |
'%‘表示0~多个字符，’_'表示一个字符
“[a-zA-Z]{4}”：字符串长度为4且都为英文字母
“1{2, 4}$”：为2~4个字的中文
逻辑运算符
| 序号 | 表达式 | 意义 | 例子 |
|---|---|---|---|
| 1 | AND | 与关系 | age > 18 AND sex = "男“ |
| 2 | OR | 或关系 | empno = 8000 OR deptno = 20 |
| 3 | NOT | 非关系 | NOT deptno = 20 |
| 4 | XOR | 异或关系 | age >18 XOR sex = “男” |
按位运算符
二进制位运算的实质是将参与运算的两个操作数，按对应的二进制数逐位进行逻辑运算
| 序号 | 表达式 | 意义 | 例子 |
|---|---|---|---|
| 1 | & | 位与关系 | 3 & 7 |
| 2 | | | 位或关系 | 3 | 7 |
| 3 | ~ | 位取反 | ~10 |
| 4 | ^ | 位异或 | 3 ^ 7 |
| 5 | << | 左移 | 10 << 1 |
| 6 | >> | 右移 | 10 >> 1 |
WHERE 子句的注意事项
WHERE子句种，条件执行的顺序是从左到右的
所以我们应该把索引条件，或者筛选掉记录最多的条件写在最左侧
各种子句的执行顺序
FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT
INSERT语句
INSERT语句
INSERT语句可以向数据表写入记录，可以是一条记录，也可以是多条记录
INSERT INTO 表名(字段1, 字段2, ......)
VALUES(值1, 值2, ......), (值1, 值2, ......);
INSERT语句方言
MYSQL的INSERT语句还有一种方言语法
INSERT INTO 表名 SET 字段1 = 值1, 字段2 = 值2, ......;
IGNORE关键字
IGNORE关键字会让INSERT只插入数据库不存在的记录，遇到数据冲突的情况也会直接跳过，并不会报错
INSERT [IGNORE] INTO 表名(字段1, 字段2, ......)
VALUES(值1, 值2, ......);
INSERT中的子查询
INSERT语句中可以包含子查询语句，代表把子查询的内容写入到数据表
# 把人数超过5人的部门记录拷贝到新的部门表
CREATE TABLE t_dept_new LIKE t_dept;INSERT INTO t_dept_new(deptno, dname, loc)
(SELECT d.deptno, d.dname, d.loc FROM t_dept d JOIN(SELECT deptno FROM t_emp GROUP BY deptnoHAVING COUNT(*) >= 5) tempON d.deptno = temp.deptno);
REPLACE语句
REPLACE INTO语句遇到冲突会直接覆盖而不是忽略
UPDATE语句
用法
UPDATE用于修改表的记录
UPDATE [IGNORE] 表名
SET 字段1 = 值1, 字段1 = 值2, ......
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];
WHERE：限制修改范围
ORDER BY：排序后按序修改，防止修改后值重复引发主键冲突
LIMIT：取分页数据，这里LIMIT只能有一个参数
UPDATE -> WHERE -> ORDER BY -> LIMIT -> SET
练习1
# 把每个员工的编号和上司的编号+1
UPDATE t_emp SET empno = empno + 1, mgr = mgr + 1
ORDER BY empno DESC;
练习2
# 把月收入前三名的员工底薪减100元
UPDATE t_emp SET sal = sal - 100
ORDER BY sal + IFNULL(comm,0) DESC
LIMIT 3;
练习3
# 把10部门中，工龄超过20年的员工，底薪增加200元
UPDATE t_emp SET sal = sal + 200
WHERE deptno = 10;
UPDATE语句的表连接
WHERE子句中的子查询效率非常低，可以利用表连接来改造，还有一点MySQL不允许对要更改的数据表进行子查询
表连接的UPDATE语句可以修改多张表的记录
UPDATE 表1 JOIN 表2 ON 条件
SET 字段1 = 值1, 字段2 = 值2, ......;# 可以演变成如下样子，也可以实现内连接
UPDATE 表1, 表2
SET 字段1 = 值1, 字段2 = 值2, ......
WHERE 连接条件;
UPDATE语句的表既可以是内连接，又可以是外连接
UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 条件
SET 字段1 = 值1, 字段2 = 值2, ......;
练习4
join可以不书写ON后面的条件，直接在两个表里找符合WHERE子句的进行结合
# 把ALLEN调往RESEARCH部门，职务调整为ANALYST
UPDATE t_emp e JOIN t_dept d
SET e.deptno = d.deptno, e.job = "ANALYST"
ame = "ALLEN" AND d.dname = "RESEARCH";
练习5
# 把底薪低于公司平均底薪的员工，底薪增加150元
UPDATE t_emp e JOIN
(SELECT AVG(sal) AS avg FROM t_emp) t
ON e.sal < t.avg
SET e.sal = e.sal + 150;
练习6
# 把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
SET e.deptno = 20
WHERE e.deptno IS NULL OR (d.dname = "SALES" AND e.sal < 2000);
DELETE子句
用法
DELETE用于删除记录
DELETE [IGNORE] FROM 表名
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];
WHERE：限定删除范围，不写就全表删除数据
ORDER BY：排序后删除
LIMIT：结合ORDER Y，删除最值记录
练习1
# 删除10部门中，工龄超过20年的员工记录
DELETE FROM t_emp
WHERE deptno = 10 AND DATEDIFF(NOW(),hiredate) / 365 >= 20;
练习2
# 删除20部门中工资最高的员工记录
DELETE FROM t_emp
WHERE deptno = 20
ORDER BY sal + IFNULL(comm,0)
LIMIT 1;
DELETE语句的表连接
相关子查询的效率非常低，可以用表连接的方式改造DELETE语句，还有一点MySQL不允许对要更改的数据表进行子查询
DELETE 表1, ...... FROM 表1 JOIN 表名2 ON 条件
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];
要删除哪个表的内容，就把表名写在DELETE后面
DELETE语句的表连接既可以是内连接，又可以是外连接
DELETE 表1, ...... FROM 表1 [LEFT|RIGHT] JOIN 表2
ON 条件 ......;
练习3
# 删除SALES部门和该部门的全部员工记录
DELETE e, d
FROM t_emp e JOIN d_dept d ON e.deptno = d.deptno
WHERE d.dname = "SALES";
# 这样写会同时删除两张表的相关记录
练习4
# 删除每个低于部门平均底薪的员工记录
DELETE e
FROM t_emp e JOIN
(SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal < t.sal;
练习5
# 删除员工KING和他的直接下属员工记录，用表连接实现
DELETE e
FROM t_emp e JOIN
(SELECT empno FROM t_emp WHERE ename = "KING") t
= t.empno pno = t.empno;
练习6
# 删除SALES部门的员工，以及没有部门的员工
DELETE e
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE d.dname = "SALES" OR e.deptno IS NULL;
快速删除数据表全部记录
DELETE语句是在事务机制下删除记录，删除记录前，先把将要删除的记录保存到日志文件里，然后再删除记录
TRUNCATE语句在事务机制之外删除记录
TRUNCATE TABLE 表名;
数字函数
| 函数 | 功能 | 用例 |
|---|---|---|
| ABS | 绝对值 | ABS(-100)=100 |
| ROUND | 四舍五入 | ROUND(4.62)=5 |
| FLOOR | 强制舍位到最近的整数 | FLOOR(9.9)=9 |
| CEIL | 强制进位到最近的整数 | CEIL(3.2)=4 |
| POWER | 幂函数 | POWER(2, 3)=8 |
| LOG | 对数函数 | LOG(7, 3)= log 7 3 log_73 log73 |
| LN | 对数函数 | LN(10)= ln 10 ln10 ln10 |
| SQRT | 开平方 | SQRT(9)= 9 sqrt{9} 9 =3 |
| PI | 圆周率 | PI() |
| SIN | 三角函数 | SIN(1) |
| COS | 三角函数 | COS(RADIANS(30)) |
| TAN | 三角函数 | TAN(1) |
| COT | 三角函数 | TAN(1) |
| RADIANS | 角度转换弧度 | RADIANS(30) |
| DEGREES | 弧度转换角度 | DEGREES(1) |
三角函数均为弧度制，需要使用角度值可以加上RADIANS函数
时间函数
获取系统时间
日期格式化函数：DATE_FORMAT()
DATE_FORMAT(日期, 表达式)
SELECT ename, DATE_FORMAT(hiredate, "%Y")
FROM t_emp;
| 占位符 | 作用 | 占位符 | 作用 |
|---|---|---|---|
| %Y | 年份 | %m | 月份 |
| %d | 日期 | %w | 星期(数字) |
| %W | 星期(名称) | %j | 本年第几天 |
| %U | 本年第几周 | %H | 小时(24) |
| %h | 小时(12) | %i | 分钟 |
| %s | 秒 | %r | 时间(12) |
| %T | 时间(24) |
练习1
# 查询明年你的生日是星期几
SELECT DATE_FORMAT("2023-01-30", "%W");
练习2
# 查询1981年上半年入职的员工有多少人
SELECT COUNT(*) FROM t_emp
WHERE DATE_FORMAT(hiredate, "%Y") = 1981
AND DATE_FORMAT(hiredate, "%m") <= 6;
注意事项
MySQL数据库里，两个日期不能直接加减，日期越能与数字加减
日期偏移计算
DATE_ADD()函数可以实现日期的偏移计算，而且时间单位很灵活
DATE_ADD(日期, INTERVAL 偏移量 时间单位)SELECT DATE_ADD(NOW(), INTERVAL 15 DAY);
SELECT DATE_ADD(NOW(), INTERVAL -300 MINUTE);SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL -6 MONTH), INTERVAL -3 DAY);
计算日期之间的相隔天数
DATEDIFF()函数用来计算两个日期之间相差的天数
DATEDIFF(日期, 日期)
字符函数
| 函数 | 功能 | 用例 |
|---|---|---|
| LOWER | 转换小写字符 | LOWER(ename) |
| UPPER | 转换大写字符 | UPPER(ename) |
| LENGTH | 字符数量 | LENGTH(ename) |
| CONCAT | 连接字符串 | CONCAT(sal, “$”) |
| INSTR | 字符出现的位置 | INSTR(ename, “A”) |
| INSERT | 插入/替换字符 | INSERT(“你好”, 1, 0, “先生”) |
| REPLACE | 替换字符 | REPLACE(“你好先生”, “先生”, “女士”) |
| SUBSTR | 截取字符串 | SUBSTR(“你好世界”, 3, 4) |
| SUBSTRING | 截取字符串 | SUBSTRING(“你好世界”, 3, 2) |
| LPAD | 左侧填充字符 | LPAD(“Hello”, 10, “*”) |
| RPAD | 右侧填充字符 | RPAD(“Hello”, 10, “*”) |
| TRIM | 去除首尾空格 | TRIM(" 你好先生") |
INSERT
1：第一个字符
0：不替换在第一个字符前插入，若为1则表示从第一个字符开始替换
SUBSTR与SUBSTRING
SUBSTR后两个参数为：截取的起始位置, 截取的结束位置
SUBSTRING后两个参数为：截取起始位置, 截取字符个数
LPAD与RPAD
第二个参数为填充后字符串的总长度
注意
utf-8编码下一个汉字占3个字节
条件函数
条件函数
IFNULL(表达式, 值)：表达式如果为NULL则替换成第二个参数IF(表达式, 值1, 值2)：表达式为真返回值1，为假返回值2练习1
# 中秋节公司发放礼品，SALES部门发放礼品A，其余部门发放礼品B，打印每名员工获得的礼品
pno, e.ename, d.dname,IF(d.dname = "SALES", "礼品A", "礼品B")
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;
条件语句
复杂的条件判断可以用条件语句来实现，比IF语句功能更强大
CASEWHEN 表达式 THEN 值1,WHEN 表达式 THEN 值2,......,ELSE 值N
END
练习2
/*
公司年庆决定组指员工集体旅游，每个部门旅游的目的地是不同的
SALES部门去P1地点，ACCOUNTING部门去P2地点
RESEARCH部门去P3地点，查询每名员工的旅行地点
*/
pno, e.ename,CASEWHEN d.dname = "SALES" THEN "p1"WHEN d.dname = "ACCOUNTING" THEN "p2"WHEN d.dname = "RESEARCH" THEN "p3"END AS place
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;
练习3
| 序号 | 条件 | 涨幅 |
|---|---|---|
| 1 | SALES部门中工龄超过20年 | 10% |
| 2 | SALES部门中工龄不满20年 | 5% |
| 3 | ACCOUNTING部门 | +300元 |
| 4 | RESEARCH部门里低于部门平均底薪 | +200元 |
| 5 | 没有部门的员工 | +100元 |
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
LEFT JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno
SET e.sal = (CASEWHEN d.dname = "SALES" AND DATEDIFF(NOW(),hiredate) / 365 >= 20THEN e.sal * 1.1WHEN d.dname = "SALES" AND DATEDIFF(NOW(),hiredate) / 365 < 20THEN e.sal * 1.05WHEN d.dname = "ACCOUNTING"THEN e.sal + 300WHEN d.dname = "RESEARCH" AND e.sal < t.avgTHEN e.sal + 200WHEN e.deptno IS NULLTHEN e.sal + 100ELSE e.salEND
);
聚合函数
聚合函数可以对数据求和、求最大值和最小值、求平均值等等
聚合函数永远不要出现在WHERE子句中，因为WHERE是用来划定范围的，然后聚合函数在该范围内进行统计
SUM 函数
用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加，SUM函数求和会排除NULL值
SELECT SUM(sal) FROM t_emp
WHERE deptno IN (10, 20);
MAX 函数
用于获得非空值的最大值
# 查询10和20部门中，月收入最高的员工
SELECT MAX(sal + IFNULL(comm,0)) FROM t_emp
WHERE deptno IN (10, 20);
MIN 函数
用于获得非空值的最小值
SELECT MIN(empno) FROM t_emp;
AVG 函数
获得非空值得平均值，非数字数据统计结果为0
# 求员工的平均月收入
SELECT AVG(sal + IFNULL(comm,0)) FROM t_emp
COUNT 函数
COUNT(*) 用于获得包含空值的记录数
COUNT(列名) 用于获得该属性列包含非空值的记录数
# 查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数
SELECT COUNT(*) FROM t_emp
WHERE deptno IN (10, 20) AND SAL >= 2000
AND DATEDIFF(NOW(), hiredate) / 365 >= 15;
分组查询
为什么要分组
默认情况下汇总函数是对全表范围内的数据做统计
GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理
逐级分组
数据库支持多列分组条件，执行的时候逐级分组
# 查询每个部门里，每种职位的人员数量和平均底薪
SELECT deptno, job, COUNT(*), AVG(sal)
FROM t_emp GROUP BY deptno, job
ORDER BY deptno;
对SELECT子句的要求
查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中
对分组结果再次做汇总计算
SELECTdeptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP;
WITH ROLLUP会对分组计算之后的结果进行汇总
GROUP_CONCAT函数
GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串，即把非分组字段合并成一条字段
# 查询每个部门内底薪超过2000元的人数和员工姓名
SELECT deptno, GROUP_CONCAT(ename), COUNT(*)
FROM t_emp WHERE sal >= 2000
GROUP BY deptno;
各种子句的执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
HAVING子句
分组查询遇到的困难
因为WHERE子句先于GROUP BY执行，一旦WHERE子句中出现了聚合函数，数据库根本不知道按照什么范围计算，HAVING子句与GROUP BY子句联合使用
HAVING子句的用途
# 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno FROM t_emp
WHERE hiredate >= "1982-01-01"
GROUP BY deptno HAVING COUNT(*) >= 2
ORDER BY deptno ASC;
HAVING子句里面可以用聚合函数跟具体的数据比较，但是不可以跟某个字段进行比较，这就需要使用表连接
HAVING子句的特殊用法
按照数字1分组，即按照SELECT子句中的第一个字段进行分组，MySQL会依据SELECT子句中的列进行分组，HAVING子句也可以正常使用
# 查询10和20部门的总人数
SELECT deptno, COUNT(*) FROM t_emp
WHERE deptno IN (10, 20)
GROUP BY 1;
表连接查询
从多张表中提取数据
必须指定关联的条件，如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积
规定了连接条件的表连接语句，就不会出现笛卡尔积
pno, e.ename, d.dname
# FROM子句的优先级最高，故可以在FROM子句里给表取别名，其它子句中都可以使用
FROM t_emp e JOIN t_dept d
ON e.deptno = d.deptno #需指定表名
表连接的分类
内连接
结果集中只保留附合连接条件的记录
外连接
不管符不符合连接条件，记录都要保留在结果集中
内连接
最常见的一种表连接，用于多张关系表符合连接条件的记录
SELECT ...... FROM 表1
[INNER] JOIN 表2 ON 连接条件
[INNER] JOIN 表3 ON 连接条件
# 语法1
SELECT ...... FROM 表1 JOIN 表2 ON 连接条件;
# 语法2
SELECT ...... FROM 表1 JOIN 表2 WHERE 连接条件;
# 语法3
SELECT ...... FROM 表1, 表2 WHERE 连接条件;
练习1
内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以
# 查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级
pno, e.ename, d.deptno, e.sale, e.job, s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
练习2
相同的数据表可以做表连接
# 查询与SCOTT相同部门的员工都有谁
ame
FROM t_emp e1 JOIN t_emp e2
ON e1.deptno = e2.deptno
WHERE e1.name = "SCOTT" AND e2.name != "SCOTT";# 子查询写法（不推荐，WHERE会被反复执行，速度很慢）
SELECT ename
FROM t_emp
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename = "SCOTT")AND ename != "SCOTT";
练习3
结果集也可以作为一张“表”来跟其他表连接
ON子句同样也不可以出现聚合函数
# 查询月薪超过公司平均月薪的员工信息
SELECTe1.name
FROMt_emp e1 JOIN# 将AVG查询的结果作为一张表和t_emp作连接(SELECT AVG(sal) avg FROM t_emp) e2
on e1.sal > e2.avg;
练习4
FLOOR()：下取整
CEIL()：上取整
ROUND()：四舍五入
# 查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄
SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(e.sal),
FLOOR(AVG(DATEDIFF(NOW(), e.hiredate) / 365))
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno
WHERE d.dname = "RESEARCH";
练习4
# 查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级
SELECT
e.job,
MAX(e.sal + IFNULL(em,0)),
MIN(e.sal + IFNULL(em,0)),
AVG(e.sal + IFNULL(em,0)),
ade),
ade)
FROM t_emp e
JOIN t_salgrade s ON (e.sal + IFNULL(em,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;
练习5
# 查询每个底薪超过部门平均底薪的员工信息
pno, e.ename, e.sal
FROM t_emp e JOIN
(SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal >= t.avg;
外连接
字段列出现空值，但又不能丢掉这条数据
外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录
左连接和右连接
左外连接就是保留左表所有的记录，与右表做连接，如果右表有符合条件的记录就与左表连接，如果右表没有符合条件的记录，就用NULL与左表连接，右外连接也是如此
练习1
# 查询每个部门的名称和部门的人数
# 有一个部门没有人，因此d表是有这个部门的，而e表中没有，COUNT需要忽略这个NULL值
SELECT d.dname, COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno;
练习2
UNION关键字可以将多个查询语句的结果集进行合并
(查询语句) UNION (查询语句) UNION (查询语句) ......
# 查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替
(SELECT d.dname, COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno
) UNION
(SELECT d.dname, COUNT(*) # NULL也要计算上
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno)
练习3
# 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门
pno, e.ename, d.dname,e.sal + IFNULL(em,0), s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate) / 365),t.empno AS mgrno, t.ename AS mname, t.dname AS mdname
FROM t_emp e LEFT JOIN t_dept d on e.deptno = d.deptno
LEFT JOIN t_salgrade s on e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(pno, e1.ename, d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno = d1.deptno
) t = t.empno
外连接的注意事项
内连接只保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的
但是在外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来
# 查询10部门人员的信息
pno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno = d.deptno
WHERE e.deptno = 10; # 这里就不能使用ON
子查询
子查询是一种查询中嵌套查询的语句
# 查询底薪超过公司平均底薪的员工信息 SELECTempno, ename, sal FROM t_emp WHERE sal >= (SELECT AVG(sal) FROM t_emp);
FROM子查询（最推荐）
这种子查询只会执行一次，所以查询效率很高
pno, e.ename, e.sal, t.avg
FROM t_emp e JOIN(SELECT deptno, AVG(sal) AS avgFROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal >= t.avg;
WHERE子查询
这种子查询最简单，最容易理解，但是确实效率很低的子查询
SELECTempno, ename, sal
FROM t_emp
WHERE sal >= (SELECT AVG(sal) FROM t_emp);
# 比较每条记录都要重新执行子查询
用表连接代替WHERE子查询
表连接的优点是子查询只执行一次，查询效率特别高
pno, e2.ename
FROM t_emp e1 JOIN t_emp e2
ON e1.deptno = e2.deptno
ame != "FORD"
ame != "FORD";
SELECT子查询
这种子查询每输出一条记录的时候都要执行一次，查询效率很低
pno, e.ename, (SELECT dname FROM t_dept WHERE deptno = e.deptno)
FROM t_emp e;
相关子查询
查询语句执行的时候要多次的依赖于子查询的结果，这类子查询被称作相关子查询
WHERE子查询和SELECT子查询都属于相关子查询，FROM为非相关子查询
因为相关子查询要反复多次执行，所以应该避免使用
单行子查询和多行子查询
WHERE子句中的多行子查询
WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断
# 查询比FORD和MARTIN底薪都高的员工信息
SELECT ename FROM t_emp
WHERE sal > ALL
(SELECT sal FROM t_empWHERE ename IN ("FORD", "MARTIN"));
EXISTS关键字
EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询的里面
SELECT ...... FROM 表名 WHERE [NOT] EXISTS (子查询);
# 查询工资等级3和4级的员工信息
SELECTempno, ename, sal, comm
FROM t_emp
WHERE EXITS
# 子查询返回的字段并不重要，只要不为空，则说明条件是满足的
(SELECT * FROM t_salgrade WHERE grade IN (3, 4)AND sal BETWEEN losal AND hisal);
事务机制（Transaction）
避免写入直接操作数据文件
如果数据的写入直接操作数据文件是非常危险的事
利用日志来实现间接写入
MySQL总共有5种日志，其中只要redo日志和undo日志与事务有关
事务机制
RDBMS = SQL语句 + 事务（ACID）
事务是一个或者多个SQL语句组成的整体，要么全部执行成功，要么全都执行失败
事务案例
把10部门中MANGER员工调往20部门，其他岗位的员工调往30部门，然后删除10部门
管理事务
默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务
为了让多条SQL语句纳入到一个事务之下，可以手动管理事务
START TRANSACTION;
SQL语句
[COMMIT|ROLLBACK];
事务的ACID属性
事务的原子性
一个事务的中的所有操作要么全部完成，要么全部失败
事务执行后，不允许停留在中间某个状态
事务的一致性
不管在任何给定的时间、并发事务有多少，事务必须保证运行结果的一致性
事务可以并发执行，但是最终MySQL却串行执行
事务的隔离性
隔离性要求事务不受其他并发事务的影响，如同在给定的时间内，该事务是数据库唯一运行的事务
undo、redo都会标记上它是属于哪一个事务的
事务的持久性
事务一旦提交，结果便是永久性的
即便发生宕机，仍然可以依靠事务日志完成数据的持久化
事务的四个隔离级别
| 序号 | 隔离级别 | 功能 |
|---|---|---|
| 1 | read uncommitted | 读取未提交数据 |
| 2 | read committed | 读取已提交数据 |
| 3 | repeatable read | 重复读取 |
| 4 | serializable | 序列化 |
修改事务的隔离级别
read uncommitted
火车购票案例：事务A先修改了火车票的状态但没提交，事务B随后也修改了火车票的状态，但提前于事务A提交，该票本该属于事务A的，这是事务B就需要读取事务A未提交的数据
READ UNCOMMITTED代表可以读取其他事务未提交的数据
SET SESSION TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED;
read committed
银行账户案例：事务A向账户转账1000，事务B支出100，如果事务A读取到事务B要支出100但还没提交，只向账户中转入900，但最后事务B回滚，此时事务A应该只能读取事务B已经提交的数据
READ COMMITTED代表只能读取其他事务提交的数据
SET SESSION TRANSACTION ISOLATION LEVEL
READ COMMITTED;
repeatable read（MySQL默认隔离级别）
商品购买案例：事务A下单了一件商品，事务B修改了商品价格，这两个事务彼此应该是相互独立地，只能读取事务开始之前的数据，事务开始之后其它事务提交的数据是读取不到的，也就是只读取undo日志里属于自己的数据
REPEATABLE READ代表事务在执行中只在当前事务中反复读取数据，得到的结果是一致的，不会受其他事务影响
因为涉及到undo日志，所以每个事务需要先执行SELECT子句，只有执行了SELECT子句后，才会将数据载入到undo日志中，因此需要先执行一次SELECT子句
SET SESSION TRANSACTION ISOLATION LEVEL
REPEATABLE READ;
serializable（影响并发，很少使用）
由于事务并发执行所带来的各种问题，前三种隔离级别只适用在某些业务场景中，但是序列化的隔离性，让事务逐一执行，就不会产生上述问题了
上一个事务必须结束，下一个事务才能输出结果并提交
SET SESSION TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
数据导入导出
数据导出与备份的区别
数据导出，导出的纯粹是业务数据
数据备份，备份的是数据文件、日志文件、索引文件等等
全量备份 -> 增量备份1 -> 增量备份2
数据导出的分类
导出SQL文件
mysqldump用来把业务数据导出成SQL文件，其中也包括了表结构
命令行方式
需要输入MySQL的密码
路径文件名最后要以 .sql 结尾
mysqldump -uroot -p [no-data] 逻辑库 > 路径
图形界面
双击展开逻辑库 -> 右击选择转储SQL文件 -> 选择
如果业务数据非常多，建议只导出表结构，即带上no-data，然后用SELECT INTO OUTFILE把数据导出成文本文档
导入SQL文件
source命令用于导入SQL文件，包括创建数据表，写入记录等
# 登录MySQL数据库
mysql -uroot -p
# 导入
USE 逻辑库名;
SOURCE 路径;
导出文本文档
导入文本文档
\u4e00-\u9fa5 ↩︎
本文发布于:2024-02-02 11:39:55，感谢您对本站的认可！
本文链接：https://www.4u4v.net/it/170684519443555.html
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
| 留言与评论（共有 0 条评论） |
