MySQL数据表中的记录操作

一、更新记录（单表更新）

update [low_priority ] [ignore] table_reference set col_name1={expr1} [,col_name2={expr2|default}]....[where where_condition];

如果更新是不加where条件，则会更新表中的所有列；

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   23 |    1 |
|  2 | JJom     |   23 |    2 |
|  3 | rJom     |   23 |    2 |
+----+----------+------+------+
3 rows in set (0.00 sec)

mysql> update users set age = age + 5;
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   28 |    1 |
|  2 | JJom     |   28 |    2 |
|  3 | rJom     |   28 |    2 |
+----+----------+------+------+
3 rows in set (0.01 sec)

mysql> update users set age = age - id,sex = 0;
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   27 |    0 |
|  2 | JJom     |   26 |    0 |
|  3 | rJom     |   25 |    0 |
+----+----------+------+------+
3 rows in set (0.00 sec)

mysql> update users set age = age + 10 where id%2 = 0;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   27 |    0 |
|  2 | JJom     |   36 |    0 |
|  3 | rJom     |   25 |    0 |
+----+----------+------+------+
3 rows in set (0.00 sec)

mysql>

二、删除记录（单表删除）

delete from ta_name [where where_condition];

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   27 |    0 |
|  2 | JJom     |   36 |    0 |
|  3 | rJom     |   25 |    0 |
+----+----------+------+------+
3 rows in set (0.00 sec)

mysql> delete from users where id = 2;
Query OK, 1 row affected (0.07 sec)

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   27 |    0 |
|  3 | rJom     |   25 |    0 |
+----+----------+------+------+
2 rows in set (0.00 sec)

三、查找记录

查询表达式：

每一个表达式表示想要的一列，必须有至少一个。

多个列之间以逗号分隔。

星号（*）表示所有列。tb_name.*可以表示命名表中的所有列。

查询表达式可以使用 [AS] alias_name 为其赋予别名。

别名可用于group by ,order by 或者 having 子句。

mysql> select * from users;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | Tom      |   27 |    0 |
|  3 | rJom     |   25 |    0 |
|  4 | jarry    |   12 |    1 |
+----+----------+------+------+
3 rows in set (0.00 sec)

mysql> select id,username from users;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  3 | rJom     |
|  4 | jarry    |
+----+----------+
3 rows in set (0.00 sec)

mysql> select users.id,users.age from users;
+----+------+
| id | age  |
+----+------+
|  1 |   27 |
|  3 |   25 |
|  4 |   12 |
+----+------+
3 rows in set (0.00 sec)

mysql> select id as uid,username as uname from users;
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | Tom   |
|   3 | rJom  |
|   4 | jarry |
+-----+-------+
3 rows in set (0.00 sec)

mysql>

select select_expr [,select_expr...] [

         from table_references

         [where where_condition]

         [group by {col_name | position} [asc | desc],.....]

         [having where_condition]

         [order by {col_name | expr | position} [asc | desc],.....]

         [limit {[offset,] row_count | row_count offset offset}]

]

where条件表达式：

对记录进行过滤，如果没有指定where子句，则显示所有记录。

在where表达式中，可以使用MySql支持的函数或运算符。

查询结果分组： [group by {col_name | position} [asc | desc],.....]

group by;默认ASC升序，desc为降序；后面需要加上列名称；

mysql> select sex from users group by sex;
+------+
| sex  |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.01 sec)

分组条件： [having where_condition]

如果新加列分组条件，需要在select后必须出现此字段；如果采用聚合函数avg()、sum()、min()、max()、count()，则不需要在select后出现此字段；

mysql> select sex,age from users group by sex having age > 10;
+------+------+
| sex  | age  |
+------+------+
|    0 |   27 |
|    1 |   12 |
+------+------+
2 rows in set (0.00 sec)

mysql> select sex from users group by sex having max(age) > 22;
+------+
| sex  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql>

对查询结果进行排序：ORDER BY； [order by {col_name | expr | position} [asc | desc],.....]

默认是升序ASC，DESC是降序；可以允许多种条件进行排序，以逗号分隔；

mysql> select * from users order by age;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  4 | jarry    |   12 |    1 |
|  8 | qq       |   12 |    2 |
|  7 | qq       |   22 |    2 |
|  3 | rJom     |   25 |    0 |
|  1 | Tom      |   27 |    0 |
|  5 | qq       |   32 |    2 |
|  6 | qq       |   32 |    2 |
+----+----------+------+------+
7 rows in set (0.00 sec)

mysql> select * from users order by age ,id desc;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  8 | qq       |   12 |    2 |
|  4 | jarry    |   12 |    1 |
|  7 | qq       |   22 |    2 |
|  3 | rJom     |   25 |    0 |
|  1 | Tom      |   27 |    0 |
|  6 | qq       |   32 |    2 |
|  5 | qq       |   32 |    2 |
+----+----------+------+------+
7 rows in set (0.00 sec)

限制返回的数量：LIMIT： [limit {[offset,] row_count | row_count offset offset}]；

如果limit 后只加一位数，为限制的数量；如果加两个数，中间用逗号隔开，则返回的记录是表中从上到下（位置从0开始），数字位置之间的记录。

mysql> select * from users order by id desc;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  8 | qq       |   12 |    2 |
|  7 | qq       |   22 |    2 |
|  6 | qq       |   32 |    2 |
|  5 | qq       |   32 |    2 |
|  4 | jarry    |   12 |    1 |
|  3 | rJom     |   25 |    0 |
|  1 | Tom      |   27 |    0 |
+----+----------+------+------+
7 rows in set (0.00 sec)

mysql> select * from users order by id desc limit 1,2;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  7 | qq       |   22 |    2 |
|  6 | qq       |   32 |    2 |
+----+----------+------+------+
2 rows in set (0.00 sec)

mysql>

四、插入记录：

insert [into] tb_name SET col_name={expr | DEFAULT},......;

此方法可以使用子查询（SubQuery）；

mysql> create table test(
    -> id tinyint unsigned primary key auto_increment,
    -> username varchar(20) not null);
Query OK, 0 rows affected (0.14 sec)

mysql> insert test select username from users where age >= 25;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert test(username) select username from users where age >= 25;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | rJom     |
|  3 | qq       |
|  4 | qq       |
+----+----------+
4 rows in set (0.00 sec)