1. 数据库及表操作

注意：如果需要本文的数据库文件，请下载，否则自己建表练习
sql文件

1.1. 创建、删除、查看数据库

1.1.1. 查看mysql服务器中所有数据库

SHOW DATABASES;
show databases;
show datebases; -- 错误写法

书写规范: SQL语句中的关键字在书写时应该大写;自定义的库名、表(别)名、列(别)名 应该小写。
SELECT * FROM stu WHERE id>2; -- 规范写法
select * from stu where id>2; -- 不规范写法,但也可以执行!
几乎所有的SQL语句在书写完成后,都需要在后面添加分号,表示语句到此结束!

1.1.2. 进入某一数据库（进入数据库后，才能操作库中的表和表记录）

-- 语法：USE 库名;
use mysql;
use test;

-- 查看已进入的库(了解)
select database();

1.1.3. 查看当前数据库中的所有表

– 先进入某一个库,再查看当前库中的所有表
use mysql;
show tables;

1.1.4. 删除mydb1库

– 语法：DROP DATABASE 库名;
drop database test;
drop database mydb1; – 删除不存在的库,会报错!

– 思考：当删除的库不存在时，如何避免错误产生?
drop database if exists mydb1; – 如果存在则删除mydb1

1.1.5. 重新创建mydb1库，指定编码为utf8

– 语法：CREATE DATABASE 库名 CHARSET 编码;
create database mydb1 charset utf8;
# 需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；

– 如果不存在则创建mydb1;
create database if not exists mydb1 charset utf8; – 如果不存在则创建

1.1.6. 查看建库时的语句(了解)（并验证数据库库使用的编码）

– 语法：SHOW CREATE DATABASE 库名;
show create database mydb1;

---

1.2. 创建、删除、查看表

---

1.2.1. 进入mydb1库，删除stu学生表(如果存在)

– 语法：DROP TABLE 表名;
use mydb1;
drop table if exists stu;

1.2.2. 创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：

CREATE TABLE 表名(
	列名 数据类型,
	列名 数据类型,
	...
  列名 数据类型
);
创建stu表的SQL语句如下:
use mydb1;
drop table if exists stu;
create table stu(
	id int primary key auto_increment,
	name varchar(50),
	gender varchar(10),
	birthday date,
	score double
);
insert into stu(id,name,gender) value(null,'test1','male');

1.2.3. 查看stu学生表结构

– 语法：desc 表名
desc stu;

---

2. 新增、修改、删除表记录

---

2.1. 往学生表(stu)中插入记录(数据)

-- 语法：INSERT INTO 表名(列名1,列名2,列名3...) VALUES(值1,值2,值3...);
-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
set names gbk;
insert into stu(id,name,gender,birthday,score) 
						value(1,'tom','male','1995-2-1',90);
insert into stu value(2,'王海涛','male','2000-3-4',80);
insert into stu value(3,'小乔','female','2001-5-4',75);
select * from stu;

插入记录时需要注意:
	1) 插入值的个数和顺序必须要和列的个数和顺序保持一致!
	2) SQL语句中的字符串和日期类型的值用单引号引起来(即使有些数据库版本支持双引号,也建议使用单引号)
	3) 如果是要给所有的列都赋值,而且值的顺序和个数,和声明时列的顺序和个数完全一致，列名可以省略！
	
常见问题1: 在往stu表中插入中文数据出现乱码解决方法
	1)首先检查当前表(stu)所在的库(mydb1)创建时有没有指定编码
		create database mydb1 charset utf8;
		如果没有指定,将库删除再重建,创建时按照上面的语法指定编码
	2)如果建库时指定了编码,那么在插入数据之前,有没有设置 set names gbk;
		如果没有设置,先设置编码,再插入数据。
	3)如果前面确认了没有问题,可以尝试设置 set names utf8;再插入数据。
	4)如果上面都确认了没有问题,最后可以尝试,在cmd窗口顶部,右键"属性",在弹出的窗口中点击"选项",在底部,勾选"使用旧版控制台",确定

