mysql学习笔记（6）数据查询part 1

一、创建各个数据表

1.学生表

create table student(stu_id int primary key,        #学生编号stu_name varchar(20) not null, #学生名称stu_sex varchar(10) not null,  #学生性别stu_birth datetime,            #学生出生日期class varchar(20)              #学生班级
);insert into student value(101,'李富金','男','1999-6-3','3');
insert into student value(102,'张三丰','男','1994-5-30','8');
insert into student value(103,'黄金菊','女','1997-6-5','4');
insert into student value(104,'关打掩','男','1991-3-3','1');
insert into student value(105,'隆个鼻','男','1996-6-7','3');
insert into student value(106,'胡旭涛','男','1995-1-2','9');
insert into student value(107,'包扈东','男','1996-6-3','3');
insert into student value(108,'戴颖鲍','女','1998-9-25','3');
insert into student value(109,'牛云丽','女','1995-2-7','5');
insert into student value(110,'穆续约','男','1994-7-13','1');
insert into student value(111,'库壹格','男','1998-4-16','2');mysql> select * from student;
+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    102 | 张三丰   | 男      | 1994-05-30 00:00:00 | 8     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    104 | 关打掩   | 男      | 1991-03-03 00:00:00 | 1     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    106 | 胡旭涛   | 男      | 1995-01-02 00:00:00 | 9     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
|    110 | 穆续约   | 男      | 1994-07-13 00:00:00 | 1     |
|    111 | 库壹格   | 男      | 1998-04-16 00:00:00 | 2     |
+--------+----------+---------+---------------------+-------+
11 rows in set (0.00 sec)

2.课程表

