Mysql【动力节点老杜学习笔记】

Mysql 【动力节点老杜】

语雀笔记链接 ：
https://www.yuque.com/docs/share/3edd6ff7-ffcb-4646-959f-3f3d6b6f17aa?# 《mysql老杜》

1、数据库概述

1、MySQL安装与配置

路径

查看服务

使用命令打开 win键+r 输入services.msc

手动查看

1、MySQL登录

打开cmd
输入：
mysql -u+用户名 -p+密码
显示mysql>sql则成功

MySQL修改密码

方法1： 用SET PASSWORD命令
首先登录MySQL。
格式：mysql> set password for 用户名@localhost = password(‘新密码’);
例子：mysql> set password for root@localhost = password(‘123’);

方法2：用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123

方法3：用UPDATE直接编辑user表
首先登录MySQL。
mysql> use mysql;
mysql> update user set password=password(‘123’) where user=‘root’ and host=‘localhost’;
mysql> flush privileges;

方法4：在忘记root密码的时候，可以这样
以windows为例：

1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges;　。
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

1、MySQL卸载（很干净）

1、卸载

1、初始MySQL

1.1 sql \ DB \ BMS是什么，之间的关系

1.2什么是表？

1.3 SQL语句分类

DQl (数据查询语言)：select语句
DML (数据操作语言): insert、delete、update对表数据的增删改
DDL（数据定义语言）：create、drop、alter对数据结构的增删改
TCL（事务控制语言）：commit提交，rollback回滚(Transaction)
DCL（数据控制语言）：grant授权,revoke撤销权限

1.4导入数据

注意标有：（这个不是SQL语句，属于MySQL语句的命令）
说明该语句只适用于MySQL，不是标准的SQL语句

学习用的测试数据（可自己创建bjpowernode.sql文件，然后鼠标拖拽文件，用source命令导入数据）

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;
 

1.5 source命令执行sql脚本

1.6删库跑路

语句：drop database 数据库名

1.7查看表结构以及表数据

表结构

查看表：
mysql> show tables;

查看dept表结构：（desc 表名）
mysql> desc dept;

查看emp表结构：
mysql> desc emp;

查看salgrade表结构：
mysql> desc salgrade;

表数据

mysql> select * from dept;

mysql> select * from emp;

mysql> select * from salgrade;

1.8常用命令

查看当前使用的是那个数据库
mysql> select database();

---

查看mysql版本号
mysql> select version();

---

命令,结束一条语句
\c

---

退出mysql数据库
exit
都不行就Ctrl+c强行终止

1.9查看创建表的语句

mysql> show create table emp;

查看其他数据库有哪些表。
mysql> show tables from mysql;

1、select查询语句（DQL）

1.1简单查询

Select语法格式

Select 字段名1，字段名2，…form 表名;

*提示：
1、任何一条sql语句以“;”结尾
2、sql语句不区分大小写
3、标准sql语句中要求字符串使用单引号括起来，虽然MySQL双引号也支持，为了sql使用标准，建议使用单引号

查询员工的年薪？（字段可以参与数学计算）

mysql> select ename,sal*12 from emp;

给查询结果的列取别名？ as 别名

mysql> select ename,sal*12 as year from emp;
±-------±---------+
| ename | year |
±-------±---------+

别名有中文？ as ‘别名’

select ename,sal*12 as 年薪from emp;//错误
mysql> select ename,sal12 as’ 年薪’from emp;

as 关键字可以省略？
mysql> select ename,sal * 12 year from emp;

查询所有字段

Select * from emp;

1.2 条件查询

语法格式：

select
字段，字段…
from
表名
where
条件;

执行顺序：先from，然后where，最后select

查询工资等于5000的员工姓名？
mysql> select ename from emp where sal=5000;

查询SMITH的工资？
mysql> select sal from emp where ename=‘SMITH’; //字符串单引号

找出工资高于3000的员工？
mysql> select ename,sal from emp where sal>3000;

*找出工资不等于3000的员工？
mysql> select ename,sal from emp where sal <> 3000;
mysql> select ename,sal from emp where sal != 3000;

找出工资在1100和3000之间的员工，包括1100和3000？
mysql> select ename,sal from emp where sal>=1100 and sal<=3000;
mysql> select ename,sal from emp where sal between 1100 and 3000;

between…and…是闭区间[…,…] 使用时必须左小右大

Between…and…

Between…and…
除了可以使用在数字方面，还可以使用在字符串中
*Between and 在数字中包含的是[…,…]左右开区间
*以下代码得出结论Between…and…在字符串中是[…,…) 的区间左闭右开

mysql> select ename from emp where ename between ‘A’ and ‘B’;

mysql> select ename from emp where ename between ‘A’ and ‘D’;

is null 和 is not null

找出哪些人津贴为null？
注意：
在数据库中NULL不是一个值，代表什么也没有，为空
空不是一个值，不能用等号衡量。
必须用is null 和 is not null

mysql> select ename,sal,comm from emp where comm is null;

//mysql> select ename,sal,comm from emp where comm = null;这样是错误的，没有任何数据
//Empty set (0.01 sec)

找出哪些人津贴不为null？
mysql> select ename,sal,comm from emp where comm is not null;

找出哪些人没有津贴？
mysql> select ename,sal,comm from emp where comm is null or comm = 0;

in 和 or 优先级问题

找出工作岗位是MANAGER 和 SALESMAN的员工
mysql> select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;

and 和or联合起来用:
找出薪资大于1000的并且部门编号是20或30的部门的员工。

错误：
mysql> select ename,sal,deptno from emp where sal>1000 and deptno = 20 or deptno = 30;
*注意：当运算符的优先级不确定的时候加小括号。
mysql> select ename,sal,deptno from emp where sal>1000 and (deptno = 20 or deptno = 30);

条件查询in

in 等同于or:
找出工作岗位是MANAGER 和 SALESMAN的员工？
mysql> select ename,job from emp where job = ‘SALESMAN’ or job = ‘MANAGER’;

用in
mysql> select ename,ename,job from emp where job in(‘SALESMAN’,‘MANAGER’);

mysql> select ename,sal,job from emp where sal in(800,5000);
//in后面的值不是区间，是具体的值；=

not in:不在这几个值当中
mysql> select ename,sal,job from emp where sal not in (800,5000);

模糊查询like

