DBA 1-2 字段约束条件！

DBA-Day02 

字段约束条件

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> show tables;
+------------------+
| Tables_in_db1    |
+------------------+
| 学生信息表1      |
| t2               |
+------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db1;
Database changed
mysql> 
mysql> show tables;
+------------------+
| Tables_in_db1    |
+------------------+
| 学生信息表1      |
| t2               |
+------------------+
2 rows in set (0.00 sec)

mysql> create table t14(
    -> name char(5) not null,
    -> level int(3) zerofill default 0,
    -> money tinyint (2) zerofill default 0
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> 
mysql> desc t14;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name  | char(5)                      | NO   |     | NULL    |       |
| level | int(3) unsigned zerofill     | YES  |     | 000     |       |
| money | tinyint(2) unsigned zerofill | YES  |     | 00      |       |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql> create table t14(
    -> name char(5) not null,
    -> level int(3) zerofill default 0,
    -> money tinyint (2) zerofill default 0
    -> );

mysql> desc t14;
mysql>  desc db1.t14;
mysql> desc db1.t14;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name  | char(5)                      | NO   |     | NULL    |       |
| level | int(3) unsigned zerofill     | YES  |     | 000     |       |
| money | tinyint(2) unsigned zerofill | YES  |     | 00      |       |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into db1.t14(name) values("bob");
Query OK, 1 row affected (0.08 sec)

mysql> select * from t14;
+------+-------+-------+
| name | level | money |
+------+-------+-------+
| null |  NULL |  NULL |
|      |  NULL |  NULL |
| bob  |   000 |    00 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into t14 values("jack",1,250)
    -> ;
Query OK, 1 row affected (0.04 sec)

mysql> select * from t14;
+------+-------+-------+
| name | level | money |
+------+-------+-------+
| null |  NULL |  NULL |
|      |  NULL |  NULL |
| bob  |   000 |    00 |
| jack |   001 |   250 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> desc t14;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name  | char(5)                      | NO   |     | NULL    |       |
| level | int(3) unsigned zerofill     | YES  |     | 000     |       |
| money | tinyint(2) unsigned zerofill | YES  |     | 00      |       |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql> insert into t14 values(null,null,null);        //第一个null这是空  但是不让赋空值
ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into t14 values("null",null,null);        //第一个“null”这不是空  只是把null引起来，失去空的意思
Query OK, 1 row affected (0.31 sec)

mysql> select * from t14;

mysql> insert into t14 values("",null,null);        //"" 这不是空  这是空站位符 也是值
Query OK, 1 row affected (0.04 sec)

mysql> select * from t14;

mysql> desc t14;

mysql> insert into t14(level,money) values(2,19);            ##// name 字段必须赋予值，  默认 no null  不能为空
ERROR 1364 (HY000): Field 'name' doesn't have a default value

再看t15表     #// name字段   默认 空白符   “ ”里面是空白的意思  不是空的意思
mysql> create table t15( name char(5) not null default"", level int(3) zerofill default 0, money tinyint (2) zerofill default 0 );

mysql> desc t15;

mysql> insert into t15(level,money) values(2,19);

mysql> select * from t15;

mysql> select * from t15;
+------+-------+-------+
| name | level | money |
+------+-------+-------+
|      |   002 |    19 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql>