大象席地而坐

sql操作

86分钟阅读

select查询

查询语句

查询常量

select 常量值1,常量值2,常量值3;
-- 示例
select 1,'b','2025-01-01';

查询表达式

select 表达式;
-- 示例
select 1+2,2+3;

查询函数

select 函数;
-- 示例
select if(null,'1','2');

查询指定字段

select 字段1,字段2,字段3 from 表名;
-- 示例
select title,href,pid from mysql_learn;

查询所有列

select * from 表名;

列/表别名

-- 列别名
select 列 [as] 别名 from 表;
-- 表别名
select 别名.字段,别名.* from 表名 [as] 别名;
 
select ml.pid from mysql_learn as ml;

查询条件

语句

select 列名 from 表名 where 列 运算符 值

where后面跟上一个或者多个条件，条件是对前面数据的过滤，只有满足where后面条件的数据才会被返回。

条件查询运算符

操作符描述
=等于
<>或!=不等于
>大于
<小于
>=大于等于
⇐小于等于

逻辑查询运算符

操作符描述
AND多个条件都成立
OR多个条件中满足一个

模糊查询(like)

select 列名 from 表名 where 列 like pattern;
-- 示例
select * from mysql_learn where title like '%主页%';
select * from mysql_learn where title like '%主页';
select * from mysql_learn where title like '主页%';

BETWEEN AND(区间查询)

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期，属于一个闭区间查询，查询结果包括临界值。

selec 列名 from 表名 where 列名 between 值1 and 值2;
-- 示例
select * from mysql_learn where pid BETWEEN 1 AND 5;

IN查询

select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

NOT IN查询

select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);

NULL的查询

查询运算符like、between and、in、not in对NULL值查询不起效，要使用 IS NULL、IS NOT NULL进行操作。

⇐>安全等于

既可以判断NULL值，又可以判断普通的数值，可读性较低，用得较少。

排序和分页

排序查询(order by)

  1. asc|desc表示排序的规则，asc：升序，desc：降序，默认为asc；
  2. 支持多个字段进行排序，多字段排序之间用逗号隔开。
select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];
-- 示例
select * from mysql_learn order by id asc,pid desc ;
-- 根据函数排序
select * from mysql_learn order by substr(status,1,2) desc ;

limit限制

limit用来限制select查询返回的行数，常用于分页等操作。limit后只能跟明确的数字。

select 列 from 表 limit [offset,] count;
/*
  offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。
  count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。
  limit中offset和count的值不能用表达式。
*/
-- 获取前n行记录
select 列 from 表 limit 0,n;
或者
select 列 from 表 limit n;
-- 获取n到m的记录
select 列 from 表 limit n-1,m-n+1;

分页查询

page：表示第几页，从1开始，范围[1,+∞)

pageSize：每页显示多少条记录，范围[1,+∞)

建议：排序中存在相同的值时，需要再指定一个排序规则，通过这种排序规则不存在二义性。

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

总结

  1. order by … [asc|desc]用于对查询结果排序，asc：升序，desc：降序，asc|desc可以省略，默认为asc；
  2. limit用来限制查询结果返回的行数，有2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取count行；
  3. limit中offset可以省略，默认值为0；
  4. limit中offset 和 count都必须大于等于0；
  5. limit中offset和count的值不能用表达式；
  6. 分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序。

分组查询

SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];

说明

group_function：聚合函数；

group_by_expression：分组表达式，多个之间用逗号隔开；

group_condition：分组之后对数据进行过滤；

分组中，select后面只能有两种类型的列：

a. 出现在group by后的列

b. 或者使用聚合函数的列

聚合函数

函数名称作用
max查询指定列最大值
min查询指定列最小值
count统计查询结果的行数
sum求和，返回指定列的总和
avg求平均数，返回指定列数据的平均值
-- 单字段分组
select user_id,count(id) from t_order group by user_id;
-- 多字段分组
select user_id,the_year,count(id) from t_order group by user_id,the_year;
-- 分组前筛选数据
select user_id,count(id) from t_order where the_year = '2018' group by user_id;
-- 分组后筛选数据
select user_id,count(id) from t_order where the_year = '2018' group by user_id having count(id)>2;
-- 分组后排序
select user_name,max(price) from t_order group by user_name order by max(price) desc ;

where和having的区别

where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。

可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。

where & group by & having & order by & limit 一起协作

select 列 from
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;
 
-- 示例
select user_name,count(id) from t_order where the_year='2018' group by user_name having count(id)>=2 order by count(id) desc limit 1;

mysql常见函数

数值型函数

函数名称作用
abs求绝对值，正数的绝对值是其本身，负数的绝对值为其相反数，0 的绝对值是0。
sqrt(n)求二次方根，非负数 x 的二次方根。负数没有平方根，返回结果为 NULL。
mod(m,n)求余数，返回 m被 n 除后的余数
ceil和ceiling两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
floor向下取整，返回值转换为一个bigint；
rand生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列；
round(x,y)对所传参数进行四舍五入,返回值保留小数点后面指定的y位。
sign返回参数的符号,负、零和正时返回结果依次为 -1、0 和 1；
pow(m,n) 和 power(m,n)两个函数的功能相同，都是所传参数的次方的结果值,函数用于计算 m 的 n 次方；
sin求正弦值
asin求反正弦值，与函数 SIN 互为反函数
cos求余弦值
acos求反余弦值，与函数 COS 互为反函数
tan求正切值
atan求反正切值，与函数 TAN 互为反函数
cot求余切值
select abs(-16);    -- 16
select sqrt(16);    -- 4
select mod(13,2);   -- 1
select ceil(12.22),ceiling(11.12); -- 13,12
select floor(12.2); -- 12
select rand(),rand(2); -- 0.8268141307186467,0.6555866465490187
select round(12.45),round(12.81,1); -- 12,12.8
select sign(12),sign(-1); -- 1,-1
select pow(2,3),power(3,2); -- 8,9

字符串函数

函数名称作用
length计算字符串长度函数，返回字符串的字节长度；
concat(sl，s2，…)合并字符串函数；若有任何一个参数为 NULL，则返回值为 NULL。若所有参数均为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串
insert(s1，x，len，s2)替换字符串函数；返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。
lower将字符串中的字母转换为小写；
upper将字符串中的字母转换为大写；
left(s，n)从左侧字截取符串，返回字符串左边的若干个字符；
right(s，n)从右侧字截取符串，返回字符串右边的若干个字符；
trim删除字符串左右两侧的空格；使用字符串 s2 替换字符串 s 中所有的字符串 s1。
replace(s，s1，s2)字符串替换函数，返回替换后的新字符串；
substr 和 substring截取字符串，返回从指定位置开始的指定长度的字符换；
reverse字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串；
select length('123456'); -- 6
select concat('hello',' ','mysql',' ','!'); -- hello mysql !
select insert('abcc',3,4,'de'); -- abde
select lower('abCdE'); -- abcde
select upper('abCdE'); -- ABCDE
select left('zhangsan',5); -- zhang
select right('zhangsan',3); -- san
select trim(' 123 '); -- 123
select replace('abcc','c','d'); -- abdd;
select substr('1234',3),substring('abcd',4); -- 34,d
select reverse('abc'); -- cba

