python之mysql进阶

一、视图

用于保存临时表过滤出来的数据，保存到视图表中，创建好的视图表不能增加，可修改、删除、创建

1.创建视图

create view 视图名称 as SQL语句

--示例
create view p1 as select * from student where sid >10
即.将临时表的内容放到视图中

2.修改视图

alter view 视图名称 as SQL语句
--示例
    ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

3删除视图

drop view 视图名称

--示例
drop view a

4.使用视图

select * from 视图名称

--示例
select * from a

二、触发器

当对某张表做，增删改操作时，可以使用触发器自定义关联行为

对你操作的表的指定结果给到触发器里面指定的添加表中，

两个代指数据

new:代指新数据，即更改后的数据
old:代指旧数据，即更改前的数据

1.创建的基本语法

# 插入前
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

View Code

--示例1
delimiter // ---- 更换结束符
create trigger t2 BEFORE INSERT on student for EACH row ----编写触发器并为触发器自定义名称，插入前，
BEGIN----需要添加的代码写在里面
    INSERT into teacher(tname) values(new.sname);
end //
delimiter ;  ---- 当运行的代码结束后更换回来
INSERT into student(gender,class_id,sname) VALUES('女',1,'张')

---------------------当触发器启动之后，需要将编写触发器的代码注释掉，不然会报错


--示例2
delimiter //
create trigger t2 BEFORE INSERT on student for EACH row 
BEGIN
------------和示例的代码不同的是，在这里还做了一个判断，
if new.sname == '张三' THEN
        INSERT into teacher(tname) values(sname);
end
end //
delimiter ;
INSERT into student(gender,class_id,sname) VALUES('女',2,'张三')

插入前的触发器

 1 --示例
 2 delimiter //
 3 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 4 BEGIN
 5 -------这里有两个 判断的语句
 6     IF NEW. num = 666 THEN
 7         INSERT INTO tb2 (NAME)
 8         VALUES
 9             ('666'),
10             ('666') ;
11     ELSEIF NEW. num = 555 THEN
12         INSERT INTO tb2 (NAME)
13         VALUES
14             ('555'),
15             ('555') ;
16     END IF;
17 END//
18 delimiter ;
19 insert into student(gender,class_id,sname) values('女',1,'李四')

插入后的触发器

2.删除触发器

drop trigger t2

3.使用触发器

触发器生成之后，用户不能直接使用，而是需要调动对表的执行【增/删/改】的操作而被引发

-- INSERT into student(gender,class_id,sname) VALUES('女',2,'张三')

三、mysql函数

MySQ中提供了许多内置函数，如下：

CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ，其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ，结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL，则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始，返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

内置函数

1.内置函数的使用

select DATE_FORMAT(time1,'%Y-%m') as 日期,count(1) as 数量 from data1 group by DATE_FORMAT(time1,'%Y-%m')
----使用DATE_FORMAT函数，制作一个bolg中的根据时间去计算bolg数量的一个例子

跟多的中文文档.....

2.自定义函数

--定义一个有参函数
delimiter \\
create function f1(
    i1 int,
    i2 int)
returns INT
BEGIN
    declare num int default 0;
    set num = i1 + i2;
    return(num);
end \\
delimiter ;
SELECT f1(1,100);

3.删除函数

drop function 函数名称；

--实例
drop function f1;

4.执行函数

--获取返回值
declare @i1 varchar(32)
select upper('alex') into @i
select @i1

--在查询中使用
select  f2(1,100),sname from student

四、存储过程

主要的功能是代替程序员写sql语句

 方式一：
            MySQL: 存储过程
            程序：调用存储过程
方式二：
            MySQL：。。
            程序：SQL语句
方式三：
            MySQL：。。
            程序：类和对象（SQL语句

1.可以很方便的存储你的sql语句，只需要执行存储的代码就可以了，方便简洁

delimiter //
create procedure p1() -- 创建一个存储的框 p1创建的别名
            BEGIN
                          -- 要执行的sql语句
                select * from student;
                INSERT into teacher(tname) values("ct");
            END //
delimiter ;
-- 执行代码
    -- mysql里面执行的代码
    call p1()
   --pymysql执行的代码
    cursor.callproc('p1')

1 import pymysql
2 
3 conn = pymysql.connect(host='localhost',user='root',password='1234qwer',charset='utf8',database='db9')
4 cursor = conn.cursor()
5 cursor.callproc('p1')
6 conn.commit()
7 cursor.close()
8 conn.close()

pymysql执行存储过程

2.传参数（in，out，inout）

delimiter //
create procedure p2(
    -- 和函数一样的参数，有几个传递几个
    in n1 int,  
    in n2 int
    )
    BEGIN
                
        select * from student where sid > n1;
    END //
delimiter ;

索引

作用：

约束
加速查找

索引：

主键索引：加速查找 + 不能为空 + 不能重复
普通索引：加速查找
唯一索引：加速查找 + 不能重复
联合索引(多列)：　既是多个一起查看，
- 联合主键索引
- 联合唯一索引
- 联合普通索引

加速查找：

快：
　　select * from tb where name = 'alex'
　　select * from tb where id = 999

