mysql-查询

查询

student

course

1. 全表查询
   select * from student
2. 筛选查询
   select * from student where age = 18
3. 模糊查询
   select * from student where age = 18 and name like “小%”
   select * from student where age = 18 and name like “小_”
   %: 匹配 0~n个任意字符
   _: 匹配1个任意字符
4. 去重查询
   select distinct age from student
5. 分组查询
   select age, count(*) as number from student GROUP BY age
   使用group by 的select后只能跟group by 的字段和函数
6. 排序
   select * from student ORDER BY id desc
   select * from student ORDER BY id asc
   desc: 降序
   asc: 升序
7. 聚合函数
   select age, count(*) as number from student where age >= 18 GROUP BY age ORDER BY age desc

联合查询

1. 内联
   查询所有不及格
   select cid, name, grade from student, course where course.uid = student.id and grade < 60 GROUP BY cid, uid
2. 左外
   查询没有考试成绩的
   select * from student left JOIN course on student.id = course.uid where cid is null
   3.右外
   查询有考试但不在student表中的
   select * from student RIGHT JOIN course on student.id = course.uid where id is NULL
   4.交叉（笛卡尔乘积）
   select * from student CROSS join course

子查询

1. in
   查询不及格的
   select * from student where id in
   (select uid from course where grade < 60)
   查询0分或者100的
   select uid from course where grade in (0,100)
2. not in
   查询不及格的
   select * from student where id not in
   (select uid from course where grade >= 60)
3. EXISTS
   将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
   select * from student where EXISTS
   (select uid from course where grade = 101)
4. ANY
   满足任一个
   select * from student where id=ANY
   (select uid from course where grade <=60)
5. ALL
   满足所有
   select * from student where id >= ALl
   (select uid from course where grade is not null)

limit

可用于分页
偏移量为0，返回行的最大数目
select distinct * from student limit 0,3
select distinct * from student limit 3

临时表

select * from course,
(select * from student where name = “小花”) a
where course.uid = a.id