日期和时间函数

函数名称用途特点
curdate()返回当前系统的日期值返回格式为YYYY-MM-DD，不包含时间部分。
current_date返回当前系统的日期值与curdate()功能相同，返回格式为YYYY-MM-DD。
curtime()返回当前系统的时间值返回格式为HH:MM:SS，不包含日期部分。
current_time返回当前系统的时间值与curtime()功能相同，返回格式为HH:MM:SS。
now()返回当前系统的日期和时间值返回格式为YYYY-MM-DD HH:MM:SS，包含日期和时间。
sysdate()返回当前系统的日期和时间值与now()类似，但可能在存储过程中的表现不同，具体取决于上下文环境。
unix_timestamp()获取UNIX时间戳返回一个以秒为单位的无符号整数，表示自1970年1月1日以来的秒数。
from_unixtime()将UNIX时间戳转换为时间格式与unix_timestamp()互为反函数，将秒数转换为日期和时间格式。
month()获取指定日期中的月份返回值范围为1~12，代表一年中的月份。
monthname()获取指定日期中的月份英文名称返回月份的英文全称，如January、February等。
dayname()获取指定日期对应的星期几的英文名称返回星期几的英文全称，如Monday、Tuesday等。
dayofweek()获取指定日期是一周中是第几天返回值范围为1~7，其中1代表周日，7代表周六。
week()获取指定日期是一年中的第几周返回值范围通常为0-52，但在某些情况下可能为1-53，具体取决于日期设置。
dayofyear()获取指定日期是一年中的第几天返回值范围为1~366，考虑了闰年。
dayofmonth()获取指定日期是一个月中是第几天返回值范围为1~31，根据月份不同而变化。
year()获取年份返回值范围为1970~2069，受限于MySQL的日期范围。
time_to_sec()将时间参数转换为秒数将指定的时间格式（如HH:MM:SS）转换为总秒数，返回整数。
sec_to_time()将秒数转换为时间格式与time_to_sec()互为反函数，将秒数转换为时间格式，如HH:MM:SS。
date_add()向日期添加指定的时间间隔支持添加年、月、日、小时、分钟、秒等时间间隔，使用INTERVAL关键字。
adddate()向日期添加指定的时间间隔与date_add()功能相同，但语法稍有不同，部分情况下可能更常用。
date_sub()向日期减去指定的时间间隔与date_add()类似，但用于减去时间间隔。
subdate()向日期减去指定的时间间隔与date_sub()功能相同，但语法稍有不同。
addtime()时间加法运算直接在原始时间上添加指定的时间，支持多种时间格式输入。
subtime()时间减法运算直接在原始时间上减去指定的时间，支持多种时间格式输入。
datediff()获取两个日期之间的间隔天数返回参数1减去参数2的值，结果为整数，可为正或负。
date_format()格式化指定的日期根据指定的格式字符串返回格式化的日期和时间值，非常灵活。
weekday()获取指定日期在一周内的对应的工作日索引返回值为0~6，其中0代表周一，6代表周日，这与其他函数可能不同。
select curdate(),current_date(); -- 2025-02-28,2025-02-28
select curtime(),current_time(); -- 21:39:55,21:39:55
select now(),sysdate(); -- 2025-02-28 21:40:21,2025-02-28 21:40:21
select unix_timestamp(); -- 1740750046
select from_unixtime(unix_timestamp()); -- 2025-02-28 21:41:23
select month(now()); -- 2
select monthname(now()); -- February
select dayname(now()); -- Friday
select dayofweek(now()); -- 6
select week(now()); -- 8
select dayofyear(now()); -- 59
select dayofmonth(now()); -- 28
select year(now()); -- 2025
select time_to_sec(now()); -- 78373
select sec_to_time(78373); -- 21:46:13
select date_add(now(),interval 2 year),adddate(now(),interval 1 month ); -- 2027-02-28 21:48:32,2025-03-28 21:48:32
select date_sub(now(),interval 2 year),subdate(now(),interval 1 month ); -- 2023-02-28 21:49:07,2025-01-28 21:49:07
select addtime(curtime(),'1200'); -- 22:02:56
select subtime(curtime(),'1200'); -- 21:39:13
select datediff('2025-01-01','2023-01-01'); -- 731
select date_format('2025-01-01 23:57:59','%Y-%m-%d %H-%i-%s'); -- 2025-01-01 23-57-59
select weekday('2025-01-01'); -- 2

聚合函数

流程控制函数

函数名称作用
if(expr,v1,v2)判断，流程控制;当 expr 为真是返回 v1 的值，否则返回 v2。
ifnull(v1,v2)判断是否为空，v1为空返回v2，否则返回v1。
case搜索语句，类似于java中的if..else if..else
select if(1>0,'是','否');
select ifnull('1','2');
 
-- case表达式1
CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END CASE;
-- case表达式2
CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;
 
 
CREATE TABLE t_stu
(
    id   INT AUTO_INCREMENT COMMENT '编号',
    name VARCHAR(10) COMMENT '姓名',
    sex  TINYINT COMMENT '性别,0:未知,1:男,2:女',
    PRIMARY KEY (id)
) COMMENT '学生表';
 
insert into t_stu (name, sex)
VALUES ('张学友', 1),
       ('刘德华', 1),
       ('郭富城', 1),
       ('蔡依林', 2),
       ('xxx', 0);
 
select *
from t_stu;
-- 查询所有学生信息，输出：姓名，性别（男、女、未知）
select name, (case sex when 1 then '男' when 2 then '女' else '未知' end) from t_stu;
 
select name, (case when sex=1 then '男' when sex=2 then '女' else '未知' end) from t_stu;

其他函数

函数名称作用
version数据库版本号
database当前的数据库
user当前连接用户
password返回字符串密码形式
md5返回字符串的md5数据

连接查询

