博客

Mysql 小记

  • Post author:
  • Post category:mysql




Mysql




排序



多重排序：


如果排序条件过多时 可以按照多重排序来进行排顺序


`select


name,age


from


student


order by


age asc,name asc


`


这个时候他是会先按找年龄排序，如果有年龄相同的情况，才会按照名字去排序



数据处理函数/单行处理函数



1. 转为大写

  • upper

    select upper(name) from student



2.转为小写

  • lower

    select lower(name) from student



3.取子串

  • substr

    select lower(name,1,1) from student

    取首字母，（name,start,stop）下标开始和结束



4.字符串拼接

  • concat

    select concat(name,age) from student

    这里就表示将 nname，age字段的数据 拼接在一起了

select concat(upper(substr(name ,1,1)) , substr(name ,2,length(name) – 1)) as result from t student;

这个语句表示将name这个字段的 数据 首字母大写其他小写了



5选择语句


case … when … then …when … then …else … end


select kind,number, (case kind when ‘猫科’ then number*2 else number end) as newnum from animal;



分组函数/多行函数

  1. min

  2. max

  3. avg

  4. count

  5. sum



分组函数是自动过滤 Null 的


count（*）


count（字段名）


二者的区别在于 count（*）求的是总记录行数，而count（字段名）计算的则是该字段非空的行数



分组函数的执行顺序


select


…


from


…


where


…


group by


…


having


…


order by


…


limit


…


执行顺序 (不能改变)
 

<span style="background-color:#f8f8f8"><span style="color:#333333">    <span style="color:#116644">1.</span> <span style="color:#770088">from</span>
    <span style="color:#116644">2.</span> <span style="color:#770088">where</span>
    <span style="color:#116644">3.</span> <span style="color:#770088">group</span> <span style="color:#770088">by</span>
    <span style="color:#116644">4.</span> <span style="color:#770088">having</span>
    <span style="color:#116644">5.</span> <span style="color:#770088">select</span>
    <span style="color:#116644">6.</span> <span style="color:#770088">order</span> <span style="color:#770088">by</span>
    <span style="color:#116644">7.</span>  <span style="color:#770088">limit</span>
    <span style="color:#770088">where</span> 后面不能接 分组函数，因为他的执行顺序在 分组前面，也就是说还没分组呢，直接用是用不了的，分组函数可以写在select 后面，因为他是在分组后执行的</span></span>



having


须知：



having不能代替where 单独出现，它需要搭配 group by 一同使用


按找种类分组 显示平均值


select kind,avg(number) from animal group by kind having avg(number) > 5000;



join



1,内连接


inner join…on
 

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">select</span> s<span style="color:#0055aa">.name</span>,s<span style="color:#0055aa">.college</span>,c<span style="color:#0055aa">.course</span>,c<span style="color:#0055aa">.score</span> <span style="color:#770088">from</span> student s <span style="color:#770088">join</span> scores c <span style="color:#770088">on</span> c<span style="color:#0055aa">.score</span> <span style="color:#770088">between</span> <span style="color:#116644">40</span> <span style="color:#770088">and</span> <span style="color:#116644">80</span> <span style="color:#770088">where</span> s<span style="color:#0055aa">.code</span><span style="color:#981a1a">=</span>c<span style="color:#0055aa">.stucode</span>;
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> name <span style="color:#981a1a">|</span> college <span style="color:#981a1a">|</span> course   <span style="color:#981a1a">|</span> score <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 线性代数 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李四 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">78</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">55</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 张三 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">45</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 薛六 <span style="color:#981a1a">|</span> 软件    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">59</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">60</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 赵七 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#116644">7</span> rows <span style="color:#770088">in</span> <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.03</span> sec<span style="color:#999977">)</span></span></span>



2.外连接

 

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql<span style="color:#981a1a">></span> <span style="color:#770088">select</span>
    <span style="color:#981a1a">-></span> s<span style="color:#0055aa">.name</span>,s<span style="color:#0055aa">.college</span>,c<span style="color:#0055aa">.course</span>,c<span style="color:#0055aa">.score</span>
    <span style="color:#981a1a">-></span> <span style="color:#770088">from</span>
    <span style="color:#981a1a">-></span> student s <span style="color:#770088">left</span> <span style="color:#770088">join</span> scores c
    <span style="color:#981a1a">-></span> <span style="color:#770088">on</span>
    <span style="color:#981a1a">-></span> s<span style="color:#0055aa">.code</span><span style="color:#981a1a">=</span>c<span style="color:#0055aa">.stucode</span>;
