mysql基础

1.执行顺序

from,where,group by,having,select,order by
3. 查询语句的执行顺序
当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
1.FROM 子句：执行顺序为从后往前、从右到左。
数据量较少的表尽量放在后面。 
2.WHERE子句：执行顺序为自下而上、从右到左。
将能过滤掉最大数量记录的条件写在WHERE 子句的最右。 
3.GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用
WHERE将不需要的记录在GROUP BY之前过滤掉。 
4.HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有
记录之后才对结果集进行过滤，需要排序等操作。 
5.SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中,
 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。 
6.ORDER BY子句：执行顺序为从左到右排序，消耗资源。

2.case

 ***********************case ---when  ----then    ----end**************

SELECT sno ,
    CASE 
        WHEN degree >= 90 THEN '优秀'
        WHEN degree >= 60 AND degree <90 THEN '合格'
        WHEN degree <60 THEN '不合格'
     END AS grades
FROM score

****************else***************************

SELECT sno ,
    CASE 
        WHEN degree <60 THEN '不合格'
        ELSE '合格'
     END AS grades
FROM score

3.并集、交集和差集

有集合A和B

假设：A={1，2,3,4} B={3,4,5,6}

1、union（并集），合并A和B，但不包含重复

	A union B = {1,2,3,4,5,6}

2、union all（并集），合并A和B，包含重复

	A union B = {1,2,3,4,3,4，5,6}


*****************************3和4mysql不存在**********************


3、except（差集）A - （A和B的交集）,即A中存在，B中不存在

	A except B = {1,2}

4、intersect（交集）

	A intersect B =  {3,4}

4.连接查询

连接分为内连接和外链接（外链接又分为左外链接和右外链接）

内连接：返回匹配的数据

外链接：返回匹配的和不匹配的

5.触发器

1.触发器能够加强数据库表中数据的完整性约束和业务规则等。

2.触发器的操作包括创建、查看和删除。

2.可以将触发器分为“一个执行语句的触发器”和“多个执行语句的触发器”。

                                     创建触发器

  一、   创建一条执行语句的触发器的语法：

             create trigger trigger_name
                before|after  trgger_event
                   on table_name for each row 
                      trigger_STMT

before和after指定了触发器的执行时间

trigger_event表示触发事件，即触发条件，包含delete、insert和update语句

table_name 表触发事件操作表的名字

for each row 表示任何一条记录上的操作满足触发事件都会触发该触发器。

trigger_STMT表示激活触发器后被执行的语句

例：创建触发器实现向部门表（t_dept）中插入数据之前，就会向日记表(t_diary)中插入当前时间。

create trigger tri_diarytime
  before insert 
    on t_dept for each row
       inser into t_diary values(null,'t_dept',now());

二、创建多条执行语句的触发器语法：

   create trigger trigger_name
     before|after  trigger_event
        on table_name for each row
           begin
              trigger_STMT
           end

例：创建触发器实现向部门表（t_dept）中插入数据之后，就会向日记表(t_diary)中插入当前时间。
delimiter //  修改结束符号为（//）
create trigger tri_diarytime2
  after insert
    on t_dept for each row
       begin
         inser into t_diary values(null,'t_dept',now());
         inser into t_diary values(null,'t_dept',now());
       end//
       
delimiter ;修改结束符号为（;）

6.存储过程

1.什么是存储过程？

    存储过程就是数据库中保存（stored）的一系列SQL命令（procedure）的集合。

2.存储过程的好处？

      提高执行性能、可减轻网络负担、可防止对表的直接访问、可将数据库的处理黑匣子化。
  
                                        定义存储过程

语法：

   create procedure 存储过程名（参数的种类1 参数1 数据类型1...）
   begin
        处理内容
    end

注意：存储过程的参数可以分为输入参数和输出参数。定义时在具体参数前指定IN、OUT、INOUT的其中之一（输入参数
时刻省略IN）、INOUT既是输入参数，也是输出参数

