MySQL笔记

DB ：数据库：在硬盘上以文件的方式存在
DBMS：数据库管理系统：例如mysql，oracle，sqlserver…
SQL：一门适用于所有DBMS，对DB进行操作的标准规划语言，
DQL：数据库查询语句 select,
DML：数据库管理语句，修改表中数据update,insert,delete,
DDL：数据库管理语句，修改表中的结构drop,create,alter,
TCL：commit提交事务,rollback回滚事务,
DCL：grant授权，revoke撤销权限

一.DOS窗口命令

DOS窗口进入数据库 mysql -uroot -p密码
查看数据库：show databases；
创建数据库：create database 数据库名字；
使用某个数据库：use 数据库名字；
查看使用数据库中的表：show tables；
数据库初始化执行sql文件：source +sql文件路径（直接拖就行）
删除数据库：drop database 数据库名字；
查看表结构：desc+表名；
查看表中数据：select * from 表名；（当在DOS界面中显示MYSQL数据库中的表的信息时，可能会出现中文乱码问题，出现是原因是因为DOS窗口默认字符集为GBK格式，如果当前MYSQL设置的默认编码格式非GBK格式，则可能会出现乱码。
解决方法：在输出信息前，执行以下语句：
set names gbk;）
查询当前使用的哪个数据库：select database（）；也可直接再use一下需要的数据库；
查询mysql的版本号：select version（）；
结束一条语句：\c 命令
退出mysql：exit 命令
查看创建表的时候使用的sql语句：show create table 表名；

脚本链接：https://pan.baidu.com/s/1IaeODXfw6oPVjQgyI1BA5A 
提取码：1234

二.DQL

单表简单查询:

1.简单的查询语句（DQL）

语法格式：select 字段名1，字段名2 *12，字段名3 as ’表名‘ ，… from 表名；

字段二表示字段可以进行数学运算；
字段三表示可以重新为该字段命名，字符串用单引号（as可以省略，用空格替换！）；

2.查询所有字段：

select * from 表名注意：实际开发中不建议*因为效率较低

3.条件查询格式：

select 字段，字段…from 表名 where 条件；条件（> ,>=,<,<=,<>或者！=，between 小数字 and 大数字 ,）

运算符	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and …	两个值之间
is null	为null（is not null不为空）
and	并且
or	或者
in	包含，相当于多个or（not in 不在这个范围中）
not	not可以取非，主要用在is或in中
like	like成为模糊查询，支持%或_匹配

in后面不是区间而是具体的值in（800，1000）意思是要么800要么1000

4.模糊查询like

(必须掌握两个符号，一个是%，一个是_)

%表示任意多个字符,__代表一个字符。如果_或%需要转化为普通字符就在前面加‘ \ ’ 起到转义的作用。

5.排序(升序和降序)：

默认指的是升序

格式：select 字段1，字段2，from表名 order by 字段名 asc（升序）；

select 字段1，字段2，from表名 order by 字段名 desc（降序）；

如果按照某个字段排序，而该字段又相等则再在后面的字段加上，第二个字段名 asc/desc；

例如：select 字段1，字段2，from表名 order by 字段名1 desc，字段名2 asc；（越靠前的字段越能起主导作用，order by后面是最后执行的）

6.分组函数：

所有的分组函数都是对某一组函数进行操作的注意：分组函数自动忽略null，统计不上并且分组函数不能写在where语句后面，分

组函数即使没有group by也自己成为一组，where的优先级高于group by，所以where不执行完，分组函数执行不了。

count计数：select count(*) from 表名； //统计的是总记录条数。

select count（字段名）from 表名； //统计的是当前字段不为null的数据条数。

sum求和：select sum（字段名） from 表名；
avg平均值：select avg（字段名） from 表名；
max最大值：select max（字段名） from 表名；
min最小值：select min（字段名） from 表名；

7.单行处理函数：

ifnull（可能为null的数据，被当作什么处理）；

例如：ifnull（sid，0）；如果sid为null，那么将其当作0来处理；

8.group by 和 having

group by：按照某个字段或者某些分段进行分组。

例子：select 分组函数 from 表名 group by 字段名；执行顺序：from-> group by -> select。

注意：

