mysql 查询语句练习

已于 2022-03-17 19:09:59 修改
阅读量651 收藏 7
点赞数 1
分类专栏： mysql 文章标签： mysql 数据库
于 2021-06-20 00:53:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wang_ze6456/article/details/118061068
版权

Mysql


在这里插入图片描述

第一部分：准备数据（创建数据库及数据表）

1 创建数据库

create database test character set utf8;

# 修改数据库密码
use mysql;
UPDATE user SET password=PASSWORD('raspberry') WHERE user='root';
flush privileges;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'raspberry' WITH GRANT OPTION;
flush privileges;

2 选择数据库

use test;

3 创建学生表

create table student (
    student_number varchar(20) primary key check(student_number>99 AND student_number<200),
    student_name varchar(20) not null,
    student_sex varchar(20) not null default "男",
    check(student_sex='男' OR student_sex='女'),
    student_birthday datetime,
    class varchar(20)
);

4 学生表插入数据

insert into student values('101','安琪拉','女','1992-01-01','95034');
insert into student values('102','白起','男','1993-01-01','95033');
insert into student values('103','老夫子','女','1994-01-01','95035');
insert into student values('104','李白','男','1996-01-01','95033');
insert into student values('105','武则天','女','1991-04-01','95036');
insert into student values('106','蔡文姬','女','1991-11-01','95033');
insert into student values('107','马可波罗','男','1994-06-02','95032');
insert into student values('108','花木兰','女','1951-06-01','95033');
insert into student values('10','小乔','女','1951-06-01','95031');

5 创建教师表

create table teacher(
    teacher_number varchar(20) primary key,
    teacher_name varchar(20) not null,
    teacher_sex varchar(20) not null,
    teacher_birthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
);

6 教师表插入数据

insert into teacher values('201','盲僧','男','1978-4-1','legend','建筑系');
insert into teacher values('202','妖姬','男','1988-4-4','actor','体育系');
insert into teacher values('203','劫','女','1988-4-4','legend','会计系');
insert into teacher values('204','亚索','女','1981-4-4','legends','自动化系');
insert into teacher values('205','卡莎','男','1983-11-4','defense','英语系');
insert into teacher values('206','卡莉斯塔','女','1983-11-4','defense','软件系');
insert into teacher values('207','牛头','男','1983-11-4','defense','大数据系');
insert into teacher values('208','小炮','女','1983-11-4','defense','测试系');
insert into teacher values('209','德莱文','女','1983-11-4','defense','测试系');
insert into teacher values('210','狐狸','女','1983-11-4','defense','测试系');
insert into teacher values('211','盖伦','女','1983-11-4','defense','测试系');

7 创建课程表

create table course(
    course_number varchar(20) primary key,
    course_name varchar(20) not null,
    teacher_number varchar(20) not null,
    foreign key (teacher_number) references teacher(teacher_number)
);

8 课程表插入数据

insert into course values('303-1','english','201');
insert into course values('303-2','math','202');
insert into course values('303-3','python','203');
insert into course values('303-4','java','204');
insert into course values('303-5','c++','205');

9 创建成绩表

create table score(
    student_number varchar(20) not null,
    course_number varchar(20) not null,
    degree decimal,
    foreign key (student_number) references student(student_number),
    foreign key (course_number) references course(course_number),
    primary key(student_number,course_number)
);

10 成绩表插入数据

insert into score values('101','303-3','88');
insert into score values('102','303-4','89');
insert into score values('103','303-3','87');
insert into score values('104','303-3','86');
insert into score values('107','303-5','99');
insert into score values('107','303-2','92');
insert into score values('105','303-1','100');
insert into score values('106','303-1','82');
insert into score values('102','303-2','87');
insert into score values('101','303-6','95');

------------------------------------------------------------------------------------------------

第二部分：查询练习（数据表查询）

11 查询student表中所有记录

select * from student;

mysql> select * from student;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.01 sec)

12 查询select 表中所有记录的student_number，student_sex及class字段（列）。

select student_name,student_sex,class from student;

mysql> select student_name,student_sex,class from student;
+--------------+-------------+-------+
| student_name | student_sex | class |
+--------------+-------------+-------+
| zhao-san     | women       | 95034 |
| wan-san      | men         | 95033 |
| sun-san      | women       | 95033 |
| li-san       | men         | 95033 |
| zhou-san     | women       | 95033 |
| fen-san      | women       | 95033 |
| chen-san     | women       | 95033 |
| chu-san      | women       | 95033 |
+--------------+-------------+-------+
8 rows in set (0.00 sec)

13 查询教师表：教师所有的单位不重复的（depart）(去重)

select distinct depart from teacher;

