データベース:整合性制約
71825 ワード
文書ディレクトリ
1.整合性制約試験
1.1整合性制約条件
PRIMARY KEY
AUTO_INCREMENT
FOREIGN KEY
NOT NULL
UNIQUE KEY
DEFAULT
1.1.1プライマリ・キー
≪プライマリ・キー|Primary Key|ldap≫:一意の識別子.ID番号と個人の関係は、プライマリ・キーとレコードの関係に相当します.一般的には、無意味なフィールドに追加されます.たとえば、番号フィールド定義プライマリ・キーフィールドの要件:重複できない、プライマリ・キーとしてマークされたフィールドは、単一フィールド・プライマリ・キーとマルチフィールド・プライマリ・キーに自動的に非空に分割されます.--
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)
DESC user1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
--
SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`id` int(11) NOT NULL,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
--
INSERT user1 VALUES(2,'QUEEN');
Query OK, 1 row affected (0.01 sec)
INSERT user1 VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
INSERT user1 VALUES(1,'KING');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
--
SELECT * FROM user1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 2 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
-- id 1
SELECT * FROM user1 WHERE id=1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
+----+----------+
1 row in set (0.01 sec)
--
mysql> CREATE TABLE IF NOT EXISTS userb(
-> id INT,
-> username VARCHAR(20),
-> card CHAR(18),
-> PRIMARY KEY (id,card)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC userb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| username | varchar(20) | YES | | NULL | |
| card | char(18) | NO | PRI | | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
-- ( , )
mysql> INSERT userb VALUES(1,'KING','111');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT userb VALUES(1,'QUEEN','112');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userb VALUES(1,'PRINCESS','112');
ERROR 1062 (23000): Duplicate entry '1-112' for key 'PRIMARY'
mysql> SELECT * FROM userb;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | KING | 111 |
| 1 | QUEEN | 112 |
+----+----------+------+
2 rows in set (0.00 sec)
1.1.2自己成長
1つのテーブルに1つの自己成長フィールドしか存在せず、プライマリ・キーと組み合わせて使用するには、自己成長として識別されるフィールドはプライマリ・キーである必要がありますが、プライマリ・キーが自己成長としてマークされるとは限りません.整数の整数列にのみ効果があります.文字列のようにデフォルトは1から1を追加するたびに意味がありません.mysql> CREATE TABLE IF NOT EXISTS userc(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)
mysql> DESC userc;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
--- 1
mysql> INSERT userc VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userc(username) VALUES('QUEEN');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 2 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
-- id
mysql> INSERT userc VALUES(111,'KING1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
+-----+----------+
3 rows in set (0.00 sec)
--
mysql> INSERT userc(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
| 112 | QUEEN1 |
+-----+----------+
4 rows in set (0.00 sec)
--
mysql> SHOW CREATE TABLE userc;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userc | CREATE TABLE `userc` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- NULL DEFAULT
mysql> INSERT userc VALUES(NULL,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userc VALUES(DEFAULT,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
| 112 | QUEEN1 |
| 113 | ABC |
| 114 | ABC |
+-----+----------+
6 rows in set (0.00 sec)
---
mysql> CREATE TABLE IF NOT EXISTS userd(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE userd;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userd | CREATE TABLE `userd` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT userd(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userd;
+-----+----------+
| id | username |
+-----+----------+
| 100 | QUEEN1 |
+-----+----------+
1 row in set (0.00 sec)
--
ALTER TABLE userd AUTO_INCREMENT =500;
1.1.3テスト非空NOT NULL
-- ( )
mysql> CREATE TABLE IF NOT EXISTS userf(
-> 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.05 sec)
mysql> INSERT userf(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT userf(username,password,age) VALUES('KING1','KING1',12);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *FROM userf;
+----+----------+----------+------+
| id | username | password | age |
+----+----------+----------+------+
| 1 | KING | KING | NULL |
| 2 | KING1 | KING1 | 12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)
--
mysql> INSERT userf(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null
1.1.4デフォルトDEFAULTのテスト
アイコンはデフォルトの画像を設定できますCREATE TABLE IF NOT EXISTS userg(
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 ' '
);
INSERT userg(username,password) VALUES('KING','KING');
mysql> SELECT * FROM userg;
+----+----------+----------+------+------+-----+
| id | username | password | age | addr | sex |
+----+----------+----------+------+------+-----+
| 1 | KING | KING | 18 | | |
+----+----------+----------+------+------+-----+
--
INSERT userg VALUES(3,'QUEEN','QUEEN',29,' ',' ');
INSERT userg VALUES(4,'QUEEN','QUEEN',DEFAULT,DEFAULT,' ');
1.1.5唯一UNIQUE KEYをテストする
フィールドが重複できない場合に使用CREATE TABLE IF NOT EXISTS userj(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
mysql> DESC userj;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | UNI | NULL | |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> SHOW CREATE TABLE userj;
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| userj | CREATE TABLE `userj` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT userj(username) VALUES('A');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userj(username) VALUES('A1');
Query OK, 1 row affected (0.00 sec)
--NULL
mysql> SELECT * FROM userj;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | A | NULL |
| 2 | A1 | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
-- username
mysql> INSERT userj(username) VALUES('A1');
ERROR 1062 (23000): Duplicate entry 'A1' for key 'username'
mysql> INSERT user9(username,card) VALUES('B','111');
Query OK, 1 row affected (0.00 sec)
--NULL
mysql> INSERT userj(username,card) VALUES('B1',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userj(username,card) VALUES('B2',NULL);
Query OK, 1 row affected (0.00 sec)
1.1.6総括
CREATE TABLE [IF NOT EXISTS] tbl_name(
[UNSIGNED|ZEROFILL] [NULL|NOTNULL] [DEFAULT ] [[PRIMARY] KEY| UNIQUE[KEY]] [AUTO_INCREMENT ]
)ENGINE=INNODB, CHARSET=utf8, AUTO_INCREMENT=100;
--
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)
DESC user1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
--
SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`id` int(11) NOT NULL,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
--
INSERT user1 VALUES(2,'QUEEN');
Query OK, 1 row affected (0.01 sec)
INSERT user1 VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
INSERT user1 VALUES(1,'KING');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
--
SELECT * FROM user1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 2 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
-- id 1
SELECT * FROM user1 WHERE id=1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
+----+----------+
1 row in set (0.01 sec)
--
mysql> CREATE TABLE IF NOT EXISTS userb(
-> id INT,
-> username VARCHAR(20),
-> card CHAR(18),
-> PRIMARY KEY (id,card)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC userb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| username | varchar(20) | YES | | NULL | |
| card | char(18) | NO | PRI | | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
-- ( , )
mysql> INSERT userb VALUES(1,'KING','111');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT userb VALUES(1,'QUEEN','112');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userb VALUES(1,'PRINCESS','112');
ERROR 1062 (23000): Duplicate entry '1-112' for key 'PRIMARY'
mysql> SELECT * FROM userb;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | KING | 111 |
| 1 | QUEEN | 112 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS userc(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)
mysql> DESC userc;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
--- 1
mysql> INSERT userc VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userc(username) VALUES('QUEEN');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 2 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
-- id
mysql> INSERT userc VALUES(111,'KING1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
+-----+----------+
3 rows in set (0.00 sec)
--
mysql> INSERT userc(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
| 112 | QUEEN1 |
+-----+----------+
4 rows in set (0.00 sec)
--
mysql> SHOW CREATE TABLE userc;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userc | CREATE TABLE `userc` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- NULL DEFAULT
mysql> INSERT userc VALUES(NULL,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userc VALUES(DEFAULT,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | QUEEN |
| 111 | KING1 |
| 112 | QUEEN1 |
| 113 | ABC |
| 114 | ABC |
+-----+----------+
6 rows in set (0.00 sec)
---
mysql> CREATE TABLE IF NOT EXISTS userd(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE userd;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userd | CREATE TABLE `userd` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT userd(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userd;
+-----+----------+
| id | username |
+-----+----------+
| 100 | QUEEN1 |
+-----+----------+
1 row in set (0.00 sec)
--
ALTER TABLE userd AUTO_INCREMENT =500;
-- ( )
mysql> CREATE TABLE IF NOT EXISTS userf(
-> 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.05 sec)
mysql> INSERT userf(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT userf(username,password,age) VALUES('KING1','KING1',12);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *FROM userf;
+----+----------+----------+------+
| id | username | password | age |
+----+----------+----------+------+
| 1 | KING | KING | NULL |
| 2 | KING1 | KING1 | 12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)
--
mysql> INSERT userf(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null
CREATE TABLE IF NOT EXISTS userg(
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 ' '
);
INSERT userg(username,password) VALUES('KING','KING');
mysql> SELECT * FROM userg;
+----+----------+----------+------+------+-----+
| id | username | password | age | addr | sex |
+----+----------+----------+------+------+-----+
| 1 | KING | KING | 18 | | |
+----+----------+----------+------+------+-----+
--
INSERT userg VALUES(3,'QUEEN','QUEEN',29,' ',' ');
INSERT userg VALUES(4,'QUEEN','QUEEN',DEFAULT,DEFAULT,' ');
CREATE TABLE IF NOT EXISTS userj(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
mysql> DESC userj;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | UNI | NULL | |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> SHOW CREATE TABLE userj;
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| userj | CREATE TABLE `userj` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT userj(username) VALUES('A');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userj(username) VALUES('A1');
Query OK, 1 row affected (0.00 sec)
--NULL
mysql> SELECT * FROM userj;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | A | NULL |
| 2 | A1 | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
-- username
mysql> INSERT userj(username) VALUES('A1');
ERROR 1062 (23000): Duplicate entry 'A1' for key 'username'
mysql> INSERT user9(username,card) VALUES('B','111');
Query OK, 1 row affected (0.00 sec)
--NULL
mysql> INSERT userj(username,card) VALUES('B1',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userj(username,card) VALUES('B2',NULL);
Query OK, 1 row affected (0.00 sec)
CREATE TABLE [IF NOT EXISTS] tbl_name(
[UNSIGNED|ZEROFILL] [NULL|NOTNULL] [DEFAULT ] [[PRIMARY] KEY| UNIQUE[KEY]] [AUTO_INCREMENT ]
)ENGINE=INNODB, CHARSET=utf8, AUTO_INCREMENT=100;