drop table if exists t_team;
create table t_team(
id int not null AUTO_INCREMENT PRIMARY KEY comment '组id',
team_name varchar(32) not null default '' comment '名称'
) comment '组表';
drop table if exists t_employee;
create table t_employee(
id int not null AUTO_INCREMENT PRIMARY KEY comment '部门id',
emp_name varchar(32) not null default '' comment '员工名称',
team_id int not null default 0 comment '员工所在组id'
) comment '员工表表';
insert into t_team values (1,'架构组'),(2,'测试组'),(3,'java组'),(4,'前端组');
insert into t_employee values (1,'路人甲Java',1),(2,'张三',2),(3,'李四',3),(4,'王
五',0),(5,'赵六',0);

笛卡尔积

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

sql中笛卡尔积语法

select 字段 from 表1,表2[,表N];
或者
select 字段 from 表1 join 表2 [join 表N];
 
-- 示例
select a.*,b.* from t_employee a,t_team b;
select a.*,b.* from t_employee a join t_team b;

内连接

select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1, 表2 [where 关联条件];
 
-- 示例
select a.id,a.team_name,b.id, b.emp_name, b.team_id from t_team a inner join t_employee b on a.id = b.team_id;
select a.id,a.team_name,b.id, b.emp_name, b.team_id from t_team a join t_employee b on a.id=b.team_id;
select a.id,a.team_name,b.id, b.emp_name, b.team_id from t_team a,t_employee b where a.id=b.team_id;

外连接

外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接

查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左外链接：使用left join关键字，left join左边的是主表。

右外连接：使用right join关键字，right join右边的是主表。

左连接

select 列 from 主表 left join 从表 on 连接条件;
-- 示例
select a.*,b.* from t_team a left join t_employee b on a.id = b.team_id;

右连接

select 列 from 从表 right join 主表 on 连接条件;
-- 示例
select a.*,b.* from t_team a right join t_employee b on a.id = b.team_id;

子查询

出现在select语句中的select语句，称为子查询或内查询。外部的select查询语句，称为主查询或外查询。

子查询分类

按结果集的行列数不同分类

  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集只有一列多行）
  • 行子查询（结果集有一行多列）
  • 表子查询（结果集一般为多行多列）

按子查询出现在主查询中的不同位置分类

  • select后面：仅仅支持标量子查询。
  • from后面：支持表子查询。将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到，然后将真实的表和子查询结果表进行连接查询；
  • where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）
  • exists后面（即相关子查询）：表子查询（多行、多列）
/*部门表*/
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT comment '部门id',
  `department_name` varchar(3) DEFAULT NULL comment '部门名称',
  `manager_id` int(6) DEFAULT NULL comment '管理者id',
  `location_id` int(4) DEFAULT NULL comment '部门位置id，来源于表locations中的location_id',
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 comment '部门表';
insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*员工表*/
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT comment '员工id',
  `first_name` varchar(20) DEFAULT NULL comment '名',
  `last_name` varchar(25) DEFAULT NULL comment '姓',
  `email` varchar(25) DEFAULT NULL comment '电子邮箱',
  `phone_number` varchar(20) DEFAULT NULL comment '手机',
  `job_id` varchar(10) DEFAULT NULL comment '职位id，来源于jobs表中的job_id',
  `salary` double(10,2) DEFAULT NULL comment '薪水',
  `commission_pct` double(4,2) DEFAULT NULL comment '佣金百分比',
  `manager_id` int(6) DEFAULT NULL comment '上级id',
  `department_id` int(4) DEFAULT NULL comment '所属部门id，来源于departments中的department_id',
  `hiredate` datetime DEFAULT NULL comment '入职日期',
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 comment '员工表';
insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*职位信息表*/
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL comment '职位id',
  `job_title` varchar(35) DEFAULT NULL comment '职位名称',
  `min_salary` int(6) DEFAULT NULL comment '薪资范围最小值',
  `max_salary` int(6) DEFAULT NULL comment '薪资范围最大值',
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB comment '职位id';
insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*位置表*/
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT comment '位置id',
  `street_address` varchar(40) DEFAULT NULL comment '街道地址',
  `postal_code` varchar(12) DEFAULT NULL comment '邮编',
  `city` varchar(30) DEFAULT NULL comment '城市名称',
  `state_province` varchar(25) DEFAULT NULL comment '省',
  `country_id` varchar(2) DEFAULT NULL comment '国家编号',
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 comment '位置表';
insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*薪资等级表*/
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`(
  `grade_level` varchar(3) comment '等级',
  `lowest_sal`  int comment '薪资最低值',
  `highest_sal` int comment '薪资最高值',
  PRIMARY KEY (`grade_level`)
) comment '薪资等级表';
INSERT INTO job_grades VALUES ('A', 1000, 2999),('B', 3000, 5999),('C', 6000, 9999),('D', 10000, 14999),('E', 15000, 24999),('F', 25000, 40000);
-- 示例1：查询每个部门员工个数
select a.department_name,(select count(*) from employees b where b.department_id = a.department_id) as 员工数量 from departments a;
-- 示例2：查询员工号=102的部门名称
select (select a.department_name from departments a,employees b where a.department_id = b.department_id and b.employee_id = '102') as 部门名称;
-- 查询每个部门平均工资的工资等级
select avg_sal_dep.department_id,avg_sal_dep.avg_sal,saler_grade.grade_level
from (select department_id,avg(a.salary) as avg_sal
from employees a group by a.department_id) as avg_sal_dep,(select * from job_grades) as saler_grade
where avg_sal_dep.avg_sal between saler_grade.lowest_sal and saler_grade.highest_sal;

where或having后面

  1. 子查询放在小括号内。
  2. 子查询一般放在条件的右侧。
  3. 标量子查询，一般搭配着单行单列操作符使用 >、<、>=、⇐、=、<>、!=
  4. 列子查询，一般搭配着多行操作符使用。

n(not in)：列表中的“任意一个”

any或者some：和子查询返回的“某一个值”比较，比如a>some(10,20,30)，a大于子查询中

任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。

all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话

说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

  1. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。
mysql中的in、any、some、all

in：in常用于where表达式中，其作用是查询某个范围内的数据；

any和some一样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

标量子查询
-- 查询谁的工资比Abel的高？
select * from employees a where a.salary>(select salary from employees where last_name = 'Abel');
多个标量子查询
-- 返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资
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);
子查询和分组查询
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资【having】
select department_id,min(salary)
from employees group by department_id having min(salary)> (select min(salary)
from employees where department_id = 50);
列子查询(子查询结果集一列多行)
-- 返回location_id是1400或1700的部门中的所有员工姓名
select a.last_name from employees  a where a.department_id in  (select distinct departments.department_id from departments where location_id in ('1400','1700'));
行子查询（子查询结果集一行多列）
-- 查询员工编号最小并且工资最高的员工信息
select * from employees where (employee_id,salary) = (select min(employee_id),max(salary) from employees);
exists后面（也叫做相关子查询）

exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。

一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。

-- 查询所有员工的部门名称
SELECT department_name
FROM departments a
WHERE exists(SELECT 1
FROM employees b
WHERE a.department_id = b.department_id);

NULL字符常见问题

比较运算符中使用NULL

select 1>null; -- null
select 1<null; -- null

任何值和NULL使用运算符(>、<、>=、<=、!=、<>)或者(in、not in、any/some、all)比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

  条件查询`where is_report !=1` ,当is_report部分值为`null`，实际无法查出为`null`的数据应该优化为`where ifnull(last_update_time,0)!=1;`或

last_update_time!=1 or last_update_time is null。

IN、NOT IN和NULL比较

当IN和NULL比较时，无法查询出为NULL的记录；当NOT IN后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

聚合函数与NULL比较

count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

判断是否为空只能用IS NULL、IS NOT NULL；NULL不能作为主键的值。

总结

  1. NULL作为布尔值的时候，不为1也不为0；
  2. 任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都为NULL；
  3. 当IN和NULL比较时，无法查询出为NULL的记录；
  4. 当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空；
  5. 判断是否为空只能用IS NULL、IS NOT NULL；
  6. count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行；
  7. 当字段为主键的时候，字段会自动设置为not null；
  8. NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值。

事务详解

什么是事务

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败，不会存在部分成功的情况。

比如A用户给B用户转账100操作，过程如下：1.从A账户扣100 2.给B账户加100

如果在事务的支持下，上面最终只有2种结果：

  1. 操作成功：A账户减少100；B账户增加100
  2. 操作失败：A、B两个账户都没有发生变化

如果没有事务的支持，可能出现错：A账户减少了100，此时系统挂了，导致B账户没有加上100，而A账户凭空少了100。

事务的特性(ACID)

原子性(Atomicity)

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

一致性(Consistency)

一个事务必须使数据库从一个一致性状态变换到另一个一致性状态。

一致性，指的是数据处于一种有意义的状态，这种状态是语义上的而不是语法上的。最常见的例子是转帐。例如从帐户A转一笔钱到帐户B上，如果帐户A上的钱减少了，而帐户B上的钱却没有增加，那么我们认为此时数据处于不一致的状态。

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(Durability)

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的。

mysql中事务操作

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。

是否开启隐式事务是由变量autocommit控制的。所以事务分为隐式事务和显式事务。

  • 显性事务控制：适合需要精确控制事务边界和生命周期的场景，例如，当需要保证一系列操作要么全部成功，要么全部失败时。
  • 隐性事务控制：适合简单的、单个操作的场景，或者当你希望每个 SQL 语句都立即生效时。但在复杂的逻辑中可能会导致数据不一致的风险。

隐式事务

事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量 autocommit 是否开启了自动提交

show variables like 'autocommit'; -- autocommit 为ON表示开启了自动提交

显式事务

事务需要手动开启、提交或回滚，由开发者自己控制。

-- 方式1
//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;
-- 方式2
start transaction;//开启事务
//执行事务操作
commit|rollback;
 
start transaction ;
insert into t_team (team_name) values ('开发组');
commit ;

savepoint关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用 savepoin 来实现，效果如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)
mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)

只读事务

表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有一些性能上的优化。

start transaction read only;
 
start transaction read only ;
select * from t_team;
update t_team set team_name = '开发组1' where id = 6; -- 执行此行会报错
commit ;

事务常见问题

脏读

一个事务在执行的过程中读取到了其他事务还没有提交的数据。

读已提交

一个事务操作过程中可以读取到其他事务已经提交的数据。事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据（相当于当前读）

可重复读

一个事务操作中对于一个读取操作不管多少次，读取到的结果都是一样的。

幻读

幻读在可重复读的模式下才会出现，其他隔离级别中不会出现；

例子：

可重复读模式下，比如有个用户表，手机号码为主键，有两个事物进行如下操作：

事务A操作如下： 1、打开事务 2、查询号码为X的记录，不存在 3、插入号码为X的数据，插入报错（为什么会报错，先向下看） 4、查询号码为X的记录，发现还是不存在（由于是可重复读，所以读取记录X还是不存在的）；

事物B操作：在事务A第2步操作时插入了一条X的记录，所以会导致A中第3步插入报错（违反了唯一约束）上面操作对A来说就像发生了幻觉一样，明明查询X（A中第二步、第四步）不存在，但却无法插入成功。

幻读可以这么理解：事务中后面的操作（插入号码X）需要上面的读取操作（查询号码X的记录）提供支持，但读取操作却不能支持下面的操作时产生的错误，就像发生了幻觉一样。

事务的隔离级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，比如A、B两个事物同时进行的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的隔离级别来保证，不同的隔离级别中所产生的效果是不一样的。

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。

分类

  1. 读未提交：READ-UNCOMMITTED
  2. 读已提交：READ-COMMITTED
  3. 可重复读：REPEATABLE-READ
  4. 串行：SERIALIZABLE

上面4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别

show variables like 'transaction_isolation';

设置隔离级别

  1. 修改mysql中的my.init文件，我们将隔离级别设置为：READ-UNCOMMITTED，如下：
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=READ-UNCOMMITTED
  1. 重新mysql服务。

各种隔离级别中会出现的问题

隔离级别脏读不可重复读幻读
READ-UNCOMMITTED有有无
READ-COMMITTED无有无
REPEATABLE-READ无无有
SERIALIZABLE无无无

隔离级别的选择

  1. 隔离级别越高，并发性也低，比如最高级别 SERIALIZABLE 会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
  2. 具体选择哪种需要结合具体的业务来选择。
  3. 读已提交（READ-COMMITTED）通常用的比较多。

视图

视图的数据是在使用视图的时候动态生成的，视图只保存了sql的逻辑，不保存查询的结果。

视图和表的区别

语法实际是否占用物理空间使用
视图create view只是保存了sql的逻辑增删改查，实际上我们只使用查询
表create table保存了数据增删改查

视图的优点：

  • 简化复杂的sql操作，不用知道他的实现细节
  • 隔离了原始表，可以不让使用视图的人接触原始的表，从而保护原始数据，提高了安全性
/*部门表*/
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT comment '部门id',
  `department_name` varchar(3) DEFAULT NULL comment '部门名称',
  `manager_id` int(6) DEFAULT NULL comment '管理者id',
  `location_id` int(4) DEFAULT NULL comment '部门位置id，来源于表locations中的location_id',
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 comment '部门表';
insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*员工表*/
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT comment '员工id',
  `first_name` varchar(20) DEFAULT NULL comment '名',
  `last_name` varchar(25) DEFAULT NULL comment '姓',
  `email` varchar(25) DEFAULT NULL comment '电子邮箱',
  `phone_number` varchar(20) DEFAULT NULL comment '手机',
  `job_id` varchar(10) DEFAULT NULL comment '职位id，来源于jobs表中的job_id',
  `salary` double(10,2) DEFAULT NULL comment '薪水',
  `commission_pct` double(4,2) DEFAULT NULL comment '佣金百分比',
  `manager_id` int(6) DEFAULT NULL comment '上级id',
  `department_id` int(4) DEFAULT NULL comment '所属部门id，来源于departments中的department_id',
  `hiredate` datetime DEFAULT NULL comment '入职日期',
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 comment '员工表';
insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*职位信息表*/
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL comment '职位id',
  `job_title` varchar(35) DEFAULT NULL comment '职位名称',
  `min_salary` int(6) DEFAULT NULL comment '薪资范围最小值',
  `max_salary` int(6) DEFAULT NULL comment '薪资范围最大值',
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB comment '职位id';
insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*位置表*/
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT comment '位置id',
  `street_address` varchar(40) DEFAULT NULL comment '街道地址',
  `postal_code` varchar(12) DEFAULT NULL comment '邮编',
  `city` varchar(30) DEFAULT NULL comment '城市名称',
  `state_province` varchar(25) DEFAULT NULL comment '省',
  `country_id` varchar(2) DEFAULT NULL comment '国家编号',
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 comment '位置表';
insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*薪资等级表*/
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`(
  `grade_level` varchar(3) comment '等级',
  `lowest_sal`  int comment '薪资最低值',
  `highest_sal` int comment '薪资最高值',
  PRIMARY KEY (`grade_level`)
) comment '薪资等级表';
INSERT INTO job_grades VALUES ('A', 1000, 2999),('B', 3000, 5999),('C', 6000, 9999),('D', 10000, 14999),('E', 15000, 24999),('F', 25000, 40000);

创建视图

create view 视图名
as
查询语句;

案例1：查询姓名中包含a字符的员工名、部门、工种信息

-- 创建视图
create view show_employee1 as
select a.last_name,b.department_name,c.job_title
from employees a,departments b,jobs c where a.department_id = b.department_id and a.job_id = c.job_id;
-- 查询视图中员工姓名包含a的员工名，部门，工种信息
select *
from show_employee1 where last_name like '%a%';

案例2：查询各部门的平均工资级别

create view show_employee2
as
select a.department_id,a.sa,b.grade_level
from (select department_id,avg(salary) as sa
from employees group by department_id) a left join job_grades b on a.sa between b.lowest_sal and b.highest_sal;
 
select * from show_employee2;

修改视图

-- 方法1：
create or replace view 视图名
as
查询语句;
-- 方法2:
alter view 视图名
as
查询语句;

删除视图

drop view 视图名1 [,视图名2] [,视图名n];

查看视图结构

/*方式1*/
desc 视图名称;
/*方式2*/
show create view 视图名称;

变量

系统变量

系统变量由系统定义的，不是用户定义的，属于mysql服务器层面的。

分类

  • 全局变量
  • 会话变量

使用

查看系统变量

-- 查看系统所有变量
show [global | session] variables;
-- 查看全局变量
show global variables;
-- 查看会话变量
show session variables;
show variables;

查看满足条件的系统变量

show [global|session] like '%变量名%';

查看指定的系统变量

select @@[global.|session.]系统变量名称;

赋值

//方式1
set [global|session] 系统变量名=值;
//方式2
set @@[global.|session.]系统变量名=值;

上面使用中介绍的，全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认为session级别。

全局变量的使用中用到了 @@ 关键字，后面会介绍自定义变量，自定义变量中使用了一个 @ 符号，这点需要和全局变量区分一下。

全局变量

mysql服务器每次启动都会为所有的系统变量设置初始值。我们为系统变量赋值，针对所有会话（连接）有效，可以跨连接，但不能跨重启，重启之后，mysql服务器会再次为所有系统变量赋初始值。

会话变量

针对当前会话（连接）有效，不能跨连接。会话变量是在连接创建时由mysql自动给当前会话设置的变量。

自定义变量

变量由用户自定义的，而不是系统提供的。

分类

  • 用户变量
  • 局部变量

用户变量

针对当前会话（连接）有效，作用域同会话变量。用户变量可以在任何地方使用也就是既可以在begin end里面使用，也可以在他外面使用。

使用

声明并初始化(要求声明时必须初始化)

/*方式1*/
set @变量名=值;
/*方式2*/
set @变量名:=值;
/*方式3*/
select @变量名:=值;

赋值（更新变量的值）

/*方式1：这块和变量的声明一样*/
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
/*方式2*/
select 字段 into @变量名 from 表;

使用

select @变量名;

示例

/*set方式创建变量并初始化*/
set @username='路人甲java';
/*select into方式创建变量*/
select 'javacode2018' into @gzh;
select count(*) into @empcount from employees;
/*select :=方式创建变量*/
select @first_name:='路人甲Java',@email:='javacode2018@163.com';
/*使用变量*/
insert into employees (first_name,email) values (@first_name,@email);

局部变量

declare用于定义局部变量变量，在存储过程和函数中通过declare定义变量在begin…end中，且在语句之前。并且可以通过重复定义多个变量

declare变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了，不能使用了。这个同编程一样。

使用

声明

declare 变量名 变量类型;
declare 变量名 变量类型 [default 默认值];

赋值

/*方式1*/
set 局部变量名=值;
set 局部变量名:=值;
select 局部变量名:=值;
/*方式2*/
select 字段 into 局部变量名 from 表;

使用

select 局部变量名;

示例

/*创建表test1*/
drop table IF EXISTS test1;
create table test1(a int PRIMARY KEY,b int);
/*声明脚本的结束符为$$*/
DELIMITER $$
DROP PROCEDURE IF EXISTS proc1;
CREATE PROCEDURE proc1()
BEGIN
/*声明了一个局部变量*/
DECLARE v_a int;
select ifnull(max(a),0)+1 into v_a from test1;
select @v_b:=v_a*2;
insert into test1(a,b) select v_a,@v_b;
end $$
/*声明脚本的结束符为;*/
DELIMITER ;
/*调用存储过程*/
call proc1();
/*查看结果*/
select * from test1;

delimiter关键字

我们写sql的时候，mysql怎么判断sql是否已经结束了，可以去执行了？

需要一个结束符，当mysql看到这个结束符的时候，表示可以执行前面的语句了，mysql默认以分号为结束符。

当我们创建存储过程或者自定义函数的时候，写了很大一片sql，里面包含了很多分号，整个创建语句是一个整体，需要一起执行，此时我们就不可用用分号作为结束符了。那么我们可以通过 delimiter 关键字来自定义结束符。

delimiter 分隔符

存储过程及自定义函数

准备数据

/*建表test1*/
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
id INT NOT NULL PRIMARY KEY COMMENT '编号',
age SMALLINT UNSIGNED NOT NULL COMMENT '年龄',
name VARCHAR(16) NOT NULL COMMENT '姓名'
) COMMENT '用户表';

