MySQL外连接、子查询、分页查询、联合查询、DDL

2020年6月6日
人的一生，能说清楚的话，确实不多

查询员工姓名、入职日期并按入职日期升序排列

select last_name,hiredate
from employees
order by hiredate asc

查询员工姓名、入职日期并按入职日期的年份升序排列

思路：按年进行截取

1. date_formate(hiredate,’%Y’)
2. year(hiredate) 同理month(hiredate) day(hiredate) hour minute second

将当前日期显示成xxxx年xx月xx日

select date_format(now(),’%Y年%m月%d日’)

题目：
学员的信息表stuinfo
stuId stuName gender majorId
已知专业表major
id majorName
已知成绩表 result
id成绩编号 majorid stuid score
提问：查询所有男生的姓名、专业名和成绩，使用SQL92和SQL99两种语法方式实现

SQL92：
select stuName,majorName,score
from stuinfo s,major m,result r
where s.majorid=m.id and r.stuid=s.stuid
and s.gender=‘男’；

SQL99
select stuName,majorName,score
from stuinfo s
join major m on s.majorid=m.id
join result r on r.stuid=s.stuid
where s.gender=‘男’；

查询每个性别的每个专业的平均成绩，并按平均成绩降序

select avg(score) 平均成绩，gender,s.majorid
from stuinfo s
join result r on s.stuid=r.stuid
group by gender,s.majorid
order by 平均成绩 decs;

二、外连接（左连接，右连接）
只有在SQL99中有外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项，如果没有匹配项，则显示null
应用场景：一般用于查询主表中有从表中没有的记录
特点:

1. 外连接分主从表，两表的顺序不能任意调换
2. 左连接的话，左边为主表
   右连接的话，右边为主表

语法：
select 查询列表
from 表1 别名
left | right |full 【outer】 join 表2 别名
on 连接条件
where 筛选条件；

案例：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null

左连接
select b. * , bo .*
from beauty b
left join boys bo
on b.boyfriend_id=bo.id;
右连接
select b. * , bo .*
from beauty b
right join boys bo
on b.boyfriend_id=bo.id;

查询那个女神没有男朋友

左连接
select b. * , bo .*
from beauty b
left join boys bo
on b.boyfriend_id=bo.id;
where bo.id is null;
右连接
select b. * , bo .*
from beauty b
right join boys bo
on b.boyfriend_id=bo.id;
where bo.id is null;

查询那个部门没有员工，并显示其部门编号和部门名

select d. department_id,e.department_name
from department d
left jion emplyees e on d.department_id=e.department_id
where e.employee_id is null;

查询那个部门没有员工的个数

select count(*) 部门个数
from department d
left jion emplyees e on d.department_id=e.department_id
where e.employee_id is null;

查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

select b.id,d.name.bo.*
from beauty b
left join boy bo on b.boyfriend_id=bo.id
where b.id>3;

查询那个城市没有部门

select l.city
from departments d
right join locations l on d.location_id=l.location_id
where d.department_id is null;

查询部门名为sal 或IT 的员工信息

select d.* ,e.*
from departments d
left join employees e on d.department_id =e.department_id
where d.department_name= ‘sal’ or d.department_name= ‘IT’;

子查询

说明：当一个查询语句中有嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询，外面的select语句称为主查询或外查询

分类：
按子查询出现的位置进行分类：

1. select后面
   要求：子查询的结果为单行单列（标量子查询）
2. from后面
   要求：子查询结果可以为多行多列
3. where或having后面（使用率最高）
   要求：子查询的结果必须为单列，分为：
   单行子查询
   多行子查询
4. exists后面
   要求：子查询结果必须是单列（相关子查询）
   特点：
   1、子查询放在条件中，要求必须放在条件的右侧
   2、子查询一般放在小括号中
   3、子查询的查询优先于主查询
   4、单行子查询对应了 单行操作符：> < <= >= = <>
   多行子查询对应了 多行操作符：any/some all in

子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！！
案例：

查询和程序员相同部门的员工姓名和工资

①查询程序员的部门编号
select department_id
from employees
where last_name=‘程序员’；
②查询department_id=①的员工姓名和工资
select last_name,salary
from emploees
where department_id=(
select department_id
from employees
where last_name=‘程序员’；
);

查询工资比公司平均工资高的员工的员工号，姓名和工资

①查询平均工资
select avg(salary)
from employees
②查询salary>①的信息
select employee_id,last_name,salary
from employees
where salary>(
select avg(salary)
from employees
);

查询最低工资大于50号部门最低工资的部门id和其最低工资

①查询50号部门最低工资
select min(salary)
from employees
where department_id=50;
②查询各部门的最低工资，筛选看那个部门的最低工资>①
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50;
);

多行子查询

in 判断某字段是否在指定列表内
x in(10,20,50)
any/some 判断某字段的值是否满足其中任意一个
x>any(10,20,50)
x=any(10,20,50)相当于in
意思就是x大于里面的最低值就行
all 判断某字段的值是否满足里面所有的
x> all(10,20,50)
x>max()

案例：返回location_id是1400或1700的部门中所有员工姓名

①查询location_id是1400或1700的部门
select department_id
from departments
where location_id in(1400,1700);
②查询department_id=①的姓名
select last_name
from employee
where department_id in(
select distinct department_id
from departments
where location_id in(1400,1700);
);

放在select后面
案例：查询部门编号为50的员工个数

select(
select count(*)
from employees
where department_id=50
) 个数；

放在from后面
案例：

查询每个部门的平均工资的工资级别
①查询每个部门的平均工资、
select avg(salary),department_id
from employees
group by department_id
②将 ①和sal_grade两表连接查询
select dep_ag.department_id,dep_ag.ag,g.grade
from sal_grade g
join (
select avg(salary),department_id
from employees
group by department_id
) dep_ag on dep_ag.ag between g.minsalary and g.max_salary;

