select查询、where、模糊查询、处理空值、去重、排序

一、select查询投影和别名

1.查询所有学生的信息

a.不推荐写法

select * from tb_student;

b.推荐写法

select
    stu_id, stu_name, stu_sex, stu_birth, col_id
from
    tb_student;

2.查询学生的学号、姓名（投影）

select
    stu_id as 学号, stu_name as 姓名
from
    tb_student;

3.查询所有课程的名称及学分（投影和别名）

select cou_name as 名称, cou_redit as 学分 
from tb_course;

二、where查询

1.查询所有女学生的姓名和出生日期（筛选）

select stu_name as 姓名, stu_birth as 出生日期 
from tb_student 
where stu_sex = '女';

2.查询籍贯为“四川成都”的女学生的姓名和出生日期（筛选）

select stu_name as 姓名, stu_birth as 出生日期 
from tb_student 
where stu_addr = '四川成都' and stu_sex = '女';

3.查询籍贯为“四川成都”或者性别是女的学生

select stu_name as 姓名 
from tb_student 
where stu_addr = '四川成都' or stu_sex = '女';

4.查询所有80后学生的姓名、性别和出生日期（筛选）

a.一般写法

select stu_name as 姓名 , stu_sex as 性别 , stu_birth as 出生日期 
from tb_student 
where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31';

注意：sql中不可以写1 < x < 3，但是python中可以，sql中必须用and拆开来写

b.between写法

select stu_name as 姓名 , stu_sex as 性别 , stu_birth as 出生日期 
from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31';

5.查询学分大于等于2的课程的名称和学分（筛选）

select cou_name as 名称, cou_redit as 学分 
from tb_course 
where cou_redit >= 2;

6.查询学分是奇数的课程的名称和学分（筛选）

select cou_name as 名称, cou_redit as 学分 
from tb_course 
where cou_redit mod 2 <> 0;

注意：sql的不等号是<>，但是也支持!=

7.查询选择选了1111的课程考试成绩在90分以上的学生学号（筛选）

select sid as 学号 from tb_record where cid = 1111 and score >= 90;

8.查询名字叫’男学生1‘的学生的姓名和性别（判断语句）

select stu_name as 姓名,
case stu_sex when '男' then '1' else '0' end as 性别
from tb_student
where stu_name = '男学生1';

select stu_name as 姓名,
    if(stu_sex, '男', '女') as 性别
from tb_student
where stu_name = '男学生1';

注意：if函数是sql里的函数，其他数据库里可能没有，if函数中的第一个参数是一个bool值，True则返回第一个结果，False则返回第二个结果；Oracle里面的if是decode

三、like模糊查询

1.查询姓’王‘的学生的姓名和性别（通配符 % 匹配零个或任意多个字符）

select stu_name as 姓名, stu_sex as 性别 
from tb_student 
where stu_name like '王%';

2.查询姓’王‘名字两个字的学生姓名和性别（通配符 _ 匹配一个字符）

select stu_name as 姓名, stu_sex as 性别 
from tb_student 
where stu_name like '王_';

3.查询姓’王‘名字三个字的学生姓名和性别

select stu_name as 姓名, stu_sex as 性别 
from tb_student 
where stu_name like '王__';

4.查询学号最后一位是3的学生的学号和姓名

select stu_name as 姓名, stu_id as 学号 
from tb_student 
where stu_id like '%3';

5.查询名字中有’天‘字或’大‘字的学生的学号和姓名

select stu_name as 姓名, stu_id as 学号 
from tb_student 
where stu_name like '%天%' or stu_name like '%大%';

select stu_name as 姓名, stu_id as 学号 from tb_student where stu_name like '%天%'
union
select stu_name as 姓名, stu_id as 学号 from tb_student where stu_name like '%不%'

四、regexp模糊查询（regular expression，正则表达式）

1.查询姓’王‘或姓’武‘名字三个字的学生的姓名

select stu_name as 姓名, stu_id as 学号 
from tb_student 
where stu_name regexp '[林杨][\\u4e00-\\u9fa5]{2}';

五、处理空值

1.查询没有录入籍贯的学生姓名（空值处理）

三值逻辑：TRUE / FALSE / UNKNOWN

select stu_name as 姓名, stu_id as 学号 
from tb_student 
where stu_addr is null or trim(stu_addr) = '';

trim：去掉字符串两边的空字符串，类似于python的strip。

2.查询录入了籍贯的学生姓名

select stu_name as 姓名, stu_id as 学号 
from tb_student 
where stu_addr is not null and trim(stu_addr) <> '';

3.concat:sql中的字符串拼接

select stu_name concat(stu_addr, 'hello') from tb_student;

注意：建表时尽量不要允许数据为NULL

六、去重（distinct）

1.查询学生选课的所有日期（去重）

select distinct stu_date from tb_record;

2.查询学生的籍贯(去重)

select distinct stu_addr 
from tb_student 
where trim(stu_addr) <> '' and stu_addr is not null;

七、排序（order by）

1.查询男学生的姓名和生日按年龄从大到小排序（排序）

order by 默认从小到大，ASC（从小到大），DESC（从大到小），order后面可以跟多个字段来进行排序

select stu_name, stu_birth 
from tb_student 
where stu_sex = '男' order by stu_birth asc, stu_id desc;

2.将上面的生日换算成年龄
a.获取日期

select curdate();

b.获取日期和时间

select now();

c.获取当前时间

select curtime();

d.计算相差日期

select datadiff('2007-12-31', '2007-12-30');

e.完整的语句

select stu_name, floor(datediff(curdate() ,stu_birth) / 365) as 年龄 
from tb_student 
where stu_sex = '男' order by 年龄 desc;

注意：向上取整是ceil，向下取整是floor