7.10继续sql语句的coding（2）```

-- 1.01 查询所有字段的信息
SELECT * FROM student;

-- 1.02 查询指定字段
SELECT sno, sname, major FROM student;
SELECT sname, sno, major FROM student;

-- 1.03 查询的别名
SELECT sno, sno AS 学号 FROM student;
SELECT sname AS 姓名, major AS 专业 FROM student;

-- 精确匹配查询
-- 1.04 查询陈诗十的信息
SELECT * FROM student WHERE sname='陈诗十';

-- 1.05 查询陈诗十和张三的信息
SELECT * FROM student WHERE sname='陈诗十' OR sname='张三';

-- 1.06 查询陈诗十、张三、王一、刘鑫的信息
SELECT * FROM student WHERE sname IN ('陈诗十','张三','王一','刘鑫');

-- 1.07 查询学号大于1500的学生信息
SELECT * FROM student WHERE sno > 1500;

-- 1.08 查询学号在1600和1700之间的学生信息
SELECT * FROM student WHERE sno >=1600 AND sno <=1700;
SELECT * FROM student WHERE sno BETWEEN 1600 AND 1700;

-- 1.09 查询姓名不等于张三和李四的信息
SELECT * FROM student WHERE sname <> '张三' AND sname <> '李四';
SELECT * FROM student WHERE sname NOT IN ('张三','李四');

-- 1.10 查询性别为男，出生年大于1980的信息
SELECT * FROM student WHERE sex='男' AND born_year > 1980;

-- 模糊匹配查询
-- 1.11 查询姓为“王”的同学的信息
SELECT * FROM student WHERE sname LIKE '王%';

-- 1.12 查询姓名中含“一”的同学的信息
SELECT * FROM student WHERE sname LIKE '%一%';

-- 1.13 查询姓名为“李X”的同学的信息
SELECT * FROM student WHERE sname LIKE '李_';

-- 1.14 查询姓名长度为三，姓为“赵”，最后一个字为“一”的同学的信息
SELECT * FROM student WHERE sname LIKE '赵_一';

-- 1.15 查询所有姓王或姓李的同学的信息
SELECT * FROM student WHERE sname LIKE '王%' OR sname LIKE '李%';

-- 1.16 查询所有姓“张王李赵”的同学的信息
SELECT * FROM student WHERE sname LIKE '[张王李赵]%';

-- 1.17 查询专业中含有“_”的信息
SELECT * FROM student WHERE major LIKE '%[_]%';

-- 1.18 查询学号以1~3开头的信息
SELECT * FROM student WHERE sno LIKE '[1-3]%';

-- 1.19 查询姓从f到o开头的信息
SELECT * FROM student WHERE sname LIKE '[f-o]%';

-- 1.20 查询sname以li开头的信息，区分大小写(注：无法区分大小写）
SELECT * FROM student WHERE sname LIKE 'li%';

-- 关于NULL和LIKE的非操作
-- 1.21 查询专业为NULL，出生年大于1982，所有姓m的同学的信息
SELECT * FROM student WHERE major IS NULL AND born_year > 1982 AND sname LIKE 'm%';

-- 1.22 查询省份不为NULL的男生信息
SELECT * FROM student WHERE province IS NOT NULL AND sex='男';

-- 1.23 查询所有不姓“张”的同学信息
SELECT * FROM student WHERE sname NOT LIKE '张%';

-- 关于条件的组合
-- 1.24 查询出生年份大于1990或小于1985的所有女生信息
SELECT * FROM student WHERE  sex='女' AND (born_year > 1990 OR born_year < 1985);

-- 去除重复行
-- 1.25 查询同学分别来自哪几个省
SELECT DISTINCT province FROM student WHERE province IS NOT NULL;

-- 1.26 查询同学的专业有几种
SELECT DISTINCT major FROM student WHERE major IS NOT NULL;

-- 在查询中函数的使用
-- 1.27 查询今年为25岁的同学的信息
SELECT * FROM student WHERE year(getdate()) - born_year = 25;

-- 1.28 查询所有2008年（含2008）后入学的学生信息
SELECT * FROM student WHERE year(enrollment) >= 2008;

-- 1.29 查询所有7月份入学的学生信息
SELECT * FROM student WHERE month(enrollment) = 7;

-- 1.30 查询名字为3个字的学生信息
SELECT * FROM student WHERE len(sname) = 3;

-- 1.31 查询出所有女生的学号，姓名和入学年份，且显示表头为“学号”、“姓名”、“入学年”
SELECT sno AS 学号, sname AS 姓名, year(enrollment) AS 入学年 FROM student WHERE sex='女';

-- 排序查询
-- 1.31 查询所有学生信息，按姓名排序
SELECT * FROM student ORDER BY sname;

-- 1.32 查询所有学生信息，按学号倒序
SELECT * FROM student ORDER BY sno DESC;

-- 1.33 查询所有学生信息，按性别分开，先显示所有女生
SELECT * FROM student ORDER BY sex DESC;

-- 1.33 查询所有学生信息，按姓名正序，入学时间倒序排列
SELECT * FROM student ORDER BY sname ASC, enrollment DESC;

-- 1.34 查询专业中含“计算机”的学生信息，并按身份证号倒序排列
SELECT * FROM student WHERE major LIKE '%计算机%' ORDER BY card_id DESC;

-- 聚合函数查询
-- 2.01 查询一共有多少名学生
SELECT count(*) FROM student;

-- 2.02 查询有多少个省
SELECT count(DISTINCT province) FROM student WHERE province IS NOT NULL;

-- 2.03 查询学生中最大的年龄
SElECT max(year(getdate())-born_year) FROM student;

-- 2.04 查询最早的入学年份
SELECT min(year(enrollment)) FROM student;

-- 2.05 查询年龄最大的学生比最小的学生大多少岁
SELECT max(year(getdate())-born_year) - min(year(getdate())-born_year) FROM student;
SELECT max(born_year) - min(born_year) FROM student;

-- 2.06 统计所有学生的平均年龄
SELECT avg(year(getdate())-born_year) FROM student;

-- 2.07 统计所有学生的年龄总和
SELECT sum(year(getdate())-born_year) FROM student;

-- 分组查询
-- 2.08 查询男女生分别有多少人
SELECT sex, count(*) FROM student GROUP BY sex;

-- 2.09 查询每个省分别有多少同学
SELECT province, count(*) FROM student GROUP BY province;

-- 2.10 查询每个省分别有多少男生和女生，表头为“省”，“性别”，“数量”
SELECT province AS 省, sex AS 性别, count(*) AS 数量 FROM student GROUP BY province, sex;

-- 2.11 查询每个省分别有多少男生和女生，表头为“省”，“性别”，“数量”,并按省排序
SELECT province AS 省, sex AS 性别, count(*) AS 数量 FROM student GROUP BY province, sex ORDER BY province;

-- 2.11 查询各个年龄分别有多少人，表头为“年龄”，“数量”，按年龄正序排列（分组中不能使用别名）
SELECT year(getdate())-born_year AS 年龄, count(*) AS 数量
FROM student
GROUP BY year(getdate())-born_year
ORDER BY 年龄 ASC;

SELECT year(getdate())-born_year AS 年龄, count(*) AS 数量
FROM student
GROUP BY born_year
ORDER BY born_year DESC;

-- 2.12 查询年龄大于20岁的学生有多少人
SELECT count(*) FROM student WHERE year(getdate())-born_year > 20;

-- 2.13 查询男生人数大于5的省份
SELECT province,count(*) AS 数量 FROM student WHERE sex='男' GROUP BY province HAVING count(*) > 5;

-- 2.14 统计平均年龄大于15小于25的专业有哪些，表头为“专业”，“平均年龄”
SELECT major, avg(year(getdate())- born_year)
FROM student
GROUP BY major HAVING avg(year(getdate())- born_year) > 15 AND avg(year(getdate())- born_year) < 25;

-- 2.15 统计每个学生选修课的总成绩且总成绩不为空，表头“学号”，“总成绩”
SELECT sno, sum(score) FROM student_course WHERE score IS NOT NULL GROUP BY sno;
SELECT sno, sum(score) FROM student_course GROUP BY sno HAVING sum(score) IS NOT NULL;

-- 2.16 统计有两门（含两门）以上功课不及格（分数小于60）的学生学号
SELECT sno FROM student_course WHERE score < 60 GROUP BY sno HAVING count(*)>=1;

-- 分组聚合不同字段的区别
SELECT count(*)                 FROM student GROUP BY province ORDER BY count(*);
SELECT sno,province,major       FROM student WHERE province IS NULL;
SELECT province,count(sno)      FROM student GROUP BY province HAVING province IS NULL ORDER BY count(sno);
SELECT province,count(major)    FROM student GROUP BY province HAVING province IS NULL ORDER BY count(major);

总体来说这些语句需要不停的写啊写你才可能明白，当你看到了输出结果的时候也许你就理解了，尤其是对于不是计算机专业的童鞋来说实践才是硬道理！！！