数据库mysql的DML和DQL_数据库中的dmi和dqi是什么意思-优快云博客

本文链接：https://blog.youkuaiyun.com/2301_80792972/article/details/145217458

一，相关定义：

DML：英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作，包含以下三个组成部分：

insert ：添加数据，可以向表结构当中来插入数据
update ：修改数据，可以修改表中的数据
delete ：删除数据，可以来删除表中的记录

DQL(Data Query Language) ：数据查询语言，使用SQL 语句实现对数据表的查询

SQL ： Structured Query Language 标准结构化查询语言简称。

语法组成：

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条

件列表 order by 排序字段列表 limit 分页参数

分拆以下及几个部分

基本查询（不带任何条件）

条件查询（ where ）

聚合函数（ count 、 max 、 min 、 avg 、 sum ）

分组查询（ group by ）

排序查询（ order by ）

分页查询（ limit ）

二，通过相关知识点完成以下案例：

2.1建库建表插入数据3

mysql> create database mydb8_worker;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb6_product      |
| mydb7_openlab      |
| mydb8_worker       |
| mydb_temp1         |
| mydbl_test         |
| mydbx_temp3        |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
15 rows in set (0.00 sec)

mysql> use mydb8_worker;
Database changed
mysql> create table t_worker(
    -> department_id int(11) not null comment '部门号' ,
    -> worker_id int(11) primary key not null comment '职工号' ,
    -> worker_date date not null comment '工作时间' ,
    -> wages float(8,2) not null comment '工资' ,
    -> politics varchar(10) not null default '群众' comment '政治面貌' ,
    -> name varchar(20) not null comment '姓名',
    -> borth_date date not null comment '出生日期' );
Query OK, 0 rows affected, 3 warnings (0.03 sec)
mysql> desc t_worker;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| department_id | int         | NO   |     | NULL    |       |
| worker_id     | int         | NO   | PRI | NULL    |       |
| worker_date   | date        | NO   |     | NULL    |       |
| wages         | float(8,2)  | NO   |     | NULL    |       |
| politics      | varchar(10) | NO   |     | 群众    |       |
| name          | varchar(20) | NO   |     | NULL    |       |
| borth_date    | date        | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> insert into t_worker values (101,1001,'2015-5-4',7500.00,'群众','张春燕','1990-7-1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_worker values(101,1002,'2019-2-6',5200.00,'团员','李名博','199
7-2-8');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_worker values(102,1003,'2008-1-4',10500.00,'党员','王博涵','19
83-6-8');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_worker values (102,1004,'2016-10-10',5500.00,'群众','赵小军','
1994-9-5');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_worker values (102,1005,'2014-4-1',8800.00,'党员','钱有财','19
92-12-30');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_worker values(103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9-2');
Query OK, 1 row affected (0.01 sec)

mysql> desc t_worker;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| department_id | int         | NO   |     | NULL    |       |
| worker_id     | int         | NO   | PRI | NULL    |       |
| worker_date   | date        | NO   |     | NULL    |       |
| wages         | float(8,2)  | NO   |     | NULL    |       |
| politics      | varchar(10) | NO   |     | 群众    |       |
| name          | varchar(20) | NO   |     | NULL    |       |
| borth_date    | date        | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

2.2查询数据

mysql> select * from t_worker; /*显示所有职工的基本信息。*/
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
mysql>mysql> select distinct department_id '所属部门的部门号' from t_worker;
+------------------+
| 所属部门的部门号 |
+------------------+
|              101 |
|              102 |
|              103 |
+------------------+
3 rows in set (0.00 sec)  /*查询所有职工所属部门的部门号，不显示重复的部门号。*/
mysql> select count(1) '所有职工人数' from t_worker;
+--------------+
| 所有职工人数 |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)  /*求出所有职工的人数。*/
mysql> mysql> select max(wages), min(wages) from t_worker;
+------------+------------+
| max(wages) | min(wages) |
+------------+------------+
|   10500.00 |    5200.00 |
+------------+------------+
1 row in set (0.00 sec) /*列出最高工和最低工资。*/
mysql> select round(avg(wages),2)'平均工资',round(sum(wages),2)'总工资' from t_worker;
+----------+--------+
| 平均工资 | 总工资 |
+----------+--------+
|  7166.67 |  43000 |
+----------+--------+
1 row in set (0.00 sec)  /*列出职工的平均工资和总工资。*/

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)  /*查看全表信息*/