找出名字当中含有o的？
(在模糊查询中，必须掌握的两个符号，一个时%，一个是_
%代表任意多个字符，_表示任意1个字符
)
mysql> select ename from emp where ename like ‘%O%’;

找出名字中第二个字母是A的
mysql> select ename from emp where ename like ‘_A%’;

*找出名字中有下滑线的？
使用转义 _
select name from t_user where name like ‘%_%’;

找出名字中最后一个字母是T的
mysql> select ename from emp where ename like ‘%T’;

1.3排序（升序、降序）

按照工资升序，找出员工名和薪资？
Order by
asc 表示升序，desc表示降序
(注意默认升序)
Select ename,sal from emp order by sal;(升序)
Select ename,sal from emp order by sal asc;(升序)

mysql> select ename,sal from emp ename order by sal desc; (降序)

*按工资降序排列，当工资相同时再按照名字的升序排列：
mysql> select ename,sal from emp order by sal desc,ename asc;
注意：越靠前的字段越能起到主导作用。只要当前面的字段无法完成排序的时候，才会启用后面的字段

mysql> select ename,job,sal from emp where job = ‘SALESMAN’ order by **sal **desc;

执行顺序

证明select 比 order by 先执行
mysql> select ename,sal as salary from emp order by salary;

自己想的:
*证明先where再select;
mysql> select ename,sal as s from emp where s>1000 order by s;
ERROR 1054 (42S22): Unknown column ‘s’ in ‘where clause’

1.4分组函数

1.4.1多行处理函数

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

记住：所有的分组函数都是对”某一组”数据进行操作的。

找出工资总和？
mysql> select sum(sal) from emp;
找出最高工资？
mysql> select max(sal) from emp;
找出最低工资？
mysql> select min(sal) from emp;
找出平均工资？
mysql> select avg(sal) from emp;
找出总人数？
mysql> select count(ename) from emp;

分组函数一共5个，分组函数还有另一个名字：多行处理函数
多行处理函数的特点：输入多行，最终输出的结果是1行。

---

分组函数自动忽略NULL

mysql> select count(comm) from emp;

忽略NULL

Select sum(comm) from emp;

Select sum(somm) from emp where comm is not null;//不需要额外添加这个条件，sum函数自动忽略null

*找出工资高于平均工资的员工？
mysql> select avg(sal) from emp;//平均工资

mysql> select ename,sal from emp where sal > avg(sal);
//ERROR 1111 (HY000): Invalid use of group function
*思考以上错误：无效使用了分组函数
原因：SQL语句当中有一个语法规则，分组函数不能直接使用在where子句当中。Why？
怎么解释？
因为group by是在where执行之后才执行的。

找出工资高于平均工资的员工？
第一种方法：
第一步：找出平均工资
mysql> select avg(sal) from emp;

第二步：找出高于平均工资的员工
mysql> select ename,sal from emp where sal > 2073.214286;

第二种方法：
mysql> select ename,sal from emp where sal > (select avg(sal) from emp);//使用子查询

count(*) 和count(具体的某个字段)有什么区别

count():不是统计某个字段中数据的个数，而是统计总记录条数。(和某个字段无关)
count(comm):表示统计comm字段中不为null的数据总数量
mysql> select count() from emp;

select count(*) from emp; select count(comm) from emp;

分组函数也可以组合起来用：
mysql> select count(),count(comm),sum(sal),avg(sal),max(sal),min(sal) from emp;

1.4.2单行处理函数

什么是单行处理函数？
输入一行，输出一行。

计算每个员工的年薪？
mysql> select ename,(sal+comm)*12 as yearsal from emp;
mysql> select ename,(sal+null)*12 as yearsal from emp;
重点：所有数据库都是这样规定的，只要有NULLl参与的运算结果一定是NULL

解决方法：使用ifnull函数：
mysql> select ename,(sal+ifnull(comm,0))*12 from emp;

Ifnull() 空处理函数？

Ifnull(可能为NULL的数据，被当做什么处理)：属于单行处理函数
对null进行预处理

mysql> select ename,ifnull(comm,0) as comm from emp;

1.5 group by 和 having

group by :按照某个字段或者某些字段进行分组。
Having : having 是对分组后的数据进行再次过滤。

---

案例：找出每个工作岗位的最高薪资**。**
mysql> select job,max(sal) from emp group by job;

*注意：分组函数一般都会和group by 联合使用，这也是为什么他被称为分组函数的原因。并且任何一个分组函数（count sum avg max min）都是在group by 执行之后才会执行的。当一条sql语句没有group by 的话，整张表的数据会自成一组。

mysql> select ename,max(sal),job from emp group by job;
*以上代码在mysql当中，查询结果时有的，但是结果(ename)没有意义，在Oracle中会报错。语法错误。Oracle的语法规则比mysql语法规则严谨。
记住一个规则：当一条语句中有group by 的话，select 后面只能跟分组函数和参与分组的字段。

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

多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job;

找出每个部门的最高薪资，要求显示薪资大于2500的数据。
*问题要一个一个的解，分成多块解决

第一步：每个部门的最高薪资
Select max(sal),deptno from emp group by deptno;

第二步：工资大于2900
Select max(sal),deptno from emp group by deptno having max(sal)>2900;

上面解决方法效率低
mysql> select max(sal),deptno from emp where sal>2900 group by deptno;
//这个效率较高，建议能够使用where的尽量使用where

但是having存在即有理，看看where搞不定的？
找出每个部门的平均工资，要求薪资大于2000的数据
第一步：每个部门的平均工资
mysql> select deptno,avg(sal) from emp group by deptno;

第二步：要求薪资大于2000的数据
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

Where后面不能使用分组函数
mysql> select deptno,avg(sal) from emp avg(sal)>2000 group by deptno having ;//错误了。这种情况只能用having过滤。
//我的看法：where判断已有的数据可以。经过你计算(或者说分组函数)得出的数据，不能能直接使用在where中

1.6 DQL语句的执行顺序

Select 5
…
From 1
…
Where 2
…
group by3
…
Having 4
…
order by 6
…

1.7除去重复记录
关键字distinct
例子：查看有哪些工作

没有除重
mysql> select job from emp;

出重后
mysql> select distinct job from emp;

mysql> select ename,distinct job from emp;
以上sql语句是错误的
记住：distinct只能出现在所有字段的最前面。