存储过程

一组预编译好的sql语句集合，理解成批处理语句。

创建存储过程

create procedure 存储过程名([参数模式] 参数名 参数类型)
begin
存储过程体
end

参数模式有3种

in：该参数可以作为输入，也就是该参数需要调用方传入值。

out：该参数可以作为输出，也就是说该参数可以作为返回值。

inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。

参数模式默认为IN。

一个存储过程可以有多个输入、多个输出、多个输入输出参数

调用存储过程

call 存储过程名称(参数列表);

删除存储过程

drop procedure [if exists] 存储过程名称;

存储过程只能一个个删除，不能批量删除。

if exists：表示存储过程存在的情况下删除。

修改存储过程

存储过程不能修改，若涉及到修改的，可以先删除，然后重建。

查看存储过程

show create procedure 存储过程名称;

示例

示例1：空参列表

-- 设置结束符为$
delimiter $
-- 删除存储过程，如果存在
DROP procedure if exists proc1;
-- 创建存储过程proc1
create procedure proc1()
    begin
        insert into t_user values (1,27,'张三');
        insert into t_user values (2,50,'李四');
    end $
-- 将结束符设置为;
delimiter ;
-- 调用存储过程
call proc1();

delimiter用来设置结束符，当mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所

有语句作为一个整体运行，存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处

