常用SQL

2021-03-02 本文已影响0人路边树上的李子很好吃

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