MySQL查询语句练习
-
创建Student与Score表
Student:
CREATE TABLE `db`.`student` ( `Id` INT(10) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(20) NOT NULL, `Sex` VARCHAR(4) NULL, `Birth` YEAR NULL, `Department` VARCHAR(20) NOT NULL, `Address` VARCHAR(50) NULL, PRIMARY KEY (`Id`), UNIQUE INDEX `Id_UNIQUE` (`Id` ASC) VISIBLE);
Score:
CREATE TABLE `db`.`score` ( `Id` INT(10) NOT NULL AUTO_INCREMENT, `Stu_id` INT(10) NOT NULL, `C_name` VARCHAR(20) NULL, `Grade` INT(10) NULL, PRIMARY KEY (`Id`), UNIQUE INDEX `Id_UNIQUE` (`Id` ASC) VISIBLE);
-
给Student与Score表添加记录
Student: insert into student(Id,name,Sex,Birth,Department,Address)values ('801','张老大', '男',1985,'计算机系', '北京市海淀区'), ('802','张老二', '男',1986,'中文系', '北京市昌平区'), ('803','张三', '女',1990,'中文系', '湖南省永州市'), ('804','李四', '男',1990,'英语系', '辽宁省阜新市'), ('805','王五', '女',1991,'英语系', '福建省厦门市'), ('806','王六', '男',1988,'计算机系', '湖南省衡阳市'); Score: insert into score (Stu_id,C_name,Grade)values (801,'计算机',98), (801,'英语', 80), (802, '计算机',65), (802, '中文',88), (803, '中文',95), (804, '计算机',70), (804, '英语',92), (805, '英语',94), (805, '计算机',90), (806, '英语',85);
-
查询student表的所有记录
mysql> select * from student; +-----+-----------+------+-------+--------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+-----------+------+-------+--------------+--------------------+ | 801 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | | 802 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | | 803 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | | 806 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | +-----+-----------+------+-------+--------------+--------------------+
-
查询 student 表的第 2 条到 4 条记录
mysql> select * from student limit 1,3; +-----+-----------+------+-------+------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+-----------+------+-------+------------+--------------------+ | 802 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | | 803 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | +-----+-----------+------+-------+------------+--------------------+
-
从 student 表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
mysql> select Id,Name,Department from student; +-----+-----------+--------------+ | Id | Name | Department | +-----+-----------+--------------+ | 801 | 张老大 | 计算机系 | | 802 | 张老二 | 中文系 | | 803 | 张三 | 中文系 | | 804 | 李四 | 英语系 | | 805 | 王五 | 英语系 | | 806 | 王六 | 计算机系 | +-----+-----------+--------------+
-
从 student 表中查询计算机系和英语系的学生的信息
mysql> select * from student where Department in ('计算机系','英语系'); +-----+-----------+------+-------+--------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+-----------+------+-------+--------------+--------------------+ | 801 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | | 806 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | +-----+-----------+------+-------+--------------+--------------------+
-
从 student 表中查询年龄 28~32 岁的学生信息
mysql> select * from student where 2020-Birth between 28 and 32; +-----+--------+------+-------+--------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+--------+------+-------+--------------+--------------------+ | 803 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | | 806 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | +-----+--------+------+-------+--------------+--------------------+
-
从 student 表中查询每个院系有多少人
mysql> select Department,count(D) from student group by Department; +--------------+-----------+ | Department | count(D) | +--------------+-----------+ | 计算机系 | 2 | | 中文系 | 2 | | 英语系 | 2 | +--------------+-----------+ 3 rows in set (0.01 sec)
扩展练习:查询score表学习每个课程的人数
mysql> select C_name,count(Cn) from score group by C_name; +-----------+-----------+ | C_name | count(Cn) | +-----------+-----------+ | 计算机 | 4 | | 英语 | 4 | | 中文 | 2 | +-----------+-----------+
-
从 score 表中查询每个科目的最高分
mysql> select C_name,max(Grade) from score group by C_name; +-----------+------------+ | C_name | max(Grade) | +-----------+------------+ | 计算机 | 98 | | 英语 | 94 | | 中文 | 95 | +-----------+------------+
-
查询李四的考试科目(c_name)和考试成绩(grade)
mysql> select C_name,Grade from score -> where Stu_id = (select Id from student where Name = '李四'); +-----------+-------+ | C_name | Grade | +-----------+-------+ | 计算机 | 70 | | 英语 | 92 | +-----------+-------+
-
用连接的方式查询所有学生的信息和考试信息
mysql> select * from student,score where student.Id = score.Stu_id; +-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+ | Id | Name | Sex | Birth | Department | Address | Id | Stu_id | C_name | Grade | +-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+ | 801 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 1 | 801 | 计算机 | 98 | | 801 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 2 | 801 | 英语 | 80 | | 802 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 3 | 802 | 计算机 | 65 | | 802 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 4 | 802 | 中文 | 88 | | 803 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 5 | 803 | 中文 | 95 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 6 | 804 | 计算机 | 70 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 7 | 804 | 英语 | 92 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 8 | 805 | 英语 | 94 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 9 | 805 | 计算机 | 90 | | 806 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 10 | 806 | 英语 | 85 | +-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
-
计算每个学生的总成绩
mysql> select student.Id,Name,sum(Grade) -> from student,score -> where student.Id = score.Stu_id -> group by id; +-----+-----------+------------+ | Id | Name | sum(Grade) | +-----+-----------+------------+ | 801 | 张老大 | 178 | | 802 | 张老二 | 153 | | 803 | 张三 | 95 | | 804 | 李四 | 162 | | 805 | 王五 | 184 | | 806 | 王六 | 85 | +-----+-----------+------------+
-
计算每个考试科目的平均成绩
mysql> select C_name,avg(Grade) -> from score -> group by C_name; +-----------+------------+ | C_name | avg(Grade) | +-----------+------------+ | 计算机 | 80.7500 | | 英语 | 87.7500 | | 中文 | 91.5000 | +-----------+------------+
-
查询计算机成绩低于 95 的学生信息
mysql> select * from student -> where Id in( -> select Stu_id from score -> where C_name = '计算机' and grade < 95); +-----+-----------+------+-------+------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+-----------+------+-------+------------+--------------------+ | 802 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | +-----+-----------+------+-------+------------+--------------------+
-
查询同时参加计算机和英语考试的学生的信息
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 = '英语'); +-----+-----------+------+-------+--------------+--------------------+ | Id | Name | Sex | Birth | Department | Address | +-----+-----------+------+-------+--------------+--------------------+ | 801 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | | 804 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | | 805 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
-
将计算机考试成绩按从高到低进行排序
mysql> select Name,Grade from score,student -> where score.Stu_id = student.Id and C_name = '计算机' -> order by Grade desc; +-----------+-------+ | Name | Grade | +-----------+-------+ | 张老大 | 98 | | 王五 | 90 | | 李四 | 70 | | 张老二 | 65 | +--------+-------+
-
从 student 表和 score 表中查询出学生的学号,然后合并查询结果
mysql> select Id from student union select Stu_id from score; +-----+ | Id | +-----+ | 801 | | 802 | | 803 | | 804 | | 805 | | 806 | +-----+
-
查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> select Name,Department,C_name,Grade from student,score -> where (Name like '张%' or name like '王%') and student.Id = score.Stu_id; +-----------+--------------+-----------+-------+ | Name | Department | C_name | Grade | +-----------+--------------+-----------+-------+ | 张老大 | 计算机系 | 计算机 | 98 | | 张老大 | 计算机系 | 英语 | 80 | | 张老二 | 中文系 | 计算机 | 65 | | 张老二 | 中文系 | 中文 | 88 | | 张三 | 中文系 | 中文 | 95 | | 王五 | 英语系 | 英语 | 94 | | 王五 | 英语系 | 计算机 | 90 | | 王六 | 计算机系 | 英语 | 85 | +-----------+--------------+-----------+-------+
-
查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql> select Name,2020-Birth,Department,C_name,Grade from student,score -> where address like '湖南%' and score.Stu_id = student.Id; +--------+------------+--------------+--------+-------+ | Name | 2020-Birth | Department | C_name | Grade | +--------+------------+--------------+--------+-------+ | 张三 | 30 | 中文系 | 中文 | 95 | | 王六 | 32 | 计算机系 | 英语 | 85 | +--------+------------+--------------+--------+-------+