mysql----select----练习题(1)

TEACHER

---

 

--  1、 查询Student表中的所有记录的Sname、Ssex和Class列。
-- select * from STUDENG limit 1;
SELECT SNAME,SSEX,CLASS FROM STUDENG 

---

 

-- 2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT DEPART FROM TEACHER 

---

-- 3、 查询Student表的所有记录。
SELECT * FROM STUDENG

---

-- 4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80

---

-- 5、 查询Score表中成绩为85，86或88的记录。
select * FROM SCORE WHERE DEGREE IN (85,86,88)
SELECT * FROM SCORE WHERE DEGREE =85 OR DEGREE =86 OR DEGREE =88

---

-- 6、 查询Student表中“95031”班或性别为“⼥”的同学记录。
SELECT * FROM STUDENG WHERE CLASS='95033' OR SSEX='⼥'

---

-- 7、 以Class降序查询Student表的所有记录。
select * from STUDENG ORDER BY Class desc

---

-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from SCORE  ORDER BY CNO asc, DEGREE DESC;
SELECT * FROM SCORE ORDER BY 2,3 DESC

---

-- 9、 查询“95031”班的学⽣⼈数。
-- SELECT COUNT(*) FROM STUDENG WHERE CLASS='95031'
SELECT COUNT(*)AS '学⽣⼈数' FROM STUDENG WHERE CLASS =95031

---

 

-- 10、查询Score表中的最⾼分的学⽣学号和课程号。SELECT 
SELECT * FROM SCORE ORDER BY DEGREE DESC LIMIT 1

---

-- 11、查询‘3-105’号课程的平均分。
SELECT ROUND(AVG(DEGREE),2) AS'3-105课程平均分' FROM SCORE WHERE CNO='3-105'

---

-- 12、查询Score表中⾄少有5名学⽣选修的并以3开头的课程的平均分数。
SELECT CNO,AVG(DEGREE)FROM SCORE WHERE CNO LIKE '3%'GROUP BY CNO HAVING COUNT(*)>5

---

-- 13、查询最低分⼤于70，最⾼分⼩于90的Sno列。
SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND 
MAX(DEGREE)<90