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
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值