【SQL】高级查询语句

SQL是什么？SQL是结构化查询语言，通过SQL语句可以对数据库进行操作。

----------------------------------------------------------单表查询-----------------------------------------------------------

# 指定字段去重，比如将class字段去重
select distinct class  from students;

# 条件查询：比较运算符(>,<,=,!=)
-- 例1：查询小乔的年龄
select age from students where name = '小乔';
-- 例2：查询20岁以下的学生
select *from students where age < 20;
-- 例3：查询家乡不在北京的学生
select *from students where hometown != '北京';

# 条件查询：逻辑运算符(and,or,not)
-- 例1：查询年龄小于20的女同学
select *from students where age < 20 and sex = '女';
-- 例2：查询女学生或'1班'的学生
select *from students where sex = '女' or class = '1班';
-- 例3：查询非20岁的学生
select *from students where not age = 20;

# 条件查询：模糊查询(like)
# % ：匹配任意多个字符
# _ : 匹配一个任意字符
-- 例1：查询姓孙的学生
select *from students where name like '孙%';
-- 例2：查询姓名以‘乔’结尾的学生
select *from students where name like '%乔';
-- 例3：查询姓名中包含‘白’的学生
select *from students where name like '%白%';
-- 例4：查询姓孙且名字是一个字的学生
select *from students where name like '孙_';

# 条件查询：范围查询
# in表示在一个非连续的范围内
# between ... and ...表示在一个连续的范围内
-- 例1:查询家乡是北京或上海或广东的学生
select *from students where hometown in ('北京','上海');
-- 例2：查询年龄为18至20的学生
select *from students where age between 18 and 20;

# 条件查询：空判断
# 空表示null，与空字符是不一样的
# 判断为空: is null
-- 例1：查询没有填写身份证的学生
select *from students where card is null;
-- 判断非空: is not null
-- 例2：查询填写了身份证的学生
select *from students where card is not null;
-- 例3：查询身份证为空字符的学生
select *from students where card = '';

# 条件查询：排序
# asc从小到大排列，即升序
# desc从大到小排序，即降序
# 不标注默认按照从小到大
-- 例1：查询所有学生信息，按年龄从小到大排序(升序)
select *from students order by age asc;
-- 例2：查询所有学生信息，按年龄从大到小排序(降序)
select *from students order by age desc;
-- 例2：查询所有学生信息，按年龄从大到小排序(降序)；年龄相同时，再按学号从小到大排序(升序)
select *from students order by age desc,studentNo;

# 聚合函数: 配合分组查询使用
# count(*)表示统计有多少个
-- 例1：查询学生总数 
select count(*) from students;
-- 例2：查询3班年龄小于18岁的同学有几个
select count(*) from students where class = '3班' and age < 18;
-- 例3：查询女生的最大年龄  max()
select max(age) from students where sex = '女';
-- 例4：查询1班的最小年龄 min()
select min(age) from students where class = '1班';
-- 例5：查询北京学生的年龄总和 sum()
select sum(age) from students where hometown = '上海';
-- 例6：查询女生的平均年龄 avg()
select avg(age) from students where sex = '女';
-- 例7：查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from students;

# 分组查询：先分组，后用聚合函数查询
# 分组前筛选使用where
# 分组后筛选使用having
-- 例1：按照性别分组
select sex from students group by sex;
-- 例2：查询各种性别的人数
select sex, count(*) from students group by sex;
-- 例3：查询各种性别的人数、每组最大年龄、每组最小年龄
select sex, count(*),max(age),min(age) from students group by sex;
-- 例4：查询每个班级中各种性别的人数、各种性别的最小年龄
select class, sex , count(*), min(age) from students group by class, sex; 
-- 例5：查询除1班外其他班级学生的平均年龄
select class, avg(age) from students group by class having class != '1班';

# 分页显示
# 索引从0开始
# 如果起点为0， 0可以不写
# 格式：起点，每页显示几行
-- 例1：查询1~3行学生信息
select *from students limit 3;
-- 例2：查询4~6行学生信息
select *from students limit 3,3;

----------------------------------------------------------多表查询-----------------------------------------------------------

一、连接查询（重点）

1.内连接(inner join)：查询两张表都有的公共部分
格式：select *from (表1名) inner join (表2名) on (表1.公共字段名) = (表2.公共字段名);

-- 例1： 查询学生信息及学生的成绩(2张表内连接)
select *from students st inner join scores sc on st.studentNo = sc.studentno;
-- 例2： 查询王昭君的成绩， 要求显示姓名、 课程号、 成绩(2张表内连接)
select st.name , sc.courseNo , sc.score from students st inner join scores sc on st.studentNo = sc.studentno where st.name = '王昭君';
-- 例3：查询学生信息及学生的课程对应的成绩(3张表内连接)
select *from students st inner join scores sc on st.studentNo = sc.studentno inner join courses co on sc.courseNo = co.courseNo;

2.左连接(left join)：以左边的表为准,另一张表没有的数据会自动用NULL填充
格式：select *from (表1名) left join (表2名) on (表1.公共字段名) = (表2.公共字段名);

-- 例1:查询所有学生的成绩，包括没有成绩的学生
select *from students st left join scores sc on st.studentNo = sc.studentno;

3.右连接(right join )：同理

---------------------------------------------------------------------------------------------------------------------------------

二、子查询(了解)

①外面的语句，主要的语句，主查询
②里面的语句，将执行结果作为条件，子查询

-- 例1：查询王昭君的成绩，要求显示成绩

-- 普通写法，先查询王昭君的学号，再通过学号查成绩
select studentNo from students where name = '王昭君';
select score from scores where studentno = '001';

-- 子查询写法，一步到位且不写死
select score from scores where studentno = (
select studentNo from students where name = '王昭君'
);

---------------------------------------------------------------------------------------------------------------------------------

三、自关联查询(了解)

①什么时候可以使用自关联查询？一张表的左右字段有关联时
②一张表怎么关联？一张表可以多次起别名，自己和自己关联

-- 例1：查询出河南省所有的市
select *from areas a1 inner join areas a2 on a1.aid = a2.pid where a1.atitle = '河南省';