MySQL笔记

MySQL

环境

安装

配置

编辑my.ini文件，编辑后重启服务

[mysqld]
#端口号
port = 3306
#mysql-5.7.27-winx64的路径
basedir="C:/Program Files/MySQL/MySQL Server 5.7/
#mysql-5.7.27-winx64的路径+\data
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
#最大连接数
max_connections=200
#编码
character-set-server=utf8

default-storage-engine=INNODB

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql]
#编码
default-character-set=utf8 

卸载

应用卸载后，删除安装目录和ProgramData下的目录

服务启动与停止

net start 服务名称
net stop  服务名称

控制台登录

- mysql -u用户名 -p密码
- mysql -h连接目标的ip地址 -u用户名 -p密码

DQL

数据查询语言，select…

简单查询

一个字段

select 字段名 from 表名

多个字段

select 字段名1，字段名2 from 表名

全部字段

select * from 表名
#缺点：
#效率低，需要先将*转换为字段名
#可读性差

起别名

select 字段名 as xxx from 表名
select 字段名 xxx from 表名
#如果起的别名中间有空格或中文可以用引号引起来，推荐单引号

数学运算

查询中字段可以直接参与数学运算

select sal*12 yearsql from emp

条件查询

select
	字段
from
	表名
where
	条件

比较

= 等于
<>或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between … and …. 两个值之间, 等同于 >= and <=

判空

is null 不为空
is not null 为空

连词

and 并且
or  或
#同时使用时and的优先级比or高，可以通过加括号进行调整

包含

in 相当于多个or
select xxx from xxx where xxx in(xxx,xxx)
#括号不是区间
not in 不在这几个值当中

模糊查询

like
%表示任意多个字符
_表示任意一个字符
#找出名字有_的，用 \ 转义

排序查询

单个字段

order by 字段
#默认升序
order by 字段 desc;
#降序排序
order by 字段 asc;
#升序排序

多个字段

order by 字段 desc, 字段 asc;
#只有前面相等时，才会启用后面字段

根据位置

select ename,sal from emp order by 2; // 2表示第二列。第二列是sal
#按照查询结果的第2列sal排序。

单行处理函数

一个输入对应一个输出

转小写

select lower(name) from emp;

转大写

select upper(name) from emp;

取子串

select substr(ename,1,1) from emp;
substr(被截取的字符串，起始下标，截取的长度)
起始下标是从1开始，没有0

字符串拼接

select concat(eid,ename) from emp;

取长度

select length(ename) from emp;

去空格

select * from emp where ename = trim("   admin  ")

将字符串转化为日期

将字符串 varchar 类型转换为 date 类型

insert into t_user(
	id,
	name,
	brith)
values(
	1,
	'张三',
	str_to_date('01-10-1990','%d-%m-%Y'));

# 如果日期字符串刚好为 %Y-%m-%d ，则该函数就可以省略不写

mysql 日期格式：

• % Y 年
• % m 月
• % d 日
• % h 时
• % i 分
• % s 秒

格式化日期

将 date 类型转换为具体有一定格式的 varchar 类型

select
	id,
	name,
	date_format(brith,'%d-%m-%Y')
from
	t_user
# 不指定的话，采用默认的日期格式化进行输出

设置千分位

select
	ename,format(sal,'$999.999')
from
	emp;
# 加入千分位

四舍五入

select round(1236.567,0) from emp;
1237
0是保留0位小数
-1是保存到十位

生成随机数

select rand() from emp;

null转换

#空处理函数,只要有null参与的数学运算，结果就是null
select ifnull(sal,0) from emp;
#如果sal为null，当0处理

case,when,then

case when then when then end

select
	ename,
	job,
	(case job when 'MANAGER' then sal*1.1 when 'SALEMAN' sal*1.5 else sal end) as newsal
from
	emp;
#当员工工作岗位是MANAGER时，工资上调10%，当工作岗位是SALEMAN，工资上涨50%，其他不变

多行处理函数

多个输入对应一个输出，分组函数

• 分组函数使用前，必须先分组才能进行使用，如果没有进行分组，则整张表默认为一组

• 分组函数不能直接使用在where子句中，例

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

• 因为分组函数在使用的时候必须先分组才能使用，where执行的时候还没有分组，所以where后不能出现分

计数

select count(ename) from emp;

求和

select sum(sal) from emp;

平均值

select avg(sal) from emp;

最大值

select max(sal) from emp;

最小值

select min(sal) from emp;

分组查询

使用 group by 进行分组

分完组后可以使用having继续进行过滤，必须和group by 用，但是优先用 where，实在完成不了用having

• where先筛选后分组
• havng先分组后筛选

sql执行顺序

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

select语句后只能跟group by的分组字段和分组函数

查询去重

只能出现在所有字段的前方

select distinct job,... from emp; 

连接查询

按年代分

SQL92

92年的sql语法

SQL99

92年的sql语法

按表连接分

内连接

• 等值连接

# sql 92
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

• 非等值连接

select
	e.ename,e.sal,s.grade
from
	emp e
(inner) join
	salgrade s
on
	e.sal between s.losal and s.hisal;

• 自连接

# 一张表看成两张表
select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno

外连接

在外连接中，产生了主次关系

• 左外连接(左连接)

select
	e.ename,d.dname
from
	emp e
left (outer) join  
	dept d
on
	e.deptno = d.deptno
# left 代表将join关键字左边的表看成是主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

• 右外连接(右连接)

select
	e.ename,d.dname
from
	emp e
right (outer) join  
	dept d
on
	e.deptno = d.deptno

全连接

子查询

select 语句中嵌套 select 语句，可以出现在，select、from、where 后面

where

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

from

from 后面的子查询，可以将子查询的查询结果当作一张临时表

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

select

select
	e.ename,(select
            	d.dname
            from
            	dept d
            where
            	e.deptno = d.deptno) as dname
from
	emp e;
# 子查询只能一次返回一条结果

合并查询结果集

select
	ename,job
from
	emp
where
	job = 'MNAAGER'
union
select
	ename,job
from
	emp
where
	job = 'SALEMAN'
#union的效率更高一些，对于表连接来说，每连接一次新表，匹配是笛卡尔积，成倍的翻
#union可以减少匹配的次数，比如a和b连接10*10，a和c连接10*10，总次数就为200，在减少匹配次数的情况下，还可以完成两个结果集的拼接
#合并时要求列与列的数据类型一致并且数目相等

分页查询

select
	ename,sal
from
	emp
order by
	sal desc
limit 
	0,5;
#只跟一个数字就是前几
#两个数字第一个是起始下标，从0开始，第二个是长度
#limit在order by之后执行
limit (pageNo-1)*PageSize ,PageSize

DML

数据操作语言，insert,delete,update…

插入数据

insert into 表名(
	字段名1，
	字段名2，
	字段名3
	...)
values(
	值1，
	值2，
	值3，
	...),
	...;

• 字段名和值要一一对应，数量要对应，数据类型也要对应
• 添加全部数据时可以省略字段

删除数据

delete from 表名
where 条件;

没有条件会删除整张表的数据

delete语句删除数据是表中的数据删除了，但是存储硬盘的真实存储空间不会被释放，可以回滚恢复数据，但是效率比较低下

# 删除整张表数据，删除效率高，不支持回滚
truncate table 表名;

修改数据

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

如果不写条件则默认修改所有

DDL

数据定义语言，操作表的结构,create,drop,alter…

建表

create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	字段名3 数据类型
);
# 建议表名以t_或tbl_开始
# 建表时可以在数据类型后使用 default 使用默认值
# 建表时可以在数据类型后使用 auto_increment 从1开始自增

快速创建表

create table  新表名 as select 字段1，字段2，... from 表名;
# 将表的查询结果当作一张表新建
create table 表名 like 被复制的表名;

删表

drop table 表名;
# 存在时进行删除
drop table if exists 表名;

truncate table 表名;
# 先删除表，再创建一个一模一样的空表

改变表

• 修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

• 修改表的字符集

alter table 表名 character set 字符集名称;

