十八哥Mysql笔记之提高篇

★★★exists 子查询 ：判断是否存在，返回值True或False，把外层的查询结果，拿到内层，看内层的查询是否成立

 

如 ： 获取 商品类型的表 中，有商品的栏目；

Select cat_id,good_name from category where exists (select * from goods where goods.cat_id=category.cat_id);

 

英国是岛国之一，有海底隧道

 

 

★★★Union ： 连接查询 ， 把两次或者以上的查询结果一起显示，要求两次或多次查询的结果列数一样,类型可以不一样

                     如 ： select user_name,msg_content from feedback where id=1

                          Union select name, content from conmumicate where id=1

                           注意 ： 此时 列名以第一个为准

练习：有两个表A和B， 都有两列 分别为 id num，有部分的id 是相同的，要求把两个表显示出来，如果id相同则把 sum 相加

Select id,sum(num) from (select * from A union select * from B) as temp group by id;

                 注意 ： (1)如果合并的两个表中，它们都有一行所有的数据都相同的话，合并后的表会将此视为重复只保留一条（去重复功能），如果想要不去重复则要 加 all                                     如：select * from A union all select * from B 

                        (2)如果union和order by ,limit 并用则要添加括号，为了分辨整体还是不部分

                                      (3)在两个表合并时，如果子句使用order by 必须陪着limit 使用呢，否则无效

   ★★★Mysql中，集合相乘，就是笛卡尔积，就是两个集合的完全组合。 所以A*B得到的积， 有M*N个元素且不可能重复。

如 ：Ａ有９行，Ｂ有８行，两表相乘有７２行 

　　　Select * from A,B;   注意 ：用逗号进行分割表示乘，形成的过程是另一表从上到下进行匹配而成

 

 

 

★★左连接：以左表为准，去右表找匹配的数据，如果找不到就用null补

            所以至少有N行（即是左表的行数）

注意 ： 与列位置无关

     如 ：现在要从goods,category表中取数据，且将两张表互联

         Select 

          Goods_id,goods.cat_id,goods_name,shop_price

          From

          Goods left join category

          On goods.cat_id = category.cat_id;

      注意 ：（1）在添加自己要的列名时，如果两个表中有相同的列名就要添加表名前缀

            （2）因为goods表在左边所以是left 

            （3）第四，五部分表看做 连接 一张大表 ， 所以后面where,having就像以前写

            （4）右连接 right 替换 left ； 内连接 inner 替换 left 

 

               ★左右连接的区别 ： 

                       （1）以不同的表为准

                       （2）一般最好以左连接为好()，右连接可以转换为左连接

                              

               ★内连接 ：查询左右表都有的数据，即舍弃null的那部分，所以内连接是左右连接的交集

                         注意 ： 目前 mysql不支持外连接 outer join，但是可以用Union达到目的 ，就是把左右连接Union

练习 ：

 

              Select goods_id,goods.cat_id,cat_name,brand_id,brand_name,goods_name

               from

               goods left join category

               on goods.cat_id = category.cat_id 

                left join brand

               on goods.brand_id = brand.brand_id;

           解释 ：把四五部分看做一张表，再次进行左连接　

 

       Select  t1.teamname,matchresult,t2.teamname,matchtime

       From

        Match left join team as t1

        On match.hostteamid = t1.teamid

        Left join team as t2

On match.guestteamid = t2.teamid

Where matchtime between ‘2006-6-1’and’2006--1’;

解析 ：为了区分team的不通所以用as添加了别名

 

★当多表时，同名如果不添加前缀，发生的错误

 

 

★★★视图 ：把查询的结果形成一张虚拟表，并没有真实的数据存在，只是与表的一种查询产生的关系 。 

创建视图的语法 ：Create view 视图名 as select 语句

删除视图的语法 ：drop view 视图名

                    

               如 ：create view goods2 as select id,goods_name,goods_price from goods where goods_price >1500;

          优点 ：（1）简化视图，能把自己经常用的拿出来

                （2）能够起到约束的作用，可以选择想给别人看的列，把表的权限封锁，开放视图的权限

（3）大数据分表时会用到，当表的行数超过了200万行时，就会变慢。因此可以把一张表分为多张表。分表常用取模法：比如200万行分为4张表，就直接求余 à id%4+1 = [1,2,3,4]

(4)还可以把多张表合成一张表

 create view new select from n1  union select from n2

 

