Mysql Day 4 数据表的增、删、改、查

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)

上一篇:Pandas系列(十)-转换连接详解


下一篇:[PAT乙级]1017 A除以B