九、MySQL 数据库

朱雀 2024-04-17 18:05 66阅读 0赞

## 九、MySQL 数据库 ##

### 1. delete，truncate 和 delete之间的区别 ###

详情参考：[https://www.cnblogs.com/alice-cj/p/10354737.html][https_www.cnblogs.com_alice-cj_p_10354737.html]  
**相同点**：truncate和不带where子句的delete, 以及drop都会删除表内的数据；  
**不同点：**  
①truncate和 delete只删除数据不删除表的结构(定义) ；drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态；  
②delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发；truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger；  
③delete语句不影响表所占用的extent, 高水线(high w2atermark)保持原位置不动；显然drop语句将表所占用的空间全部释放；truncate 语句缺省情况下将空间释放到minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始)；  
④速度,一般来说: drop> truncate > delete；  
⑤安全性：小心使用drop 和truncate,尤其没有备份的时候，否则哭都来不及；  
**使用上：**  
想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.；  
想删除表,当然用drop；  
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete；  
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据；

### 3. SQL优化 ###

详情参考：[https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA][https_mp.weixin.qq.com_s_ZosZWVeME3V652uO1yrdoA]

1.创建索引  
要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；在经常需要进行检索的字段上创建索引；  
避免在索引上使用表达式操作  
2.选择正确的存储引擎  
4.(查询select语句优化)select 语句列出需要查询的字段，不用\*  
5.①尽量避免在where子句中使用or来连接条件；  
②尽量避免在where子句中使用 !=(<>) not in not exists；  
③尽量避免在where子句中进行null值判断；is null. is not null；  
④不能使用前导模糊查询，也就是 ‘%XX’ 或 ‘%XX%’，由于前导模糊不能利用索引的顺序，必须一个个去找，看是否满足条件，这样会导致全索引扫描或者全表扫描；  
⑤ 不要在where 子句中对字段进行表达式操作；  
⑥用union all替换union，使用exists代替in；  
6.调整where子句连接顺序，表连接最好写在其他where条件之前，那些可以过滤掉最大数量记录数的条件必须写在where子句的末尾(因为DBMS一般采用自下而上的顺序解析where字句)  
7.使用表的别名  
8.尽量将多条SQL语句压缩到一句SQL中  
9.使用预编译查询。

### 1. MySql和Oracle区别？ ###

1.数据类型区别：参考 [https://www.cnblogs.com/yangxia-test/p/4710250.html][https_www.cnblogs.com_yangxia-test_p_4710250.html]  
2. oracle中NUMBER(10,2)表示什么：参考 [https://zhidao.baidu.com/question/1536396442757275827.html][https_zhidao.baidu.com_question_1536396442757275827.html]  
3. Oracle/MySql下分别对应的日期与字符串转换函数？  
参考：[https://blog.csdn.net/J080624/article/details/52753726?utm\_source=blogxgwz5][https_blog.csdn.net_J080624_article_details_52753726_utm_source_blogxgwz5]

**两者区别具体参考：  
[https://blog.csdn.net/it\_you\_know/article/details/83617761][https_blog.csdn.net_it_you_know_article_details_83617761]**  
1.单引号的处理（字符串单引号都可以，双引号Oracle不可以）  
单引号：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 1]  
双引号：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 3]  
2.自动增长的数据类型处理  
oracle不支持自动增长的数据类型，通过建立一个自动增长的序列号来完成自动增长：  
①代码中使用到sequence：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 5]  
②Sequence接口：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70]  
③OracleSequence类实现Sequence接口：

public class OracleSequence implements Sequence {
        private final String sequenceName;
        private final String sql;
        private final DataSource dataSource;
    
        public OracleSequence(String sequenceName,DataSource dataSource){
            this.sequenceName=sequenceName;
            this.sql="SELECT"+sequenceName+".nextval FROM DUAL CONNECT BY LEVEL<=?";
            this.dataSource=dataSource;
        }
    
        public Long nextValue(){
            return this.nextValues(1).get(0);//当前条记录的"nextValues(1)"下面所有记录集的第1行记录的"get(0)"第一个字段值
        }
        
        public List<Long> nextValues(int n){
            if (n<1){
                throw new IllegalArgumentException("Input parameter must be greater than zero");
            }else {
                Connection con= DataSourceUtils.getConnection(this.dataSource);
                PreparedStatement stmt=null;
                try {
                    stmt=con.prepareStatement(this.sql);
                    stmt.setInt(1,n);
                    ResultSet resultSet=stmt.executeQuery();
                    ArrayList seqNumbers=new ArrayList();
                    while (resultSet.next()){
                        seqNumbers.add(resultSet.getLong(1));
                    }
                    ArrayList var6=seqNumbers;
                    return var6;
                } catch (SQLException e) {
                    JdbcUtils.closeStatement(stmt);
                    stmt=null;
                    DataSourceUtils.releaseConnection(con,this.dataSource);
                    con=null;
                    throw new RuntimeException("Failed to generate sequence",e);
                } finally {
                    JdbcUtils.closeStatement(stmt);
                    DataSourceUtils.releaseConnection(con,this.dataSource);
                }
            }
        }
        
        public String toString(){
            return "sequence:"+this.sequenceName;
        }
    }

④SequenceConfig中：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 6]

②Oracle自增实现：下图＋  
[https://js.aizhan.com/data/oracle/7236.html][https_js.aizhan.com_data_oracle_7236.html]  
关于序列 sequence：[https://blog.csdn.net/qq\_29832217/article/details/87938177][https_blog.csdn.net_qq_29832217_article_details_87938177]  
③Oracle分页：  
[https://blog.csdn.net/YAn\_2018/article/details/86620619][https_blog.csdn.net_YAn_2018_article_details_86620619]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 3]

### 0. 对事务了解多少？ ###

#### 四种事务特性、四种隔离级别、七种传播行为 ####

**【1】四种事务特性：ACID**  
**① 原子性**：强调事务的不可分割；  
**② 一致性**：事务执行前后数据的完整性保持一致；  
**③ 隔离性**：一个事务执行过程中，不应该受到其他事务的干扰；  
**④ 持久性**：事务一旦结束，数据就持久到数据库中。  
（若不考虑隔离性，回引发安全性问题：  
**① 脏读**：一个事务读到另一个事务未提交的数据；  
**② 不可重复读**：一个事务读到另一个事务已提交的 update的数据导致多次查询结果不一致；  
**③ 幻读**：一个事务读到另一个事务已提交的 insert的数据导致多次查询结果不一致）  
**【2】解决读的问题：设置隔离级别\_四种**  
**① 读未提交**：脏读、不可重复读、幻读都可能发生；  
**② 读已提交**：避免脏读，但不可重复读、幻读有可能发生；  
**③ 可重复读**：避免脏读、不可重复读，但幻读有可能发生；  
**④ 串行**：避免脏读、不可重复读、幻读；  
**MySQL 默认的隔离级别是 可重复读，Oracle默认的隔离级别是 读已提交；**  
![在这里插入图片描述][20190830000232277.png]  
**DEFAULT**：是一个PlatformTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别；  
（读未提交 是最低的隔离级别，它允许另一个事务可看到这个事务未提交的数据；读已提交 保证一个事务提交后才能被另一个事务读取，另一个事务不能读取该事务未提交的数据；可重复读 这种隔离级别可防止脏读、不可重复读，但可能出现幻读，它保证了一个事务不能被另一个事务读取未提交的数据外还避免了不可重复读；串行 这种花费最高但最可靠的事务隔离级别，事务被处理为顺序执行，防止了脏读、不可重复读、幻读。 ）  
**【3】事务的七种传播行为**  
PROPAGION\_XXX：事务的传播行为  
一、保证在同一事务中：  
PROPAGION\_REQUIRED：支持当前事务，如果不存在，就新建一个（默认）；  
PROPAGION\_SUPPORTS：支持当前事务，如果不存在，就不使用事务；  
PROPAGION\_MANDATORY：支持当前事务，如果不存在，抛出异常；  
二、保证没有在同一事务中：  
PROPAGION\_REQUIRES\_NEW：如果有事务存在，挂起当前事务，创建一个新的事务；  
PROPAGION\_NOT\_SUPPORTED：以非事务运行，如果事务存在，挂起当前事务；  
PROPAGION\_NEVER：以非事务运行，如果有事务存在，抛出异常；  
PROPAGION\_NESTED：如果当前事务存在，则嵌套事务执行。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 4]

### 1. 数据库的三范式？ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 5]  
**【1】第一范式：**  
第一范式的目标是确保每列的原子性，每列都是不可再分的最小数据单元；  
![在这里插入图片描述][20190916174246411.png]  
**【2】第二范式：**  
如果一个关系满足第 1NF，并且除了主键以外的其他列，都完全依赖于该主键，则满足第二范式；第二范式要求每个表只描述一件事情；  
![在这里插入图片描述][20190916175055292.png]  
**【3】第三范式：**  
如果一个关系满足 2NF，且除了逐渐以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）；第三范式要求表中不存在冗余字段；  
![在这里插入图片描述][20190916175416198.png]

### 2. 说一下 ACID 是什么？ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 6]

### 3. char和varchar、float和double的区别？ ###

【1】Char和 Varchar在指定时必须要指定后面的数字（VARCHAR类型的实际长度是它的值的实际长度+1，为什么“+1”呢？这一个字节用于保存实际使用了多大的长度）；  
【2】Char固定长度 ，Varchar可变长度；  
【3】Char比 Varchar效率高，Varchar比 Char更节省空间；  
【4】Char如果存入数据的实际长度比指定长度要小，**会补空格至指定长度** ，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错;；Varchar类型如果存入的数据的实际长度比指定的长度小，**会缩短到实际长度**，如果存入数据的实际长度大于指定长度，低版本会被截取 高版本会报错；  
（目前 VARCHAR是 VARCHAR2的同义词。工业标准的 VARCHAR类型可以存储空字符串，但是 oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型 VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中 varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用 VARCHAR2而不是VARCHAR）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 7]

### 4. MySQL 的 内连接、左连接、右连接 有什么区别区别？ ###

![在这里插入图片描述][20190829171506541.png]

### 4\_1. 连接查询、条件查询、联合查询 ###

**【一、连接查询】**  
**内连接：**  
① 相连接两张表地位平等；  
② 若一张表在另一张表中不存在对应数据，则不作连接；

select colList from table1 [inner] join table2 on table1.col=table2.col;

//(隐式内连接)
    select orders.*,name,age from orders,users where users.id=orders.user_id;

**外连接：**  
① 作连接的两张表地位不平等，其中一张为基础表；  
② 基础表中每条数据必须出现，即使另一表中无数据与之匹配，也要用 null补充；  
③ 左（右）连接左（右）表是基础表；

select colList from table1 left/right [outer] join table2 on table1.col=table2.col;

**【二、子查询】：in / exists**

select * from scores where gno in(select gno from game) where name="tom";
    select * from scores where exists(select * from users where name="tom");
    //查询工资<平均工资的员工号、名字
    select id,name from employee where salary<(select avg(salary) from employee);

**【三、联合查询】：union / unionall**  
① 把多条查询语句产生的结果纵向连接为一体；  
② 有 all关键字会显示全部数据，重复的也显示；无 all，重复的只显示一次；  
③ 列的数量和类型要一致；

select * from users where sex="女" union select *from users where age is null;
    结果：
     女 1995
     女 1996
     男 null

**【union / unionall 区别】**  
**同：** 都是合并结果集；  
**异：** ① union去除两个结果集的重复记录，unionall不能去除重复记录；  
② union效率低，unionall效率高。  
**【limit 的使用】**  
查询31~40条记录的数据：`select * from users limit 30,10;`  
从表中第一条数据开始，查询三条记录：`select * from users limit 3;`  
![在这里插入图片描述][20190902224558778.png]

### 5. MySQL 索引是怎么实现的？ ###

**MySQL数据库四种类型的索引：普通索引、唯一索引、主键索引、组合索引；**  
**【1】普通索引**：是最基本的 Mysql索引，无任何限制；  
① 创建索引：（create index on）  
`create index indexName on mytable(username(length));`；  
② 修改表结构：（alter mytable add index on）  
`alter mytable add index [indexName] on(username(length));`；  
③ 创建表时直接指定：（create table index）

create table mytable(id int not null,
    	                 username varchar(16) not null,
    	                 index [indexName] (username(length)));

④ 删除索引：（drop index on）  
`drop index [indexName] on mytable;`；  
**【2】唯一索引**：与普通索引类似，不同的是：mysql数据库索引列的值必须唯一，但允许有空值；（若是组合索引，则列值的组合必须唯一）  
① 创建索引：（create unique index on）  
`create unique index indexName on mytable(username(length));`；  
② 修改表结构：（alter mytable add unique index on）  
`alter mytable add unique [indexName] on(username(length));`；  
③ 创建表时直接指定：（create table unique index）

create table mytable(id int not null,
    	                 username varchar(16) not null,
    	                 unique index[indexName] (username(length)));

**【3】主键索引**：它是一种特殊的唯一索引，不允许有空值。  
①一般在建表的同时创建主键索引：（create table primary key）。也可用 alter命令，一个表只能一个主键。

create table mytable(id int not null,
    	                 username varchar(16) not null,
    	                 primary key(id));

**【4】组合索引**  
**总结：** 通常说的索引是B+Tree索引，树状的，搜索需要从根节点出发，上层节点对应靠左的值，如果不从根节点出发，后面的节点对应下层的值，依然是乱序的，需要遍历，所以索引就失效了，所以有最左原则。

**组合索引的使用：（例如组合索引（a,b,c），组合索引的生效原则是 ）**  
① 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；  
② abc只要用上了就行，跟写的顺序无关；  
③ 范围值也算断点，只不过自身用到了索引；  
④ order by排序也用到了索引的效果（但在 order by之前有断点不会发挥排序、索引效果了）；

**组合索引详细参考：** [https://blog.csdn.net/u014590757/article/details/79590561][https_blog.csdn.net_u014590757_article_details_79590561]  
为形象的对比单列索引和组合索引，为表添加多个字段，例如：

create table mytable(id int not null,
    	                 username varchar(16) not null,
    	                 city varchar(50) not null,
    	                 age int not null);

为进一步榨取 mysql的效率，要考虑建立组合索引，即将 name、city、age建到一个索引中：  
`aletr table mytable add index name_city_age(name(10),city,age);`  
建表时，username长度为 16，这里用 10，因为通常名字长度 <10，这样会加速索引查询速度，还会减少索引文件大小，提高 insert的更新速度。若分别在 username、city、age上建立单列索引，让该表有3个单列索引，查询时效率会远低于组合索引，虽然有3个索引，但 mysql只能用到其中那个它认为似乎最有效的单列索引。建立这样的组合索引，其实相当于分别建立了下面三组组合 mysql数据库索引：  
username city、age username、city username；为何没有 city age的组合索引呢？：因为 mysql组合索引“最左前缀”的结果，即只从最左面开始组合，并非只要包含这三列的查询都会用到该索引；

下面的几个 sql语句就会用到这个组合 mysql数据库索引：

select * from mytable where username="admin" and city="shanghai";
    select * from mytable where username="admin";

而下面的几个则不会用到：

select * from mytable where age=20 and city="shanghai";
    select * from mytable where city="shanghai";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 8]

#### **B+树（特点）：** ####

**总结：** ①所有数据都保存在叶子节点且叶子结点本身依关键字的大小自小而大顺序链接，非叶子结点作为叶子结点的索引，搜索总是到叶子结点结束，不可能在非叶子结点命中；  ②有n棵子树的非叶子结点中含有n个关键字(即非叶子结点的子树指针与关键字个数相同)，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点；  ③同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素；  
**1.** 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个）(即非叶子结点的子树指针与关键字个数相同)，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。  
**2.** 所有的叶子结点中包含了全部关键字的信息及指向含这些关键字记录的指针(即所有关键字都在叶子结点出现)，且叶子结点本身依关键字的大小自小而大顺序链接（叶子节点组成一个链表）。  
**3.** 同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。  
**4.** 在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中,不可能在非叶子结点命中；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 10]  
**B-树（B树）（特点）：**  
（B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。如人们可能会以为B-树是一种树，而B树又是另一种树。而事实上是，B-tree就是指的B树。特此说明。）

