MySQL查询语言

MySQL查询语言

查询table1的所有信息

select * from table1;
select column1, column2,... from table1;

投影、别名

select column1 as col1, column2 as col2 from table1;

筛选

• 用where来引入条件

-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex=0;
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name, stu_sex, stu_birth from tb_student
where stu_birth between 1980-1-1 and 1989-12-31;
-- 在查询时可以对列的值进行处理
-- 分支结构建议用(case...when 条件 then...else...end)
select stu_name as 姓名, 
case stu_sex when 1 then '男' else '女' end as 性别, 
stu_birth as 生日
from tb_student where stu_birth between 1980-1-1 and 1989-12-31;
-- MySQL方言：if()函数（其他数据库可能不兼容）---> Oracle方言decode()函数
select stu_name as 姓名, 
if(stu_sex, '男', '女') as 性别, 
stu_birth as 生日
from tb_student where stu_birth between 1980-1-1 and 1989-12-31;

通配符

• 通配符(wild card) - %：匹配零个或任意多个字符；_：精确匹配一个字符。

-- 查询姓”杨“的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨%';
-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨_';
-- 查询姓”杨“名字三个字的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨__';
-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
select stu_name from tb_student 
where stu_name like '%不%' or stu_name like '%嫣%';
-- 基于正则表达式的模糊查询
select stu_name from tb_student 
where stu_name regexp '.*不.*' or stu_name like '.*嫣.*';

• union和union all
  union: 去重；union all: 不去重

select stu_name from tb_student where stu_name like '%不%' 
union
select stu_name from tb_student where stu_name like '%嫣%';

空值的处理

• 空值(null)做任何运算结果也是产生空值(null),null相当于条件不成立（什么都查不到）

-- 查询没有录入家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_sddr is null;
-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_sddr is not null;

去重

• 使用distinct

-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record;
-- 查询学生的家庭住址(去重)
-- stu_addr有空值
select distinct stu_addr from tb_student where stu_addr is not null;

排序(order by)

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- 升序（从小到大）---> ascending
select stu_name, stu_birth from tb_student
where stu_sex=1
order by stu_birth asc;

-- 降序（从大到小）---> descending
-- curdate ---> 获取当前日期
-- datediff ---> 计算时间差（以天为单位）
-- floor / ceil ---> 向下/上取整
select stu_name, stu_birth as 生日
	floor(datediff(curdate(), stu_birth)/365) as 年龄 
from tb_student where stu_sex=1
order by stu_birth desc;

聚合函数

-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from tb_student;

-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from tb_student;

-- 查询编号为1111的课程考试成绩的最高分
select max(score) from tb_record where cou_id=1111;

-- 查询学号为1001的学生考试成绩的最低分
select min(score) from tb_record where stu_id=1001;

-- 查询学号为1001的学生考试成绩的平均分
select avg(score) from tb_record where stu_id=1001;

-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分
-- ifnull ---> 如果遇到null（空值），将其替换为指定的值
select avg(ifnull(score,0)) from tb_record where stu_id=1001;

-- 查询学号为1001的学生考试成绩的标准差
select round(std(score),4) from tb_record where stu_id=1001;

分组和聚合函数

-- 查询男女学生的人数(分组和聚合函数)
select
	if(stu_sex, '男', '女') as 性别
	count(*) as 人数
from tb_student group by stu_sex;

-- 查询每个学院男女学生人数
select
	col_id as 学院编号,
	if(stu_sex, '男', '女') as 性别,
	count(*) as 人数
from tb_student group by col_id, stu_sex;

-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select 
	stu_id as 学号, 
	round(avg(score),2) as 平均分
from tb_record group by stu_id;
	
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
select 
	stu_id as 学号, 
	round(avg(score),2) as 平均分
from tb_record group by stu_id
having 平均分>=90;

-- 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句
select 
	stu_id as 学号, 
	round(avg(score),2) as 平均分
from tb_record where cou_id in (1111,2222,3333)
group by stu_id having 平均分>=90;

子查询和连接查询

-- 查询年龄最大的学生的姓名(子查询)
-- 嵌套查询：把一个select的结果作为另一个select的一部分来使用
-- 嵌套查询通常也称之为子查询，在查询语句中有两个或多个select
select stu_name from tb_student 
	where stu_birth=(
		select min(stu_birth) from tb_student
	);

-- 查询年龄最大的学生姓名和年龄(子查询+运算)
-- 获取当前时间：curdate(), 求差值：datediff()
select stu_name, 
	floor(datediff(curdate(),stu_birth)/365) as 年龄
from tb_student where stu_birth=(
		select min(stu_birth) from tb_student
);

-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name from tb_student
where stu_id in (
	select stu_id from tb_record group stu_id where count(*)>2);

-- 查询学生的姓名、生日和所在学院名称
select stu_name, stu_birth, col_name
from tb_student t1 
inner join tb_coolege t2 on t1.col_id=t2.col_id;

-- 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
-- limit n: 限制取n条；limit n offset m: 跳过m条，取后面的n条
select stu_name, cou_name, score
from tb_record t1 
inner join tb_student t2 on t1.stu_id=t2.stu_id
inner join tb_course t3 on t1.stu_id=t3.stu_id
where score is not null 
-- order by score desc limit 10,5
order by score desc limit 5 offset 10;

-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name, avg_score
from tb_student t1 inner join
    (
		-- 结果是形似表
		select stu_id, round(avg(score),1) as avg_score
		from tb_record group by stu_id
	) t2 on t1.stu_id=t2.stu_id;

-- 查询学生的姓名和选课的数量
select stu_name, total 
from tb_student t1 inner join(
	select stu_id, count(*) as total from tb_record group by stu_id
) t2 on t1.stu_id=t2.stu_id;

-- 查询每个学生的姓名和选课数量(左外连接和子查询)
-- 内连接：查询左右两表满足连接条件的数据。
-- 外连接
-- 左外连接：确保左表（现在join前面的表）中的所有记录都能查出来，不满足连接条件的补充null。
-- 右外连接：确保右表（现在join后面的表）中的所有记录都能查出来，不满足连接条件的补充null。
-- 全外连接：确保左表和右表中的所有记录都能查出来，不满足连接条件的补充null。

-- 左外连接
select stu_name, ifnull(total, 0) as 选课数量 
from tb_student t1 left outer join(
	select stu_id, count(*) as total from tb_record group by stu_id
) t2 on t1.stu_id=t2.stu_id;

-- 删除tb_record表的外键约束
alter tb_record drop foreign key fk_record_stu_id;
alter tb_record drop foreign key fk_record_cou_id;

-- 给tb_record表加两条记录，学号5566在学生表没有对应的记录
insert into tb_record 
values
	(default, 5566, 1111, '2019-09-02', 80),
    (default, 5566, 2222, '2019-09-02', 70);

-- 右外连接
select t1.stu_id, stu_name, t2.stu_id, total as 选课数量 
from tb_student t1 right outer join(
	select stu_id, count(*) as total from tb_record group by stu_id
) t2 on t1.stu_id=t2.stu_id;

-- MySQL不支持全外连接
-- 可以通过左外连接与右外连接求并集运算得到全外连接的结果
select t1.stu_id, stu_name, t2.stu_id, total as total 
from tb_student t1 left outer join (
	select stu_id, count(*) as total from tb_record 
	group by stu_id
) t2 on t1.stu_id=t2.stu_id
union 
select t1.stu_id, stu_name, t2.stu_id, total as total 
from tb_student t1 right outer join (
	select stu_id, count(*) as total from tb_record 
	group by stu_id
) t2 on t1.stu_id=t2.stu_id;

• 囊括所有数据库查询的方法