DBA (day2)

表结构

约束条件

查看约束条件

myslq> desc 库名.表名;

mysql> desc db1.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int(11)    | YES  |     | NULL    |       |
| name    | char(5)    | YES  |     | NULL    |       |
| age     | tinyint(4) | YES  |     | NULL    |       |
| pay     | float      | YES  |     | NULL    |       |
| money   | float(5,2) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
  字段名        类型       空     键值    默认值   额外设置

  设置约束条件

　null　　 允许为空(默认设置)

　not null  不允许为空

   key         键值类型

　default   设置默认值，缺省为null

　extra      额外设置 

ysql> create table t22(
    -> name char(10) not null,
    -> age tinyint unsigned default 25,
    -> sex enum("m","w") not null default "w");
Query OK, 0 rows affected (0.01 sec)

mysql> desc t22;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 25      |       |
| sex   | enum('m','w')       | NO   |     | w       |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t22(name) values("jerry");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t22(name) values("jerry",null);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into t22(name,age) values("jerry",null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t22 values("jerry",null,null);
ERROR 1048 (23000): Column 'sex' cannot be null

mysql> insert into t22 values("jerry",null,"null");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

mysql> insert into t22 values("null",null,"m");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t22;
+-------+------+-----+
| name  | age  | sex |
+-------+------+-----+
| jerry |   25 | w   |
| jerry | NULL | w   |
| null  | NULL | m   |
+-------+------+-----+
3 rows in set (0.00 sec)

修改表结构

语法结构

添加新字段

修改字段类型

修改字段名

删除字段

修改表名