1、数据表插入记录(标准)
(1)、形式一
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sex BOOLEAN
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
将自动编号的值设置为NULL,会执行自增。
mysql> INSERT users VALUES(NULL, 'Tom', '123', 25, 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT users VALUES(NULL, 'John', '456', 25, 1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
将自动编号的值设置为DEFAULT,也会执行自增。
mysql> INSERT users VALUES(DEFAULT, 'Tom', '123', 25, 1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | Tom | 123 | 25 | 1 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)
如果省略了列名的话,要一次的赋值,缺少某一项会报错如下:
mysql> INSERT users VALUES(NULL, 'John', '456', 25);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
(2)形式二( 表达式与默认值)
mysql> INSERT users VALUES(DEFAULT, 'Tom', '123', 2*5+8, 1);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Tom | 123 | 18 | 1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)
mysql> INSERT users VALUES(DEFAULT, 'Tom', '123', DEFAULT, 1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Tom | 123 | 18 | 1 |
| 5 | Tom | 123 | 10 | 1 |
+----+----------+----------+-----+------+
5 rows in set (0.00 sec)
(3)形式三 (一次写入多值)
mysql> INSERT users VALUES(DEFAULT, 'Tom', '123', 3*5+8, 1),(NULL, 'Rose', md5('123'), DEFAULT, 0);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Tom | 123 | 18 | 1 |
| 5 | Tom | 123 | 10 | 1 |
| 6 | Tom | 123 | 23 | 1 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
2、数据表插入记录(set 方式)
说明:和前面一种方式的区别在于,此方式可以使用子查询(SubQuery),并且只能一次插入一条记录。
mysql> INSERT users SET username='Ben',password='456';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Tom | 123 | 18 | 1 |
| 5 | Tom | 123 | 10 | 1 |
| 6 | Tom | 123 | 23 | 1 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 0 |
| 8 | Ben | 456 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
3、数据表插入记录 (select 方式)
说明:此方法可以将查询结果插入到指定的数据表中。
INSERT tbl_name[col_name] SELECT……
将查询结果插入表中(后面再讲)
4、单表更新记录update
单个字段更新:
mysql> UPDATE users set age = age + 5;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 30 | 1 |
| 2 | John | 456 | 30 | 1 |
| 3 | Tom | 123 | 30 | 1 |
| 4 | Tom | 123 | 23 | 1 |
| 5 | Tom | 123 | 15 | 1 |
| 6 | Tom | 123 | 28 | 1 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 15 | 0 |
| 8 | Ben | 456 | 15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
多个字段更新:
mysql> UPDATE users set age = age - id, sex = 0;
Query OK, 8 rows affected (0.02 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 28 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Tom | 123 | 19 | 0 |
| 5 | Tom | 123 | 10 | 0 |
| 6 | Tom | 123 | 22 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 7 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
有条件地更新字段:
mysql> UPDATE users set age = age + 10 WHERE id % 2 = 0;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Tom | 123 | 29 | 0 |
| 5 | Tom | 123 | 10 | 0 |
| 6 | Tom | 123 | 32 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
5、删除记录(单表删除)
mysql> DELETE FROM users WHERE id = 6;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Tom | 123 | 29 | 0 |
| 5 | Tom | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+---------
当记录被删除之后,新增数据表的id号是当前最大的id号加1的值。
mysql> INSERT users VALUES(NULL, '111', '222', 33, NULL);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Tom | 123 | 29 | 0 |
| 5 | Tom | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | 111 | 222 | 33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
6、MySQL查询表达式解析
mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
指定要查询的数据列,多个列用英文的逗号隔开:
mysql> SELECT id,username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Tom |
| 5 | Tom |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+----+----------+
8 rows in set (0.00 sec)
*表示所有的列,可以在字段前加上数据表的名字。tbl_name.*
mysql> SELECT users.id, users.username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Tom |
| 5 | Tom |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+----+----------+
8 rows in set (0.00 sec)
查询表达式可以使用[AS] alias_name为其赋予别名,别名可以用GROUP BY, ORDRE BY 或 HAVING子句。
mysql> SELECT id AS userId, username AS uname FROM users;
+--------+-------+
| userId | uname |
+--------+-------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Tom |
| 5 | Tom |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+--------+-------+
8 rows in set (0.00 sec)
两个影响:
1、select 字段出现的顺序,将影响结果集出现的顺序。
2、字段的别名也将影响结果集中字段的别名。
这两个影响在以后的PHP中比较的重要。
如果不写AS也可以,将id改为别名username.
mysql> SELECT id username FROM users;
+----------+
| username |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
+----------+
8 rows in set (0.00 sec)
写AS比较的保险(强烈建议推荐使用):
mysql> SELECT id AS username FROM users;
+----------+
| username |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
+----------+
8 rows in set (0.00 sec)