[大数据]SQL：基本的数据操作语句

1.查询全体学生的学号和姓名

SELECT Sno,Sname 
FROM student

2.查询全体学生的姓名，学号和所在系

SELECT Sname , Sno , Sdept  
FROM student

3.查询全体学生的详细信息

SELECT Sno , Sname , Sdept , Sage , Ssex
FROM student

SELECT * 
FROM student

4.查询全体学生的姓名及出生年份

SELECT Sname , 2022-Sage 
FROM student

5.查询全体学生的姓名及出生年份，有新列。

SELECT Sname  姓名,2022-Sage  出生年份 
FROM student

SELECT Sname , '出生年份' , 2022-Sage 
FROM student

SELECT Sname AS 姓名,'出生年份' AS 常数列 ,2022-Sage AS 年份 FROM student

6.在选课表中查询有哪些学生选修了课程，列出选课学生的学号

该列选课学生的学号有重复的

SELECT Sno 
FROM sc

distinct消除取值相同的行，即该列选课学生的学号无重复的

SELECT DISTINCT Sno 
FROM sc

7.查询计算机系全体学生的姓名

SELECT Sname 
From student 
WHERE Sdept = '计算机系'

WHERE后跟条件或者是为真的逻辑表达式

8.查询年龄小于20的学生姓名和年龄

SELECT Sname , Sage 
FROM student 
WHERE Sage<20

9.查询考试成绩有不及格的学生的学号

SELECT DISTINCT Sno 
FROM sc 
WHERE Crade<60

此处DISTINCT是因为有学生多门课程不及格的话直接去掉重复的学号

10.查询年龄在20-23岁之间的学生的姓名，所在系和年龄

SELECT Sname , Sdept , Sage 
FROM student 
WHERE Sage BETWEEN 20 AND 23

SELECT Sname ,Sdept , Sage 
FROM student 
WHERE Sage>=20 AND Sage<=23

11.查询年龄不在20-23岁之间的学生的姓名，所在系和年龄

SELECT Sname , Sdept , Sage 
FROM student 
WHERE Sage NOT BETWEEN 20 AND 23

SELECT Sname , Sdept , Sage 
FROM student 
WHERE Sage<20 OR Sage>23

建立图书表

CREATE TABLE 图书表(

书号 char(6) ,

书名 varchar(30),

价格 decimal(4,2),

出版日期 date

)

INSERT INTO 图书表 values
						 ('T001','Java程序设计','26.00','2015-6-1'),('T002','数据结构','32.00','2014-6-15'),

                         ('T003','操作系统基础','36.50','2015-7-1'),('T004','计算机体系结构','29.50','2014-6-1'),

						 ('T005','数据库原理','30.00','2015-7-1'),('T006','汇编语言','34.00','2013-8-15'),

						 ('T007','编译原理','38.00','2013-8-1'),('T008','计算机网络','35.00','2014-3-15'),

						 ('T009','高等数学','22.00','2013-3-1')

SELECT * FROM 图书表

12.查询2014年6月出版的全部图书的详细信息

SELECT * 
FROM 图书表 
WHERE 出版日期 BETWEEN '2014/6/1' AND '2014-6-30

13.查询信息系，数学系和计算机系学生的姓名和性别

SELECT Sname , Ssex 
FROM student 
WHERE Sdept IN ('信息系','数学系','计算机系')

SELECT Sname , Ssex 
FROM student 
WHERE Sdept = '信息系' OR Sdept =  '数学系' OR Sdept = '计算机系'

14.查询信息系和计算机系之外的其他系的学生姓名，性别和所在系

SELECT Sname , Ssex , Sdept 
FROM student 
WHERE Sdept NOT IN('信息系','计算机系')

SELECT Sname , Ssex , Sdept 
FROM student 
WHERE Sdept != '信息系' AND Sdept != '计算机系'

15.查询姓张的学生的详细信息

SELECT * 
FROM student 
WHERE Sname LIKE '张%'

16.查询学生表中姓张，李，刘的学生的详细信息

SELECT * 
FROM student 
WHERE Sname LIKE '[张李刘]%'

SELECT * 
FROM student 
WHERE Sname LIKE '张%' OR Sname LIKE '李%' OR Sname LIKE '刘%'

17.查询名字第二个字为小或大的姓名和学号

SELECT Sname , Sno 
FROM student 
WHERE Sname LIKE '_[小大]%'

18.查询所有不姓王也不姓张的学生姓名

SELECT Sname 
FROM student 
WHERE Sname NOT LIKE '[王张]%'

