MYSQL数据库系统第3次实验 数据表的修改与数据操作

实验目的：

1. 熟悉修改数据表；
2. 熟悉数据表的数据操作。

实验设备及器材：
PC，mysql 等

实验内容：
基于上次实验创建的数据库teaching，以及数据库中的6张数据表，完成以下操作：

1. 查看数据库teaching已经创建的表；

mysql> show tables;

2. 查看数据表student的基本结构；

mysql> desc student;

3. 查看数据表course的建表语句；

mysql> show create table course;

4. 通过复制表student方式，创建表student1；

mysql> create table student1 like student;

5.修改表student1的表名为test_student;

mysql> alter table student1 rename to test_student;

6.在表test_student开头位置(第一列的前面)添加新字段address,数据类型为varchar(30),字段非空，默认值为“广州华商学院”；

mysql> alter table test_student
    -> add address varchar(30) not null default'广州华商学院' first;

mysql> desc test_student;

7. 修改表test_student中address的默认值为“广州市增城区”;

mysql> alter table test_student
    -> alter address set default '广州市增城区';

mysql> desc test_student;

8. 删除表test_student中字段address；

mysql> alter table test_student
    -> drop address;

mysql> desc test_student;

9. 删除数据表test_student；

mysql> drop table test_student;

10.向数据库teaching中的6张表中插入数据，数据详情见excel表：teaching数据.xlsx;

mysql> set character_set_client=gbk;

insert into student values
('18122210009','许东山','男','1999/11/5','789','13623456778','qwe@163.com'),
('18122221324','何白露','女','2000/12/4','879','13178978999','heyy@sina.com'),
('18125111109','敬横江','男','2000/3/1','789','15678945623','jing@sina.com'),
('18125121107','梁一苇','女','1999/9/3','777','13145678921','bing@126.com'),
('18135222201','凌浩风','女','2001/10/6','867','15978945645','tang@163.com'),
('18137221508','赵临江','男','2000/2/13','789','12367823453','ping@163.com'),
('19111133071','崔依歌','女','2001/6/6','787','15556845645','cui@126.com'),
('19112100072','宿沧海','男','2002/2/4','658','12545678998','su12@163.com'),
('19112111208','韩山川','男','2001/2/14','666','15878945612','han@163.com'),
('19122203567','封月明','女','2002/9/9','898','13245674564','jiao@126.com'),
('19123567897','赵既白','女','2002/8/4','999','13175689345','pingan@163.com'),
('19126113307','梅惟江','女','2003/9/7','787','13245678543','zhu@163.com');

mysql> replace into course values
    -> ('c05103','电子技术','必修','64','16','2'),
    -> ('c05109','C语言','必修','48','16','2'),
    -> ('c05127','数据结构','必修','64','16','2'),
    -> ('c05138','软件工程','选修','48','8','5'),
    -> ('c06108','机械制图','必修','60','8','2'),
    -> ('c06127','机械设计','必修','64','8','3'),
    -> ('c06172','铸造工艺','选修','42','16','6'),
    -> ('c08106','经济法','必修','48','0','7'),
    -> ('c08123','金融学','必修','40','0','5'),
    -> ('c08171','会计软件','选修','32','8','8');

mysql> select * from course;

mysql> insert into teacher values
    -> ('t05001','苏超然','软件工程','教授','计算机学院'),
    -> ('t05002','常杉','会计学','助教','管理学院'),
    -> ('t05003','孙释安','网络安全','教授','计算机学院'),
    -> ('t05011','卢敖治','软件工程','副教授','计算机学院'),
    -> ('t05017','茅佳峰','软件测试','讲师','计算机学院'),
    -> ('t06011','夏南望','机械制造','教授','机械学院'),
    -> ('t06023','葛庭宇','铸造工艺','副教授','材料学院'),
    -> ('t07019','韩既乐','经济管理','讲师','管理学院'),
    -> ('t08017','时观','金融管理','副教授','管理学院');

mysql> select * from teacher;

11. 利用insert命令向表student中插入一行数据，字段取值为本人的信息；

mysql> insert into student values
-> ('41940238','金智秀','女','2000-09-04','660','00000000000','000000000@qq.com');

12. 复制表student结构和数据，创建表student2；

mysql> create table student2 as select * from student;

mysql> select * from student2;

13. 将表student2中男生的入学成绩增加20；

mysql> update student2 set entrance=entrance+20 where sex='男';

mysql> select * from student2;

14. 删除表student中本人信息的那一行记录。

mysql> delete from student
    -> where studentno='41940238';

mysql> select * from student;