• 添加一列

alter table 表名 add 列名 数据类型;

• 修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型;

• 修改数据类型

alter table 表名 modify 列名 新数据类型;

• 修改表名

alter table 表名 rename to 新的表名;

• 删除列

alter table 表名 drop 列;

数据类型

• varchar：可变长度的字符串，需要动态分配空间，速度慢，最长255
• char：定长字符串，使用不恰当可能会导致空间浪费，最长255
• int：整数型，等同于Java int，最长11，默认11
• bigint：长整型，等同于Java long
• float：单精度浮点型数据
• double：双精度浮点型数据
• date：短日期类型，只包括年月日信息，默认格式 %Y-%m-%d
• datetime：长日期类型，包括年月日时分秒信息，默认格式 %Y-%m-%d %h:%i:%s
• clob：字符大对象，最多可以存储4G的字符串 Character Large Object
• blob：二进制大对象，存储图片、声音、视频等流媒体数据 Binary Large Object

TCL

事务控制语言，commit,rollback…

概念

• 一个事物其实就是一个完整的业务逻辑，只有 DML 语句才有事务

• 在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。

使用

• 开启事务：start transaction

• 提交事务

  • 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中
  • 提交事务标志着，事务的结束。并且是一种全部成功的结束。
  • commit

• 回滚事务

  • 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
  • 回滚事务标志着，事务的结束。并且是一种全部失败的结束
  • rollback

特性：

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据
• 隔离性：多个事物之间相互独立
• 一致性：事务操作前后数据总量不变

隔离级别

• read uncommited：读未提交
  • 事务A可以读取到事务B未提交的数据
  • 产生的问题：脏读，不可重复读，幻读
• read commited：读已提交(Oracle默认)
  • 事务A只能读取到事务B提交后的数据，在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4，称为不可重复读取。
  • 产生的问题：不可重复读
• repeatable read：可重复读(MySQL默认)
  • 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
  • 产生的问题：幻读
• serializable：串行化
  • 事务同步，不能同时并发多个事务
  • 可以解决所有的问题

隔离级别从小到大安全性越来越高，但是效率越来越低

# 查看隔离级别
select @@tx_isolation;

# 设置隔离级别
set global transaction isolation level 级别字符串;

DCL

数据控制语言，grant,revoke…

• 添加用户

create user '用户名'@'主机名' identified by '密码' ;
# 主机名写通配符`%`则表示任意主机均可登录

• 删除用户

drop user '用户名'@'主机名';

• 修改用户密码

update user set password = password('新密码') where user = '用户名';
set password for '用户名'@'主机名' = password('新密码');#需要执行刷新权限
flush privileges;

• 查询权限

show grants for '用户名'@'主机名';

• 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 授予全部权限
grant all on *.* to '用户名'@'主机名';

• 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
# 撤销所有权限
revoke all on *.* to '用户名'@'主机名';

权限	权限级别	权限说明
create	数据库、表或索引	创建数据库、表或索引权限
drop	数据库或表	删除数据库或表权限
grant option	数据库、表或保存的程序	赋予权限选项
references	数据库或表	外键权限
alter	表	更改表，比如添加字段、索引、修改字段等
delete	表	删除数据权限
index	表	索引权限
insert	表	插入权限
select	表	查询权限
update	表	更新权限
create view	视图	创建视图权限
show view	视图	查看视图权限
alter routine	存储过程	更改存储过程权限
create routine	存储过程	创建存储过程权限
execute	存储过程	执行存储过程权限
file	服务器主机上的文件访问	文件访问权限
create temporary tables	服务器管理	创建临时表权限
lock tables	服务器管理	锁表权限
create user	服务器管理	创建用户权限
proccess	服务器管理	查看进程权限
reload	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
replication client	服务器管理	复制权限
replication slave	服务器管理	复制权限
show databases	服务器管理	查看数据库权限
shutdown	服务器管理	关闭数据库权限
super	服务器管理	执行kill线程权限

其他SQL

表结构

desc 表名

导入

source xxx.sql

导出

