1.创建学生数据库db_student;
mysql> create database db_student;
Query OK, 1 row affected (0.00 sec)
2.打开学生数据库db_student;
mysql> use db_student;
Database changed
3.在db_student数据库里创建学生表t_student包括(学号st_id,姓名st_name,性别st_sex,出生日期st_birthday,系部st_department)字段,并将性别st_sex默认值设置为“男”;
mysql> create table t_student(st_id int,st_name char(10),st_sex varchar(50) default '男', st_birthday char(50),st_department varchar(20));
Query OK, 0 rows affected (0.01 sec)
4.显示学生表t_student结构;
mysql> desc t_student;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | char(10) | YES | | NULL | |
| st_sex | varchar(50) | YES | | 男 | |
| st_birthday | char(50) | YES | | NULL | |
| st_department | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5.给学生表t_student设置主键st_id;
mysql> alter table t_student
-> add primary key(st_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| st_name | char(10) | YES | | NULL | |
| st_sex | varchar(50) | YES | | 男 | |
| st_birthday | char(50) | YES | | NULL | |
| st_department | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
6.将学生表中系部st_department的默认值设置为“计算机系”;
mysql> alter table t_student
-> change st_department
-> st_department varchar(20) default '计算机系';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+---------------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+--------------+-------+
| st_id | int | NO | PRI | NULL | |
| st_name | char(10) | YES | | NULL | |
| st_sex | varchar(50) | YES | | 男 | |
| st_birthday | char(50) | YES | | NULL | |
| st_department | varchar(20) | YES | | 计算机系 | |
+---------------+-------------+------+-----+--------------+-------+
5 rows in set (0.00 sec)
7.创建课程表t_course,包括字段(课程号course_id,课程名course_name,学分course_credit);
mysql> create table t_course(course_id char(3),course_name varchar(30),course_credit varchar(40));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t_course;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| course_id | char(3) | YES | | NULL | |
| course_name | varchar(30) | YES | | NULL | |
| course_credit | varchar(40) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
8.给课程表t_course设置主键course_id;
mysql> alter table t_course
-> add primary key(course_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_course;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| course_id | char(3) | NO | PRI | NULL | |
| course_name | varchar(30) | YES | | NULL | |
| course_credit | varchar(40) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
9.创建选修表t_choose,包括字段(学号st_id,课程号course_id,成绩score)
mysql> create table t_choose(st_id int,course_id int,score char(50));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t_choose;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| course_id | int | YES | | NULL | |
| score | char(50) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
10给选修表创建主键(st_id+course_id);
mysql> alter table t_choose add primary key(st_id,course_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_choose;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| course_id | int | NO | PRI | NULL | |
| score | char(50) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
11.给选修表设置外键st_id,并参照学生表主键st_id;
mysql> alter table t_choose
-> add foreign key(st_id) references t_student(st_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_choose;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| course_id | int | NO | PRI | NULL | |
| score | char(50) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc t_student;
+---------------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+--------------+-------+
| st_id | int | NO | PRI | NULL | |
| st_name | char(10) | YES | | NULL | |
| st_sex | varchar(50) | YES | | 男 | |
| st_birthday | char(50) | YES | | NULL | |
| st_department | varchar(20) | YES | | 计算机系 | |
+---------------+-------------+------+-----+--------------+-------+
5 rows in set (0.00 sec)
12.验证参照完整性规则:插入规则
(1)给学生表中插入一条记录;
insert into s_student values (01,'李明','男','20','英语系');
(2)给课程表中插入一条记录;
insert into t_course values(01,'英语',3);
(3)给选修表中插入两条记录(一个是主表中有的,一个是主表中没的)
insert into t_choose values (01,01,70);
13.验证参照完整性规则:删除规则
删除学生表中的一条记录(子表中有的)
delete from s_student where st_id=01;
14.给学生表中插入多条记录
insert into s_student values (01,'李明','男','20','数学系'),(02,'李丽','女','19','英语系'),(03,'小王','男','21','计算机系'),(04,'王莉','女','18','计算机系');
15.新建一个t_student1数据表,表结构与t_student表一致,将t_student表中的数据全部插入到t_student1表中。
create table t_student1 select *from s_student;
16.在t_student表中完成一下查询操作:
(1)查询t_student表中所有学生的信息。
select *from s_student;
+-------+---------+--------+-------------+---------------+
| st_id | st_name | st_sex | st_birthday | st_department |
+-------+---------+--------+-------------+---------------+
| 1 | 李明 | 男 | 20 | 数学系 |
| 2 | 李丽 | 女 | 19 | 英语系 |
| 3 | 小王 | 男 | 21 | 计算机系 |
| 4 | 王莉 | 女 | 18 | 计算机系 |
+-------+---------+--------+-------------+---------------+
(2)在t_student表中查询学生的st_name和st_bithday。
select st_name,st_birthday from s_student;
+---------+-------------+
| st_name | st_birthday |
+---------+-------------|
| 李明 | 20 |
| 李丽 | 19 |
| 小王 | 21 |
| 王莉 | 18 |
+---------+-------------+
4 rows in set (0.00 sec)
(3)在t_student表中查询所有女生的信息。
select *from s_student where st_sex='女';
+-------+---------+--------+-------------+---------------+
| st_id | st_name | st_sex | st_birthday | st_department |
+-------+---------+--------+-------------+---------------+
| 2 | 李丽 | 女 | 19 | 英语系 |
| 4 | 王莉 | 女 | 18 | 计算机系 |
+-------+---------+--------+-------------+---------------+
2 rows in set (0.00 sec)
(4)在t_student表中查询所有计算机系的学生的姓名。
select st_name from s_student where st_department='计算机系';
+---------+
| st_name |
+---------+
| 小王 |
| 王莉 |
+---------+
2 rows in set (0.00 sec)
(5)在s_student表中查询st_department,并显示为“系部”。
select st_department as '系部' from s_student;
+--------------+
| 系部 |
+--------------+
| 数学系 |
| 英语系 |
| 计算机系 |
| 计算机系 |
+--------------+
4 rows in set (0.00 sec)