用到了delimiter。

mysql默认结束符是分号。

上面存储过程中向t_user表中插入了2条数据。

示例2：带in参数的存储过程

-- 设置结束符为$
delimiter $
-- 删除存储过程，如果存在
DROP procedure if exists proc2;
-- 创建存储过程proc2
create procedure proc2(id int,age int,name varchar(16))
    begin
        insert into t_user values (id,age,name);
    end $
-- 将结束符设置为;
delimiter ;
-- 创建自定义变量
select @id:=3,@age:=15,@name:='李四';
-- 调用存储过程
call proc2(@id,@age,@name);

示例3：带out参数的存储过程

-- 设置结束符为$
delimiter $
-- 删除存储过程，如果存在
DROP procedure if exists proc3;
-- 创建存储过程proc3
create procedure proc3(id int,age int,name varchar(16),out user_count int,out out_max_id int)
    begin
        insert into t_user values (id,age,name);
        -- 查询t_user表的记录，放入user_count中,max_id用来存储t_user中最小的id
        select count(*),max(id) from t_user;
    end $
-- 将结束符设置为;
delimiter ;
-- 创建自定义变量
select @id:=4,@age:=16,@name:='王五';
-- 调用存储过程
call proc3(@id,@age,@name,@user_count,@max_id);

示例4：带inout参数的存储过程

