一、Mysql优化

mysql索引

mysql索引是一种帮助mysql提高查询效率的数据结构

mysql索引分类

优点:大大加快数据查询速度
缺点：
1.维护索引需要耗费数据库资源（表-索引结构，刚好对应2/3的缺点）
2.索引需要占用磁盘空间
3.当对表进行增删改查的时候受到索引的影响，会拉低查询速度
索引分类：
innodb引擎：支持事务
a.主键索引：创建表的主键是自动创建，唯一不可为null

CREATE TABLE t_user1(
ID INTEGER PRIMARY KEY,
name VARCHAR(20),
age INTEGER
)
show INDEX FROM t_user1;

b.唯一索引：unique ，索引值唯一，只允许一个为null
— 建表时创建索引

-- 建表时创建索引
CREATE TABLE t_user2(
ID INTEGER PRIMARY KEY,
name VARCHAR(20),
UNIQUE(NAME)
)
show INDEX FROM t_user2;
-- 建表后创建索引
CREATE TABLE t_user3(
ID INTEGER PRIMARY KEY,
name VARCHAR(20)
)
show INDEX FROm t_user3;
CREATE UNIQUE  INDEX name_index on t_user3(name);

c.普通索引（单值索引|单列索引|前缀索引）：一列一个索引，一个表可以有多个索引

-- 建表时创建索引
CREATE TABLE t_user4(
ID INTEGER PRIMARY KEY,
name VARCHAR(20),
KEY(NAME)
)
show INDEX FROM t_user4;
-- 建表后创建索引
CREATE TABLE t_user5(
ID INTEGER PRIMARY KEY,
name VARCHAR(20)
)
CREATE INDEX name_index ON t_user5(NAME);
show INDEX FROm t_user5;

d.复合索引：一个索引包含多个列

-- 建表时创建索引
CREATE TABLE t_user6(
ID INTEGER PRIMARY KEY,
name VARCHAR(20),
age INTEGER,
KEY(NAME,age)
)
show INDEX FROM t_user6;
-- 建表后创建索引
CREATE TABLE t_user7(
ID INTEGER PRIMARY KEY,
name VARCHAR(20),
age INTEGER
)
CREATE INDEX name_index ON t_user7(NAME,age);
show INDEX FROm t_user7;

最左匹配原则
1.最左前缀原则，
2.mysql引擎在查询为了更好利用索引，在查询过程中会动态调整顺序以便利于利用索引
必须包含最左前缀

    **这四种的语法和特点不同，主键和唯一是，唯一可以有一个为空，唯一需要加上unique，唯一和普通是在语法上一个是，表前key，表后不写key，直接index，唯一是表前unique，表后是unique index**<br />**MYISAM引擎**：数据压缩，从库<br />**e.full text 全文索引：**mysql5.7之前智能用于myisan引擎<br />        全文索引类型为full text，在定义索引列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在varchar text char 类型咧上创建。mysql只有myisam储存引擎支持全文索引

索引失效:

1. or语句的，前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
2. 组合索引，不是使用第一列索引，索引失效。
3. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
5. 在索引列上使用 is null 或 is not null 操作。
   索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
6. where中索引列有运算;
7. where中索引列使用了函数;
8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引引效。

   索引原理：

   创建一个表，下面无序的插入顺序
   
   
   这是底层页节点的存储方式：主键值id+数据字节大小+ponit（p指针）方式储存为一条，一页存放n条数据
   默认的储存的数据大小为16kb
   eg： id int 4字节，数据carchar（20） 20字节，，int 4字节 p指针4-8字节，一共36字节
   （16kb1024）/36字节=455一条，一页是455条数据，
   
   随后加入页目录：页目录只存放页节点中的主键值id和p，
   eg：int id ：4字节 ，p：4-8字节
   (16kb1024)/12=60w+
   一般两层的B+TREE数据量就很庞大了，10E左右
   
   B+Tree是在B-Tree(B树)基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
   B-Tree(B树)
   
   从上面的B-Tree结构图中可以看到每个节点中不仅包含数据的key值和p，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，当存储的数据量很大时会导致B-Tree的深度较大，增大查询时的磁盘/0次数，进而影响查询效率，在B+Tree中，所有数据记泉节点都是按照键值大小序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低 B+Tree的高度。