分组函数一般都是和group by联合使用，这也是为什么称之为分组函数的原因，并且任何一个分组函数（count，max，avg，

min，sum）都是在group by执行完成后才执行。

如果一个sql语句后面有group by则查询的字段字段只能有分组字段，和分组函数。

having：having是对分组之后的数据进行再次过滤。可以理解作为优先级低的where；

9.distinct去重记录:

select distinct 字段1，字段2 from 表；使得字段1和字段2联合起来去重。

注意：distinct必须出现在所有字段的最前面。不可：select 字段1，distinct 字段2 from 表；

count（diatinct 字段），先为字段去重，再统计数据条数；

10.总结完整DQL语句

select …from…where…group by…having…order by;

以及查询的顺序；

提示：

1.任何一条sql语句都以’’ ; ''结尾;

2.sql语句不区分大小写；

3.字符串用单引号括起来；

4.任何值与null进行运算结果都是null；

多表连接查询：

1.什么是连接查询：

在实际开发中大部分都不是从一张表中查询数据，一般都是多张表联合查询取得结果。

实际开发中，一般一个业务对应多张表。比如：学生和班级，起码两张。（因为一张表可能会出现冗余的情况，即数据大量重复！）

2连接查询的分类：

sql92（一些老的DBA还在使用的语法，已经被淘汰）
sql99（比较新的语法）
内连接：等值连接

非等值连接

自链接

外连接：左外连接（左连接）

右外连接（右连接）

*全连接（很少用，了解）

3.笛卡尔积现象：

在表的连接查询方面的一种现象：当两张表进行查询的的时候，没有任何条件限制，最终的查询结果条数是两张表的乘积。

SELECT e.ename,d.dname FROM emp e , dept d  ;   #会出现笛卡儿积现象（56条数据）（已经被淘汰的方式sql92写法）

关于表别名，使用表别名有什么好处吗？

执行效率高
可读性好

4.避免笛卡尔积现象

答：当然是加条件了。

SELECT e.ename,d.dname FROM emp e , dept d WHERE e.deptno = d.deptno ;

思考：避免了笛卡尔积会减少记录的匹配次数吗？

不会，还是匹配一样的次数，但是只显示有效条数。（并不能提高效率）

5.内连接

1、等值连接

语法:（inner）join …on…

inner可省略，带着只为可读性好一些。两张表内连接的意思 (常用)

SELECT e.ename,d.dname FROM emp e 	INNER(可以省略) JOIN  dept d  ON e.deptno = d.deptno WHERE 过滤条件;

sql99语法：使得表的连接条件和后来的过滤条件分离了！

2、非等值连接

SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

sql99语法：表连接条件并不是一个确切的值！

3、自连接

最大特点：一张表看作两张表，自己连自己

SELECT a.ename,b.mgr admin FROM emp a JOIN emp b WHERE a.mgr = b.empno ;

6.外连接

语法格式：left|right（outer）join … on …

outer可省略，带着只为可读性好一些。两张表外连接的意思

左外连接（左连接）：表示左边是主表。
右外连接（右连接）：表示右边是主表。

左连接有右连接的写法，同样右连接也有左连接的写法 :

SELECT d.* FROM dept d LEFT JOIN emp e ON e.`DEPTNO` = d.`DEPTNO` WHERE  e.`EMPNO` IS NULL

7内连接和外连接的区别：

内连接：假设有AB两张表，使用内连接，凡是A表B表能够匹配上的记录查询出来，这就是内连接，AB两张表并没有主次之分，两张表是平等的。
外连接：假设AB两张表，使用外连接，AB两张表中的一张是主表，一张是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据并没和主表中的数据匹配上，副表自动模拟出NULL与之相匹配（主表数据无条件查询）

8.三个表连接查询

