mysql
第一部分：准备数据
1 创建学生表
create table student (
student_number varchar(20) primary key,
student_name varchar(20) not null,
student_sex varchar(20) not null default "men",
check(student_sex='men' OR student_sex='women'),
check(student_number>99 AND student_number<200),
student_birthday datetime,
class varchar(20)
);
扩展（check约束）
student_number 大于99，小于200
student_sex 值为 men 或者 women
2 插入数据
insert into student values('101','zhao-san','women','1992-01-01','95034');
insert into student values('102','wan-san','men','1993-01-01','95033');
insert into student values('103','sun-san','women','1994-01-01','95033');
insert into student values('104','li-san','men','1996-01-01','95033');
insert into student values('105','zhou-san','women','1991-04-01','95033');
insert into student values('106','fen-san','women','1991-11-01','95033');
insert into student values('107','chen-san','women','1994-06-02','95033');
insert into student values('108','chu-san','women','1951-06-01','95033');
3 创建教师表
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
);
4 插入数据
insert into teacher values('201','kobe','men','1978-4-1','legend','basketball3');
insert into teacher values('202','james','men','1988-4-4','actor','basketball2');
insert into teacher values('203','jordan','women','1988-4-4','legend','basketball4');
insert into teacher values('204','durant','women','1981-4-4','legends','basketball1');
insert into teacher values('205','paul','men','1983-11-4','defense','basketball5');
insert into teacher values('206','yao','men','1983-11-4','defense','basketball7');
insert into teacher values('207','yi','men','1983-11-4','defense','basketball8');
insert into teacher values('208','allen','men','1983-11-4','defense','basketball9');
5 创建课程表
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)
);
6 插入数据
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');
insert into course values('303-6','auto','206');
7 创建成绩表
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)
);
# 联合主键: primary key(student_number,course_number)
8 插入数据
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');
第二部分 : 查询练习
1 查询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)
2 查询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)
3 查询教师表：查询教师所有不重复的单位（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)
4 查询score表中成绩在80-90之间的所有记录
（1）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)
# 另一种方式：直接运算符比较
（2）select * from score where degree > 80 and degree < 90;
5 查询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)
6 查询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)
7 以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;
8 以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)
9 查询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)
10 查询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)
