MySQL中对于数据的相关操作

1. 插入完整数据

insert into <表名>(字段名1,字段名2...) values(值1,值2...)

mysql> insert into student(name,phone) values('张三','111111111');
Query OK, 1 row affected (0.00 sec)

2. 查询表中所有的数据

select * from 表名

mysql> select * from employee;
+-----+--------+----------+---------------+-------------+
| _id | name   | salary   | department    | phone       |
+-----+--------+----------+---------------+-------------+
|   1 | 张三   |    10000 | 国防部        | 13888888888 |
|   2 | 王五   | 56454564 | android开发   | 845664468   |
|   3 | 李四   |      800 | 程序员        | 66666666666 |
|   4 | tom    |  1111800 | 测试          | 44444444444 |
|   5 | jerry  |  1111800 | 产品经理      | 22222222222 |
|   6 | cindy  |    15550 | boss          | 88888888888 |
|   7 | vscode |  5645464 | client        | 845664468   |
+-----+--------+----------+---------------+-------------+
7 rows in set (0.00 sec)

3. 如果我们添加全部字段的数据，那么我们可以省略前面字段的名称

insert into <表名> values(值1,值2,...)

mysql> insert into student values(5,"李四","22222222");
Query OK, 1 row affected (0.00 sec)

4. 插入部分数据，字段和这个值对应即可

mysql> insert into student(name) values("cindy");
Query OK, 1 row affected (0.00 sec)

5. 修改数据

update <表名> set <字段>=<值> <条件where>

mysql> update employee set department="boss" where department="boos";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6. 当where为空的时候，不能使用=null，而是需要使用is null

update student set age = 22 where phone is null;

7. 删除表中的数据

delete from <表名>  <where条件>

8. 数据表中数据的详细查询

where条件:

• select * from <表名> [where 条件]

mysql> select * from employee where _id = 2;
+-----+--------+----------+---------------+-----------+
| _id | name   | salary   | department    | phone     |
+-----+--------+----------+---------------+-----------+
|   2 | 王五   | 56454564 | android开发   | 845664468 |
+-----+--------+----------+---------------+-----------+
1 row in set (0.00 sec)

mysql> select * from employee where salary is not null;
+-----+--------+----------+---------------+-------------+
| _id | name   | salary   | department    | phone       |
+-----+--------+----------+---------------+-------------+
|   1 | 张三   |    10000 | 国防部        | 13888888888 |
|   2 | 王五   | 56454564 | android开发   | 845664468   |
|   3 | 李四   |      800 | 程序员        | 66666666666 |
|   4 | tom    |  1111800 | 测试          | 44444444444 |
|   5 | jerry  |  1111800 | 产品经理      | 22222222222 |
|   6 | cindy  |    15550 | boss          | 88888888888 |
|   7 | vscode |  5645464 | client        | 845664468   |
+-----+--------+----------+---------------+-------------+
7 rows in set (0.00 sec)

mysql> select * from employee where _id > 4;
+-----+--------+---------+--------------+-------------+
| _id | name   | salary  | department   | phone       |
+-----+--------+---------+--------------+-------------+
|   5 | jerry  | 1111800 | 产品经理     | 22222222222 |
|   6 | cindy  |   15550 | boss         | 88888888888 |
|   7 | vscode | 5645464 | client       | 845664468   |
+-----+--------+---------+--------------+-------------+
3 rows in set (0.00 sec)

• 查询_id在10~14之间的(两种方法 and 或 between and)
  但是between and 前面的数值，一定要小于后面的数值

mysql> select * from employee where _id >= 3 and _id <= 6;
+-----+--------+---------+--------------+-------------+
| _id | name   | salary  | department   | phone       |
+-----+--------+---------+--------------+-------------+
|   3 | 李四   |     800 | 程序员       | 66666666666 |
|   4 | tom    | 1111800 | 测试         | 44444444444 |
|   5 | jerry  | 1111800 | 产品经理     | 22222222222 |
|   6 | cindy  |   15550 | boss         | 88888888888 |
+-----+--------+---------+--------------+-------------+
4 rows in set (0.00 sec)

mysql> select * from employee where _id between 3 and 6;
+-----+--------+---------+--------------+-------------+
| _id | name   | salary  | department   | phone       |
+-----+--------+---------+--------------+-------------+
|   3 | 李四   |     800 | 程序员       | 66666666666 |
|   4 | tom    | 1111800 | 测试         | 44444444444 |
|   5 | jerry  | 1111800 | 产品经理     | 22222222222 |
|   6 | cindy  |   15550 | boss         | 88888888888 |
+-----+--------+---------+--------------+-------------+
4 rows in set (0.00 sec)