B+Tree相对于B-Tree有几点不同:1.非叶子节点只存储键值信息。2所有叶子节点之间都有一个链指针3.数据记录都存放在叶子节点中
一般4层的B+TREE是三次io，因为顶层是常驻内存，不需要io，3层的2次io

聚簇索引和非聚簇索引

聚簇索引: 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（典型的就是上面的主键索引）
主键是聚簇索引，聚簇索引不是主键索引，聚簇索引是一种格式
非聚簇索引:将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置，通俗来讲就是非聚簇索引，例如name，age这些，先在非聚簇索引中查找age所在的id再去聚簇索引查找id对应的节点
在innodb中，在聚簇索引之上创建的索引称之为辅助索引也就是非聚簇索引，例如，普通索引（前缀索引），唯一索引，复合索引，非聚簇索引叶子节点储存的不再是行的物理位置，而是主键值，非聚簇索引的访问数据都是需要二次查找。
索引合并：数据库执行的时候，会根据执行引擎的特点进行索引的整合，多个索引合并到一块进行操作，针对单列索引
覆盖索引：查询的字段都是索引覆盖的

记录的是id，不是数据地址，因为增删改的时候存储地址的话，需要把所有的地址修改一遍，这样是非常不方便的
1.InnoDB中
innoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id=14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骏:第一步在辅助索引B+树中检索Name;到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个谁一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键(类似oracle中的Rowld)来作为聚簇索引，如果已经设置了主键为聚能索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。
2.MYISAM MyIsAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两棵B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检案无需访问主键的索引树。

使用聚簇索引的优势

    每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?<br />        1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(缓存器)，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。<br />        2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化;或者是我们需要查找的数据，在上一次I0读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚辉索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引需要注意什么?

    当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。<br />        建议使用int类型的自增，方便排序并且默认会在索引树的末屋增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到I0操作读取到的数据量。

常建议使用自增id

    聚筵索引的数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是白增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。markdown

什么情况下无法利用索引呢?

1.查询语句中使用LIKE关键字 在查询语句中使用LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
2.查询语句中使用多列索引 多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
3.查询语句中使用OR关键字 查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

mysql优化

优化三部曲：1.定位问题 2.分析原因 3.解决问题
慢查询：mysql
主要几大方面

