sql练习题

原创

_追随我心 2016-03-29 20:21:25 博主文章分类：mysql ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者_追随我心的原创作品，请联系作者获取转载授权，否则将追究法律责任

图片省略了。

本文转自： http://blog.sina.com.cn/s/blog_767d65530101861c.html

1.创建student和score表

CREATE TABLE student (

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,

name VARCHAR(20) NOT NULL ,

sex VARCHAR(4) ,

birth YEAR,

department VARCHAR(20) ,

address VARCHAR(50)

);

创建score表。SQL代码如下：

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)

);

2.为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);

3.查询student表的所有记录

mysql> SELECT * FROM student;

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |

| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+------------+--------------+

4.查询student表的第2条到4条记录 （此题有错误）

mysql> SELECT * FROM student LIMIT 1,3;

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |

| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

+-----+--------+------+-------+------------+--------------+

limit语法：

select * from table limit m,n;

n指的是从第m+1条开始，取n条。

例1: select * from count limit 2,4; 从第3条开始，取4条数据。

例2：如果要取第5-10条数据，用select * from count limit 4,6 ; 此处 m=5-1=4 n=10-5+1=6

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

mysql> SELECT id,name,department FROM student;

+-----+--------+------------+

| id | name | department |

+-----+--------+------------+

| 901 | 张老大 | 计算机系 |

| 902 | 张老二 | 中文系 |

| 903 | 张三 | 中文系 |

| 904 | 李四 | 英语系 |

| 905 | 王五 | 英语系 |

| 906 | 王六 | 计算机系 |

+-----+--------+------------+

6.从student表中查询计算机系和英语系的学生的信息

mysql> SELECT * FROM student WHERE department IN ('计算机系','英语系');

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+------------+--------------+

7.从student表中查询年龄18~22岁的学生信息

mysql> SELECT id,name,sex,2013-birth AS age,department,address

-> FROM student

-> WHERE 2013-birth BETWEEN 18 AND 22;

+-----+------+------+------+------------+--------------+

| id | name | sex | age | department | address |

+-----+------+------+------+------------+--------------+

| 905 | 王五 | 女 | 22 | 英语系 | 福建省厦门市 |

+-----+------+------+------+------------+--------------+

mysql> SELECT id,name,sex,2013-birth AS age,department,address

-> FROM student

-> WHERE 2013-birth>=18 AND 2013-birth<=22;

+-----+------+------+------+------------+--------------+

| id | name | sex | age | department | address |

+-----+------+------+------+------------+--------------+

| 905 | 王五 | 女 | 22 | 英语系 | 福建省厦门市 |

+-----+------+------+------+------------+--------------+

8.从student表中查询每个院系有多少人

mysql> SELECT department, COUNT(id) FROM student GROUP BY department;

+------------+-----------+

| department | COUNT(id) |

+------------+-----------+

| 计算机系 | 2 |

| 英语系 | 2 |

| 中文系 | 2 |

+------------+-----------+

9.从score表中查询每个科目的最高分

mysql> SELECT c_name,MAX(grade) FROM score GROUP BY c_name;[注意：不是top]

+--------+------------+

| c_name | MAX(grade) |

+--------+------------+

| 计算机 | 98 |

| 英语 | 94 |

| 中文 | 95 |

+--------+------------+

10.查询李四的考试科目（c_name）和考试成绩（grade）

mysql> SELECT c_name, grade

-> FROM score WHERE stu_id=

-> (SELECT id FROM student

-> WHERE name= '李四' );

+--------+-------+

| c_name | grade |

+--------+-------+

| 计算机 | 70 |

| 英语 | 92 |

+--------+-------+

select c_name,grade from student,score where student.id=score.stu_id and name='李四';

11.用连接的方式查询所有学生的信息和考试信息

mysql> SELECT student.id,name,sex,birth,department,address,c_name,grade

-> FROM student,score

-> WHERE student.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

+-----+--------+------+-------+------------+--------------+--------+-------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |

| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 计算机 | 70 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 英语 | 92 |

| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

12.计算每个学生的总成绩

mysql> SELECT student.id,name,SUM(grade) FROM student,score

-> WHERE student.id=score.stu_id

-> GROUP BY id;[注意：不是count]

+-----+--------+------------+

| id | name | SUM(grade) |

+-----+--------+------------+

| 901 | 张老大 | 178 |

| 902 | 张老二 | 153 |

| 903 | 张三 | 95 |

| 904 | 李四 | 162 |

| 905 | 王五 | 94 |

| 906 | 王六 | 175 |

+-----+--------+------------+

13.计算每个考试科目的平均成绩

mysql> SELECT c_name,AVG(grade) FROM score GROUP BY c_name;

+--------+------------+

| c_name | AVG(grade) |

+--------+------------+

| 计算机 | 80.7500 |

| 英语 | 87.7500 |

| 中文 | 91.5000 |

+--------+------------+

14.查询计算机成绩低于95的学生信息

mysql> SELECT * FROM student

-> WHERE id IN

-> (SELECT stu_id FROM score

-> WHERE c_name="计算机" and grade<95);[注意：select子句的查询]

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+------------+--------------+

15.查询同时参加计算机和英语考试的学生的信息

mysql> 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= '英语' );

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+------------+--------------+

mysql> SELECT a.* FROM student a ,score b ,score c

-> WHERE a.id=b.stu_id

-> AND b.c_name='计算机'

-> AND a.id=c.stu_id

-> AND c.c_name='英语';

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+------------+--------------+

16.将计算机考试成绩按从高到低进行排序

mysql> SELECT stu_id, grade

-> FROM score WHERE c_name= '计算机'

-> ORDER BY grade DESC; [注意：默认为正序]

+--------+-------+

| stu_id | grade |

+--------+-------+

| 901 | 98 |

| 906 | 90 |

| 904 | 70 |

| 902 | 65 |

+--------+-------+

17.从student表和score表中查询出学生的学号，然后合并查询结果

Union：合并两个或者多个select语句的结果集

mysql> SELECT id FROM student

-> UNION

-> SELECT stu_id FROM score;

+-----+

| id |

+-----+

| 901 |

| 902 |

| 903 |

| 904 |

| 905 |

| 906 |

+-----+

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql> SELECT student.id, name,sex,birth,department, address, c_name,grade

-> FROM student, score

-> WHERE

-> (name LIKE '张%' OR name LIKE '王%')

-> AND

-> student.id=score.stu_id ;

+-----+--------+------+-------+------------+--------------+--------+-------+

+-----+--------+------+-------+------------+--------------+--------+-------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |

| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |

| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |

| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql> SELECT student.id, name,sex,birth,department, address, c_name,grade

-> FROM student, score

-> WHERE address LIKE '湖南%' AND

-> student.id=score.stu_id;

+-----+------+------+-------+------------+--------------+--------+-------+

+-----+------+------+-------+------------+--------------+--------+-------+

| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |

+-----+------+------+-------+------------+--------------+--------+-------+

补充部分：

======================================================================================

20.分别查询student表的第一条数据、最后一条数据、最后3条数据

第一条数据：

mysql> select * from student limit 1;

+-----+-----------+------+-------+--------------+--------------------+

+-----+-----------+------+-------+--------------+--------------------+

| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

+-----+-----------+------+-------+--------------+--------------------+

1 row in set (0.00 sec)

最后一条数据：

mysql> select * from student order by id desc limit 1;[注意：先倒序再查第一条]

+-----+--------+------+-------+--------------+--------------------+

+-----+--------+------+-------+--------------+--------------------+

| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |

+-----+--------+------+-------+--------------+--------------------+

1 row in set (0.00 sec)

最后三条数据：

select * from driver ORDER BY driver_id DESC LIMIT 0,3;

21.例如，查询公司2010年入职的各个部门每个级别里的最高薪水

SELECT dept,edlevel,MAX(salary) AS MAXIMUM

