MySQL常用语句

命令是以分号结尾！！！

登录：mysql -uroot -p+密码

退出：exit

查看mysql中有哪些数据库：show databases ;（注意以分号结尾）

使用一个数据库：use test ; （表示使用名为test的数据库）

创建一个数据库：create database kkk; (创建一个叫做kkk的数据库)

查看一个数据库下有哪些表：show tables ; ;

导入一个数据：source +数据的位置;

查看一个表中的数据：select * from +表名;

查看某一个字段名：select 字段名 from +表名；

查看多个字段名：select 字段名，字段名… +表名；

不看表的数据，只看表的结构：desc +表名; （describe的缩写）

查看MySQl的版本号：select version（）;

查看当前数据库：select database（）;

终止一条语句：\c

退出MySQL也可以使用ctrl+c

给字段名取别名：使用as关键字（也可以将as去掉）（注意，只是将显示的时候改为as后的字段名，不会改表中的字段名，select 语句永远不会进行更改操作）

条件查询格式： select +字段名 from +­ 表名 where +条件；

mysql> select ename,sal as '工资在2000和3000之间的人' from emp where sal between 2000 and 3000;

+-------+--------------------------+

| ename | 工资在2000和3000之间的人 |

+-------+--------------------------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| FORD | 3000.00 |

通过条件查找可以查出一个具体的一行

mysql> select ename,sal from emp where ename='smith' ;

+-------+--------+

| ename | sal |

+-------+--------+

| SMITH | 800.00 |

+-------+--------+

运算符 in

mysql> select ename,empno,sal,job from emp where job in('manager','analyst');

+-------+-------+---------+---------+

| ename | empno | sal | job |

+-------+-------+---------+---------+

| JONES | 7566 | 2975.00 | MANAGER |

| BLAKE | 7698 | 2850.00 | MANAGER |

| CLARK | 7782 | 2450.00 | MANAGER |

| SCOTT | 7788 | 3000.00 | ANALYST |

| FORD | 7902 | 3000.00 | ANALYST |

排序：

指定降序排序

Select 字段名，字段名 from 表名 order by 字段名 desc；

指定升序排序（默认是升序排序）

Select 字段名，字段名 from 表名 order by 字段名 asc；

可以两个字段或者多个字段进行排序

（规则，只有当第一个排序相同的时候，才会使用的二种排序方法进行排序）

mysql> select ename,sal from emp order by sal asc,ename asc;

+--------+---------+

| ename | sal |

+--------+---------+

| SMITH | 800.00 |

| JAMES | 950.00 |

| ADAMS | 1100.00 |

| MARTIN | 1250.00 |

| WARD | 1250.00 |

| MILLER | 1300.00 |

| TURNER | 1500.00 |

| ALLEN | 1600.00 |

| CLARK | 2450.00 |

| BLAKE | 2850.00 |

| JONES | 2975.00 |

| FORD | 3000.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

+--------+---------+

排序也可以通过字段的序号进行说明：

mysql> select ename,sal from emp order by 2 asc,1 asc;

+--------+---------+

| ename | sal |

+--------+---------+

| SMITH | 800.00 |

| JAMES | 950.00 |

| ADAMS | 1100.00 |

| MARTIN | 1250.00 |

| WARD | 1250.00 |

| MILLER | 1300.00 |

| TURNER | 1500.00 |

| ALLEN | 1600.00 |

| CLARK | 2450.00 |

| BLAKE | 2850.00 |

| JONES | 2975.00 |

| FORD | 3000.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

+--------+---------+

和上面的结果是相同的。

关键字的顺序不能改变：

Select

…

From

…

Where

…

Order by

…

这个顺序的执行顺序是：

第一步：from

第二步：where

第三步：select

第四步：order by（排序总是在最后执行的）

数据处理函数（单行处理函数）：

单行处理函数的特点是一个输入对应输出一个

和单行处理函数的相对的是多行处理函数（多行处理函数的特点：多个输入，一个输出）

Lower 转换为小写

Select lower（ename）from emp；

Upper 转化为大写

Select upper（ename）from emp；

Substr 取子串（substr（被截取的字符串，起始下标，截取长度））注意，起始下标是从一开始的

mysql> select ename from emp where ename like 'a%';

+-------+

| ename |

+-------+

| ALLEN |

| ADAMS |

+-------+

2 rows in set (0.00 sec)

mysql> select ename from emp where substr(ename,1,1)='A';

+-------+

| ename |

+-------+

| ALLEN |

| ADAMS |

+-------+

Length 取长度

mysql> select ename from emp where length(ename)=5;

+-------+

| ename |

+-------+

| SMITH |

| ALLEN |

| JONES |

| BLAKE |

| CLARK |

| SCOTT |

| ADAMS |

| JAMES |

+-------+

mysql> select ename from emp where length(ename);

+--------+

| ename |

+--------+

| SMITH |

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |

| ADAMS |

| JAMES |

| FORD |

| MILLER |

+--------+

mysql> select length(ename) namelength from emp;

+------------+

| namelength |

+------------+

| 5 |

| 5 |

| 4 |

| 5 |

| 6 |

| 5 |

| 5 |

| 5 |

| 4 |

| 6 |

| 5 |

| 5 |

| 4 |

| 6 |

+------------+

Trim 去空格

mysql> select ename from emp where ename=trim(' king');

+-------+

| ename |

+-------+

| KING |

+-------+

Str_to_date 将字符串转化为日期

Date_format 格式化日期

Format 设置千分位

Round 四舍五入

Rand() 生成随机数

Ifnull 可将null转化成一个具体的值

Concat 进行字符串的拼接

Select concat（empno，ename） from emp；

首写字母为大写，其余为小写

mysql> select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)-1))) result from emp;

+--------+

| result |

+--------+

| Smith |

| Allen |

| Ward |

| Jones |

| Martin |

| Blake |

| Clark |

| Scott |

| King |

| Turner |

| Adams |

| James |

| Ford |

| Miller |

+--------+

当select后面跟的是字面值时：

mysql> select 'smith' from emp;

+-------+

| smith |

+-------+

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

| smith |

+-------+

Case … when… then… when … then … else …end

mysql> select ename,sal,job, (case job when 'salesman' then sal*1.2 when 'manager' then sal*10 else sal end) result from emp;

+--------+---------+-----------+----------+

| ename | sal | job | result |

+--------+---------+-----------+----------+

| SMITH | 800.00 | CLERK | 800.00 |

| ALLEN | 1600.00 | SALESMAN | 1920.00 |

| WARD | 1250.00 | SALESMAN | 1500.00 |

| JONES | 2975.00 | MANAGER | 29750.00 |

| MARTIN | 1250.00 | SALESMAN | 1500.00 |

| BLAKE | 2850.00 | MANAGER | 28500.00 |

| CLARK | 2450.00 | MANAGER | 24500.00 |

| SCOTT | 3000.00 | ANALYST | 3000.00 |

| KING | 5000.00 | PRESIDENT | 5000.00 |

| TURNER | 1500.00 | SALESMAN | 1800.00 |

| ADAMS | 1100.00 | CLERK | 1100.00 |

| JAMES | 950.00 | CLERK | 950.00 |

| FORD | 3000.00 | ANALYST | 3000.00 |

| MILLER | 1300.00 | CLERK | 1300.00 |

+--------+---------+-----------+----------+

14 rows in set (0.00 sec)

这种语句相当于if语句

分组处理函数：

Avg（）；一个组的平均值