SELECT e.ename,d.`DNAME`,s.grade FROM emp e 
JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` 
JOIN salgrade s ON e.`SAL` BETWEEN s.losal AND s.hisal

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

子查询：

子查询可以出现在：

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

1.where后面嵌套子查询

select * from emp where sal > (select avg(sal) from emp) ;

2.from后面嵌套子查询*

SELECT d.dname,AVG(s.grade) '部门平均薪资' 
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal 
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname

3.在select 后面嵌套子查询

写法一：看作临时表
SELECT t.ename,d.dname
FROM dept d JOIN (SELECT e.ename,e.deptno FROM emp e) t ON t.deptno = d.deptno   

写法二：连接查询
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

写法三：select后嵌套子查询
SELECT e.ename,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dept FROM emp e ;

4.union

可将查询结果集相加

SELECT ename,job FROM emp e WHERE job = 'CLERK'
UNION
SELECT ename,job FROM emp e WHERE job = 'SALESMAN'

而且两张不相干的表中数据也能够因此拼接在一起。（但是列数保持一致）

SELECT ename FROM emp 
UNION
SELECT dname FROM dept

5.*limit

1.limit是mysql中特有的，其他数据库中没有，但是Oracle中有一个相同的机制叫rownum。

2.limit取结果集中的部分数据

3.语法机制：limit startIndex，length

startindex表示起始位置，从0开始，0表示第一条数据。

length表示取几个。

4.limit是sql语句中最后一个执行的环节。

（当前页数-1）* 页的大小 = 当前页起始下标

三.DDL和DML

1.创建表：

char 和 varchar 如何选择？

char 适用于数长度不会发生改变的时候，是定长的，例如：性别，生日
varchar 当一个字段数据长度不确定，例如：简介、姓名、等都是采用varchar

char是直接开辟一定长度的空间，varchar是根据存储数据的长度动态的开辟空间！

#创建表
CREATE TABLE user(
   id  INT(10),
   username VARCHAR(30),
   PASSWORD VARCHAR(30)        #最后一行不需要,
);

2.删除表：

drop table 表名；（通用）

drop table 表名 if exists 表名；	//Oracle不支持这种写法。

使用drop删除表，就像是我们把表中的数据擦干净，但是表的结构还在，可以通过rollback回滚过来数据！

我们如果需要删除大表的话，就需要truncate 来删除，会清空所有数据且执行速度很快，并且永久删除，数据不可恢复！

truncate table name

删除表常用：

drop table if exist 表名（如果存在某个表删除该表）。

3.修改表结构：

用工具：哈哈！设计表选项

（因为在实际开发中表设计好以后，对表结构进行修改是很少的。并且修改表中结构的语句并不会写在java语句当中。）

4.insert：表中插入数据

语法：insert into 表名（字段1，字段2，字段3…）values(value1 , value2 , value3) ;

INSERT INTO dept(deptno,dname,loc)VALUES(50,'Java开发','ZHENGZHOU');

需要注意的地方：当一条insert语句执行成功之后，表格必然会多一行记录，及时多的一行记录当中某些字段为null，后期也没办法再通过insert插入数据了，只能通过update更新！

**一次插入多行数据：**insert into 表名（字段1，字段2，字段3）values（value1，value2，value3），（value1，value2，value3）

INSERT INTO dept(deptno,dname,loc)VALUES(70,'大数据开发','ZHENGZHOU'),(60,'运维部','KAIFENG');

5.表的复制

CREATE TABLE mytable AS SELECT * FROM dept      #将将查询结果当作表创建出来。

将查询出来的结果插入道一张表中

INSERT INTO mytable SELECT * FROM dept

6.update：修改表中数据

语法格式：update 表名 set 字段名1 = 值1 ，字段名2 = 值2 …where 条件；

注意：没有条件整张表的数据全部更新；

UPDATE dept SET dname = '测试开发' ,loc = 'HUEL' WHERE DEPTNO = 10 ;   #就1个set！

7.delete删除表中数据；

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

注意：没有条件表中数据全部清空！

DELETE FROM dept WHERE deptno = 70   #删除部门号为70的数据

DELETE FROM dept  #清空表中数据

8.增删改查有一个术语：CRUD操作

Create(增加)
Retrieve(查询)
Update(修改)
Delete(删除)

四.约束

非空约束（not null）：约束字段不能为NULL
唯一约束（unique）：约束字段不能重复
主键约束（primary key）：约束字段既不能为NULL也不能重复
外键约束（foreign key）：阿里巴巴手册，已经禁止使用外键了
检查f约束（check）：Oracle数据库有check约束，但是Mysql没有。

注意：我们主要还是记住英文，平时使用的都是英文！

1.非空约束 not null

CREATE TABLE mytable(
   id  INT(10),
   username VARCHAR(30) NOT NULL,    #添加非空约束
   PASSWORD VARCHAR(30)
);

insert into mytable(id,password) values(1,'123') ;  #由于我们的的用户字段要求非空，所以会执行失败
ERROR 1364(HY000) : Filed 'username' doesn't have a default value

2.唯一约束(unique)

列级约束

表级约束

3.主键约束(primary key) *

必须记住：一张表的主键约束只有一个；最好与别的字段不相关，仅仅代表唯一标识！

CREATE TABLE mytable(
   id  INT(10) PRIMARY KEY,       #添加主键约束【属于列级约束】
   username VARCHAR(30), 
   email VARCHAR(30)
);

【表级约束】

主键值自增：在primary key后面添加auto_increment（从1开始，递增）；

了解：Oracle也提供一个自增序列叫做序列（sequence）；

4.外键约束（foreign key）

外键约束:foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值。

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求:

删除数据的时候,先删除子表,再删除父表。
添加数据的时候,先添加父表，在添加子表。
创建表的时候,先创建父表,再创建子表。
删除表的时候,先删除子表,在删除父表。

create table t_class(
	cno int ,
    cname varchar(255) ,    #被引用：父表
    primary key(cno)
);

create table t_student(
	sno int ,
    sname varchar(255) ,   #子表
    classno int ,  
    foreign key(classno) reference t_class(cno)
);
#t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表

reference是引用；
外键可以为NULL；
此时classno中的数据来自cno；
外键引用的字段不一定是主键，但是起码是被unique约束；

五.存储引擎

存储引擎只存在mysql中，（Oracle中有对应机制，但是不叫存储引擎）。

完整的建表语句:

CREATE TABLE mytable(
   id  INT(10) PRIMARY KEY,
   username VARCHAR(30) NOT NULL, 
   PASSWORD VARCHAR(30)
)ENGINE = InnoDB Default CAHRSET=UTF8;

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

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

mysql默认的存储引擎是InnoDB的方式！默认采用的字符集是UFT-8

mysql> show engines \G   #查看当前，mysql版本支持的存储引擎命令! 当前MYSQL版本是5.7.33，支持9种存储引擎！

1.常见的存储引擎：

MyISAM 存储索引
InnoDB 存储索引
MEMORY 存储索引

2.MyISAM 存储引擎:

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

它管理的表具有以下特征:

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

格式文件 — 存储表的结构（mytable.frm）
数据文件 — 存储表的数据(mytable.MYD)
索引文件 — 存储表中索引的文件（mytable.MYI）

优点：可被转换为压缩，来节省空间，并且可以转换为只读表，提高检索效率！

缺点：不支持事务！

3.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数据库崩溃之后提供自动恢复机制！【事务、安全、重量级】
支持级联删除和级联更新。

4.MEMORY 存储引擎:

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

在数据库目录当中，每个表均已.frm格式的文件表示
表数据以及索引被存储在内存当中
表级锁机制
不能包含TEXT（CLOB）或BLOB字段
查询速度快

六.事务（Transaction）*

1、什么是事务

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

比如：银行转账，从A账户向B账务转账10000，需要执行两条update语句

update t_act set balance = balance - 10000 where actno = 'act-001' ; 
update t_act set balance = balance + 10000 where actno = 'act-002' ;

以上两条DML语句必须同时成功，或同时失败，不允许一条成功，一条失败！

想要保证以上的两条DML语句（update、insert、delete）同时成功或者同时失败，那么就需要使用数据库的“事务机制” ！

2、事务原理

假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete，这个事儿才算完成。

开启事务机制（开始)

执行insert语句–>insert.(这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存-条数据，不会真正的修改硬盘上的数据。)
执行update语句—> update…(这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据)
执行delete语句---->delete…(这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据)

提交事务或者回滚事务（结束）

提交事务：commit；语句

回滚事务：rollback；语句==（回滚永远都是只能回到上次事务的提交点！）==

MySQL默认情况下是支持自动提交事务的。（自动提交）：就是一条DML提交一次！

先执行如下命令：start transaction ,开启事务，也是将自动提交事务关闭！

在次命令后的指定的DML语句并不会持久化到数据库，等待commit | rollback 操作！

3、事务的特性：ACID

A：原子性（atomicity）：事务是最小的工作单元，不可再分。

C：一致性（consistency）：事务必须保证多条DML语句同时成功或同时失败。

I ：隔离性（isolation）：事务A与事务B之间具有隔离。

D：持久性（durability）：持久性说的时最终的数据必须持久化道硬盘文件中，事务才算成功的结束。

4、事务的隔离性

第一级别：读未提交（read uncommitted）：我们当前事务可以读取对方未提交的事务，

存在问题：存在脏读（Dirty read）现象，表示读到了脏的数据。

第二级别：读已提交（read committed）：我们当前事务可读取对方已提交的事务，解决了脏读现象，存在问题：不可重复读。
第三级别：可重复读（repeatable read）：解决不可重复读，永远读取到的都是开启事务时的数据，==存在问题：读取到的数据是幻像（幻读）==例如：执行一条查询的sql需要从1点3点，那么这条sql读取的一直是1点的数据，即使13点之间存在其他操作，该sql的结果并不会受到影响，类似快照，一直读取的是快照中的数据。
第四级别：序列化读/串行化读（serializable）：解决了所有问题。效率低，需要事务排队（类似于synchronized）。

Oracle数据库默认级别：第二级别；读已提交

Mysql数据库默认级别：第三级别，可重复读

5、验证各种隔离级别

通过命令 select @@tx_isolation；查看当前事物的隔离级别 !
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

读未提交（read uncommitted）：

#执行如下命令，设置全局的事务隔离级别为读未提交
set global transaction isolation level read uncommitted;

读已提交（read committed）：

#执行如下命令，设置全局的事务隔离级别为读已提交
set global transaction isolation level read committed;

可重复读（repeatable read）：

#执行如下命令，设置全局的事务隔离级别为可重复读
set global transaction isolation level read committed;

序列化读/串行化读（serializable）：

#执行如下命令，设置全局的事务隔离级别为可重复读
set global transaction isolation level serializable;

七.索引 *

1、索引的概述

作用：提高检索效率，缩小扫面范围。

注意：索引虽然提高效率但是依旧不能随意添加，因为索引也是数据库中的对象，也是需要数据库不断的维护的，有维护成本，表中数据经常修改就不适合，因为一旦修改索引需要重新排序，进行维护。

2、怎么创建索引对象

语法格式：create index 索引名称 on 表名（字段名）。

eg：create index emp_sal_index on emp(sal);

3、怎么删除索引对象

语法格式：drop index 索引名称 on 表名；

4、什么时候给字段添加索引

数据量庞大。（根据客户需求，根据线上的环境。）
该字段很少的DML操作。
该字段经常出现在where子句中。

5、sql语句的执行计划

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

==explain+sql语句可以解释该语句。（执行计划）==可以通过此命令判断sql是否走了索引！

6、索引底层采用的数据结构是：b+tree

7、索引分类

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

8、索引什么时候失效 ?

1、模糊查询，这个时候索引失效,因为以%开始，无法使用索引！

select ename from emp where ename ='%a%' ;

2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效！

select ename from emp where ename ='zhangsan' or job = 'Java' ;

3、使用复合索引，没有使用左侧的列去查找，索引失效

creat index emp_job_sal_index on emp(job,sal) # 为job和sal两个字段添加复合索引
select * from emp where job = 'java' ; #会走索引，因为使用job列去查了
select * from emp where sal = 1800 ; #不会走索引，因为没有使用左侧的job列！

4、where当中的索引列参加了运算，索引失效。

select * from emp where sal + 1 = 1800 ;

5、在where当中索引列使用了函数

select * from emp where lower(ename) = 'smith'  ;

八.视图（view）

1.视图的概念

站在不同的角度看到数据。（同一张表的数据，通过不同的角度去看待。）

2.创建/删除视图的sql

#创建视图
create view myview as select * from emp；

#删除视图
drop viem myview;

注意：只有DQL语句才能以视图对象的方式创建出来；

3.对视图进行CRUD操作

create table emp_bak as select * from emp;
create view myview as select empno,ename,sal from emp_bak ;
update myview set name = 'sqx' ,sql = 1 where empno = '7369' ; #通过视图修改原表数据
delete from myview where empno = 7369 ;  #通过视图删除原表数据

注意：视图对象时保存在硬盘上的，而非保存在内存当中，所以不会消失！

4.视图的作用

答：可以把复杂的SQL以视图的形式展示出来，我们需要执行复杂的SQL时，只需查询该SQL所映射的视图即可！并且这种方式很容易后期的维护，我们只需改动原SQL，重新创建视图即可！

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对你提供相关的视图java程序员只能对视图进行CRUD。（并不会提高视图的检索效率）

九.DBA命令（了解）

1.将数据库导出

#在windows的DOS窗口命令中输入(不需要进入mysql)

mysqldump 数据库名 > 导出路径  -u：mysql账号 -p：mysql密码

2.向数据库导入数据

create database 数据库名；

use数据库名；

source+sql脚本文件路径；

十.数据库设计三范式（重点）

1、三范式都是那些

第一范式:要求任何一张表必须有主键，每一个字段原子性不可再分。【核心】
第二范式:建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式:建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

声明：三范式是面试官经常问的，所以一定要熟记在心!

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

2、必备口诀

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

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

一对一两种方案：主键共享，外键唯一。

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

三十四道练习题

1、取得每个部门最高薪水的人员名称；

SELECT e.ename,t.maxsal FROM emp e 
JOIN (SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) t 
ON t.maxsal = e.sal AND t.deptno = e.deptno ;     #需要满足两个条件！

2、哪些人的薪水在部门的平均薪水之上；

SELECT e.ename,e.sal,t.* FROM emp e 
JOIN (SELECT deptno,AVG(sal)avgsal FROM emp GROUP BY deptno ) t 
ON e.deptno = t.deptno AND e.sal > t.avgsal ;  # 与第一题类似满足两个条件

3、取得部门中（所有人的）平均的薪水等级；

SELECT t.deptno,AVG(grade) 
FROM (
	SELECT e.sal,e.deptno,s.grade
	FROM emp e JOIN salgrade s 
	ON e.`SAL` BETWEEN s.losal AND s.hisal ) t 
GROUP BY deptno

4、不准用组函数（Max）取得最高薪水

SELECT sal maxsal FROM emp ORDER BY sal DESC LIMIT 1 ;

5、取得平均薪水最高的部门的部门编号

SELECT deptno,AVG(sal) FROM emp e GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

6、取得平均薪水最高的部门的部门名称

SELECT deptno,dname FROM dept
WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1) ;

7、求平均薪水等级最低的部门的部门名称

SELECT deptno,dname FROM dept
WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) LIMIT 1) ;

8、取得比普通员工（员工代码没在mgr字段出现的）的最高薪水还要高的领导人姓名；

注意（比“普通员工最高薪水”还要高的一定是领导）

SELECT e.ename FROM emp e
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE empno NOT IN
              (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL))            
#not in 使用的时候，后面的小括号记得排除null

9、取得薪水最高的前五名

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5 ;

10、取得薪水最高的第6名刀到第10名员工

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5 ;

11、最后入职的五名员工

SELECT ename,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5

12、取得每个薪水等级有多少个员工

SELECT COUNT(*),grade 
FROM (SELECT e.ename,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal ) t
GROUP BY grade

13.列出所有员工以及领导的名字

SELECT a.ename '员工' ,b.ename '领导' FROM emp a LEFT JOIN emp b ON a.mgr = b.empno

14、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

SELECT a.ename '员工' ,a.hiredate,b.ename '领导',b.hiredate,d.dname '部门名称'
FROM emp a 
JOIN emp b ON a.mgr = b.empno    
JOIN dept d ON d.deptno = a.deptno
WHERE a.hiredate < b.hiredate

15、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT d.dname '部门名称',e.*
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno ;

16、列出至少有五个员工的所有部门

SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*) >= 5 ;

17、列出薪金比 SMITH多的所有员工信息

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'smith')

18、列出所有CLERK(办事员)的姓名，部门名称，部门的人数。

注意：对两张临时表，进行连接查询！（目前最复杂）

SELECT t1.ename,t1.job,t1.dname,t2.deptcount
FROM 
(SELECT e.ename,e.job,d.dname,d.deptno FROM emp e JOIN dept d ON d.deptno = e.deptno WHERE job = 'clerk') t1
JOIN 
(SELECT deptno,COUNT(*)deptcount FROM emp GROUP BY deptno) t2
ON t1.deptno = t2.deptno ;

19、列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

SELECT job,COUNT(*) FROM emp GROUP BY job HAVING MIN(sal) > 1500

20、列出在部门sales（销售部）工作的员工的姓名，假定不知道销售部的部门编号

SELECT e.ename,d.dname 
FROM emp e 
JOIN (SELECT deptno,dname FROM dept WHERE dname = 'sales' ) d 
ON e.deptno = d.deptno

21、列出薪金高于公司平均薪金的所有员工，所在部门,上级领导，雇员的工资等级

SELECT e.ename,d.dname,a.ename,s.grade FROM emp e 
JOIN dept d ON d.deptno = e.deptno 
JOIN emp a ON e.mgr = a.empno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp)

22、列出与SCOTT从事相同工作的所有员工以及部门名称

SELECT e.ename,d.dname FROM emp e 
JOIN dept d ON d.deptno = e.deptno 
WHERE job = (SELECT job FROM emp WHERE ename='scott')

23、列出薪金等于30部门中员工的薪金的其他员工的姓名和工资

SELECT ename,sal FROM emp WHERE sal IN(SELECT distinct sal FROM emp WHERE deptno = 30 ) and depno <> 30

24、列出薪金高于部门30工作的所有员工的薪金的员工姓名，薪金，部门名称

SELECT e.ename,e.sal,d.dname FROM emp e
JOIN dept d ON d.deptno = e.deptno
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30) AND e.deptno <> 30

25、列出在每个部门工作的员工数量，平均工资和平均服务期限

SELECT d.*,COUNT(e.ename) '员工数量',IFNULL(AVG(e.sal) ,0) '平均工资',IFNULL(AVG(TIMESTAMPDIFF(YEAR,e.`HIREDATE`,NOW())),0) '平均服务期限'
FROM emp e 
RIGHT JOIN dept d ON d.deptno = e.deptno 
GROUP BY d.deptno,d.dname,d.loc

27、列出所有员工的姓名，部门名称和工资

SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON e.`DEPTNO` = d.`DEPTNO`

28、列出所有部门的详细信息和人数

SELECT d.*,COUNT(*) FROM dept d 
 LEFT JOIN emp e ON e.`DEPTNO` = d.`DEPTNO`
 GROUP BY d.deptno,d.dname,d.loc     #这里使用三个字段联合分组，因此select后可以使用d.*

29、列出各种工作的最低工资以及从事此工作的雇员姓名

SELECT e.ename,t.job,t.minsal FROM emp e 
JOIN (SELECT job,MIN(sal) minsal FROM emp GROUP BY job) t
ON e.job = t.job AND e.sal = t.minsal

30、列出各个部门的MANGER（领导）的最低薪资

#先找所有的领导，然后按部门分组
SELECT deptno,MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno

31、列出所有员工的年工资、按照年薪从低到高排序

SELECT ename,IFNULL(sal,0)*12 FROM emp ORDER BY sal

32、求出员工领导的薪水超过3000的员工名称与领导名称

SELECT a.ename '员工',b.ename '领导' FROM emp a JOIN emp b ON a.mgr = b.empno WHERE b.sal > 3000

33、求出部门名称当中，带’S’字符的部门员工的工资合计、部门人数

SELECT d.*,SUM(e.sal),COUNT(e.`ENAME`) FROM emp e
JOIN dept d ON e.deptno = d.deptno 
WHERE d.dname LIKE '%S%' 
GROUP BY d.`DEPTNO`,d.`DNAME`,d.`LOC`

34、给任职超过30年的员工加薪10%

UPDATE emp SET sal = sal * 1.1  WHERE TIMESTAMPDIFF(YEAR,`HIREDATE`,NOW()) > 30

MySQL笔记【基础篇】