# 无需登录mysql
mysqldump 数据库名称>路径xxx.sql  -uxxx -pxxx

版本号

select version()

当前数据库

select database()

获取当前系统时间

now()
# 获取的日期格式是 datetime 格式的

查看建表语句

show create table 表名;

约束

非空约束

not null

唯一性约束

unique

两个字段联合唯一性

加在表后面叫表级约束，加在列后面叫列级约束

unique(字段1，字段2)

主键约束

primary key

在mysql中，如果一个字段被unique和not null同时约束的话，该字段变成主键字段

一张表主键约束只能添加一个

主键值建议使用 int，bigint，char，不建议varchar，主键值一般都是数字定长的

# 复合主键
primary key(字段1，字段2)

# 不建议使用

外键约束

foreign key

foreign key(添加的字段) references 引用的外键表(引用的字段)

约束引用的字段不一定是主键，可以为空，但一定具有唯一性

存储引擎

表的存储方式

# 建表默认 engine=InnoDB

查看支持的存储引擎

show engines \G

常用引擎

MyISAM

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

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

  • 格式文件 — 存储表结构的定义（mytable.frm）
  • 数据文件 — 存储表行的内容（mytable.MYD）
  • 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。可被转换为压缩、只读表来节省空间(对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。)

• MyISAM存储引擎特点：

  • 可被转换为压缩、只读表来节省空间
  • 这是这种存储引擎的优势！！！！
  • MyISAM不支持事务机制，安全性低。

InnoDB

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。

• 它管理的表具有下列主要特征：

  • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
  • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
  • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
  • 多版本（MVCC）和行级锁定
  • 支持外键及引用的完整性，包括级联删除和更新

• InnoDB存储引擎特点:

  • 支持事务：
  • 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
  • 不能很好的节省存储空间。

MEMORY

以前被称为HEAP 引擎，使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定

• MEMORY 存储引擎管理的表具有下列特征：

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

• MEMORY 存储引擎特点:

  • 查询效率是最高的。不需要和硬盘交互。
  • 不安全，关机之后数据消失。因为数据和索引都是在内存当中。

索引

概念

• 索引是在数据库的字段上添加，是提高查询效率存在的一种机制
• 一张表的一个字段可以添加一个索引，也可以多个字段联合添加索引
• 索引相当于一本书的目录，是为了缩小范围所存在的一种机制
• 主键默认添加索引，在MySQL中，unique字段也默认添加索引，如果一个字段没有添加索引则默认进行全扫描

原理

• 索引也是需要排序的，底层排序和TreeSet数据结构相同，它是一个自平衡的二叉树，遵循左小右大原则存放，采用中序遍历
• 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在
  • 在MyISAM存储引擎中，索引存储在一个.MYI文件中。
  • 在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中。
  • 在MEMORY存储引擎当中索引，被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

索引的实现原理就是缩小扫描的范围，避免全表扫描

使用

• 数据量庞大
• 该字段总是出现在where后面，也就是总是被扫描
• 该字段很少出现DML操作，因为在DML之后，索引需要重新排序
• 索引也是需要维护的

创建索引

create index emp ename_index on emp(ename);
# 给emp表的ename字段添加索引，起名ename_index

删除索引

drop index ename_index on emp;
# 将emp表上的ename_index索引删除 

查看索引

explain select * from emp where ename = 'KING';
# 根据type 和 rows查看扫描了多少次

索引失效

explain select * from emp where ename like '%T';

ename不会走索引，原因是因为模糊匹配当中以“%”开头了！尽量避免模糊查询的时候以“%”开始。这是一种优化的手段/策略。

explain select * from emp where ename = 'KING' or job = 'MANAGER';

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。可以使用union来代替

create index emp_job_sal_index on emp(job,sal);
explain select * from emp where job = 'MANAGER';# 生效
explain select * from emp where sal = 800;# 失效

使用复合索引的时候，没有使用左侧的列查找，索引失效。什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

create index emp_sal_index on emp(sal);
explain select * from emp where sal+1 = 800;

在where当中索引列参加了运算，索引失效。

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

在where当中索引列使用了函数，索引失效。

