MySQL周测题
来源:互联网 发布:淘宝账号注销不了 编辑:程序博客网 时间:2024/06/04 19:52
测试题目
Student表的定义
Score表的定义
系统初始化了以下数据
- 向student表插入记录的INSERT语句如下:
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
- 向score表插入记录的INSERT语句如下:
INSERT INTO score VALUES(NULL,901, '计算机',98);INSERT INTO score VALUES(NULL,901, '英语', 80);INSERT INTO score VALUES(NULL,902, '计算机',65);INSERT INTO score VALUES(NULL,902, '中文',88);INSERT INTO score VALUES(NULL,903, '中文',95);INSERT INTO score VALUES(NULL,904, '计算机',70);INSERT INTO score VALUES(NULL,904, '英语',92);INSERT INTO score VALUES(NULL,905, '英语',94);INSERT INTO score VALUES(NULL,906, '计算机',90);INSERT INTO score VALUES(NULL,906, '英语',85);
1.创建student和score表语句
-- 创建student表CREATE TABLE student (id int(11) AUTO_INCREMENT PRIMARY KEY,name varchar(255) NOT NULL,sex varchar(255) DEFAULT NULL,birth year(4) DEFAULT NULL,department varchar(255) NOT NULL,address varchar(255) DEFAULT NULL);-- 创建score表CREATE TABLE score (id int(11) AUTO_INCREMENT PRIMARY KEY,stu_id int(11) NOT NULL,c_name varchar(255) DEFAULT NULL,grade int(10) DEFAULT NULL);
2.查询student表的所有记录
SELECT * FROM student;
3.查询student表的第2条到4条记录
SELECT *FROM STUDENTLIMIT 1,3;
4.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
SELECT id AS '学号',name AS '姓名',department AS '院系' FROM student;
5.从student表中查询计算机系和英语系的学生的信息(用 IN 关键字)
SELECT *FROM studentWHERE department IN ('计算机系','英语系');
6.从student表中查询年龄18~22岁的学生信息(用 BETWEEN AND)
SELECT * FROM student WHERE birth BETWEEN (YEAR(NOW())-22) AND (YEAR(NOW())-18);
SELECT YEAR(NOW()) 查询当前年份SELECT MONTH(NOW()) 查询当前月份SELECT DAY(NOW()) 查询当前日SELECT DATE(NOW()) 查询当前日期SELECT DATE_FORMAT(NOW(),'%Y') 查询当前年份
7.从student表中查询每个院系有多少人
SELECTdepartment AS '院系',COUNT(*) AS '人数'FROM studentGROUP BY department;
8.从score表中查询每个科目的最高分
SELECTc_name AS '科目',MAX(grade) AS '最高分'FROM scoreGROUP BY c_name;
9.查询李四的考试科目(c_name)和考试成绩(grade)
SELECTst.name AS '姓名',sc.c_name AS '考试科目',sc.grade AS '考试成绩'FROM score scLEFT JOIN student st ON st.id = sc.stu_idWHERE st.name = '李四';
10.用连接的方式查询所有学生的信息和考试信息
SELECT *FROM student stLEFT JOIN score sc ON st.id = sc.stu_id;
11.计算每个学生的总成绩
SELECTst.name AS '姓名',SUM(grade) AS '总成绩'FROM student stLEFT JOIN score sc ON st.id = sc.stu_idGROUP BY st.name;
12.计算每个考试科目的平均成绩
SELECTc_name AS '考试科目',AVG(grade) AS '平均成绩'FROM scoreGROUP BY c_name;
13.查询计算机成绩低于95的学生信息
-- 方法一:SELECT st.*FROM student stLEFT JOIN score sc ON sc.stu_id = st.idWHERE sc.c_name = '计算机' AND sc.grade < 95;-- 方法二:SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name = '计算机' AND grade < 95);
14.查询同时参加计算机和英语考试的学生的信息
-- 方法一:SELECT st.*FROM student stLEFT JOIN score sc ON sc.stu_id = st.idWHERE c_name = '计算机' OR c_name = '英语' GROUP BY stu_id HAVING COUNT(*) = 2;-- 方法二:SELECT * FROM studentWHERE id IN (SELECT stu_id FROM score WHERE c_name = '计算机' OR c_name = '英语' GROUP BY stu_id HAVING COUNT(*) = 2);-- 方法三:SELECT *FROM studentWHERE id = ANY( SELECT stu_id FROM scoreWHERE stu_id IN (SELECT stu_id FROM scoreWHERE c_name = '计算机' )AND c_name = '英语');-- 方法四:SELECT a.* FROM student a, score b, score cWHERE a.id = b.stu_id AND b.c_name = '计算机'AND a.id = c.stu_id AND c.c_name = '英语';
15.将计算机考试成绩按从高到低进行排序
SELECTc_name AS '课程名',grade AS '分数'FROM scoreWHERE c_name = '计算机'ORDER BY grade DESC;
16.从student表和score表中查询出学生的学号,然后合并查询结果
SELECT id FROM studentUNIONSELECT stu_id FROM score;
17.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECTst.name AS '姓名',st.department AS '院系',sc.c_name AS '考试科目',sc.grade AS '成绩'FROM score scLEFT JOIN student st ON sc.stu_id = st.idWHERE st.name LIKE '张%' OR st.name LIKE '王%';
18.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT s.name AS '姓名',YEAR(NOW())-s.birth AS '年龄',c.c_name AS '考试科目',c.grade AS '成绩',s.address AS '家庭住址'FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.idWHERE s.address LIKE '湖南%';
阅读全文
0 0
- MySQL周测题
- Mysql
- MySql
- MYSQL
- MySQL
- mysql
- mysql
- mysql
- mysql
- Mysql
- Mysql
- mysql
- MySQL
- mysql
- Mysql
- mysql
- mysql
- mysql
- oracle <>
- 程序循环
- Requests: 让 HTTP 服务人类
- [2017纪中10-30]Graph 最小生成树
- xml(2)
- MySQL周测题
- 页面布局 --- 格子布局
- Hive学习总结
- LightOJ1128 Greatest Parent
- 文章标题
- 【NOIP模拟】 (10.30) T2 游戏
- linux下使用百度云python客户端
- 使用Spring+Junit4.4进行测试
- mybatis中mysql和oracle的区别