• 把_id为1、5、7的挑选出来（where in 或 or）

mysql> select * from employee where _id in(1,5,7);
+-----+--------+---------+--------------+-------------+
| _id | name   | salary  | department   | phone       |
+-----+--------+---------+--------------+-------------+
|   1 | 张三   |   10000 | 国防部       | 13888888888 |
|   5 | jerry  | 1111800 | 产品经理     | 22222222222 |
|   7 | vscode | 5645464 | client       | 845664468   |
+-----+--------+---------+--------------+-------------+
3 rows in set (0.00 sec)

mysql> select * from employee where _id = 1 or _id = 5 or _id =7;
+-----+--------+---------+--------------+-------------+
| _id | name   | salary  | department   | phone       |
+-----+--------+---------+--------------+-------------+
|   1 | 张三   |   10000 | 国防部       | 13888888888 |
|   5 | jerry  | 1111800 | 产品经理     | 22222222222 |
|   7 | vscode | 5645464 | client       | 845664468   |
+-----+--------+---------+--------------+-------------+
3 rows in set (0.00 sec)

• select 字段,字段... from <表名> [where 条件]

这个方法适用于有些字段是我们不需要看到的

mysql> select name,department from employee;
+--------+---------------+
| name   | department    |
+--------+---------------+
| 张三   | 国防部        |
| 王五   | android开发   |
| 李四   | 程序员        |
| tom    | 测试          |
| jerry  | 产品经理      |
| cindy  | boss          |
| vscode | client        |
+--------+---------------+
7 rows in set (0.00 sec)

• select 字段,字段... from <表名> order by <字段名> [<asc>或<desc>]

asc升序，desc降序

mysql> select * from employee order by salary asc;
+-----+--------+----------+---------------+-------------+
| _id | name   | salary   | department    | phone       |
+-----+--------+----------+---------------+-------------+
|   3 | 李四   |      800 | 程序员        | 66666666666 |
|   1 | 张三   |    10000 | 国防部        | 13888888888 |
|   6 | cindy  |    15550 | boss          | 88888888888 |
|   4 | tom    |  1111800 | 测试          | 44444444444 |
|   5 | jerry  |  1111800 | 产品经理      | 22222222222 |
|   7 | vscode |  5645464 | client        | 845664468   |
|   2 | 王五   | 56454564 | android开发   | 845664468   |
+-----+--------+----------+---------------+-------------+
7 rows in set (0.00 sec)

mysql> select * from employee order by department desc;
+-----+--------+----------+---------------+-------------+
| _id | name   | salary   | department    | phone       |
+-----+--------+----------+---------------+-------------+
|   3 | 李四   |      800 | 程序员        | 66666666666 |
|   4 | tom    |  1111800 | 测试          | 44444444444 |
|   1 | 张三   |    10000 | 国防部        | 13888888888 |
|   5 | jerry  |  1111800 | 产品经理      | 22222222222 |
|   7 | vscode |  5645464 | client        | 845664468   |
|   6 | cindy  |    15550 | boss          | 88888888888 |
|   2 | 王五   | 56454564 | android开发   | 845664468   |
+-----+--------+----------+---------------+-------------+
7 rows in set (0.00 sec)

• select 字段 as 别名,字段 as 别名,... from <表名> where 条件

别名，别名的好处就是我们可以根据自己的喜好来显示字段

mysql> select name as "名字",department as "部门" from employee;
+--------+---------------+
| 名字   | 部门          |
+--------+---------------+
| 张三   | 国防部        |
| 王五   | android开发   |
| 李四   | 程序员        |
| tom    | 测试          |
| jerry  | 产品经理      |
| cindy  | boss          |
| vscode | client        |
+--------+---------------+
7 rows in set (0.00 sec)

9. 模糊查询

select * from <表名> where <字段名> like <关键字%>

”%“为占位符

mysql> select * from employee where name like "张%";
+-----+--------+----------+------------+-------------+
| _id | name   | salary   | department | phone       |
+-----+--------+----------+------------+-------------+
|   1 | 张三   |    10000 | 国防部     | 13888888888 |
|   9 | 张1    |   456456 | 路人       | 6456464     |
|  10 | 张2    |    45897 | 扫地僧     | 6556        |
|  11 | 张9    | 45564897 | 保安       | 12138       |
+-----+--------+----------+------------+-------------+
4 rows in set (0.00 sec)