Sum（）；一个组的和

Min（）；一个组的最小值

Max（）；一个组的最大值

Count（）；一个组的元素个数

分组函数必须要在分组后才能被使用

Select

…

From

…

Where

…

Group by

…

Order by

…

这个顺序不能发生改变

执行的顺序为

先执行from 在执行where 然后是group by 再是select 最后是order by

Avg（）；一个组的平均值

mysql> select job,avg(sal) '每个部门的平均工资' from emp group by job;

+-----------+--------------------+

| job | 每个部门的平均工资 |

+-----------+--------------------+

| ANALYST | 3000.000000 |

| CLERK | 1037.500000 |

| MANAGER | 2758.333333 |

| PRESIDENT | 5000.000000 |

| SALESMAN | 1400.000000 |

+-----------+--------------------+

Sum（）；一个组的和

mysql> select sum(sal) from emp;

+----------+

| sum(sal) |

+----------+

| 29025.00 |

+----------+

没有进行分组的时候，会将整个表格当作一个组

mysql> select job, sum(sal) '每个部门需要赋予的工资'from emp group by job;

+-----------+------------------------+

| job | 每个部门需要赋予的工资 |

+-----------+------------------------+

| ANALYST | 6000.00 |

| CLERK | 4150.00 |

| MANAGER | 8275.00 |

| PRESIDENT | 5000.00 |

| SALESMAN | 5600.00 |

+-----------+------------------------+

Min（）；一个组的最小值

mysql> select ename,job,min(sal) '每个部门最低工资员工' from emp group by job;

+-------+-----------+----------------------+

| ename | job | 每个部门最低工资员工 |

+-------+-----------+----------------------+

| SCOTT | ANALYST | 3000.00 |

| SMITH | CLERK | 800.00 |

| JONES | MANAGER | 2450.00 |

| KING | PRESIDENT | 5000.00 |

| ALLEN | SALESMAN | 1250.00 |

+-------+-----------+----------------------+

Max（）；一个组的最大值

mysql> select job,max(sal) '每个部门最高工资员工' from emp group by job;

+-----------+----------------------+

| job | 每个部门最高工资员工 |

+-----------+----------------------+

| ANALYST | 3000.00 |

| CLERK | 1300.00 |

| MANAGER | 2975.00 |

| PRESIDENT | 5000.00 |

| SALESMAN | 1600.00 |

+-----------+----------------------+

Count（）；一个组的元素个数

mysql> select job,count(job) '每个部门的人数' from emp group by job;

+-----------+----------------+

| job | 每个部门的人数 |

+-----------+----------------+

| ANALYST | 2 |

| CLERK | 4 |

| MANAGER | 3 |

| PRESIDENT | 1 |

| SALESMAN | 4 |

+

Group by中可以填多个字段名

mysql> select deptno,job,max(sal) '每个部门和每个工作中最高工资' from emp group by deptno,job order by sal asc;

+--------+-----------+------------------------------+

| deptno | job | 每个部门和每个工作中最高工资 |

+--------+-----------+------------------------------+

| 20 | CLERK | 1100.00 |

| 30 | CLERK | 950.00 |

| 10 | CLERK | 1300.00 |

| 30 | SALESMAN | 1600.00 |

| 10 | MANAGER | 2450.00 |

| 30 | MANAGER | 2850.00 |

| 20 | MANAGER | 2975.00 |

| 20 | ANALYST | 3000.00 |

| 10 | PRESIDENT | 5000.00 |

+--------+-----------+------------------------------+

在分完组之后，可以使用having对分完组后的数据进行过滤，having不能单独使用，只能和group by一同出现，having不能替代where

Select

…

From

…

Group by

…

Having

…

mysql> select deptno,avg(sal) '平均工资' from emp group by deptno having avg(sal)>2500;

+--------+-------------+

| deptno | 平均工资 |

+--------+-------------+

| 10 | 2916.666667 |

+--------+-------------+

mysql> select deptno,max(sal) '最高工资' from emp group by deptno having max(sal)>3000;

+--------+----------+

| deptno | 最高工资 |

+--------+----------+

| 10 | 5000.00 |

+--------+----------+

当可以使用where和having进行解决问题时都是用where，where执行后的效率较高

当where完成不了的时候在使用having进行过滤。

当在筛选语句中出现需要使用分组函数的时候，就不能使用where，原因在于where在分组之前进行，这时只能使用having进行筛选

Where和having同时使用：

mysql> select job,avg(sal) result from emp where job <> 'manager' group by job having avg(sal) > 1500 order by sal desc;

+-----------+-------------+

| job | result |

+-----------+-------------+

| PRESIDENT | 5000.000000 |

| ANALYST | 3000.000000 |

+-----------+-------------+

使用distinct关键字进行去重

mysql> select distinct job ,deptno from emp;

+-----------+--------+

| job | deptno |

+-----------+--------+

| CLERK | 20 |

| SALESMAN | 30 |

| MANAGER | 20 |

| MANAGER | 30 |

| MANAGER | 10 |

| ANALYST | 20 |

| PRESIDENT | 10 |

| CLERK | 30 |

| CLERK | 10 |

+-----------+--------+

Distinct只能出现在字段名的最前面，distinct关键字后面可以更多个字段名，表示多个字段名联合起来去重。

连接查询：

根据表连接的方式进行分类：

内连接：（每个表都是主体，表与表之间地位是相同的）

等值连接

非等值连接

自连接

因为每个表的地位是相同的，因此会出现笛卡尔积效应，需要再连接的时候添加条件及进行避免

笛卡尔积效应：

mysql> select ename,dname from emp,dept;

+--------+------------+

| ename | dname |

+--------+------------+

| SMITH | ACCOUNTING |

| SMITH | RESEARCH |

| SMITH | SALES |

| SMITH | OPERATIONS |

| ALLEN | ACCOUNTING |

| ALLEN | RESEARCH |

| ALLEN | SALES |

| ALLEN | OPERATIONS |

| WARD | ACCOUNTING |

| WARD | RESEARCH |

| WARD | SALES |

| WARD | OPERATIONS |

| JONES | ACCOUNTING |

| JONES | RESEARCH |

| JONES | SALES |

| JONES | OPERATIONS |

| MARTIN | ACCOUNTING |

| MARTIN | RESEARCH |

| MARTIN | SALES |

.。。

emp中的每行都会与dept中的每一行进行匹配

结果就会出现56行数据（14*4）

当使用了条件的时候：

mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno;

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

+--------+------------+

这个是92版本的写法

99版本的写法：

mysql> select ename,dname from emp e join dept d on e.deptno=d.deptno;

emp e 和dept d的作用是另名的作用(可以提高效率)

以后都是使用99版的语法

Select 字段名，字段名 from 表名 另命名 join 表名 另命名 on 条件 join 表名 另命名 on条件 …

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

+--------+------------+

外连接：（有主表的概念，主要看使用的什么关键字（right left） 使用right则在join关键字右边的是主表，如使用lift则在join关键字左边的为主表）

左外连接（左连接）

右外连接（右连接）

全连接

等值连接：

mysql> select ename,dname from emp e join dept d on e.deptno=d.deptno;

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

+--------+------------+

非等值连接：

将等值连接中的等于变成不等于

右外连接：

mysql> select ename,dname from emp e right join dept d on e.deptno=d.deptno;

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

| NULL | OPERATIONS |

+--------+------------+

将join右边的表作为主表进行输出（主表中的所有数据一定会输出），连接emp进行辅助输出