例子：
   delimiter //
   create procedure sp_search_customer(IN p_name varchat(20))
    begin
    if p_name is NULL OR p_name='' then
              select * from customer;
    else
              select * from customer where name like p_name;
     end if;
    end
    //  
   delimiter ;

执行存储过程

call 存储过程名（参数）;

                                                      定义输出参数
delimiter //
create procedure sp_search_user2(IN p_nam varchar(20),OUT p_cnt int)
  begin
     if p_nam is NULL or p_nam='' then
        select * from user;
     else
        select * from user where name like p_nam;
     end if;
     select found_rows() into p_cnt;
  end
  //
delimiter ;   

   1、 使用found_rows() 函数取得前一条select语句中检索出记录件数。found_rowa函数取得记录件数后使用select...into命令将其设置到变量p_cnt中。
   2、创建了存储过程后，指定OUT/OUTIN类型的参数时，在参数名的头部加上【@】。这时处理结果保存到OUT型变量num中，然后使用select @num语句显示变量num的值。

call sp_search_user2('l%',@num);

select @num;

                                           使用if命令实现多重条件分支

if 表达式 then
   处理
elseif 表达式 then  
   处理
elseif 表达式 then 
...
else
   处理

end if;
例子:
create procedure sp_search_employee(IN p_depart int) 
   begin
     if p_depart=1 then
        select * from employee where depart='研究部';
     elseif p_depart=2 then
        select * from employee where depart='AC部';
     elseif p_depart=3 then
        select * from employee where depart='IT部';
     elseif p_depart=4 then
        select * from employee where depart='人事部';
     else
        select * from employee where depart='经理部';
     end if;
   end
   //

                                            使用case命令实现多重条件分支

create procedure sp_search_employee2(IN p_depart int)
  begin
    case p_depart
      when 1 then
         select * from employee where depart='研究部';
     when 2 then
          select * from employee where depart='AC部';
     when 3 then
         select * from employee where depart='IT部'
     when 4 then
         select * from employee where depart='人事部';
     else
         select * from employee where depart='经理部';
     end case;
  end
  //

                                                   定义本地变量
  
使用本地变量（局部变量）时，要使用declare进行声明、给变量赋值使用set命令

create procedure sp_search_employee3(IN p_deaprt int)
  begin
    declare tmp char(4);
    case p_depart
      when 1 then
        set tmp='研究部'
      when 2 then
        set tmp='AC部'
      when 3 then
        set tmp='IT部' 
      when 4 then
        set tmp='人事部'
      else
        set tmp=‘经理部’
     end case；
     select * from employee where depart=tmp；
  end
  //

                                              使用循环语句（while和repeat）

1、计算p_num的阶乘

 create procedure sp_factorial(IN p_num int,OUT p_result int)
  begin
   set p_result=1;
   while p_num>1 do
    set p_result = p_result * p_num ;
    set p_num = p_num - 1;
   end while;
  end
  //

调用： call sp_factorial(5,@res);
       
       
     select @res;

                                         while与repeat命令的区别

create procedure sp_factorial2(IN p_num int,OUT p_result int)
  begin
    set p_result = 1;
    repeat
      set p_result = p_result * p_num;
      set p_num = p_num - 1;
    until p_num <=1 end repeat;
  end
  //

区别:使用repeat命令后，不管until语句后的条件表达式的结果如何，循环程序至少被执行一次。当参数p_num=0时，0与p_result（初值1）的乘机为0，从而输出参数p_result的值变为0 了。

 

7.复制表结构和数据

1.表结构加数据的复制

  create table 新表名 select * from 旧表名

2.复制表的列构造

create table 新表名 like 旧表名

3.数据的复制

insert into 表名 select * from 含有数据的表

8.函数

                                      函数的定义

create function 函数名（参数1 数据类型1
                        参数2 数据类型2...）

returns 返回值类型
begin
  任意系列SQL语句
  return 返回值；