常见问题2: 关于查询stu表中的数据没有对齐的解决方法
	1)关闭之前的cmd窗口,新开一个cmd窗口,连接上mysql服务器
	2)设置set names GBK;编码之后再查询,如果还是没有对齐看一步
	3)再新开一个cmd窗口,使用 mysql --default-character-set=gbk -uroot -proot 连接mysql服务器,连接后不用再 set names gbk; 直接查询数据, 看数据是否对齐。

2.2. 查询stu表所有学生的信息

– 语法：SELECT 列名 | * FROM 表名
select * from stu;

2.3.修改stu表中所有学生的成绩，加10分特长分

– 修改语法: UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句];
update stu set score=score+10;

2.4.修改stu表中编号为1的学生成绩，将成绩改为83分。

update stu set score=83 where id=1;
提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。

2.5.删除stu表中所有的记录

– 删除记录语法: DELETE FROM 表名 [where子句]
delete from stu;
– 仅删除符合条件的
delete from stu where id=1;

---

3. 查询表记录

3.1. 基础查询

– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!

---

3.1.1.查询emp表中的所有员工，显示姓名，薪资，奖金

select * from emp; -- 查询所有列
select name,sal,bonus from emp; -- 查询指定列

*(星号)是通配符,在select后面表示查询所有的列

3.1.2.查询emp表中的所有部门和职位

select dept,job from emp;
-- 上面的查询结果中有大量重复记录,可以通过distinct剔除重复记录
select distinct dept,job from emp;

3.2.WHERE子句查询

3.2.1. 查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

3.2.2. 查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资

select name,sal+bonus from emp; -- 查询所有员工的薪资加奖金
select name,sal+bonus from emp where sal+bonus > 3500; -- 有误差

– ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
select name,sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0)>3500;

– 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 “总薪资”
select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
where sal+ifnull(bonus,0)>3500;

– 使用as可以为表头指定别名(另外as可以省略)
select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp
where sal+ifnull(bonus,0)>3500;

3.2.3. 查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资

-- 不包含3000和4500
select name,sal from emp where sal>3000 and sal<4500;
-- 包含3000和4500
select name,sal from emp where sal>=3000 and sal<=4500;
-- 如果包含3000和4500,也可以使用 between..and...
select name,sal from emp where sal between 3000 and 4500;

3.2.4. 查询emp表中【薪资为 1400、1600、1800】的员工，显示员工姓名和薪资

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或者使用in: in前面的列的值只要等于in后面括号中的任何一个值,就算满足条件!
select name,sal from emp where sal in(1400,1600,1800);

3.2.5. 查询薪资不为1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800);
-- 或
select name,sal from emp where sal not in(1400,1600,1800);

3.2.6.(自己完成) 查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

select name,sal from emp where sal>4000 or sal<2000;

3.2.7.(自己完成) 查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600;

3.2.7. 查询没有部门的员工（即部门列为null值）

select * from emp where dept=null; -- 错误写法!
select * from emp where dept is null; -- 判断某一列中有没有null,用is,而不是用=

– 思考：如何查询有部门的员工（即部门列不为null值）
select * from emp where dept is not null;
select * from emp where not(dept is null);

3.3. 模糊查询

模糊查询可以通过like关键字按照指定的模式进行匹配
需要配合 % 和 _(下划线)使用
%: 匹配0或多个任意字符
_: 匹配1个任意字符

3.3.1. 查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。

select name from emp where name like '刘%';

3.3.2.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

select name from emp where name like '%涛%'

3.3.3.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

select name from emp where name like '刘_';
select name from emp where name like '刘__';

3.4.多行函数查询

	count(*|列名) -- 统计查询的结果中的所有列或某一列的行数
	max(列名) -- 求某一列中的最大值,例如,max(sal)求最高薪资
	min(列名) -- 求某一列中的最小值,例如,min(sal)求最低薪资
	sum(列名) -- 求某一列中所有值的和,例如,sum(sal)求薪资这一列中所有值的和
	avg(列名) -- 求某一列中所有值的平均值,例如,avg(sal)求薪资这一列的平均薪资

3.4.1.统计emp表中薪资大于3000的员工个数

