- 去重
select distinct sex from stu

select age from stu where name =‘诸葛亮3‘
-- 条件查询
select * from stu where age <‘20‘

select * from stu where hometown !=‘河南‘

-- 查询学号为“003”学生身份证号
select card from stu where studenNo=‘003‘
-- 查询“01班”以外的学生信息
select * from stu where class !=‘01班‘
-- 查询年龄大于“20岁”学生的姓名和性别
select name ,sex from stu where age >‘20‘
-- 查询年龄小于20岁的女同学
select * from stu where age <‘20‘ and sex=‘女‘
-- 查询女学生或者为01班的学生
select * from stu where sex =‘女‘ or class =‘01班‘
-- 查询家乡不是河南的学生
select * from stu where not hometown =‘河南‘
-- 查询广东或河南的学生
select * from stu where hometown =‘广东‘ or hometown =‘河南‘
-- 查询01班的河南学生
select * from stu where class=‘01班‘ and hometown =‘河南‘
-- 查询非20岁的学生
select * from stu where not age =‘20‘
-- 查询姓刘的学生
select * from stu where name like ‘刘%‘
-- 查询姓曹且名字是一个字的学生
select * from stu where name like ‘曹_‘
-- 查询叫婵的学生
select * from stu where name like ‘%禅‘
-- 查询家乡是广东或者河南或者山东
select * from stu where hometown =‘广东‘ or hometown =‘河南‘ or hometown =‘山东‘
select * from stu where hometown in (‘广东‘,‘河南‘,‘山东‘)
-- 查询年龄大于18岁小于40岁的学生信息
select * from stu where age >=18 and age <= 40
select * from stu where age between 18 and 40
-- 查询年龄在19或23或34的女生
select * from stu where age in (‘19‘,‘23‘,‘34‘) and sex =‘女‘
-- 查询年龄在18到30岁以外的学生信息 
select * from stu where NOT age between 18 and 30

-- 查询没有填写身份证号的学生信息
select * from stu where card is NULL
-- 查询空字符串的身份证号的学生信息
select * from stu where card =‘‘
-- 查询填写身份证的学生信息
select * from stu where NOT card is null 
-- 查询学生所有信息，按照年龄从小到大排序（默认不写asc也是升序），即升序
select * from stu group by age asc
-- 查询学生所有信息，按照年龄从大到小排序，即降序
select * from stu group by age desc
-- 按照名字进行排序【对中文进行排序】
select * from stu group by CONVERT(name USING gbk)


-- 【聚合函数】
-- 查询学生总数
select count(*) from stu
-- 查询女生最大年龄
select max(age) from stu where sex =‘女‘
-- 查询1班最小年龄

-- 查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from stu 
-- 01班共有多少个学生
select count(*) from stu where class= ‘01班‘
-- 查询3班小于50岁的年龄有多少
select count(*) from stu where age < 50 and class=‘03班‘

-- 查询各种性别
select sex as 性别, count(*) as 性别总人数 from stu group by sex
-- 查询各种年龄的人数
select age , count(*) from stu group by age

-- 查询各个班级学生的平均年龄、最大年龄、最小年龄
select class as 班级, count(class) as 班级, avg(age) as 平均年龄,max(age) as 最大年龄,min(age) 最小年龄 from stu group by class

-- 分别查询别个班下面的男女各多少人
select class ,sex ,count(*) from stu group by class ,sex
-- 查询男生总人数
select count(*) from stu where sex ="男"
select sex, count(*) from stu group by sex having sex=‘男‘


-- 查询除01班以外其他班级的平均年龄、最大年龄、最小年龄

select class, avg(age),max(age),min(age) from stu where class !="01班" group by class
select class, avg(age),max(age),min(age) from stu group by class having not class=‘01班‘

-- 统计每个班级中每种性别的学生人数，且按照班级进行升序
select class,sex,count(*) from stu group by class , sex order by class

-- 查询前3条数据
select * from stu LIMIT 0,3
select * from courses

select * from scores
-- 等值查询
select * from stu,scores where stu.studenNo=scores.studenNo
-- 内连接
select * from stu inner join scores on stu.studenNo=scores.studenNo
-- 查询课程信息及课程的成绩
select * from courses inner join scores on courses.cuseNo=scores.curseNo

-- 查询学生信息及学生的课程对应的成绩
select * from stu
inner join scores on stu.studenNo=scores.studenNo
inner join courses on courses.corseNo=scores.corseNo


-- 查询张飞的成绩，要求显示姓名，课程号，成绩
SELECT
stu.name as 姓名,courses.corseNO as 课程号 , stu.studenNo as 学号 , scores.score as 成绩
FROM
stu
INNER JOIN scores ON stu.studenNo = scores.studenNo
INNER JOIN courses ON courses.corseNo = scores.corseNo
where stu.`name`=‘张飞‘

-- 查询男生中最高成绩，要求显示姓名，课程名，成绩
select stu.`name` as 姓名,courses.`name` as 课程名 , scores.score as 成绩 from stu inner join scores on stu.studenNo=scores.studenNo
inner join courses ON courses.corseNO=scores.corseNo where sex=‘男‘order by score desc LIMIT 0,1

-- 查询所有学生的成绩，包括没有成绩的学生
-- 左连接join 前面的表示为左表，join 后面的表示为右表
select * from stu left join scores on stu.studenNo=scores.studenNo
-- 查询所有学生的成绩，包括没有成绩的学生,显示课程名
select * from stu left join scores on stu.studenNo=scores.studenNo
left join courses on courses.corseNO=scores.corseNo