SELECT Sname 
FROM student 
WHERE Sname LIKE '[^王张]%'

SELECT Sname 
FROM student 
WHERE Sname NOT LIKE '[王]%' AND Sname NOT LIKE '[张]%'

19.查询姓王且名字是两个字的学生姓名

SELECT Sname 
FROM student 
WHERE Sname LIKE '王_'

20.查询姓王且名字是三个字的学生姓名

SELECT Sname 
FROM student 
WHERE Sname LIKE '王__'

SELECT Sname 
FROM student 
WHERE rtrim (Sname) LIKE '王__'

21.在Student表中查询学号的最后一位不是2，3，5的学生信息

SELECT * 
FROM student 
WHERE Sno LIKE '%[^235]'

22.查询没有有考试成绩的学生的学号和相应的课程号

SELECT Sno , Cno 
FROM sc 
WHERE Crade IS NULL

23.查询所有有考试成绩的学生的学号，课程号和成绩

SELECT Sno , Cno , Crade 
FROM sc 
WHERE Crade IS NOT NULL

24.查询计算机系和信息系在20岁以下的学生姓名

SELECT Sname 
FROM student 
WHERE Sdept = '计算机系' OR Sdept = '信息系'

25.查询计算机系和信息系年龄大于等于20的学生姓名，所在系和年龄

SELECT Sname , Sdept , Sage 
FROM Student 
WHERE (Sdept = '计算机系'OR Sdept = '信息系')
AND Sage >=20

SELECT Sname , Sdept , Sage 
FROM Student 
WHERE Sdept IN('计算机系','信息系')AND Sage >=20

26.查询全体学生详细信息，并将结果按年龄升序排序

SELECT * 
FROM student 
ORDER BY Sage ASC

ORDER BY是对列名排序,其默认的是ASC（升序）

27.查询选修了“C02”号课程的学生的学号及其成绩，查询结果按成绩降序排序

SELECT Sno , grade 
FROM sc 
WHERE Cno = 'C02' 
ORDER BY crade DESC

28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的按年龄降序排列

SELECT * 
FROM student 
ORDER BY Sdept ASC ,Sage DESC

29.统计学生总人数

SELECT COUNT(*) AS 学生总人数 
FROM student

30.统计选修了的课程学生人数

SELECT COUNT (DISTINCT Sno) as 学生人数  
FROM sc

31.统计“9512101”学生的选课门数和考试总成绩

select count(*),sum(grade)
from sc
where sno = '9512101'

32.计算“c01”课程的考试平均成绩

select avg(grade)
from sc
where cno = 'c01'

33.查询“c01”课程的考试最高分和最低分

select max(grade),min(grade)
from sc
where cno = 'c01'

34.查询“9512101”学生的选课门数，已考试课程门数以及考试最高分、最低分、平均分。

select count(*) , count(grade),max(grade),min(grade),avg(grade)
from sc
where sno = '9512101'

35.统计每门课的选课人数，列出课程号和选课人数。

select cno,count(sno)
from sc
group by cno

36.统计每个学生的选课门数和平均成绩

select sno,count(*),avg(grade)
from sc
group by sno

37.统计每个系的学生人数和平均年龄

select sdept,count(*),avg(sage)
from student
group by sdept

38.统计每个系的女生人数

select sdept,count(*)
from student
where ssex = '女'
group by sdept

39.统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄，结果按照系名的升序排序

select sdept,ssex,count(*),max(sage)
from student
group by sdept,ssex
order by sdept asc

40.在选课表中统计每门课程的选课人数和已考试的人数。

select cno,count(*),count(grade)
from sc
group by cno

41.查询选修了三门以上的课程的学生的学号和选课门数。

select sno,count(*)
from sc
group by sno
having count(*) > 3

42.查询选课门数超过四门的学生的平均成绩和选课门数

select sno,count(*),avg(grade)
from sc
group by sno
having count(*) >= 4

43.查询计算机系和信息管理系的学生人数。

select sdept,count(*)
from student
group by sdept
having sdept in('计算机系','信息管理系')

select sdept,count(*)
from student
where sdept in('计算机系','信息管理系')
group by sdept

44.查询每个系年龄小于等于20的学生人数。

select sdept,count(*)
from student 
where sage <= 20
group by sdept

错误写法：

select sdept,count(*)
from student
group by sdept
having sage <= 20

45.查询每个学生及其选课的详细信息

select *
from student inner sc on student.sno = sc.sno

46.去掉例题45 中的重复列

select student.sno,sname,ssex,sage,sdept,cno,grade
from student join sc on student.sno = sc.sno