MySQL3>mysql进阶

　　部分内容转自：https://www.cnblogs.com/wupeiqi/articles/5713323.html

★视图

1，有点类似给临时表起个别名，然后保存在数据库里，下一次可以直接通过视图名字访问
2，视图是一个【虚拟表】
3，调用的时候得把视图当成一个【表】来操作

创建视图:
	create view v1 as select * from student where sid > 10;
修改视图：
	alter view v1 as select * from student where sid < 10;
删除视图：
	drop view v1

★触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为

删除

drop trigger t1;

调用

无法自己调用，由于对表的某些操作【增删改】触发

创建

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

生成触发器

触发器语法

DELIMITER //
CREATE TRIGGER demo BEFORE insert
ON tb1 FOR EACH ROW
BEGIN
insert into tb2(name) values('hello');
END
//
DELIMITER ;

插入数据前触发器

delimiter //
create trigger t1 after insert on tb1 for each row
begin
insert into tb2(name) values(new.name);    # new代指新插入的数据
end//
delimiter ;

插入数据后触发器

new：
    代指新插入的数据
    插入的时候
old：
    代指老数据
    删除的时候
new/old：
    新/老数据
    更新的时候

关于new old

★函数

◇内置函数

select curdate();    时间
select char_length('hello');    长度
select concat('hello','world');        拼接
select date_format('2017/5/6','%Y-%m')    格式化时间

◇自定义函数

delimiter \
create function f1(
i1 int,
i2 int
)
returns int
begin
    declare num int default 0;
    set num = i1 + i2;
    return(num);
end \
delimiter ;

自定义函数

★存储过程

一组为了完成特定功能的【SQL语句集】
1 把SQL语句封装成存储过程
2 将SQL语句和程序解耦
3 存储过程写在数据库服务端里

◇调用

mysql:	call p1();
pymysql:	cursor.callproc('p1') ---> 有结果集-->通过fetchall()取值

◇删除

drop procedure p1;

◇创建

1，普通的存储过程

delimiter //
create procedure p1()
begin
    select * from student;
    insert into teacher(tname) values('ct');
end//
delimiter ;

简单的存储过程

2，带参数的存储过程

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

删除：
drop procedure p1;
调用：
-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

delimiter //
create procedure p2(
in n1 int,
in n2 int    # 没有用到
)
begin
select * from student where sid > n1;    # 参数传给SQL语句做条件判断
end //
delimiter ;

调用:
mysql:
    call p2(12,2);
pymysql:
    cursor.callproc('p2',(12,2))---> 有结果集-->通过fetchall()取值

参数IN

delimiter //
create procedure p3(
in n1 int,
out n2 int        # 引用
)
begin
    set n2 = 123;    # 引用改变
    select * from student where sid > n1;
end //
delimiter ;

调用：
mysql:
    需要先创建传进去的变量：set @v1 = 0;
    call p3(10,@v1);    # 返回结果集
    select @v1;            # 查看返回值
pymysql：
    结果集：
        cursor.callproc('p3',(12,2))
        r1 = cursor.fetchall()
        print(r1)
    返回值：
        cursor.execute('select @_p3_0,@_p3_1')
        r2 = cursor.fetchall()
        print(r2)a

参数OUT

可传进去，在里面可以用，还可以在外面用
delimiter //
create procedure p4(
in i1 int,
inout i2 int,
out r1 int
)
begin
    declare temp1 int;
    declare temp2 int default 0;
    
    set temp1 = 1;
    
    set r1 = i1 + temp1 + temp2;
    set i2 = i2 + 100;

end //
delimiter ;

调用：
mysql：
    需要先创建(不创建的话随便一个变量默认为Null)传进去的变量：
        set @i2 = 4; set @r1 = 0;
    call p4(10,@i2,@r1);
    查看返回值：select @i2,@r1;

参数INOUT

3，事务操作的存储过程

delimiter //
create procedure p5(
out p_return_code tinyint
)
begin
    declare exit handler for sqlexception    # 声明如果出现异常则执行以下代码
    begin
        set p_return_code = 1;    
        rollback;    # 回滚
    end;
start transaction;    # 开始事务
    delete from tb1;
    insert into tb2(name) values('seven');
commit;

set p_return_code = 2;

end //
delimiter ;

事务操作的存储过程

4，游标的存储过程

delimiter //
create procedure p6()
begin                         # 开始
    declare row_id int;                 # 声明变量row_id
    declare row_num int;                 # 声明变量row_num
    declare done int DEFAULT FALSE;        # 声明done = False
    declare temp int;                    # 声明变量temp 
    
    declare my_cursor CURSOR FOR select id,num from A;            # 声明游标，并把数据放进游标里
    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;        # 声明没有没有数据时，则break
    
    open my_cursor;                            # 打开游标
        xxoo: LOOP                                # 开始循环
            fetch my_cursor into row_id,row_num;    # 从游标里取数据
            if done then                             # 如果done是真的
                leave xxoo;                            # 则break
            END IF;                                    # 结束if
            set temp = row_id + row_num;            # 计算temp
            insert into B(number) values(temp);        # 往B表插入一条数据，值是temp
        end loop xxoo;                            # 离开循环
    close my_cursor;                        # 关闭游标
end  //                        # 结束
delimiter ;

游标的存储过程

5，动态执行SQL

此处应有内容

★索引

◇作用

约束

加速查找

◇索引的分类

普通索引

主键索引：不为空，唯一约束（不可含null）

唯一索引：唯一约束（可含null）

联合索引（多列） ---> 最左前缀匹配

联合主键索引

联合唯一索引

联合普通索引

◇索引的种类

hash索引：索引表

单值速度快

查找范围慢

btree索引：二叉树（innodb默认）

◇SQL命令

创建索引：
	普通：create index 索引名 on 表名(列名);
	唯一：create unique index 索引名 on 表名(列名);
	主键：alter table 表名 add primary key(列名);
	联合：create index 索引名 on 表名(列名,列名);
	联合唯一：create unique index 索引名 on 表名(列名,列名);
删除索引：
	普通：drop index 索引名 on 表名;
	唯一：drop unique index 索引名 on 表名;
查看索引：
	show index from 表名

◇名词

覆盖索引:在索引文件中直接获取数据

1，先把email创建成索引

2，select email from userinfo where email = 'sdfsd';

# 去email列里找email，只用在索引文件里就可以找到

索引合并：把多个单列索引合并使用

1，前提：id是索引，email是索引

2，select * from userinfo where id < 50 and email = 'sdf';

组合索引效率 > 索引合并

◇不正确使用索引

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

不正确使用索引

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

其他注意事项