<span style="color:#981a1a">+--------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> name   <span style="color:#981a1a">|</span> college <span style="color:#981a1a">|</span> course   <span style="color:#981a1a">|</span> score <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+--------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> 李二   <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 线性代数 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二   <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">55</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二   <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">60</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 张三   <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">45</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李四   <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">78</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李四   <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">83</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 王五   <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">98</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 薛六   <span style="color:#981a1a">|</span> 软件    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">59</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 赵七   <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 张柳   <span style="color:#981a1a">|</span> c<span style="color:#981a1a">++</span>     <span style="color:#981a1a">|</span> <span style="color:#221199">NULL</span>     <span style="color:#981a1a">|</span>  <span style="color:#221199">NULL</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 张倩文 <span style="color:#981a1a">|</span> c<span style="color:#981a1a">+</span>      <span style="color:#981a1a">|</span> <span style="color:#221199">NULL</span>     <span style="color:#981a1a">|</span>  <span style="color:#221199">NULL</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+--------+---------+----------+-------+</span></span></span>
<span style="background-color:#f8f8f8"><span style="color:#333333">mysql<span style="color:#981a1a">></span> <span style="color:#770088">select</span>
    <span style="color:#981a1a">-></span> s<span style="color:#0055aa">.name</span>,s<span style="color:#0055aa">.college</span>,c<span style="color:#0055aa">.course</span>,c<span style="color:#0055aa">.score</span>
    <span style="color:#981a1a">-></span> <span style="color:#770088">from</span>
    <span style="color:#981a1a">-></span> student s <span style="color:#770088">right</span> <span style="color:#770088">join</span> scores c
    <span style="color:#981a1a">-></span> <span style="color:#770088">on</span>
    <span style="color:#981a1a">-></span> s<span style="color:#0055aa">.code</span><span style="color:#981a1a">=</span>c<span style="color:#0055aa">.stucode</span>;
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> name <span style="color:#981a1a">|</span> college <span style="color:#981a1a">|</span> course   <span style="color:#981a1a">|</span> score <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 线性代数 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李四 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">78</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">55</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 王五 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">98</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 张三 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">45</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 薛六 <span style="color:#981a1a">|</span> 软件    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">59</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李四 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">83</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 李二 <span style="color:#981a1a">|</span> 数学    <span style="color:#981a1a">|</span> 离散数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">60</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> 赵七 <span style="color:#981a1a">|</span> 计算机  <span style="color:#981a1a">|</span> 高等数学 <span style="color:#981a1a">|</span>    <span style="color:#116644">68</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+---------+----------+-------+</span>
<span style="color:#116644">9</span> rows <span style="color:#770088">in</span> <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span></span></span>


left join…on 表示 左边的是主表 ，所有内容都要显示，右边没有则用null 表示



通用分页



删除表


drop table student; //当student表不存在的时候，会报错


drop table if exists student ; // 当表存在的时候会删除，如果不存在也不会报错



复制表


create table dome2 as select * from demo;



插入表


insert into demo select * from dome2;



存储引擎



1.什么是存储引擎


存储引擎是一个表存储/组织数据的方式


不同而储存引擎，表存储数据的方式不同



2.怎么给表添加/指定 存储引擎

 

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">show</span> <span style="color:#770088">create</span> <span style="color:#770088">table</span> demo;
<span style="color:#981a1a">+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span style="color:#981a1a">|</span> <span style="color:#770088">Table</span> <span style="color:#981a1a">|</span> <span style="color:#770088">Create</span> <span style="color:#770088">Table</span>                                                                                                                                              <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span style="color:#981a1a">|</span> demo  <span style="color:#981a1a">|</span> <span style="color:#770088">CREATE</span> <span style="color:#770088">TABLE</span> <span style="color:#0055aa">`demo`</span> <span style="color:#999977">(</span>
  <span style="color:#0055aa">`id`</span> <span style="color:#3300aa">int</span><span style="color:#999977">(</span><span style="color:#116644">11</span><span style="color:#999977">)</span> <span style="color:#770088">default</span> <span style="color:#221199">NULL</span>,
  <span style="color:#0055aa">`name`</span> <span style="color:#3300aa">char</span><span style="color:#999977">(</span><span style="color:#116644">10</span><span style="color:#999977">)</span> <span style="color:#770088">default</span> <span style="color:#221199">NULL</span>,
  <span style="color:#0055aa">`brith`</span> <span style="color:#3300aa">datetime</span> <span style="color:#770088">default</span> <span style="color:#221199">NULL</span>
<span style="color:#999977">)</span> <span style="color:#770088">ENGINE</span><span style="color:#981a1a">=</span><span style="color:#770088">InnoDB</span> <span style="color:#770088">DEFAULT</span> <span style="color:#ff5500">CHARSET</span><span style="color:#981a1a">=</span>gb2312 <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span style="color:#116644">1</span> <span style="color:#770088">row</span> <span style="color:#770088">in</span> <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span></span></span>


