表结构 、 MySQL键值

案例1：约束条件
案例2：修改表结构
案例3：index 普通索引
案例4：primary key 主键
案例5：foreign key 外键

1 案例1：约束条件
1.1 问题

具体要求如下：
如图-1所示设置约束条件

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：设置约束

在db2库里创建t2表时设置字段约束条件

mysql>
mysql> create database db2; //建库
Query OK, 1 row affected (0.00 sec)
mysql> use  db2; //切换库
Database changed
mysql> create table  t2 (  //建表
    -> class   char(9),
    -> name    char(10) not null  ,
    -> age     tinyint  not null default  19 ,
    -> likes   set("a","b","c","d")  default  "a,b"
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2; //查看表结构
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| class | char(9)              | YES  |     | NULL    |       |
| name  | char(10)             | NO   |     | NULL    |       |
| age   | tinyint(4)           | NO   |     | 19      |       |
| likes | set('a','b','c','d') | YES  |     | a,b     |       |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> insert into  t2  values (null,"bob",29,"c,d");
Query OK, 1 row affected (0.05 sec)
mysql> insert into  t2(class,name) values ("nsd1902","tom");
Query OK, 1 row affected (0.05 sec)
mysql> insert into  t2  values (null,null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null  //不允许赋null值
MariaDB [db2]>
MariaDB [db2]> select  * from db2.t1;  //查看记录
+---------+------+-----+-------+
| class   | name | age | likes |
+---------+------+-----+-------+
| NULL    | bob  |  29 | c,d   |
| nsd1902 | tom  |  19 | a,b   |
+---------+------+-----+-------+
2 rows in set (0.00 sec)
mysql>

2 案例2：修改表结构
2.1 问题

具体要求如下：
添加字段
修改字段名
修改字段类型
删除字段
修改表名

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：添加字段

Mysql> create database studb;
mysql> CREATE TABLE studb.tea6(
-> id int(4) ,
    -> name varchar(4) NOT NULL,
    -> age int(2) NOT NULL
    -> );
Query OK, 0 rows affected (0.34 sec)
mysql>

为tea6表添加一个address字段

添加前：

mysql> DESC tea6;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(4)     |  YES  |      | NULL    |       |
| name  | varchar(4) | NO   |     | NULL    |       |
| age   | int(2)     | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加address字段：

mysql> ALTER TABLE tea6 ADD address varchar(48);
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加后（默认作为最后一个字段）：

mysql> DESC tea6;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | YES   |   | NULL    |       |
| name    | varchar(4)  | NO   |     | NULL    |       |
| age     | int(2)      | NO   |     | NULL    |       |
| address | varchar(48) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3）在tea6表的age列之后添加一个gender字段

添加操作：

mysql> ALTER TABLE tea6 ADD gender enum('boy','girl') AFTER age;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

确认添加结果：

mysql> DESC tea6