--学生表
--学号
--名字
--性别
--出生年月日
--所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbrithday datetime,
class varchar(20)
);
--教师表
--教师编号
--教师名字
--教师性别
--出生年月日
--职称
--所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbrithday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
--课程表
--课程号
--课程名称
--教师名称
--教师编号
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
--成绩表
--学号
--课程号
--成绩
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
--往数据表中添加数据
--学生信息
insert into student values('1','张三','男','1999-01-01','A1811');
insert into student values('2','李四','男','1999-02-01','A1812');
insert into student values('3','王五','男','1999-03-01','A1813');
insert into student values('4','赵柳','男','1999-04-01','A1811');
insert into student values('5','力气','男','1999-05-01','A1812');
insert into student values('6','张辉','男','1999-06-01','A1813');
insert into student values('7','钟会','女','1999-06-01','A1813');
--教师信息
insert into teacher values('101','李成','男','1987-09-24','讲师','电子工程');
insert into teacher values('102','胡成','男','1987-10-24','副教授','信息学院');
insert into teacher values('103','李艳丽','男','1987-11-24','教授','化环学院');
insert into teacher values('104','刘佳','男','1989-09-24','副教授','政法学院');
insert into teacher values('106','李虎','女','1987-09-24','讲师','电子工程');
--添加课程表信息
insert into course values('3-1','计算机理论','102');
insert into course values('3-2','数字电路','101');
insert into course values('3-3','高等数学','103');
insert into course values('3-4','概率论','104');
--成绩表
insert into score values('1','3-2','87');
insert into score values('2','3-3','87');
insert into score values('4','3-1','87');
insert into score values('6','3-4','87');
insert into score values('5','3-4','100');
insert into score values('3','3-4','89');
insert into score values('5','3-1','97');
--查询练习
--查询student表的所有记录
mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbrithday | class |
+-----+--------+------+---------------------+-------+
| 1 | 张三 | 男 | 1999-01-01 00:00:00 | A1811 |
| 2 | 李四 | 男 | 1999-02-01 00:00:00 | A1812 |
| 3 | 王五 | 男 | 1999-03-01 00:00:00 | A1813 |
| 4 | 赵柳 | 男 | 1999-04-01 00:00:00 | A1811 |
| 5 | 力气 | 男 | 1999-05-01 00:00:00 | A1812 |
| 6 | 张辉 | 男 | 1999-06-01 00:00:00 | A1813 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)
--查询student表中所记录的sname,ssex,class列
select sname,ssex,class from student;
mysql> select sname, ssex, class from student;
+--------+------+-------+
| sname | ssex | class |
+--------+------+-------+
| 张三 | 男 | A1811 |
| 李四 | 男 | A1812 |
| 王五 | 男 | A1813 |
| 赵柳 | 男 | A1811 |
| 力气 | 男 | A1812 |
| 张辉 | 男 | A1813 |
+--------+------+-------+
6 rows in set (0.00 sec)
--查询教师所有的单位
select depart from teacher;
mysql> select depart from teacher;
+--------------+
| depart |
+--------------+
| 电子工程 |
| 信息学院 |
| 化环学院 |
| 政法学院 |
| 电子工程 |
+--------------+
5 rows in set (0.00 sec)
我们发现其中有些重复的内容，那么如何排重呢？
--distinct 排重
select distinct depart from teacher;
mysql> select distinct depart from teacher;
+--------------+
| depart |
+--------------+
| 电子工程 |
| 信息学院 |
| 化环学院 |
| 政法学院 |
+--------------+
4 rows in set (0.00 sec)
--查询score表中成绩在80-90之间的所有记录
--查询区间between...and...
select * from score where degree between 80 and 90;
mysql> select * from score where degree between 80 and 90;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 1 | 3-2 | 87 |
| 2 | 3-3 | 87 |
| 4 | 3-1 | 87 |
| 6 | 3-4 | 87 |
+-----+-----+--------+
4 rows in set (0.00 sec)
--直接使用运算符比较
select * from score where degree > 80 and degree < 90;
mysql> select * from score where degree > 80 and degree < 90;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 1 | 3-2 | 87 |
| 2 | 3-3 | 87 |
| 4 | 3-1 | 87 |
| 6 | 3-4 | 87 |
+-----+-----+--------+
4 rows in set (0.00 sec)
--查询成绩表中成绩为100或８９或97的所有记录
--表示或者关系的查询in
select * from score where degree in (100,89,97);
mysql> select * from score where degree in (100,89,97);
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 5 | 3-4 | 100 |
| 3 | 3-4 | 89 |
| 5 | 3-1 | 97 |
+-----+-----+--------+
3 rows in set (0.00 sec)
--查询班级为A1811班或性别为女的记录
select * from student where class = 'A1811' or ssex = '女';
mysql> select * from student where class = 'A1811' or ssex = '女';
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbrithday | class |
+-----+--------+------+---------------------+-------+
| 1 | 张三 | 男 | 1999-01-01 00:00:00 | A1811 |
| 4 | 赵柳 | 男 | 1999-04-01 00:00:00 | A1811 |
| 7 | 钟会 | 女 | 1999-06-01 00:00:00 | A1813 |
+-----+--------+------+---------------------+-------+
3 rows in set (0.00 sec)
--以class降序查询score表中的所有记录
--升序，降序
--降序desc
select * from student order by class desc;
mysql> select * from student order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbrithday | class |
+-----+--------+------+---------------------+-------+
| 3 | 王五 | 男 | 1999-03-01 00:00:00 | A1813 |
| 6 | 张辉 | 男 | 1999-06-01 00:00:00 | A1813 |
| 7 | 钟会 | 女 | 1999-06-01 00:00:00 | A1813 |
| 2 | 李四 | 男 | 1999-02-01 00:00:00 | A1812 |
| 5 | 力气 | 男 | 1999-05-01 00:00:00 | A1812 |
| 1 | 张三 | 男 | 1999-01-01 00:00:00 | A1811 |
| 4 | 赵柳 | 男 | 1999-04-01 00:00:00 | A1811 |
+-----+--------+------+---------------------+-------+
--升序asc(默认就是升序，所以asc可以省略),如下可以看出其结果是一样的
select * from student order by class asc;
mysql> select * from student order by class asc;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbrithday | class |
+-----+--------+------+---------------------+-------+
| 1 | 张三 | 男 | 1999-01-01 00:00:00 | A1811 |
| 4 | 赵柳 | 男 | 1999-04-01 00:00:00 | A1811 |
| 2 | 李四 | 男 | 1999-02-01 00:00:00 | A1812 |
| 5 | 力气 | 男 | 1999-05-01 00:00:00 | A1812 |
| 3 | 王五 | 男 | 1999-03-01 00:00:00 | A1813 |
| 6 | 张辉 | 男 | 1999-06-01 00:00:00 | A1813 |
| 7 | 钟会 | 女 | 1999-06-01 00:00:00 | A1813 |
+-----+--------+------+---------------------+-------+
7 rows in set (0.00 sec)
select * from student order by class;
mysql> select * from student order by class;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbrithday | class |
+-----+--------+------+---------------------+-------+
| 1 | 张三 | 男 | 1999-01-01 00:00:00 | A1811 |
| 4 | 赵柳 | 男 | 1999-04-01 00:00:00 | A1811 |
| 2 | 李四 | 男 | 1999-02-01 00:00:00 | A1812 |
| 5 | 力气 | 男 | 1999-05-01 00:00:00 | A1812 |
| 3 | 王五 | 男 | 1999-03-01 00:00:00 | A1813 |
| 6 | 张辉 | 男 | 1999-06-01 00:00:00 | A1813 |
| 7 | 钟会 | 女 | 1999-06-01 00:00:00 | A1813 |
+-----+--------+------+---------------------+-------+
7 rows in set (0.00 sec)
7 rows in set (0.00 sec)
--以cno升序，degree降序查询score表中的所有记录
select * from score order by cno asc,degree desc;
mysql> select * from score order by cno asc,degree desc;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 5 | 3-1 | 97 |
| 4 | 3-1 | 87 |
| 1 | 3-2 | 87 |
| 2 | 3-3 | 87 |
| 5 | 3-4 | 100 |
| 3 | 3-4 | 89 |
| 6 | 3-4 | 87 |
+-----+-----+--------+
7 rows in set (0.00 sec)
--查询A1811班级的学生人数
--统计count
select count(*) from student where class= 'A1811';
mysql> select count(*) from student where class= 'A1811';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
--查询score表中最高分的学生学号和课程号
select sno,cno from score where degree = (select max(degree) from score);
mysql> select sno,cno from score where degree = (select max(degree) from score);
+-----+-----+
| sno | cno |
+-----+-----+
| 5 | 3-4 |
+-----+-----+
1 row in set (0.00 sec)