mysql多字段多规则排序 mysql多重排序

转载

mob64ca14085c24 2023-10-19 15:44:51

文章标签 mysql多字段多规则排序 mysql 数据库 database 索引 文章分类 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;

mysql多字段多规则排序 mysql多重排序_索引

分组函数/多行函数

min
max
avg
count
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 表示

通用分页

mysql多字段多规则排序 mysql多重排序_database_02

删除表

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.常用的几种存储引擎

MYISAM

他管理的表具有以下特征
使用三个文件表示每个表
格式文件 --- 存储表结构的定义（mytable.frm）
数据文件 --- 存储表行的内容（mytable.MYD）
索引文件 --- 存储表上索引（mytable.MYI）

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

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

MYISAM 存储引擎的特点：

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

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>

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 事务结束了!

在事务的执行过程中，每一条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）

A 原子性
- 表示事务是最小的工作单元，不可再分
C 一致性
- 所有的事务要求，必须是同时成功或失败，保持事务的一致性
I 隔离性
- A事务和B事务之间有一定的隔离性
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

mysql多字段多规则排序 mysql多重排序_索引_03

失效4

mysql多字段多规则排序 mysql多重排序_database_04

失效5

mysql多字段多规则排序 mysql多重排序_mysql_05

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 ;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java 结构体转form encoded java 结构体转 c 结构体

下一篇：python 计算数据包络分析 python处理数据的包

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