end

1、存储函数中不能指定参数的类型。指定的参数只能是输入型的，不支持输出/输入输出型的参数。
2.星调用方返回结果值
 create function f(p_num int)
  returns int
    begin
      declare p_res int default 1;
        while p_num>1 do
          set p_res = p_res * p_num;
          set p_num = p_num - 1;
        end while;
        return p_res;
    end
    //

9.技巧

1.使用delimiter修改结束符号（；）

2.分号（；）和（\g）和(\G)都可以作为结束符号，而且（\G）更美观

3.避免查询重复记录distinct   select distinct * from table_name

4.再具体使用关键字IN时，查询的集合中如果存在NULL，则不会影响查询；如果使用关键字NOT IN，查询的集合中如果
存在NULL，则不会有任何的查询结果。

10.事务

六：事务

1.事务的四大特性

事务是必须满足4个条件（ACID）： 

Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性） 

1.事务的原子性：在一组事务中所有操作，要么全部执行，要么全部都不执行。

2.一致性 ： 一旦一个事务结束了，系统所处的状态与它的业务规则保持一致(所有的数据保持一致)。

3.隔离性：多个事务同时操作统一数据时，每个事务都有各自的完整数据空间。

4.持久性：一旦事务完成，事务的结果应该持久化。

11.视图

1.使用视图的目的：为了提高复杂SQL语句的复用性和表操作的安全性。

2.视图的特点：视图是由基本表（实表）产生的表（虚表）、对视图的建立和删除不影响基本表、但对视图内容的CRUD

操作直接影响基本表。当视图来自多个基本表时，不允许添加和删除数据。

3.视图是一张虚表，没有存储数据

4.由于视图是一张虚表，所以对视图数据的操作，实际上是对基本表的操作。

                                          创建视图

一：   create view view_name
       as
       select id,name from table_name;

删除视图：drop view view_name1,view_name2,...

                                          修改视图

一、通过create or replace view来修改视图

语法：create or replace view view_name as 查询语句

create or replace view view_selectproduct
as
select name from t_product;

二、通过alter语句修改视图

语法： alter view view_name as 查询语句

注意：如果视图定义了【with check option】命令，将不能插入或更新不符合视图的检索条件的数据。
 create view view_name
       as
       select语句
       with check option；

12.数据类型

1.日期和时间
  date   1999-1-1
  datetime  1999-1-1 23:22:55
  timestamp  2038年的某个时刻
  time  23:22:55
  year   2018
2.TEXT字符(存储文字内容的纯文本)只能存储字符数据
  tinytext
  text
  mediumtext
  longtext
3.binary二进制系列
  binary（M）可以存放二进制数据（包括图片、音乐或视频）
  varbinary（M）（只能存储字符数据）
4.blob系列（可以存储图片、音乐或视频）
  tinyblob
  blob
  mediumblob
  longblob

**************************************

数字类型
   整数: tinyint(一个字节-128~127)、smallint（2个字节）、mediumint（三个字节）、int（四个字节）
        bigint（八个字节）
   浮点数: float、double、real、decimal
   日期和时间: date、time、datetime、timestamp、year
字符串类型
   字符串: char、varchar
   文本: tinytext、text、mediumtext、longtext
   二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

************************************************

char()与varchar():
例如对于简体中文字符集gbk的字符串而言，varchar(255)表示可以存储255个汉字，而每个汉字占用2个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间；而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。

13.索引

   1. 一个索引包含表中按照一定顺序排序的一列或多列字段。索引的操作包括创建索引、修改索引和删除索引。

   2.为什么要使用索引？ 为了提高从表中检索数据的速度，还可以保证字段的唯一性，从而实现数据库表的完整性。

   3.索引分为B型树索引和哈希索引。索引过多会占据许多的磁盘空间。

   4.mysql支持六种索引：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

   5.以下情况适合创建索引:

      经常被查询的字段，即在where子句中出现的字段。

      在分组的字段，即在group by子句中出现的字段。

      主键或外键

      设置唯一完整性约束的字段

   6.以下情况不适合创建索引

      在查询中很少被使用的字段

      有许多重复值的字段

                                 普通索引

