表结构

表结构

约束条件

查看约束条件

. Mysql>desc库名.表名;

 

 

 设置约束条件

 . null 　　　　允许为空（默认设置) 

. not null 　　　　不允许为null(空)

. key　　　　键值类型

. default　　　　设置默认值，缺省为NULL

. extra　　　　额外设置

mysql> create table db1.t7(->name char(10) not null,
-> age tinyint unsigned default 19,
-> class char(7) not null default "class19”,

-> pay float(7.2) default 28000
->);

 

 

 

设置约束

1）在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> 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.t2;  //查看记录
+---------+------+-----+-------+
| class   | name | age | likes |
+---------+------+-----+-------+
| NULL    | bob  |  29 | c,d   |
| nsd1902 | tom  |  19 | a,b   |
+---------+------+-----+-------+
2 rows in set (0.00 sec)

mysql> select name from db2.t2;
+------+
| name |
+------+
| bob |
| tom |
+------+
2 rows in set (0.00 sec)

mysql> select name,age from db2.t2;
+------+-----+
| name | age |
+------+-----+
| bob | 29 |
| tom | 19 |
+------+-----+
2 rows in set (0.00 sec)

修改表结构

语法结构

·用法
mysql> alter table库名.表名执行动作;

add 　　添加字段

modify　　修改字段类型

change　　修改字段名

drop　　删除字段

rename　　修改表名

添加新字段

·用法
-新字段默认添加在字段末尾

mysql> alter table库名.表名
　　　　add　字段名  类型(宽度)　约束条件

　　　　[ after字段名| first ];

mysql>alter table db1.t2
->add
->name char(15)  first;
Query OK,0 rows affected (0.63 sec)

修改字段类型

基本用法
-修改的字段类型不能与已存储的数据冲突

mysql> alter table库名.表名
　　　　modify  字段名 　类型(宽度)　约束条件

　　　　[ after字段名| first ] ;

mysql>alter table db1.t2
　　　　->modify
　　　　-> id int ;
Query OK,1 row affected (0.64 sec)

修改字段名

·基本用法
-也可以用来修改字段类型

mysql> alter table库名.表名
　　　　change  源字段名    新字段名    类型(宽度)   

　　　　约束条件;

新字段名：当跟新类型和约束条件时，可修改字段类型

mysql>alter table db1.t2 change   id    user_num int;

Query OK, 0 rows affected (0.12 sec)

删除字段

·基本用法
-表中有多条记录时，所有列的此字段的值都会被删除
mysql> alter table  库名.表名   drop  字段名﹔

mysql> alter table db1.t2 drop user_num , drop pay；

Query OK, 0 rows affected (0.55 sec)

修改表名

基本用法
-表对应的文件名，也被改变-表记录不受影响
mysql> alter table  表名  rename   新表名;

mysql> alter table t2 rename school;

Query OK, 0 rows affected (0.12 sec)

 

案例2：修改表结构

步骤一：添加字段

1）在studb库下创建tea6表

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>

2）为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;
+---------+--------------------+------+-----+---------+-------+
| Field   | Type               | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| id      | int(4)             |YES   |  | NULL    |       |
| name    | varchar(4)         | NO   |     | NULL    |       |
| age     | int(2)             | NO   |     | NULL    |       |
| gender  | enum('boy','girl') | YES  |     | NULL    |       |
| address | varchar(48)        | YES  |     | NULL    |       |
+---------+--------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

步骤二：修改字段名和字段类型

将tea6表的gender字段改名为sex，并添加非空约束

修改操作：

mysql> ALTER TABLE tea6 CHANGE gender
    -> sex enum('boy','girl') NOT NULL;
Query OK, 0 rows affected (0.08 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    |       |
| sex     | enum('boy','girl') | NO   |     | NULL    |       |
| address | varchar(48)        | YES  |     | NULL    |       |
+---------+--------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

步骤三：删除字段

删除tea6表中名为sex的字段：

mysql> ALTER TABLE tea6 DROP sex;     //删除操作
Query OK, 0 rows affected (0.52 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)