查询关键字

1. SELECT
2. DISTINCT
3. AND、OR
4. BETWEEN、AND
5. IN、NOT IN
6. IS NULL
7. LIKE、“%”、“_”、“[charlist]”
8. ALL
9. AVG SUM MAX MIN COUNT
10. GROUP BY
11. HAVING
12. ORDER BY DESC/ASC
13. INTO
14. COMPUTE
15. ROLLUP
16. INNER JOIN
17. LEFT JOIN
18. RIGHT JOIN
19. FULL JOIN
20. CROSS JOIN
21. ANY
22. EXISTE
23. UNION、UNIONA LL
24. INTERSECT
25. EXCEPT

数据库更新

1. INSERT INTO<表名>[<列名>] VALUES<值>
2. UPDATE<表名> SET <列名> WHERE <条件>
3. DELETE FROM<表名> WHERE <条件>

练习题

SELECT *
--FROM STUDENTS
FROM CHOICES

--(1)	查询年级为2001的所有学生的名称并按编号升序排列。
SELECT  sname
FROM STUDENTS
WHERE grade ='2001'
ORDER BY sid ASC

--(2)	查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。
SELECT SCORE,(score-50)/10 +((score-50)%10)*0.1 as gpi
FROM CHOICES
WHERE score>60
--(3)	查询课时是48或64的课程的名称。
SELECT CNAME
FROM COURSES
WHERE hour IN (48,64)

--(4)	查询所有课程名称中含有data的课程编号。
SELECT CID
FROM COURSES
WHERE cname LIKE '%data%'

--(5)	查询所有选课记录的课程号（不重复显示）。
SELECT DISTINCT CID
FROM CHOICES

--(6)	统计所有教师的平均工资。
SELECT AVG(SALARY)
FROM TEACHERS

--***(7)   查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。
SELECT TID,AVG(score) AS AVG_SOCRE
FROM CHOICES
GROUP BY tid
ORDER BY AVG(score) DESC

--***(8)统计各个课程的选课人数和平均成绩。
SELECT CID ,COUNT(*)AS NUM,AVG(score) AS AVG_SCORE
FROM CHOICES
GROUP BY CID

--(9)	查询至少选修了三门课程的学生编号。
SELECT SID --,COUNT(*) AS SC_NUM
FROM CHOICES
GROUP BY sid
HAVING COUNT(*)>3

--(10)	查询编号800009026的学生所选的全部课程的课程名和成绩。
SELECT CID,SCORE
FROM CHOICES
WHERE SID = '800009026'

--(11)	查询所有选修了database的学生的编号。
SELECT SID 
FROM CHOICES
WHERE CID = (
	SELECT CID
	FROM COURSES
	WHERE cname = 'DATABASE'
)

--(12)	求出选择了同一个课程的学生数。
SELECT CID, COUNT(*) AS S_NUM
FROM CHOICES
GROUP BY cid

--(13)	求出至少被两名学生选修的课程编号。
SELECT CID 
FROM CHOICES
GROUP BY CID
HAVING COUNT(*)>2

--***(14)	查询选修了编号80009026的学生所选的某个课程的学生编号。
SELECT CID
FROM CHOICES
WHERE sid = (
	SELECT sid
	FROM CHOICES
	WHERE CID = '80009026'
)
--(15)	查询学生的基本信息及选修课程编号和成绩。
SELECT STUDENTS.SID,SNAME,EMAIL,GRADE ,CID,score
FROM STUDENTS, CHOICES


--(16)	查询学号850955252的学生的姓名和选修的课程名及成绩。
SELECT STUDENTS.SNAME,CHOICES.cid,CHOICES.score
FROM STUDENTS,CHOICES
WHERE  (STUDENTS.sid = CHOICES.sid)and(STUDENTS.sid = '850955252')

--(17)	查询与学号850955252的学生同年纪的所有学生资料。
SELECT SID,SNAME,EMAIL,GRADE
FROM STUDENTS
WHERE grade = (
	SELECT grade
	FROM STUDENTS
	WHERE sid = '850955252'
)

--(18)	查询所有有选课的学生的详细信息。
SELECT STUDENTS.SID,STUDENTS.SNAME,STUDENTS.EMAIL,STUDENTS.GRADE
FROM STUDENTS
WHERE SID =ANY (
	SELECT sid
	FROM CHOICES
	GROUP BY sid
	HAVING COUNT(*)>0
)


--(19)***查询没有学生选的课程的编号。
SELECT CID ,count(*) as s_num
FROM CHOICES
group by cid


--(20)	查询选修了课程名为C++的课时一样课程名称。
select cname
from COURSES
where hour = (
	select hour
	from COURSES
	where cname = 'C++'
)

--(21)	找出选修课程成绩最好的选课记录。
SELECT *
FROM CHOICES
WHERE SCORE = (
	SELECT MAX(SCORE)
	FROM CHOICES
)

--(22)	找出和课程UML或课程C++的课时一样课程名称。
SELECT CNAME
FROM COURSES
WHERE HOUR IN (
	SELECT hour
	FROM COURSES
	WHERE cname = 'UML'OR cname= 'C++'
)
--(23)	查询所有选修编号10001的课程的学生的姓名。
SELECT sname
FROM STUDENTS
WHERE sid=ANY(
	SELECT SID
	FROM CHOICES
	WHERE cid = '10001'
)
--(24)***	查询选修了所有课程的学生姓名。
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS(
	SELECT *
	FROM COURSES
	WHERE NOT EXISTS(
		SELECT *
		FROM CHOICES
		WHERE (STUDENTS.sid = CHOICES.sid )AND( COURSES.cid = CHOICES.cid)
	)
)
--(25)	利用集合运算，查询选修课程C++或选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'C++'
)
UNION ALL

SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'java'
)
--(26)	实现集合交运算，查询既选修课程C++又选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'C++'
)
UNION 

SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'java'
)
--(27)	实现集合减运算，查询选修课程C++而没有选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'C++'
)
EXCEPT
SELECT SID
FROM CHOICES
WHERE cid =(
	SELECT cid
	FROM COURSES
	WHERE cname = 'java'
)

SELECT *
FROM SC

SELECT SNAME ,BIRTH = YEAR(GETDATE())-AGE
FROM S

--比较大小
SELECT S#,GRADE
FROM SC
WHERE GRADE>85

SELECT S# ,GRADE
FROM SC
WHERE C# = 'C1'

--多重条件查询

select S#,C#,GRADE
FROM SC
--WHERE (C#='C1'OR C#='C2')AND GRADE>=90
where C# IN ('C1','C2')

--确定范围
select *
from t

select TNO,TN,PROF
FROM T
where SAL BETWEEN 1000 AND 6000

-- 确定集合 IN 关键字
-- 部分匹配查询 LIKE
SELECT TNO,TN
FROM T
WHERE TN LIKE '张%'

-- 空值查询 IS NULL
SELECT S#
FROM SC
WHERE GRADE IS NULL