视图

概念

• 可以面向视图对象进行增删改查，对视图对象的增删改查会导致原表被操作

• 视图可以简化 sql 语句

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？

• 可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

使用

创建视图对象

create view dept2_view as select * from dept2(必须实施DQL语句);

删除视图

drop view dept2_view

三大范式

• 第一范式
  • 任何一张表必须有主键，每一个字段原子性且不可再分
• 第二范式
  • 建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
• 第三范式
  • 建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
• 按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

第一范式

必须有主键，并且每一个字段都是原子性不可再分。

第二范式

建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

函数依赖

A->B,如果通过A属性(属性组：多个属性确定一个别的属性)的值，可以确定唯一B属性的值，则称B依赖于A

• 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定依赖于A属性组的所有属性值(学号，课程名称–>分数)
• 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组的某一些值即可(学号，课程名称–>姓名)
• 传递函数依赖：A–>B，B–>C，如果通过A属性(属性组)的值，可以确定唯一B属性的值，再通过B属性(属性组)的值可以唯一确定C属性的值，则称为传递函数依赖于A(学号–>系名，系名–>系主任)
• 多对多三张表，关系表两个外键
• 一对多两张表，多的表加外键
• 一对一，外键唯一

码，主属性，非主属性

• 码：如果在一张表中，一个属性或属性组被其他所有属性所完全依赖，则成为这个属性(属性组)为该表的码
• 主属性：码属性组中的所有属性
• 非主属性：除过码属性组的属性

第三范式

在第二范式的基础上，任何非主属性不依赖其他非主属性(在2NF基础上消除传递依赖)

数据库设计三范式是理论上的,实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

例题

数据准备

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;

CREATE TABLE SC 
( 
 SNO VARCHAR(200), 
 CNO VARCHAR(200), 
 SCGRADE VARCHAR(200) 
); 
CREATE TABLE S 
( 
 SNO VARCHAR(200 ), 
 SNAME VARCHAR(200) 
); 
CREATE TABLE C 
( 
 CNO VARCHAR(200), 
 CNAME VARCHAR(200), 
 CTEACHER VARCHAR(200) 
); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
COMMIT; 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4'); 
COMMIT; 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit; 

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

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

SELECT 
	e.ename,t.*
FROM
	emp e
JOIN
	(SELECT
		deptno,MAX(sal) AS maxsal
	FROM
		emp
	GROUP BY
		deptno) AS t
ON
	e.deptno = t.deptno AND e.sal = t.maxsal

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

SELECT 
	e.ename,e.sal
FROM
	emp e
JOIN
	(SELECT 
		AVG(sal) AS avsal,deptno
	FROM 
		emp
	GROUP BY
		deptno) AS t
WHERE
	e.deptno = t.deptno AND e.sal > t.avsal

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

SELECT
	e.deptno,AVG(s.grade) AS 'avg<grade>'
FROM
	emp e
LEFT JOIN
	salgrade s
ON
	e.sal BETWEEN s.losal AND s.hisal
GROUP BY
	deptno

不准用组函数（Max），取得最高薪水（给出三种解决方案）

SELECT
	MAX(sal)
FROM
	emp
================	
SELECT
	sal
FROM
	emp
ORDER BY
	sal DESC
LIMIT 0,1
================
SELECT
	sal
FROM
	emp
WHERE
	sal
NOT IN
	(SELECT 
		DISTINCT a.sal 
	FROM 
		emp a 
	JOIN 
		emp b 
	ON 
		a.sal < b.sal)

取得平均薪水最高的部门的部门编号（给出两种解决方案）

SELECT
	deptno,AVG(sal) AS avsal
FROM
	emp
GROUP BY
	deptno
ORDER BY
	avsal DESC
LIMIT
	0,1
============================
SELECT
	deptno,AVG(sal) AS avsal
FROM
	emp 
GROUP BY
	deptno
HAVING
	avsal = (
		SELECT
			MAX(t.avsal)
		FROM
			(SELECT
				deptno,AVG(sal) AS avsal
			FROM
				emp
			GROUP BY
				deptno) AS t)

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