create table course(cour_id int primary key,          #课程编号cour_name varchar(20) not null,   #课程名称tch_id int not null,              #教师编号foreign key (tch_id) references teacher (tch_id)  #创建外键
);insert into course value(301,'语文','201');
insert into course value(302,'数学','202');
insert into course value(303,'英语','203');
insert into course value(304,'计算机','204');
insert into course value(305,'信号与系统','201');
insert into course value(306,'思政','202');
insert into course value(307,'java课程设计','202');
insert into course value(308,'数据结构','203');
insert into course value(309,'通信原理','204');
insert into course value(310,'数字信号处理','201');mysql> insert into course value(311,'历史','206');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`selecttest`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tch_id`) REFERENCES `teacher` (`tch_id`))mysql> select * from course;
+---------+--------------+--------+
| cour_id | cour_name    | tch_id |
+---------+--------------+--------+
|     301 | 语文         |    201 |
|     302 | 数学         |    202 |
|     303 | 英语         |    203 |
|     304 | 计算机       |    204 |
|     305 | 信号与系统   |    201 |
|     306 | 思政         |    202 |
|     307 | java课程设计 |    202 |
|     308 | 数据结构     |    203 |
|     309 | 通信原理     |    204 |
|     310 | 数字信号处理 |    201 |
+---------+--------------+--------+
10 rows in set (0.00 sec)

3.教师表

create table teacher(tch_id int primary key,          #教师编号tch_name varchar(20) not null,   #教师姓名tch_sex varchar(10) not null,    #教师性别tch_birth datetime,              #教师出生日期porf varchar(20),                #教师职称department varchar(20) not null  #教师住所
);insert into teacher value(201,'王尼玛','男','1988-1-30','讲师','计算机系');
insert into teacher value(202,'赵铁蛋','男','1978-12-10','副教授','计算机系');
insert into teacher value(203,'张全蛋','男','1967-5-14','教授','电子信息系');
insert into teacher value(204,'韦东东','女','1990-6-3','讲师','电气工程系');mysql> select * from teacher;
+--------+----------+---------+---------------------+--------+-------------+
| tch_id | tch_name | tch_sex | tch_birth           | porf   | department  |
+--------+----------+---------+---------------------+--------+-------------+
|    201 | 王尼玛   | 男      | 1988-01-30 00:00:00 | 讲师   | 江湖大道101 |
|    202 | 赵铁蛋   | 男      | 1978-12-10 00:00:00 | 副教授 | 风云大道11  |
|    203 | 张全蛋   | 男      | 1967-05-14 00:00:00 | 教授   | 刘金路101   |
|    204 | 韦东东   | 女      | 1990-06-03 00:00:00 | 讲师   | 江海路1852  |
+--------+----------+---------+---------------------+--------+-------------+
4 rows in set (0.00 sec)

删除数据表

drop table teacher;

4.成绩表

create table score(stu_id int not null,              #学生编号cour_id int not null,             #课程编号degree decimal,                   #成绩foreign key (stu_id) references student (stu_id),    #创建外键foreign key (cour_id) references course (cour_id),   #创建外键primary key (stu_id,cour_id)      #创建联合主键
);mysql> show tables;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course               |
| score                |
| student              |
| teacher              |
+----------------------+
4 rows in set (0.00 sec)insert into score value(101,301,'69');
insert into score value(101,302,'63');
insert into score value(101,303,'67');
insert into score value(102,301,'60');
insert into score value(102,302,'66');
insert into score value(102,303,'85');
insert into score value(103,301,'79');
insert into score value(103,302,'90');
insert into score value(103,303,'85');
insert into score value(104,301,'12');
insert into score value(104,302,'58');
insert into score value(104,303,'46');
insert into score value(105,301,'75');
insert into score value(105,302,'98');
insert into score value(105,303,'87');mysql> select * from score;
+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    101 |     301 |     69 |
|    101 |     302 |     63 |
|    101 |     303 |     67 |
|    102 |     301 |     60 |
|    102 |     302 |     66 |
|    102 |     303 |     85 |
|    103 |     301 |     79 |
|    103 |     302 |     90 |
|    103 |     303 |     85 |
|    104 |     301 |     12 |
|    104 |     302 |     58 |
|    104 |     303 |     46 |
|    105 |     301 |     75 |
|    105 |     302 |     98 |
|    105 |     303 |     87 |
+--------+---------+--------+
15 rows in set (0.00 sec)

二、查询练习part 1

例1.查询student表中的所有记录

select * from student;   # * 代表所有字段的意思mysql> select * from student;
+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    102 | 张三丰   | 男      | 1994-05-30 00:00:00 | 8     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    104 | 关打掩   | 男      | 1991-03-03 00:00:00 | 1     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    106 | 胡旭涛   | 男      | 1995-01-02 00:00:00 | 9     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
|    110 | 穆续约   | 男      | 1994-07-13 00:00:00 | 1     |
|    111 | 库壹格   | 男      | 1998-04-16 00:00:00 | 2     |
+--------+----------+---------+---------------------+-------+
11 rows in set (0.00 sec)

例2.查询指定列（查询student表中的所有记录的stu_name、stu_sex和class列）

select stu_name,stu_sex,class from student;mysql> select stu_name,stu_sex,class from student;
+----------+---------+-------+
| stu_name | stu_sex | class |
+----------+---------+-------+
| 李富金   | 男      | 3     |
| 张三丰   | 男      | 8     |
| 黄金菊   | 女      | 4     |
| 关打掩   | 男      | 1     |
| 隆个鼻   | 男      | 3     |
| 胡旭涛   | 男      | 9     |
| 包扈东   | 男      | 3     |
| 戴颖鲍   | 女      | 3     |
| 牛云丽   | 女      | 5     |
| 穆续约   | 男      | 1     |
| 库壹格   | 男      | 2     |
+----------+---------+-------+
11 rows in set (0.00 sec)

例3.查询教师所有的单位及不重复的department列（distinct）

select department from teacher;+------------+
| department |
+------------+
| 计算机系   |
| 计算机系   |
| 电子信息系 |
| 电气工程系 |
+------------+
4 rows in set (0.00 sec)select distinct department from teacher;  #用于排重+------------+
| department |
+------------+
| 计算机系   |
| 电子信息系 |
| 电气工程系 |
+------------+
3 rows in set (0.01 sec)

例4.查询区间（score的分数degree在60~80之间的学生）

#法一：
select * from score where degree between 60 and 80;+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    101 |     301 |     69 |
|    101 |     302 |     63 |
|    101 |     303 |     67 |
|    102 |     301 |     60 |
|    102 |     302 |     66 |
|    103 |     301 |     79 |
|    105 |     301 |     75 |
+--------+---------+--------+
7 rows in set (0.00 sec)select stu_id from score where degree between 60 and 80;+--------+
| stu_id |
+--------+
|    101 |
|    101 |
|    101 |
|    102 |
|    102 |
|    103 |
|    105 |
+--------+
7 rows in set (0.00 sec)

# 法二：运算符比较
select * from score where degree > 60 and degree < 80;+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    101 |     301 |     69 |
|    101 |     302 |     63 |
|    101 |     303 |     67 |
|    102 |     302 |     66 |
|    103 |     301 |     79 |
|    105 |     301 |     75 |
+--------+---------+--------+
6 rows in set (0.00 sec)

例5.记录成绩表中成绩为85,86,88的学生id

#法一：
select stu_id from score where degree = 85 or degree = 86 or degree = 88;
#法二：
select stu_id from score where degree in (85,86,88);+--------+
| stu_id |
+--------+
|    102 |
|    103 |
+--------+

例6.查询student中3班或者性别为女的同学对象

select * from student where class = '3' or stu_sex = '女';+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
+--------+----------+---------+---------------------+-------+
6 rows in set (0.00 sec)

例7.以class降序|升序查询student表的所有记录

select * from student order by class asc;  #升序
+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    104 | 关打掩   | 男      | 1991-03-03 00:00:00 | 1     |
|    110 | 穆续约   | 男      | 1994-07-13 00:00:00 | 1     |
|    111 | 库壹格   | 男      | 1998-04-16 00:00:00 | 2     |
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
|    102 | 张三丰   | 男      | 1994-05-30 00:00:00 | 8     |
|    106 | 胡旭涛   | 男      | 1995-01-02 00:00:00 | 9     |
+--------+----------+---------+---------------------+-------+
11 rows in set (0.00 sec)select * from student order by class desc;  #降序+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    106 | 胡旭涛   | 男      | 1995-01-02 00:00:00 | 9     |
|    102 | 张三丰   | 男      | 1994-05-30 00:00:00 | 8     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    111 | 库壹格   | 男      | 1998-04-16 00:00:00 | 2     |
|    104 | 关打掩   | 男      | 1991-03-03 00:00:00 | 1     |
|    110 | 穆续约   | 男      | 1994-07-13 00:00:00 | 1     |
+--------+----------+---------+---------------------+-------+
11 rows in set (0.00 sec)select * from student order by class;  #默认升序+--------+----------+---------+---------------------+-------+
| stu_id | stu_name | stu_sex | stu_birth           | class |
+--------+----------+---------+---------------------+-------+
|    104 | 关打掩   | 男      | 1991-03-03 00:00:00 | 1     |
|    110 | 穆续约   | 男      | 1994-07-13 00:00:00 | 1     |
|    111 | 库壹格   | 男      | 1998-04-16 00:00:00 | 2     |
|    101 | 李富金   | 男      | 1999-06-03 00:00:00 | 3     |
|    105 | 隆个鼻   | 男      | 1996-06-07 00:00:00 | 3     |
|    107 | 包扈东   | 男      | 1996-06-03 00:00:00 | 3     |
|    108 | 戴颖鲍   | 女      | 1998-09-25 00:00:00 | 3     |
|    103 | 黄金菊   | 女      | 1997-06-05 00:00:00 | 4     |
|    109 | 牛云丽   | 女      | 1995-02-07 00:00:00 | 5     |
|    102 | 张三丰   | 男      | 1994-05-30 00:00:00 | 8     |
|    106 | 胡旭涛   | 男      | 1995-01-02 00:00:00 | 9     |
+--------+----------+---------+---------------------+-------+
11 rows in set (0.00 sec)

例8.以cour_id升序，degree降序查询score表中的所有记录

select * from score order by cour_id asc,degree desc;+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    103 |     301 |     79 |
|    105 |     301 |     75 |
|    101 |     301 |     69 |
|    102 |     301 |     60 |
|    104 |     301 |     12 |
|    105 |     302 |     98 |
|    103 |     302 |     90 |
|    102 |     302 |     66 |
|    101 |     302 |     63 |
|    104 |     302 |     58 |
|    105 |     303 |     87 |
|    102 |     303 |     85 |
|    103 |     303 |     85 |
|    101 |     303 |     67 |
|    104 |     303 |     46 |
+--------+---------+--------+
15 rows in set (0.00 sec)

例9.查询4班的人数

select count(*) from student where class = '4';+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

例10.查询score表中分数最高的学生的学生学号与课程编号

select stu_id,cour_id from score where degree = 
(select max(degree) from score);+--------+---------+
| stu_id | cour_id |
+--------+---------+
|    105 |     302 |
+--------+---------+
1 row in set (0.01 sec)

#1.找到最高分
select max(degree) from score
#2.找最高分的stu_id course_id
select stu_id,cour_id from score where degree =

#排序法
select stu_id,cour_id,degree from score order by degree; +--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    104 |     301 |     12 |
|    104 |     303 |     46 |
|    104 |     302 |     58 |
|    102 |     301 |     60 |
|    101 |     302 |     63 |
|    102 |     302 |     66 |
|    101 |     303 |     67 |
|    101 |     301 |     69 |
|    105 |     301 |     75 |
|    103 |     301 |     79 |
|    102 |     303 |     85 |
|    103 |     303 |     85 |
|    105 |     303 |     87 |
|    103 |     302 |     90 |
|    105 |     302 |     98 |
+--------+---------+--------+
15 rows in set (0.00 sec)select stu_id,cour_id,degree from score order by degree limit 0,1;+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    104 |     301 |     12 |
+--------+---------+--------+
1 row in set (0.00 sec)select stu_id,cour_id,degree from score order by degree desc limit 0,1;#第一个数字表示从多少开始，第二个数字表示查多少条。+--------+---------+--------+
| stu_id | cour_id | degree |
+--------+---------+--------+
|    105 |     302 |     98 |
+--------+---------+--------+
1 row in set (0.00 sec)