-- 设置结束符为$
delimiter $
-- 删除存储过程，如果存在
DROP procedure if exists proc4;
-- 创建存储过程proc4
create procedure proc4(inout a int,inout b int)
    begin
        set a = a*2;
        select b*2 into b;
    end $
-- 将结束符设置为;
delimiter ;
-- 创建自定义变量
set @a:=10,@b:=20;
-- 调用存储过程
call proc4(@a,@b);
select @a,@b;

函数

一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回值。

创建函数

create function 函数名(参数名称 参数类型)
returns 返回值类型
begin
  函数体
end

参数是可选的。

返回值是必须的。

调用函数

select 函数名(实参列表);

删除函数

drop function [if exists] 函数名;

查看函数详细

show create function 函数名;

示例

示例1：无参函数

drop function if exists fun1;
delimiter $
create function fun1()
    returns int
    begin
        -- 定义默认值为0的变量max_id;
        declare max_id int default 0;
        -- 从表 t_user 中查询 id 列的最大值，并将结果存入 max_id
        select max(id) into max_id from t_user;
        return max_id;
    end $
delimiter ;
 
select fun1();

示例2：有参函数

drop function if exists fun2;
delimiter $
create function fun2(v_name varchar(16))
    returns int
    begin
        declare t_id int;
        select id into t_id from t_user where name = v_name;
        return t_id;
    end $
delimiter ;
 
select fun2('王五');

存储过程和函数的区别

  1. 存储过程的关键字为procedure，返回值可以有多个，调用时用call，一般用于执行比较复杂的的过程体、更新、创建等语句。
  2. 函数的关键字为function，返回值必须有一个，调用用select，一般用于查询单个值并返回。
存储过程函数
返回值可以有0个或者多个必须有一个
关键字procedurefunction
调用方式callselect

流程控制语句

准备数据

/*创建表：t_user*/
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id int PRIMARY KEY COMMENT '编号',
sex TINYINT not null DEFAULT 1 COMMENT '性别,1:男,2:女',
name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名'
)COMMENT '用户表';
/*插入数据*/
INSERT INTO t_user VALUES
(1,1,'路人甲Java'),(2,1,'张学友'),(3,2,'王祖贤'),(4,1,'郭富城'),(5,2,'李嘉欣');
SELECT * FROM t_user;
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (a int not null);
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (a int not null,b int NOT NULL );

if函数

if(条件表达式,值1,值2);
/*
if函数有3个参数。
当参数1为true的时候，返回 值1 ，否则返回 值2 。
*/

示例

/*
需求：查询 t_user 表数据，返回：编号、性别（男、女）、姓名。
分析一下：数据库中性别用数字表示的，我们需要将其转换为（男、女），可以使用if函数。
*/
select id 编号,if(sex=1,'男','女') 性别,name 姓名 from t_user;

case结构

case 表达式
when 值1 then 结果1或者语句1（如果是语句需要加分号）
when 值2 then 结果2或者语句2
...
else 结果n或者语句n
end [case] （如果是放在begin end之间需要加case，如果在select后则不需要）

示例

示例1：select中使用

select id 编号,(case sex when 1 then '男' else '女' end) 性别,name 姓名 from t_user;
 
select id 编号,(case sex when 1 then '男' when 2 then '女' end) 性别,name 姓名 from t_user;

示例2：begin end 中使用

delimiter $
drop procedure if exists proc5;
 
create procedure proc5(p_id int, p_sex varchar(10), p_name varchar(10))
begin
    insert into t_user (id, sex, name) values (p_id,(case p_sex when '男' then 1 else 0 end), p_name);
end $
 
delimiter ;
select @p_id:=6,@p_sex:='男',@p_name:='毛毛';
call proc5(@p_id,@p_sex,@p_name);

示例3：函数中使用

drop function if exists fun3;
delimiter $
create function fun3(sex tinyint unsigned)
    returns varchar(10)
begin
    declare v_sex varchar(10);
    case sex when 1 then set v_sex = '男'; when 2 then set v_sex = '女'; end case;
    return v_sex;
end $
delimiter ;
 
