mysql select练习2

1练习如下

1-9

题

1

mysql> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
6 rows in set (0.00 sec)


2

mysql> select * from student limit 1,3;
+-----+-----------+------+-------+------------+--------------------+
| id  | name      | sex  | birth | department | address            |
+-----+-----------+------+-------+------------+--------------------+
| 902 | 张老二    | 男   |  1986 | 中文系     | 北京市昌平区       |
| 903 | 张三      | 女   |  1990 | 中文系     | 湖南省永州市       |
| 904 | 李四      | 男   |  1990 | 英语系     | 辽宁市阜新市       |
+-----+-----------+------+-------+------------+--------------------+
3 rows in set (0.01 sec)


3

mysql> select id,name,department from student;
+-----+-----------+--------------+
| id  | name      | department   |
+-----+-----------+--------------+
| 901 | 张老大    | 计算机系     |
| 902 | 张老二    | 中文系       |
| 903 | 张三      | 中文系       |
| 904 | 李四      | 英语系       |
| 905 | 王五      | 英语系       |
| 906 | 王六      | 计算机系     |
+-----+-----------+--------------+
6 rows in set (0.00 sec)

4

mysql> select * from student where department='英语系' or department='计算机系';
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
4 rows in set (0.00 sec)




5

mysql> select * from student where birth  between 2001 and 2005;
Empty set (0.00 sec)


6

mysql> select count(*) as '人数',department from student group by department;
+--------+--------------+
| 人数   | department   |
+--------+--------------+
|      2 | 计算机系     |
|      2 | 中文系       |
|      2 | 英语系       |
+--------+--------------+
3 rows in set (0.00 sec)


7
mysql> select c_name,max(grade) from score group by c_name ;
+-----------+------------+
| c_name    | max(grade) |
+-----------+------------+
| 计算机    |         98 |
| 英语      |         94 |
| 中文      |         95 |
+-----------+------------+
3 rows in set (0.00 sec)

8
mysql> select t.name,c_name,grade from score  as s inner join student as t on s.stu_id=t.id where t.name='张三';
+--------+--------+-------+
| name   | c_name | grade |
+--------+--------+-------+
| 张三   | 中文   |    95 |
+--------+--------+-------+
1 row in set (0.00 sec)


9

mysql> select t.*,s.* from score  as s inner join student as t on s.stu_id=t.id ;
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id  | name      | sex  | birth | department   | address            | id | stu_id | c_name    | grade |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |  1 |    901 | 计算机    |    98 |
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |  2 |    901 | 英语      |    80 |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |  3 |    902 | 计算机    |    65 |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |  4 |    902 | 中文      |    88 |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |  5 |    903 | 中文      |    95 |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |  6 |    904 | 计算机    |    70 |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |  7 |    904 | 英语      |    92 |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |  8 |    905 | 英语      |    94 |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |  9 |    906 | 计算机    |    90 |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 10 |    906 | 英语      |    85 |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
10 rows in set (0.00 sec)


10

10-17

10


mysql> select t.name,s.* from student  as t right join (select  stu_id,sum(grade) from score group by stu_id) as s on s.stu_id=t.id group by stu_id;
+-----------+--------+------------+
| name      | stu_id | sum(grade) |
+-----------+--------+------------+
| 张老大    |    901 |        178 |
| 张老二    |    902 |        153 |
| 张三      |    903 |         95 |
| 李四      |    904 |        162 |
| 王五      |    905 |         94 |
| 王六      |    906 |        175 |
+-----------+--------+------------+
6 rows in set (0.00 sec)



11

mysql> select c_name,avg(grade) from score group by c_name;
+-----------+------------+
| c_name    | avg(grade) |
+-----------+------------+
| 计算机    |    80.7500 |
| 英语      |    87.7500 |
| 中文      |    91.5000 |
+-----------+------------+
3 rows in set (0.00 sec)


12


mysql> select * from score as s inner join student as t on s.stu_id=t.id where c_name='计算机'
 and grade<95;
+----+--------+-----------+-------+-----+-----------+------+-------+--------------+--------------------+
| id | stu_id | c_name    | grade | id  | name      | sex  | birth | department   | address            |
+----+--------+-----------+-------+-----+-----------+------+-------+--------------+--------------------+
|  3 |    902 | 计算机    |    65 | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
|  6 |    904 | 计算机    |    70 | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |
|  9 |    906 | 计算机    |    90 | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+----+--------+-----------+-------+-----+-----------+------+-------+--------------+--------------------+
3 rows in set (0.00 sec)


13

mysql> select * from score as s inner join student as t on s.stu_id=t.id where c_name='计算机' and c_name='英语';
Empty set (0.00 sec)


14

mysql> select * from student as t right join (select * from score  where c_name='计算机'order by grade desc) as s on t.id=s.stu_id;
+------+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id   | name      | sex  | birth | department   | address            | id | stu_id | c_name    | grade |
+------+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
|  901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |  1 |    901 | 计算机    |    98 |
|  902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |  3 |    902 | 计算机    |    65 |
|  904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |  6 |    904 | 计算机    |    70 |
|  906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |  9 |    906 | 计算机    |    90 |
+------+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
4 rows in set (0.00 sec)


15
mysql> select t.*,s.* from score  as s inner join student as t on s.stu_id=t.id ;
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id  | name      | sex  | birth | department   | address            | id | stu_id | c_name    | grade |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |  1 |    901 | 计算机    |    98 |
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |  2 |    901 | 英语      |    80 |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |  3 |    902 | 计算机    |    65 |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |  4 |    902 | 中文      |    88 |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |  5 |    903 | 中文      |    95 |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |  6 |    904 | 计算机    |    70 |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁市阜新市       |  7 |    904 | 英语      |    92 |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |  8 |    905 | 英语      |    94 |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |  9 |    906 | 计算机    |    90 |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 10 |    906 | 英语      |    85 |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
10 rows in set (0.00 sec)


16
mysql> select name,department,t.id,grade from (select id,name,department from student  where naame like '张%' or name like '王%') as t inner join score as s on t.id=s.stu_id;
+-----------+--------------+-----+-------+
| name      | department   | id  | grade |
+-----------+--------------+-----+-------+
| 张老大    | 计算机系     | 901 |    98 |
| 张老大    | 计算机系     | 901 |    80 |
| 张老二    | 中文系       | 902 |    65 |
| 张老二    | 中文系       | 902 |    88 |
| 张三      | 中文系       | 903 |    95 |
| 王五      | 英语系       | 905 |    94 |
| 王六      | 计算机系     | 906 |    90 |
| 王六      | 计算机系     | 906 |    85 |
+-----------+--------------+-----+-------+
8 rows in set (0.00 sec)



17

mysql> select name,address,grade from score as s inner join (select * from student where address like '%湖南省%') as t on s.stu_id=t.id;
+--------+--------------------+-------+
| name   | address            | grade |
+--------+--------------------+-------+
| 张三   | 湖南省永州市       |    95 |
| 王六   | 湖南省衡阳市       |    90 |
| 王六   | 湖南省衡阳市       |    85 |
+--------+--------------------+-------+
3 rows in set (0.00 sec)