左外连接：

mysql> select ename,dname from dept d left join emp e on e.deptno=d.deptno;

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

| NULL | OPERATIONS |

+--------+------------+

多张表怎么连接：

Select

…

From

a

Join

b

On

A和b的连接条件

Join

c

On

A和c的连接条件

Join

D

On

A和d的连接条件

找出每个员工的部门名称和工资等级，要求显示员工名、部门名、薪资、薪资等级

mysql> select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal >=losal and e.sal <= hisal;

+--------+---------+------------+-------+

| ename | sal | dname | grade |

+--------+---------+------------+-------+

| SMITH | 800.00 | RESEARCH | 1 |

| ALLEN | 1600.00 | SALES | 3 |

| WARD | 1250.00 | SALES | 2 |

| JONES | 2975.00 | RESEARCH | 4 |

| MARTIN | 1250.00 | SALES | 2 |

| BLAKE | 2850.00 | SALES | 4 |

| CLARK | 2450.00 | ACCOUNTING | 4 |

| SCOTT | 3000.00 | RESEARCH | 4 |

| KING | 5000.00 | ACCOUNTING | 5 |

| TURNER | 1500.00 | SALES | 3 |

| ADAMS | 1100.00 | RESEARCH | 1 |

| JAMES | 950.00 | SALES | 1 |

| FORD | 3000.00 | RESEARCH | 4 |

| MILLER | 1300.00 | ACCOUNTING | 2 |

找出每个员工的部门名字以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

mysql> select e.ename,a.ename,e.sal,d.dname,s.grade from emp e left join emp a on e.mgr=a.empno join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

+--------+-------+---------+------------+-------+

| ename | ename | sal | dname | grade |

+--------+-------+---------+------------+-------+

| SMITH | FORD | 800.00 | RESEARCH | 1 |

| ALLEN | BLAKE | 1600.00 | SALES | 3 |

| WARD | BLAKE | 1250.00 | SALES | 2 |

| JONES | KING | 2975.00 | RESEARCH | 4 |

| MARTIN | BLAKE | 1250.00 | SALES | 2 |

| BLAKE | KING | 2850.00 | SALES | 4 |

| CLARK | KING | 2450.00 | ACCOUNTING | 4 |

| SCOTT | JONES | 3000.00 | RESEARCH | 4 |

| KING | NULL | 5000.00 | ACCOUNTING | 5 |

| TURNER | BLAKE | 1500.00 | SALES | 3 |

| ADAMS | SCOTT | 1100.00 | RESEARCH | 1 |

| JAMES | BLAKE | 950.00 | SALES | 1 |

| FORD | JONES | 3000.00 | RESEARCH | 4 |

| MILLER | CLARK | 1300.00 | ACCOUNTING | 2 |

+--------+-------+---------+------------+-------+

Between and 是从小到大的原则

上面的sql语句使用了外连接的方式将emp e表作为主表进行筛选。

子查询：

在select语句中嵌套select语句，这就是子查询

子查询可以出现的位置：

Select

…(select)

From

… (select)

Where

…(select)

Where语句中的子查询：（注意在where语句中不能直接使用分组函数，原因在于优先级）

找出比最低工资高的员工姓名和工资：

查询思路：

第一步：先找出最低工资的员工

Select min（sal）from emp；

mysql> select min(sal) from emp ;

+----------+

| min(sal) |

+----------+

| 800.00 |

+----------+

第二步：找出高于最低工资的员工

Select ename from emp where sal >800；

mysql> Select ename from emp where sal >800;

+--------+

| ename |

+--------+

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |

| ADAMS |

| JAMES |

| FORD |

| MILLER |

+--------+

第三部：将前两部进行合并

Select ename,sal from emp where sal>(select min(sal) from emp);

mysql> Select ename,sal from emp where sal>(select min(sal) from emp);

+--------+---------+

| ename | sal |

+--------+---------+

| 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 |

+--------+---------+

这样就行了查询

在执行的时候，会先执行子查询

From后面的子查询：

注意：在from后面的子查询出的结果可以看成是一张临时表（技巧）

找出每个岗位的平均工资的薪资等级

mysql> select a.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) a join salgrade s on a.avgsal >=s.losal and a.avgsal <=s.hisal;

+-----------+-------------+-------+

| job | avgsal | grade |

+-----------+-------------+-------+

| CLERK | 1037.500000 | 1 |

| SALESMAN | 1400.000000 | 2 |

| ANALYST | 3000.000000 | 4 |

| MANAGER | 2758.333333 | 4 |

| PRESIDENT | 5000.000000 | 5 |

+-----------+-------------+-------+

在子查询的时候如果使用了分组函数，尽量给他命名，以方便后面的使用，如果在后续直接使用分组函数的话，就会把它当作一个分组函数而不是使用分组函数查询出来的结果

在select后面使用子查询：

（要求，子查询出的结果只能是一条结果，如果是多条就会出错）

找出每个员工的部门名称，要求显示员工名，部门名

mysql> select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

+--------+------------+

| 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,d.dname from emp e join dept d on e.deptno=d.deptno;

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

+--------+------------+

可以使用其他方法实现这个案例

错误代码：

Select ename,(select dname from dept) dname from emp;

错误结果：ERROR 1242 (21000): Subquery returns more than 1 row

原因在于每一个ename中都对应多个dname因此报错

Union合并查询结果集：

案例：查询工作岗位是manager和salesman的员工

mysql> select ename,job from emp where job='manager' or job='salesman';

mysql> select ename,job from emp where job in ('manager','salesman');

+--------+----------+

| ename | job |

+--------+----------+

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| JONES | MANAGER |

| MARTIN | SALESMAN |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| TURNER | SALESMAN |

+--------+----------+

使用union实现以上案例

mysql> select ename,job from emp where job='manager' union select ename,job from emp where job ='salesman';

+--------+----------+

| ename | job |

+--------+----------+

| JONES | MANAGER |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| MARTIN | SALESMAN |

| TURNER | SALESMAN |

+--------+----------+

使用union的效率较高，对于表链接来说，每次连接新表，每次都存在笛卡尔积效应，使用union可以减少匹配的次数，提高效率

Limit是将查询的结果集的一部分取出来，通常使用在分页查询中。

Limit的用法：

Limit的完整用法：limit startindex length （开始下标，长度）

Limit的缺省用法：limit 5 表示去前五 startindex=0

案例：

按照薪资降序，取出排名前五的员工

mysql> select ename,sal from emp order by sal desc limit 5;

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

| SCOTT | 3000.00 |

| FORD | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

+-------+---------+

Limit实在order by之后执行的（先要完成查询，得到查询结果在在进行取出）

取出工资排名三到武五名的员工

mysql> select ename,sal from emp order by sal desc limit 2,3;

+-------+---------+

| ename | sal |

+-------+---------+

| FORD | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

+-------+---------+

Limit 2，3表示开始下标是从2开始的，要取出三个元素

（limit中的下标是从0开始的）

案例：取出工资排名在【5~9】的员工

mysql> select ename,sal from emp order by sal desc limit 4,5;

+--------+---------+

| ename | sal |

+--------+---------+

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| ALLEN | 1600.00 |

| TURNER | 1500.00 |

| MILLER | 1300.00 |

+--------+---------+

分页：

假设每一页显示3个数据

第一页 limit 0，3；

第二页 limit 3，3；

第三页 limit 6，3；

第n页 limit （n-1）*3，3；

