SQL多表查询

多表查询
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)
);
mysql> 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)
-> );
Query OK, 0 rows affected (0.32 sec)

mysql> describe student;
±-----------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±------------±-----±----±--------±------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
±-----------±------------±-----±----±--------±------+
6 rows in set (0.01 sec)

创建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)
);

mysql> 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)
-> );
Query OK, 0 rows affected (0.43 sec)

mysql> describe score;
±-------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±---------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| stu_id | int(10) | NO | | NULL | |
| c_name | varchar(20) | YES | | NULL | |
| grade | int(10) | YES | | NULL | |
±-------±------------±-----±----±--------±---------------+
4 rows in set (0.11 sec)
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);

mysql> insert into student values(901,‘张老大’,‘男’,1985,‘计算机系’,‘北京市海淀区’);
Query OK, 1 row affected (0.24 sec)

mysql> insert into student values(902,‘张老二’,‘男’,1986,‘中文系’,‘北京市昌平区’);
Query OK, 1 row affected (0.33 sec)

mysql> insert into student values(903,‘张三’,‘女’,1990,‘中文系’,‘湖南省永州市’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(904,‘李四’,‘男’,1990,‘英语系’,‘辽宁省阜新市’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(905,‘王五’,‘女’,1991,‘英语系’,‘福建省厦门市’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(906,‘王六’,‘男’,1988,‘计算机系’,‘湖南省衡阳市’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,901,‘计算机’,98);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,901,‘英语’,80);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,902,‘计算机’,65);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,902,‘中文’,88);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,903,‘中文’,95);
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(null,904,‘计算机’,70);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,904,‘英语’,92);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(null,905,‘英语’,94);
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(null,906,‘计算机’,90);
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(null,906,‘英语’,85);
Query OK, 1 row affected (0.00 sec)

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.40 sec)

mysql> select * from score;
±—±-------±----------±------+
| id | stu_id | c_name | grade |
±—±-------±----------±------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
±—±-------±----------±------+
10 rows in set (0.00 sec)

3.查询student表的所有记录

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)
4.查询student表的第2条到4条记录
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)

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

mysql> select id,name,department from student;
±----±----------±-------------+
| id | name | department |
±----±----------±-------------+
| 901 | 张老大 | 计算机系 |
| 902 | 张老二 | 中文系 |
| 903 | 张三 | 中文系 |
| 904 | 李四 | 英语系 |
| 905 | 王五 | 英语系 |
| 906 | 王六 | 计算机系 |
±----±----------±-------------+
6 rows in set (0.00 sec)
6.从student表中查询计算机系和英语系的学生的信息
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)

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

mysql> select * from student where (2020-birth) >= 28 and (2020-birth) <= 32;
±----±-------±-----±------±-------------±-------------------+
| id | name | sex | birth | department | address |
±----±-------±-----±------±-------------±-------------------+
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
±----±-------±-----±------±-------------±-------------------+
4 rows in set (0.01 sec)

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

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

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

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

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 |
±----------±------+
2 rows in set (0.36 sec)

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

mysql> select student.id,name,sex,birth,department,address,c_name,grade from student,score where student.id = score.stu_id;
±----±----------±-----±------±-------------±-------------------±----------±------+
| id | name | sex | birth | department | address | c_name | grade |
±----±----------±-----±------±-------------±-------------------±----------±------+
| 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 |
±----±----------±-----±------±-------------±-------------------±----------±------+
10 rows in set (0.00 sec)

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

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

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

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

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

mysql> select * from student where id in (select stu_id from score where c_name = ‘计算机’ and grrade < 95);
±----±----------±-----±------±-------------±-------------------+
| id | name | sex | birth | department | address |
±----±----------±-----±------±-------------±-------------------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
±----±----------±-----±------±-------------±-------------------+
3 rows in set (0.00 sec)

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

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 = ‘英语’;
±----±----------±-----±------±-------------±-------------------+
| id | name | sex | birth | department | address |
±----±----------±-----±------±-------------±-------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
±----±----------±-----±------±-------------±-------------------+
3 rows in set (0.01 sec)

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 |
±-------±------+
4 rows in set (0.00 sec)

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

mysql> select id from student union select stu_id from score;
±----+
| id |
±----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
±----+
6 rows in set (0.13 sec)

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;
±----±----------±-----±------±-------------±-------------------±----------±------+
| id | name | sex | birth | department | address | c_name | grade |
±----±----------±-----±------±-------------±-------------------±----------±------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |
±----±----------±-----±------±-------------±-------------------±----------±------+
8 rows in set (0.02 sec)

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;
±----±-------±-----±------±-------------±-------------------±----------±------+
| id | name | sex | birth | department | address | c_name | grade |
±----±-------±-----±------±-------------±-------------------±----------±------+
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |
±----±-------±-----±------±-------------±-------------------±----------±------+
3 rows in set (0.00 sec)

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值