distinct 排除重复

mysql> select distinct depart from teacher;
+-------------+
| depart      |
+-------------+
| basketball3 |
| basketball2 |
| basketball4 |
| basketball1 |
| basketball5 |
| basketball7 |
| basketball8 |
| basketball9 |
+-------------+
8 rows in set (0.00 sec)

14 查询score表中成绩在80-90之间的所有记录

select * from score where degree between 80 and 90;

查询区间 between 。。。 and 。。。
查询结果
mysql> select * from score where degree between 80 and 90;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 102            | 303-4         |     89 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 106            | 303-1         |     82 |
+----------------+---------------+--------+
6 rows in set (0.00 sec)

# 另一种方式：直接运算符比较
select * from score where degree > 80 and degree < 90;

15 查询score表中成绩为 86、87、88的所有记录

表示或者的关系的 in

select * from score where degree in(86,87,88);

mysql> select * from score where degree in(86,87,88);
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
+----------------+---------------+--------+
4 rows in set (0.00 sec)

16 查询student表中为’95034’班或者 student_sex为’women’的所有记录

or表示或者

select * from student where class='95034' or student_sex='women';

mysql> select * from student where class='95034' or student_sex='women';
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
6 rows in set (0.00 sec)

17 以class字段为标准按照顺序查询student记录

降序查询
select * from student  order by class desc;

mysql> select * from student  order by class desc;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.00 sec)


升序查询(默认升序)
select * from student order by class;
select * from student order by class asc;

18 以course_number升序，degree降序查询score表中所有记录

select * from score order by course_number asc,degree desc;

查询结果
mysql> select * from score order by course_number asc,degree desc;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 105            | 303-1         |    100 |
| 106            | 303-1         |     82 |
| 107            | 303-2         |     92 |
| 102            | 303-2         |     87 |
| 101            | 303-3         |     88 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 102            | 303-4         |     89 |
| 107            | 303-5         |     99 |
| 101            | 303-6         |     95 |
+----------------+---------------+--------+
10 rows in set (0.00 sec)

19 查询student表中 '95034’班的人数

统计 count
select count(*) from student where class='95034';

mysql> select count(*) from student where class='95034';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

20 查询score表中的最高分的学生学号及课程号(子查询或者排序)

最大值  max

select student_number,course_number from score where degree=(select max(degree) from score);

mysql> select student_number,course_number from score where degree=(select max(degree) from score);
+----------------+---------------+
| student_number | course_number |
+----------------+---------------+
| 105            | 303-1         |
+----------------+---------------+
1 row in set (0.00 sec)


# 倒序
select student_number,course_number,degree from score order by degree desc;
一条数据（0为第一个值）
select student_number,course_number,degree from score order by degree desc limit 0,1;
两条数据
select student_number,course_number,degree from score order by degree desc limit 0,2;

mysql> select student_number,course_number,degree from score order by degree desc limit 0,2;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 105            | 303-1         |    100 |
| 107            | 303-5         |     99 |
+----------------+---------------+--------+
2 rows in set (0.00 sec)


# 正序(默认)
select student_number,course_number,degree from score order by degree asc;

21 查询每门课的平均成绩

通过avg函数
select avg(degree) from score where course_number='303-3';

查询一门课的平均成绩
mysql> select avg(degree) from score where course_number='303-3';
+-------------+
| avg(degree) |
+-------------+
|     87.0000 |
+-------------+
1 row in set (0.00 sec)

---------------------------------------------
查询每门课的平均成绩

select avg(degree) from score group by course_number;

mysql> select avg(degree) from score group by course_number;
+-------------+
| avg(degree) |
+-------------+
|     91.0000 |
|     89.5000 |
|     87.0000 |
|     89.0000 |
|     99.0000 |
+-------------+
5 rows in set (0.00 sec)


group by 分组

select course_number,avg(degree) from score group by course_number;

mysql> select course_number,avg(degree) from score group by course_number;
+---------------+-------------+
| course_number | avg(degree) |
+---------------+-------------+
| 303-1         |     91.0000 |
| 303-2         |     89.5000 |
| 303-3         |     87.0000 |
| 303-4         |     89.0000 |
| 303-5         |     99.0000 |
+---------------+-------------+
5 rows in set (0.00 sec)

22 查询练习：查询score表中至少有两名学生选修的并以3开头的课程

select course_number,avg(degree),count(*) from score
group by course_number #分组
having count(course_number)>=2 and course_number #  条件查询
like '3%'; # 模糊查询

mysql> select course_number,avg(degree),count(*) from score group by course_number having count(course_number)>=2 and course_number like '3%';
+---------------+-------------+----------+
| course_number | avg(degree) | count(*) |
+---------------+-------------+----------+
| 303-1         |     91.0000 |        2 |
| 303-2         |     89.5000 |        2 |
| 303-3         |     87.0000 |        3 |
+---------------+-------------+----------+


总结：
group by 跟聚合函数一起使用
现在需要把这些重复的数据删除掉，使用到的语句就是Group By来完成
作用是先把table按列分组（比如说group by name,是按name分组），然后各个group分别使用聚合函数，然后得出结果。

23 查询分数大于70，小于90的student_number列

select student_number,degree from score where degree>70 and degree <90
select student_number,degree from score where degree between 70 and 90;


mysql> select student_number,degree from score where degree>70 and degree <90;
+----------------+--------+
| student_number | degree |
+----------------+--------+
| 101            |     88 |
| 102            |     87 |
| 102            |     89 |
| 103            |     87 |
| 104            |     86 |
| 106            |     82 |
+----------------+--------+
6 rows in set (0.00 sec)

24 多表查询：查询所有学生的 student_number,course_number,degree列（student和score表）

select student_name,course_number,degree from student,score where student.student_number=score.student_number;

mysql> select student_name,course_number,degree from student,score where student.student_number=score.student_number;
+--------------+---------------+--------+
| student_name | course_number | degree |
+--------------+---------------+--------+
| zhao-san     | 303-3         |     88 |
| wan-san      | 303-2         |     87 |
| wan-san      | 303-4         |     89 |
| sun-san      | 303-3         |     87 |
| li-san       | 303-3         |     86 |
| zhou-san     | 303-1         |    100 |
| fen-san      | 303-1         |     82 |
| chen-san     | 303-2         |     92 |
| chen-san     | 303-5         |     99 |
+--------------+---------------+--------+
9 rows in set (0.00 sec)

25 多表查询：查询所有学生的student_number,course_name和degree列

select student_number,course_name,degree from course,score where course.course_number=score.course_number;

mysql> select student_number,course_name,degree from course,score where course.course_number=score.course_number;
+----------------+-------------+--------+
| student_number | course_name | degree |
+----------------+-------------+--------+
| 101            | python      |     88 |
| 102            | math        |     87 |
| 102            | java        |     89 |
| 103            | python      |     87 |
| 104            | python      |     86 |
| 105            | english     |    100 |
| 106            | english     |     82 |
| 107            | math        |     92 |
| 107            | c++         |     99 |
+----------------+-------------+--------+
9 rows in set (0.01 sec)

26 三表查询：查询所有学生的student_number,course_name和degree列

多表之间的联系就是通过共同字段的相等来建立的

select student.student_number,student_name as sn,course_name,degree from student,course,score where student.student_number=score.student_number and course.course_number=score.course_number;

mysql> select student.student_number,student_name,course_name,degree from student,course,score where student.student_number=score.student_number and course.course_number=score.course_number;
+----------------+--------------+-------------+--------+
| student_number | student_name | course_name | degree |
+----------------+--------------+-------------+--------+
| 101            | zhao-san     | python      |     88 |
| 102            | wan-san      | math        |     87 |
| 102            | wan-san      | java        |     89 |
| 103            | sun-san      | python      |     87 |
| 104            | li-san       | python      |     86 |
| 105            | zhou-san     | english     |    100 |
| 106            | fen-san      | english     |     82 |
| 107            | chen-san     | math        |     92 |
| 107            | chen-san     | c++         |     99 |
+----------------+--------------+-------------+--------+
9 rows in set (0.00 sec)


as 别名
student.student_number student表中的student_number列

select student_number,course_name,degree from student,course,score;
报错：无法辨认出是哪个表的student_number
ERROR 1052 (23000): Column 'student_number' in field list is ambiguous

27 查询 95033班 学生每门课的平均分

查询 班级为95033的学生学号
select student_number from student where class=95033;

select * from score where student_number in (select student_number from student where class=95033);

mysql> select * from score where student_number in (select student_number from student where class=95033);
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 102            | 303-2         |     87 |
| 102            | 303-4         |     89 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 105            | 303-1         |    100 |
| 106            | 303-1         |     82 |
| 107            | 303-2         |     92 |
| 107            | 303-5         |     99 |
+----------------+---------------+--------+
8 rows in set (0.00 sec)



select student_number,avg(degree)
from score
where student_number in (select student_number from student where class=95033)
group by student_number;

# group by 目的是分组去重

mysql> select student_number,avg(degree)
    -> from score
    -> where student_number in (select student_number from student where class=95033)
    -> group by student_number;
+----------------+-------------+
| student_number | avg(degree) |
+----------------+-------------+
| 102            |     88.0000 |
| 103            |     87.0000 |
| 104            |     86.0000 |
| 105            |    100.0000 |
| 106            |     82.0000 |
| 107            |     95.5000 |
+----------------+-------------+
6 rows in set (0.00 sec)
非运不可自通
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL查询语句练习
小白
07-11 128
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');| 102 | 1006 | 2017-05-05 | 4500.00 | 党员 | 孙八 | 1996-09-02 | 女 | 29 |
史上最全MySql查询练习
????allubiba_H的博客
08-01 2812
本文章拥有MySQL中使用的所有查询操作
MySQL数据库常用查询（带练习示例），java技术框架
2401_83977385的博客
04-09 670
较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新**
MySql查询之单表查询 --附练习素材
UNIQUE_XIAOXI的博客
11-15 3228
目标： 熟练掌握SQL查询 DML ： insert update delete//插入：insert insert into table_name(col1, col2,...) values(val1, val2, ...); insert into table_name(col1, col2,...) values(val1, val2, ...), (val1, val2, ...)
MySQL查询语句练习题，测试基本够用了
LYX_WIN
11-11 635
【代码】MySQL查询语句练习题，测试基本够用了。
Mysql的基本查询练习
JJR_YZ的博客
09-17 350
mysql基本查询练习
mysql查询语句练习
05-10
查询每个雇员的编号、姓名、职位。 02.查询出所有的职位，使用DISTINCT消除掉显示的重复行记录。 03.计算每个雇员的编号、姓名、基本年薪。年薪=(工资+奖金)*12，(使用IFNULL函数判断NULL）... ...）
MySQL语句的查询练习
03-28
45道基本题
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
练习使用mysql查询语句 50题.zip
最新发布
05-23
本资料包“练习使用mysql查询语句 50题.zip”显然是为了帮助用户提升在MySQL中的查询技能，特别是SQL选择查询（SELECT语句）的应用。以下是根据标题和描述可能包含的一些关键知识点，以及可能的实践题目。 1. **...
mysql查询语句汇总.zip
05-23
"mysql查询语句汇总.zip"这个压缩包很可能包含了关于MySQL查询语句的各种示例和讲解，帮助用户更好地理解和掌握SQL语言。 在MySQL中，查询语句是用于从数据库中获取数据的核心部分，主要由SELECT关键字开始。以下是...
mysql 多表联接查询练习
10-31
mysql,多表联接查询练习,mysql,多表联接内联,外联练习。 mysql,多表联接查询练习,mysql,多表联接内联,外联练习。
MySQL总结：02_条件查询
cai_ing的博客
10-04 963
条件查询 语法： select 查询列表 from 表名 where 筛选条件; 分类： 一、按条件表达式筛选 简单条件运算符： > < = != <> >= <= #案例1：查询工资>12000的员工信息 SELECT * FROM employees WHERE salary>12000; #案例2：查询部门编号不等于90号的员工名和部门编号 SELECT last_name, department_id
mysql查询练习实例
weixin_50614575的博客
05-25 1127
每个测试工程师都需要掌握最基本的数据库增删改查，下面给初学者一个简单的案例，跟着案例练习起来，一定会让你对数据库的操作更加容易。
MySQL批量统计数据库中的数据量
x²＋(y－√³x²)²＝1的博客
12-10 1456
一、查询一个数据库中每张表的数据量： 1、执行下面的SQL语句，是为了拼接一个查询语句 select concat( 'select "', TABLE_name, '", count(*) from ', TABLE_SCHEMA, '.', TABLE_name, ' union all' ) from information_schema.tables where TABLE_SCHEMA='数据库名称'; 返回这样的结果： 2、将返回的结果
Mysql数据库查询练习题~~入门级别
xiaozhegaa的博客
06-03 2487
一、本文章目标：掌握基本的查询、嵌套子查询及连接查询。 面试开发岗位，无论是什么语言都会涉及到数据库查询的题目，这里收集关于数据库查询的某些例子，建议可以在面试之前稍微看看 资料分享，如果想要手动练习，可以导入下列资料中的数据库，答案也在里面了 点击打开链接   二、给出下面表的关系，写出题目要求的Sql语句 题目问题： 1．投影查询： l 查询Student表中所有记录的SN...
MySQL查询语句练习题
夜子的专栏
03-20 3568
Sutdent表的定义 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 Id 学号 INT(10) 是 否 是 是 是 Name
mysql查询语句练习题
08-23
以下是一些MySQL查询语句练习题的例子： 1. 查询学生表中所有学生的姓名和年龄： SELECT sname, sage FROM student; 2. 查询课程表中所有课程的名称和对应的教师姓名： SELECT cname, tname FROM course JOIN ...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值