插入数据:INSERT
INSERT [INTO] table_name (列1, 列2,...) VALUES (值1, 值2,....),()....;
创建一个表users,有一下属性:
mysql> CREATE TABLE users(
-> int SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sec BOOLEAN
-> );
插入两组数据,形成一下表
mysql> INSERT users VALUES (NULL,'Tom' ,'123',25,1);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
+----+----------+----------+-----+------+
除了用NULL,增加主键的自动编号,还可以用DEFAULT,数字除了直接用还可以用算数表达式,默认约束也可以用DEFAULT 表示:
mysql> INSERT users VALUES (DEFAULT,'John' ,'456',25,1);
mysql> INSERT users VALUES (NULL,'John' ,'456',3*7-5,1);
还可以插入两条属性:有一个有意思的东西,我试验的,一并分享出来:
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
+----+----------+----------+-----+------+
可以一次性写入多个记录
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(NULL,'ASD','234',34,1);
还可以使用insert..........set的方式插入,但每次只能插入一条记录