一、创建和查看普通索引

   1.在创建索引时，可以指定索引的长度。这是因为不同存储引擎定义了表的最大索引数和最大索引长度。mysql所支
持的存储引擎对每个表至少支持16个索引，总长度为256个字节。

   2.创建索引的语法

  create table table_name(

     属性名  数据类型
     ...
     index|key  索引名（属性名1 【长度】 【asc|desc】）
     );
例子：
   create table t_dept(
      deptno  int,
      dname  varchar(20),
      loc  varchar(40),
      index  index_deptno(deptno)
       );
   查看创建的索引是否被使用

    explain select * from t_dept where deptno=1 \G   

    根据结果possible_keys和key字段的值都为index_deptno，则说明索引已经存在，而且已经开始使用。

二、在已经存在的表上创建索引
 

  create index index_name

     on  table_name (属性名 【长度】 【asc|desc】

     create index index_deptno on t_dept(deptno)

三、通过alter table创建索引
  

alter table table_name

  add index|key  index_name (属性名 【长度】 【asc|desc】

alter table t_dept add index index_deptno(deptno)  

                                    唯一索引

四、创建和查看唯一索引

 create table table_name(

     属性名  数据类型
     ...
    unique index|key  索引名（属性名1 【长度】 【asc|desc】）
     );
   create table t_dept(
      deptno  int,
      dname  varchar(20),
      loc  varchar(40),
      unique index  index_deptno(deptno)
       );
五、在已经存在的表上创建唯一索引
 
  create unique index index_name

     on  table_name (属性名 【长度】 【asc|desc】

     create unique index index_deptno on t_dept(deptno)

六、通过alter table创建唯一索引
  

alter table table_name

  add unique index|key  index_name (属性名 【长度】 【asc|desc】

alter table t_dept add unique index index_deptno(deptno) 

                                       全文索引（只能在存储引擎为MyISAM的数据库表上创建全文索引）
七、创建和查看全文索引
   1.全文索引主要关联char、varchar和 text字段上

  create table table_name(
     属性名  数据类型
     ...
    fulltext index|key  索引名（属性名1 【长度】 【asc|desc】）
     );

 create table t_dept(
      deptno  int,
      dname  varchar(20),
      loc  varchar(40),
      fulltext index  index_loc(loc)
       )engine=MyISAM;
八、在已经存在的表上创建全文索引
 
  create fulltext index index_name
     on  table_name (属性名 【长度】 【asc|desc】

     create fulltext index index_loc on t_dept(loc)

九、通过alter table创建全文索引
  

alter table table_name

  add fulltext index|key  index_name (属性名 【长度】 【asc|desc】

alter table t_dept add fulltext index index_loc(loc) 

                                   多列索引

一、创建和查看多列索引

   1.全文索引主要关联char、varchar和 text字段上

  create table table_name(

     属性名  数据类型
     ...
     index|key  索引名（属性名1 【长度】 【asc|desc】），
                      （属性名2 【长度】 【asc|desc】）
     );

 create table t_dept(
      deptno  int,
      dname  varchar(20),
      loc  varchar(40),
      index  index_dname_loc(dname,loc)
       );

二、在已经存在的表上创建多列索引
 
  create index index_name

     on  table_name (属性名1 【长度】 【asc|desc】
                    (属性名2【长度】 【asc|desc】

     create index index_dname_loc on t_dept(dname,loc)

三、通过alter table创建多列索引
  

alter table table_name

  add index|key  index_name (属性名1 【长度】 【asc|desc】

                             (属性名2 【长度】 【asc|desc】

alter table t_dept add index index_dname_loc(dname,loc)  

                           删除索引

drop  index index_name  on table_name