《MySQL数据库》之"浅尝辄止"（五）DQL的使用详解和存储引擎简介
发布时间：2023-06-18 13:30
一、DQL语言的详解
SQL标准语言中的DQL,用于查询数据。其基本语法可以分成六个部分，我们称之为六个子句
1、基本查询语句的语法格式
select..... from......[where......][group by.......][having........][order by.......]
一个基本查询语句中，至少要有select子句和from子句。其他四个子句根据需求来选择。
2、子句执行顺序
在六个子句同时存在的查询语句中，他们的执行时机也是由先后顺序的。我来整理一下：
1）、查询时，要基于表操作，因此，第一步先 执行 from子句
2）、表确定后，可以使用where子句，进行条件筛选。
3）、然后，再对数据进行分组查询，因此，要执行group by子句
4）、分组查询后，我们可以对分组查询后的数据，再次过滤，限制，所以，要使用having子句
5）、过滤后，我们使用select子句来选择要显示的字段信息
6）、显示的字段值，可以进行相应的排序，这个时候可以使用 order by子句
3、where子句
当我们在对数据表进行CRUD（PS:增加Create、读取查询Retrieve、更新Update和删除Delete）操作时,where子句有对数据进行条件筛选和约束的作用。
where子句的条件表达式形式如下
1）关系表达式。 符号有 >,>=,<,<=, =, !=, <>
如要求年龄大于20，写法：where age > 20;
年龄等于20，写法：where age=20;
年龄不等于20，写法： where age!=20 或者 where age<>20
2）多条件连接符：and、or、[not] between ....and ....
需求1：查询工资在[1500,3500]区间内的员工姓名、职位、工资、部门号。
mysql > select ename,job,sal,deptno from emp where sal>=1500 and sal<=3500;
mysql > select ename,job,sal,deptno from emp where sal between 1500 and 3500;需求2：查询工资不在[1500,3500]区间内的员工姓名、职位、工资、部门号。
mysql > select ename,job,sal,deptno from emp where sal<1500 or sal>3500;
mysql > select ename,job,sal,deptno from emp where sal not between 1500 and 3500;需求3：查询姓名是‘james’和‘smith’员工的信息。
mysql > select * from emp where ename='james' or ename='smith';PS: and 和 or 一定要慎重考虑，以免出差错。
3）集合操作：[not] in (集合元素)、>all(集合元素)、 in (集合元素) 相当于 使用连接符or，对同一字段的"="等式 进行多个值的连接。 not int (集合元素) 相当于使用连接符and，对同一个字段的"!="不等式进行多个值的连接 需求1:查询姓名是 ”james“,"smith","lucy"的员工信息。 需求2:查询姓名是不是 ”james“,"smith","lucy"的员工信息。 PS：对于>all(集合元素)、 4）模糊查询 关键字：like 通配符 %：匹配0或0个以上的字符。 占位符 _: 匹配一个字符。 需求1：查询姓名中含有s的员工姓名，职位，入职日期，部门编号 需求2：查询姓名中第二个字母是a,工资大于1000的员工姓名，职位，入职日期，部门编号 对于查询的数据，我们可以按照需求来排序，如升序，降序，此时需要使用order by子句。 排序关键字：desc 降序 asc: 升序 语法： order by colName1 [desc|asc] [，colName2 [desc|asc] ] 如果order by子句中不写排序关键字，默认是升序，即默认情况下为asc。 需求1：查询10号部门员工的姓名、职位，工资，奖金 按照工资降序，奖金升序(工资相同时，奖金排序生效) mysql > select ename,job,sal,comm from emp where deptno=10 order by sal desc,comm; 有的时候，我们的需求可能是这样的：查询员工表中每个部门的总人数，总工资，平均工资等等；或者是在全校成绩表中，查询每个班级的平均成绩等等。此时，在查询时，就要将表中的数据按照相应的字段，根据值的不同，来划分成不同的小组。如上述需求，我们要按照部门号这个字段，根据不同的值，10号一组，20号一组，30号一组.......来统计。全校成绩表中，我们要按照班级编号来划分小组。 在做分组查询时，一般都会涉及到相应的分组函数（也叫聚合函数），而所有聚合函数都会忽略null,不进行统计。 count(colName)：统计指定字段的字段值的记录总数，null值会被忽略。 max(colName)：返回指定字段的字段值中的最大值，null值会被忽略。 min(colName)：返回指定字段的字段值中的最小值，null值会被忽略。 sum(colName)：返回指定字段的字段值的和，null值会被忽略。 avg(colName)：返回指定字段的字段值的平均值，null值会被忽略。 注意：在使用avg()函数时，因为其忽略空值记录，因此要根据需求考虑是否要将null值记录计算在内，如果想要计算在内的话，我们可以使用这个函数：ifnull(colName,value)，它的作用是如果指定的colName对应的值有null,我们就使用value值,否则使用其本身 需求1：求员工表中的所有员工的平均奖金。分析：即使有的员工的奖金为null,我们也要将之计算进来。 参照下面需求 需求2：求有奖金的员工的平均奖金。分析：此时，不计算奖金为null的员工数。 注意：当使用了分组函数时，却没有使用group by子句，此时是将整张表划成了一组。 下面我整理了一些需求，供大家理解分组查询。 需求1：查询所有员工的最高工资，最低工资，工资总和，平均工资及其总员工数；--分析：整张表为一组。 mysql > select max(sal),min(sal),sum(sal),avg(ifnull(sal,0)),count(ename) from emp; 需求2：查询每个部门的最高工资，入职最早的日期，奖金之和，平均奖金及其总人数；--分析：按照部门分组 mysql > select max(sal),min(hiredate),sum(comm),avg(ifnull(comm,0)),count(ename) from emp group by deptno; 从第二个需求，我们可以看到，在数据显示上，我们不知道是哪个部门的信息，因此，我们可以将分组字段，添加到select子句中。再次注意，在分组查询中，select子句中除了分组函数外，只能存在分组字段，其他不相关的字段不允许存在。 改进后： 需求3：查询每个部门的总人数，工资之和，按照 部门号降序排序 mysql > select deptno "部门号",count(*) '总人数',sum(sal) 工资之和 from emp group by deptno order by deptno desc; 需求4：查询每个部门，每种职位的人数 --分析：按照部门和职位分组 在做分组查询时，我们可能需要再次过滤，这个时候需要使用having子句。 如下需求 需求1：查询部门工资总和大于10075的部门号，工资总和，最高工资，最低工资，总人数,按照部门号降序 mysql > select deptno,sum(sal),max(sal),min(sal),count(*) from emp group by deptno having sum(sal)>10075 order by deptno; 关键字 distinct。需要注意的是此关键字必须紧挨着select关键字。看下面需求： 需求1：查询员工表中有哪些部门编号------分析：同一个部门编号不需要显示很多次，因此需要去重查询。 需求2：查看员工表中有哪些职位 MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许只需要将数据存储在内存中而不是文件中(内存存储引擎能够在内存中存储所有的表格数据)。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。 这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型) MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。 这里介绍一下常用的存储引擎 1）innoDB InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束。 在以下场合下，使用InnoDB是最理想的选择： 2）MyISAM MyISAM不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表定义) 3）MEMORY 使用Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。 一般在以下几种情况下使用Memory存储引擎： 可以按照下列判断来选择 （1）是否需要支持事务； --------------------------------------------------------------------------未完待续，欢迎留言----------------------------------------------------------------------------
mysql > select * from emp where ename='james' or ename='smith' or ename='lucy';
mysql > select * from emp where ename in ('james' , 'smith' , 'lucy');
mysql > select * from emp where ename!='james' and ename!='smith' and ename<>'lucy';
mysql > select * from emp where ename not in ('james' , 'smith' , 'lucy');
>all(..):查询结果为大于集合中最大的值。
select ename,job,hiredate,deptno from emp where ename like '%s%';-----单双引号都可以
select ename,job,hiredate,deptno from emp where ename like '_a%' and sal>1000;4、order by子句
5、group by子句
mysql > select avg(ifnull(comm,0)) from emp ;
mysql > select avg(comm) from emp
mysql > select deptno,max(sal),min(hiredate),sum(comm),avg(ifnull(comm,0)),count(ename) from emp group by deptno;
mysql > select deptno,job,count(*) 总人数 from emp group by deptno,job;
6、having子句
二、去重查询
mysql > select distinct deptno from emp;
mysql > select distinct job from emp;
三、存储引擎
1、简介
2.存储引擎的分类
在客户端使用相关命令显示所有存储引擎：show engines;
1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列AUTO_INCREMENT属性。
一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。
.MYD(MYData，存储数据)
.MYI(MYIndex，存储索引)
1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。3.如何选择合适的存储引擎？
（2）是否需要使用热备；
（3）崩溃恢复：能否接受崩溃；
（4）是否需要外键支持；
然后按照标准，选择对应的存储引擎即可。