1.NULL / NOT NULL
NULL(表示默认的),数据库默认字段基本都是字段为空。
NOT NULL(表示不为空)
--NULL和任意数据进行运算操作,结果为NULL
2.default(默认值)
比如创建女生班级学生表时所有的学生性别都是女,就可以在创建表时给性别列附上默认值'女';在插入数据时,不用给性别赋值,就使用默认值。
mysql> create table student(id int,name varchar(32),sex varchar(32) default '女');
Query OK, 0 rows affected (0.49 sec)
mysql> insert into student(id,name)values(1,'huahua'),(2,'haha');
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | huahua | 女 |
| 2 | haha | 女 |
+------+--------+------+
2 rows in set (0.00 sec)
mysql> insert into student values(3,'hh','男');
Query OK, 1 row affected (0.14 sec)
mysql> select * from student;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | huahua | 女 |
| 2 | haha | 女 |
| 3 | hh | 男 |
+------+--------+------+
3 rows in set (0.00 sec)
3.列描述(comment注释:对程序没有影响)
mysql> create table student(
id int comment '学号',
name varchar(32) comment '姓名',
sex varchar(32) comment '性别' default '女');
Query OK, 0 rows affected (0.49 sec)
4.zerofill(如果宽度小于设定的宽度,自动填充0)
mysql> create table zerofilltest(a int(5));
Query OK, 0 rows affected (0.80 sec)
mysql> insert into zerofilltest values(1);
Query OK, 1 row affected (0.17 sec)
mysql> select * from zerofilltest;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> alter table zerofilltest modify a int(5) zerofill;
Query OK, 1 row affected (1.75 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from zerofilltest;
+-------+
| a |
+-------+
| 00001 |
+-------+
5.主键(primary key)
主键用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键,主键所在的列往往都是整数类型。
- 添加主键的方式
I.直接在列名后加primary key。
mysql> create table pritest(a int primary key,b int);
Query OK, 0 rows affected (0.63 sec)
mysql> desc pritest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.11 sec)
II.在表创建成功后使用alter table 表名 add primary key(字段名)添加主键
mysql> create table pritest1(a int,b int);
Query OK, 0 rows affected (0.65 sec)
mysql> desc pritest1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--添加主键
mysql> alter table pritest1 add primary key(a);
Query OK, 0 rows affected (1.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc pritest1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
III.使用复合主键,在创建表时用primary key(字段1名,字段2名)
mysql> create table pritest2(a int,b int,primary key(a,b));
Query OK, 0 rows affected (0.71 sec)
mysql> desc pritest2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- ***主键对应的值不可以重复,一旦插入重复的值,则插入失败
mysql> insert into pritest values(1,1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into pritest values(1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
- 删除主键:用alter table 表名 drop primary key;
mysql> alter table pritest drop primary key;
Query OK, 1 row affected (1.51 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into pritest values(1,2);
Query OK, 1 row affected (0.23 sec)
6.自增长(auto_increment)
当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作, 得到一个新的不同的值
mysql> create table incrementtest(id int primary key auto_increment,age int);
Query OK, 0 rows affected (0.57 sec)
mysql> insert into incrementtest (age)values(15),(16);
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from incrementtest;
+----+------+
| id | age |
+----+------+
| 1 | 15 |
| 2 | 16 |
+----+------+
2 rows in set (0.00 sec)
自增长的特点:
任何一个字段要做自增长,前提是本身是一个索引(key一栏有值)
自增长字段必须是整数
一张表最多只能有一个自增长
7.唯一键(unique key)
唯一键可以解决表中有多个字段需要唯一性约束的问题。唯一键允许为空,也可多个为空,空字段不进行唯一性比较。
8.外键(foreign key )
外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,主表则必须是有主键约束或unique约束。当 定义外键后,要求外键列数据必须在主表的主键列存在或为null。
外键语法:foreign key (字段名) references 主表(列)