t_grade：每个学生都出现三次，分别对应他们考试的科目和分数

//查询特定属性出来
select id,name from t_student;
//跟等于同效
select * from t_student where name like '张三'; //当然还有not like
//以“张三”开头的
select * from t_student where name like '张三%';
//以“张三”开头且后面只有两位的
select * from t_student where name like '张三__' //注意是两根下划线
//范围，包括首尾
select * from t_student where age in(21,23); //当然还有not in
select * from t_student where age between 21 and 23; //not between
//is null
select * from t_student where sex is null; //not null
//分组
select * from group By gradeName; //只能查出每个年级第一个出现的那个学生
//分组+聚合
select gradeName,group_concat(stuName) from t_student group by gradeName; //group_concat：把每个年级的所有学生都列出来，用逗号分隔
select gradeName,count(stuName) from t_student group by gradeName; //把每个年级的所有学生姓名统计出来（重复不算）
//不超过3个不查出来
select gradeName,count(stuName) from t_student group by gradeName having count(stuName) >3;
//在查出来的记录后面多加一行总计
select gradeName,count(stuName) from t_student group by gradeName with rollup;
//如果是文本的话则多加一行把所有的名字都打印出来
select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;
//从第几条开始查多少条出来
select * fron t_student limit 0,5;
//统计
select count(*) from t_grade;
//统计每个学生出现了几次（这样就可以知道每个学生参加了几门课程）
select count(*) from t_grade group_by stuName; 
//求张三的总分(换成avg就是求平均分了)
select stuName sum(score) from t_grade where stuName="张三";
//求每个人的总分
select stuName,sum(score) from t_grade group by stuName;
//找出张三的哪一科分数最高，最高多少
select stuName,course,max(score) from t_grade where stuName='张三';
//找出每个学生的最高分
select stuName,max(score) from t_grade group by stuName; //不能加course，因为group by要求查出来的字段必须是group by后面的字段，它按什么来分组的就只能出现什么字段

连接查询：将两个或两个以上的表按照某个条件连接起来

select * from t_book,t_bookType; //t_book中有一个类型字段引用的是第二张表的id，不过它不是外键；尽管两张表没有关联，但是类型有几个，书就要重复出现几次，每次对应一个类型，符合笛卡尔乘积的结果

内连接查询是最常用的一种连接查询

select * from t_book,t_bookType where t_book.bookTypeId=t_bookType.id; //书有基本，查出来就有几条，而且连带着它们对应的类型也查出来
//起别名
select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id;

外连接

//左连接：查出第一张表的所有数据，第二张表的匹配数据
select * from t_book left join t_booktype on t_book.bookTypeId=t_bookType.id; //把所有书都列出来，如果有的书没有设置类型的话那就用默认值（null之类）代替
//右连接
select * from t_book right join t_booktype on t_book.bookTypeId=t_bookType.id;

子查询

select* from t_book where booktypeId in (select id from t_booktype); //查出来的类型id必须在书本类型的范围内
select * from t_book where price>=(select from t_pricelevel where pricelevel =1);
//如果子查询有结果则继续外层查询，否则外层查询不执行
select * from t_book where exist (select * from t_booktype); //not exist
//满足任意一个条件
select * from t_book where price>=any (select price from t_pricelevel);
//满足所有条件(其实就是必须大于最大的)
select * from t_book where price>=all (select price from t_pricelevel);

union

//把两张表的id字段都合并
select id from t_book union select id from t_booktype; //比如第一张表有1,2,3，第二张有1,2,6，则结果为1,2,3,6
//不去重
select id from t_book where all select id from t_booktype;