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条件:
| 符号 | 意义 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| > | 大于 |
| < | 小于 |
| is null | 为空 |
| is not null | 不为空 |
| and | 并且 |
| or | 或者 |
| like | 类似 |
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)