SQL数据查询

1、查询students表中的所有数据

SELECT * FROM STUDENTS

2、查询students表中的姓名和年龄

SELECT NAME,AGE FROM STUDENTS

3、查询students表中name=‘xx’和‘yy’的数据

SELECT NAME FROM STUDENTS WHERE NAME='XX'OR NAME='YY'

SELECT NAME FROM STUDENTS WHERE NAME IN ('XX','YY)

SELECT NAME FROM STUDENTS WHERE NAME='XX'
UNION
SELECT NAME FROM STUDENTS WHERE NAME='YY'

4、查询年龄在14-18之间的学生姓名

SELECT NAME FROM STUDENTS WHERE AGE BETWEEN 14 AND 18

5、查询姓‘王’的同学姓名

SELECT NAME FROM STUDENTS WHERE NAME LIKE '王%'

6、查询姓王的同学且名字为两个字的同学姓名

SELECT NAME FROM STUDENTS WHERE NAME LIKE '王_'

7、查询未填写年龄的同学姓名

SELECT NAME FROM STUDENTS WHERE AGE IS NULL

8、查询19岁的王同学

SELECT NAME FROM STUDENTS WHERE NAME LIKE '王%' AND AGE= 19

9、查询姓王和姓李的同学

SELECT NAME FROM STUDENTS WHERE NAME LIKE '王%' OR NAME LIKE '李%'

10、查询年龄大于18或者姓王的同学

SELECT NAME FROM STUDENTS WHERE NAME LIKE '王%' or AGE>18

这样查询出来的肯定会有重复的数据。因为如果有姓王的同学年龄大于18就会被输出两次。这里就要用到关键字DISTINCT

SELECT DISTINCT NAME FROM STUDENTS WHERE NAME LIKE '王%' or AGE>18

11、查询学生姓名，按学生年龄排序

SELECT NAME FROM STUDETS ORDER BY AGE

这里用到了ORDER BY 关键字，默认为升序排列，升序关键字ASC,降序关键词DESC。

12、将STUDENTS表按年龄分组显示。

SELECT NAME FROM STUDETS GROUP BY AGE

13、显示成绩最好的三名学生

SELECT NAME FROM STUDENT ORDER BY GRADES DESC LIMIT 3

这里先将学生成绩按降序排列，使用limit关键字输出前三条查询记录。

14、显示学生人数

SELECT COUNT(ID) FROM STUDENTS 

15、显示学生成绩总和

SELECT SUM(GRADES) FROM STUDENTS

16、显示学生成绩的平均值

SELECT AVG(GRADES) FROM STUDENTS

17、显示成绩最好学生姓名

SELECT NAME
FROM STUDET
WHERE STUDENTS.GRADES = (
SELECT MAX(GRADES)
FROM STUDENTS
)

18、显示成绩最差学生姓名

SELECT NAME
FROM STUDET
WHERE STUDENTS.GRADES = (
SELECT MIN(GRADES)
FROM STUDENTS
)

19、显示王同学的老师姓名

SELECT TEACHER.NAME
FROM TEACHER,STUDENT
WHERE TEACHER.STUDENT_NAME = STUDENTS.NAME AND STUDENT.NAME LIKE '王%'

20、显示王同学的老师姓名

SELECT NAME
FROM TEACHER
WHERE TEACHER.NAME IN (
SELECT TEACHER_NAME
FROM STUDENT 
WHERE NAME LIKE '王%' 
)

21、查询成绩为优秀的学生姓名。

设有一个表A，grades = 90 status为优秀

SELECT NAME
FROM STUDENT
WHERE GRADES >= (
SELECT GRADES
FROM A
WHERE STATUS = '优秀'
)

22、如果B班有大于20岁的学生存在，输出B班的老师。

SELECT NAME
FROM TEACHER 
WHERE EXIST (
SELECT *
FROM STUDENT 
WHERE STUDENT.CLASS = B AND STUDENT.AGE >20
)

23、个人搞不清ANY和ALL关键字有什么区别，暂时不写了

24、查询A班同学和B班的同学

SELECT NAME
FROM STUDENTS
WHERE STUDET.CLASS = A
UNION
SELECT NAME
FROM STUDENTS
WHERE STUDET.CLASS = B