常用SQL

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