每一页的起始下标为页数*每页要显示的数据条数-3；

关于DQL语句的总结：

Select

。。。

From

。。。

Where

。。。

Group by

。。。

Having

。。。

Order by

。。。

Limit

。。。

执行顺序：

第一是from

第二是where

第三是group by

第四是having

第五是select

第六是order by

第七是limit

先按条件进行筛选，在进行排序，最后取出一部分进行分页操作

第七个作业：

找出平均工资等级最低的部门的工作名称：

mysql> select d.dname,a.* from (select q.deptno,q.kk,s.grade from (select deptno,avg(sal) kk from emp group by deptno) q join salgrade s on q.kk between s.losal and s.hisal order by grade asc limit 1) a join dept d on a.deptno=d.deptno;

+-------+--------+-------------+-------+

| dname | deptno | kk | grade |

+-------+--------+-------------+-------+

| SALES | 30 | 1566.666667 | 3 |

+-------+--------+-------------+-------+

第八个作业：

mysql> select f.* from (select a.ename,a.sal sal from emp a where a.empno in(select distinct mgr from emp d where d.mgr is not null)) f join (select max(e.sal) maxsal from emp e where e.empno not in(select distinct mgr from emp p where p.mgr is not null)) g on f.sal >= maxsal;

+-------+---------+

| ename | sal |

+-------+---------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

| FORD | 3000.00 |

+-------+---------+

Distinct的作用是去重的作用

可以使用时间进行排序，例如：

作业第11题：（找出最后入职的5名员工）

mysql> select ename,hiredate from emp order by hiredate desc limit 5;

+--------+------------+

| ename | hiredate |

+--------+------------+

| ADAMS | 1987-05-23 |

| SCOTT | 1987-04-19 |

| MILLER | 1982-01-23 |

| FORD | 1981-12-03 |

| JAMES | 1981-12-03 |

+--------+------------+

作业第十五题：

mysql> select a.*,b.dname from (select q.empno,q.ename,q.job,q.deptno deptno from emp q join (select e.ename,e.empno,e.hiredate kk from emp e join (select distinct mgr from emp) m on m.mgr=e.empno and e.empno is not null) t on q.mgr=t.empno and q.hiredate<t.kk) a join dept b on b.deptno=a.deptno;

+-------+-------+----------+--------+------------+

| empno | ename | job | deptno | dname |

+-------+-------+----------+--------+------------+

| 7782 | CLARK | MANAGER | 10 | ACCOUNTING |

| 7369 | SMITH | CLERK | 20 | RESEARCH |

| 7566 | JONES | MANAGER | 20 | RESEARCH |

| 7499 | ALLEN | SALESMAN | 30 | SALES |

| 7521 | WARD | SALESMAN | 30 | SALES |

| 7698 | BLAKE | MANAGER | 30 | SALES |

+-------+-------+----------+--------+------------+

作业第22题：

mysql> select a.ename,d.dname,m.ename,s.grade from (select e.ename ename,e.deptno deptno,e.mgr mgr ,e.sal sal from emp e join (select avg(sal) kk from emp ) q on e.sal>q.kk) a join dept d on d.deptno=a.deptno left join emp m on m.empno=a.mgr join salgrade s on a.sal between s.losal and hisal;

+-------+------------+-------+-------+

| ename | dname | ename | grade |

+-------+------------+-------+-------+

| JONES | RESEARCH | KING | 4 |

| BLAKE | SALES | KING | 4 |

| CLARK | ACCOUNTING | KING | 4 |

| SCOTT | RESEARCH | JONES | 4 |

| KING | ACCOUNTING | NULL | 5 |

| FORD | RESEARCH | JONES | 4 |

+-------+------------+-------+-------+

关于建表（表的创建）：

建表的语法格式：

create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型）;

表名：建议以t_或者 tab_开始，可读性强，见名知意。

字段名：见名知意

建表的时候，可以在字段名后面使用default进行设置默认值，例如

Create t_student(

Name varchar（32），

Age int（3） default 12 （设置一个默认值12，当没有个age赋值的时候，age=12）

)

表名和字段民都属于标识符

关于mysql中的数据类型（常见的数据类型）

Varchar（最长255） （可变长度的字符串，可根据实际情况分配空间）

优点：节约内存

缺点：需要动态分配空间，速度较慢

Char（最长255） （定长字符串，不管实际的数据长度是多少，分配固定长度进行存储）

优点：分配固定长度进行存储，速度较快

缺点：使用不当会浪费内存

使用varchar和char需要根据实际情况进行使用。

Int（最长11） （数字中的整数型 等同与java中的int）

Bigint （数字中的长整型 等同于java中的long）

Float （单精度浮点型 java中的float）

Double （双精度浮点型 java中的double）

Date （短日期类型）

Datetime （长日期类型）

Clob（Character Large Object：CLOB） （字符大对象，最多可以存储4G的字符串，例如一篇文章，或说明）

超过255个字符的都要使用clob进行存储

Blob（Binary Large Object） （二进制大对象）

用来存储图片，视频、声音等流媒体数据

往BLOb数据类型中插入数据的时候，需要使用io流

删除表

Drop table +表名; 当表不存在的时候会报错

Drop table if exists + 表名；用这种方法当这个表不存在的时候也不会报错

删除数据库

Drop database + 数据库名字;

插入数据insert（DML）

语法格式：

Insert into 表名（字段名1，字段名2，字段名3…） values （值1，值2，值3）；

注意：字段名和值要一一对应（数量要对应，数据类型也要对应）

mysql> insert into t_student(email,name,no,sex,age) values('lisi@123.com','lisi',2,'f',20);

inert语句一旦执行成功，就会多出一条数据，当其他的值没有进行赋值的时候，系统会自动填写null，例如：

mysql> insert t_student(name) values ('kk');

mysql> select * from t_student;

+------+----------+------+------+------------------+

| no | name | sex | age | email |

+------+----------+------+------+------------------+

| 1 | zhangsan | m | 20 | zhangsan@123.com |

| 2 | lisi | f | 20 | lisi@123.com |

| NULL | kk | NULL | NULL | NULL |

+------+----------+------+------+------------------+

当省略前面的字段名后，values需要个所有的字段名进行赋值，例如：

Insert into t_student values(‘lisi’,’lisi@123.com’,2,’f’,20);

使用insert插入日期：

Str_to_date(‘字符串的日期’,’日期格式化的格式’)；需要注意日期格式化的格式需要和字符串的日期的格式相同

这个函数的作用是将字符串转化为date类型，通常和insert进行联合使用

Mysql中的日期格式：

%y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

如果再插入的时候提供的日期格式为
%y-%m-%d 这种格式MYsql就会自动将字符串类型的日期转化为日期类型，不需要使用str_to_date函数

Date_format（）函数是将日期类型的数据转化为字符串类型的函数

可以使用date_format()将查询时候的日期以某种日期格式进行展示出来，例如：

select date_format(hiredate,'%d-%m-%y') from emp;

date_fromat(日期类型数据，‘日期格式’)

date和datetime的区别：

date是短日期，只包括年月日信息

datetime是长日期，包括年月日时分秒信息

mysql中的默认短日期格式为 %y-%m-%d

mysql中的默认长日期格式为 %y-%m-%d %h：%i：%s

mysql> insert into t_user(name,id,birth_date,create_time) values ('zhangsan',1,'1999-3-29','2022-3-22 17:19:52');

mysql> select * from t_user;

+----------+------+------------+---------------------+

| name | id | birth_date | create_time |

+----------+------+------------+---------------------+

| zhangsan | 1 | 1999-03-29 | 2022-03-22 17:19:52 |

+----------+------+------------+---------------------+

在mysql中获取当前时间的函数：now（）函数 这个时间类型是长日期类型

mysql> insert into t_user(name,id,birth_date,create_time) values('lisi',2,'1990-03-12',now());

mysql> select * from T_user;

+----------+------+------------+---------------------+

| name | id | birth_date | create_time |

+----------+------+------------+---------------------+

| zhangsan | 1 | 1999-03-29 | 2022-03-22 17:19:52 |

| lisi | 2 | 1990-03-12 | 2022-03-22 17:24:39 |

+----------+------+------------+---------------------+

修改update（DML）

语法格式：

Update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3… where 条件;

注意：如果没有条件会导致数据全部更新

update t_user set name='jeck',birth_date='2000-03-12' where id=2;

mysql> select * from t_user;

+----------+------+------------+---------------------+

| name | id | birth_date | create_time |

+----------+------+------------+---------------------+

| zhangsan | 1 | 1999-03-29 | 2022-03-22 17:19:52 |

| jeck | 2 | 2000-03-12 | 2022-03-22 17:24:39 |

删除数据：

语法格式：

Delete from 表名 where 条件

注意：没有条件会导致整张表的数据全部删除

mysql> delete from t_student where name='kk';

mysql> select * from t_student;

+------+----------+------+------+------------------+

| no | name | sex | age | email |

+------+----------+------+------+------------------+

| 1 | zhangsan | m | 20 | zhangsan@123.com |

| 2 | lisi | f | 20 | lisi@123.com |

+------+----------+------+------+------------------+

使用insert一次插入多条记录：

Insert into 表名（字段名1，字段名2，字段名3…） values (值1，值2，值3)，（值1，值2，值3）；

mysql> insert into t_user(name,id,birth_date,create_time) values

-> ('lisi',4,'2003-09-13',now()),

-> ('lisan',5,'2003-09-12',now());

mysql> select * from t_user;

+----------+------+------------+---------------------+

| name | id | birth_date | create_time |

+----------+------+------------+---------------------+

| zhangsan | 1 | 1999-03-29 | 2022-03-22 17:19:52 |

| jack | 2 | 2000-03-12 | 2022-03-22 17:24:39 |

| kk | 3 | 2004-09-19 | 2022-03-22 17:39:24 |

| lisi | 4 | 2003-09-13 | 2022-03-22 18:16:53 |

| lisan | 5 | 2003-09-12 | 2022-03-22 18:16:53 |

+----------+------+------------+---------------------+

快速创建一张表：

Create table 表名 as select 字段名 from 表名；

原理：

将一个表的查询结果当作一张表进行新建

这个可以完成表的快速复制

表创建出来，同时数据也存在

例如：

mysql> create table t_birth_date as select birth_date from t_user;

mysql> select * from t_birth_date;

+------------+

| birth_date |

+------------+

| 1999-03-29 |

| 2000-03-12 |

| 2004-09-19 |

| 2003-09-13 |

| 2003-09-12 |

+------------+

将一张表插入一张表：

mysql> insert into t_birth_date select hiredate from emp;

mysql> select * from T_birth_date;

+------------+

| birth_date |

+------------+

| 1999-03-29 |

| 2000-03-12 |

| 2004-09-19 |

| 2003-09-13 |

| 2003-09-12 |

| 1980-12-17 |

| 1981-02-20 |

| 1981-02-22 |

| 1981-04-02 |

| 1981-09-28 |

| 1981-05-01 |

| 1981-06-09 |

| 1987-04-19 |

| 1981-11-17 |

| 1981-09-08 |

| 1987-05-23 |

| 1981-12-03 |

| 1981-12-03 |

| 1982-01-23 |

+------------+

Delete语句删除数据的原理：（属于DML语句）

表中的数据被删除了，但是这个数据所在硬盘上的空间不会被释放

这种删除方式的优点是：支持回滚，可以恢复数据

这种删除方式的缺点是：删除效率较低

快速删除数据的方法：

使用truncate语句

Truncate语句删除数据的原理：

删除后硬盘是数据所占的空间会释放

这种删除方式的优点：快速

这种删除方式的缺点：不支持回滚

用法：truncate table 表名；（这种操作属于DDL操作）

Truncate删除的是表中的数据，表的结构还在

删除表：

Drop table 表名；

Mysql中的约束：

什么是约束：

约束对应的英语单词为constraint

在创建表的时候，可以在字段中加上一些约束，来保证数据的完整性、有效性

约束的作用就是为了保证表中的数据有效

常见的约束：

非空约束：not null

唯一性约束：unique

主键约束：primary key (简称PK)

外键约束：foreign key（简称FK）

检查约束：check （mysql不支持，oracle支持）

非空约束：not null

非空约束not null约束的字段不能为null。

Drop table if exists t_vip;

Create table t_vip(

Id int,

Name varchar(255) not null

);

Insert into t_vip(id,name) values(1,’zhangsan’);

Insert into t_vip(id,name) values(2,’lisi’);

文件拓展名是以 .sql 结尾的文件叫做sql脚本文件

当执行sql脚本文件的时候，脚本文件种的所有sql语句都会执行

如何执行sql脚本文件？

Source +sql脚本文件的绝对路径

当创建了一个t_vip执行以下sql语句就会出现以下问题：

mysql> insert into t_vip(id) values(3);

ERROR 1364 (HY000): Field 'Name' doesn't have a default value

原因在于：

T_vip这个表中的name这个字段名不能null，因为没有给name这个字段名赋值，name会自动等于默认值，但是默认值为null，而且name是使用非空约束not null进行约束的，因此就会报错。

唯一性约束：unique

唯一性约束unique约束的字段不能重复，但是可以是null

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) unique , （这种被称为列级约束）

email varchar(255)

);

insert into t_vip (id,name,email) values(1,'zhangsan','zhangsan@123.com');

insert into t_vip (id,name,email) values(1,'wangwu','wangwu@123.com');

insert into t_vip (id,name,email) values(1,'lisi','lisi@123.com');

这样写只要是name重复，就不能向表中添加数据

如何使用两个字段名来具有唯一性？

（也就是只有当name和email都相同的时候才判定为重复）

使用unique（字段名，字段名…）的方式

drop table if exists t_vip;

create table t_vip(

id int ,

name varchar(255),

email varchar(255),

unique(name,email) （这种被称为表级约束）

);

insert into t_vip values(1,'zhangsan','zhangsan@123.com');

insert into t_vip values(2,'zhangsan','zhangsan1@123.com');

mysql> select * from t_vip;

+------+----------+-------------------+

| id | name | email |

+------+----------+-------------------+

| 1 | zhangsan | zhangsan@123.com |

| 2 | zhangsan | zhangsan1@123.com |

如果再插入一条

insert into t_vip values(1,'zhangsan','zhangsan@123.com');

mysql> insert into t_vip values(3,'zhangsan','zhangsan@123.com');

出现错误：

ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@123.com' for key 'name'

什么时候使用表级约束？

当需要使用多个字段联合起来进行某一个约束的时候，就需要使用表级约束。

注意：

Not null只有列级约束约束，没有表级约束

在mysql中，如果一个字段同时使用not null和unique约束的话，这个字段名会自动变成主键约束，（注意，在Oracle中不一样）

*****主键约束（primary key，简称PK）

主键约束的相关术语：

主键约束：就是一种约束

主键字段：被主键约束修饰的字段

主键值：主键字段中的每一个值都叫做主键值

主键的作用：

主键值是每一行记录的唯一标识

（主键值是每一行记录的生份证）

任何一张表都需要有一个主键，没有主键，表无效。

主键的特征：

Nut null+unique （主键值不能使null，也不能重复）

给一张表中添加主键：

drop table if exists t_vip;

create table t_vip(

id int primary key, （列级约束 单一主键）

name varchar(255)

);

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'zhangsan');

select * from t_vip;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | zhangsan |

+----+----------+

在向表中添加一个id等于2的数据

mysql> insert into t_vip(id,name) values(2,'zhangsan');

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY

向表中添加一条id为null的数据：

mysql> insert into t_vip(name) values('lisi');

ERROR 1364 (HY000): Field 'id' doesn't have a default value

使用主键primary key的时候，可以使用表级约束。

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255),

primary key(id)

);

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'zhangsan');

insert into t_vip(id,name) values(3,'lisi');

select * from t_vip;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | zhangsan |

| 3 | lisi |

+----+----------+

mysql> insert into t_vip(id,name) values(3,'lisi');

ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

将多个字段联合起来添加主键：

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255),

email varchar(255),

primary key(id,name) （将id和name联合起来添加主键 复合主键）

);

insert into t_vip(id,name,email) values (1,'zhangsan','zhangsan@123.com');

insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');

select * from t_vip;

+----+----------+------------------+

| id | name | email |

+----+----------+------------------+

| 1 | zhangsan | zhangsan@123.com |

| 2 | lisi | lisi@123.com |

+----+----------+------------------+

复合主键只有当primary key中的字段都不相同并且都不为null的时候才能添加数据

（将复合主键中的字段结合起来）

向表中在添加一个name字段为lisi的数据

mysql> insert into t_vip (id,name,email) values(3,'lisi','lisi@123.com');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;

+----+----------+------------------+

| id | name | email |

+----+----------+------------------+

| 1 | zhangsan | zhangsan@123.com |

| 2 | lisi | lisi@123.com |

| 3 | lisi | lisi@123.com |

+----+----------+------------------+

当插入id为2，name为lisi的数据时：

mysql> insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');

ERROR 1062 (23000): Duplicate entry '2-lisi' for key 'PRIMARY'

建议使用单一主键，减少使用复合主键（比较复杂，不建议使用）

一张表中，主键只能存在一个。

主键值建议使用定长的数据类型：

Int

Bigint

Char等

不建议使用可变长度的varchar作为主键

主键除了使用单一主键和复合主键进行分类外，还是用自然主键，业务主键：

自然主键：主键值是一个自然数，和业务没有关系

业务主键：主键值和业务紧密相关，例如将银行卡账号作为主键值

在实际开发中使用较多的为自然主键

原因在于自然主键只需要做到不重复就可以了，但是业务主键和业务相关，当业务发生改变的时候，主键值也会发生改变，故不建议使用业务主键。

在mysql中存在自增机制可以自动维护一个主键值

drop table if exists t_vip;

create table t_vip(

id int primary key auto_increment, （auto_increment表示自增）

name varchar(255)

);

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('zhangsan');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('zhangsan');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('zhangsan');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('lisi');

insert into t_vip(name) values('zhangsan');

select * from t_vip;

+----+----------+

| id | name |

+----+----------+

| 1 | lisi |

| 2 | lisi |

| 3 | zhangsan |

| 4 | lisi |

| 5 | lisi |

| 6 | zhangsan |

| 7 | lisi |

| 8 | lisi |

| 9 | zhangsan |

| 10 | lisi |

| 11 | lisi |

| 12 | zhangsan |

+----+----------+

Auto_increment表示自增

*******外键约束（foreign key，简称FK）

外键的相关术语：

外键约束：一种约束

外键字段：被外键约束的字段

外键值：外键字段中的每一个值

情景：

设计学生和班级的表格：

第一种方式：

将学生和班级写入同一个表中

Name‘ class

Kk 高一一班

Zz 高二二班

Cc 高一一班

Tt 高二二班

这种写法容易造成数据重复，浪费内存

推荐使用第二种方式：

学生表：

Name no classno

Kk 1 11

Zz 2 22

Cc 3 11

Tt 4 22

班级表：

Classno class

11 高一一班

22 高二二班

但是，这种方式如果不采取任何约束的话，在学生表中很有可能出现班级表中不存在的classno编码，这时，就需要使用到外键约束。

当两个表使用了外键约束过后，就会出现子表和父表，如在上述例子中，学生表中需要引用班级表中的字符（外键字段），因此班级表是父表，学生表是子表。

删除表的顺序：

先删除子表，在删除父表

新建表的顺序：

先创建父表，在创建子表

删除数据的顺序：

先删除子表，在删除父表

插入数据的顺序：

先插入父表，再插入子表

实践：

drop table if exists t_student;

drop table if exists t_class;

create table t_class(

classno int primary key,

classname varchar(255)

);

create table t_student(

no int primary key auto_increment,

name varchar(255),

cno int,

foreign key(cno) references t_class(classno)

);

insert into t_class values(11,'高一一班');

insert into t_class values(22,'高二二班');

insert into t_student(name,cno) values('kk',11);

insert into t_student(name,cno) values('zz',22);

insert into t_student(name,cno) values('cc',11);

insert into t_student(name,cno) values('tt',22);

select s.no,s.name,c.classname from t_student s join t_class c on c.classno=s.cno;

+----+------+--------------+

| no | name | classname |

+----+------+--------------+

| 1 | kk | 楂樹竴涓€鐝? |

| 3 | cc | 楂樹竴涓€鐝? |

| 2 | zz | 楂樹簩浜岀彮 |

| 4 | tt | 楂樹簩浜岀彮 |

+----+------+--------------+

向t_student表中添加一个不属于t_class表中的班级：