mysql> create table t_workerdate select worker_id,name,worker_date from t_worker;
Query OK, 6 rows affected (0.02 sec) /*先创表，从已知的表中获取相关信息*/
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t_workerdate;
+-----------+--------+-------------+
| worker_id | name   | worker_date |
+-----------+--------+-------------+
|      1001 | 张春燕 | 2015-05-04  |
|      1002 | 李名博 | 2019-02-06  |
|      1003 | 王博涵 | 2008-01-04  |
|      1004 | 赵小军 | 2016-10-10  |
|      1005 | 钱有财 | 2014-04-01  |
|      1006 | 孙菲菲 | 2019-05-05  |
+-----------+--------+-------------+
6 rows in set (0.00 sec)
mysql> select * from t_workerdate;
+-----------+--------+-------------+
| worker_id | name   | worker_date |
+-----------+--------+-------------+
|      1001 | 张春燕 | 2015-05-04  |
|      1002 | 李名博 | 2019-02-06  |
|      1003 | 王博涵 | 2008-01-04  |
|      1004 | 赵小军 | 2016-10-10  |
|      1005 | 钱有财 | 2014-04-01  |
|      1006 | 孙菲菲 | 2019-05-05  |
+-----------+--------+-------------+
6 rows in set (0.00 sec)
 /*创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。*/
mysql> select year(now())-year(borth_date) '年龄' from t_worker where politics='党 员';
+------+
| 年龄 |
+------+
|   42 |
|   33 |
|   29 |
+------+
3 rows in set (0.01 sec)  /*显示所有党员的年龄。*/

mysql> select name '姓名',wages'工资' from t_worker where wages between 4000 and 80
00 ;
+--------+---------+
| 姓名   | 工资    |
+--------+---------+
| 张春燕 | 7500.00 |
| 李名博 | 5200.00 |
| 赵小军 | 5500.00 |
| 孙菲菲 | 5500.00 |
+--------+---------+
4 rows in set (0.00 sec)  /*列出工资在4000-8000之间的所有职工姓名*/
mysql> select name'姓名' from t_worker where name like '孙%' or name like '李%';
+--------+
| 姓名   |
+--------+
| 李名博 |
| 孙菲菲 |
+--------+
2 rows in set (0.00 sec)  /*列出所有孙姓和李姓的职工姓名。*/
mysql> select name '姓名' , worker_id '职工号' from t_worker where department_id=10
2 or department_id=103;
+--------+--------+
| 姓名   | 职工号 |
+--------+--------+
| 王博涵 |   1003 |
| 赵小军 |   1004 |
| 钱有财 |   1005 |
| 孙菲菲 |   1006 |
+--------+--------+
4 rows in set (0.00 sec)  /*列出所有部门号为102和103日不是党员的职工号、姓名。*/
mysql> select name '姓名' , worker_id '职工号' from t_worker where (department_id=1
02 or department_id=103) and politics!='党员';
+--------+--------+
| 姓名   | 职工号 |
+--------+--------+
| 赵小军 |   1004 |
+--------+--------+
1 row in set (0.00 sec)
/*列出所有部门号为102和103日不是党员的职工号、姓名。*/
mysql> select * from t_worker order by borth_date ;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
/*、将职工表t worker中的职工按出生的先后顺序排序。*/

mysql> select name'姓名',worker_id'职工号' from t_worker order by wages desc limit 3;
+--------+--------+
| 姓名   | 职工号 |
+--------+--------+
| 王博涵 |   1003 |
| 钱有财 |   1005 |
| 张春燕 |   1001 |
+--------+--------+
3 rows in set (0.00 sec)
/*显示工资最高的前3名职工的职工号和姓名。*/
mysql> select department_id '部门号',count(worker_id)'党员人数' from t_worke
r where politics='党员' group by department_id;
+--------+----------+
| 部门号 | 党员人数 |
+--------+----------+
|    102 |        2 |
|    103 |        1 |
+--------+----------+
2 rows in set (0.00 sec)/*求出各部门党员的人数。*/
mysql> select department_id '部门号',sum(wages) '部门平均工资' from t_worker
 group by department_id;
+--------+--------------+
| 部门号 | 部门平均工资 |
+--------+--------------+
|    101 |     12700.00 |
|    102 |     24800.00 |
|    103 |      5500.00 |
+--------+--------------+
3 rows in set (0.00 sec)  /*统计各部门的工资和平均工资并保留2位小数*/
mysql> select department_id '部门号',sum(wages) '部门工资总和' ,round(avg(wages),2) '部门平均工资' from t_worker group by department_id;
+--------+--------------+--------------+
| 部门号 | 部门工资总和 | 部门平均工资 |
+--------+--------------+--------------+
|    101 |     12700.00 |         6350 |
|    102 |     24800.00 |      8266.67 |
|    103 |      5500.00 |         5500 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)  /*列出总人数大于等于3的部门号和总人数*/