mysql> select distinct deptno,job from emp;//distinct在最前面表示联合去重

案例：
统计岗位的数量。
mysql> select count(distinct job) from emp;

2、连接查询

2.1什么是连接查询？

在实际开发中，大部分的情况下都不是单表查询数据，一般都是多表查询取出最终的结果。
在实际开发中，一般一个业务都会对应多张表格，比如：学生和班级，起码两张表。

2.2 连接查询的分类？

根据语法出现年代来划分？
SQL92（一些老的DBA可能还在使用这种语法。DBA: DataBase Administrator, 数据库管理员）
SQL99（比较新的语法）

根据表的连接方式来划分？
1、内连接： (inner可以省略
等值连接 … inner join … on 条件 where (=)
非等值连接 … inner join … on 条件 where (between and)
自连接
**2、外连接： ** (outer可以省略
左外连接（左连接）表示左边的这张表是主表 …A **left **outer **join …**B on 左为主，右为副
右外连接（右连接）表示右边的这张表是主表 …B **right **outer join …A on 右为主，左为副
3、全连接：

内外连接区分就是有无left,right
内连接是查到了匹配就显示出来 （就像同级员工，是平等的。查到了给有效数据）
外连接是查到了匹配显示出来，没匹配的也要显示 （就像老板要员工干活，要所有的数据，主副关系）

2.3连接查询原理和笛卡尔积现象(笛卡尔乘积现象)

案例：找出每个员工的部门名，要求显示员工名和部门名
EMP表

DEPT表

mysql> select ename,dname from emp,dept;
//ename和dname要联合起来一块显示，粘到一块。

。。。。共56条记录
56 rows in set (0.01 sec)

笛卡尔现象：当两张啊表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的成绩。

关于表的别名：
mysql> select e.ename,d.dname from emp e,dept d;
表的别名有什么好处？
第一：执行效率高
第二：可读性好

2.4怎么避免笛卡尔积现象？当然是加条件进行过滤。
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是56次，只不过显示的是有效记录。

案例：找出每个员工的部门名，要求显示员工名和部门名
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno; //SQL92，以后不会用

2.5内连接

等值连接

内连接之等值连接：最大特点：条件是等价的；
案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92(太老了，不用了)
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

SQL99：(常用的) join no
mysql> select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;

语法：
…
A
join
B
on
连接条件
Where
…
SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了

// inner 可以省略的，带着inner目的是可读性好一些。
mysql> select
-> e.ename,d.dname
-> from
-> emp e
-> inner join
-> dept d
-> on
-> e.deptno = d.deptno;

非等值连接

内连接值非等值连接：最大的特点是：连接条件中的关系是非等量关系。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级

//inner 可以省略
mysql>
select
e.ename,e.sal,s.grade
from
emp e
inner join
Salgrade s
on
e.sal between s.losal and s.hisal;

自连接

自连接：最到的特点是：一张表看做两张表。自己连接自己
案例：找出每个员工的上级领导，要求显示员工名和对应领导名。
mysql> select empno,ename,mgr from emp;
员工表

员工表里面有领导

---

mysql> select a.ename ‘员工’,b.ename ‘领导’ from emp a inner join emp b on a.mgr = b.empno;

2.6、外连接？

什么是外连接，和 类连接有什么区别？
** 内连接：**
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的数据查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。

外连接：
** 假设A和B表进行连接，使用外连接的话，AB两张表中一张表是主表**，一张表是副表，主要查询主表的数据，捎带着查询副表。当副表中的数据没有和主表中的数据匹配上，附表自动模拟出NULL与之匹配。

外连接分类
左外连接（左连接）表示左边的这张表是主表 left join …on （left outer join …on）
右外连接（右连接）表示右边的这张表是主表** right join…on （right outer join …on）**

左连接有右连接的写法，右连接也会有左连接的写法。

案例：找出每个员工的上级领导
内连接
mysql> Select a.ename ‘员工’,b.ename ‘领导’ from emp a join emp b on a.mgr = b.empno;

---

外连接：

左连接

mysql> Select a.ename ‘员工’,b.ename ‘领导’ from emp a left join emp b on a.mgr = b.empno;

右连接

mysql> Select a.ename ‘员工’,b.ename ‘领导’ from emp b right join emp a** on **a.mgr = b.empno;

外连接的最重要的特点是：主表的数据无条件的全部查询出来。
案例:找出哪个部门没有员工？

mysql>
//Select e.,d. from
Select d.* from
dept d
left join
emp e
on
e.deptno = d.deptno
where
e.empno is null;

2.7三张表以上怎么查询？
案例：找出每个员工的部门名称以及工资等级。
。。。
A
join
B
join
C
on
。。。

表示：A表和B表先进行连接，连后A继续与C表进行连接。
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 hisal;

2.7多表查询

案例：找出每个员工的部门名称、工资等级、以及上级领导。

mysql> select e.ename ‘员工’,d.dname,s.grade,e2.ename ‘领导’ from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal left join emp e2 on e.mgr = e2.empno;

3、子查询

3.1什么是子查询？可以出现在哪里？
Select 语句中嵌套select语句，被嵌套的select语句是子查询
子查询可以出现在哪里？

3.2 where中使用子查询

案例：找出高平均工资的员工信息
Select * from emp where sal>avg(sal);//错误写法，where后面不能直接使用分组函数。Where在分组之前

第一步找出平均薪资
Select avg(sal) from emp;
第二步where过滤
mysql> select * from emp where sal > 2073.214286;

双剑合璧
mysql> select * from emp where sal>(select avg(sal) from emp);

3.3 from后面嵌套子查询

案例：找出每个部门平均薪水的薪资等级。
第一步：找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将以上的查询结果当做临时表t,让t表和salgrade s表连接。
条件是: t.avgsal between s.losal and s. hisal;

mysql> select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal;

案例：找出每个部门平均薪资等级。
第一步：每个员工的薪资等级
mysql> select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;

第二步：基于以上结果，继续按照deptno分组，求grade平均值。
mysql> select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and hisal group by e.deptno;

我的错误：
mysql> select t.deptno,avg(t.grade) from (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t group by t.deptno;
这一题我使用了子查询，其实根本不需要。
我的结论:当查询出的数据是新的数据（不是源数据)。才当做临时表，使用子查询。
上面的题查询出来是本来就有的源数据，不需要当做临时的表，使用子查询会降低效率。

3.4 select后面嵌套子查询。

案例：找出每个员工所在的部门名称，要求显示员工和部门名。
老方式：

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

4、union(可以将查询结果集相加)

案例：找出工作岗位是SALEMAN和MANAGER的员工？
第一种：mysql> select ename,job from emp where job=‘manager’ or job=‘salesman’;
第二种：mysql> select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);

第三种：union
mysql> select ename,job from emp where job = ‘manager’
-> union
-> select ename,job from emp where job = ‘salesman’;

两张不相干的数据拼接在一起显示。
mysql> select ename from emp
-> union
-> select dname from dept;

我的理解就是缝合怪，把表和在一起，但是字段必须相同，不然对不上号。

5、Limit

（重点中的重点，以后分页查询全靠它）

limit x,y x下标默认从0开始，y是要多少个length
5.1 limit 是mysql特有的，其他数据库中没有，不通用（Oracle有一个相同的机制，叫做rownum）

5.2limit取结果集中的部分数据，这是它的作用。

5.3语法机制：
limit startIndex,length
startIndex表示起始位置，从0开始。
length表示取几个

案例：取出工资前5名的员工。（思路：降序取5个）

mysql> select ename,sal from emp order by sal desc;
取前5个：
mysql> select ename,sal from emp order by sal desc limit 0,5;
mysql> select ename,sal from emp order by sal desc limit 5;
5.4执行顺序

5.5案例：找出工资排名在第4到底9名的员工？
mysql> select ename,sal from emp order by sal desc limit 3,6;

5.6、通用的标准分页sql?
每页显示3条记录
第1页：0,3
第2页：3,3
第3页：6,3
第4页：9,3
第5页：12,3

每页如果是pageSize条记录
第pageNo页：pageSize(pageNo-1),pageSize

6. 创建表

键表语法
Creat table 表名(
字段1 数据类型 (约束),
字段2 数据类型 (约束),
…
字段x 数据类型 (约束)
)
起名：见名知意思

数据类型，常见的。

char 和varchar怎么选择

BLOB和CLOB类型的使用。

创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日

创建学生表：
mysql> create table t_student(
-> no bigint,
-> name varchar(255),
-> sex char(1),
-> classno varchar(255),
-> birth char(10)
-> );

7、insert插入数据

语法格式：
Insert into 表名(字段1，字段2，…) values(值1，值2，…),
(值1，值2，…);

要求：字段的数量和值得数量相同，并且数据类型要求对应相同。
mysql> insert into t_student(no,name,sex,classno,birth) values(201902140837,‘zoubin’,‘1’,‘java1908’,‘2020-12-07’);

//字段位置换了没关系，只要值相对应
Inset into t_student(name,sex,classno,birth,no) values(…);

字段可省略，默认值NULL

mysql> drop table if exists t_student; //当这个表存在的话删除

mysql> create table t_student(
-> no bigint,
-> name varchar(255),
-> sex char(1) default 1, //default设置默认值1
-> classno varchar(255),
-> birth char(10)
-> );

需要注意：

//字段可以省略，但是values后面的值，必须一一对应

//一次插入多行数据
Insert into t_student() values(),(),()…();

8、表的复制

语法：
create table 表名 as select 语句;
将查询结果当做表创建出来

将查询结果插入一张表中？条件：表的结构必须相同。插入的字段必须一致

10、修改数据:update

语法
Update 表名 set 字段名1=值1,字段名2=值2,… where 条件;

注意：没有条件整张表数据更新；
mysql> update t_student set name=‘zb’ where no=3;

更新所有数据

11、删除数据

语法格式：
delete from 表名where 条件;

注意:没有条件全部删除；

怎么删除大表中的数据？（重点）

truncate table 表名；

把表截断，不可回滚。永久删除。

数据全删除，但表结构还在；

12 、表结构的修改：

老杜：“用工具”；术语CRUD

弹幕：
DQL(select) DML(insert delete update) DDL(create drop alter)

增加列定义的语法：alter table 表名 add(字段名1 数据类型,字段名2 数据类型 …)

修改列定义的语法：alter table 表名 modify 字段名 数据类型 [default 默认值] [修改该列到位置]
【】里的值可以不指定，修改列定义的语句只能修改一个列定义
例如：alter table t_stuInfo modify Grade char(2) null first。

重命名数据表的语法：alter table 表名 rename to 新表名;
还有一个完全改变列定义change。但是不常用，一般modify就足够了

13、约束(Constraint)

13.1什么是约束？常见的约束有哪些呢？

13.2 非空约束 not null

mysql> drop table if exists t_user;
mysql> create table t_user(
-> id int,
-> username varchar(255) not null,
-> password varchar(255)
-> );

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

mysql> insert into t_user(id,username,password) values(1,‘lili’,‘123’);

1.3.3唯一约束：(unique)

1. 唯一约束（unique）
唯一约束：字段后面unique
多个唯一约束：unique(字段名1,字段名2,…); //多个字段联合起来添加的1个约束【表级约束】

*注意not null约束只有列级约束，没有表级约束。

给两个或者多个列添加unique:

两个列约束

单个约束。

1.3.4主键约束：primary key

*怎么给一张表添加主键约束呢?
primary key //列级约束

*主键相关的术语？
主键约束 ：primary key
主键字段 : id字段添加primary key 后，叫做主键字段。
主键值 : id中的每一个值都是主键值。

*主键有什么用？
-表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
-主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）

*主键的分类；
根据主键的字段数量来划分：
单一主键（推荐，常用的）
复合主键（多个字段联合其起来添加一个主键约束）
（复合主键不建议使用，因为复合主键违背三范式）
根据主键性质来划分：
自然主键：主键值和业务没有任何关系的自然数（推荐）
业务主键：主键值之和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证作主键。（不推荐用）
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发 改变，主键值也可能需要随着发生变化，但有的时候没有办法
变化，因为变化可能会导致主键值重复

一张表的主键约束只有1*个（必须记住）

*使用表级约束方式定义主键:
mysql> drop table if exists t_user;
mysql> create table t_user(
-> id int,
-> username varchar(255),
-> primary key(id)
-> );
mysql> insert into t_user(id,username) values(1,‘a1’),
-> (2,‘a2’),(3,‘a3’) ;
mysql> insert into t_user(id,username) values(1,‘aa’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
以下内容是演示一下复合主键，不需要掌握：
mysql> drop table if exists t_user;
mysql> create table t_user(
-> id int,
-> username varchar(25),
-> primary key(id,username)
-> );

主键值自增auto_increment

*mysql提供的主键值自增：（非常重要）
mysql> drop table if exists t_user;
mysql> create table t_user(
-> id int primary key auto_increment, //id字段自动维护一个自增的数字，从1开始，以1递增
-> username varchar(25)
-> );

mysql> insert into t_user(username) values(‘a’),(‘b’),(‘c’);
mysql> select * from t_user;

提示：Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象

1.3.5外键约束foreign key

*关于外键约束的相关术语：
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值

*业务背景：
请设计数据库表，用来维护学生和班级的信息？
第一种方案：用一张表储存所有数据

*将以上表的建表语句写出来：

mysql> drop table if exists t_student;
mysql> drop table if exists class;

mysql> create table t_class(
-> cno int,
-> cname varchar(225),
-> primary key(cno)
-> );

mysql> create table t_student(
-> sno int primary key,
-> sname varchar(255),
-> classno int,
-> foreign key(classno) references t_class(cno)
-> );

mysql> insert into t_class values(101,‘xxxx’),(102,‘yyy’);
mysql> insert into t_student values(1,‘zs1’,101),(2,‘zs2’,101),(3,‘zs3’,102);
mysql> select * from t_student;

编写成sql文本
mysql> source D:\数据库\Mysql\resources\school.sql

mysql> insert into t_student values(4,‘zs4’,103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bjpowernode.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (classno) REFERENCES t_class (cno))

问题:
*外键值可以为null吗？
外键可以为null
实践证明：
mysql> insert into t_student(sno,sname) values(4,‘zs4’);
Query OK, 1 row affected (0.01 sec)

问题:

Primary key
Foreign key 外键字段 reference 参照表(参照对象)。

1.1、完整的建表语句

mysql> use bjpowernode;
mysql> show desc table t_xt;

这才是完整的建表语句
CREATE TABLE t_xt (
id int(11) DEFAULT NULL,
name varchar(25)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
引擎 =InnoDB 默认 字符集=latin1

查看完整的建库语句：mysql> show create database bjpowernode;

注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

建表的时候可以指定**存储引擎**，也可以指定**字符集**。

mysql**默认**使用的**存储引擎**是**InnoDB**方式。
     **默认**采用的**字符集**是**UTF8**;
是安装时选择的。（终于找到我为什么varchar类型都不支持中文了，原来安装时，字符集默认latin1）
![](https://img-blog.csdnimg.cn/img_convert/fa6ead779c1a151bc356c611a8f0cb25.png#crop=0&crop=0&crop=1&crop=1&height=153&id=ZST4j&originHeight=460&originWidth=627&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=&width=208)

自己建一个支持中文utf-8
CREATE TABLE t_xt (
id int(11) DEFAULT NULL,
name varchar(25)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

14、存储引擎(了解)

2.2、什么是存储引擎呢？

2.3、查看当前mysql支持的存储引擎？

mysql> show engines \G
mysql> select version(); mysql : 5.6.40-log //支持9种存储引擎

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

2.4、常见的存储引擎

1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

要想保证以上的两条DML语句同时成功或者失败，那么久需要使用数据库的“事务机制”

1.MyISAM储存引擎

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO //不支持事务。
XA: NO
Savepoints: NO

MyISAM这种存储引擎不支持事务。
MyISAM是Mysql最常见的存储引擎，但是这种引擎不是默认的。
TCL(Transactions Control Language)

MyISAM采用三个文件组织一张表：
xxx.frm (储存表格式的文件)
xxx.myD （存储表中数据的文件）
xxx.myI （存储表中索引的文件）
优点：可被压缩，节省储存空间，并且可以转换为只读表，提高检索效率。
缺点：不支持事务。

2.InnoDB

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障

表的结构存储在xxx.frm文件中
数据存储在tablespace这样的本表空间中（逻辑概念），无法被压缩，无法转换成只读
这种InnoDB储存引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。

3.MEMORY

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

缺点：不支持事务，数据容易丢失。因为所有数据和索引都是存储在内存当中。
优点：查询速度最快。
以前叫做HEPA引擎。

事务概述（Transaction）

2、和事务相关的语句只有：DML语句。

（insert delete update）只有DML语句才支持事务
为什么？因为他们这三个语句都是和数据库当中的“数据”相关的。
事务的存在是为例保证数据的完整性，安全性。

---

3、假设所有的业务都能使用一条DML语句搞定，还需要事务机制吗？

不需要事务。
但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成

提交：commit 回滚：rollback savepoint(一个保存点，回滚时回到保存点)

Insert
Delete
Update
Savepoint a1;
Delete
Update
…
Rollback a1; //这里会回到savepoint a1
Commit;

4、事务的特性？

事务包括四大特性：ACID
A:原子性：事务是最小的工作单元，不可再分
B:一致性：事务必须保证多条DML语句同时成功或者同时失败。
C:隔离性：事务A与事务B之间具有隔离。
D:持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

5、关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务就可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据（极其不稳定
数据不在硬盘上，在缓存里，一回滚就读不到了，断电也读不到了）
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了：脏读现象没有了
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复读的问题。
这种隔离级别存在的问题是：读取到的数据是幻像。
第四级别：序列化读/串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。

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

6、演示事务

*mysql 事务默认情况下是自动提交的。
（什么是自动提交？只要执行任意一条DML语句则提交一次）
怎么关闭自动提交: start transaction

---

*演示：
drop table if exists t_user;
create table t_user(
Id int primary key auto_increment,
Username varchar(255)
);
*演示：
mysql> insert into t_user(Username) values(‘za’);
mysql> rollback; //回不去了，mysql自动提交
mysql> select * from t_user;

*演示:使用start transatction;关闭自动提交机制
//表示一个事务的开始，再次使用要重新写一遍
mysql> start transaction;
mysql> insert into t_user(username) values(‘z1’);
mysql> insert into t_user(username) values(‘z2’);
mysql> select * from t_user;

mysql> rollback;

演示一下提交commit
mysql> start transaction;
mysql> select * from t_user;

mysql> insert into t_user(Username) values(‘z1’);
mysql> insert into t_user(Username) values(‘z2’);
mysql> commit;
mysql> select * from t_user;

mysql> rollback;
mysql> select * from t_user;

7、使用两个事务演示以上的隔离级别

第一：演示 read uncommitted

演示读未提交（要开两个dos窗口）
先登录
use 数据库 全局 事务 隔离 水平
设置事务的全局隔离级别：mysql> set global transaction isolation level read uncommitted;

查看事务的全局隔离级别: mysql> select @@global.tx_isolation;

设置完后两个dos窗口exit。：
重新登录、
use 数据库、
start transaction;

Dos1 dos2
1、Select * from t_user; 2、insert into t_user(Username) values(‘z3’);
2、Select * from t_user;

Dos1
mysql> select * from t_user;

Dos2
mysql> insert into t_user(Username) values(‘z3’);

Dos1
mysql> select * from t_user; //事务还未commit 就读取到了数据(读未提交)

Dos2
mysql> rollback;

Dos1
mysql> select * from t_user; //数据rollback,就读不到数据了。脏读现象，不稳定，因为数据在内存里

第二：演示read committed

（读已提交）
登录
use 数据库
设置事务的全局隔离级别为：read committed
mysql> set global transaction isolation level read committed;

查看事务的全局隔离级别
mysql> select @@global.tx_isolation;

退出exit

---

打开两个dos窗口
登录
use 数据库
start transactio;

---

Dos1
mysql> select * from t_user;

Dos2
mysql> insert into t_user(Username) values(‘read-committed’);

Dos1
mysql> select * from t_user; //Dos2 数据没commit，所以读不到

Dos2
mysql> commit;

Dos1
mysql> select * from t_user; //读取到了已commit的数据

第三：repeatable read

（可重复读）
这种隔离级别解决了：不可重复读的问题。
这种隔离级别存在的问题是：读取到的数据是幻像。（另一边数据已经更新，而读到的数据 还是以前的内容）

设置事务的全局隔离级别

查看事务的全局隔离级别

退出

登录
Use 数据库；
Start transaction;

Dos1
mysql> select * from t_user;

Dos2
mysql> delete from t_user;
mysql> commit;

Dos1
mysql> select * from t_user; //上面删除数据已经提交，Dos1还是原来的数据，是幻像

第四：演示serializable

(序列化读)
修改和查看事务的全局隔离级别
mysql> set global transaction isolation level serializable;

Exit

登录
Use 数据库;
Start transaction;

Dos1
mysql> select * from t_user;

mysql> insert into t_user(Username) values(‘z5’);

Dos2
mysql> select * from t_user;
//一直等待中，没有读到数据（因为Dos1的事务还没有结束commit）

Dos1
Commit;

Dos2
//Dos1一提交，Dos2数据就读出来了。

1、索引

1.1什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全局扫描
第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索的效率呢？
其实最根本的原因是缩小了扫描的范围；

添加索引是给某一个字段，或者某些字段添加索引

1.2怎么创建索引对象？怎么删除索引对象？
创建索引对象
create index 索引名 on 表名(字段名);
删除索引对象
drop index 索引名 on表名; (Mysql使用)
drop index 索引名; (Oracle可以)

1.3什么时候考虑给字段添加索引（满足什么条件）
*数据量庞大（根据客户的需求，根据线上的环境）
*该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
*该字段经常出现在where子句中。（经常根据哪个字段查询）

1.4注意：主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高，尽量主键检索

1.5查看sql语句的执行计划：
mysql> explain select ename,sal from emp where sal=5000;

给薪资sal字段添加索引:
mysql> create index emp_sal_index on emp(sal);

查看sql执行计划
mysql> explain select ename,sal from emp where sal=5000;

4.6索引底层采用的数据结构是：
B + Tree

---

4.7、索引的实现原理？



通过B Tree 缩小扫描范围，底层索引进行排序，分区，索引会携带数据在表中的”物理地址”
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
Select ename from emp where ename = ‘SMITH’;
通过索引转换为：
Select ename from emp where ename= 物理地址;

---

---

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

1.9索引什么时候失效？
Select ename from emp where ename like ‘%A%’;
模糊查询的时候，第一个通配符使用的是**%，这个时候索引是失效的。**

---

---

1、视图（view）

1、什么是视图？
站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）

2、怎么创建视图？怎么删除视图？
视图名称
创建：mysql>** create view** myview as select empno,ename from emp;
删除：mysql>** drop view** myview;
注意：只有DQL语句才能以视图对象的方式创建出来

3、对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作原数据）
可以对视图进行CRUD操作。

4、面向视图操作？
mysql> select * from myview;

创建emp_bak 把emp表复制到emp_bak表
mysql> create table emp_bak as select * from emp;

创建视图
mysql> create view myview1 as select empno,ename,sal from emp_bak;
mysql> select * from myview;

修改视图
mysql> update myview1 set ename=‘wocao’,sal=1 where empno =7369;
查看表（视图修改，表中数据也修改了）
mysql> select * from emp_bak; //通过视图修改原表数据

mysql> delete from myview where empno=7369; //通过视图删除原表数据

自己玩
mysql> drop table emp_bak; //删除表后

mysql> select * from myview1; //视图还在，但是没有有数据了。

5. 视图的作用？
   视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD.

mysql> create table emp_bak as select * from emp; //创建表emp_bak复制emp
mysql> create view myview2 as select empno a,ename b,sal c from emp_bak; //创建视图

mysql> select * from myview2; //查询,只能看到给的数据，和别名。

1、数据库数据的导入导出

1、将数据库当中的数据导出

在windows的dos命令窗口中执行：
导出 数据库 >导到 D:bj…node.sql 有权限的账户和密码
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -proot123

导出整个数据库：> 前后要有空格，不然报错
mysqldump bjpowernode > D:\bjpowernode.sql -uroot -proot123
导出指定数据库中的表 表名
mysqldump bjpowernode emp > D:\bjpowernode.sql -uroot -proot123

2、导入数据

先要创建数据库，在use数据库，再source导入
mysql> create database bjpowernode;
mysql> use bjpowernode;
mysql> source D:\bjpowernode.sql

1、数据库设计三范式（重点内容，面试经常问）

1、什么是设计范式？
设计表的依据。按照这个三范式设计的表不会出现数据

2、三范式都有哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖

第三范式：建立在第二范式的基础之上，所有非主键的字段直接依赖主键，不能生传递依赖。

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度

---

3.一对一怎么设计？

1、修改MySQL修改默认字符集

查看完整的建库语句：
mysql> show create database bjpowernode;

±------------±-----------------------------------------------------------------------+
| Database | Create Database |
±------------±-----------------------------------------------------------------------+
| bjpowernode | CREATE DATABASE bjpowernode /*!40100 DEFAULT CHARACTER SET latin1 */ |
±------------±-----------------------------------------------------------------------+

然后自己使用完整的建表语句建一个数据库，字符集使用utf8

CREATE DATABASE bjpowernode /*!40100 DEFAULT CHARACTER SET utf8 */；

1、ERROR 1300 (HY000): Invalid utf8 character string: ‘\xC0\xC0’

这是由于CMD字符集是GBK的，而MySQL字符集是UTF-8的

1、临时修改CMD编码为UTF-8 ** chcp 65001**

2、永久修改
注册表修改
https://jingyan.baidu.com/article/d7130635e8a38413fdf4753b.html
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor
命名为“autorun”, 点击右击修改，数值数据填写“chcp 65001”，确定

作业题

第1道
1、取得每个部门最高薪资的人员名称
第一步：取得每个部门最高薪资（按部门编号分组，找出每组最大值）
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
第二步：将以上结果当做临时表t，t表和emp e表进行连接，条件是部门相等，工资相等
mysql> select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on e.deptno=t.deptno and e.sal=t.maxsal;

第2题

哪写人的薪水在部门的平均水平之上
第一步：找出每个部门的平均薪水
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;

---

第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal
mysql> select t.*,e.ename,e.sal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.sal>t.avgsal and e.deptno=t.deptno;

第3题*

取得部门中（所有人）的平均薪资等级
阅读理解：
平均的薪水等级：先找出每一个薪水的等级，然后找出薪水等级的平均值。
平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级。

第一步：找出每个人的薪水等级
Emp e 和 salgrade s表连接
连接条件：e.sal between s.losal and s.hisal
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

第二步：基于以上的结果继续按照deptno分组,求grade的平均值
mysql> select e.deptno,avg(grade) avggrade from emp e join salgrade s on e.sal between s.losal and hisal group by deptno;
自己：通过表连接，获取的数据是原数据，不是通过计算的到的，就不用想成临时表

自己玩：
每个部门平均薪水的等级
第一步：每个部门的平均薪水
mysql> select deptno,avg(sal) avgsal from emp group by deptno;
第二步：基于以上表结果连接表salgrade。
需要建立临时表吗？需要，因为不是原数据，是使用通过计算得出的数据
mysql> select e.deptno,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) e join salgrade s on e.avgsal between s.losal and s.hisal;

第4题

不准用组函数（max）,取得最高薪资（给出两个或两个以上解决方案）
第一种：
排序order by 降序，然后limit 1（order by 字段 asc升序（默认），desc降序
limit 下标，length （下标默认0） ）

mysql> select ename,sal from emp order by sal desc limit 0,1;
select ename,sal from emp order by sal desc limit 1; //一样

**第二种：**mysql> select max(sal) from emp;//可惜题目不允许。

第三种：表的自连接：*
a表 （select sal from emp;） b表

select
distinct a.sal //去重，取出小的数
from
emp a
join
emp b
on
a.sal<b.sal //只要满足小于的
mysql> select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);（使用not in除去小的数，就只剩最大的数）

第5题

取得平均薪水最高的部门的部门编号
第一种方案：
第一步：找出么个部门的平均薪水
mysql> select deptno,avg(sal) from emp group by deptno;

第二步:降序order by,选择第一个limit 0,1;
mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

第二种方案：
mysql> select t.deptno,max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t ; //老师的

mysql> select e.deptno,max(t.avgsal) from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.deptno=t.deptno; //我的写的啥？画蛇添足

二、//老师在搞我呢（套娃）
mysql> select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t);

第6题

取得平均薪水最高的部门名称
mysql> select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by dname order by avgsal desc limit 1; //部门名称分组

mysql> select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by deptno order by avgsal desc limit 0,1; //部门编号分组

第7题

取得平均薪水的等级最低的部门名称
(平均薪水最低，一定是平均薪水等级最低。平均薪水等级最低，不一定是平均薪水最低。)

第一步：找出每个部门的平均薪水的等级
//每个部门的平均薪水
mysql> select deptno,avg(sal) from emp group by deptno;
第二步：找出每个部门的平均薪水的等级（条件以上t表，条件t.avgsal between s.losal and s.hisal）
mysql> select t.* ,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade =(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

最低等级怎么找

mysql> select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
mysql> select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;

第8题

取得比普通员工（员工代码么有在mgr字段上出现的）的最高薪水还要高的领导人
领导

mysql> select distinct mgr from emp;

员工编号没有在以上范围内的都是普通员工

第一步找出普通员工的最高薪水
not in 在使用的时候，后面小括号中记得排除null
mysql> select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

第二步：找出高于1600的
比“普通员工的最高薪”还要高的一定是领导！（是的）
mysql> select empno,sal from emp where sal>(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

第9题

取得薪水最高的前5名员工
mysql> select empno,ename,sal from emp order by sal desc limit 0,5;

第10题

取得薪水最高的第6到底10名员工
（limit 下标，长度 （下标从0开始，默认也是0））
mysql> select empno,ename,sal from emp order by sal desc limit 5,5;

第11题

取得最后入职的5名员工
日期也是可以降序，升序排列的（日期越早越大）
mysql> select ename,hiredate from emp order by hiredate desc limit 5;

第12题

取得每个薪水等级有多少员工
第一步：获取每个员工的薪水的级
mysql> select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;

第二步：按薪水等级分组,然后count(grade)
Count()统计总记录条数 count(字段)统计字段中不为null的记录条数
count() 也可以
mysql> select s.grade,count(s.grade) from emp e join salgrade s on e.sal between s.losal and hisal group by s.grade;

第13题

面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

第14题

列出所有员工及领导的姓名
mysql> select a.ename ‘员工’,b.ename ‘领导’ from emp a left join emp b on a.mgr=b.empno;

第15题

列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp a 员工表
emp b 领导表
a. mgr = b.empno and a.hiredate < b.hiredate

mysql> select a.empno ‘员工编号’,a.ename’员工’,d.dname ‘部门名称’,a.hiredate ‘员工入职日期’,b.ename ‘领导’,b.hiredate ’ 领导入职日期’ from emp a join emp b on a.mgr=b.empno join dept d on a.deptno=d.deptno where a.hiredate<b.hiredate;

mysql> select a.empno ‘员工编号’,a.ename’员工’,d.dname ‘部门名称’,a.hiredate ‘员工入职日期’,b.ename ‘领导’,b.hiredate ’ 领导入职日期’ from emp a join emp b on a.mgr=b.empno and a.hiredate<b.hiredate join dept d on a.deptno=d.deptno;

第16题

列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
emp e
dept d
左外连接
mysql> select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno;

右外连接
select
e.*,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;

第17题

列出至少有 5 个员工的所有部门
按照部门编号分组，计数，筛选出 >= 5
mysql> select deptno from emp group by deptno having count(empno)>=5;

mysql> select e.deptno,d.dname from emp e join dept d on e.deptno=d.deptno group by e.deptno having count(e.empno)>=5;
mysql> select deptno from emp group by deptno having count(*)>=5;

第18题

列出薪金比"SMITH" 多的所有员工信息
mysql> select * from emp where sal>(select sal from emp where ename=“SMITH”);

第19题 *

列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
找出工作是CLERK的
mysql>
select
e.ename,e.job,d.dname,d.deptno
from emp e
Join
dept d
on
e.deptno=d.deptno
where
e.job=‘CLERK’;

//每个部门的人数(临时表)
mysql> select deptno,count() as deptcount from emp group by deptno;

mysql> select t1.,t2.deptcount from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where job=‘CLERK’) t1 join (select deptno,count(*) as deptcount from emp group by deptno) t2 on t1.deptno=t2.deptno;

第20题：
列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
第一不能：按照工作岗位分组求最小值>1500。
mysql> select job from emp group by job having min(sal)>1500;
统计：
mysql> select job,count(*) from emp group by job having min(sal)>1500;

第21题

列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
第一步：找出部门名为SALES的的编号
mysql> select deptno from dept where dname=‘SALES’;
第二步：根据部门编号查员工
mysql> select ename from emp where deptno=(select deptno from dept where dname=‘SALES’);

第22题

列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
第一步：薪金高于公司平均薪金的所有员工
mysql> select empno,ename from emp where sal>(select avg(sal) from emp);

mysql> select a.ename ‘员工’,d.dname,b.ename ‘上级领导’,s.grade from emp a join dept d on a.deptno=d.deptno left join emp b on a.mgr=b.empno join salgrade s on a.sal between s.losal and s.hisal where a.sal>(select avg(sal) from emp);

第23题

列出与"SCOTT" 从事相同工作的所有员工及部门名称
第一步找出SCOTT从事的工作
mysql> select job from emp where ename=‘SCOTT’;

mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.job=(select job from emp where ename=‘SCOTT’) and e.ename<>‘SCOTT’;

第24题

列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
mysql> select distinct sal from emp where deptno=30;

mysql> select ename,sal from emp where sal in(select distinct sal from emp where deptno=30) and deptno<>30;
Empty set (0.00 sec)

第25题

列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
第一步：找出30部门的最高薪资
mysql> select max(sal) from emp where deptno=30;

mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal>( select max(sal) from emp where deptno=30);

第26题

列出在每个部门工作的员工数量, 平均工资和平均服务期限
没有员工的部门，部门人数是0
每个部门工作的员工数量
mysql> select e.,d. from emp e right join dept d on e.deptno=d.deptno;

平均工资
mysql> select d.*,count(e.ename),avg(e.sal) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;

mysql> select d.deptno,count(e.ename),ifnull(avg(e.sal),0) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

平均服务期限avg(系统当前年限-入职年限)
mysql当中怎么计算两个日期之间的时间间隔方法，差了多少年？
TimeStampDiff(间隔类型，前一个日期，后一个日期)

例子：mysql> select timestampdiff(year,hiredate,now()) from emp;

mysql> select d.deptno,count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal,ifnull(avg(timestampdiff(year,hiredate,now())),0) avgservicetime from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

第27题

列出所有员工的姓名、部门名称和工资。
mysql> select e.ename,d.dname,e.sal from emp e left join dept d on e.deptno=d.deptno;

第28题

列出所有部门的详细信息和人数
mysql> select d.*,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
//老杜：分组的字段和分组函数，才能放到select后面（为了sql语句通用，不这样oracle就不能用了）
mysql> select d.deptno,d.dname,d.loc,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;

第29题

列出各种工作的最低工资及从事此工作的雇员姓名
mysql> select job,min(sal) from emp group by job;

emp e表和以上t表连接
mysql> select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job=t.job and e.sal=t.minsal;

第二种
mysql> select ename,job,sal from emp where sal in(select min(sal) from emp group by job);

第30题

列出各个部门的 MANAGER( 领导) 的最低薪金
mysql> select deptno,min(sal) from emp where job=‘MANAGER’ group by deptno;

第31题

列出所有员工的年工资, 按年薪从低到高排序
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal;

32、求出员工领导的薪水超过3000的员工名称与领导
mysql> select a.ename ‘员工’,b.ename ‘领导’ from emp a join emp b on a.mgr=b.empno where b.sal>3000;

第33题

求出部门名称中 带’S’字符的，部门员工的工资合计、部门人数
mysql> select d.deptno,d.dname,d.loc,ifnull(sum(e.sal),0) as sumsal,count(e.ename) from emp e right join dept d on e.deptno=d.deptno where d.dname like ‘%S%’ group by d.deptno,d.dname,d.loc;

34、给任职日期超过 30 年的员工加薪 10%.
看看任职年限
mysql> select empno,ename,timestampdiff(year,hiredate,now()) from emp;

mysql> update emp set sal=sal*1.1 where timestampdiff(year,hiredate,now())>30;
Query OK, 14 rows affected (0.01 sec)
Rows matched: 14 Changed: 14 Warnings: 0

mysql> select ename,timestampdiff(year,hiredate,now()) as years,sal*1.1 newsal from emp where timestampdiff(year,hiredate,now())>30;

如有错误，或者是其他问题，还望指出。