关键字分布在整棵树中，任何一个关键字出现且只出现在一个节点中；搜索可能在非叶子节点结束；

**1.** 关键字分布在整颗树中；  
**2.** 任何一个关键字出现且只出现在一个结点中；  
**3.** 搜索有可能在非叶子结点结束；  
**4.** 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 11]  
**详细参考：**  
[面试题：B（B-）树，B+树，B树和B+树的区别，B树和B+树的优点；][B_B-_B_B_B_B_B]  
[https://blog.csdn.net/yu876876/article/details/84896789][https_blog.csdn.net_yu876876_article_details_84896789]  
[https://blog.csdn.net/qq\_35008624/article/details/81947773][https_blog.csdn.net_qq_35008624_article_details_81947773]

### 6. 怎么验证 MySQL 的索引是否满足需求？ ###

根据查询需求决定配置的索引类型，确定后，在 select语句前加上 explain，使用 explain 来查看执行计划。  
（执行结果中主要关注的是，type 和 key：  
type = ALL ：表示全表扫描；  
type = const ：表示通过索引一次就找到了。  
key = NULL：表示没有使用索引；  
key = primary ：表示使用了主键；  
key一般=使用了主键/索引的名字）

**详细参考：**[https://blog.csdn.net/u014453898/article/details/55004193][https_blog.csdn.net_u014453898_article_details_55004193]  
[https://www.cnblogs.com/pengyunjing/p/9197430.html][https_www.cnblogs.com_pengyunjing_p_9197430.html]  
![在这里插入图片描述][20190829171802294.png]

### 6\_1. 索引的优缺点 ###

**优点：**  
提高查询速度；  
主键索引或唯一索引能保证数据唯一性；  
对于有依赖关系的子表和父表之间的联合查询时，提高表连接速度，提高查询速度；  
使用分组和排序子句进行数据查询时，提高分组和排序效率。

**缺点：**  
索引会占用物理空间；  
增加、删除和修改数据时，要动态的维护索引，会消耗性能；  
创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；

### 6\_2. 索引何时生效、何时不生效 ###

#### **【1】索引何时失效：** ####

（1）组合索引未使用最左前缀；例如组合索引（A，B），where B=b不会使用索引；  
（2）模糊查询时 like通配符前置；where A like ‘%China’；  
（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；  
（4）or会使索引失效（即使用 or且查询字段不同）；如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）  
（5）存在 NULL值条件：is null；（字段值存在 null）如：`select * from users where id is not null;`  
（6）NOT条件：查询条件为非时，索引定位就困难了，执行计划此时更倾向于全表扫描，这类的查询条件有：！=(<>)、NOT in、NOT exists；

（7）如果列类型是字符串（即字符串类型的列没使用引号）；要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；  
（8）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

**【2】何时使用索引：**  
MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引 B+树更加耗费时间。所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。  
（1）主键，unique字段；  
（2）和其他表做连接的字段需要加索引；  
（3）在 where里使用＞，≥，＝，＜，≤，is null和 between等字段；  
（4）使用不以通配符开始的 like，where A like ‘China%’；  
（5）聚集函数 MIN()，MAX()中的字段；  
（6）order by和 group by字段  
**【3】何时不使用索引：**  
（1）表记录太少；  
（2）数据重复且分布平均的字段（只有很少数据值的列）；  
（3）经常插入、删除、修改的表要减少索引；  
（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；  
（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

**详细参考：**  
参考一：[https://blog.csdn.net/weixin\_39420024/article/details/80040549][https_blog.csdn.net_weixin_39420024_article_details_80040549]  
参考二：[https://blog.csdn.net/weixin\_39420024/article/details/80040549][https_blog.csdn.net_weixin_39420024_article_details_80040549]

### 6\_3. 索引碎片 ###

① **查询所有带有碎片语句**：`show table status like 'aa'`；（aa表示队友索引的表名）；查询结果中 Data\_free值大于0，表示有索引碎片产生；  
② 可以使用如下方式清理碎片：  
方法一：通过一个 nop操作（不产生对数据实质影响的操作）来修改表，如：表的引擎为 innodb，可以 `alter table xx engine innodb；`，修改表的引擎为默认引擎会重新调整数据，但不会影响数据；  
方法二：使用 `optimize table xx`，来进行修复；  
③ 再去查索引碎片：`show table status like 'aa'`，Data\_free变为0；

**参考**：[https://www.cnblogs.com/YasinXiao/p/7736400.html][https_www.cnblogs.com_YasinXiao_p_7736400.html]  
[https://www.cnblogs.com/qlqwjy/p/8594859.html][https_www.cnblogs.com_qlqwjy_p_8594859.html]

### 7. MySQL 的常用引擎 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 12]

### 8. MySQL 的行锁和表锁 ###

表锁：开销小，锁表快，高并发下性能低；  
行锁：开销大，锁表慢，高并发下性能高；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 13]

### 9. 乐观锁和悲观锁 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 14]  
**乐观锁：** 十分乐观，总是认为不会出现问题，无论干什么都不去上锁，如果出现问题，在此更新测试；  
**悲观锁：** 十分悲观，认为总是出现问题，无论干什么都会上锁，在去操作；  
乐观锁的实现方式：  
1.取出记录时，获取当前版本；  
2.更新时，带上这个version；  
3.执行更新时，set version=newVersion where version=oldversion  
4.如果version不对，就更新失败;  
**eg：**  
(1.先查询获取版本号为version=1;2.更新时,id条件后带上这个version;3.执行更新时把version由原来的版本改为最新的版本,一般会自动+1;4.如果version不对，就更新失败)  
\-----A(线程)  
update user set name=‘lala’,version=version+1  
where id=2 and version=1

-----B(线程)抢先于A线程完成，此时version=2了，会导致A线程修改失败！
    update user set name='lala',version=version+1
    where id=2 and version=1

**测试MP(mybatis plus)的乐观锁插件**  
1.表中新增version字段,并设置默认值都为1;  
2.实体类加对应的字段version,并在字段上添加@version注解(mybatis plus的乐观锁的version注解);  
3.注册组件：新建config包,创建MyBatisPlusConfig配置类：  
①在类上加@Confguration注解表示这是一个配置类;(有事务的控制还可以加上一个@EnableTransactionManagement注解自动管理事务);  
若在此配置类上管理mybatis plus,则可以将启动类上的包扫描(是交给mybatis做的),不放在启动类上了,放在此配置类上;  
②类中注册乐观锁插件;  
此时乐观锁插件配置完毕;  
4.测试类中进行测试;

**乐观锁：** 每次拿数据的时候都认为别人不会修改数据，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据；  
乐观锁需要自己实现，在表中添加一个 version字段，每次修改成功值加 1，这样每次修改的时候先对比一下自己的 version跟数据库现在的 version是否一致，如果不一致就不修改，这样就实现了乐观锁；  
**悲观锁：** 每次拿数据的时候都认为别人会修改，所以每次拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放；悲观锁的特点是先获取锁，再进行业务操作，通常所说的“一锁二查三更新”即指的是使用悲观锁，通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁，当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果；select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。  
这里需要注意的一点是不同的数据库对select for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。另外mysql还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在mysql中用悲观锁务必要确定走了索引，而不是全表扫描；

**总结：** 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能；（乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方）

**详细参考**：[https://blog.csdn.net/qq\_27469747/article/details/79741490][https_blog.csdn.net_qq_27469747_article_details_79741490]

### 10. 如何做 MySQL 的性能优化？ ###

![在这里插入图片描述][20190829172203755.png]  
（1）创建索引； 选择正确的存储引擎；  
（2）日志和数据分开存储，减小 IO压力；  
（3）模糊查询%通配符后置，前置导致全表扫描：  
select id from t where name like ‘%abc%’ 或者  
select id from t where name like ‘%abc’ 或者  
若要提高效率，可以考虑全文检索。  
而select id from t where name like ‘abc%’ 才用到索引；  
（4）应尽量避免在 where 子句中使用 or 来连接条件，可以 使用 union合并查询结果集： select id from t where num=10 union all select id from t where num=20；  
（5） 应尽量避免在 where 子句中对字段进行 null 值判断（is null，is notnull），否则将导致引擎放弃使用索引而进行全表扫描；  
（6）应尽量避免在 where 子句中使用 ！=(<>)、in、NOT in操作符，否则引擎将放弃使用索引而进行全表扫描；  
（7）很多时候用 exists 代替 in 是一个好的选择：  
select \* from sys\_user s where exists (select 1 from runoob\_tbl rb where runoob\_id = s.id)；  
（8）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；  
（9）避免使用 select \*，列出需要查询的字段；

### 11. 如何获取当前数据库版本？ ###

![在这里插入图片描述][2019082917250485.png]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 15]

### 12. MySQL 问题排查都有哪些手段？ ###

![在这里插入图片描述][20190829172249336.png]

### 12\_1.说一些数据库优化方面的经验？ ###

① 程序优化，用 PrepareStatement 进行增删改查；  
② 查询结果不要用 \*，要明确指明结果字段；  
③ 减少多表连接数，使用尽量少的表进行连接；表连接时，尽量用主键连接或用唯一索引；

1.创建索引  
要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；在经常需要进行检索的字段上创建索引；  
避免在索引上使用表达式操作  
2.选择正确的存储引擎  
4.(查询select语句优化)select 语句列出需要查询的字段，不用\*  
5.调整where子句连接顺序，表连接最好写在其他where条件之前，那些可以过滤掉最大数量记录数的条件必须写在where子句的末尾(因为DBMS一般采用自下而上的顺序解析where字句)  
6.尽量避免在where子句中使用or来连接条件；  
尽量避免在where子句中进行null值判断；  
尽量避免在where子句中使用 !=(<>) not in not exists；  
用union all替换union，使用exists代替in；  
模糊查询 %后置；  
7.使用表的别名  
8.尽量将多条SQL语句压缩到一句SQL中  
9.使用预编译查询

### 12\_2.web响应优化？ ###

1.使用CDN，cdn可以优化网站访问速度  
2.合并静态资源(减少HTTP请求)  
3.把多个css合并为一个css，把图片组合成雪碧图  
4.把css放页面头部，js放底部(让浏览器尽快下载CSS渲染页面，不会让页面出现长时间的空白)  
5.对于较大的文本资源，必须开启gzip压缩  
6.延长静态资源缓存时间  
CDN（contentdistribute network，内容分发网络）的本质仍然是一个缓存，而且将数据缓存在离用户最近的地方，使用户以最快速度获取数据，即所谓网络访问第一跳，如下图。  
由于CDN部署在网络运营商的机房，这些运营商又是终端用户的网络服务提供商，因此用户请求路由的第一跳就到达了CDN服务器，当CDN中存在浏览器请求的资源时，从CDN直接返回给浏览器，最短路径返回响应，加快用户访问速度，减少数据中心负载压力。  
CDN缓存的一般是静态资源，如图片、文件、CSS、script脚本、静态网页等，但是这些文件访问频度很高，将其缓存在CDN可极大改善网页的打开速度。

### 13. MySQL 中表删除数据时，id值变化情况？ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 16]

### 14. MySQL的存储过程 ###

##### ① 创建存储过程：CREATE PROCEDURE 过程名（参数） #####

##### ② 执行存储过程：CALL 过程名（参数） #####

##### ③ 删除存储过程：DROP PROCEDURE 过程名 #####

##### ④ 显示所有的存储过程信息 ：SHOW CREATE PROCEDURE 过程名 #####

##### ⑤ 可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程；若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE 过程名 #####

##### \_在创建存储过程时，必须具有 CREATE ROUTINE 权限 #####

##### \_使用 DELIMITER 命令将结束命令修改为其他字符； #####

**【注释】**：MySQL存储过程可使用两种风格的注释：  
双杠：–，该风格一般用于单行注释  
C风格： 一般用于多行注释

##### 1.存储过程是什么？ #####

简单来说就是为方便以后的使用而保存的一条或者多条MySQL语句的集合；  
MySQL的存储过程：存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

##### 2.为什么使用存储过程？ #####

（1）存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。  
（2）提高性能，因为存储过程比单独执行的SQL语句执行的要快。高性能  
较快的执行速度，如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。  
（3）简化对变动的管理，如果表名，列名或者其他的内容需要更改直接改存储过程就可以了，别人甚至不需要直到里面的内容。方便

##### 3.存储过程的缺陷？ #####

（1）普通用户没有存储过程的安全访问权限，需要管理员进行设置创建存储过程的权限，允许用户使用存储过程，而不是创建存储过程。  
（2）一般来说，存储过程的编写更加复杂，需要更高的技能。更丰富的经验。

##### 4.如何执行存储过程？（CALL 过程名（参数）） #####

执行的语句是CALL，示例：  
CALL productpricing(@pricelow,@pricehigh,@priceaverage);  
其中，执行的是名为producpricing的存储过程；计算并返回价格的最高，最低和平均值。可以显示结果，也可以不显示结果。

##### 5.如何创建存储过程？（CREATE PROCEDURE 过程名（参数））语法：CREATE PROCEDURE 过程名(\[\[IN|OUT|INOUT\] 参数名 数据类型\[,\[IN|OUT|INOUT\] 参数名 数据类型…\]\]) \[特性 …\] 过程体 #####

示例：

CREATE PROCEDURE productpricing()      //括号里如果有参数可以列出；
    BEGIN     //这是存储过程的主体，由一个select语句构成；
    	SELECT Avg(prod_price) AS priceaverage
    	FROM products;
    END;

mysql> DELIMITER //
    mysql> CREATE PROCEDURE ShowStuScore()
        -> BEGIN
        -> SELECT * FROM tb_students_score;
        -> END //
    Query OK， 0 rows affected (0.09 sec)

DELIMITER //
      CREATE PROCEDURE myproc(OUT s int)
        BEGIN
          SELECT COUNT(*) INTO s FROM students;
        END
        //
    DELIMITER ;

DELIMITER $
      CREATE PROCEDURE pro_test()
        BEGIN
          SELECT * FROM student WHERE id = 1;
          SELECT * FROM student WHERE id = 3;
        END $

**【过程名】**：存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db\_name.sp\_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。  
**【分隔符】**：MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//“之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。  
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,"分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:  
**【过程参数】**：存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数；  
      MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。  
     IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值；OUT：该值可在存储过程内部被改变，并可返回；INOUT：调用时指定，并且可被改变和返回  
**【过程体】**：过程体的开始与结束使用BEGIN与END进行标识。存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。  
      在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。  
      在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可**使用 DELIMITER 命令将结束命令修改为其他字符**。语法格式：`DELIMITER $$`；语法说明：$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。当使用 DELIMITER 命令时，应该避免使用反斜杠“\\”字符，因为它是 MySQL 的转义字符。  
      在 MySQL 命令行客户端输入如下SQL语句：`mysql > DELIMITER ??`；成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。  
      若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：`mysql > DELIMITER ;`；注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。  
【① IN参数例子】

-- (1)带有输入参数的存储过程
    DELIMITER $
    CREATE PROCEDURE pro_test1(IN sid INT)
    BEGIN
        SELECT * FROM student WHERE id=sid;
    END $
    -- 定义会话变量
    SET @sid = 2;
    -- 调用存储过程
    CALL pro_test1(@sid);

DELIMITER //
      CREATE PROCEDURE in_param(IN p_in int)
        BEGIN
        SELECT p_in;
        SET p_in=2;
        SELECT p_in;
        END;
        //
    DELIMITER ;
    
    #调用
    SET @p_in=1;
    CALL in_param(@p_in);
    
    SELECT @p_in;

执行结果可以看出，p\_in虽然在存储过程中被修改，但并不影响@p\_in的值

【② OUT参数例子】

-- (2)带有输出参数的存储过程
    DELIMITER $
    CREATE PROCEDURE pro_test2(OUT str VARCHAR(20))
    BEGIN
        -- 修改str的值
        SET str = '输出参数';
    END $
    -- 定义会话变量
    -- set @s='';
    -- 调用存储过程
    CALL pro_test2(@s);
    -- 查询会话变量的值
    SELECT @s;

#存储过程OUT参数
    DELIMITER //
      CREATE PROCEDURE out_param(OUT p_out int)
        BEGIN
          SELECT p_out;
          SET p_out=2;
          SELECT p_out;
        END;
        //
    DELIMITER ;
    #调用
    SET @p_out=1;
    CALL out_param(@p_out);
    
    SELECT @p_out;

【③ INOUT参数例子】

-- (3)带有输入输出参数的存储过程
    DELIMITER $
    CREATE PROCEDURE pro_test3(INOUT m INT)
    BEGIN
          -- 查看变量的值
          SELECT m;
          -- 修改变量的值
          SET m = 999;
    END $
    -- 定义会话变量
    SET @money = 100;
    -- 调用存储过程
    CALL pro_test3(@money);
    -- 查询变量的值
    SELECT @money;

#存储过程INOUT参数
    DELIMITER //
      CREATE PROCEDURE inout_param(INOUT p_inout int)
        BEGIN
          SELECT p_inout;
          SET p_inout=2;
          SELECT p_inout;
        END;
        //
    DELIMITER ;
    #调用
    SET @p_inout=1;
    CALL inout_param(@p_inout) ;
    SELECT @p_inout;

-- (4)将查询的结果赋值给输出参数
    DELIMITER $
    CREATE PROCEDURE pro_test4(IN sid INT,OUT sname VARCHAR(10))
    BEGIN
        SELECT NAME INTO sname FROM student WHERE id=sid;
    END $
    -- 定义会话变量
    SET @sid = 4;
    -- 调用存储过程
    CALL pro_test4(@sid,@sname);
    -- 查询会话变量的值
    SELECT @sname;
    
    -- (5)带有条件判断的存储过程
    -- 需求:编写一个存储过程，当n=1，输出'星期一'，
    -- 当n=2，输出'星期二'
    -- 当n=3，输出'星期三'，其他情况输出'其他情况'
    DELIMITER $
    CREATE PROCEDURE pro_test5(IN n INT,OUT str VARCHAR(20))
    BEGIN
        IF n=1 THEN
            SET str = '星期一';
        ELSEIF n=2 THEN
            SET str = '星期二';
        ELSEIF n=3 THEN
            SET str = '星期三';
        ELSE 
            SET str = '其他情况';
        END IF;
    END $
    -- 定义会话变量
    SET @n = 7;
    -- 调用存储过程
    CALL pro_test5(@n,@str);
    -- 查询变量的值
    SELECT @str;

##### 6.如何删除存储过程？（DROP PROCEDURE 存储名） #####

##### 注意 ：所有的MySQL变量都以@开头。 #####

执行下列语句：`DROP PROCEDURE productpricing;`

##### 7.显示所有的存储过程信息 （SHOW CREATE PROCEDURE 存储名） #####

SHOW CREATE PROCEDURE ordertotal;

### 14. where、gruop by、having、order by ###

**where、having：** 都是查询条件；但 having子句是统计结果（大小、平均数、总数、个数）做条件；  
**group by、order by：** group by 把查询结果分组，order by 把查询结果排序；

### 14\_1. Mysql常用函数 ###

**聚合函数：**  
sum（）：求和；  
avg（）：求平均值；  
min（）：最小值；  
max（）：最大值；  
count（）：统计记录行的数量；  
**数学函数：（Mysql、Oracle都常用这些）**  
ceil（）：向上取整；  
floor（）：向下取整；  
abs（）：取绝对值；  
round（）：四舍五入；… …等等

select sum(score) as '总分' from scores where name='tom';
    select avg(score) as '平均分' from scores where name='tom';
    select min(score) as '最低分' from scores where gno=1;
    
    select count(name) from users;//具体某一列的行数,如果name列中有一行值为空,则值为总行数-1
    select count(*) from users;//查询总的行数(记录数),包括某列中值为空的一行

### 14. 数据库题目 ###

一、用两种方式根据部门号降序，工资升序列出每个员工的信息：  
`select * from employee order by dep_id desc,salary(asc);`  
二、列出各个部门中工资高于本部门平均工资的员工数、部门号，并按部门号排序：

select count(*),a.dep_id from employee a a.salary>
                             (select avg(b.salary) from employee b where b.dep_id="本部门")
                             group by a.dep_id order by a.dep_id;

三、用一条 sql语句查询每门课分数都 >80分的学生姓名：

name  kechneg  fenshu
    tom   语文     81
    tom   数学     75
    jay   语文     76
    jay   数学     90

select name from student group by name having min(score)>80;

四、所有部门之间的比赛组合：一个表中只有一个 name字段，一共四条记录，分别对应 a、b、c、d 四个球队，对四个球队进行比赛，用一条 sql语句显示所有可能的比赛内容：

name
    1   a
    2   b
    3   c
    4   d

a.name   b.name
    1   d        c
    2   b        b
    3   d        a
    4   c        b
    5   c        d
    6   c        a

select a.name,b.name from team a,team b where a.name>b.name;

五、统计每年每月信息，将表1内容换为表2内容显示：

year   month   amount
    1991   1       1.1
    1991   2       1.2
    1991   3       1.3
    1991   4       1.4
    1992   1       2.1
    1992   2       2.2
    1993   3       2.3
    1994   4       2.4

year   m1   m2   m3   m4
    1991   1.1  1.2  1.3  1.4
    1992   2.1  2.2  2.3  2.4

select s.year,
           (select t.amount from sales t where t.month='1' and t.year=s.year)m1,
           (select t.amount from sales t where t.month='2' and t.year=s.year)m2,
           (select t.amount from sales t where t.month='3' and t.year=s.year)m3,
           (select t.amount from sales t where t.month='4' and t.year=s.year)m4
    from sales s group by s.year;

[https_www.cnblogs.com_alice-cj_p_10354737.html]: https://www.cnblogs.com/alice-cj/p/10354737.html
[https_mp.weixin.qq.com_s_ZosZWVeME3V652uO1yrdoA]: https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA
[https_www.cnblogs.com_yangxia-test_p_4710250.html]: https://www.cnblogs.com/yangxia-test/p/4710250.html
[https_zhidao.baidu.com_question_1536396442757275827.html]: https://zhidao.baidu.com/question/1536396442757275827.html
[https_blog.csdn.net_J080624_article_details_52753726_utm_source_blogxgwz5]: https://blog.csdn.net/J080624/article/details/52753726?utm_source=blogxgwz5
[https_blog.csdn.net_it_you_know_article_details_83617761]: https://blog.csdn.net/it_you_know/article/details/83617761
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/76057ec2ed78467dae9bf776a0838c8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e6f1d5961a154aa5b9833b3619a8c575.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/48c2903675f14584b9743297dc597f4f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/bba7ccc5e7cf4b1e937f35ec1fa7c8bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/aee54f8764ce426d907560ecbf8dcfbf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/0f2a52d47ebb4f148744c42b89f26c1a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/904bf25791554dc19cf1c844c2805884.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70_pic_center 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/5592f679a4034dd58c803408fb5750d2.png
[https_js.aizhan.com_data_oracle_7236.html]: https://js.aizhan.com/data/oracle/7236.html
[https_blog.csdn.net_qq_29832217_article_details_87938177]: https://blog.csdn.net/qq_29832217/article/details/87938177
[https_blog.csdn.net_YAn_2018_article_details_86620619]: https://blog.csdn.net/YAn_2018/article/details/86620619
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/3c6bd4dfde2042499a47c0ad7da177fb.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/bedd20cab84c4967883a2405f25db0e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a3af5e5c9d464ed1a5b884506494d001.png
[20190830000232277.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/3081236fcbea440499bdef1c7ab962eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/838cbebc2c474b3f9f8bb584d22147ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/52d996e26e1845f08c8cb6506467a839.png
[20190916174246411.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a73ae4f63cfd460c9d7a17cfd7bea75b.png
[20190916175055292.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/3960ac6e621b4e189da738bee99a5cb9.png
[20190916175416198.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c3d8f0b79ea14badafa8143b44b22582.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c631756dd9104da2a697e80f5a0440a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e97e651d8e6b40f49957106beef1ea92.png
[20190829171506541.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/fb68db2e6d514c148479a0508744f4d9.png
[20190902224558778.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c1cf5d65d96545989c8859eef8ad7518.png
[https_blog.csdn.net_u014590757_article_details_79590561]: https://blog.csdn.net/u014590757/article/details/79590561
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/9040788d580e46389ffae3e51e387a31.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e761224947f340c49bce7d026947e319.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e2a2a5c245f941fdb104aafcb9824861.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8e19260d84e14a90a030201f5168bedc.png
[B_B-_B_B_B_B_B]: https://blog.csdn.net/weixin_42228338/article/details/97684517?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&dist_request_id=da3be0ac-e656-48e4-8c9e-ee7639945719&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control
[https_blog.csdn.net_yu876876_article_details_84896789]: https://blog.csdn.net/yu876876/article/details/84896789
[https_blog.csdn.net_qq_35008624_article_details_81947773]: https://blog.csdn.net/qq_35008624/article/details/81947773
[https_blog.csdn.net_u014453898_article_details_55004193]: https://blog.csdn.net/u014453898/article/details/55004193
[https_www.cnblogs.com_pengyunjing_p_9197430.html]: https://www.cnblogs.com/pengyunjing/p/9197430.html
[20190829171802294.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7aaa0b140cc141579d723e4e2afd2b97.png
[https_blog.csdn.net_weixin_39420024_article_details_80040549]: https://blog.csdn.net/weixin_39420024/article/details/80040549
[https_www.cnblogs.com_YasinXiao_p_7736400.html]: https://www.cnblogs.com/YasinXiao/p/7736400.html
[https_www.cnblogs.com_qlqwjy_p_8594859.html]: https://www.cnblogs.com/qlqwjy/p/8594859.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/00a242e3c24f491abcd820d61ca1ec32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8f4b2571fa7248009909fa5598029164.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/93eb9ad8521a40c6a89e9aa17425ffc9.png
[https_blog.csdn.net_qq_27469747_article_details_79741490]: https://blog.csdn.net/qq_27469747/article/details/79741490
[20190829172203755.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/11c58f01857c42edbbd9b912e94e1e94.png
[2019082917250485.png]: https://img-blog.csdnimg.cn/2019082917250485.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a8716ae0cd7b4f6aadc19f6379d46303.png
[20190829172249336.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/44018e02164b4230a2223d298047cfdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDI5OTIz_size_16_color_FFFFFF_t_70 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/2de2cedbcb1a4824bdcc980947c9cd5a.png