• 数据库设计：数据表设计遵循三范式，使用合适的数据类型，使用合适的存储引擎
• 适当创建索引
• 数据库扩展：数据库的分表分库，读写分离等
• SQL语句优化等

  一、数据库设计

  三大范式

  第一范式1NF：字段原子性
  第一范式简单的说就是表中的字段是最小不可再分的，里边的字段是不可再分的
  第二范式2NF： 消除对主键的部分依赖
  2NF的使用是需要满足1NF为前提，在表中添加一个业务字段，而主键不用来做业务处理，比如我们的商品表有商品id，商品id为商品的主键，但是需要创建一个商品编号列来专门处理业务，因为id太敏感，我们处理业务都是用商品编号来处理，比如展示商品时展示编号等等！
  
  第三范式3NF：在2NF的基础上添加外键
  3NF的使用必须满足2NF，要求表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键），比如下面的例子，订单表中有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户id即可（外键），而不能有其他的客户信息。因为其他的客户信息直接关联于用户id，而不是直接与订单id直接相关。如下图所示：
  

  数据类型

  尽量使用可以正确存储数据的最小数据类型 更小的数据类型意味着更快，占用更少的磁盘，内存、缓存和处理时间
  尽量使用整型表示字符串 因为字符集和校对规则，使处理字符比整型更复杂，比如：我们使用数据库内置的datetime类型存储时间而不是字符类型，我们使用整型存储ip而不是直接将ip字符串存到数据库中
  尽可能使用not null 这个值是很烦人的，建字段时请尽量指定是否非空，NULL使得索引，统计，比较都变得更复杂，而且索引尽量不要创建到可以为null的字段上

  存储引擎

    数据库存储引擎是数据库底层组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。<br />**InnoDB存储引擎**<br />容灾恢复性比较好支持事务，默认事务隔离界别为可重复读使用的锁粒度为行锁，可以支持更高的并发支持外键配合一些热备工具可以支持在线热备份在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序，数据和索引放在一块，都位于B+数的叶子节点上<br />**MyISAM存储引擎**<br />不支持事务不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复选择依据<br />**如果没有特殊需求默认使用InnoDB引擎即可**<br />**MyISAM**：以读写插入为主的应用程序，比如博客系统、新闻门户网站。<br />**Innodb**：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统

  二、数据库索引

  详情见开头

  三、数据分表分库，读写分离

    **读写分离就**是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全<br />        **分表分库**;

  四、SQL语句优化

  1、查询尽量避免全表扫描，首先考虑在where、order by字段上添加索引
  2、避免在where字段上使用NULL值，所以在设计表时尽量使用NOT NULL约束，有些数据会默认为NULL，可以设置默认值为0或者-1
  3、避免在where子句中使用!=或<>操作符，Mysql只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE使用索引
  4、避免在where中使用OR来连接条件，否则可能导致引擎放弃索引来执行全表扫描，可以使用UNION进行合并查询
  5、尽量避免在where子句中进行函数或者表达式操作
  6、最好不要使用select from t，用具体的字段列表代替”“，不要返回用不到的任何字段
  7、in 和 not in 也要慎用，否则会导致全表扫描，如
  select id from t where num IN(1,2,3)如果是连续的值建议使用between and，select id from t where between 1 and 3；
  8、模糊查询左侧有%会导致全表检索，select id from t where col like %a%；，如果需要全文检索可以使用全文搜索引擎比如es，slor
  9、limit offset rows关于分页查询，尽量保证不要出现大的offset，比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中，要避免出现大页码的情况，尽量引导用户做条件过滤
  FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.ORDER BY: 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10.LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

  mysql执行计划

  Explain命令是用来直接检查sql语句的，有两个参数很重要“Type”和“Extra”

  一、Type

  找到所需数据使用的扫描方式，或者说是MySQL查询行的方式
  由快到慢：system > const > eq_ref > ref > range > index > ALL
  1.system，说明数据已经加载到内存里，不需要进行磁盘IO。这类扫描是速度最快的。
  2 const扫描的条件为：（1）命中主键(primary key)或者唯一(unique)索引；（2）被连接的部分是一个常量(const)值；
  3 eq_ref扫描的条件为：对于前表的每一行(row)，后表只有一行被扫描。我也没有遇到！
  4 ref对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
  5 range类型，它是索引上的范围查询，它会在索引上扫码特定范围内的值。
  6 index：需要扫描索引上的全部数据。
  7 ALL全表扫描。

  二、Extra：

  主要包含不适合在其他列中显示但十分重要的额外信息
  1.Using filesort：排序的时候最好遵循所建索引的顺序与个数否则就可能会出现using filesort
  2.Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。group by一定要遵循所建索引的顺序与个数
  3.Using index表示相应的select操作中使用了覆盖索引(covering index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。对两个字段建立索引将其中一个字段作为where条件就符合键值查找
  4. Using where表明使用了where过滤
  5. Using join buffer使用了连接缓存
  6. impossible wherewhere子句的值总是false，不能用来获取任何元组

1. select tables optimized away在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MYSQL存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
2. distinct优化distinct操作，在找到第一匹配的元组后即停止找同样的值的动作

其中using filesort，using temporary，using index最为常见，出现前两种表示是需要优化的地方，出现第三种表示索引效率不错

二、mysql事务&关键词

事务四大特性（Transaction）

是访问和更新数据库的程序执行单元；
事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行，要么都成功要都失败
事务主要有四大特性。ACID
a 原子性：不可分割的操作单元，事务中所有操作，要么全部成功；要么回滚到执行事务之前的状态。
c 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
i 隔离性：事务操作之间彼此独立和透明互不影响。如果一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。
d 持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

三个隔离四级别

               **幻读，脏读，不可重复读。**<br />**脏读**：一个事务读取到另一个未提交事务修改的数据。<br />**不可重复读**：多次读取的**数据内容**不一样。因为修改操作<br />**幻读**：            前后多次读取**数据总量**不一样。因为新增删除<br />            一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差<br />**读未提交**：脏读，不可重复读，幻读都有可能发生<br />**读已提交**：不可重复读，幻读可能发生<br />**可重复读**：幻读可能发生<br />**可串行化**：都不可能发生<br />**读未提交**在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。<br />    **可串行化**强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少<br />    **大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)**<br />    **MySQL事务默认的隔离级别是可重复读，而且MySQL可以解决了幻读的问题。**<br />**是如何解决幻读的？**