放在exists后面
案例：查询有为名字叫程序员的员工

select exists(
select *
from employees
where last_name=‘程序员’
) 有无程序员;

查询没有女朋友的男神信息

select bo.*
from boys bo
where bo.id in(
select boyfriend_id
from beauty b
);

分页查询

语句：
select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引，显示的条目数

执行顺序：

from 子句
join 子句
on 子句
where子句
group by 子句
having 子句
select 子句
order by 子句
limit子句

特点：

1. 起始条目索引从0开始，如果不写，这就是从0开始
2. limit 后面支持两个参数
   参数1：显示的起始条目索引
   参数2：条目数
   公式：
   假如要显示的页数是page，每页显示的条目数位size
   select *
   from employees
   limit (page-1)*size,size

案例：查询员工信息的前五条

select * from employees limit 5;
select * from employees limit 0,5;

查询有奖金的且工资较 高的第11名到第20名

select *
from employees
where commission_pct is not null
order by salary desc
limit 10,10;

联合查询

说明：当查询结果来自多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称union查询

特点：

1. 多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2. union 自动去重
   union all 可以支持重复项

语法：

select 查询列表 from 表1 where 筛选条件
union
select 查询列表 from 表2 where 筛选条件

案例：查询所有国家的年龄>20岁的用户信息

select *from chinese where age>20 union
select *from usa where uage>20;

查询所有国家的用户姓名和年龄

select uname,uage from usa
union
select name,age from chinese;

DDL(数据定义语言)

说明：Data Define Language，用于对数据库和表的管理和操作

库的管理

一、创建数据库

create database stuDB; //创建stuDB数据库
create database if not exists stuDB;//如果不存在这个库则创建，可提高代码的容错性

二、删除数据库

drop database stuDB;
drop database if exists stuDB;

表的管理

一、创建表
语法：

create table 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】
);

案例：没有添加约束

create table [if not exists] stuinfo(
stuid int ,
stuname varchar(20),
gender char,
email varchar(20),
borndate datetime
);

数据类型：

1. 整形
   tinyint smallint int bigint
2. 浮点型
   float(m,n) double(m,n) decimal(m,n)
   m和n可选
3. 字符型
   char(n):n可选
   varchar(n):n必选
   text n表示最多的字符个数
4. 日期型
   date time datetime timestamp
5. 二进制型
   blob 存储图片数据
   

desc stuinfo;//查看表的结构

常见约束

说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！
not null 非空：用于限制该字段为必填项
default默认 ：用于限制该字段没有显式插入值，则直接显式默认值
primary key主键：用于限制该字段的值不能重复，设置为主键列的字段默认不能为空，一个表只能有一个主键，当然可以是组合主键
unique 唯一：用于限制该字段的值不能重复，

check检查：用于限制该字段的值必须满足指定条件(mysql不支持check操作)
check(age between 1 and 100)

foreign key外键: 用于限制两个表的关系,要求外键的值必须来自主表的关联列

要求:

1. 主表的关联列和从表的关联列必须一致，意思一样，名称无要求
   2 主表的关联列要求必须是主键.

案例：添加约束

create table [if not exists] stuinfo(
stuid int primary key,//添加了主键约束
stuname varchar(20) unique not null,//添加了唯一约束+非空
gender char default ‘男’,//添加了默认约束
email varchar(20) not null,
age int check(age between 1 and 100),//添加了检查约束，mysql不支持
majorid int,
constraint fk_stuinfo_major foregin key (majorid) references major(id)//添加了外键
borndate datetime
);

二、修改表[了解]

语法：alter table 表名 add|modify|change|drop column 字段名 字段类型 字段约束;

修改表名

alter table stuinfo rename to students;

添加字段

alter table students add borndate datetime not null;

修改字段名

alter table student change column borndate birthday datetime;

修改字段类型

alter table students modify column birthday timestamp;

删除字段

alter table students drop column birthday;

三、删除表

drop table if exists students;

四、复制表

仅仅复制表的结构

create table newTable like stuinfo;

复制表的结构+数据

create table newTable1 select * from girls.beauty;

案例：复制员工表中的last_name,department_id,salary字段到新表 emp表，但不复制数据

create table emp
select last_name,department_id,salary
from myemployees.employees
where 1=2;//条件恒不成立

测试案例：

1. 使用分页查询实现，查询员工信息表中部门为50号的工资最低的5名员工信息

select *
from employees
where department_id = 50
order by salary asc
limit 0,5;

2. 使用子查询实现城市为Toroto的，且工资>10000的员工姓名

①查询城市为Toroto的部门编号
select department_id
from departments d
join locations l on d.location_id=l.location_id
where city=‘Toroto’
②查询部门号在①里面的员工姓名
select last_name
from employees
where salary>10000 department_id in(
select department_id
from departments d
join locations l on d.location_id=l.location_id
where city=‘Toroto’
);

3. 创建qqinfo，里面包含qqid,添加主键约束、昵称nickname,添加唯一约束、邮箱email(添加非空约束)、性别gender

create table if not exists qqinfo(
qqid int primary key,
nickname varchar(20) nuique,
email varchar(20) not null,
gender char(1)

);

4. 删除qqinfo

drop table if exists qqinfo;

5. 试写出SQL查询语句的定义顺序和执行顺序
   1、定义顺序（书写顺序）

select distinct 查询列表
from 表名 别名
join 表名 别名
on 连接条件
where 筛选条件
group by 分组列表
having分组后筛选
order by 排序列表
limit 条目数；

2、执行顺序

form子句
join子句
on子句
where子句
group by子句
having 子句
select子句
order by子句
limit子句