ENGINE=InnoDB DEFAULT CHARSET=gb2312





mysql默认的存储引擎是 InnoDB，默认的编码方式为utf-8



3.如何查看mysql支持哪些存储引擎

  • 命令：

    show engines

    ; 

    <span style="background-color:#f8f8f8">        support（支持）
mysql<span style="color:#981a1a">></span> <span style="color:#770088">show</span> <span style="color:#770088">engines</span>;
<span style="color:#981a1a">+------------+----------+------------------------------------------------------------------------+</span>
<span style="color:#981a1a">|</span> <span style="color:#770088">Engine</span>     <span style="color:#981a1a">|</span> Support  <span style="color:#981a1a">|</span> <span style="color:#770088">Comment</span>                                                                <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------------+----------+------------------------------------------------------------------------+</span>
<span style="color:#981a1a">|</span> MyISAM     <span style="color:#981a1a">|</span> YES      <span style="color:#981a1a">|</span> <span style="color:#770088">Default</span> <span style="color:#770088">engine</span> <span style="color:#770088">as</span> of MySQL <span style="color:#116644">3.23</span> <span style="color:#770088">with</span> great performance                 <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> MEMORY     <span style="color:#981a1a">|</span> YES      <span style="color:#981a1a">|</span> <span style="color:#770088">Hash</span> based, stored <span style="color:#770088">in</span> memory, useful <span style="color:#770088">for</span> <span style="color:#770088">temporary</span> <span style="color:#770088">tables</span>              <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> <span style="color:#770088">InnoDB</span>     <span style="color:#981a1a">|</span> <span style="color:#770088">DEFAULT</span>  <span style="color:#981a1a">|</span> Supports transactions, <span style="color:#770088">row</span><span style="color:#981a1a">-</span><span style="color:#770088">level</span> locking, <span style="color:#770088">and</span> <span style="color:#770088">foreign</span> <span style="color:#770088">keys</span>             <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> BerkeleyDB <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> Supports transactions <span style="color:#770088">and</span> page<span style="color:#981a1a">-</span><span style="color:#770088">level</span> locking                           <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> BLACKHOLE  <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> /dev/null <span style="color:#770088">storage</span> <span style="color:#770088">engine</span> <span style="color:#999977">(</span>anything you <span style="color:#770088">write</span> <span style="color:#770088">to</span> it disappears<span style="color:#999977">)</span>         <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> EXAMPLE    <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> Example <span style="color:#770088">storage</span> <span style="color:#770088">engine</span>                                                 <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> ARCHIVE    <span style="color:#981a1a">|</span> YES      <span style="color:#981a1a">|</span> Archive <span style="color:#770088">storage</span> <span style="color:#770088">engine</span>                                                 <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> CSV        <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> CSV <span style="color:#770088">storage</span> <span style="color:#770088">engine</span>                                                     <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> ndbcluster <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> Clustered, fault<span style="color:#981a1a">-</span>tolerant, memory<span style="color:#981a1a">-</span>based <span style="color:#770088">tables</span>                         <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> FEDERATED  <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> Federated MySQL <span style="color:#770088">storage</span> <span style="color:#770088">engine</span>                                         <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> MRG_MYISAM <span style="color:#981a1a">|</span> YES      <span style="color:#981a1a">|</span> Collection of identical MyISAM <span style="color:#770088">tables</span>                                  <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> <span style="color:#770088">binlog</span>     <span style="color:#981a1a">|</span> DISABLED <span style="color:#981a1a">|</span> This <span style="color:#770088">is</span> a meta <span style="color:#770088">storage</span> <span style="color:#770088">engine</span> <span style="color:#770088">to</span> represent the <span style="color:#770088">binlog</span> <span style="color:#770088">in</span> a <span style="color:#770088">transaction</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span> ISAM       <span style="color:#981a1a">|</span> <span style="color:#770088">NO</span>       <span style="color:#981a1a">|</span> Obsolete <span style="color:#770088">storage</span> <span style="color:#770088">engine</span>                                                <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------------+----------+------------------------------------------------------------------------+</span>
<span style="color:#116644">13</span> rows <span style="color:#770088">in</span> <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span></span>