（5）视图的数据来自于表，如果表中的数据改变了则视图的数据也会改变；

     当视图的数据与表的数据一一对应时，此时可以进行修改。对于视图insert还应注意，视图必须包含表中没有默认值的列，否则视图不知道如何处理该列  

      ★★视图的algorithm  （以下是它的取值）

          （1）merge ：不写默认，当引用视图时，引用视图的语句与定义视图的语句合并。即是把查询视图的语句与创建视图的语句合并，分析之后形成select语句

 

                例如 ：新生成一个表goods2，进行查询表goods2小于3000元的手机

                       create view goods2 as select id,goods_name,goods_price from goods where goods_price >1500;

                       select * from goods2 where shop_price ＜3000;

                   在执行第二句进行查询的时候，实际上执行的是where shop_price >1500 and shop_price ＜3000 ,第一句执行之后实际上是没有数据的只是空表

最终执行的是       select id,goods_name,goods_price from goods where goods_price >1500 and goods_price <3000’

                        查询的对象是goods表

　　　　　　　　　解析 ：在查询的时候，实际上还是回到了goods表中进行查询

          （2）temptable ：当引用视图时，根据视图的创建语句建立一个临时表

 

               例如 ：  create algorithm=temptable view goods2 as select id,goods_name,goods_price from goods where goods_price >1500;

                           select * from goods2 where shop_price ＜3000;

          最终执行的是2句话 ： 先取出数据放到临时表然后去查临时表

                                 查询的对象是临时表

　　　　　（3）Undefind　：让系统帮你选

 

 

 ★★★字符集 ： 如果某级没有设置字符集，则继承上一级的。一般设置表的即可 。一般出现乱码的问题是因为字符集不符合造成的，可以声明字符的编码进行解决

 

 

 

★★★触发器 ：监控某种情况，并触发某种操作

               如 ：商店拍下商品并减少库存　；　

　　　　★触发器能够监视和触发的操作均为增删改 , 四要素

 

         触发的时间 ：after/before ：比如饭前洗手（before）/饭后洗手（after）

        ★★创建触发器的语法 

           Create trigger 触发器名字

            After/before  insert/update/delete  on  表名

             For each row

             Begin

               Sql语句

              End$

            在表XX触发XX操作之后/之前，每一行开始触发XX操作，然后结束

注意 ：（1）sql即是需要触发的语句，可以是多句，但是也必须是那三个语句范围

（2）多个sql语句用分号隔开，并事先声明用$进行结束，语句为 delimiter $，此时分号只表隔开不是结束 ， 修改回来则是  delimiter ；

 

 

  ★续上，如何在触发器的sql语句中引用行的值 ： 

 

  

（1）对于监视insert来说，新增的行 可以用new来表示，且行中增加的一列：

用  new.列名 来表示

实例 ：两张表，一张是商品表goods；另一张是订单表orde，如下

 

 注意 ：这里不能命名为order ，因为是关键字

 

当进行下单后，库存减少的触发器

进行设置触发器

 

 

 

当插入语句后，触发的情况。这里是的good的gid是和orde的id对应的，

 

 

（2）对于监视delete 来说 ， 删去之后，则之前的是旧行 ， 可以用old来进行表示

     且行中被删除的一列的值：用  old.列名 来表示

 

当删除下单后，库存增加的触发器

 

删除后的触发效果

 

 

（3）对于监视update来说， 更新之后 ， 改之前的是old，改之后是new

        如 ：同一样商品，下单之后，又修改了订单的数量　，则

库存数＝当前库存（未改的）＋未改订单商品数量—修改订单后商品数量　

 

注意　：如果不是同一样商品的话则拆开计算那个语句

 

 

 

★删除触发器的语法 ：drop trgger 触发器的名字； 

★查看触发器 ：show trigger

★after和before的区别

 

 

before案例 ：用于对订单的判断，如订单的数量超出范围，强制修改订单等等

　　　如 ：如果订单超过5，就强制把订单数改为5

 

 

★★★存储引擎 ：数据库存储同样的数据，有着不同的存储方式和管理方式，称为存储引擎　，　常用的有　

Myisam ：批量插入速度快，不支持事务，锁表，支持全文索引

innoDB ：批量插入相对较慢，支持事务，锁行，支持全文索引

 

　　★★事务：指一组操作，要么全部都成功操作，要么都不执行à原子性（不可分割）

在所有的操作完成之前，其他会话不够看见中间的改变à隔离性

当事务完成之后，其影响是会保留下来的，不能撤销à 持久性

