MySQL 学习笔记（四）——查询练习

查询练习

1、准备数据

准备创建 几个表：

学生表（Student）：学号、姓名、性别、出生年月日、班级
课程表（Course）：课程号、课程名称、教师编号
成绩表（Score）　：学号、课程号、成绩
教师表（Teacher）：教师编号、教师性别、教师性别、出生年月日、职称、所在部门

#创建一个selectTest新数据库
mysql> create database selectTest;    
Query OK, 1 row affected, 0 warning (0.67 sec)

#创建学生表
create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
);

#创建老师表
create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20),
	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(
	s_no varchar(20) not null,
	c_no varchar(20) not null,
	degree decimal,
	foreign key(s_no) references student(sno),
	foreign key(c_no) references course(cno),
	primary key(s_no,c_no)
);

下面添加数据：

-- 添加学生表数据
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-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

查看表结构：

-- 查看表结构
mysql> select * from student;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

mysql> select * from teacher;
+---------+--------+------+---------------------+-----------+-----------------+
| tnumber | tname  | tsex | tbirthday           | prof      | depart          |
+---------+--------+------+---------------------+-----------+-----------------+
| 111     | 古一   | 女   | 0000-01-01 00:00:00 | 教授      | 化学系          |
| 112     | 王     | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系        |
| 113     | 春丽   | 女   | 1988-11-05 00:00:00 | 助教      | 英语系          |
| 114     | 刘邦   | 男   | 1978-12-03 00:00:00 | 助教      | 通信工程系      |
+---------+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from course;
+---------+--------------+---------+
| cnumber | cname        | tnumber |
+---------+--------------+---------+
| 3-105   | 数据结构     | 112     |
| 3-245   | 模拟电路     | 113     |
| 6-166   | 人工智能     | 111     |
| 9-888   | 数字电路     | 114     |
+---------+--------------+---------+
4 rows in set (0.00 sec)

mysql> select * from score;
+---------+---------+--------+
| s_no    | c_no    | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
| 106     | 6-166   |     92 |
+---------+---------+--------+
7 rows in set (0.00 sec)

2、查询练习 (1-10)

1、查询student表中的所有记录

select  *  from  student;