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.40 sec)
-
INSERT (插入记录)
- 方法一：INSERT...VALUES...
mysql> INSERT users VALUES(NULL,'Tom','123',25,1); //在表users中插入一条记录
Query OK, 1 row affected (2.44 sec)
mysql> SELECT * FROM users; //查询表users的记录
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
+----+----------+----------+-----+------+
1 row in set (0.07 sec)
mysql> INSERT users VALUES(NULL,'John','456',20,1);
Query OK, 1 row affected (2.42 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> INSERT users VALUES(DEFAULT,'Tom','123',25,1);
Query OK, 1 row affected (0.13 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
| 3 | Tom | 123 | 25 | 1 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)
mysql> INSERT users VALUES(NULL,'John','456',20); //此时插入数值与所给列的数目不同，报错
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT users VALUES(DEFAULT,'Mary','123',3*7-5,2); //插入内容中可以使用算式
Query OK, 1 row affected (0.19 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Mary | 123 | 16 | 2 |
+----+----------+----------+-----+------+
4 rows in set (0.00 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.11 sec)
mysql> INSERT users VALUES(DEFAULT,'Mary','123',DEFAULT,2); //之前DEFAULT 赋值为10，可以直接作为一个值使用
Query OK, 1 row affected (0.46 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Mary | 123 | 16 | 2 |
| 5 | Mary | 123 | 10 | 2 |
+----+----------+----------+-----+------+
5 rows in set (0.00 sec)
mysql> INSERT users VALUES(DEFAULT,'Mary','123',3*7-5,2),(NULL,'Rose',md5('123'),DEFAULT,0); //可以同时赋两个值，可以使用数学表达式
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Mary | 123 | 16 | 2 |
| 5 | Mary | 123 | 10 | 2 |
| 6 | Mary | 123 | 16 | 2 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
- 方法2：INSERT ... SET ...
mysql> INSERT users SET username = 'Ben',password = '456';
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 20 | 1 |
| 3 | Tom | 123 | 25 | 1 |
| 4 | Mary | 123 | 16 | 2 |
| 5 | Mary | 123 | 10 | 2 |
| 6 | Mary | 123 | 16 | 2 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 0 |
| 8 | Ben | 456 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
-
UPDATE 更新记录（单表更新）
mysql> UPDATE users SET age = age + 5;
Query OK, 8 rows affected (0.14 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 | 25 | 1 |
| 3 | Tom | 123 | 30 | 1 |
| 4 | Mary | 123 | 21 | 2 |
| 5 | Mary | 123 | 15 | 2 |
| 6 | Mary | 123 | 21 | 2 |
| 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; //年龄=年龄-id，性别为0
Query OK, 8 rows affected (2.49 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 | 23 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 17 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 6 | Mary | 123 | 15 | 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; //id为偶数时年龄加10
Query OK, 4 rows affected (2.11 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 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 6 | Mary | 123 | 25 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
-
DELETE 删除记录（单表删除）
mysql> DELETE FROM users WHERE id = 6; //删除id为6的记录
Query OK, 1 row affected (2.43 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.04 sec)
mysql> INSERT users VALUES(NULL,'111','113',10,2); //删除6号之后再添加记录则编号不会为6
Query OK, 1 row affected (0.51 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | 111 | 113 | 10 | 2 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 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)
-
SELECT 查找记录
mysql> SELECT id,username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Mary |
| 5 | Mary |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+----+----------+
8 rows in set (0.00 sec)
mysql> SELECT username,id FROM users;
+----------+----+
| username | id |
+----------+----+
| Tom | 1 |
| John | 2 |
| Tom | 3 |
| Mary | 4 |
| Mary | 5 |
| Rose | 7 |
| Ben | 8 |
| 111 | 9 |
+----------+----+
8 rows in set (0.00 sec)
mysql> SELECT users.id,users.username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Mary |
| 5 | Mary |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+----+----------+
8 rows in set (0.00 sec)
mysql> SELECT id AS userid,username AS uname FROM users; //修改名字用AS
+--------+-------+
| userid | uname |
+--------+-------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | Mary |
| 5 | Mary |
| 7 | Rose |
| 8 | Ben |
| 9 | 111 |
+--------+-------+
8 rows in set (0.00 sec)
mysql> SELECT id username FROM users; //不使用AS也可以，但是容易误解
+----------+
| username |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
+----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | 111 | 113 | 10 | 2 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY sex; //对sex分组
+------+
| sex |
+------+
| 0 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY 1 HAVING count(id) >=2;
+------+
| sex |
+------+
| 0 |
+------+
1 row in set (2.27 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | 111 | 113 | 10 | 2 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY id DESC; //id降序排列，DESC为降序，ORDER BY为排序
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 9 | 111 | 113 | 10 | 2 |
| 8 | Ben | 456 | 17 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 2 | John | 456 | 33 | 0 |
| 1 | Tom | 123 | 29 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY age;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 9 | 111 | 113 | 10 | 2 |
| 8 | Ben | 456 | 17 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY age ,id DESC; //年龄升序，id降序
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 9 | 111 | 113 | 10 | 2 |
| 5 | Mary | 123 | 10 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | 111 | 113 | 10 | 2 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
- LIMIT 限制查询结果的返回数量
mysql> SELECT * FROM users LIMIT 2; //返回前两条数据
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 33 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 3,2; //返回从第3条起的两条数据
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 4 | Mary | 123 | 27 | 0 |
| 5 | Mary | 123 | 10 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 2,2; //返回从第2条起的两条数据
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 3 | Tom | 123 | 27 | 0 |
| 4 | Mary | 123 | 27 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 9 | 111 | 113 | 10 | 2 |
| 8 | Ben | 456 | 17 | 0 |
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | Mary | 123 | 10 | 0 |
| 4 | Mary | 123 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 2 | John | 456 | 33 | 0 |
| 1 | Tom | 123 | 29 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY id DESC LIMIT 2,2 ;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | Mary | 123 | 10 | 0 |
+----+----------+----------------------------------+-----+------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE test(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.80 sec)
mysql> SELECT * FROM test;
Empty set (0.00 sec)
- INSERT 方法3：将查询结果插入到指定数据表
- INSERT ... SELECT ...
mysql> INSERT test(username) SELECT username FROM users WHERE age >= 30;
Query OK, 1 row affected (2.42 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
+----+----------+
1 row in set (0.00 sec)