select基本用法

Select语句

注：下列所用数据库及二维表地址：https://blog.csdn.net/weixin_44134356/article/details/119959170?spm=1001.2014.3001.5501

-基础语法：

select [distinct] 字段名1 as 别名,2... from 表名 [where 条件语句] | [group by 字段名1,2... ] [order by 字段1,字段2.. [desc|asc]] [limit n]

-- 解释参数：

[]  --> 表示可选项
|  --> 表示其中之一
as  -->  用于设置别名
distinct  --> 设置去重，有时查询一个字段会有相同值，只想列出不同项
where  --> 设置筛选条件
group by  --> 表示分组，按照字段的值来分
order by  --> 表示排序，按字段排序，多个时，先按字段1排，如果1有相同在按照字段2排，类推
		desc - descend的缩写，降序
		asc - ascend的缩写，升序（默认可省）
limit  -->  分页查询，显示规定条数

-where（筛选）

• 进行数据条件筛选

-- 多个条件可用 and 或 or 跟随
-- 表示范围可用：字段名 between ~ and ~
	等价于 字段名>=x and 字段名<=y

-条件函数

例子：

• 条件语句用法：case 字段 when 值 then … else … end
  • 解释：下面的语句，查询出性别值为1就显示出男，否则显示女
• 方法三中，if()函数：if(值，x, y)
  • 解释：值为真，显示x, 否则y

-- 查询80后学生的姓名、性别、出生日期？
方法一：
select stu_name, stu_sex, stu_birth 
	from tb_student 
    where stu_birth>='1980-1-1' and stu_birth<='1989-12-31';

方法二：
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';

方法三：
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';

-模糊查询

-通配符

%   -->   表示匹配任意个字符（包括0个）
_   -->   表示精确匹配一个字符

-- 查询姓‘杨’的学生姓名和性别（%）
select stu_name, stu_sex from tb_student where stu_name like '杨%';

-- 查询‘杨**’名字三个字学生姓名和性别（_）
select stu_name, stu_sex from tb_student where stu_name like '杨__'; （注意：这里有2个下划线）

-- 查询名字中有‘不’字或者‘嫣’字的学生姓名？
方法一： （%）
select stu_name from tb_student 
where stu_name like '%不%' or stu_name like '%嫣%'; 

方法二：（union）
-- 使用union并集查询,会去重； union all不去重查所有
select stu_name from tb_student where stu_name like '%不%'
union
select stu_name from tb_student where stu_name like '%嫣%';

方法三：(正则 -  关键字regexp  -->  后跟正则表达式)
select 
	stu_name 
from tb_student
where stu_name regexp '[不嫣]';

-记录中空值处理（null）

• is null 或者 is not null --> 判断是否空值
• <=> --> 判断空值，等同is
• 区别：<=> 和 <>
  • <=> 判断null的等于,这个符号主要用于判断空值的
  • <> 不等于符号

-ifnull() 方法

ifnull() —> 如果遇到null（空值），将其替换为指定的值

ifnull(a,b)函数解释：
如果value1不是空，结果返回a
如果value1是空，结果返回b
注：a，b可以是查询语句

-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分
select sum(score) / count(*) from tb_record where stu_id=1001;
select avg(ifnull(score, 0)) from tb_record where stu_id=1001;

-- 查询没录入住址的学生名？
方式一：（is null）
select stu_name from tb_student 
where stu_addr is null or stu_add='';
方式二：（<=>）
select stu_name from tb_student 
where stu_addr<=>null or stu_addr='';

-- 查询录了住址的学生名？
select stu_name from tb_student 
where stu_addr is not null and stu_addr<>'';

-记录去重（distinct）

• 查询出来的记录存在重复值，可去掉重复值

-- 查询学生选课的所有日期？
select distinct sel_date from tb_record;

-常用聚合函数

stddev  -->  standard deviation - 标准差

variance  -  方差

avg  -->  average - 平均值

count  -  计数

sum  -  求和

max / min   -    最大/最小值

-排序（order by）

• 用法：order by 字段1,[字段2…] [ desc(降序) | asc(升序默认) ]
  • 先按照字段1排序，如果存在相同字段数据，则在按照字段2排序，以此类推