4.常用的几种存储引擎

  1. MYISAM

    • 他管理的表具有以下特征

      使用三个文件表示每个表

      格式文件 — 存储表结构的定义（mytable.frm）

      数据文件 — 存储表行的内容（mytable.MYD）

      索引文件 — 存储表上索引（mytable.MYI）

    可被转换为压缩、只读表来节省空间




    对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引


    MYISAM 存储引擎的特点：


    可被转换为压缩或只读表来节省空间，这是这种存储引擎的优势 , 不支持事务机制，安全性低

    1. InnoDB 

<span style="background-color:#f8f8f8"><span style="color:#333333">  这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
​
  InnoDB支持事务，支持数据库崩溃后自动恢复机制。
  **InnoDB存储引擎最主要的特点是:非常安全.**
​
  它管理的表具有下列主要特征:
​
  - 每个InnoDB表在数据库目录中以.frm格式文件表示
​
  - InnoDB表空间 tablespace被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据文件和索引）
​
  - 提供一组用来记录事务性活动的日志文件
​
  - 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
​
  - 提供全AcID兼容
​
  - 在MysQL服务器崩溃后提供自动恢复
​
  - 多版本(Mvcc)和行级锁定
​
  - 支持外键及引用的完整性，包括级联删除和更新</span></span>



<font color=’red’>


lnnoDB最大的特点就是支持事务:



以保证数据的安全，效率不高，不能压缩，不能转换为只读，不能很好的节省存储空间


</font>

  1. MEMORY

    MEMORY存储引擎? 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY 存储引擎非常快。

    MEMORY 存储引擎管理的表具有下列特征:

    • 在数据库目录内，每个表均以.

      frm

      格式的文件表示。


    • 表数据及索引被存储在内存中。

      （目的就是快，查询快!)

    • 表级锁机制。

    • 不能包含TEXT或BLOB字段。

      不支持事务

      MEMORY 存储引擎以前被称为HEAP引擎。 MEMORY引擎优点:查询效率是最高的. MEMORY引擎缺点:不安全，关机之后数据消失。因为数据和索引都是在内存当中。



事务



1.事务的理解


只有DML 语句才支持事务 （insert update delete）



2.什么是事务


说白了就是 多条DML语句 执行同时成功 或同时失败



3.事务是怎么做到多条DML语句同时执行成功或失败的呢





InnoDB存储引擎:提供一组用来记录事务性活动的日志文件


事务开启了: insert insert


insert


delete


update


update


update 事务结束了!


在事务的执行过程中，每一条DML的操作都会记录到”事务性活动的日志文件”中在事务的执行过程中，我们可以提交事务，也可以回滚事务。



提交事务?


清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着，事务的结束。并且是一种全部成功的结束。


回滚事务?


将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件 回滚事务标志着，事务的结束。并且是一种全部失败的结束。



4.如何提交事务 回滚事务


提交事务：commit 语句


回滚事务： rollback 语句


在cmd 中演示，执行DML语句，事务是自动提交的，这个时候想回滚是不可以的


如果想要回滚事务成功，需要提前 关闭自动提交事务的操作



关闭自动提交事务的语句： start transaction




