工具猿之MySQL教程-04-创建与管理表

创建与管理表

数据库创建完成后，数据库是空的。可以用show tables 命令查看。

mysql> show tables;

创建表  查看表  修改表  复制表  删除表  插入数据  修改数据  删除数据

创建表

表决定了数据库结构，表是存放数据的地方，一个库需要什么表，各个数据表中需要什么样的列，是要合理设计的。
此教程示例的数据库（StuMS）为学生管理系统。需创建students（学生信息表）、course（课程表）、achievement（成绩表）、teachers（教师信息表）、departments（院系表）、teach（授课表）。

students（学生信息表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
stu_num	char	20	no	主键		学生学号
stu_name	char	6	no			学生姓名
stu_sex	enum	2	no		男	学生性别
stu_birthday	date		no			学生出生日期
dep_num	char	18	no	外键		院系编号
stu_address	varchar	20	no			学生家庭住址
stu_phone	varchar	18	no			学生联系电话
stu_photo	blob		no			学生照片

mysql> create table if not exists students(
    -> stu_num char(20) not null comment'学生学号',
    -> stu_name char(6) not null comment'学生姓名',
    -> stu_sex enum('男','女') default'男' comment'学生性别',
    -> stu_birthday date not null comment'学生出生日期',
    -> dep_num char(18) not null comment'院系编号',
    -> stu_address varchar(20) not null comment'学生家庭住址',
    -> stu_phone varchar(18) not null comment'学生联系电话',
    -> stu_photo blob comment'学生照片',
    -> primary key (stu_num));

course（课程表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
course_num	char	6	no	主键		课程编号
course_name	char	16	no			课程名称
dep_num	char	18	no	外键		院系编号
course_hours	tinyint	10	no			学时
course_credit	tinyint	11	no			学分
course_type	varchar	11	no		必修	类型

mysql> create table if not exists course(
    -> course_num char(6) not null comment'课程编号',
    -> course_name char(16) not null comment'课程名称',
    -> dep_num char(18) not null comment'院系编号',
    -> course_hours int(10) not null comment'学时',
    -> course_credit int(11) not null comment'学分',
    -> course_type enum('必修','选修') default '必修',
    -> primary key (course_num));

achievement（成绩表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
stu_num	char	20	no	主键、外键		学生学号
course_num	char	6	no	主键、外键		课程编号
course_report	float	2	no			课程成绩

mysql> create table if not exists achievement(
    -> stu_num char(20) not null comment'学生学号',
    -> course_num char(6) not null comment'课程编号',
    -> course_report float(3,1) default 0,
    -> primary key (stu_num,course_num));

teachers（教师信息表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
tea_num	char	10	no	主键		教师编号
tea_name	char	6	no			教师姓名
dep_num	char	18	no	外键		院系编号

mysql> create table if not exists teachers(
    -> tea_num char(10) not null comment'教师编号',
    -> tea_name char(6) not null comment'教师姓名',
    -> dep_num char(18) not null comment'院系编号',
    -> primary key (tea_num));

departments（院系表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
dep_num	char	18	no	主键		院系编号
dep_name	char	10	no			院系名称

mysql> create table if not exists departments(
    -> dep_num char(18) not null comment'院系编号',
    -> dep_name char(10) not null comment'院系名称',
    -> primary key (dep_num));

teach（授课表）

字段名	数据类型	长度	是否空值	是否主外键	默认值	备注
tea_num	char	10	no	主键、外键		教师编号
course_num	char	6	no	主键、外键		课程编号

mysql> create table if not exists teach(
    -> tea_num char(10) not null comment'教师编号',
    -> course_num char(6) not null comment'课程编号',
    -> primary key (tea_num,course_num));

讨论：
  1、关于设置主键：primary key 表示设置该字段为主键。如在students表中，primary key（stu_num）表示将stu_num 字段定义为主键。在achievement表中，primary key （stu_num,course_num）表示把stu_num,course_num两个字段一起作为复合主键。
  2、添加注释：comment‘学生学号’ 表示对 stu_num 字段增加注释为‘学生学号’。
  3、字段类型的选择：enum（’男‘，’女‘）表示stu_sex字段类型为enum。取值范围在’男‘和’女‘之间。
  4、默认值的设置：default’男‘ 表示默认值是男。
  5、设置自动增量：一个整数列可以拥有一个附加属性 auto_increment。auto_increment序列一般从1开始，也可以自定义开始值。可以通过auto_increment属性为新的行产生唯一的标识。

查看表

创建数据表之后，可用 show tables 命令查看已建表。

mysql> show tables;

修改表

atter table 用于更改原有表的结构。如增加或删减列、重新重命名列或表，还可以修改字符集。
a、在achievement表的course_num(课程编号)列后面增加一列course_name（课程名称）。

mysql> alter table achievement add course_name char(6) not null comment'课程名称' after course_num;

b、在students表中的stu_birthday列后增加一列‘入学年份’，并定义默认值为‘2019级’。

mysql> alter table students add 入学年份 varchar(10) not null comment'入学年份' default'2019 级' after stu_birthday;

c、修改students表中的stu_sex列的默认值为’女‘。

mysql> alter table students modify stu_sex enum ('男','女') not null default'女';

这里使用了modify关键字，也可以 change 关键字更改枚举值的默认值。

mysql> alter table students change stu_sex stu_sex enum('男','女') not null default'男';

注意使用change关键字的时候，一定要把字段原名称写上。modify 与change的区别：在于修改字段名称只能是用change，modify不能修改字段名称。

d、删除students表中的‘入学年份’列的默认值。开始设置的默认值为‘2019级’。

mysql> alter table students alter 入学年份 drop default;

e、将表students重命名为’学生信息表‘。

