SQL查询数据的几大方法

有你，查询数据我什么都不怕。快快掌握！！

出大招的工具：

1.使用LIKE、BETWEEN、IN进行模糊查询

eg1:

SELECT * FROM Students

WHERE 姓名 like '张%'

eg2:

SELECT StudentID, Score FROM SCore WHERE Score

BETWEEN 60 AND 80

eg3:

SELECT SName AS 学生姓名,SAddress AS 地址

FROM Students

WHERE SAddress IN ('北京','广州','上海')

2.通配符

eg:

SELECT * FROM 数据表

WHERE 编号 LIKE '00[^8]%[AC]%'

3.SUM() 

AVG() 

MAX()、MIN() 

COUNT()

eg1:

SELECT SUM(Score)  AS  学号为23的学生总分

FROM  Score 

WHERE  StudentID =23

eg2:

SELECT AVG(SCore) AS 及格平均成绩

FROM Score

WHERE Score >=60

eg3:

SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,

    MIN (Score) AS 最低分

FROM Score

WHERE Score >=60

eg4:

SELECT COUNT (*)  AS 及格人数

FROM Score

WHERE Score>=60

4.分组查询用法

SELECT …… FROM  <表名> 

WHERE  ……

GROUP BY ……

eg:

SELECT COUNT(*) AS 人数, SGrade AS 年级

FROM  Students

 GROUP BY SGrade

eg2:

SELECT CourseID, AVG(Score) AS 课程平均成绩

FROM Score

GROUP BY CourseID

eg3:

SELECT CourseID, AVG(Score) AS 课程平均成绩

FROM Score

GROUP BY CourseID

ORDER BY AVG(Score)

5.多列分组

SELECT COUNT(*) AS 人数,SGrade AS 年级,SSex AS   性别

 FROM StudentS

GROUP BY SGrade,SSex

ORDER BY SGrade

6.分组筛选

SELECT …… FROM  <表名>

WHERE ……

GROUP BY ……

HAVING……

eg:

SELECT COUNT(*) AS 人数,SGrade AS 年级

FROM Students

GROUP BY SGrade

HAVING COUNT(*)>15

7.多表连接查询

内连接(INNER JOIN)

SELECT   ……

FROM   表1

INNER JOIN   表2

ON   ……

等价于

SELECT  ……

FROM   表1，表2

WHERE ……

eg:

SELECT Students.SName, Score.CourseID, Score.Score

FROM   Students,Score

WHERE  Students.SCode = Score.StudentID

三表内连接：

eg：

SELECT

S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩

FROM Students AS S

INNER JOIN Score AS C ON (S.SCode = C.StudentID)

INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

外连接

左外连接   (LEFT JOIN)

eg：

SELECT   S.SName,C.CourseID,C.Score

FROM   Students AS S

LEFT JOIN   Score AS C

ON     C.StudentID = S.SCode

右外连接   (RIGHT JOIN)

eg：

SELECT 图书编号,图书名称,出版社名称

FROM 图书表

RIGHT OUTER JOIN 出版社表

ON 图书表.出版社编号 = 出版社表.出版社编号