1 限制输出结果条数 数据第一行是0
select * from 表名 limit 5，5 ; 从行5开始的5行 第一个数为开始的位置，第二个数为要检索的位置
2 单列降序
select 列名1，列名2 from表名 order by 列名1 desc
3 区间范围值检查：
select lie1,lie 2 from student where lie1 betewen 5 and 10; 带上关键字 and 包含5和10
4 查询次序
SELECT * from SC where (Cno = 'c01' OR Cno = 'c02' ) AND Grade >88 优先级 ()> and >or
5 查询范围 IN ;NOT IN
select lie1 ,lie2 from biao where lie3 in （1002,1003） order by lie1
6 select lie1 ,lie2 from biao where lie2 like 'jet%' 检索任意 jet 起头的词。
select lie1 ,lie2 from biao where lie2 like '%@jet%' 匹配任何位置包含文本@jet%
7 select count（*) as num_cust from customers 利用count（*） 计算customer表中客户的总数
8 select sum (item_price*quantity) as total_price from orderitems where order_num=2005 (item_price*quantity) 用来计算所有物品价钱之和 ，WHERE同样保证只统计某个物品订单中的物品
select sum(Grade) from sc where Sno='9512101' 统计总和
9 分组 select Sno,COUNT(*) as num from SC GROUP BY Sno
count（*）表中行的数目计数，不管表列中包含的是空值还是非空值。
count（column）对特定列中具有值得行进行计数，忽略NULL值
10 having
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2 基于聚集分组值而不是特定行值
select Sno,COUNT(*) as num from SC GROUP BY Sno having num >3
11 排序 select Sno,COUNT(*) as num from SC GROUP BY Sno having num >1 ORDER BY num desc
12 子查询
作用：利用子查询进行过滤 使用的数据库关系表都是关系表 订单存储在两个表中:
查询考试成绩不及格的学生的姓名
select sname from student where student .sno in (select sno from sc where sc.grade <60 )
13 嵌套查询
查询选修了课程“c02”且成绩高于此课程的平均成绩的学生的学号和成绩
SELECT sno,grade FROM sc WHERE cno='c02' AND grade> (
SELECT avg(IFNULL(Grade,"0")) AS '平均成绩' FROM sc WHERE cno='c02')
SELECT sno,sname FROM student WHERE sno IN (
SELECT sno FROM sc WHERE cno IN (
SELECT cno FROM course WHERE Cname='数据库基础'))
查询选修了刘晨没有选修的课程的学生的学号和所在系
SELECT Sno,Sdept FROM Student WHERE Sno IN (
SELECT Sno FROM SC WHERE Cno NOT IN (
SELECT Cno FROM SC WHERE Sno=(
SELECT Sno FROM Student WHERE Sname='刘晨')))
14 去重
SELECT DISTINCT sno from sc
15 以Class降序查询Student表的所有记录
select * from student ORDER BY class DESC
