一、创建各个数据表
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)