-- 查询薪资大于3000的员工有哪些
select * from emp where sal>3000;
-- 统计薪资大于3000的员工个数
select count(*) from emp where sal>3000;
select count(name) from emp where sal>3000;
select count(bonus) from emp where sal>3000;
# 使用多行函数对某一列统计,如果该列中包含null值,会直接将null值丢弃,不参与统计

3.4.2. 求emp表中的最高/最低薪资

select max(sal) from emp;
select min(sal) from emp;

3.4.3. 统计emp表中所有员工的薪资总和(不包含奖金)

select sum(sal) from emp;
select sum(bonus) from emp;

3.4.4. 统计emp表员工的平均薪资(不包含奖金)

select avg(sal) from emp;
select sum(sal)/count(*) from emp;

#多行函数和分组之间的联系
1) 如果不进行分组,那么整个查询结果默认就是一个组,在统计时就返回一个结果
	select * from emp; -- 查询结果有12条记录,默认是一个组
	此时如果使用多行函数对该查询结果进行统计,其实就是对这一个组进行统计,统计的结果也只有一个
	select count(*) from emp;
2) 如果添加了分组,最终分成了几个组,在统计时,就返回几个统计结果
	select count(*) from emp; -- 默认一个组,统计结果只有一个
	select gender,count(*) from emp group by gender; -- 分成两个组,统计结果有两个
	select job,count(*) from emp group by job; -- 分成3个组,统计结果就有3个

3.5. 分组查询

语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;

3.5.1.对emp表，按照部门对员工进行分组，查看分组后效果。

select * from emp group by dept;
-- 按照部门分组,统计每一组的人数
select dept,count(*) from emp group by dept;

3.5.2.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

-- 按照职位分组,统计每一组的人数
select job,count(*) from emp group by job;
# 在分组之后进行统计,显示的列中除了统计结果,还可以将进行分组的列一并显示

3.5.3. 对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

select dept,max(sal) from emp group by dept;

3.6.排序查询

语法：SELECT 列名 FROM 表名 `ORDER BY 列名 [ASC|DESC]`
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。

3.6.1.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

select name,sal from emp order by sal; -- 默认是升序,asc可以省略
select name,sal from emp order by sal asc;

3.6.2.对emp表中所有员工的奖金进行降序(从高到低)排序，显示员工姓名、奖金。

select name,bonus from emp order by bonus desc;

-- 按照奖金降序排序,如果奖金相同,再按照薪资降序排序
select name,bonus,sal from emp order by bonus desc,sal desc;

3.7.分页查询

在mysql中，通过limit进行分页查询，查询公式为：
`limit (页码-1)*每页显示记录数, 每页显示记录数`

3.7.1.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据

select * from emp limit 0,3;  -- 查询第 1 页
select * from emp limit 3,3;  -- 查询第 2 页
select * from emp limit 6,3;  -- 查询第 3 页
select * from emp limit 9,3;  -- 查询第 4 页

3.7.2.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

-- 按照薪资降序排序(从高到低),再分页,每页显示3条,只查询第1页
select name,sal from emp order by sal desc limit 0,3;

3.7.3.其他函数

curdate() -- 返回当前日期,格式:年月日
curtime() -- 返回当前时间,格式:时分秒
sysdate() / now() -- 返回当前日期+时间,格式:年月日 时分秒

year(日期)/month(日期)/day(日期)/hour(时间)/minute(时间)/second(时间)
-- 以上6个函数分别获取日期+时间中的 年份/月份/天数/小时/分钟/秒值

concat(s1,s2,s3..sn) -- 将传入其中的子字符串拼接在一起。
concat_ws(x,s1,s2,s3..sn) -- 将传入其中的子字符串拼接在一起，在拼接时会通过第一个参数,即间隔符进行拼接。

3.7.4.查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。

select name,birthday from emp 
where birthday between 1993 and 1995; -- 日期不能和数值比较,错误!
-- 方式一: 将birthday中的年份提取出来,和1993、1995进行比较
select name,birthday from emp
where year(birthday) between 1993 and 1995;

-- 方式二: 将1993、1995转成日期('1993-1-1'、'1995-12-31')再和birthday比较
select name,birthday from emp
where birthday between '1993-1-1' and '1995-12-31';

3.7.5.查询emp表中本月过生日的所有员工

-- 获取当前月份 和 员工的出生月份 进行比较
select * from emp
where month( now() )=month( birthday );

-- 获取下个月过生日的员工
select * from emp
where (month( now() )+1) % 12 =month( birthday ) % 12;

3.7.6.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

select name,concat( sal, '(元)' ) from emp;

– 补充练习：查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）
select name,concat( sal, ‘/元’ ) from emp;
select name,concat_ws( ‘/’, sal, ‘元’ ) from emp;

mysql的字段约束：
1）主键约束:
如果一个列可以唯一的表示一行表记录（或可以作为一行表记录的唯一标识），通常会给这样的列添加主键约束，通常情况下，每张表都会有一个主键。主键特点：唯一且不为空。（但不是每个唯一且不为空的列都是主键！）
如何添加主键约束:
create table stu(
id int primary key auto_increment,
…
);
主键自增策略:
如果主键是数值类型,可以为主键添加自增策略(目的是为了更方便插入主键的值)
添加了自增策略后，以后再插入数据时，可以不为主键赋值，数据库会自己维护一个变量（AUTO_INCREMENT）,该变量的值从1开始,每次用完后会自动加1,当插入数据时,如果没有给主键赋值,数据库就会从AUTO_INCREMENT变量上获取一个值,作为主键值插入到表中。

2）非空约束:
如果某一列的值要求不能为空（某些系统要求用户性别 或 密码不能为空），可以为这个列添加非空约束，这个列的值就不能为空（但可以重复）
如何添加非空约束:
create table stu(
gender varchar(10) not null,
);
3）唯一约束:
如果某一列的值要求不能重复，可以为这个列添加唯一约束，这个值就不能重复（但可以为空）
如何添加唯一约束:
create table stu(
username varchar(50) unique not null,
email varchar(20) unique,
);

4）外键约束（foreign key）:
问题1: 如何保存两张表数据之间的对应关系?
在其中的一张表中添加列,用于保存另外一张表的主键,以此来保存两张表数据之间的对应关系。
例如：在emp表中添加一个dept_id列,用于保存dept表中的id列,表示员工所属的部门编号

问题2: 如何避免在删除部门后,员工表中出现冗余数据?
方式一: 在删除每一个部门之前,先检查部门下还有没有对应的员工,如果有,先将员工删除或者移到别的部门,再删除部门即可
方式二: 通知数据库员工表和部门表之间存在对应关系,员工表中的dept_id列是要严格参考部门表中的id列。即设置dept_id这个列为外键。通知完后，数据库会一直帮我们盯着，如果再删除部门，部门下有员工，数据库就会阻止我们删除，或者当我们插入一个员工，而员工对应的部门编号在部门表中是不存在的，数据库会组织我们插入！

问题3: 什么是外键?
外键就是用于通知数据库两张表(比如部门和员工表)数据之间存在对应关系的这么一个列。
例如：将emp的dept_id设置为外键,就等同于告诉数据库,emp和dept表之间存在对应关系。

问题4: 如何添加外键?
create table dept(
id int primary key auto_increment,
name varchar(50)
);
create table emp(
id int primary key auto_increment,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
);

练习: 切换到db20库，分别查询dept和emp表中的数据
1）在没有将dept_id设置为外键的情况下去删除一个部门,查看是否能删除?
可以删除，数据库不知道两张表存在对应关系，所以不会阻止我们删除！
2）如果将dept_id设置为外键的情况下去删除一个部门,查看是否能删除?
如果部门下有员工，删除会失败，此时数据库知道两张表存在对应关系
并且会帮我们维护这个关系，所以会阻止我们删除有员工的部门！

3）如果加了级联删除,则在删除某一个部门的同时,先删除该部门下的所有员工,再删除部门本身!

---

4. 多表查询

4.1. 连接查询

– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!

---

4.1.1. 查询部门和部门对应的员工信息

