MySQL--基础三

本章节将总结MySQL的表结构修改，约束条件，表关系。

表结构修改：

首先查看一下表结构：
desc tb_name; 查询表结构

mysql> desc student;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(20)   | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改表结构，就是对表结构进行增删改查。关键字为alter
1、修改表结构：alter table tb_name rename new_tb_name;
2、修改字段名：alter table tb_name change name new_name data_type;
3、修改字段类型：alter table tb_name modify field_name data_type;
4、添加字段：alter table tb_name add [col] field_name data_type;
5、删除字段：alter table tb_name drop [col] field_name;
其中，tb_name为要修改的表名，new_tb_name为修改后的新表名，data_type为字段的类型，new_name 为新的字段名，field_name为字段名。

mysql> show tables;
+------------------------+
| Tables_in_python_study |
+------------------------+
| class                  |
| student                |
+------------------------+
2 rows in set (0.00 sec)

mysql> alter table student rename stu; #修改表名
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+------------------------+
| Tables_in_python_study |
+------------------------+
| class                  |
| stu                    |
+------------------------+
2 rows in set (0.00 sec)

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(20)   | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> alter table stu change id s_id tinyint; #修改字段名以及类型。
Query OK, 13 rows affected (1.05 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | varchar(20)   | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | varchar(20)   | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table stu modify name char(20); #修改字段类型
Query OK, 13 rows affected (0.90 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | char(20)      | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | char(20)      | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table stu add phone bigint; #增加字段
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | char(20)      | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
| phone | bigint(20)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | char(20)      | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
| phone | bigint(20)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table stu drop phone; #删除字段
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| s_id  | tinyint(4)    | YES  |     | NULL    |       |
| name  | char(20)      | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| grade | varchar(6)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

约束条件：

约束是一种限制，通过对表中的数据做出限制，来确保表中的数据完整性，唯一性。

约束类型	关键字
默认	default
非空	not null
唯一	unique key
自增长	auto_increment 和主键联合使用
主键	primary key
外键	foreign key

外键的约束：
B表中的id字段，只能添加A表中id有的字段值。如果没有，将无法添加。
A表中的id字段是被参照的数据，不能被修改和删除。
我们以A表为student表，B为class表为例。

mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> age tinyint,
    -> sex enum('M','F') default 'M',
    -> grade varchar(6),
    -> id_card bigint unique key
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql> create table class(
    -> c_id int primary key auto_increment,
    -> c_name varchar(20) not null,
    -> foreign key(c_id) references student(id) 
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> insert into student(name,grade,id_card)values
    -> ('apple','1','123456789'),
    -> ('pear','2','213456789');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+------+-------+-----------+
| id | name  | age  | sex  | grade | id_card   |
+----+-------+------+------+-------+-----------+
|  1 | apple | NULL | M    | 1     | 123456789 |
|  2 | pear  | NULL | M    | 2     | 213456789 |
+----+-------+------+------+-------+-----------+
2 rows in set (0.00 sec)

mysql> insert into class values('class1'),('class2');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into class(c_name) values('class1'),('class2');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
|    1 | class1 |
|    2 | class2 |
+------+--------+
2 rows in set (0.01 sec)

mysql> alter table student drop id;
ERROR 1829 (HY000): Cannot drop column 'id': needed in a foreign key constraint 'class_ibfk_1' of table 'db_name.class'

表关系

表关系可以分为：
一对一、一对多、多对多。

图1

举例，学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表来存放。
这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息。用主键加主键的方式来实现这种关系。
1、一对一：用外键的方式，把两个表的主键关联。
2、一对多：通过外键来实现表的一对多关系。
3、多对多：多对多的关系需要创建中间表来实现。

mysql> desc student;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | int(11)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)   | NO   |     | NULL    |                |
| age     | tinyint(4)    | YES  |     | NULL    |                |
| sex     | enum('M','F') | YES  |     | M       |                |
| grade   | varchar(6)    | YES  |     | NULL    |                |
| id_card | bigint(20)    | YES  | UNI | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> create table course(
    -> cou_id int primary key auto_increment,
    -> cou_name varchar(20) not null,
    -> cou_teacher varchar(20) not null,
    -> foreign key(cou_id) references student(id)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into course (cou_name,cou_teacher)values('python','tony'),('java','matin');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from course;
+--------+----------+-------------+
| cou_id | cou_name | cou_teacher |
+--------+----------+-------------+
|      1 | python   | tony        |
|      2 | java     | matin       |
+--------+----------+-------------+
2 rows in set (0.01 sec)

mysql> create table curr(  #创建中间表，实现多对多的关系
    -> id int,
    -> cou_id int ,
    -> primary key(id,cou_id),
    -> foreign key(id) references student(id),
    -> foreign key(cou_id) references course(cou_id)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> insert into curr values(1,2),(2,1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from curr;
+----+--------+
| id | cou_id |
+----+--------+
|  2 |      1 |
|  1 |      2 |
+----+--------+
2 rows in set (0.00 sec)

mysql> select s.name,c.cou_name,c.cou_teacher  from student s,course c ,curr cu where s.id=cu.id and c.cou_id=cu.cou_id;
+-------+----------+-------------+
| name  | cou_name | cou_teacher |
+-------+----------+-------------+
| pear  | python   | tony        |
| apple | java     | matin       |
+-------+----------+-------------+
2 rows in set (0.00 sec)