SELECT
	d.dname
FROM
	dept d,(SELECT
			deptno,AVG(sal) AS avsal
		FROM
			emp
		GROUP BY
			deptno
		ORDER BY
			avsal DESC
		LIMIT
			0,1) AS  t
WHERE
	d.deptno  = t.deptnoSELECT
	d.dname
FROM
	dept d,(SELECT
			deptno,AVG(sal) AS avsal
		FROM
			emp
		GROUP BY
			deptno
		ORDER BY
			avsal DESC
		LIMIT
			0,1) AS  t
WHERE
	d.deptno  = t.deptno

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

SELECT 
	d.dname
FROM
	dept d,(SELECT
			AVG(sal) AS avsal,deptno
		FROM
			emp
		GROUP BY
			deptno
		ORDER BY
			avsal
		LIMIT 0,1) AS t
WHERE
	d.deptno = t.deptno

取得比普通员工(员工代码没有在mgr 字段上出现的)的最高薪水还要高的领导人姓名

SELECT
	ename,sal
FROM
	emp
WHERE
	sal > (SELECT 
			MAX(sal)
		FROM
			emp
		WHERE
			empno NOT IN (SELECT
						DISTINCT mgr
					FROM
						emp
					WHERE
						mgr IS NOT NULL))

取得薪水最高的前五名员工

select 
	ename,sal
from
	emp
order by
	sal desc
limit
	0,5

取得薪水最高的第六到第十名员工

select 
	ename,sal
from
	emp
order by
	sal desc
limit
	5,5

取得最后入职的5 名员工

SELECT 
	ename,hiredate
FROM
	emp
ORDER BY
	hiredate DESC
LIMIT
	0,5

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

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

列出所有员工及领导的姓名

SELECT
	e.ename AS '员工',IFNULL(t.ename,'没有上级') AS '领导'
FROM
	emp e
LEFT JOIN
	emp t
ON
	e.mgr = t.empn

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

SELECT
	n.empno,n.ename,d.dname
FROM
	dept d,(SELECT
			e.empno,e.ename,e.deptno
		FROM
			emp e
		LEFT JOIN
			emp t
		ON
			e.mgr = t.empno 
		WHERE
			e.hiredate <  t.hiredate) AS n
WHERE
	n.deptno =  d.deptno

列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

select
	d.dname,e.*
from
	dept d
left join
	emp e
on
	d.deptno = e.deptno

列出至少有5 个员工的所有部门

select
	d.dname,count(*) as total
from
	emp e
left join
	dept d
on
	d.deptno = e.deptno
group by
	d.dname
having total >= 5

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

SELECT
	e.*
FROM
	emp e
WHERE
	e.sal > (SELECT
			sal
		FROM
			emp	
		WHERE
			ename = 'SMITH')

列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

SELECT
	e.ename,d.dname,e.cc
FROM
	dept d,(SELECT
			e1.ename,e1.deptno,e1.job,e2.cc
		FROM
			emp e1

		LEFT JOIN
			(SELECT
				deptno,COUNT(*) AS cc
			 FROM
				emp 
			GROUP BY
				deptno) AS e2
		ON
			e1.deptno = e2.deptno
		HAVING
			e1.job = 'CLERK') AS e
WHERE
	d.deptno = e.deptno

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

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

列出在部门"SALES"<销售部>工作的员工的姓名

SELECT
	ename
FROM
	emp
WHERE
	deptno = (SELECT
			deptno
		  FROM
			dept
		  WHERE
			dname = 'SALES')

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

SELECT
	e.ename '姓名',d.dname '部门名称',t.ename '上级领导',s.grade '工资等级'
FROM
	emp e
LEFT JOIN
	dept d
ON
	d.deptno = e.deptno

LEFT JOIN
	emp t
ON 
	e.mgr = t.empno
LEFT JOIN
	salgrade s
ON
	e.sal BETWEEN s.losal AND s.hisal
WHERE	
	e.sal > (SELECT 
			AVG(sal)
		 FROM
			emp)

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