事务回滚


 

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#aa5500">#空表</span>
mysql<span style="color:#981a1a">></span> <span style="color:#770088">select</span> <span style="color:#981a1a">*</span> <span style="color:#770088">from</span> demo;                          
Empty <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
<span style="color:#aa5500">#关闭 自动提交事务</span>
mysql<span style="color:#981a1a">></span> <span style="color:#770088">start</span> <span style="color:#770088">transaction</span>;
<span style="color:#770088">Query</span> OK, <span style="color:#116644">0</span> rows affected <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
<span style="color:#aa5500"># 添加数据</span>
mysql<span style="color:#981a1a">></span> <span style="color:#770088">insert</span> <span style="color:#770088">into</span> demo <span style="color:#770088">values</span><span style="color:#999977">(</span><span style="color:#116644">1</span>,<span style="color:#aa1111">'alala'</span>,<span style="color:#116644">20</span><span style="color:#999977">)</span>;
<span style="color:#770088">Query</span> OK, <span style="color:#116644">1</span> <span style="color:#770088">row</span> affected <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
​
mysql<span style="color:#981a1a">></span> <span style="color:#770088">insert</span> <span style="color:#770088">into</span> demo <span style="color:#770088">values</span><span style="color:#999977">(</span><span style="color:#116644">1</span>,<span style="color:#aa1111">'alala'</span>,<span style="color:#116644">20</span><span style="color:#999977">)</span>;
<span style="color:#770088">Query</span> OK, <span style="color:#116644">1</span> <span style="color:#770088">row</span> affected <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
​
mysql<span style="color:#981a1a">></span> <span style="color:#770088">insert</span> <span style="color:#770088">into</span> demo <span style="color:#770088">values</span><span style="color:#999977">(</span><span style="color:#116644">1</span>,<span style="color:#aa1111">'alala'</span>,<span style="color:#116644">20</span><span style="color:#999977">)</span>;
<span style="color:#770088">Query</span> OK, <span style="color:#116644">1</span> <span style="color:#770088">row</span> affected <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
​
mysql<span style="color:#981a1a">></span> <span style="color:#770088">insert</span> <span style="color:#770088">into</span> demo <span style="color:#770088">values</span><span style="color:#999977">(</span><span style="color:#116644">1</span>,<span style="color:#aa1111">'alala'</span>,<span style="color:#116644">20</span><span style="color:#999977">)</span>;
<span style="color:#770088">Query</span> OK, <span style="color:#116644">1</span> <span style="color:#770088">row</span> affected <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
<span style="color:#aa5500">#数据添加成功</span>
mysql<span style="color:#981a1a">></span> <span style="color:#770088">select</span> <span style="color:#981a1a">*</span> <span style="color:#770088">from</span> demo;
<span style="color:#981a1a">+------+-------+------+</span>
<span style="color:#981a1a">|</span> id   <span style="color:#981a1a">|</span> name  <span style="color:#981a1a">|</span> age  <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+-------+------+</span>
<span style="color:#981a1a">|</span>    <span style="color:#116644">1</span> <span style="color:#981a1a">|</span> alala <span style="color:#981a1a">|</span>   <span style="color:#116644">20</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span>    <span style="color:#116644">1</span> <span style="color:#981a1a">|</span> alala <span style="color:#981a1a">|</span>   <span style="color:#116644">20</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span>    <span style="color:#116644">1</span> <span style="color:#981a1a">|</span> alala <span style="color:#981a1a">|</span>   <span style="color:#116644">20</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">|</span>    <span style="color:#116644">1</span> <span style="color:#981a1a">|</span> alala <span style="color:#981a1a">|</span>   <span style="color:#116644">20</span> <span style="color:#981a1a">|</span>
<span style="color:#981a1a">+------+-------+------+</span>
<span style="color:#116644">4</span> rows <span style="color:#770088">in</span> <span style="color:#770088">set</span> <span style="color:#999977">(</span><span style="color:#116644">0.00</span> sec<span style="color:#999977">)</span>
<span style="color:#aa5500">#回滚</span>
mysql<span style="color:#981a1a">></span> <span style="color:#770088">rollback</span>
    <span style="color:#981a1a">-></span> ;
<span style="color:#aa5500">#无数据空表，事务回滚了</span>
<span style="color:#770088">Query</span> OK, <span style="color:#116644">0</span> rows affected <span style="color:#999977">(</span><span style="color:#116644">0.05</span> sec<span style="color:#999977">)</span></span></span>




