-- 操作符
-- distinct 去重
-- 查询grade表里面的具体有哪些科目
select distinct subject from grade;
-- in 多个相同的值
-- 查询学生名称是老子和孔子具体信息
select * from student where name = '孔子' or name = '老子' or name = '朱佳'
select * from student where name in ('孔子','老子','朱佳');
-- not in 不包含这些数据
select * from student where name not in ('孔子','老子','朱佳');
-- GROUP BY 分组
-- 按照科目去分组,然后查询每个组的总分
select subject,sum(score) from grade group by subject;
-- 聚合函数
-- avg(具体的列名)
-- 求grader表的平均分
select avg(score) from grade;
-- 求语文的平均分
select avg(score) from grade where subject = '语文';
-- count(列名|*)
-- 查询考语文的有几个
select count(*) from grade where subject = '语文';
-- min(列名)
-- 查询语文的最小成绩是多少
select min(score) from grade where subject = '语文';
-- max(列名)
-- 查询语文最高分
select max(score) from grade where subject = '语文';
-- 查询grade表最高分
select max(score) from grade
-- sum(列名)
-- 求数学的总分是多少
select sum(score) from grade where subject = '数学';
-- select 列名 from 表名称
select name from student;
select id,name from student;
-- * 查询所有
select * from student;
-- 使用比较运算符
-- 查询大于60分的数据
select * from grade where score > 60;
-- 查询 成绩不大于60
select * from grade where score !> 60;
select * from grade where score < 60;
-- 查询成绩不等于99的数据
select * from grade where score != 99;
select * from grade where score <> 99;
-- 条件筛选
-- and
--查询成绩大于90 并且 科目是语文
select * from grade where score > 90 and subject = '语文'
-- or
-- 查询姓名是孔子或者老子
select * from student where name = '孔子' or name = '老子';
-- not 姓名不是庄子的
select * from student where name not in ('孔子','庄子')
-- between 范围查询
-- 查询 成绩在90-95之间的
select * from grade where score between 90 and 95;
-- not between 不在这个范围
-- 查询 成绩不在80-90之间的
select * from grade where score not between 80 and 90;
-- 模糊查询
-- %任意字符
-- 查询名字是以子结尾
select * from student where name like '老%';
-- 查询名字中间有马
select * from student where name like '%马%';
-- _ 单个字符
-- 查询名字是x子
select * from student where name like '_子';
-- NULL
-- 查询gerade表里面s_id为null
select * from grade where s_id = null; -- 错误写法
-- null值不可以使用等于 is
select * from grade where s_id is null;
-- not null
select * from grade where s_id is not null;
-- top(数量名词) 前几条数据
select top (3) * from student;
-- 排序 order by
-- order by 字段名称 asc | desc
-- asc 升序
select * from grade order by score asc;
-- desc 降序
select * from grade order by score desc;
-- 查询成绩前五名的
select top(5) * from grade order by score desc;