MySQL | Kang`Blog

一、数据查询语言DQL

select .. from .. where .. group by .. having .. order by .. limit ..;
顺序:5 1 2 3 4 6 7

简单查询

语法格式

1	select 字段名1,字段名2,... from 表名;

查询一个字段

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+

查询多个字段

mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

查询全部字段

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

计算员工的年薪

mysql> select ename,sal*12 from emp;
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

将查询出来的字段重命名

mysql> select ename,sal*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

其中as也可以省略，如果要替换成中文时，要用单引号括起来。

mysql> select ename,sal*12 '年薪' from emp;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

条件查询

语法格式

1	select 字段1,字段2,... from 表名 where 条件;

等号操作符

找出SMITH的工资
mysql>  select sal from emp where ename = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

< >操作符

找出emp表中工资大于2500的员工
mysql> select ename,sal from emp where sal>2500;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

找出emp表中工资小于1000的员工
mysql> select ename,sal from emp where sal<1000;
+-------+--------+
| ename | sal    |
+-------+--------+
| SMITH | 800.00 |
| JAMES | 950.00 |
+-------+--------+

找出emp表中工资不等于3000的员工，<>与!=都是不等于的意思
mysql> select ename,sal from emp where sal<>3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+

between…and…操作符

找出工资在2000到3000之间的员工，between...and... 是闭区间
mysql> select ename,sal from emp where sal between 2000 and 3000;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
+-------+---------+

is null

数据库中null不是一个值，代表为空，不能用=进行判断

找出没有津贴的员工或津贴为null
mysql> select ename,comm from emp where comm is null;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+

找出津贴不为null的员工
mysql> select ename,comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+

and or

and 并且
or 或

找出工作岗位为salesman或manager的员工
mysql> select ename,job from emp where job='manager' or job='salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

找出部门编号是20或30中薪资大于1000的员工
mysql> select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+

in/not in

in不是一个区间，括号里指的是具体的某几个值

找出工作岗位为salesman或manager的员工
mysql> select ename,job,deptno from emp where job in ('SALESMAN', 'MANAGER');
+--------+----------+--------+
| ename  | job      | deptno |
+--------+----------+--------+
| ALLEN  | SALESMAN |     30 |
| WARD   | SALESMAN |     30 |
| JONES  | MANAGER  |     20 |
| MARTIN | SALESMAN |     30 |
| BLAKE  | MANAGER  |     30 |
| CLARK  | MANAGER  |     10 |
| TURNER | SALESMAN |     30 |
+--------+----------+--------+

like模糊查询

% 任意多个字符
_任意一个字符

找出名字中含有O的员工
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+

找出名字中第二个字母是A的员工
mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

1
2
3

找出名字中带有下划线的员工
\转义字符
select ename from emp where ename='%\_%';

排序

代码格式

1
2
3

select 字段 from 表名 order by 字段 升降序;
默认升序排列，asc表示升序，desc表示降序
越靠前的字段越能起主导作用，如多种排序时，只有当前一种排序的值相同时，相同值的数据再按照下一个排序规则进行排序

按照工资进行排序，默认升序
mysql> select ename,sal from emp order by sal asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

按照工资的降序排列，当工资相同时，再按照名字的升序排
mysql> select ename,sal from emp order by sal desc,ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

找出工作岗位是SALESMAN的员工，并且按照员工薪资的降序进行排列
mysql> select ename,job,sal from emp where job in ('salesman') order by sal desc;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+

分组函数

分组函数一共5个，都是对某一组数据进行操作
分组函数自动忽略空
分组函数不能应用到where语句中
count 计数
sum 求和
avg 平均
max 最大值
min 最小值

找出工资总和
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

找出最高工资
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

找出平均工资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

找出总人数
mysql> select count(*) from emp; // 统计总记录条数，和字段无关
mysql> select count(ename) from emp; // 统计ename中不为null的字段
+----------+
| count(*) |
+----------+
|       14 |
+----------+

分组函数自动忽略空
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

mysql> select sum(sal),max(sal),min(sal),avg(sal) from emp;
+----------+----------+----------+-------------+
| sum(sal) | max(sal) | min(sal) | avg(sal)    |
+----------+----------+----------+-------------+
| 29025.00 |  5000.00 |   800.00 | 2073.214286 |
+----------+----------+----------+-------------+

group by 和 having

group by:按照某个字段或者某些字段进行分组；当一条语句中有group by时，select后面只能跟分组函数和参与分组的字段
having:对分组之后的数据进行在此过滤

找出每个工作岗位的最高工资
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

获取工资大于平均工资的员工
mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

获取每个岗位的平均工资
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

找出每个部门不同职位的最高工资
mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

找出每个部门的最高工资，只显示大于2900的数据
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>2900; // 效率低
mysql> select deptno,max(sal) from emp where sal>2900 group by deptno; // 效率相对高
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     10 |  5000.00 |
+--------+----------+

找出每个部门的平均薪资，只显示大于2000的数据
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     10 | 2916.666667 |
+--------+-------------+

distinct

查询结果集的去重

mysql> select distinct deptno,job from emp;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

计算岗位的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

连接查询

在实际开发中，大部分情况下都不能从单表中查询数据，一般都是多张表联合查询取出最终结果。

根据表的连接方式来划分，包括
- 内连接
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接
  - 左外连接
  - 右外连接
- 全连接

关于表的别名
mysql> select e.ename,d.dname from emp e,dept d;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
| ALLEN  | SALES      |
| ALLEN  | RESEARCH   |
| ALLEN  | ACCOUNTING |
...
56 rows in set (0.01 sec)

怎样避免笛卡尔积现象？加过滤条件
会减少记录的匹配次数吗？不会，次数不变，只不过显示的是有效记录

内连接inner

等值连接

查询每个员工的部门名称，要求显示员工名和部门名
mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

非等值连接

找出每个员工的工资等级，要求显示员工名、工资、工资等级
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

自连接

特点：一张表看做两张表，自己连接自己

找出每个员工的上级领导，显示员工名和对应的领导
mysql> select e.ename as '员工名',m.ename as '领导名' from emp e join emp m on e.mgr=m.empno;
+--------+--------+
| 员工名 |  领导名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

外连接outer

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张是副表，主要查询主表的内容，捎带查讯副表的数据；如果副表中的数据没有和主表中的数据匹配时，副表自动模拟出null与之匹配

左外连接：左边这张表为主表
右外连接：右边这张表为主表

左连接，比内连接多了king的一行
mysql> select e.ename as '员工名',m.ename as '领导名' from emp e left join emp m on e.mgr=m.empno;
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

找出没有员工的部门
mysql> select d.* from emp e right join dept d on e.deptno=d.deptno where e.empno is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

三张表进行连接

找出每一个员工的部门名称以及工资等级
mysql> select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+

找出每一个员工的部门名称、工资等级、以及上级领导
mysql> select e.ename,d.dname,s.grade,m.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp m on e.mgr=m.empno;
+--------+------------+-------+-------+
| ename  | dname      | grade | ename |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+

子查询

select语句中嵌套select语句，被嵌套的select语句就是子查询

where后面嵌套子查询

mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

from后面嵌套子查询

找出每个部门平均薪水的薪资等级
mysql> select avg.*,grade from (select deptno,avg(sal) as avgsal from emp group by deptno) avg join salgrade s on avg.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

找出每个部门

select后面嵌套子查询

union

可以将查询结果集相加

找出工作岗位是salesman和manager的员工
mysql> select ename,job from emp where job='SALESMAN' union select ename,job from emp where job='MANAGER';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
+--------+----------+

limit

分页查询，mysql中特有的
limit取结果集中部分数据

1
2
3

limit startIndex, length
// startIndex:表示起始位置
// length:取几个

取出工资前五名的员工
mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

找出工资排名第四到第九名的员工
mysql> select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

二、数据操纵语言DML

insert

insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,,值3,...);
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','class1','1998-01-01');
// 前面字段省略时，后面要按照建表顺序写
insert into t_student values(1,'zhangsan','1','class1','1998-01-01');

将查询结果插入到一张表中
insert into 表名 select * from 表名
mysql> insert into t_s2 select no,name,birth from t_student;
mysql> select * from t_s2;
+------+----------+------------+
| no   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1998-01-01 |
|    1 | zhangsan | 1998-01-01 |
+------+----------+------------+

update

update 表名 set 字段名1=值1,字段名2=值2... where 条件

// 更改部门10的dname和loc
mysql> update dept set loc='BEIJING',dname='RENSHIBU' where DEPTNO=10;
mysql> select * from dept;
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     10 | RENSHIBU   | BEIJING |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+

delete

delete from 表名 where 条件
注意：没有条件，全部删除

// 删除部门10的数据
mysql> delete from dept where deptno=10;
mysql> select * from dept;
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
// 删除全部数据
mysql> delect from dept;
mysql> select * from dept;
Empty set (0.01 sec)

// 删除大表中的数据。表被截断，不可回滚。永久丢失。
truncate table 表名;
mysql> truncate table emp;
mysql> select * from emp;

三、数据定义语言DDL

SQL中数据类型
|类型||
|:–:|—|
|int|整数型|
|bigint|长整型|
|float|浮点型|
|char|定长字符串|
|varchar|可变长字符串|
|date|日期类型|
|BLOB|二进制大对象|
|CLOB|字符大对象|

create

create table 表名(
    字段名 数据类型,
    字段名 数据类型,
    ...
);

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,
    classno varchar(255),
    birth char(10)
);

表的复制
create table 表名 as select语句;
mysql> create table t_s2 as select no,name,birth from t_student;
mysql> select * from t_s2;
+------+----------+------------+
| no   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1998-01-01 |
+------+----------+------------+

drop

1
2
3

drop table if exists 表名;

drop table if exists t_student;

alter

四、约束

非空约束(not null):约束的字段不能为空
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为空，也不能重复
外键约束(foreign key):

非空约束

create table t_user(
    id int,
    username varchar(255) not null,
    password varchar(255)
);

mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(255) | NO   |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

// 错误
mysql> insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

唯一性约束

唯一性约束修饰的字段具有唯一性，不能重复，但是可以为null

// 列级约束，username不能重复
mysql> create table t_user(
    id int,
    username varchar(255) unique
);

mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(255) | YES  | UNI | NULL    |       |
+----------+--------------+------+-----+---------+-------+

// 唯一性约束,表级约束，将usercode和username联合起来不能重复
create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username)
);
mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| usercode | varchar(255) | YES  | MUL | NULL    |       |
| username | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

主键约束

create table t_user(
    id int primary key,
    username varchar(255),
    password varchar(255)
);
mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| username | varchar(255) | YES  |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

mysql提供主键值自增
create table t_user(
    id int primary key auto_increment,
    username varchar(255)
);
mysql> desc t_user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| username | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

外键约束

// 主表
create table t_calss(
    cno int,
    cname varchar(255),
    primary key(cno)
);
// 子表
create table t_student(
    sno int,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_calss(cno)
);

外键可以为null
外键中被引用的字段可以不是主键，但是必须具有唯一性。

五、存储引擎(存储方式)

// 完整的建表语句
create table `t_user`(
    `id` int(11) default null
) engine=InnoDB default charset=utf-8;

// 查看所有存储引擎
mysql> show engines \G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

MyISAM

最常用的引擎
结构，使用三个文件表示每个表
- 格式文件 - 存储表结构的定义（mytable.frm）
- 数据文件 - 存储表行的内容（mytable.MYD）
- 索引文件 - 存储表上索引（mytable.MYI）
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间
不支持事务语言

InnoDB

默认的引擎
结构特点
- 每个InnoDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 数据库崩溃后提供自动回复
- 支持级联删除和级联更新

MEMORY

不支持事务，断点就丢失数据
查询速度快

六、事务（Transaction）

保证多条语句同时成功
只用于DML语句

事务特性

ACID四大特性

A原子性：一个事务要么全部提交成功，要么全部失败回滚，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
C一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态
I隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
D持久化：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

第一级别：读未提交（read uncommitted）
- 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
- 存在脏读现象
第二级别：读已提交（read committed）
- 对方事务提交后的数据我们当前事务可以读取到
- 读已提交存在的问题：不可重复读，只要其他人更改了数据，你读到的数据就会改变
第三级别：可重复读（repeatable read）
第四级别：序列化读/串行化读
- 需要事务排队

oracle数据库默认的隔离级别是：读已提交
mysql数据库默认的隔离级别是：可重复读

演示事务

mysql事务默认情况下是自动提交的。
怎样关闭自动提交？ start transaction

七、索引

// 创建索引
create index 索引名称 on 表名(字段名);
// 删除索引
drop index 索引名称 on 表名;

什么时候考虑给字段添加索引

数据量庞大。
该字段很少的DML操作。（因为字段进行修改操作，索引也需要进行维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）

注：主键和具有unique约束的字段会自动添加索引

// type为all，查询了14次
mysql> explain select sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
// 创建索引
mysql> create index emp_sal_index on emp(sal);
// type为ref，查询了1次
mysql> explain select sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+

索引实现原理
底层实现：B+tree
通过B tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据。

1
2
3

select ename from emp where ename='SMITH';
通过索引转换为
select ename from emp where 物理地址=查找到的地址;

索引分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

索引何时失效？当使用模糊查询时，第一个字符是%， ename like ‘%A%’;

八、数据库设计三范式

码：能够唯一确定一条记录的一个/多个属性。码包括主码和候选码。任意一个候选码也能做主键。
第一范式：任何一张表都应该有主键，并且每一个字段具有原子性（不可再分）
第二范式：建立在第一范式之上，所有非主属性完全依赖码，目的是消除非主属性对码的部分依赖
- 多对多？三张表，关系表有两个外键
第三范式：建立在第二范式基础上，非主属性不能传递依赖于码（不要产生传递依赖）
- 一对多？两张表，多的表加外键
BC范式：消除主属性对码的传递和函数依赖
一对一
- 主键共享
  - 既是主键也是外键
- 外键唯一
  - 一张表新建一列作为外键

索引

索引结构

B TREE:多路平衡搜索树，一棵m叉的BTree特征如下

树中每个节点最多包含m个孩子
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1>

B+TREE结构

n叉B+Tree最多包含n个key，而BTree最多含有n-1个key
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
所有的非叶子节点都可以看作是key的索引部分

B+Tree

MySQL中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，提高区间访问的性能

MySQL中的B+Tree

索引语法

创建索引

create [unique\fulltext\spatial] index index_name [using index_type] on table_name(index_col_name,...);

如：
mysql> create index idx_emp_ename on emp(ename);

查看索引

show index from table_name;

如:
mysql> show index from emp;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY       |            1 | EMPNO       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_emp_ename |            1 | ENAME       | A         |          14 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

删除索引

drop index index_name on table_name;

如：
mysql> drop index idx_emp_ename on emp;

alter命令

添加一个主键，索引值必须唯一，且不能为空
alter table table_name add primary key(column_list);
索引的值必须是唯一的（null可以出现多次）
alter table table_name add unique index_name(colunm_list);
添加普通索引，索引值可以出现多次
alter table table_name add index index_name(colunm_list);
索引为fulltext，用于全文索引
alter table table_name add fulltext index_name(clounm_list);

索引设计原则

对查询频次较高，且数据量比较大的表建立索引
索引字段的选择，最佳候选应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合
使用唯一索引，区分度越高，使用索引的效率越高
索引不是越多越好
使用段索引
利用最左索引，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组合该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建符合索引
create index idx_name_email_status on table_name(name,email,status);
相当于
对name创建了索引
对name，email创建了索引
对name，email，status创建了索引

存储过程和函数

概述

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程和函数的区别在于函数必须有返回值，而存储过程没有

创建存储过程

create procedure procedure_name ([proc_parameter[,...]])
begin
    -- SQL语句
end;

未完

优化SQL步骤

查看SQL执行频率

show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 1     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+

定位低效率执行的SQL

慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句
show processlist：慢查询日志在查询结束后才有记录，所以在反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时查看SQL的执行情况，同时对一些锁表操作进行优化

show processlist;
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host           | db   | Command | Time   | State                  | Info             |
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost      | NULL | Daemon  | 382043 | Waiting on empty queue | NULL             |
|  8 | root            | localhost:3960 | NULL | Query   |      0 | init                   | show processlist |
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+

explain分析执行计划

mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

id

id值相同时，从上到下依次执行
id值不同时，从大到小依次执行

select_type

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或union
PRIMARY	查询中若包含任何复杂的子查询，最外层被标记为该标识
SUBQUERY	在select或where列表中包含了子查询
DERIVED	在from列表中包含的子查询；MySQL会递归的执行这些语句，把结果放在临时表中
UNION
UNION RESULT

table

key

possible_key：显示可能应用在这张表的索引，一个或多个
key：实际使用的索引，如果为null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度

rows

扫描行的数量

extra

其他的额外的执行计划信息

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引排序进行读取，成为“文件排序”
using temporary	使用了临时表存储中间结果，MySQL在对查询结果排序时使用临时表。常用语orderby和groupby
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错

show profile分析SQL

索引的使用

避免索引失效

全值匹配，对索引中所有列都指定具体值

1	select * from emp where ename='SMITH' and sal='800' and deptno='20';

最左前缀法则
- 如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列
范围查询右边的列，索引失效
不要再索引列上进行运算操作，否则索引将失效
字符串不加单引号，造成索引失效
尽量使用覆盖索引，避免使用select *
用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
用 % 开头的like模糊查询，索引失效
- 如果仅仅是尾部模糊匹配，索引不会失效。如果头部模糊匹配，索引失效
- 通过覆盖索引解决
如果Mysql评估使用索引比全表更慢，则不使用索引
is null和is not null，符合上一规则
in 走索引，not in 不走索引
单列索引和符合索引
- 尽量使用符合索引，而少使用单列索引

查看索引使用情况

1	show status like 'Handler_read%';

SQL优化

大量导入数据

当使用load命令导入数据的时候，适当的设置可以提高导入的效率

按照主键顺序导入
关闭唯一性校验
- 导入数据前set unique_checks=0，关闭唯一性校验，导入结束后，set unique_checks=1，恢复唯一性校验
手动提交事务
- 再导入前执行set autocommit=0，关闭自动提交，导入结束后，再执行set autocommit=1，打开自动提交

优化insert语句

同时对同一张表插入多行数据时，应该尽量使用多个值表的insert语句，可以大大缩减客户端与数据库之间的连接、关闭等消耗

1	insert int table_name values(1,'smith'),(2,'tom');

在事务中进行数据插入

start transaction;
insert into table_name values(1,'smith');
insert into table_name values(2,'tom');
commit;

数据有序插入

按照主键顺序插入

order by语句优化

尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by 的顺序和索引顺序相同，并且order by的字段都是升序或都是降序，否则需要额外的操作

group by语句优化

与order by类似，也可以通过创建索引来优化分组

优化嵌套查询

使用多表联查替换子查询

1	select ename from emp e,dept d where e.deptno=d.deptno;

优化 or 条件

对于包含or的查询子句，如果要利用索引，则 or 之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引

对于or，可以用nuion替换

1	select ename from emp where id='1' union select ename from emp where id='10';

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能
但是对于limit 2000000,10，此时需要MySQL排序前2000010条记录，仅仅返回最后10条，其他记录丢弃，直接查询代价非常大

优化1
- 在索引上完成排序分页操作，最后根据主键关联回原来表查询所需要的其他列的内容
优化2
- 该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询

1	select * from emp where id>2000000 limit 10;

应用优化

使用数据库连接池
减少对MySQL的访问
- 避免对数据的重复检索
- 增加cache层
- 通过mysql的主从复制，实现读写分离
- 采用分布式数据库架构

锁问题

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，除传统的计算资源（如CPU、RAM、I/O）的争用之外，数据也是一种供许多用户共享的资源

锁分类

对数据操作的粒度分
- 表锁：操作时，锁定整个表
- 行锁：操作时，会锁定当前操作行
对数据操作的类型分
- 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁

Mysql锁

MyISAM表锁

加表锁

MyISAM在执行查询语句(select)前，会自动给涉及的所有表加锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程不需要用户干预

1
2
3

// 显示加表锁语法
lock table table_name read; // 读锁
lock table table_name write; // 写锁

读锁

对于本客户端，只能读和更新当前表的数据，无法读取其他表的数据
对于其他客户端，只能读该表的数据，无法更新该表的内容(阻塞状态)

写锁

对于本客户端，可读可写
对于其他客户端，无法读和写(阻塞状态)

查看锁的争用

mysql> show open tables;
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| mysql              | check_constraints         |      0 |           0 |
| mysql              | column_type_elements      |      0 |           0 |
| mysql              | slave_master_info         |      0 |           0 |
| mysql              | foreign_keys              |      0 |           0 |
| mysql              | columns                   |      0 |           0 |
...

mysql> show status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 41     |
| Table_locks_waited    | 0     |
+-----------------------+-------+

InnoDB行锁

开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度高

避免

无索引行锁升级为表锁
间隙锁

常用SQL技巧

正则表达式的使用

1 2	// 匹配name中以s开头的行 select * from emp where name regexp '^s';

符号	含义
^	在字符串开始处进行匹配
$	在字符串末尾处进行匹配
.	匹配任意单个字符，包括换行符
[…]	匹配括号内的任意字符
[^…]	匹配除了括号内的其他任意字符