FROM STAFF

WHERE hiredate > '2010-01-01'

GROUP BY dept,edlevel

22. 例如，查找雇员平均工资大于3000的部门的最高薪水和最低薪水：

SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM

FROM STAFF

GROUP BY dept

HAVING AVG(salary) > 3000

ORDER BY dept

HAVING类似于WHERE（唯一的差别是WHERE过滤行，HAVING过滤组）AVING支持所有WHERE操作符。

23. 查看当前时间： select now();

24. 左右连接、内连接

一般说的左连接是指左外连接，右连接是指右外连接。

实例：

t1表

mysql> select * from t1;

+------+------+

| id | name |

+------+------+

| 1 | aaa |

| 2 | bbb |

+------+------+

2 rows in set (0.00 sec)

t2表

mysql> select * from t2;

+------+------+

| id | age |

+------+------+

| 1 | 20 |

| 3 | 30 |

+------+------+

2 rows in set (0.00 sec)

左连接sql语句

mysql> select * from t1 left join t2 on t1.id=t2.id;

+------+------+------+------+

| id | name | id | age |

+------+------+------+------+

| 1 | aaa | 1 | 20 |

| 2 | bbb | NULL | NULL |

+------+------+------+------+

2 rows in set (0.00 sec)

右连接sql语句

mysql> select * from t1 right join t2 on t1.id=t2.id;

+------+------+------+------+

| id | name | id | age |

+------+------+------+------+

| 1 | aaa | 1 | 20 |

| NULL | NULL | 3 | 30 |

+------+------+------+------+

2 rows in set (0.00 sec)

内连接sql语句

mysql> select * from t1 inner join t2 on t1.id=t2.id;

+------+------+------+------+

| id | name | id | age |

+------+------+------+------+

| 1 | aaa | 1 | 20 |

+------+------+------+------+

1 row in set (0.00 sec)

左外连接是以左边的表为基准,先将左边的表全部显示出来，然后右边的表id与左边表id相同的记录就“拼接”上去.

比如说id为1的记录。如果没有匹配的id，比如说t1中id为2的t2中就没有。那边就以null显示。

右连接类似

内连接就是只取出符合过滤条件的记录也就是t1.id=t2.id

26. 如果表里某个字段为json格式，那么取json格式的某个字段用 JSON_EXTRACT(`data`,'$.publishTime') 其中，data代表字段名，$.固定洗发 ,publishTime 要取的json字段值

注意：如果要打印该列需要用字段名，不能用 JSON_EXTRACT(`data`,'$.publishTime')

SELECT id,`key`,`data` FROM lite_app_const_config WHERE client_id='gongkao' and `key`='ExamExperience' ORDER BY JSON_EXTRACT(`data`,'$.publishTime') DESC;

27. group by 、 where 、having区别

GROUP BY 原理：https://blog.csdn.net/u014717572/article/details/80687042

where和having区别：where是非分组的情况下以列名为单位进行条件的，having是分组后以组为单位进行过滤。

根据group by的原理，group by后，生成的临时表部分列会有多个值，所以经常需要用聚合函数对这些值进行操作。

例子：

全表数据如下：

id username birthday address score

22 陈小明男河南郑州 56

24 张三丰河南郑州 34

25 陈小明1 河南郑州 34

93 zhang 2019-01-01 北京 34

94 李四 92020-04-27男北京22

95 大栗子 2020-07-06男北京12

116 sunlands 2021-03-25 beijing0

118 sunlands 022021-03-28 beijing 4

119 sunlands 022021-03-28 beijing 3

120 sunlands 022021-03-28 beijing 34

121 张三1 2019-01-011 北京24

121 zhang 2019-01-011 北京24

综合sql如下

SELECT address,SUM(score) FROM `user` WHERE address IS NOT NULL GROUP BY address HAVING SUM(score) > 60 ORDER BY SUM(score) DESC;

结果：

address Sum(score)

河南郑州124

北京92

上一篇：mysql分表分库

下一篇：索引

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