事务提交


 

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> use animal
Database changed
mysql> select * from demo;
Empty set (0.00 sec)
#关闭自动提交事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#填充数据
mysql> insert into demo values(1,'alala',20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(1,'alala',20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(1,'alala',20);
Query OK, 1 row affected (1.21 sec)

mysql> insert into demo values(1,'alala',20);
Query OK, 1 row affected (0.00 sec)
#提交事务
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
#数据提交成功
mysql> select * from demo;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | alala |   20 |
|    1 | alala |   20 |
|    1 | alala |   20 |
|    1 | alala |   20 |
+------+-------+------+
4 rows in set (0.00 sec)
# 执行回滚失败 ，因为数据已经提交
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
# 数据持久化了  保存了 
mysql> select * from demo;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | alala |   20 |
|    1 | alala |   20 |
|    1 | alala |   20 |
|    1 | alala |   20 |
+------+-------+------+
4 rows in set (0.00 sec)</span></span>



5.事务的四大特性 （ACID）


  1. A 原子性

    – 表示事务是最小的工作单元，不可再分


  2. C 一致性

    – 所有的事务要求，必须是同时成功或失败，保持事务的一致性


  3. I 隔离性

    – A事务和B事务之间有一定的隔离性


  4. D 持久性

    – 事务最终结束的一个保障，事务提交，相当于把原本没有保存在硬盘上数据 保存在硬盘上



6.事务的隔离性


重点研究一下事务的隔离性!!!


A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。这道墙越厚，表示隔离级别就战高。 事务和事务之间的隔离级别有哪些呢?


4个级别

读未提交: read uncommitted

(最低的隔离级别) 什么是读未提交? 事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题就是: 脏读现象!(Dirty Read) 我们称读到直脏薮据- 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步!





打开两个窗口，先更改事务级别为 read uncommitted，同时执行事务，在A窗口添加数据，不提交的情况下，B窗口也能查询到此数据，这叫读未提交



读已提交: read committed

什么是读已提交?





打开两个窗口，先更改事务级别为 read committed，同时执行事务，在A窗口添加数据，不提交的情况下，B窗口不能查询到此数据，只有提交了才能查询到，这叫读已提交


orcal 数据库默认为 此级别



可重复读:repeatable read





打开两个窗口，先更改事务级别为 repeatable read;，同时执行事务，在A窗口添加数据，提交的情况下，B窗口不能查询到此数据，只有B提交了才能查询到，这叫可重复读


mysql 数据库默认为 此级别



序列化/串行化: serializable

(最高的隔离级别)


不许插队 ，同时操作同一张表





打开两个窗口，先更改事务级别为 serializable;，同时执行事务，在A窗口添加数据，未提交的情况下，B窗口不能操作查询到此数据（光标会卡死），只有A提交了，B这里在A提交的同时跳出查询的数据，这叫序列化



 

<span style="background-color:#f8f8f8"><span style="color:#333333">//查看当前事物级别：
SELECT @@tx_isolation;

mysql -uroot -pmysql

//设置mysql的隔离级别：
set session transaction isolation level 设置事务隔离级别

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;</span></span>



索引



1.mysql在查询方面的两种方式

  • 全表扫描

  • 根据索引检索


注意：每个字段都可以添加索引，索引是需要排序的，并且索引的排序和TreeSet数据结构相同，TreeSet底层是一个自平衡的二叉树，在Mysql当中索引是一个B-Tree数据结构

提醒1:在

任何数据库

当中主键上都会自动添加索引对象

，id字段上自动有索引

，因为id是PK。另外

在mysql当中

，一个字段上如果有

unique

约束的话，也会

自动创建索引对象

。

提醒2:在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的

物理存储编号.

提醒3:在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyiISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个

逻辑名称叫做tablespace

的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在.(自平衡二叉树:B-Tree)



2.索引的实现原理


缩小扫描范围，避免全表扫描


什么条件下，我们会考虑给字段添加索引呢?


条件1:数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同)


条件2:该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。


条件3:该字段很少的DM(insert delete update)操作。(因为DM之后，索引需要重新排序。


建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。



3.创建索引


单个字段的索引



create index stu_index_name on student(name)


给student表的name字段添加索引 ，起的别名叫 stu_idnex_name


复合索引


create index stu_index_name on student(name，age)



4.删除索引



drop index stu_index_name on student


将student表中的 stu_index_name 索引对象删除



5.如何判断一个sql语句是否使用了索引查询


在查询语句前 加上 explain


explain select * from student where name=’李四’



6.索引的失效


什么时候索引会失效？
 

<span style="background-color:#f8f8f8"><span style="color:#333333">失效1
select  * from student where name like '%a'

#这里即使name上添加了索引 ，也不会走索引
#因为模糊匹配当中以  ‘%’   开头了
#这种情况尽量避免，避免不了的话只能以模糊查询进行全表查询
#这事一种优化的手段、策略

失效2

or
如果是使用or的时候，需要两边的字段都有索引，才可以，如果有一边没有，另一边的索引也会失效

尽量少用or  可以使用 union 拼接起来

</span></span>


失效3


失效4


失效5



7.索引的分类

 

<span style="background-color:#f8f8f8"><span style="color:#333333">- 单一索引
- 复合索引
- 主键索引
- 唯一性索引</span></span>



视图



1.什么是视图


view ： 站在不同的角度去看待同一份数据



2.创建视图


create view stu_view as select * from student


stu_view 视图别名



3.删除视图


drop view stu_view ;


版权声明：本文为liutongzhuang原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: