Mysql基础

最新推荐文章于 2022-07-23 14:19:53 发布

Yokna

最新推荐文章于 2022-07-23 14:19:53 发布

阅读量88

点赞数

分类专栏：学习文章标签： mysql

本文链接：https://blog.csdn.net/Yokna/article/details/119714647

版权

学习专栏收录该内容

33 篇文章 0 订阅

订阅专栏

Mysql基础

基本操作

导入sql文件

在进入到mysql数据库中后，使用命令

source + sql文件的路径；

就可以将sql文件导入到数据库中。

查看数据库表结构

desc + 表名

查看版本号

select version();

查看当前使用的数据库

select database();

终止一条命令

\c

DQL

简单查询

select loc from dept;
select deptno,dname from dpet;

查询表中所有字段

1.select * from dept;  这种查询会先将*转化为表中所有字段，效率上来说会稍微低一点，可读性也差
2.select 表中所有字段 from dept； 推荐使用第二种

给查询列起别名–as

select deptno,dname as deptname from dept;
只是将显示的查询结果列显示为deptname，原表不会发生改变。

取的别名有空格的话，可以用‘’包裹起来
select deptno,dname 'dept name' from dept;
select deptno,dname "dept name" from dept;
在所有的数据库中，字符串统一用单引号括起来，mysql支持双引号，oracle不支持双引号

计算员工工资

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

如果别名是中文，需要使用单引号括起来
mysql> select ename,sal*12 as '年薪' 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 |
+--------+----------+

条件查询

= 、<、>、!= <= 、>= 、is null条件

mysql> select ename,empno from emp where sal = 800;
+-------+-------+
| ename | empno |
+-------+-------+
| SMITH |  7369 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select ename,empno from emp where sal != 800;
mysql> select ename,empno from emp where sal <> 800;  <>相当于不等
+--------+-------+
| ename  | empno |
+--------+-------+
| ALLEN  |  7499 |
| WARD   |  7521 |
| JONES  |  7566 |
| MARTIN |  7654 |
| BLAKE  |  7698 |
| CLARK  |  7782 |
| SCOTT  |  7788 |
| KING   |  7839 |
| TURNER |  7844 |
| ADAMS  |  7876 |
| JAMES  |  7900 |
| FORD   |  7902 |
| MILLER |  7934 |
+--------+-------+

范围查询
mysql> select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
between and 必须遵循左小右大的原则，范围相当于[2450,3000]闭区间
mysql> select empno,ename,sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)

查询是否为空

mysql> select empno,ename,sal,comm from emp where comm is null;
mysql> select empno,ename,sal,comm from emp where comm is not null;
数据库中null不能使用=来判断，null在数据库中是真正意义的空，什么也没有。
+-------+--------+---------+------+
| empno | ename  | sal     | comm |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | NULL |
|  7566 | JONES  | 2975.00 | NULL |
|  7698 | BLAKE  | 2850.00 | NULL |
|  7782 | CLARK  | 2450.00 | NULL |
|  7788 | SCOTT  | 3000.00 | NULL |
|  7839 | KING   | 5000.00 | NULL |
|  7876 | ADAMS  | 1100.00 | NULL |
|  7900 | JAMES  |  950.00 | NULL |
|  7902 | FORD   | 3000.00 | NULL |
|  7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+

and | or |in 查询

mysql> select empno,ename from emp where sal > 2500 and job='manager';
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
+-------+-------+

mysql> select empno,ename,sal,comm from emp where comm is null or comm = 0;
+-------+--------+---------+------+
| empno | ename  | sal     | comm |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | NULL |
|  7566 | JONES  | 2975.00 | NULL |
|  7698 | BLAKE  | 2850.00 | NULL |
|  7782 | CLARK  | 2450.00 | NULL |
|  7788 | SCOTT  | 3000.00 | NULL |
|  7839 | KING   | 5000.00 | NULL |
|  7844 | TURNER | 1500.00 | 0.00 |
|  7876 | ADAMS  | 1100.00 | NULL |
|  7900 | JAMES  |  950.00 | NULL |
|  7902 | FORD   | 3000.00 | NULL |
|  7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+

and 优先级比 or优先级要高，会先执行and,所以在查询的时候可以通过加（）表示准确的语意。
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

in 是包含，相当于多个or（not in表示不在这个集合里面）

select empno,ename,job from emp where job in('manager','salesman');
in后面跟的是一个集合
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

模糊查询

like %匹配任意多个字符下划线代表任意一个字符

select ename from emp where ename like '%o%';
找出名字中含有'o'的名字

找出以t结尾
select ename from emp where ename like '%t';

找出以k开头
select ename from emp where ename like 'k%';

第二个字母为a的
select ename from emp where ename like '_a%';
第三个字母为r的
select ename from emp where ename like '__a%';

找出名字中有下划线的
select ename from emp where ename like '%\_%';

排序

查询所有员工的工资排序

mysql> select ename,sal from emp order by sal; 默认是升序
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; 降序需要加descend
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

按照多个字段排序
查询员工名字和薪资，按照薪资升序排列，如果薪资一样的话，按照名字升序排列。
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 |
+--------+---------+

根据字段的位置排序
select ename,sal from emp order by 2;按照查询结果的第二列排序，不推荐，不健壮

mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
执行顺序 ：
1.from 
2.where
3.select
4.order by
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+

数据处理函数

upper、lower 转换大小写

select lower(ename) from emp;
select upper(ename) from emp;

奇怪的bug

select后面跟字面量 会创建n个字面量，n为emp的记录
mysql> select upper('2'+ lower(ename)) from emp;
+--------------------------+
| upper('2'+ lower(ename)) |
+--------------------------+
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
| 2                        |
+--------------------------+
以上bug是mysql中字符串拼接不能用+号，用的是一个concat函数
mysql> select concat(2,upper(lower(ename))) from emp;
+-------------------------------+
| concat(2,upper(lower(ename))) |
+-------------------------------+
| 2SMITH                        |
| 2ALLEN                        |
| 2WARD                         |
| 2JONES                        |
| 2MARTIN                       |
| 2BLAKE                        |
| 2CLARK                        |
| 2SCOTT                        |
| 2KING                         |
| 2TURNER                       |
| 2ADAMS                        |
| 2JAMES                        |
| 2FORD                         |
| 2MILLER                       |
+-------------------------------+

substr 取字串

select substr(ename,1,1) as ename from emp;
其实下标从1开始，没有0

字符串拼接

select concat(empno,ename) from emp;

首字母大写

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

获取字段长度

获取字符串长度
select length(ename) enamelength from emp;

去除空格

mysql> select * from emp where ename =  trim(' KING ');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
仅能去除前后的空格，不能去除中间的空格

mysql> select * from emp where ename =  trim(' KI NG ');
Empty set (0.00 sec)

日期的处理

str_to_date

round 四舍五入

select round(1234.56,0) as result from emp;保留0位小数
select round(1234.56,1) as result from emp;保留1位小数
select round(1234.56,-1) as result from emp;保留到十位整数

生成随机数 rand

select round(rand()*100,0) as result from emp;
100以内的随机数

ifnull 可以将null转化为一个数值

在左右的数据库中，只要有null参与的数学运算，结果都是null

ifnull(数据，被当做哪一个值)

计算年薪 = (月薪 + 补助) * 12;
错误示范
mysql> select ename,(sal + comm) *12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+


正确示范
mysql> select ename,(sal + ifnull(comm,0)) *12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.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 |
+--------+----------+

case when then when then else end

不修改数据库，仅将查询结果显示上调
select ename,job, sal,
	(case job 
	when 'MANAGER' then sal*1.1
	when 'SALESMAN' then sal*1.5
	else sal
	end)
	as newsal
from emp;

+--------+-----------+---------+---------+
| ename  | job       | sal     | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+

分组函数/多行处理函数

分组函数是多个输入–>一个输出。分组函数使用时先分组才能使用,如果没有分组，则默认整张表为一组数据。

注意：

1.分组函数会自动忽略null，不需要提前对null处理

2.count会忽略null，不会将null统计进去

3.count（*）与count（具体字段）区别：

count（字段）：表示统计该字段下不为null的元素的总和

count（*）：表示多少行

4.分组函数不能直接使用在where子句中,分组函数在使用时必须先分组后再使用，where使用时还没分组

找出比最低工资高的员工信息

错误示范
select ename,sal from emp where sal > min(sal);
报错1111！可恶
ERROR 1111 (HY000): Invalid use of group function

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

count 计数

select count(ename) from emp;

sum 求和

工资和
select sum(sal) from emp;

avg 求平均

select avg(sal) from emp;

max 取最大

工资最多为多少
select max(sal) from emp;

min 取最小

select min(sal) from emp;

分组查询

格式：
select
	
from

where

group by

order by

执行顺序：
1.from
2.where
3.group by
4.select
5.order by

查询每一个部门的工资和

mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

查询每一个部门的最大工资

select deptno,max(sal) as maxsal from emp group by deptno order by deptno;

查询每个部门不同岗位的最高薪资

mysql> select deptno,job,max(sal) as maxsal from emp group by deptno,job order by deptno;
+--------+-----------+---------+
| deptno | job       | maxsal  |
+--------+-----------+---------+
|     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 |
+--------+-----------+---------+
9 rows in set (0.00 sec)

查出每个部门最高薪资大于3000的部门

方法1：
mysql> select deptno,max(sal) from emp group by deptno
    -> having max(sal) > 3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)
方法2：效率更高
mysql> select deptno,max(sal) from emp where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

查询部门平均工资高于2500的

mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

总结：
select

from

where

group by 

having

order by;

执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by

distinct

去除查询出来的重复数据，原数据不受影响
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

联合起来去除重复记录
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 |
+-----------+--------+

连接查询

多张表联合起来查询数据。

连接查询的分类

根据表连接的方式分类：

内连接

等值连接

非等值连接

自连接

外连接

左连接

右连接

全连接

简单连接查询

select 
	ename,dname
from 
	emp,dept
where
	emp.deptno = dept.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 |
+--------+------------+

为了查询效率，做如下优化
1.在select选择字段时，明确指出字段属于哪一张表，避免到两张表中去找
2.from时可以给表取别名，这样可以缩短代码长度（尤其是在表名特别长的时候）
sql92语法内连接
select 
	e.ename,d.dname
from 
	emp e,dept d;
where 
	e.deptno = d.deptno;
	

sql 99语法
select 
	e.ename,d.dname
from 
	emp e 
inner join 
	dept d 
on 
	e.deptno = d.deptno
where 
	substr(e.ename,1,1)='s' and d.dname like "%S%";

内连接

等值连接

sql 99语法
select 
	e.ename,d.dname
from 
	emp e 
inner join 
	dept d 
on 
	e.deptno = d.deptno
where 
	substr(e.ename,1,1)='s' and d.dname like "%S%";

非等值连接

select 
	e.ename,e.sal,s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.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 |
+--------+---------+-------+

自连接

查询员工的上级领导，要求显示员工名和对应的领导名

select
	a.ename ename,b.ename leadname
from
	emp a
join
	emp b
on
	a.empno = b.mgr
;

+-------+----------+
| ename | leadname |
+-------+----------+
| FORD  | SMITH    |
| BLAKE | ALLEN    |
| BLAKE | WARD     |
| KING  | JONES    |
| BLAKE | MARTIN   |
| KING  | BLAKE    |
| KING  | CLARK    |
| JONES | SCOTT    |
| BLAKE | TURNER   |
| SCOTT | ADAMS    |
| BLAKE | JAMES    |
| JONES | FORD     |
| CLARK | MILLER   |
+-------+----------+
没有king 因为king的领导编号为null，他是最大的领导。
需要外连接来查询
select e.ename ename,d.ename leadname
from emp e
right join
	emp d
on e.mgr = d.empno;

+--------+----------+
| ename  | leadname |
+--------+----------+
| 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.ename leadername,e.ename ename
    -> from emp e
    -> right join emp d
    -> on e.mgr = d.empno
    -> order by e.mgr;
+------------+--------+
| leadername | ename  |
+------------+--------+
| SMITH      | NULL   |
| ALLEN      | NULL   |
| WARD       | NULL   |
| MARTIN     | NULL   |
| TURNER     | NULL   |
| ADAMS      | NULL   |
| JAMES      | NULL   |
| MILLER     | NULL   |
| JONES      | SCOTT  |
| JONES      | FORD   |
| BLAKE      | TURNER |
| BLAKE      | ALLEN  |
| BLAKE      | WARD   |
| BLAKE      | JAMES  |
| BLAKE      | MARTIN |
| CLARK      | MILLER |
| SCOTT      | ADAMS  |
| KING       | BLAKE  |
| KING       | CLARK  |
| KING       | JONES  |
| FORD       | SMITH  |
+------------+--------+

外连接

右外连接:表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表，在外连接当中，两张表连接，产生了主次关系。

左连接：就是join左边的表是主表，右边的表是次表

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

任何一个左连接都有右连接写法！

多表联查之内外连接例题

找出每个员工的部门名称、薪资等级、上级领导

显示出员工名、领导名、部门、薪资、薪资等级

select e.ename '员工名',l.ename '领导名',d.dname,e.sal,g.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade g
on e.sal between g.losal and g.hisal
left join emp l
on e.mgr = l.empno;

+--------+--------+------------+---------+-------+
| 员工名 | 领导名 | dname      | sal     | grade |
+--------+--------+------------+---------+-------+
| SMITH  | FORD   | RESEARCH   |  800.00 |     1 |
| ALLEN  | BLAKE  | SALES      | 1600.00 |     3 |
| WARD   | BLAKE  | SALES      | 1250.00 |     2 |
| JONES  | KING   | RESEARCH   | 2975.00 |     4 |
| MARTIN | BLAKE  | SALES      | 1250.00 |     2 |
| BLAKE  | KING   | SALES      | 2850.00 |     4 |
| CLARK  | KING   | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES  | RESEARCH   | 3000.00 |     4 |
| KING   | NULL   | ACCOUNTING | 5000.00 |     5 |
| TURNER | BLAKE  | SALES      | 1500.00 |     3 |
| ADAMS  | SCOTT  | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE  | SALES      |  950.00 |     1 |
| FORD   | JONES  | RESEARCH   | 3000.00 |     4 |
| MILLER | CLARK  | ACCOUNTING | 1300.00 |     2 |
+--------+--------+------------+---------+-------+

子查询

select中嵌套select

select
	(select)
from
	(select)
where
	(select)

查询比最低工资高的员工

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

DML

快速复制表

create table emp2 as select * from emp;

将一个查询结果当做一张表新建。

create table mydata as select empno,ename from emp where job='MANAGER';

将查询结果插入到表中

create table dept_bak as select * from dept;
insert into dept_bak select * from dept;

快速删除表中数据

delete from dept_bak;
delete属于DML删除数据的原理：
删除数据后，数据占用的空间并不会释放，优点在于可以回滚rollback，可以理解为逻辑上删除，但是在硬盘上的存储空间不会释放。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from dept_bak;
Query OK, 8 rows affected (0.00 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select *  from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)

truncate语句删除（属于ddl）

物理删除，这种删除效率比较高，但是不支持回滚。
mysql> truncate table dept_bak;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

约束

创建表的时候，给表字段加上约束，保证整个表中的数据的完整性、有效性。

非空约束

not null;
用来标注一个字段不能为空

唯一约束

unique
整个表中该字段不能重复
两个字段联合起来唯一(表级约束)
drop table if exists t_vip
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255)
	unique(name,email)
)

主键约束

primary key;
当一个字段同时被unique + not null约束时，在mysql中就是成了主键字段（oracle中不一样）
主键值是每一行的身份证号，没有主键表没有意义。
drop table if exists t_vip
create table t_vip(
id int primary key auto_increament,
	name varchar(255),
	email varchar(255)
);
	

复合主键
drop table if exists t_vip
create table t_vip(
	id int ,
	name varchar(255),
	email varchar(255),
	primary key(id,name)
);
实际开发中不建议使用复合主键

一个表中的主键约束只能有一个，不能添加两个。主键值一般都是定长的，建议使用int bigint char。

外键约束

foreign key;
加上fk的字段的记录必须在另外一张表的出现.
外键的出现是为了解决数据冗余问题，避免重复的数据存储，造成空间上的浪费
用了外键约束以后表级关系存在父子关系，删除时先删除子表，再删除父表。创建时先创建父，再创建子表。
drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	class_no int primary key,
	class_name varchar(255)
)

create table t_student(
	id int primary key,
	name varchar(255),
	class_no int,
	foreign key(class_no) references t_class(class_no)
)

被引用的字段必须具有唯一性，可以为空。

约束