SELECT
	e.ename,d.dname
FROM
	emp e

LEFT JOIN
	dept d
ON
	e.deptno = d.deptno
WHERE
	job = (SELECT
			job
		FROM
			emp
		WHERE
			ename = 'SCOTT')
	AND e.ename <> 'SCOTT'

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

SELECT
	ename,sal
FROM
	emp
WHERE
	sal IN  (SELECT 
			DISTINCT sal
		FROM
			emp
		WHERE
			deptno = 30)
	AND deptno <> 30

列出薪金高于在部门30 工作的所有员工的薪金的员工姓名和薪金.部门名称

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

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

SELECT
	d.dname,e.total,e.avsal,e.years
FROM
	dept d
RIGHT JOIN
	(SELECT 
		AVG(sal) avsal,deptno,COUNT(*) total,AVG(TIMESTAMPDIFF(YEAR, hiredate, NOW())) years
	 FROM
		emp
	 GROUP BY
		deptno) AS e
ON
	e.deptno = d.deptno
mysql当中计算两个日期的“年差”，差了多少年？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年

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

SELECT
	e.ename,d.dname,e.sal
FROM
	emp e
LEFT JOIN
	dept d
ON
	e.deptno = d.deptno

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

SELECT
	d.*,t.total
FROM
	dept d
LEFT JOIN
	(SELECT 
		deptno,COUNT(*) AS total
	 FROM
		emp
	 GROUP BY
		deptno) AS t
ON
	d.deptno = t.deptno

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

select
	e.*
from
	emp e
join
	(select
		job,min(sal) minsal
	from
		emp
	group by
		job) as t
on
	e.job = t.job and e.sal = t.minsal

列出各个部门的MANAGER(领导)的最低薪金

SELECT
	deptno,MIN(sal)
FROM
	emp
WHERE
	job = 'MANAGER'
GROUP BY
	deptno

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

select
	ename,sal * 12
from
	emp
order by
	sal

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

SELECT 
	e.ename,t.ename
FROM
	emp e
JOIN
	(SELECT
		empno,ename
	 FROM
		emp
	 WHERE
		sal > 3000) AS t
ON
	e.mgr = t.empno

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

SELECT
	d.dname,t.sumsal,t.total
FROM
	dept d
LEFT JOIN
	(SELECT
		deptno,SUM(sal) sumsal,COUNT(*) total
	 FROM
		emp
	 GROUP BY
		deptno) AS t
ON 
	d.deptno = t.deptno
WHERE
	d.dname LIKE '%S%'

给任职日期超过30 年的员工加薪10%

update
	emp
set
	sal = sal * 1.1
where
	timestampdiff(YEAR, hiredate, now()) > 30;

找出没选过“黎明”老师的所有学生姓名

SELECT
	sname
FROM
	s
WHERE 
	sno NOT IN (SELECT
	sno
FROM
	sc
WHERE
	cno = ( SELECT
		        cno
		FROM
		        c
	        WHERE
		        cteacher = '黎明'))

列出 2 门以上（含2 门）不及格学生姓名及平均成绩

SELECT
	s.sname,t.avggrade
FROM
	s s,(SELECT
			sc1.sno,COUNT(*) failtotal,sc2.avggrade
	     FROM
			sc sc1
	     JOIN
			(SELECT
				sno,AVG(scgrade) avggrade
			FROM
				sc
			GROUP BY
				sno) AS sc2
	     ON
			sc1.sno = sc2.sno
	     WHERE
			sc1.scgrade < 60
	     GROUP BY
			sno) AS t
WHERE
	s.sno = t.sno AND failtotal > 2

既学过 1 号课程又学过 2 号课所有学生的姓名

select
	sname
from
	s
where
	sno in (select
			distinct t.sno
		from
			(select
				sc1.sno,sc1.cno cno1,sc2.cno cno2
			 from
				sc sc1
			 join
				sc sc2
			 on
				sc1.sno = sc2.sno) as t
		where
			(t.cno1 = 1 and t.cno2 = 2) or (t.cno1 = 2 AND t.cno2 = 1))