mysql> insert into t_student (name,cno) values('bb',123);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`kkk`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`))

添加完外键约束后，cno中的值只能是classno中的值

Foreign key（字段名） references 表名（字段名）；

注意：子表中的外键引用的父表中的某个字段不一定是主键。

原因是父表中的那个字段需要在子表中进行使用，不能重复，必须具有唯一性，可以为null。

注意外键值可以是null，外键值是子表中的引用父类的字段，不是父类中的字段。

存储引擎：

什么是存储引擎：

存储引擎是表存储/组织数据的方式。

不同的存储引擎，对应不同的存储组织方式

怎样查看存储引擎？

Show create table 表名。

| t_student | CREATE TABLE `t_student` (

`no` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`cno` int(11) DEFAULT NULL,

PRIMARY KEY (`no`),

KEY `cno` (`cno`),

CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

其中engine=innoDB就是存储引擎

Default Charset=utf8是字符编码方式

可以看出

Mysql中默认的存储引擎是innoDB

Mysql中默认的字符编码方式是utf8

怎样指定存储引擎？

再将表的时候指定

Create table kk(

Id int primary key auto_increment,

Name varchar(255)

) engine=innodb default charset=gbk;

| kk | CREATE TABLE `kk` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

怎么查看mysql支持哪些存储引擎：

Show engines \G

mysql> show engines \g

+--------------------+---------+----------------------------------------------------------------+------------

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+------------

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+------------

Mysql一共有九大搜索引擎，版本不同，支持的情况不同

MyISAM存储引擎：

特征：

使用三个文件表示一个表：

格式文件：存储表结构的定义（XXX.frm）

数据文件：存储表行的内容（XXX.MYD）

索引文件：存储表上索引（XXX.MYI）：索引可以缩小扫描范围，提高执行效率（类比于书的目录）

Mtisam的优点：可以被压缩，或者转化为可读表

对于一张表来说：

只要是有主键，或者有用unique限制的字段，就会自动在这些字段上创建索引

InnoDB存储引擎：

这是mysql中默认的存储引擎，innodb支持事务，支持数据库奔溃后自动恢复机制

每个innoDB表在数据库目录中以 .frm格式文件表示

innoDB表空间tablespace用于存储表的内容

innoDB提供一组用来记录事务性活动的日志文件

innoDB最重要的特点就是非常安全。

Innodb

每个innoDB表在数据库目录中以 .frm格式文件表示

innoDB表空间tablespace用于存储表的内容（表空间是一个逻辑名称，表空间存储数据+索引）

innoDB提供一组用来记录事务性活动的日志文件

使用commit（提交）、savepoint及rollback（回滚）支持事务处理

提供全ACID兼容

在mysql服务器奔溃后提供自动恢复服务

多版本（MVCC）和行级锁定

支持外键及引用的完整性，包括级联删除和更新

Innodb最大的特点就是支持事务：

以保证数据的安全，效率不是很高，不能进行压缩

MEMORY存储引擎：

使用MEMORY存储引擎的表中的数据是存储在内存中的，且行的长度固定

MEMORY存储引擎相应速度十分快，因为是将数据存储在内存中，所以，一旦断电数据就会消失

MEMORY存储引擎的特点：

1. 在数据库目录中每个表都是以.fre格式的文件进行表示
2. 表数据及索引被存储在内存中
3. 表级锁机制
4. 不能包括TEXT或者BLOB字段

MEMORY存储引以前也叫HEAP引擎

MEMORY存储引擎的优点：查询效率最高

MEMORY存储引擎的缺点：不安全，关机之后数据消失，数据和索引存储在内存中

MEMORY存储引擎不支持事务。

事务：

一个事务就是一个完整的业务逻辑

是一个最小的工作单元，不能再分

只有DML语句才有事务，其他语句和事务没关系

DML：delete insert update

只有这三个。

因为只有这三个语句可以对数据库中的数据进行增删改

只要涉及数据的增删改就必须考虑数据的安全问题

一个事务就是多个DML语句同时成功或者同时失败。

在事务的执行过程中，每执行一条DML语句，就会在“事务性活动日志”中记录

在事务执行过程中，我们可以提交事务，也可以回滚事务

提交事务：

提交事务标志着事务的结束，每一条DML都成功的完成了，提交事务会清空事务性活动日志，将数据全部彻底持久化到数据库中（成功的结束）

回滚事务：

将之前所有的DML操作全部撤消，并清空事务性活动日志，回滚事务也标志着事务的结束，并且是DML语句执行全部失败的结束（失败的结束）

怎么提交事务和回滚事务？

提交事务：commit；语句

回滚事务：rollback；语句

事务的单词是：transaction

Mysql默认情况下支持自动提交

每执行一条DML语句，就提交一次

事务开始：start transaction

关闭自动提交机制：start transaction

mysql> start transaction; （开启事务，关闭自动提交）

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_class(classno,classname) values(5,'kk');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_class(classno,classname) values(6,'kk');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_class(classno,classname) values(7,'kk');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_class(classno,classname) values(8,'kk');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t_class;

+---------+--------------+

| classno | classname |

+---------+--------------+

| 5 | kk |

| 6 | kk |

| 7 | kk |

| 8 | kk |

| 11 | 楂樹竴涓€鐝? |

| 22 | 楂樹簩浜岀彮 |

+---------+--------------+

回滚事务：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_class;

+---------+--------------+

| classno | classname |

+---------+--------------+

| 11 | 楂樹竴涓€鐝? |

| 22 | 楂樹簩浜岀彮 |

+---------+--------------+

如果在执行完DML语句之后，也可以使用commit进行提交（成功的结束），也就是将上面所有的DML语句插入的数据全部持久的放到数据库中。

提交事务：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_class;

+---------+--------------+

| classno | classname |

+---------+--------------+

| 5 | kk |

| 6 | kk |

| 7 | kk |

| 8 | kk |

| 11 | 楂樹竴涓€鐝? |

| 22 | 楂樹簩浜岀彮 |

+---------+--------------+

如果在使用回滚事务：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_class;

+---------+--------------+

| classno | classname |

+---------+--------------+

| 5 | kk |

| 6 | kk |

| 7 | kk |

| 8 | kk |

| 11 | 楂樹竴涓€鐝? |

| 22 | 楂樹簩浜岀彮 |

+---------+--------------+

由于清空了实务性活动日志，回滚之后也不会改变表中的数据

事务的四个特性：

A：原子性

事务是最小的工作单元，不能再分

C：一致性

在同一个事务中所有的DML语句只能同时成功或者同时失败

I：隔离性

A事务和B事务之间具有一定的隔离

当A事务在操作这张表时，B事务会怎样？？

D：持久性

事务最终结束的保障，事务提交，将没有保存到硬盘上保存到硬盘上。

事务的隔离性：

事务和事务之间的隔离性可能很强，也可能很弱，很强叫做事务的隔离级别高，很弱叫做事务的隔离级别低

事务和事物之间隔离级别有哪些：四个等级

读未提交：read uncommitted（最低隔离级别） 《事务没有提交就可以读到》

事务A可以读取到事务B未提交的数据

这种隔离级别存在 脏读现象（Dirty read）

叫做读到了脏数据

大多数数据库都是从read commitied起步的，基本不会使用read uncommitted

读已提交：read committed 《事务提交就可以读到》

事务A可以读取事务B已经提交的数据

这种隔离级别解决了脏读现象

这种隔离级别存在的问题：

不可重复读取数据

什么时不可重复读取数据：（这里的不重复是指读到的数据）

事务开启后，第一次读取到的数据是3条，但是事务还没有结束，第二次读取数据的时候，读取到的事务可能是四条，四条不等于三条 ，每一次读取到的数据都是不同的

这种隔离级别读取的是比较真实的数据，每一条数据都是绝对真实的

Oracle中默认读取的隔离级别是read committed

可重复读：repeatable read 《事务提交过后也读不到，永远读取到的都是事务开始时的数据》

事务A开始后，不管多久，每一次事务A读取到的数据都是一致的，即使事务B将数据已经修改了，并且提交了，事务A读取到的数据还是不会发生变化，这就是可重复读

可重复读解决了不可重复读问题

可重复读存在的问题是：

可能会出现幻影读

每一次读取到的数据都是幻想，数据不真实

Mysql中默认的隔离级别就是repeated read等级

序列化\串行化：serializable（最高隔离级别）

这是最高的隔离级别，解决了所有的问题，同时，他的效率最低

这种隔离级别表示事务排队，不能并发

事务A在操作一个表的时候，事务B只有等事务A事务提交后再开始

索引：（index）

索引是在字段上添加的，目的是为了提高查询效率

（类似于一本书的目录）

Mysql存在两种检索方式：

第一种：全表扫描（在没有添加索引的时候使用）

第二种：根据索引扫描

Mysql数据库中索引的数据结构和treeset的数据结构相同（红黑树，自平衡的二叉树），在mysql中索引是一个B-tree数据结构

索引遵循左小右大原则存放，采用中序遍历取数据

注意：

1、在任何数据库中，主键限制的字段会默认添加索引

在mysql，字段用unique约束也会默认添加索引

2、在任何数据库中，每个表中的每一条数据在硬盘上都有物理存储编号。

3、在mysql中，索引是一个单独的对象，不同存储引擎对应不同的存储位置，MYISAM中索引在.MYI文件中，InnoDB中索引在一个逻辑名为tablespace当中，MERMORY中，索引在内存中，不管索引在哪儿，索引都是以自平衡二叉树（B-tree）的数据结构存在

什么情况下，我们会添加索引：

1. 数据量大
2. 这个字段经常出现在where的后面，以条件的形式存在，经常被扫描
3. 该字段很少的DML语句操作

建议不要随便添加索引

推荐通过主键查询，通过使用unique约束的字段进行查询，这样效率较高

索引怎么创建：

mysql> create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，取名叫emp_ename_index

删除索引：

mysql> drop index emp_ename_index on emp;

删除emp中emp_ename_index索引对象删除

怎么查看一条SQL语句是否使用了索引进行检索？

mysql> explain select * from emp where ename='king';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Rows等于14 type=all

创建了索引之后查看这条sql语句

mysql> explain select * from emp where ename='king';

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

| 1 | SIMPLE | emp | ref | ename_index | ename_index | 33 | const | 1 | Using where |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

rows等于1 type=ref （ref表示使用索引）

在字段上添加了索引后，索引也有可能失效。

什么时候索引失效？

例如

失效的第一种情况：

Select * from emp where ename like’%t’;

这时候，索引就会失效。

原因是在模糊查询的时候，使用了%开头

所以，在使用模糊查询的时候，尽量避免使用%开头

失效的第二种情况：

使用or的时候，如果or两边的条件字段都具有索引，这时候查找的时候才会使用索引，但凡有一个字段没有添加索引，在查找的时候都不会使用索引进行查找，所以，尽量少使用or。

Explain select * from emp where ename=’king’ or job=’manager’;

失效的第三种情况：

使用复合索引的时候，没有使用左侧的列查询，索引失效

什么是复合索引？

两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引

Create index emp_sal_job_index on emp(sal,job); 创建一个复合索引

使用sal字段进行查找：

mysql> explain select * from emp where sal=800;

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

| 1 | SIMPLE | emp | ref | emp_sal_job_index | emp_sal_job_index | 9 | const | 1 | Using where |

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

使用sal字段进行查找的时候使用了索引

运用job进行查找：

mysql> explain select * from emp where job='manager';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

使用job进行查找的时候没有使用索引，索引失效

在复合索引中，使用复合索引中左端的字段进行查找会是使用索引。

索引失效的第四种方式：

在where中索引列添加运算，索引失败。

mysql> explain select * from emp where sal+4=1234;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

在where中索引列添加运算索引会失效

失效的第五中情况：

在where中索引列使用了函数

mysql> explain select * from emp where lower(ename)='king';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

索引是数据库的重要优化手段

索引的分类：

单一索引：一个字段添加索引

符合索引：两个字段添加一个索引

主键索引：主键上添加索引

唯一性索引：具有unique约束的字段上添加索引

注意：唯一性索引比较弱的时候，在字段上添加索引的作用不大

越唯一，添加索引后的效率越高

视图（view）：

注意，只有DQL语句才能以view的形式创建（select）

什么是视图？

View：站在不同的角度看待同一份数据

怎么创建视图对象，删除视图对象？

Create view emp_iew as select * from emp；

创建一个叫做emp_view的视图

创建一个视图：

mysql> create view dept2_view as select * from dept2;

删除一个视图：

mysql> drop view dept2_view;

视图的作用：

我们可以对视图进行增删改查，对视图进行增删改查会导致原表的数据发生变化

（视图的特点：通过对试图进行操作，会影响原表中的数据）

mysql> insert into dept2_view values(50,'kk','zz');

在视图中添加一条数据

mysql> select * from dept2;

+--------+------------+----------+

| DEPTNO | DNAME | LOC |

+--------+------------+----------+

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

| 50 | kk | zz |

+--------+------------+----------+

对原表进行查看，发现原表中的数据也进行了插入

mysql> delete from dept2_view where deptno=50;

Query OK, 1 row affected (0.00 sec)

mysql> select * from dept2;

+--------+------------+----------+

| DEPTNO | DNAME | LOC |

+--------+------------+----------+

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

+--------+------------+----------+

将视图中数据进行删除，相应的，原表中的数据也会发生变化

同样，在原表中添加或删除数据，视图中的数据也会发生变化

即使是用多张表联合创建出来的view也可以同时更改多张表对应的数据

视图在开发中的作用：

可以简化开发

例如当有一条复杂的sql语句需要重复执行的时候，可以将这个复杂的sql语句创建成一个视图，然后对试图中的数据进行修改，这样就做到了简化开发，同时利于后期的维护，后期要修改就只需要修改视图就行了

以后面向视图开发的时候，就像是使用一张表一样，视图是一个文件，存储在硬盘中，开关机不会影响视图

视图对应的是一条DQL语句

增删改查：CRUD

C：create（新建）

R：retrieve（检索，查）

U：update（改）

D：delete（删）

DBA的常用命令：

重点数据的导入、导出（数据的备份）

数据的导出：

Mysqldump 表名>需要存放的路径+文件名 在输入用户名和密码

实例：

C:\Users\小鲨鱼>mysqldump kkk>d:\mysql\kkk.sql -uroot -p123456

将数据库kkk导出到d:\mysql\kkk.sql中

C:\Users\小鲨鱼>mysqldump kkk brithday>d:\mysql\brithday.sql -uroot -p123456

将数据库kkk中的brithday表进行导出

数据库导出需要注意：

在Windows的dos窗口中需要先将mysql推出后在进行

数据的导入：

使用source +拓展名为.sql的文件

导入时，需要登录到mysql上，在创建数据库，使用数据库，在使用source进行导入

数据库的设计三范式：

第一范式：任何一张表都需要有一个主键，每一个字段原子性不能再分

第二范式：所有非主键完全依赖主键，不要产生部分依赖（建立在第一范式上）

第三范式：所有非主键完全依赖主键，不要产生传递依赖（建立在第二范式上）

按照三范式进行设计的数据库可以减少数据的冗余，减少空间的浪费

第二范式中多对多怎么设计：

多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！！！！

一对多：

一对多，两张表，多的表加外键

数据表设计：

多对多：

多对多，三张表，关系表两个外键

一对多：

一对多，两张表，多的表加外键

一对一：

一对一，填外键加唯一

数据库设计三范式是理论上的，实践和理论会有偏差，最终的目的都是为了满足客服的需求，有的时候会拿冗余来换执行速度，因为在sqli中，表和表之间连接会产生笛卡尔积效应，表与表之间连接次数越多，效率越低，有时候就会那冗余来换取减少表与表之间的连接，提高执行效率。

悲观锁（行级锁）：在select后面添加for update

将查询出的数据整行进行锁住，不能进行修改，再提交事务后才能另一个事务才能修改

类似于不能并发操作

乐观锁：会添加一个版本号，当事务1和事务2对数据进行处理之前，会先识别版本号，当事务1对数据进行修改后，也会修改数据的版本号，在事务2对数据进行修改之前，查看到当前被事务1修改的版本号和之前识别的版本号不同，就会进行回滚。