10.27周测

CREATE TABLE student (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,sex VARCHAR(4),birth YEAR,department VARCHAR(20),address VARCHAR(50));CREATE TABLE score (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,stu_id INT(10) NOT NULL,c_name VARCHAR(20),grade INT(10));SELECT * FROM student;SELECT * FROM student LIMIT 1, 3;SELECT id,name,department FROM student;SELECT * FROM student WHERE department IN ('计算机系','英语系');SELECT id,name,sex,2017-birth,department,address FROM student WHERE 2017-birth BETWEEN 22 AND 27;SELECT department,COUNT(id) FROM student GROUP BY department;SELECT c_name,MAX(grade) FROM score GROUP BY c_name;SELECT c_name,grade FROM score WHERE stu_id = (SELECT id FROM student WHERE `name` = '李四');SELECT st.id,name,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE st.id = sc.stu_id;SELECT st.id,name,SUM(grade) FROM student st,score sc WHERE st.id = sc.stu_id GROUP BY st.id;SELECT c_name,AVG(grade) FROM score GROUP BY c_name;SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name = '计算机' AND grade < 95);SELECT * FROM student WHERE id = ANY(SELECT stu_id FROM score WHERE stu_id IN(SELECT stu_id FROM score WHERE c_name = '计算机')AND c_name = '英语');SELECT stu_id,grade FROM score WHERE c_name = '计算机' ORDER BY grade DESC;SELECT id FROM student UNION (SELECT stu_id FROM score);SELECT st.id,`name`,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE (`name` LIKE '张%' OR `name` LIKE '王%') AND st.id = sc.stu_id;SELECT st.id,`name`,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE address LIKE '湖南%' AND st.id = sc.stu_id;