1.多版本并发控制（MVCC）（快照读）
多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的

以 InnoDB 为例，每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。
普通的 select 就是快照读。
原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的

1. next-key 锁 （当前读）

next-key 锁包含两部分

• 记录锁（行锁）
• 间隙锁

记录锁是加在索引上的锁，间隙锁是加在索引之间的
原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的

三、mysql基础语句

1.demo01基本查询

SELECT empAddress,empId FROM employee e;
-- 效率偏低一点，要转化为全部字段
SELECT * FROM employee;
-- 简单的四则运算，没有取模运算因为斜杠是占位符
SELECT empName,empSale*12 FROM employee e;
SELECT empName,(empSale+300)*13+empSale FROM employee;
-- 别名 AS 可以省略不写
SELECT e.empId AS ID,empSale FROM employee as e;
-- 去重 DISTINCT
SELECT  e.empName as 名字 FROM employee e;
SELECT  DISTINCT e.empName as 名字 FROM employee e;
-- 排序查询 order BY +字段  desc （降序），默认asc（降序）
SELECT  empSale  as 薪资 FROM employee e ORDER BY e.empSale  DESC;
SELECT  empSale  as 薪资 FROM employee e ORDER BY e.empSale ;
-- 按照多字段进行排序,谁在前先排谁
SELECT  empSale ,empName FROM employee e ORDER BY e.empSale,empSale ;

2.demo02条件查询

-- *****条件查询
-- SELECT 字段 from 表名 where 条件
-- 条件(>,<,>=,<=,=,<>(不等于),!=(不等于),)
-- 条件（between...and ...,on,in（枚举）,AND=&&,Or=||,IS NULL,is not NULL,）
-- 先条件（过滤），再排序
SELECT *,empsale as 薪资 FROM employee  where empSale>5000 ORDER BY empSale DESC;
SELECT *,empsale as 薪资 FROM employee  where empSale>5000 and empSale<=20000;
SELECT *,empsale as 薪资 FROM employee  where empSale>40000 OR empSale<=20000;
SELECT *,empsale as 薪资 FROM employee  where empSale BETWEEN 10000 AND 30000;
SELECT *,empsale as 薪资 FROM employee  where empSale=10000 OR empSale=20000 OR empSale=30000;
SELECT *,empsale as 薪资 FROM employee  where empSale in(10000,20000,30000);
SELECT *,empsale as 薪资 FROM employee  where empSale is NULL;
SELECT *,empsale as 薪资 FROM employee  where empSale is not NULL;
-- 模糊查询
-- where 字段 like 条件
-- _%占位符
SELECT empAddress 地址 FROM employee where empAddress LIKE '南_';
SELECT empAddress 地址 FROM employee where empAddress LIKE '_京';
SELECT empAddress 地址 FROM employee where empAddress LIKE '__';
SELECT empAddress 地址 FROM employee where empAddress LIKE '%';
-- LIMIT 参数1，参数2
-- 参数1，从哪开始(默认，0算1)
-- 参数2，查多少条
SELECT * from employee limit 0,3;
SELECT * from employee limit 2,3;
-- 分页公式limit (n-1)*m,m，第n页，m条数据