select sex,fun3(sex) 性别,name from t_user;
case
when 条件1 then 结果1或者语句1（如果是语句需要加分号）
when 条件2 then 结果2或者语句2
...
else 结果n或者语句n
end [case] （如果是放在begin end之间需要加case，如果是在select后面case可以省略）

if结构

if 条件语句1 then 语句1;
elseif 条件语句2 then 语句2;
...
else 语句n;
end if;

只能使用在begin end之间。

示例

delimiter $
drop procedure if exists prco1;
create procedure prco1()
begin
    declare v_count int default 0;
    declare v_id int default 0;
    select id into v_id from mysql_learn where id = 27;
    if v_id>0 then
        begin
            update mysql_learn set last_update_time = now() where id = v_id;
        end ;
    else
        begin
            insert into mysql_learn (title,create_time) VALUES ('存储过程新增',now());
            set v_count = 1;
        end ;
    end if;
end $
 
delimiter ;
 
call prco1();

循环

  1. while：类似于java中的while循环
  2. repeat：类似于java中的do while循环
  3. loop：类似于java中的while(true)死循环，需要在内部进行控制。
-- 结束本次循环
iterate 循环标签;
-- 退出循环
leave 循环标签;

while循环

[标签:]while 循环条件 do
循环体
end while [标签];

标签：是给while取个名字，标签和 iterate 、 leave 结合用于在循环内部对循环进行控制：

如：跳出循环、结束本次循环。

注意：这个循环先判断条件，条件成立之后，才会执行循环体，每次执行都会先进行判断

示例

-- 根据传入的参数v_count向test1表插入指定数量的数据。
/*删除test1表记录*/
DELETE FROM test1;
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc3;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc3(v_count int)
BEGIN
DECLARE i int DEFAULT 1;
a:WHILE i<=v_count DO
INSERT into test1 values (i);
SET i=i+1;
END WHILE;
END $
/*结束符置为;*/
DELIMITER ;
-- 根据传入的参数v_count向test1表插入指定数量的数据，当插入超过10条，结束。
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc4;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc4(v_count int)
BEGIN
DECLARE i int DEFAULT 1;
a:WHILE i<=v_count DO
INSERT into test1 values (i);
/*判断i=10，离开循环a*/
IF i=10 THEN
LEAVE a;
END IF;
SET i=i+1;
END WHILE;
END $
/*结束符置为;*/
DELIMITER ;
-- 根据传入的参数v_count向test1表插入指定数量的数据，只插入偶数数据。
/*删除test1表记录*/
DELETE FROM test1;
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc5;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc5(v_count int)
BEGIN
DECLARE i int DEFAULT 0;
a:WHILE i<=v_count DO
SET i=i+1;
/*如果i不为偶数，跳过本次循环*/
IF i%2!=0 THEN
ITERATE a;
END IF;
/*插入数据*/
INSERT into test1 values (i);
END WHILE;
END $
/*结束符置为;*/
DELIMITER ;

repeat循环

[标签:]repeat
循环体;
until 结束循环的条件 end repeat [标签];

loop循环

[标签:]loop
循环体;
end loop [标签];

索引

索引是依靠某些数据结构和算法来组织数据，最终引导用户快速检索出所需要的数据。

特点：

  1. 通过数据结构和算法来对原始的数据进行一些有效的组织
  2. 通过这些有效的组织，可以引导使用者对原始数据进行快速检索

索引的本质：

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

磁盘中数据的存取

以机械硬盘来说，先了解几个概念。
扇区：磁盘存储的最小单位，扇区一般大小为512Byte。
磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个（2n）扇区组成，块一般大小一般为4KB。

磁盘读取数据：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是

说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行

5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

mysql中的页

mysql中和磁盘交互的最小单位称为页，页是mysql内部定义的一种数据结构，默认为16kb，相当于4个磁盘块，也就是说mysql每次从磁盘中读取一次数据是16KB，要么不读取，要读取就是16KB，此值可以修改的。

数据检索过程

我们对数据存储方式不做任何优化，直接将数据库中表的记录存储在磁盘中，假如某个表只有一个字段，为int类型，int占用4个byte，每个磁盘块可以存储1000条记录，100万的记录需要1000个磁盘块，如果我们需要从这100万记录中检索所需要的记录，需要读取1000个磁盘块的数据（需要1000次io），每次io需要9ms，那么1000次需要9000ms=9s，100条数据随便一个查询就是9秒，这种情况我们是无法接受的，显然是不行的。

  1. 需要一种数据存储结构：当从磁盘中检索数据的时候能，够减少磁盘的io次数，最好能够降低到一个稳定的常量值。
  2. 需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存多条记录中快速检索出目标数据。

常见的检索算法和数据结构

循环遍历查找

从一组无序的数据中查找目标数据，常见的方法是遍历查询，n条数据，时间复杂度为O(n)，最快需要1次，最坏的情况需要n次，查询效率不稳定。

二分法查找

在一个有序数组中快速定义某一个需要查找的数据。二分查找法定位数据非常快，前提是数据是有序的。

先将一组无序的数据排序（升序或者降序）之后放在数组中，此处用升序来举例说明：用数组中间位置的数据A和需要查找的数据F对比，如果A=F，则结束查找；如果A<F，则将查找的范围缩小至数组中A数据右边的部分；如果A>F，则将查找范围缩小至数组中A数据左边的部分，继续按照上面的方法直到找到F为止。

有序数组

如果我们将mysql中表的数据以有序数组的方式存储在磁盘中，那么我们定位数据步骤是：

  1. 取出目标表的所有数据，存放在一个有序数组中
  2. 如果目标表的数据量非常大，从磁盘中加载到内存中需要的内存也非常大。

步骤取出所有数据耗费的io次数太多，步骤2耗费的内存空间太大，还有新增数据的时候，为了保证数组有序，插入数据会涉及到数组内部数据的移动，也是比较耗时的，显然用这种方式存储数据是不可取的。

链表

链表相当于在每个节点上增加一些指针，可以和前面或者后面的节点连接起来，就像一列火车一样，每节车厢相当于一个节点，车厢内部可以存储数据，每个车厢和下一节车厢相连。

单链表：每个节点中持有只想下一个节点的指针。

双向链表：每个节点中有两个指针，分别指向上一个节点和下一个节点。

优点

  1. 可以快速定位到上一个或下一个节点；
  2. 可以快速删除数据，只需改变指针的指向。

缺点

  1. 无法通过下标随机访问；
  2. 查找数据需从第一个节点开始遍历，不利于数据查找，查找时间和无需数据类型，需要全遍历。

二叉查找树

二叉树是每个结点最多有两个子树的树结构，通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性 ：

1、每个结点都包含一个元素以及n个子树，这里0≤n≤2。