select * from dept,emp;

上面的SQL语句执行的结果中有大量错误的数据，一般我们不会直接使用这种查询。
笛卡尔积查询: 是指两张表联查,其中一张表有m条记录,另一张表有n条记录,笛卡尔积查询的结果就是m*n条。
虽然上面的结果中包含错误数据，但也包含正确数据。可以通过where子句将其中错误的记录剔除，只保留正确的记录。

select * from dept,emp where emp.dept_id=dept.id;

如何书写where条件:
1)两张表联查,通常这两张表是有对应关系的,找到两张表中的对应关系的列(dept_id)
2)找到该列后,再找出这个列所对应的另外一张表的主键,让这个列和另外一张表的主键相等即可

---

4.2.连接查询

---

4.2.1. 查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null即可

select * from dept left join emp on emp.dept_id=dept.id;
#select * from emp right join dept on emp.dept_id=dept.id;
【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

– 44.查询【所有员工】及员工所属部门，如果某个员工没有所属部门，部门显示为null
select * from dept right join emp on emp.dept_id=dept.id;

【右外连接查询】：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。

---

4.3.子查询练习

– 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!

---

4.3.1.列出薪资比’王海涛’的薪资高的所有员工，显示姓名、薪资

-- 查询'王海涛'的薪资
select sal from emp where name='王海涛'; #2450
-- 查询 薪资比'王海涛'的薪资还高的员工
select name,sal from emp where sal > (
	select sal from emp where name='王海涛'
);

– 46.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。
– 查询’刘沛霞’的职位
select job from emp where name=‘刘沛霞’;

-- 查询 和'刘沛霞'从事相同职位的员工
select name,job from emp where job=(select job from emp where name='刘沛霞');

---

4.4.多表查询练习

---

4.4.1.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号，显示部门名称，员工名称。

-- 连接查询dept和emp表
	select d.name,e.name from dept d,emp e 
	where e.dept_id=d.id;
	
-- 求出'培优部'的员工
	select d.name,e.name from dept d,emp e 
	where e.dept_id=d.id and d.name='培优部';

– 48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
/* emp e1(员工表), emp e2(上级表)
查询的列: e1.name, e1.topid, e2.name
查询的表: emp e1, emp e2
筛选条件: e1.topid=e2.id
*/
select e1.name, e1.topid, e2.name
from emp e1, emp e2
where e1.topid=e2.id;

4.4.2. 列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资

	-- 按照职位进行分组(职位相同的为一组),求出每组(每个职位)的最低薪资
	select job,min(sal) from emp group by job;

	-- 求出哪些职位的最低薪资是大于1500的
	select job,min(sal) from emp group by job having min(sal)>1500;

	总结: where和having的区别:
	1)where是在分组之前对结果进行筛选过滤,where中不能包含多行函数,并且where中不能使用列别名(但可以使用表别名)
	2)having是在分组之后对结果进行筛选过滤,having中可以包含多行函数,并且having中可以使用列别名以及表别名。

– 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
– 按照部门对员工进行分组(每个部门的员工为一组)
select * from emp group by dept_id;
– 再统计每个部门的人数(count)以及平均薪资(avg)
select dept_id,count(*),avg(sal) from emp group by dept_id;

4.4.3.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、上级姓名、部门名称。

/* emp e1(员工表),emp e2(上级表),dept d(部门表)
	显示的列: e1.id,e1.name,e2.name,d.name
	查询的表: emp e1,emp e2,dept d
	连接条件: e1.topid=e2.id  e1.dept_id=d.id
	筛选条件: e1.hdate<e2.hdate
 */
	select e1.id,e1.name,e2.name,d.name
	from emp e1,emp e2,dept d
	where e1.topid=e2.id and e1.dept_id=d.id
				and e1.hdate<e2.hdate;

	select e1.id 员工编号,e1.name 员工姓名,e2.name 上级姓名,d.name 所属部门
	from emp e1,emp e2,dept d
	where e1.topid=e2.id and e1.dept_id=d.id
				and e1.hdate<e2.hdate;

本文感谢讲师张慎政教导，不过没有传送门。