3.demo03分组统计查询

-- 统计函数
SELECT MAX(empSale) 最高工资 from employee;
SELECT min(empSale) 最低工资 from employee;
SELECT avg(empSale) 平均工资 from employee;
-- count中的参数可以是字段，*以及任何数字
SELECT count(empSale) 多少条数据 from employee;
SELECT sum(empSale) 当前字段总和 from employee;
-- 分组查询  group BY
SELECT empsex ,count(0) FROM employee GROUP BY empsex;
-- 分组查询一般都是单字段分组，如下没有意义
SELECT empsex , empName ,count(0) FROM employee GROUP BY empsex,empName;
-- 分组统计中，统计函数不能用于wherez字句中
-- 因为where是分组前的过滤，需要的是统计之后的二次过滤，只要子段用到统计函数
-- 必须用having ，而且having也必须用于二次过滤,
-- 分组，过滤（条件），排序
SELECT e.empName, AVG(e.empSale) FROM employee e 
GROUP BY (e.empName)
HAVING AVG(e.empSale)>10000; 
ORDER BY AVG(e.empSale)DESC;

4.demo04时间等小查询

-- 时间查询
select SYSDATE();
SELECT NOW();

5.demo05表操作

CREATE table dept(
deptno INTEGER,
deptname VARCHAR(20),
deptloc VARCHAR(50)
)
CREATE TABLE emp(
empno INTEGER ,
empname VARCHAR(50),
empsex VARCHAR(50),
empjob VARCHAR(20),
empmgr INTEGER,
empsal DOUBLE,
deptno INTEGER
)
-- 修改表的结构 DDL：create ，drop ，alter
Drop table dept;
Drop table emp;
alter TABLE emp add(empaddress VARCHAR(20));
-- 操作表中字段 DML :INSERT ,UPDATE ,DELETE
INSERT INTO dept(deptno,deptname,deptloc) VALUES (1,'总办','北京');
INSERT INTO dept(deptno,deptname,deptloc) VALUES (2,'财务部','南京'),
(3,'人事部','东京'),
(4,'销售部','西京'),
(5,'策划部','美国'),
(6,'招生部','日本')
INSERT into emp(empno,empname,empsex,empjob,empmgr,empsal,deptno,empaddress)
VALUES(001,'小一','男','招生总监',011,1000,6,'A地'),
(002,'小二','男','招生老师',001,2000,6,'Z地'),
(003,'小三','女','销售总监',011,3000,4,'D地'),
(004,'小四','男','销售',003,4000,4,'C地'),
(005,'小五','女','策划总监',011,5000,5,'B地'),
(006,'小六','女','策划',005,3500,5,'A地'),
(007,'小七','男','人事副总',011,3600,3,'Q地'),
(008,'小八','女','人事',007,20000,3,'Q地'),
(009,'小九','女','财务总监',011,13000,6,'R地'),
(010,'小十','男','财务',009,8000,6,'D地'),
(011,'小土','女','招生老师',012,9000,6,'C地'),
(012,'小王','女','老板',012,30000,6,'B地')
update emp SET empsal =empsal+500 WHERE empname ='小三';
-- 更新如果不写条件的话，表中所有字段数据都将执行这个更新操作，如下
update emp SET empsal =empsal+500;
delete from emp where empname = '小三';
-- 删除如果不写条件的话，表中所有数据都被删
delete from emp ;