2、左子树和右子树是有顺序的，次序不能任意颠倒，左子树的值要小于父结点，右子树的值要大于父结点

数组[20,10,5,15,30,25,35]使用二叉查找树存储如下：

每个节点上面有两个指针（left,rigth），可以通过这2个指针快速访问左右子节点，检索任何一个数据最多只需要访问3个节点，相当于访问了3次数据，时间为O(logN)，和二分法查找效率一样，查询数据还是比较快的。

但是如果我们插入数据是有序的，如[5,10,15,20,30,25,35]，那么结构就变成下面这样：

二叉树退化为了一个链表结构，查询数据最差就变为了O(N)。

优缺点

  1. 查询数据的效率不稳定，若树左右比较平衡的时，最差情况为O(logN)，如果插入数据是有序的，退化为了链表，查询时间变成了O(N)；
  2. 数据量大的情况下，会导致树的高度变高，如果每个节点对应磁盘的一个块来存储一条数据，需io次数大幅增加，显然用此结构来存储数据是不可取的

平衡二叉树

平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉查找树的两个特性，同时还有一个特性：

它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

平衡二叉树相对于二叉树来说，树的左右比较平衡，不会出现二叉树那样退化成链表的情况，不管怎么插入数据，最终通过一些调整，都能够保证树左右高度相差不大于1。这样可以让查询速度比较稳定，查询中遍历节点控制在O(logN)范围内.

如果数据都存储在内存中，采用AVL树来存储，还是可以的，查询效率非常高。不过我们的数据是存在磁盘中，用过采用这种结构，每个节点对应一个磁盘块，数据量大的时候，也会和二叉树一样，会导致树的高度变高，增加了io次数，显然用这种结构存储数据也是不可取的。

B-树

B杠树 ，B-树在是平衡二叉树上进化来的，前面介绍的几种树，每个节点上面只有一个元素，而B-树节点中可以放多个元素，主要是为了降低树的高度。

一棵m阶的B-Tree有如下特性 ：

  1. 每个节点最多有m个孩子，m称为b树的阶
  2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子
  3. 若根节点不是叶子节点，则至少有2个孩子
  4. 所有叶子节点都在同一层，且不包含其它关键字信息
  5. 每个非终端节点包含n个关键字（健值）信息
  6. 关键字的个数n满足：ceil(m/2)-1 ⇐ n ⇐ m-1
  7. ki(i=1,…n)为关键字，且关键字升序排序
  8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支。
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个键将数据划分成的三个范围域，对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

  1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
  2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2
  3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
  4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2
  5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
  6. 在磁盘块8中的关键字列表中找到关键字29。

缺点

B-不利于范围查找，比如上图中我们需要查找[15,36]区间的数据，需要访问7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，范围查找也是我们经常用到的，所以b-树也不太适合在磁盘中存储需要检索的数据。

B+树

特征

  1. 每个结点至多有m个子女
  2. 除根结点外,每个结点至少有[m/2]个子女，根结点至少有两个子女
  3. 有k个子女的结点必有k个关键字
  4. 父节点中持有访问子节点的指针
  5. 父节点的关键字在子节点中都存在（如上面的1/20/35在每层都存在），要么是最小值，要么是最大值，如果节点中关键字是升序的方式，父节点的关键字是子节点的最小值
  6. 最底层的节点是叶子节点
  7. 除叶子节点之外，其他节点不保存数据，只保存关键字和指针
  8. 叶子节点包含了所有数据的关键字以及data，叶子节点之间用链表连接起来，可以非常方便的支持范围查找

B+树和B-树的区别

  1. b+树中一个节点如果有k个关键字，最多可以包含k个子节点（k个关键字对应k个指针）；而b-树对应k+1个子节点（多了一个指向子节点的指针）；
  2. b+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，而b-树还存储了数据，这样同样大小情况下，b+树可以存储更多的关键字；
  3. b+树叶子节点中存储了所有关键字及data，并且多个节点用链表连接，从上图中看子节点中数据从左向右是有序的，这样快速可以支撑范围查找（先定位范围的最大值和最小值，然后子节点中依靠链表遍历范围数据）。

B+树和B-树的如何选择

  1. B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，BTree查找某个关键字的效率更高。
  2. 由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。
  3. 由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

Mysql的存储引擎和索引

mysql内部索引是由不同的引擎实现的，主要说一下InnoDB和MyISAM这两种引擎中的索引，这两种引擎中的索引都是使用b+树的结构来存储的。

InnoDB中的索引

Innodb中有2种索引：主键索引（聚集索引）、辅助索引（非聚集索引） 。

主键索引：每个表只有一个主键索引，b+树结构，叶子节点同时保存了主键的值以及数据记录，其他节点只存储主键的值。

辅助索引：每个表可以有多个，b+树结构，叶子节点保存了索引字段的值以及主键的值，其他节点只存储索引指端的值。

MyISAM引擎中的索引

B+树结构，MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

InnoDB数据检索过程

如果需要查询id=14的数据，只需要在左边的主键索引中检索就可以了。
如果需要搜索name=‘Ellison’的数据，需要2步：

  1. 先在辅助索引中检索到name=‘Ellison’的数据，获取id为14
  2. 再到主键索引中检索id为14的记录

辅助索引这个查询过程在mysql中叫做回表。

MyISAM数据检索过程

  1. 在索引中找到对应的关键字，获取关键字对应的记录的地址
  2. 通过记录的地址查找到对应的数据记录

索引管理

索引分类

聚集索引

每个表有且一定会有一个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采用B+树结构保存在文件中，叶子节点存储主键的值以及对应记录的数据，非叶子节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会自动给每条记录添加一个隐藏的rowid字段（默认4个字节）作为主键，用rowid构建聚集索引。聚集索引在mysql中又叫主键索引。

非聚集索引(辅助索引)

也是b+树结构，不过有一点和聚集索引不同，非聚集索引叶子节点存储字段（索引字段）的值以及对应记录主键的值，其他节点只存储字段的值（索引字段）。每个表可以有多个非聚集索引。

mysql中非聚集索引分类

单列索引：即一个索引只包含一个列。

多列索引(复合索引)：即一个索引包含多个列。

唯一索引：即一个索引包含多个列。

索引过程

索引管理

创建索引

create [unique] index 索引名称 on 表名(列名[(length)]);
alter 表名 add [unique] index 索引名称 on (列名[(length)]);

如果字段是char、varchar类型，length可以小于字段实际长度，如果是blog、text等长文本类型，必须指定length。
[unique]：中括号代表可以省略，如果加上了unique，表示创建唯一索引。
如果table后面只写一个字段，就是单列索引，如果写多个字段，就是复合索引，多个字段之间用逗号隔开。

删除索引

drop index 索引名称 on 表名;

查看索引

show index from 表名;

关系图谱

反向链接