一、准备数据
1、新建数据库selectTest
mysql> create database selectTest;
mysql> use selectTest;
2、新建学生表student
--创建学生表
mysql> create table student(
-> s_no varchar(20) primary key comment'学生学号',
-> s_name varchar(20) not null comment'学生姓名',
-> s_sex varchar(10) not null comment'学生性别',
-> s_birthday datetime comment'学生生日',
-> s_class varchar(20) comment'学生所在班级'
-> );
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no | varchar(20) | NO | PRI | NULL | |
| s_name | varchar(20) | NO | | NULL | |
| s_sex | varchar(10) | NO | | NULL | |
| s_birthday | datetime | YES | | NULL | |
| s_class | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3、课程表
--创建课程表
mysql> create table course(
-> c_no varchar(20) primary key comment'课程号',
-> c_name varchar(20) not null comment'课程名称',
-> t_no varchar(20) not null comment'教师编号-外键关联teacher',
-> foreign key(t_no) reference teacher(t_no)
-> );
//这时报错,因为没有创建教师表
//创建教师表后,再查看
mysql> desc course;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no | varchar(20) | NO | PRI | NULL | |
| c_name | varchar(20) | NO | | NULL | |
| t_no | varchar(20) | NO | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
4、教师表
mysql> create table teacher(
-> t_no varchar(20) primary key comment'教师编号',
-> t_name varchar(20) not null comment'教师姓名',
-> t_sex varchar(20) not null comment'教师性别',
-> t_birthday datetime comment'教师生日',
-> t_rof varchar(20) not null comment'教师职称',
-> t_depart varchar(20) not null comment'教师所在部门'
-> );
mysql> desc teacher;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no | varchar(20) | NO | PRI | NULL | |
| t_name | varchar(20) | NO | | NULL | |
| t_sex | varchar(20) | NO | | NULL | |
| t_birthday | datetime | YES | | NULL | |
| t_rof | varchar(20) | NO | | NULL | |
| t_depart | varchar(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
5、成绩表
mysql> create table score(
-> s_no varchar(20) not null comment'成绩表编号-依赖于学生学号s_no',
-> c_no varchar(20) not null comment'课程号-依赖于课程表的c_no',
-> sc_degree decimal,
-> foreign key(s_no) references student(s_no),
-> foreign key(c_no) references course(c_no),
-> primary key(s_no,c_no)
//复合主键,一个学生号对应多个课程号,而某一个学生的某一个课程号就不能有重复,只能有一个成绩
//而之前只要学生表只要学生的编号定了,其他的字段都是定了的,随之变化。
-> );
mysql> desc score;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no | varchar(20) | NO | PRI | NULL | |
| c_no | varchar(20) | NO | PRI | NULL | |
| sc_degree | decimal(10,0) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
6、添加数据
--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');
--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');
--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');
--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');
mysql> select * from student;
+------+-----------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+-----------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+-----------+-------+---------------------+---------+
mysql> select * from teacher;
+------+--------+-------+---------------------+-----------+-----------------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
+------+--------+-------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+------+--------+-------+---------------------+-----------+-----------------+
mysql> select * from course;
+-------+-----------------+------+
| c_no | c_name | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+------+
mysql> select * from score;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+-----------+
二、查询练习
1、查询student中的所有记录
select * from student;
2、查询student中的name,sex,class列
mysql> select s_name,s_sex,s_class from student;
+-----------+-------+---------+
| s_name | s_sex | s_class |
+-----------+-------+---------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆军 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
+-----------+-------+---------+
3、查询教师所有的单位-即不重复的depart列
dintinct排除重复。
mysql> select distinct t_depart from teacher;
+-----------------+
| t_depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
4、查询score表中成绩在60-80之间的所有记录
查询区间between ... and ...
,左右边界是包含的。
mysql> select * from score where sc_degree between 60 and 80;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+------+-------+-----------+
也可以写成select * from score where degree > 60 and degree < 80;
5、查询score表中成绩为85,86,88的记录
表示或者关系的查询in()
mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+------+-------+-----------+
6、查询student表中"95031"班或性别为“女”的同学记录。
or表示或者
mysql> select * from student where s_class='95031' or s_sex='女';
+------+-----------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+-----------+-------+---------------------+---------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+-----------+-------+---------------------+---------+
7、以class降序查询student表的所有记录
升序asc,降序desc,不写默认升序。
mysql> select * from student order by s_class desc; //降序
+------+-----------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+-----------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+-----------+-------+---------------------+---------+
升序select * from student order by s_class asc;
,其中asc不写默认升序。
8、以c_no升序、sc_degree降序查询score表中的所有记录
mysql> select * from score order by c_no asc,sc_degree desc;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+------+-------+-----------+
9、查询95031班的人数
统计count
mysql> select count(*) from student where s_class='95031';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
10、查询score中最高分的学生学号和课程号(子查询或者排序)
mysql> select s_no,c_no from score where sc_degree=(select max(sc_degree) from score);
+------+-------+
| s_no | c_no |
+------+-------+
| 103 | 3-105 |
+------+-------+
最高分有多个时会有问题,可以写成SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;
。其中limit x,y表示从第x条数据开始,查询y条。
并且如果只有select max(sc_degree) from score
时,是可以展示多个相同的最高分的。
敏而好学,不耻下问,是以谓之文也。