6.demo06多表操作

select e.*,d.* from emp e,dept d;
-- 如上会产生迪卡尔积，最后结果会相乘，比如6*12条数据
-- 我们不能避免，但是可以给定条件让他只显示有用的信息
SELECT e.*,d.* FROM emp e ,dept d WHERE e.deptno = d.deptno;
-- 经典查询1
SELECT  e1.empno,e1.empname,e1.empsex,e1.empsal,e2.empname mgrname
FROM emp e1,emp e2
 WHERE e1.empmgr = e2.empno 
GROUP BY e1.empsal DESC;
-- 经典查询2
SELECT  e1.empname ,e1.empno ,e1.empsex,e2.empname ,d.deptname,e1.empsal
FROM emp e1 ,emp e2,dept d 
WHERE e1.empmgr= e2.empno and e1.deptno=d.deptno;

7.demo07连接查询

-- 连接查询：内连接: INNER join
--                     外连接:
--                             外连接:左外:left JOIN on 条件
--                                          右外:RIGHT JOIN ON 条件
-- 连接查询可以消除笛卡尔积
-- 1.内连接，需要主外键支持
-- 2.外连接，条件自己给
insert into dept(deptno,deptname,deptloc) VALUES(7,'吹牛部','澳大利亚');
-- 左外，以左边的表为参考表，连接右边的表
select e.*,d.* from emp e LEFT JOIN dept d  ON e.deptno=d.deptno;
-- 右外，以右边的表为参考表，连接左边的表
select e.*,d.* from emp e right JOIN dept d  ON e.deptno=d.deptno;
-- 经典查询2
SELECT  e1.empname ,e1.empno ,e1.empsex,e2.empname ,d.deptname,e1.empsal
FROM emp e1 LEFT JOIN emp e2 ON e1.empmgr= e2.empno 
LEFT JOIN dept d ON e1.deptno=d.deptno;
SELECT  e1.empname ,e1.empno ,e1.empsex,e2.empname ,d.deptname,e1.empsal
FROM emp e1 LEFT JOIN emp e2 ON e1.empmgr= e2.empno 
LEFT JOIN dept d ON e1.deptno=d.deptno WHERE e1.empsal>2000;

8.demo08子查询1

-- 子查询=基本查询+条件查询+分组查询+连接查询
-- 结果都是以表格形式返回（都可以出现在from后面）
-- 1.多行多列(在子查询中当做一张表查询，只能在from后面出现)
-- 2.多行单列(一般用于in中)
-- 3.单行多列(在子查询中当做一张表查询，只能在from后面出现)
-- 4.单行单列(可以使用在任意条件上)
-- sql语句的执行顺序：
-- sql语句是从右向左执行，先判断条件，再执行查询结果，
-- 如果有子查询，先执行子语句，再执行主语句
-- 子语句需要用小括号括起来
-- 在书写查询条件时，条件可能不止一个，当多个条件并行查询时，
-- 可能是and也可能是or,
-- 如果是and,会将条件结果不成立的向后放，
-- 如果是or,会将结果成立的向后放
-- 查询所有工资比小七还要高的所有雇员信息
-- 1.查询出小七的工资
SELECT e.empsal FROM emp e WHERE e.empname='小七';
-- 2.将1的结果当做条件查询，进行查询所有比小七工资高的员工信息，合并成子查询
SELECT e2.* from emp e2 where 
e2.empsal>(SELECT e.empsal FROM emp e WHERE e.empname='小七');
-- 查询每个部门的编号，名称，位置，部门人数，平均工资
-- 1.多表联查方式
SELECT d.deptno,d.deptname,d.deptloc,COUNT(0),avg(e.empsal)
FROM emp e RIGHT JOIN dept d  ON e.deptno= d.deptno
GROUP BY d.deptno,d.deptno,d.deptloc;
-- 2.子查询方式
-- 2.1查询出平均工资
SELECT e.deptno,count(0),avg(e.empsal) FROM emp e GROUP BY e.deptno;
-- 2.2将2.1当做虚拟表，与dept表进行连接查询
SELECT d.deptno,d.deptname,d.deptloc,temp.avg,temp.count
FROM dept d left JOIN
(SELECT e.deptno,count(0) count,avg(e.empsal) avg FROM emp e GROUP BY e.deptno) temp
ON d.deptno =temp.deptno;

