【MySQL】笔记
MySQL
1 基本知识
常见名词
1、DB Database
2、DBMS Database Management System e.g MySQL Oracle DB2(IBM) SQLServer(Microsoft)
3、SQL Stucture Query Language
如何存储
1、数据放在表中，表放在库中
2、一个库可以有多个表。表名有唯一性
3、表有一些特性，决定了数据如何在表中存储
4、数据按行存储
DBMS分为两类
1、基于共享文件系统（Access）
2、基于客户机/服务器（MySQL,Oravle,sqlserver）
卸载
1、控制面板中卸载
2、安装目录下删除
3、C盘中的ProgramData（隐藏文件）中删除MySQL
2 安装
我安装的是mysql-community-5.7.36.0；只安装了mysql server(x64)
配置：
Config type：Development Computer(开发；后面的是服务器和专用服务器，占用内存大)
TCP/IP port:3306 openfirewall port for network access框选
password:7322
Windows service name:MySQL57 取消了开机自启
my.ini文件 C:\ProgramData\MySQL\MySQL Server 5.7（隐藏目录）
服务端信息[mysqld]
character-set-server=utf8 字符集
启动和停止
- 方法1：计算机管理->服务->MySQL57 右键启动
- 方法2：以管理员身份运行cmd->net start mysql57（启动)
net stop mysql57（停止）
服务端的登录和退出
方法1：打开mysql command line client（mysql57服务需启动） 只适用root用户 exit（退出）
方法2：管理员cmd-
mysql [-h localhost -P 3306] -u root -p（h host, P port u user)
exit 退出
如果是连接本机，可以简写
mysql -u root -p
配置环境变量
将MySQL命令加入系统PATH
C:\Program Files\MySQL\MySQL Server 5.7\bin
3 基础命令
常见命令
show databases; 显示数据库
use DATABASENAME; 打开指定的库
show tables [from DATABASENAME]; 显示表
select database(); 显示当前所在的数据库
create table TABLENAME（ id int, name varchar(20),列名,列类型); 创建一个表
desc TABLENAME; 查看表的结构
select * from TABLENAME; 查看表的内容
insert into TABLENAME (id,name) value(1,'john'); 插入一个数据
update TABLENAME set name='marry' where id=1; 修改数据
delete from TABLENAME where id=1; 删除数据
查看MySQL服务端版本
Method1: mysql>select version();
Method2: cmd>mysql --version
cmd>mysql -V
语法规范
1、不区分大小写，建议关键字大写，表名、列名小写
2、每条命令分号结尾
3、每条命令根据需要，可以缩进，换行
4、注释
单行注释 #注释文字
单行注释 -- 注释文字
多行注释 /* 注释文字 */
5、字符型和日期型常量值必须用''括起来。如果字符之间有空格，用""
图形化用户界面的安装
sqlyog D:\installation\conquer\SQLyog
新建一个连接mysql57-
DQL语言(data query language)
导入myemployees库：root@localhost右键->执行sql脚本->刷新
commission_pct 奖金率
基础查询
select 查询列表 from 表名;
# 特点：
# 查询列表可以是： 表中的字段、常量值、表达式、函数
# 查询的结果是一个虚拟表格
#1.查询表中单个字段
SELECT last_name FROM employees;
#2. 查询表中的多个字段
SELECT last_name,salary,email FROM employees;
#3. 查询表中的所有字段
SELECT * FROM employees; # 按照表中顺序的字段，不能灵活显示
#4. 查询常量值
SELECT 100;
SELECT 'john';
#5. 查询表达式
SELECT 100*98;
#6. 查询函数
SELECT VERSION();
#7. 起别名
/*
1便于理解
2如果要查询的字段有重名情况，使用别名可以区分开
*/
# method 1 as
SELECT 100*98 AS result;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
# method 2 space
SELECT last_name 姓,first_name 名 FROM employees;
# 案例：查询salary，显示结果为out put
SELECT salary AS "out put" FROM employees;
#8. 去重
# 案例：查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
#9. +号作用
/*
java中的+号：
运算符，两个操作数都为数值型
连接符，只要有一个操作数为字符串
mysql中的+号：
运算符
select 100+90; 两个操作数都是数值型，做加法运算
select 100+'90'; 如果一个为字符型，试图将字符型转换成数值型，
转换成功，做加法运算；
转换失败，将字符型转换成0
select null+10; 只要其中一方为null，结果肯定是null
*/
#10. concat实现连接
# 案例：查询员工名和姓连接成一个字段，显示为 明敏·姓名
SELECT CONCAT( last_name, first_name )AS 姓名 FROM employees;
#11. 显示表的结构
DESC employees;
#12. ifnull 替换Null
# 如果为null，改为0
SELECT IFNULL(commission_pct,0) AS result FROM employees;
#13. isnull 判断Null
# 如果为null，返回1，否则返回0
建议每次操作前进入数据库
USE myemployees;
当字段名和关键字冲突，可以加着重号表示是字段``
格式化：选中命令->F12
条件查询
select 查询列表 from 表名 where 筛选条件
分类：
1.按条件表达式筛选 条件运算符： > < = != <>（不等于) <= >=
2.按逻辑表达式筛选 逻辑运算符： && || ! and or not
3.模糊查询 like, between and, in, is null
#1. 按条件表达式
# 案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
# 案例2：查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;
#2. 按逻辑表达式
#案例1：查询工资在10000到20000之间的员工们、工资和奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000 AND salary <= 20000 ;
#案例2：查询部门编号不在90-110之间，或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE department_id < 90 OR department_id > 110 OR salary > 15000 ;
#3. 模糊查询
# like
/*
特点：
1 一般和通配符搭配使用
通配符：
% 任意多个字符，包括0个字符
_ 任意单个字符
2 可以判断字符型或数值型
*/
# 案例1：查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
# 案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%';
# 案例3：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%'; #\表示转义
SELECT last_name FROM employees WHERE last_name LIKE '_a_%' ESCAPE 'a'; #escape 表示转义
# between and
/*
提高语句的简洁度
[a,b]
两个临界值不要调换顺序
*/
# 案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 100;
# in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
更简洁
In列表的值类型必须一致或兼容
不能使用通配符
*/
# 案例1：查询员工的工种编号 IT_PROG、AD_VP、AS_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROT','AD_VP','AD_PRES');
# is null, is not null
/*
=或<>不能用于判断Null值
is null或is not null 可以判断null值
*/
# 案例1：查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;
# 案例2：查询有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
# 安全等于 <=>
# 可读性低，不建议使用
# 案例1：查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
# 案例2：查询工资为12000的员工信息
SELECT last_name, salary FROM employees WHERE salary <=> 12000;
排序查询
select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc]
/*
默认升序
order by子句中支持单个字段、多个字段、表达式、函数、别名
order by子句一般放在查询语句的最后面，limit子句除外
*/
# 案例1：查询员工信息，工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary;
# 案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT * FROM employees
WHERE department_id>=90
ORDER BY hiredate;
# 案例3：查询员工信息，按年薪高低显示【按表达式排序】
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0));
# 案例4：查询员工信息，按年薪高低显示【按别名排序】
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪;
# 案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY 字节长度 DESC;
# 案例5：查询员工信息，要求先按工资排序，再按员工编号排序【多个字段排序】
SELECT *
FROM employees
ORDER BY salary, employee_id DESC;
常见函数
/*
调用：
select 函数名(实参列表) [from 表];
分类：
1、单行函数
2、分组函数（组函数，复合函数，统计函数） 做统计使用
*/
单行函数
# 一、字符函数
#1. length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha'); # utf8下，一个汉字3字符
SHOW VARIABLES LIKE '%char%'; # 查看字符集
#2. concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) FROM employees;
#3. upper,lower
SELECT UPPER('john');
SELECT LOWER('john');
# 案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
#4. substr,substring
# 索引从1开始
# 截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
# 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(SUBSTR(last_name,1,1),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
#5. instr 返回字串第一次出现的索引，如果找不到，返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
#6. trim 从两边开始截
SELECT TRIM(' 张翠山 ') AS out_put;
SELECT TRIM('a' FROM 'aaa张aaaaaaaaa翠山aaaaaaaa') AS out_put;
#7. lpad 用指定的字符实现左填充至指定长度。如果指定长度小于初始字符，就会截断
SELECT LPAD('殷素素',10,'*') AS out_put;
#8. rpad 用指定的字符实现右填充至指定长度。如果指定长度小于初始字符，就会截断
SELECT RPAD('殷素素',10,'*') AS out_put;
#9. replace 替换
SELECT REPLACE('张无忌爱上了周芷若周芷若我不爱周芷若','周芷若','赵敏') AS out_put;
# 二、数学函数
#1. round 四舍五入
SELECT ROUND(1.65);
SELECT ROUND(-1.457,2); # 小数点后保留指定位数
#2. ceil 向上取整 返回>=该参数的最小整数
SELECT CEIL(1.01);
#3. floor 向下取整 返回<=该参数的最小整数
SELECT FLOOR(0.99);
#4. truncate 截断 小数点后保留指定位置，直接截断
SELECT TRUNCATE(1.65,1);
#5. mod 取余 a-a/b*b
SELECT MOD(-10,3);
SELECT 10%3;
#6. rand 获取随机数，返回0-1之间的小数
# 三、日期函数
#1. now 返回当前系统日期+时间
SELECT NOW();
#2. curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#3.curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
# str_to_date 将字符通过指定的格式转换成日期
# Y四位年份 y两位年份 m两位月份 c月份1，11 d两位日 H小时24 h小时12 i分钟 s秒
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
# 查询入职日期为1992-4-3的员工信息
SELECT * FROM employees
WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate= STR_TO_DATE('4-3 1992','%c-%d %Y');
# date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
# 查询有奖金的员工名和入职日期
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees WHERE commission_pct IS NOT NULL;
#查询两个日期之间的相差天数
SELECT DATEDIFF(a,b); # (a,b]
# 四、其他函数
SELECT VERSION(); # 数据库服务器版本
SELECT DATABASE(); # 当前打开的数据库
SELECT USER(); # 当前用户
SELECT PASSWORD('叶知秋'); # 返回该字符的密码形式
SELECT MD5('叶知秋'); # 返回该字符的md5加密格式
# 五、流程控制函数
#1. if函数：? : 的效果
SELECT IF('10>5','big','small');
#2. case函数
#使用一：switch case 的效果 判断等值
#案例：查询员工的工资，要求 部门号=30，显示工资为1.1倍 40，1.2；50，1.3；其他，原始
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
#使用二：类似于多重if 判断区间
/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
#案例：查询员工的工资 工资>20000，A;>15000,B;>10000,C;其他,D
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
遇到一个1064的错误，查了半天，结果是上一句话没有加分号。
分组函数
/*
sum 求和 avg 平均值 max min count 计算不为Null个数
*/
#1. 简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均 FROM employees;
#2. 参数支持那些类型
/*
1.sum,avg一般用于处理数值类型
max,min,count可以处理任何类型
2.是否忽略Null 都忽略Null
3.可以和distinct搭配实现去重
4. 一般使用COUNT(*)用作统计行数
5.和分组函数一同查询的字段要求是group by后的字段
*/
SELECT SUM(last_name) FROM employees;
#3. 是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35 FROM employees;
#4. 搭配distinct
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#5. count详细介绍
/*效率：
INNODB存储引擎下，COUNT(*)和COUNT(1)效率差不多，比COUNT(字段)高一些
MYISAM存储引擎下，COUNT(*)效率最高
*/
SELECT COUNT(salary) FROM employees; # 统计该字段非空值的个数
SELECT COUNT(*) FROM employees; #计算行数
SELECT COUNT(1) FROM employees; #计算行数
#6、和分组函数一同查询的字段有限制
分组查询
/*
语法
select 分组函数,列(要求出现在group by后面)
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]
注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
特点
1、分组查询的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果表 group by子句的后面 having
分组函数做条件肯定是放在having子句中
能用分组前筛选的，有点使用where
2、支持单个字段分组，多个字段分组（没有顺序要求，用逗号隔开），表达式或函数
3、也可以添加排序，排序放在整个分组的最后
*/
#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加筛选条件 分组前的筛选
#案例3：查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例4：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加复杂的筛选条件 分组后的筛选
#案例5：查询那个部门的员工个数>2
#先查询每个部门的员工个数；筛选查询
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例6：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
# 按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*) n,LENGTH(last_name) 长度
FROM employees
GROUP BY 长度
HAVING n>5;
# 按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
# 添加排序
#案例：查询每个部门每个工种的员工的平均工资，从高到低显示
SELECT AVG(salary) a,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING a>10000
ORDER BY a DESC;
连接查询（多表查询）
- 应用：当查询的字段来自于多个表时。
- 笛卡尔乘积现象：表一m行，表二n行，结果有m*n行
- 有效的连接条件
- 分类：
- 按年代分类：
- sql92标准 仅支持内连接，一部分外连接(oracle、sqlserver,mysql不支持)
- sql99标准【推荐】（mysql不支持全外连接）
- 按功能分类：
- 内连接：等值连接；非等值连接；自连接
- 外连接：左外连接；右外连接；全外连接
- 交叉连接
- 按年代分类：
#一、sql92标准
#1. 等值连接
/*
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序无所谓
一般需要为表起别名
可以搭配排序分组筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.`boyfriend_id`=boys.`id`;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#1) 为表起别名
# 提高语句简洁度；区分多个重名的字段
#注意：如果为表起了别名，拆线呢字段就不能使用原表名
#案例：查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs
WHERE e.`job_id`=jobs.`job_id`;
#2) 可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM locations l, departments d
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
#3) 可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT MIN(salary),d.manager_id,department_name
FROM departments d, employees e
WHERE e.`commission_pct` IS NOT NULL
AND e.`department_id`=d.`department_id`
GROUP BY department_name;
#4) 加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工的个数排序
SELECT COUNT(*) c, j.job_title
FROM employees e, jobs j
WHERE j.`job_id`=e.`job_id`
GROUP BY j.job_title
ORDER BY c DESC;
#5) 三个表连接
#案例：查询员工名，部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
#2. 非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3. 自连接
#案例：查询员工名和上级名
SELECT e.employee_id,e.last_name,m.manager_id,m.`last_name`
FROM employees e,employees m # 一张表既当成领导表，也当成员工表
WHERE e.`employee_id`=m.`manager_id`;
#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by】
【having】
【order by】
分类：
内连接：inner
外连接：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：cross
*/
#一）内连接
/*
语法：
select 查询列表
from 表1 别名
【inner】 join 表2 别名
on 连接条件
【where 筛选条件】
【group by】
【having】
【order by】
特点：
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
多表的交集
*/
#1. 等值连接
#案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%a%';
#案例3：查询部门个数大于3的城市名和部门个数
SELECT COUNT(*) c,city
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING c >3;
#案例4：查询那个部门的部门员工个数大于3的部门名和员工个数，按个数降序
SELECT department_name,COUNT(*) c
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING c>3
ORDER BY c DESC;
#案例5：查询员工名、部门名、工种名，按部门名降序
SELECT last_name, department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON j.`job_id`=e.`job_id`
ORDER BY department_name DESC;
#2. 非等值连接
#案例1：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例2：查询每个工资级别的个数>20个，按级别降序
SELECT COUNT(*) c,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING c>20
ORDER BY grade_level DESC;
#三）自连接
#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#二、外连接
/*应用场景：用于一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中的所有记录，
如果从表中有和他匹配的，则显示匹配的值
如果没有，则显示Null
外连接查询结果=内连接结果+主表中有 从表中没有的记录（null）
2、左外连接，left左边的是主表
右外连接，right右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1总有但表2中没有的+表2中有但表1没有的
*/
#查询男朋友不在男神表的女神名 左外
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.`id` IS NULL;
#查询男朋友不在男神表的女神名 右外
SELECT b.name
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id = bo.id
WHERE bo.`id` IS NULL;
#案例1：查询哪个部门没有员工
SELECT d.*,e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
#全外 mysql不支持
/*
select b.*,bo.*
from beauty b
full outer join boys bo
on b.`boyfriend_id`=bo.id;
*/
#交叉连接 实现笛卡尔连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
子查询
- 含义：出现在其他语句中的select语句，成为子查询或内查询
外部的查询语句成为主查询或外查询 - 分类
- 按子查询出现的位置
- select 后面
仅支持标量子查询 - from 后面
支持表子查询 - where或having 后面
标量子查询（单行）
列子查询（多行）
行子查询 - exists后面（相关子查询）
表子查询
- select 后面
- 按子查询出现的位置
- 按结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集为一行多列）
- 表子查询
#一、where或having后面
/*
标量子查询 特点
1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、表来个奶子查询、一般搭配单行操作符使用
列子查询 特点
一般搭配多行操作符使用
子查询的执行优先于主查询的执行
*/
#1. 标量子查询
#案例1：谁的工资比abel高
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#案例2：返回Job_id与141员工相同，salary比143员工多的员工 姓名 jobid 工资
select last_name,job_id,salary
from employees
where job_id=(
select job_id
from employees
where employee_id=141
)
and salary>(
select salary
from employees
where employee_id=143
);
#案例3：返回公司工资最少的员工的lastname jobid salary
select last_name,job_id,salary
from employees
where salary=(
SELECT MIN(salary)
FROM employees
);
#案例4：查询最低工资大于50部门最低工资的部门id和其最低工资
select min(salary) mins,department_id
from employees
group by department_id
having mins>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);
#非法使用标量子查询
# 子查询结果不是一行一列
#2. 列子查询（多行子查询）
/*
多行比较操作符
in,not in
any|some a>any(10,20,30),a大于其中任何一个都可以 可以换成max,min
all a>all(10,20,30)，a大于所有的值 可以换成max
*/
#案例1：返回Location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2：返回其他部门中比jobid为itprog部门任一工资低的员工的员工号、姓名，jobid和salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#案例3：返回其他部门中比jobid为itprog部门所有工资低的员工的员工号、姓名，jobid和salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查询（结果为一行多列或多行多列） 不怎么用
#案例：查询员工编号最新奥且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#二、select后面 不怎么用 仅支持标量子查询
#案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
)
FROM departments d;
#案例3：员工号=102的部门名
SELECT (
SELECT department_name
FROM departments d,employees e
WHERE d.department_id = e.`department_id`
AND e.`employee_id`=102
);
#三、from后面
#将子查询的结果充当一张表，必须起别名
#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#四、exists后面（相关子查询）
/*
exists(完整的查询语句)
结果1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);
#案例：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#案例：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
分页查询
- 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
- 语法
select 查询列表
from 表
...
limit 【offset】,size;
# offset 要显示条目的其实索引（起始索引从0开始）
# size 要显示的条目个数
- 特点
- limit 语句放在查询语句的最后
- 公式：要显示的页数page，每页的条目数size； limit (page-1)*size,size
#案例：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
#案例：查询第11条到25条
SELECT * FROM employees LIMIT 10,15;
#案例：显示有奖金的员工信息，并且工资较高的前10名
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
联合查询
- union 将多条查询语句的结果合并成一个结果
- 应用场景：要查询的结果来自多个表，多个表之间没有直接关系，且查询的信息一致
- 特点：
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句查询的每一列的类型和顺序最好一致
- 默认去重，如果使用union all，可以包含重复项
/*
语法：
查询语句1
union
查询语句2
...
*/
# 案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
DML语言(data manipulation language)
数据操作语言
- 插入insert
- 修改 update
- 删除 delete
#一、插入语句
#方式一：
/*
语法：
insert into 表名(列名)
values(值1);
*/
#1.插入的值的类型与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUE(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);
#2.不可以为Null的列必须插入值，可以为null的列如何插入值？
#方式1
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUE(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);
#方式2
INSERT INTO beauty(id,NAME,sex,borndate,phone)
VALUE(14,'金星','女','1990-4-23','18988888888');
#3.列的顺序是否可以调换 可以但是要一一对应
#4.列数和值的个数必须一致
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
#方式二：
/*
语法：
insert into 表明
set 列名=值，列名=值
*/
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
/*
两种方式比较：
1、方式一支持插入多行，每个对象的值之间用逗号分开；方式二不支持
2、方式一支持子查询，方式二不支持
insert into beauty(id,name,phone)
select 26,'songqian','11111';
*/
#二、修改语句
/*
1、修改单表的记录
update 表名
set 列=值,列=值...
where 筛选条件;
2、修改多表的记录
sql92
update 表1 别名，表2 别名
set 列=值
where 连接条件
and 筛选条件
sql99
update 表1 别名
inner/.. join 表2 别名
on 连接条件
set 列=值
where 筛选条件
*/
#1.修改单表的记录
#修改beauty表中姓唐的女神的电话为190000000
UPDATE beauty SET phone='190000000'
WHERE NAME LIKE '唐%';
#修改id=2的名字为张飞，魅力值10
UPDATE boys SET boyName='张飞',userCP=10
WHERE id=2;
#2.修改夺标的记录
#案例1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE bo.`boyName`='张无忌';
#案例2：修改没有男朋友的女神的男朋友的编号为2
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE b.`boyfriend_id` IS NULL;
#三、删除语句
/*
方式一：delete
1.单表的删除
delete from 表名 where 筛选条件
2.多表删除
sql92
delete 表1别名，【表2的别名】（删哪个写那个，都删就都写）
from 表1，表2
where 连接条件
and 筛选条件;
sql99
delete 表1别名，【表2的别名】（删哪个写那个)
from 表1
inner/ join 表2的别名
on 连接条件
where 筛选条件;
方式二、truncate
语法：truncate table 表名；
比较：
1、delete可以加where条件，truncate不能
2、truncate删除，效率高一点
3、加入要删除的表中有自增长列，如郭用delete删除，自增长列的值从断点开始
truncate删除后，在查数据，自增长列从1开始
4、truncate删除没有返回值，delete删除有返回值（几行被删除）
5、truncate删除不能回滚，delete删除可以回滚
*/
#delete
#1.单表的删除
#案例1：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
#2.多表的删除
#案例2：删除张无忌的女朋友
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌';
#方式二 删除全部数据
#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;
SELECT * FROM beauty;
SELECT * FROM boys;
DDL语言(Data Define language) 数据定义语言 库和表的管理
表和库的管理
- 库的管理
- 创建、修改、删除
- 表的管理
- 创建、修改、删除
数据库的位置 C:\ProgramData\MySQL\MySQL Server 5.7\Data
#一、库的管理
#1.库的创建
/*
create database [IF NOT EXISTS]库名
*/
#案例：创建库test
CREATE DATABASE test;
CREATE DATABASE IF NOT EXISTS test; #容错性处理，如果存在就不建了，可以防止报错
#2.库的修改 基本不修改
#直接改文件夹的名字
#更改库的字符集
ALTER DATABASE test CHARACTER SET utf8;
#3.库的删除
DROP DATABASE IF EXISTS test;
#二、表的管理
#1.表的创建
/*
create table 表名(
列名 列的类型【(长度)，约束】，
列名 列的类型【(长度)，约束】，
列名 列的类型【(长度)，约束】
)
*/
#案例：创建表book
CREATE TABLE book(
id INT, #编号
bName VARCHAR(20), #图书名
price DOUBLE, #价格
authorId INT, #作者
publishDate DATETIME #出版日期
);
DESC book;
#案例：创建作者表
CREATE TABLE author(
id INT,
NAME VARCHAR(50),
nation VARCHAR(20)
);
DESC author;
#2.表的修改
/*
alter table 表名 add|drop|modify|change column 列名【列类型 约束】;
*/
#1）修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#2)修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
ALTER TABLE author MODIFY COLUMN auName VARCHAR(30);
#3)添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#4)删除列
ALTER TABLE author DROP COLUMN annual;
#5)修改表名
ALTER TABLE author RENAME TO bookAuthor;
#3.表的删除
DROP TABLE IF EXISTS author;
SHOW TABLES; # 查看当前库的所有表
#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
#4.表的复制
INSERT INTO author VALUE
(1,'Murakami Haruki','japan'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
#1)仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2)复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM author;
#只复制部分数据
CREATE TABLE copy3
SELECT id,NAME
FROM author
WHERE nation='中国';
#不带数据，仅复制部分字段
CREATE TABLE copy4
SELECT id,NAME
FROM author
WHERE 0; # 写一个谁都不满足的条件，就不会有条件复制过去
常见的数据类型
- 数值型
- 整型
- 小数
- 定点数
- 浮点数
- 字符型
- 较短的文本
- char
- varchar
- 较长的文本
- text
- blob（较长的二进制数据）
- 较短的文本
- 日期型
#一、整型
/*
分类：
tinyint,smallint,mediumint,int/integer,bigint
1 2 3 4 8
特点：
1、默认有符号。如果设置无符号，添加unsigned关键字
2、溢出，会报out of range异常，同时插入临界值
3、如果不设置长度，会有默认长度。
设置的长度代表现实的最大长度，如果不够会在左边用0填充，用关键字zerofill会让0显示出来
*/
#1.如何设置无符号和有符号
CREATE TABLE tab_int(
t1 INT,
t2 INT UNSIGNED,
t3 INT(7) ZEROFILL
);
DESC tab_int;
#二、小数
/*
分类：
浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)
特点：
1、M代表整数部位+小数部位,D代表小数部位。如果超过范围，插入临界值
2、M和D都可以省略。如果是decimal，默认D=10,M=0；
float,double 会随着插入的数值的精度决定精度
3、定点型的精确度较高。如果对数值精度要求较高用这个，如货币运算
*/
#原则：选择的类型越简单越好；能保存数值的类型越小越好
#三、字符型
/*
较短的文本
char(M) 固定长度 比较耗费空间 效率更高 M可以省略，默认为1
varchar(M) 可变长度 比较节省空间 效率更低 M不可以省略
* M最多的字符数，
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本
text
blob（较大的二进制）
*/
CREATE TABLE tab_char(
c1 ENUM('a','b','c') #枚举类型，只能选一个插入，不区分大小写
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('m'); # 超出列表值，报错
CREATE TABLE tab_set(
c1 SET('a','b','c','d') #集合类型类型，选大于等于一个插入，不区分大小写
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
#四、日期型
/*
分类
date 只有日期
time 只有时间
year 年份
datetime 日期+时间 8字节 1000-9999 不受时区影响
timestamp 日期+时间 4字节 1970-2038 受时区影响
*/
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SHOW VARIABLES LIKE 'time_zone'; #查看时区
SET time_zone='+9:00'; # 修改时区
DESC tab_date;
SELECT * FROM tab_date;
约束
-
含义：一种限制，用于限制表中的数据，保证表中数据的准确性和可靠性
-
分类
-
not null 非空，保证该字段的值不能为空
姓名，学号等 -
default 默认，用于保证该字段有默认值
性别 -
primary key 主键，保证该字段的值有唯一性，并且非空
学号，员工编号 -
unique 唯一，保证该字段的值有唯一性，可以为空
座位号 -
check 检查（mysql不支持） 兼容性考量
年龄、性别 -
foreign key 外键，用于限制两个表的关系，保证该字段的值必须来自于主表关联列的值
在从表添加外键约束，用于引用主表中某列的值
学生表的专业编号，员工表的部门编号
-
-
添加约束的时机 （数据添加之前）
- 创建表时
- 修改表时
-
约束的添加分类
- 列级约束 语法上都支持，但外键约束没有效果
- 表级约束 除了非空、默认，其他都支持
-
主键和唯一的对比
| 保证唯一性 | 是否允许为空 | 一个表可以有多少个 | 是否允许组合 | |
|---|---|---|---|---|
| 主键 | y | n（null也只能有一个） | 至多有1个 | y,但不推荐 |
| 唯一 | y | y | 可以有多个 | y,但不推荐 |
- 外键
- 要求在从表设置外键关系
- 从表的外键列的类型应该与主表列的类型要求一致或兼容，名称无需求
- 主表的关联列表必须是一个key（一般是主键或唯一）
- 插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
#一、创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和类型后面追加约束类型即可
只支持：默认、非空、主键、唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY, # 主键
stuName VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查
seat INT UNIQUE, # 唯一
age INT DEFAULT 18, # 默认
majorId INT REFERENCES major(id) # 外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
SHOW TABLES;
DESC stuinfo;
SHOW INDEX FROM stuinfo; # 查看表中所有的索引，包括主键、外键、唯一
#2.添加表级约束
/*
语法 在各个字段最下面
[constraint 约束名] 约束类型（字段名）
*/
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT, # 主键
stuName VARCHAR(20), # 非空
gender CHAR(1), # 检查
seat INT, # 唯一
age INT, # 默认
majorId INT, # 外键
CONSTRAINT pk PRIMARY KEY(id), #主键名不会别改，还是叫primary
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender='男' OR gender='女'),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
#通用的写法
CREATE TABLE IF NOT EXISTS tablename(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
gender CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_tablename_major FOREIGN KEY(majorid) REFERENCES major(id)
);
#二、修改表时添加约束
/*
1、添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
2、添加表级约束
ALTER TABLE 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
);
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
# 列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD 【 CONSTRAINT fk_stuinfo_major 】 FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表示删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat; # 索引名
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; #外键约束名
标识列
- 又称为自增长列
- 含义：可以不用手动插入值，系统提供默认的序列值
#一、创建表时设置标识列
/*
特点
1、标识列必须和主键搭配吗？ 不需要，要求是一个Key
2、一个表中可以有几个标识列？ 1个
3、标识列的类型：只能是数值型
4、标识列可以通过 set auto_increment_increment=3; 设置步长
可以通过手动插入值，设置起始值
*/
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity VALUES(NULL,'john');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%';
# mysql中不支持设置offset，但是可以设置increment
# 但是如果想修改起始值，可以先插入一个值，然后后面的按此值开始增长
#二、修改表示设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表示删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;
TCL语言(transaction control language)
- 事务控制语言
- 事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账- 存储引擎：不同的存储技术
- SHOW ENGINES; # 显示支持的存储引擎
- innodb支持事务，myisam，memory不支持事务
- 事物的特点（属性，ACID)
- 原子性（Atomicity） 一个不可分割的工作单位
- 一致性（consistency） 一个一致状态->另一个一致性状态
- 隔离性（isolation） 不受其他事务干扰
- 持久性（durability） 一旦被提交，改变是永久性的
- 存储引擎：不同的存储技术
/*
事物的创建：
隐式事务：事务没有明显的开启和结束的标记 e.g. insert, update, delete
显式事务：事务具有明显的开启和结束标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
步骤1：开始事务
set autocommit=0;
start transaction; 可选
步骤2：编写事务中的sql语句（select, insert, update, delete）
语句1；
语句2；
...
步骤3：结束植物
commit； 提交事务
rollback； 回滚事务
savepoint 节点名; 设置保存点
*/
#演示事务的执行步骤
#开启事务
SET autocommit=0;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='郭襄';
#结束事务
COMMIT;
#演示savapoint
set autocommit=0;
start transaction;
UPDATE account SET balance=500 WHERE username='张无忌';
SAVEPOINT a; # 设置保存点
UPDATE account SET balance=1500 WHERE username='郭襄';
ROLLBACK TO a; # 回滚到保存点
事务并发问题
当多个事务访问数据库相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
- 脏读：T1读取了T2更新但还没被提交的字段，如果T2回滚，T1读取的内容就是临时无效的
- 不可重复读 T1读取了一个字段，T2更新了该字段，T1再次读取同一个字段，值就不同了
- 幻读 T1读取了一个字段，T2插入了一些新的行，1再次读取同一个表，就会多出几行
Mysql支持4种事务隔离级别
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| read uncommitted | y | y | y |
| read committed | n | y | y |
| repeatable read | n | n | y |
| serializable | n | n | n |
- mysql中默认第三个隔离级别 repeatable read
- oracle 中默认第二个隔离级别 read committed
select @@tx_isolation; 查看隔离类型
设置隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
视图
-
含义：虚拟表，和普通表一样使用
Mysql5.1版本出现的新特性，通过表动态生成的数据 -
只保存了sql逻辑，不保存查询结果
-
应用场景：
- 多个地方用到同样的查询结果
- 该查询结果使用的sql结果较复杂
#一、创建视图
/*
create view 视图名
as
查询语句;
*/
#1.查询邮箱中包含a字符的员工名、部门名和工种信息
#创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON j.job_id=e.job_id;
#使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#二、修改视图
/*
方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
方式二：
ALTER VIEW 视图名
AS
查询语句;
*/
#三、删除视图
/*
DROP VIEW 视图名,视图名...；
*/
#四、查看视图
DESC 视图名;
SHOW CREATE VIEW 视图名;
#五、更新视图 更新视图中的数据
#插入
INSERT INTO 视图名 VALUES('','',);
#修改
UPDATE 视图名 SET 字段名='' WHERE 条件;
#删除
DELETE FROM 视图名 WHERE 条件;
#具备以下特点的视图不允许更新
/*
1）包含分组函数,distince,group by, havign, union, union all
2）常量视图
3）select中包含子查询
4）join
5)from一个不能更新的视图
6）where子句的子查询引用了from子句中的类
*/
delete和truncate在事务使用时的区别
truncate不支持回滚，delete支持
变量
- 系统变量
- 全局变量
- 会话变量
- 自定义变量
- 用户变量
- 局部变量
系统变量
变量由系统提供，不是用户定义，属于服务器层面
使用语法
- 查看所有的系统变量
SHOW GLOBAL【SESSION】 VARIABLES;
- 查看满足条件的部分系统变量
SHOW GLOBAL【SESSION】 VARIABLES like '%%';
- 查看指定的某个系统变量的值
select @@global|session.系统变量名;
- 为某个系统变量赋值
set global|session 系统变量名= 值;
set@@global|session.系统变量名= 值;
全局级别 global，会话级别 session。默认session
自定义变量
变量时用户自定义的，不是系统的
使用步骤：
- 声明
- 赋值
- 使用（查看，比较，运算等）
- 用户变量
作用域：针对当前会话（连接）有效，同于会话变量的作用域
应用在任何地方，begin end里面外面都可以
- 声明并初始化 赋值操作符 = :=
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
- 赋值（更新用户变量的值）
#方式1
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#方式2 select into
select 字段 into 变量名
from 表;
- 使用（查看用户变量的值）
select @用户变量名;
- 局部变量
作用域：仅仅在定义它的begin end中有效
应用在 begin end中第一句话
- 声明
declare 变量名 类型;
declare 变量名 类型 default 值;
- 赋值
#方式1
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
#方式2 select into
select 字段 into 局部变量名
from 表;
- 使用（查看用户变量的值）
select 局部变量名;
对比用户变量和局部变量
| 作用域 | 定义和使用的位置 | |
|---|---|---|
| 用户变量 | 当前会话 | 会话中的任何地方 |
| 局部变量 | begin end | begin end中的第一句话 |
存储过程和函数
存储过程
- 含义：一组预先编译好的sql语句的集合，理解成批处理语句
- 好处：
- 提高了代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
#一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的sql语句）
END
/*
注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
IN stuname VARCHAR(20)
参数模式：
IN 该参数可以作为输入，即该参数需要调用方传入值
OUT 该参数可以作为输出，即该参数可以作为返回值
INOUT 该参数既可以作为输入，又可以作为输出，即该参数既需要传入值，又可以返回值
2、如果存储过程提只有一句话， BEGIN END 可以省略
存储过程提中的每条sql语句结尾要求加分号
结尾可以用 DELIMITER 重新设置
DELIMITER 结束标记 案例： DELIMITER $;
*/
#调用语法
CALL 存储过程名（实参列表）;
#二、删除存储过程
DROP PROCEDURE 存储过程名; # 不支持一次删除多个
#三、查看存储过程信息
SHOW CREATE PROCEDURE 存储过程名;
函数
| 返回 | 适用范围 | |
|---|---|---|
| 存储过程 | >=0个 | 批量插入、更新 |
| 函数 | 仅1个 | 处理数据后返回一个结果 |
#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
/*
注意
1、参数列表包含两部分 参数名、参数类型
2、函数体：肯定会有return语句，如果没有会报错
return语句没有放在最后也不报错，但不建议
3、使用delimiter语句设置结束标记
4、函数体仅有一句话时，可以省略begin end
*/
#调用
SELECT 函数名(参数列表)
#三、查看函数
SHOW CREATE FUNCTION 函数名;
#四、删除函数
DROP FUNCTION 函数名;
流程控制结构
- 顺序结构
- 分支
- 循环
分支结构
#一、分支结构
#1.if 函数 实现简单双分支
IF(表达式1,表达式2,表达式3)；
#2.case结构
#3.if结构 实现多重分支
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
END IF;
#应用在begin end中
循环结构
- 分类：while, loop, repeat
- 循环控制
- iterate 类似于continue
- leave 类似于break
#1.while
【标签】 WHILE 循环条件 DO
循环体;
END WHILE 【标签】;
#2.loop
【标签:】 LOOP
循环体;
END LOOP 【标签】;
#可以用来模拟简单的死循环
#3.repeat
【标签:】 REPEAT
循环体;
UNTIL 结束循环的条件
END REPEAT 【标签】;
标签:
MySQL
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构