MySQL（2）

自增长

mysql> -- 测试AUTO_INCREMENT
mysql> CREATE TABLE IF NOT EXISTS user5(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT user5 VALUES(111,'KING1');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT user5 VALUES(NULL,'AAAA');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user5;
+-----+----------+
| id  | username |
+-----+----------+
| 111 | KING1    |
| 112 | AAAA     |
+-----+----------+
2 rows in set (0.01 sec)

mysql>
mysql> INSERT user5 VALUES(DEFAULT,'AAAA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user5;
+-----+----------+
| id  | username |
+-----+----------+
| 111 | KING1    |
| 112 | AAAA     |
| 113 | AAAA     |
+-----+----------+
3 rows in set (0.00 sec)
mysql> INSERT user5(username) VALUES('queen1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user5;
+-----+----------+
| id  | username |
+-----+----------+
| 111 | KING1    |
| 112 | AAAA     |
| 113 | AAAA     |
| 114 | queen1   |
+-----+----------+
4 rows in set (0.00 sec)

自增长就是：只赋予值的时候，编号会自动增长。
设定编号起始点

mysql> CREATE TABLE IF NOT EXISTS user6(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT user6(username) VALUES('queen1');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user6;
+-----+----------+
| id  | username |
+-----+----------+
| 100 | queen1   |
+-----+----------+
1 row in set (0.00 sec)

修改自增长的值

mysql> ALTER TABLE user6 AUTO_INCREMENT =500;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user6;
+-----+----------+
| id  | username |
+-----+----------+
| 100 | queen1   |
+-----+----------+
1 row in set (0.00 sec)

mysql> INSERT user6(username) VALUES('queen2');
Query OK, 1 row affected (0.05 sec)

mysql> select * from user6;
+-----+----------+
| id  | username |
+-----+----------+
| 100 | queen1   |
| 500 | queen2   |
+-----+----------+
2 rows in set (0.00 sec)

非空

非空一般和默认值一起使用。

mysql> CREATE TABLE IF NOT EXISTS user7(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| password | char(32)            | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

mysql> INSERT user7(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user7;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
+----+----------+----------+------+
1 row in set (0.00 sec)

mysql> INSERT user7(username,password,age) VALUES('king1','king2',12);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user7;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
|  2 | king1    | king2    |   12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)

默认值DEFAULT

mysql> CREATE TABLE IF NOT EXISTS user8(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> addr VARCHAR(50) NOT NULL DEFAULT '北京',
    -> sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user8;
+----------+------------------------+------+-----+---------+----------------+
| Field    | Type                   | Null | Key | Default | Extra          |
+----------+------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned       | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)            | NO   |     | NULL    |                |
| password | char(32)               | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned    | YES  |     | 18      |                |
| addr     | varchar(50)            | NO   |     | 北京        |                |
| sex      | enum('男','女','保密')        | NO   |     | 男        |                |
+----------+------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> INSERT user8(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT user8 VALUES(2,'QUEEN','QUEEN',29,'上海','保密');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT user8 VALUES(3,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');
Query OK, 1 row affected (0.01 sec)

唯一性 UNIQUE KET

mysql> CREATE TABLE IF NOT EXISTS user9(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> card CHAR(18) UNIQUE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT user9(username) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user9(username,card) VALUES('B','111');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT user9(username,card) VALUES('B1',NULL);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT user9(username,card) VALUES('B2',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user9(username,card) VALUES('B',NULL);
ERROR 1062 (23000): Duplicate entry 'B' for key 'username'

建立重复的ID会报错。
如何建立表格?
CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称 字段类型 [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [PRIMARY] KEY | UNIQUE[KEY] [AUTO_INCREMENT ])ENGINE=引擎名称 CHARSET=‘编码方式’ AUTO_INCREMENT=100;