-- 查询男同学的姓名和生日，按年龄大到小排列（降序）
select 
	stu_name,
	stu_birth,
	floor(datediff(curdate(),stu_birth)/365) as stu_age
from tb_student 
where stu_sex=1 
order by stu_age desc;
解释：
-- curdate  -->  获取当前日期
-- datediff(日期1,日期2)函数 -> 计算两个日期的相差天数,前大后小是负数
-- floor / ceil  -->  向下/向上取整

-- 查询年龄最大学生的出生日期（min）
select min(stu_birth) from tb_student;
-- 年龄大，年份的数值就小

-分组（group by）

-- 语法：
group by 字段名1,字段名2 [having 筛选条件]

-- 注：分组以前的数据筛选使用where子句，分组后的数据筛选使用having子句

-- 查询每个学院的男女生人数？
select 
	col_id as 学院编号,
    if(stu_sex, '男', '女') as 性别, 
    count(*) as 人数
from tb_student group by col_id, stu_sex;
解释：
-- 先按照学院排序出学生，在按照男女分出各个人数

-- 查询平均成绩大于等于90分的学生学号和平均成绩？（group by  having）
select 
	stu_id as 学号, 
    round(avg(score), 2) as 平均分
from tb_record 
group by stu_id 
having 平均分>=90;
解释：
-- 先按照学生分组，求得每个学生得平均值，在使用having条件筛选，选出分大于等于90的学生

-嵌套查询（子查询）

• 了解：可定义临时变量获取查询值，格式如下
  • set @变量名=(select …)
  • select @变量名：=(select …)

-- 嵌套查询：把一个select的结果作为另一个select的一部分来使用
-- 嵌套查询通常也称之为子查询，在查询语句中有两个或多个select

-- 例如：把一个查询结果用来筛选条件
select stu_name 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 by stu_id having count(*)>2
	);
解释：
-- 先按照学生分组，获取选了2门课以上学生的id, 在根据in判断在这里面的学生id查询到学生姓名

-查询多个表（连接）

根据多个表中的外键进行联系，查询信息

-内连接

• 查询左右两表满足连接条件的数据

-外连接

• 左外连接（left join）：确保左表（现在join前面的表）中的所有记录都能查出来，不满足连接条件的补充null。
• 右外连接（right join）：确保右表（现在join后面的表）中的所有记录都能查出来，不满足连接条件的补充null。
• 全外连接：确保左表和右表中的所有记录都能查出来，不满足连接条件的都补充null。(MySQL不支持，可用左外并集（union）右外实现)

-- 查询学生的姓名、生日、和所在学院名称
（student表 和 college表）
方式1 
select stu_name,stu_birth,col_name from tb_student,tb_college 
where tb_college.col_id=tb_student.col_id;

方式2（内连接） -  inner join
select stu_name,stu_birth,col_name from tb_student inner join tb_college 
on tb_college.col_id=tb_student.col_id;

方式3（自然连接） -  natural join 
select stu_name,stu_birth,col_name 
from tb_student as t1 natural join tb_college t2;

-- 查询学生的姓名和选课的数量
select stu_name, total 
from 
	tb_student t1, 
	(
		select stu_id, count(*) as total from tb_record 
		group by stu_id
    ) t2
where t1.stu_id=t2.stu_id;
解释：
-- 先子查询出学生选课总数和学生id保存在临时表中，临时表取别名t2, 在根据外键连接，显示出id学生的姓名和选课数

外连接例子：

-- 查询每个学生的姓名和选课数量(外连接和子查询)
-- 左外连接
select stu_name, ifnull(total, 0) 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;

方式二（右连接）
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;

方式三（全外连接）
-- 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;

-分页查询（limit）

-- 记录指针初始为0
limit n  -->  查询结果显示前N条
limit n offset m  -->  查询显示结果跳过n条，显示M条记录
limit n,m  等价于 limit n offset m

-窗口函数

可看链接详细介绍

1.row_number()

• 输出排名，按顺序排下来，1-2-3-4…，并列第几名不影响结果

2.rank()

• 输出排名，可并列第几名，如（并列第二）：1-2-2-2-5-6…，并列之后会计算并列人数，在跟着排名

3.dense_rank()

• 输出排名，可并列，如（并列第一）：1-1-2-3…，并列之后连续输出排名

4.聚合函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。