-- 如果在无索引的情况下，从前往后依次查找
　　id    name     email
　　1     alex　　12@qq.com
　　......
　　索引：
　　　　id        创建额外文件（某种格式存储）
            name  创建额外文件（某种中格式存储）
           email    创建额外文件（某种格式存储） 
         name email 创建额外文件（某种格式存储）
--创建额外索引的方式
   create index 为创建的索引命名 on 被创建的表单（列名）
-- 示例
    create index ix_name on userinfo(name)

索引种类（某种格式存储）：
    hash索引：
          单值快：查询单个值的速度快
          范围：设定范围去查找
    btree索引： 
          例如二叉树：
                       10
               5                15
           3        8       13      17
        1     4  7     9  12  14 16   18
就像上面这种查找的方式，快捷

建立索引

额外的文件保存特殊的数据结构
查询快：插入更新删除慢
命中索引:
    select * from userinfo where name = 'alex';
    select * from userinfo where name like 'alex';运行速度慢，不建议使用这个查找

主键索引

有两个功能：加速查找，运行速度快

 1 create table in1(
 2     nid int not null auto_increment primary key,
 3     name varchar(32) not null,
 4     email varchar(64) not null,
 5     extra text,
 6     index ix_name (name)
 7 )
 8 
 9 OR
10 
11 create table in1(
12     nid int not null auto_increment,
13     name varchar(32) not null,
14     email varchar(64) not null,
15     extra text,
16     primary key(ni1),
17     index ix_name (name)
18 )

创建表+创建主键

1 alter table 表名 add primary key(列名);

创建主键

1 alter table 表名 drop primary key;
2 alter table 表名  modify  列名 int, drop primary key;

删除主键

普通索引

create index 索引名称 on 表名（列名）； -- 创建索引
drop index 索引名称  on 表名； 删除索引　

-----注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

唯一索引：

create unique index 索引名称 on 表名（列名）；-- 创建索引并提示他是唯一索引unique

drop unique index 索引名称 on 表名； -- 删除并提示是唯一索引

组合索引（最左前缀匹配）：

create unique index 索引名称 on 表名（列名，列名）；-- 创建组合索引，并声明是唯一索引，且创建多个索引

drop unique index  索引名称 on 表名；-- 删除索引

create index ix_name on userinfo(name,email); -- 创建组合索引的示例

　　最左前缀匹配

表：
id          name              email
1           alex                1@qq.com
2           alex1               2@qq.com
3           alex2               3@qq.com
1.select * from userinfo where name='alex';
2.select * from userinfo where name='alex' and email ='1@qq.com';
3.select * from userinfo where email='1@qq.com';
---  当运行1时，会从左至右的方式查找，运行速度快，
---  当运行2时，会从左至右的方式查找，先找到name的内容，在找email的内容
---  当运行3时，会先把email之前的列都查找一遍，也就是从左至右都会查找，而不是直接去查找email列

组合索引效率 > 索引合并

组合索引
    --（name,email）
        select * from userinfo where name='alex' and email='1@qq.com';
        select * from userinfo where name='alex';
索引合并
    -name
    -email
        select  * from userinfo3 where name='alex' and email='asdf';
    select  * from userinfo3 where name='alex';
    select  * from userinfo3 where email='alex';

即.组合索引效率 > 索引合并

名词

覆盖索引
    -- 在索引文件中直接获取数据
索引合并
    --把多个单列索引合并使用

2. 频繁查找的列创建索引

命中索引 *****

在适当的时候，使用正确的索引查找，效率会很快

- 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                -- 不使用索引

效率即时间的速度查找

在命中索引查找之前，可以先让mysql预估执行操作的时间（一般都是正确）

all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

慢：
    explain -- 在sql语句前添加explain可以查看type类型 
    select * from userinfo where name='alex'
    explain select * from userinfo where name = 'alex'
    type:all(全表扫描)
        select * from userinfo limit 1;
快：
    select * from userinfo where email = '1@qq.com' --已经添加过索引
    type:const(s索引)--

DBA工作

　　慢日志

做一个动态处理结果的事件，让不满足条件的表重新设置
-- 执行时间 >10
-- 未命中索引
-- 日志文件路径

- 内存
    show variables like '%query%'
    set global 变量名 = 值
- 配置文件
    mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
                
        my.conf内容：
        slow_query_log = ON
        slow_query_log_file = D:/....
                    
        注意：修改配置文件之后，需要重启服务

配置

*******分页********

基本的分页创建

select * from userinfo3 limit 20,10;

当有几百万条数据，就不能这么去进行分页，

select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)

分页的制作方案

--记录当前页最大或最小id
1.页面只有上一页，下一页
    #max_id
    #min_id
    下一页：
         select * from userinfo3 where id > max_id limit 10;
    上一页：
    select * from userinfo3 where id< max_id \order by id desc limit 10;

2.上一页 192 193  [196]  197  198  199 下一页
select * from userinfo3 where id in (
                        select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)

以上是两个分页的技巧，上一页，下一页，多页点击查找，
不能使用区间查找，有可能中间的某个id被删除，那么取到的索引就会变少

posted @ 2017-06-09 19:27 beiguuu 阅读(126) 评论(0) 收藏举报

刷新页面返回顶部

噼里啪啦

python之mysql进阶

一、视图

三、mysql函数

四、存储过程

索引

公告