DML语句

一、

插入数据：

mysql> insert into employees(ename, age, birth, hiredate, sal, deptno)
    -> values
    -> ('zhaoyang', 24, '1993-07-17', '2021-10-18', 3000, 1),
    -> ('chenqian', 21, '1996-05-12', '2021-10-21', 3001, 1),
    -> ('dengdesheng', 19, '1999-02-25', '2021-11-15', 4000, 1),
    -> ('zhouwenqiang', 21, '2001-1-1', '2021-8-19', 3500, 2);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from employees;
+--------------+------+------------+------------+---------+--------+
| ename        | age  | birth      | hiredate   | sal     | deptno |
+--------------+------+------------+------------+---------+--------+
| zhaoyang     |   24 | 1993-07-17 | 2021-10-18 | 3000.00 |      1 |
| chenqian     |   21 | 1996-05-12 | 2021-10-21 | 3001.00 |      1 |
| dengdesheng  |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
| zhouwenqiang |   21 | 2001-01-01 | 2021-08-19 | 3500.00 |      2 |
+--------------+------+------------+------------+---------+--------+
4 rows in set (0.00 sec)

修改记录值：

mysql> update employees set sal=5000 where ename='zhaoyang';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employees;
+--------------+------+------------+------------+---------+--------+
| ename        | age  | birth      | hiredate   | sal     | deptno |
+--------------+------+------------+------------+---------+--------+
| zhaoyang     |   24 | 1993-07-17 | 2021-10-18 | 5000.00 |      1 |
| chenqian     |   21 | 1996-05-12 | 2021-10-21 | 3001.00 |      1 |
| dengdesheng  |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
| zhouwenqiang |   21 | 2001-01-01 | 2021-08-19 | 3500.00 |      2 |
+--------------+------+------------+------------+---------+--------+
4 rows in set (0.04 sec)

创建部门表：

mysql> create table if not exists dept(
    ->  deptno int,
    ->  dept_name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dept(deptno, dept_name) values(1,'运维服务部'),(2,'开发部'),(3,'实施部'),(4,'产品部');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+
| deptno | dept_name  |
+--------+------------+
|      1 | 运维服务部 |
|      2 | 开发部     |
|      3 | 实施部     |
|      4 | 产品部     |
+--------+------------+
4 rows in set (0.00 sec)

多表更新：

同时更新employees中的字段和表dept表中的dept_name字段：

mysql> update employees emp, dept de set emp.sal=emp.sal*de.deptno, de.dept_name=emp.ename
    -> where emp.deptno=de.deptno;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 6  Changed: 3  Warnings: 0

mysql> select * from employees;
+--------------+------+------------+------------+---------+--------+
| ename        | age  | birth      | hiredate   | sal     | deptno |
+--------------+------+------------+------------+---------+--------+
| zhaoyang     |   24 | 1993-07-17 | 2021-10-18 | 5000.00 |      1 |
| chenqian     |   21 | 1996-05-12 | 2021-10-21 | 3001.00 |      1 |
| dengdesheng  |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
| zhouwenqiang |   21 | 2001-01-01 | 2021-08-19 | 7000.00 |      2 |
+--------------+------+------------+------------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+--------------+
| deptno | dept_name    |
+--------+--------------+
|      1 | zhaoyang     |
|      2 | zhouwenqiang |
|      3 | 实施部       |
|      4 | 产品部       |
+--------+--------------+
4 rows in set (0.00 sec)

去除重复：

mysql> select distinct deptno from employees;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
+--------+
2 rows in set (0.00 sec)

特定条查询：

mysql> select * from employees where deptno = 1;
+-------------+------+------------+------------+---------+--------+
| ename       | age  | birth      | hiredate   | sal     | deptno |
+-------------+------+------------+------------+---------+--------+
| zhaoyang    |   24 | 1993-07-17 | 2021-10-18 | 5000.00 |      1 |
| chenqian    |   21 | 1996-05-12 | 2021-10-21 | 3001.00 |      1 |
| dengdesheng |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
+-------------+------+------------+------------+---------+--------+
3 rows in set (0.00 sec)

多字段条件查询：

mysql> select * from employees where deptno = 1 and sal >= 4000;
+-------------+------+------------+------------+---------+--------+
| ename       | age  | birth      | hiredate   | sal     | deptno |
+-------------+------+------------+------------+---------+--------+
| zhaoyang    |   24 | 1993-07-17 | 2021-10-18 | 5000.00 |      1 |
| dengdesheng |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
+-------------+------+------------+------------+---------+--------+
2 rows in set (0.04 sec)

排序：

mysql> select * from employees order by birth asc, sal asc;
+--------------+------+------------+------------+---------+--------+
| ename        | age  | birth      | hiredate   | sal     | deptno |
+--------------+------+------------+------------+---------+--------+
| zhaoyang     |   24 | 1993-07-17 | 2021-10-18 | 5000.00 |      1 |
| chenqian     |   21 | 1996-05-12 | 2021-10-21 | 3001.00 |      1 |
| dengdesheng  |   19 | 1999-02-25 | 2021-11-15 | 4000.00 |      1 |
| zhouwenqiang |   21 | 2001-01-01 | 2021-08-19 | 7000.00 |      2 |
+--------------+------+------------+------------+---------+--------+
4 rows in set (0.00 sec)