数据的变化，在事务的前后保持不变 à一致性

 

比如 ：转钱瞬间银行断电收不到钱，因为只完成了转钱扣钱的操作，转钱进的操作未完成

　　　★事务一般选用InnoDB，所以建表的时候要进行声明

 

     ★事务的语法 ：

（1）开启事务：start  transaction

（2）提交事务：commit  （在输入完要进行的事务语句之后，进行提交）

 

在没提交事务之前，对方都不会看见你的操作结果

有些语句会造成事务的隐式提交比如再来一个start transaction

（3）rollback ：取消事务

事务的原理

 

 

★★★备份 ：系统运行时，增量备份与整体备份

        比如 ： 每周日整体备份一次，周一到周六备份当天

 ★★备份文件语法：

  （1）导出库下的多张表

mysqldump –u用户名 –p密码 库名 表1 表2 > 地址/自定义备份文件名

 

注意 ：如果把表名去掉就能够导出一个库下的所有表

mysqldump –u用户名 –p密码 库名 > 地址/自定义备份文件

 

（2）以库为单位进行导出

mysqldump –u用户名 –p密码 –B 库1 库2> 地址/自定义备份文件

 

（3）导出所有的库

mysqldump –u用户名 –p密码 –A> 地址/自定义备份文件

 

★★★数据库的恢复 ：即是把备份文件里面的语句全部执行一次

　　★★登陆到命令行的状态下

（1）恢复一个库

　　　Source 地址//库名

 

（2）恢复一个表

   Use 库名

   　  Source 地址//表名

 

 

★★不登陆到命令行

 

注意 ：如果直接使用cmd的话，显示‘mysql'不是内部或外部命令,也不是可运行的程序时，需要设置环境变量。我的电脑上右键-〉属性-〉高级-〉环境变量-〉新建：变量名：path 变量值：mysql.exe所在的目录。然后重启CMD窗口

 

 

★★★索引 ：针对数据所建立的目录，加快搜索的速度

 例子 ：　select * from goods where id=100; à 实际上在查找的时候并不一定是从id=1查到id=100，它是会按照一定的优化算法寻找的

 ★★索引的算法 ：

　　　（1）二叉树索引，把数按照大小不断分开在两边 log2N

      （2）哈希索引

　★索引的优点 ：加快查询速度

    索引的缺点 ：降低了增删改的速度，增大表文件的大小（索引文件可能大过表文件）

　　★实例　：如果要导入很多的数据，索引占内容比较大的话，就要先去掉所引，再导入，然后再统一添加索引

    所以，索引的时候必须：（1）不过度索引 （2）索引条件列选择比较频繁的比如id而不是性别 （3）索引散列的值，比如不索引为男，女添加索引

★★文件第一个是表结构文件，第二个是表数据文件（最后的Date），第三个是表索引文件（最后的index）。所以索引文件比数据文件大。

 

 

 ★★索引的分类 ： 

    （1）普通索引 ： index 仅仅是加快查询的速度

    （2）唯一索引 ：unique index 行上的值不能重复

    （3）主键索引 ：primary key 不能重复，一张表只能有一个主键，但是可以有一个或者多个唯一索引

    （4）全文索引 ：fulltext index

★查看一张表上的所有索引 ： show index from 表名  

            垂直排列查看    Show index from 表名 /G

 

★给表中的列添加索引 ： alter table 表名 add index/unique /fulltext自定索引名（列名）

　　　　　　　　　　　alter table 表名 add primary (列名)

  注 ：自定索引名可以省略则默认和列名一样，主键索引直接省略它

 

            注 ：全文索引是依靠相似度来匹配找出

Select * from 表名 where match（全文搜索的列名） agains （‘搜索的内容’）

全文索引是有停止词的，就是对于常见的词比如in it，是不会添加索引的

全文索引在mysql的默认情况下，对中文的意义是不大的，因为英文单词有空格拆分成单词，进而对单词进行索引，中文则没有

★删除非主键索引 ：alter table 表名drop index 索引名 

删除主键索引 ：alter table 表名 drop primar key

 

 

★★★存储过程 ：　procedure ，将一段代码封装起来，当要调用该存储过程来实现。在封装的语句体里面，可以用if，while等控制

  ★★查看现有的封装　：show procedure status

      删除封装 ：drop procedure 名字

       调用封装 ： call 名字（）；  实际调用封装的语句，即是begin到end之间的语句