mysql> alter table students rename to 学生信息表;

f、将表students中的‘入学年份’列删除。

mysql> alter table students drop 入学年份;

g、删除在achievement表的course_name（课程名称）列。

mysql> alter table achievement drop column course_name;

复制表

可以通过create table命令复制表得结构与数据。
a、复制一个students表为students_test表。注意，这里复制得到的students_test表不含原表(students表)的数据，只复制了原表(students表)的结构

mysql> create table students_test like students;

b、复制表students的结构与数据。

mysql> create table students_copy as select * from students;

删除表

如果不需要表了或者表不合适，用drop table删除表。
a、删除students_test表

mysql> drop table students_test;

插入数据

插入数据的方法有很多，可以通过 insert into 、replace into 语句插入，也可以使用 load data infle方式将保存在文本文件中的数据插入到指定的表。一次也可以插入一行或多行。
a、同时向students表中插入两行数据。

mysql> insert into students values
    -> ('201925080105','易峰','男','1998-03-14','A001','山东省菏泽市永平路345号','15023454321',null),
    -> ('201925080107','张紫云','女','1999-06-23','A003','山东省德州市禹城县','17812345234',null);

b、再次使用insert into 插入语句向students表中插入语句。

mysql> insert into students values
    -> ('201925080105','王依依','女','2000-05-15','B103','上海市黄埔区635号','17833467586',nu
ll);

注意，这里新插入的学生学号信息，与主键发生冲突，已经存在于表中。出现错误。用replace into 语句可以直接插入数据，而不会出现数据错误，相当于学生‘易峰’的数据信息被替换成‘王依依’的数据信息。

c、假设有一个与students表结构一样的数据表（students_test）,现将students表中的数据插入到students_test表中。

mysql> insert into students_test select * from students;

讨论：
  1、values子句：
  2、insert into：使用insert into语句可以向表中插入一行数据，也可以插入多上数据，建议一次插入多行数据，各数据之间用逗号隔开。
  3、set子句：也可以使用 set 子句插入数据，用 set 子句直接赋值时可以不用按字段顺序插入数据，对允许空值的列可以不插入。例如：

mysql> replace into students set
    -> stu_num='201925080105',stu_name='王依依',stu_sex='女',stu_birthday='2000-05-15',dep_num='B103',stu_address='上海市黄埔区635号',stu_phone='17833467586';

  4、replace into：根据主键或者唯一索引判断，如果所插入的数据已存在表中，造成冲突，可用replace into语句，表示，先删除此行数据，然后插入新的数据。如果没有造成冲突，直接插入新的数据。

d、用load data 语句将数据信息插入数据库表（course表）中。
这个是在本地服务器上的 kc.txt 文件（课程表数据），被放在/opt/目录下

执行load data命令

mysql> load data local infile '/opt/kc.txt' into table course fields terminated by ',';

注意：
  fields terminated by：意思是以什么字符作为分隔符。因为此例的文本数据是以逗号隔开的，所以执行命令时 fields terminated by ‘,’ 。

e、向students表中插入一条信息（含有照片）。照片路径是‘/opt/images/test_photo1.jpg’。

说明：
  图片一般可以以路径的方式来存储，则插入图片采用直接插入图片的存储路径。也可以直接插入图片本身，只要用load_file()函数即可。

mysql> insert into students values
    -> ('201925080108','董小璇','女','2000-10-07','A001','上海市南京路','17823349870','/opt/images/test_photo1.jpg');

mysql> insert into students values
    -> ('201925080110','欧阳谷宇','男','1998-08-13','A003','湖南长沙市德中路','17843527650',l)oad_file('/opt/images/test_photo2.jpg'));

我们可以看到，当使用load_file()函数直接插入图片时，返回 null 值，是因为MySQL中的secure-file-priv特性。

查看secure-file-priv参数的值：

mysql> show global variables like'%secure%';

MYSQL新特性secure_file_priv对读写文件的影响
此开关默认为NULL，即不允许导入导出。

解决问题：
  修改my.cnf 在[mysqld]内加入secure_file_priv =
MYSQL新特性secure_file_priv对读写文件的影响
然后重启mysql，再查询secure_file_priv。

vim /etc/my.cnf 

/etc/init.d/mysqld restart

mysql> show global variables like'%secure%';

这样再执行以上 insert into …load_file() 语句即可完成。

修改数据

用update…set…命令对表中的数据进行修改。可改一个或多个表得数据。
a、将achievement（成绩表）中学号201925080100学生，由原始成绩‘84.0’修改为‘80’分。

mysql> update achievement set course_report=80 where stu_num='201925080100';

b、将course（课程表）中A00102课程，由原始‘必修’修改为‘选修’。

mysql> update course set course_type='选修' where course_num='A00102';

讨论：
  set子句：根据where指定的条件，对符合条件的数据进行修改。若语句中不设定where子句，则更新所有行。可以同时修改所在行的多个列值，中间用逗号隔开。

删除数据

a、在students（学生表）中删除学号为201925080105的学生。

mysql> delete from students where stu_num='201925080105';

b、在achievement（成绩表）中删除成绩低于‘60’分的学生。

mysql> delete from achievement where course_num='A00101' and course_report < 60;

c、在achievement（成绩表）中删除成绩最低的三行记录。

mysql> delete from achievement order by course_report limit 3;

讨论：
  where子句：用于指定的删除条件，如果不含有where子句，则删除该表的所有行。
  order by子句：各行按照子句中指定的顺序进行删除，只在与limit 联合使用时才起作用。如果不指定升序(ASC)或降序(DESC)，默认是升序排序。
  limit子句：用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。如上列中与delete …where…order by …联用，使用course_report进行默认升序，并删除前三（最旧的，先排序的）行。