9.demo09子查询2

-- 子查询的特点：可以分布操作，理解容易
-- in 条件等于给定的几个值
-- >any 大于任意一个 <ANY
-- >all 大于所有     <ALL
但是any，all都必须适用于子查询中
-- 查询工资是5000,3000,70000的
SELECT * from emp e where e.empsal in(5000,3000,7000);
-- 查询工资比小七，小四，小四工资任意一个高的
SELECT * from emp e where e.empsal >ANY
(SELECT e2.empsal FROM emp e2 WHERE e2.empname in('小七','小四','小四'))
SELECT * from emp e where e.empsal >ALL
(SELECT e2.empsal FROM emp e2 WHERE e2.empname in('小七','小四','小四'))

10.demo10子查询3

-- 至少有一个员工的部门编号名称，并统计出这些部门的平均工资，最低工资，最高工资
-- 1.查询出至少有一个员工的部门
SELECT  d.* FROM emp e1 LEFT JOIN dept d ON e1.deptno= d.deptno 
GROUP BY d.deptno ,d.deptname,d.deptloc;
-- 2.在1的基础上进行查询
SELECT d.deptno,d.deptname,d.deptloc,avg(e1.empsal),MIN(e1.empsal),MAX(e1.empsal)
FROM emp e1 LEFT JOIN dept d ON e1.deptno=d.deptno GROUP BY d.deptno ,d.deptname,d.deptloc;
3.将1当做是一张表，与emp进行连接查询
4.对emp表进行分组统计，按照部门尽进行分组统计平均，最低，最高工资
select e2.deptno,avg(e2.empsal),min(e2.empsal),MAX(e2.empsal) FROM emp e2

11.demo11表的完整性

-- 表的完整性体现在约束中
-- 1.非空约束(not null)：当前这个字段不能为空
-- 2.唯一约束(UNIQUE)：当前这个字段的所有值不能相同
-- 3.主键约束(PRIMARY KEY)：等于非空约束+唯一约束
-- 4.默认约束(DEFAULT)：当前字段不设置值，给一默认值
-- 在创建表时都需要进行约束的管理
create table person(
personId int PRIMARY KEY auto_increment,-- 形式主键
personName VARCHAR(255) not NULL,
personSex VARCHAR(4) DEFAULT '男',
personPhone INTEGER UNIQUE
) 
DROP TABLE person;
create table person1(
personId INTEGER,-- 形式主键
personName VARCHAR(255) not NULL,
personSex VARCHAR(4) DEFAULT '男',
personPhone INTEGER UNIQUE
) 
DROP TABLE person;
alter table person1 ADD(
PRIMARY KEY (personId)
)
DELETE 
主键约束：当前记录的唯一标识：每张表都必须要有主键
逻辑主键：有其他用途
形式主键：仅仅是用来区分每条记录的，没有其他含义
-- personId int PRIMARY KEY 形式主键
5.外键约束：

二、Mysql大全

查询当前数据库的时候需要加括号；


浮点型：123.45，m:5（整个数的长度），d：2（小数点后面的位数）；
无符号就是age integer unsigend（代表用后面的取值范围）;

char定长，varchar不定长，比如性别就一个字符就是char（1）；

date time datetime常用








一对一和一对多的区别在于一对一有一个关键字uniqye