Python基础学习之数据库（Mysql 操作全收录）

淡淡的烟草味﹌ 2022-12-15 11:17 175阅读 0赞

# Python基础学习之数据库（Mysql 操作全收录） #

### 文章目录 ###

*  Python基础学习之数据库（Mysql 操作全收录）
 *   *  1. 数据库操作
     *   *  1.1 显示数据库
         *  1.2 创建数据库
         *  1.3 使用数据库
         *  1.4 显示所有表
         *  1.4 用户管理
         *  1.5 授权管理
     *  2. 数据表基本操作
     *   *  2.1 创建表
         *  2.2 删除表
         *  2.3 清空表
         *  2.4 修改表
     *  3. 表内容操作
     *   *  3.1 增 （insert）
         *  3.2 删 （delete）
         *  3.3 改 （update）
         *  3.4 查 （select）
         *  3.5 条件查询 (where)
         *  3.6 通配符查询 (% \_)
         *  3.7 限制查询 （limit）
         *  3.8 排序（order by）
         *  3.9 分组 （group by）
         *  3.10 链接表（inner, outer, left, right）
         *  3.11 组合（union）
         *  3.12 去除重复项（DISTINCT）
     *  4. 视图
     *   *  4.1 创建临时表（as）
         *  4.2 创建视图
         *  4.3 删除视图
         *  4.4 修改视图
         *  4.5 使用视图
     *  5. 触发器
     *   *  5.1 六种触发器的创建：
         *  5.2 删除触发器
         *  5.3 使用触发器
     *  6. 存储过程
     *   *  6.1 创建存储过程
         *  6.2 删除存储过程
         *  6.3 执行存储过程

## 1. 数据库操作 ##

### 1.1 显示数据库 ###

SHOW DATABASES;

### 1.2 创建数据库 ###

# 使用 utf-8 字符集
    CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
     
    # 使用 gbk 字符集
    CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

### 1.3 使用数据库 ###

USE db_name;

### 1.4 显示所有表 ###

显示当前使用的数据库中所有表: `SHOW TABLES;`

### 1.4 用户管理 ###

用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（但是不建议这么做）；

1.  创建用户: `create user '用户名'@'IP地址' identified by '密码';`
2.  删除用户: `create user '用户名'@'IP地址' identified by '密码';`
3.  修改用户: `rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';`
4.  修改密码: `set password for '用户名'@'IP地址' = Password('新密码')`

### 1.5 授权管理 ###

1.  查看权限：`show grants for '用户'@'IP地址'`
2.  授权权限：`grant 权限 on 数据库.表 to '用户'@'IP地址'`
3.  取消权限：`revoke 权限 on 数据库.表 from '用户'@'IP地址'`

## 2. 数据表基本操作 ##

### 2.1 创建表 ###

create table 表名(
        列名  类型  是否可以为空，
        列名  类型  是否可以为空
    )ENGINE=InnoDB DEFAULT CHARSET=utf8

### 2.2 删除表 ###

drop table 表名

### 2.3 清空表 ###

delete from 表名
    truncate table 表名

### 2.4 修改表 ###

*  添加列：`alter table 表名 add 列名 类型`
 *  删除列：`alter table 表名 drop column 列名`
 *  修改列：`alter table 表名 modify column 列名 类型;` & `alter table 表名 change 原列名 新列名 类型;`
 *  添加主键：`alter table 表名 add primary key(列名);`
 *  删除主键：`alter table 表名 drop primary key;` & `alter table 表名 modify 列名 int, drop primary key;`
 *  添加外键：`alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);`
 *  删除外键：`alter table 表名 drop foreign key 外键名称`
 *  修改默认值：`ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;`
 *  删除默认值：`ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;`

## 3. 表内容操作 ##

### 3.1 增 （insert） ###

insert into 表 (列名,列名...) values (值,值,值...)
    insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
    insert into 表 (列名,列名...) select (列名,列名...) from 表

### 3.2 删 （delete） ###

delete from 表
    delete from 表 where id＝1 and name＝'kai'

### 3.3 改 （update） ###

update 表 set name ＝ 'kai' where id>1

### 3.4 查 （select） ###

select * from 表
    select * from 表 where id > 1
    select nid,name,gender as gen from 表 where id > 1

### 3.5 条件查询 (where) ###

select * from 表 where id > 1 and name != 'kai' and num = 100;
    select * from 表 where id between 100 and 160;
    select * from 表 where id in (13,23,233)
    select * from 表 where id not in (13,23,233)
    select * from 表 where id in (select nid from 表)

### 3.6 通配符查询 (% \_) ###

*  % 代表多个任意字符；
 *  \_ 代表一个任意字符；

select * from 表 where name like 'kai%'  -- kai开头的所有（多个字符串）
    select * from 表 where name like 'kai_'  -- kai开头的所有（一个字符）

### 3.7 限制查询 （limit） ###

当查询结果很多时，我们可以使用limit 来限制取出其中的某些行；在网页分页中常用；

select * from 表 limit 10;            -- 前10行
    select * from 表 limit 4,10;          -- 从第4行开始的10行
    select * from 表 limit 10 offset 5;    -- 从第5行开始的10行

### 3.8 排序（order by） ###

*  asc ：升序；
 *  desc ：降序；

select * from 表 order by 列 asc              -- 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             -- 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    -- 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

### 3.9 分组 （group by） ###

*  group by 必须在where之后，order by之前；

select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 100 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
    select num from 表 group by num having max(id) > 100

### 3.10 链接表（inner, outer, left, right） ###

-- 无对应关系则不显示
        select A.num, A.name, B.name
        from A outer join B
        Where A.nid = B.nid
    
    -- 无对应关系则不显示
        select A.num, A.name, B.name
        from A inner join B
        on A.nid = B.nid
    
    -- A表所有显示，如果B中无对应关系，则值为null
        select A.num, A.name, B.name
        from A left join B
        on A.nid = B.nid
    
    -- B表所有显示，如果B中无对应关系，则值为null
        select A.num, A.name, B.name
        from A right join B
        on A.nid = B.nid

### 3.11 组合（union） ###

-- 组合，自动处理重合
        select nickname
        from A
        union
        select name
        from B
    
    -- 组合，不处理重合
        select nickname
        from A
        union all
        select name
        from B

### 3.12 去除重复项（DISTINCT） ###

去除重复项时，使用 DISTINCT 关键词，这样只会查找出不重复的项；

SELECT DISTINCT cpu FROM TABLE_CUP_TYPE ;

## 4. 视图 ##

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

### 4.1 创建临时表（as） ###

SELECT 
        *
    FROM
        (SELECT 
            nid, NAME
        FROM
            table1_stu
        WHERE
            nid > 2) AS A
    WHERE
        A.NAME = 'kai';

这里，A就代表了前面搜索的结果，用起来很方便；但是这里只是临时视图，我们只能在这句sql中使用，下面我们来创建视图；

### 4.2 创建视图 ###

*  创建格式：CREATE VIEW 视图名称 AS SQL语句

--格式：CREATE VIEW 视图名称 AS SQL语句
    CREACT VIEW v1 AS
    SELECT nid,
    	   name
    FROM
        A
    WHERE
        nid > 4

### 4.3 删除视图 ###

DROP VIEW v1

### 4.4 修改视图 ###

*  修改视图格式：ALTER VIEW 视图名称 AS SQL语句

-- 格式：ALTER VIEW 视图名称 AS SQL语句
    
    ALTER VIEW v1 AS
    SELECT 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

### 4.5 使用视图 ###

使用视图时，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

select * from v1    -- v1 是视图名

## 5. 触发器 ##

所谓触发器，就是当我们在对表进行 增删改的时候，我们还希望做一些别的事情，这时我们使用触发器，触发器一共有六种，即：插入前，插入后，删除前，删除后，更改前，更改后；

### 5.1 六种触发器的创建： ###

1.  插入前：

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

实例：

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
    IF NEW. NAME == 'kai' THEN
        INSERT INTO tb2 (NAME)
    VALUES
        ('bbb')
    END

NEW表示即将插入的数据行，OLD表示即将删除的数据行。

1.  插入后：

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

实例：

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        IF NEW. num = 666 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('666'),
                ('666') ;
        ELSEIF NEW. num = 555 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('555'),
                ('555') ;
        END IF;
    END

1.  删除前：

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

1.  删除后：

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

1.  更改前：

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

1.  更改后：

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END

### 5.2 删除触发器 ###

DROP TRIGGER tri_after_insert_tb1;

### 5.3 使用触发器 ###

insert into tb1(num) values(666);

## 6. 存储过程 ##

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

### 6.1 创建存储过程 ###

1.  无参数存储过程

-- 创建存储过程
    delimiter //
    create procedure p1()
    BEGIN
        select * from t1;
    END//
    delimiter ;
    -- 执行存储过程
    call p1()
    无参数存储过程

1.  有参数的存储过程

对于存储过程，可以接收参数，其参数有三类：

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

-- 创建存储过程
    delimiter \\
    create procedure p1(
        in i1 int,
        in i2 int,
        inout i3 int,
        out r1 int
    )
    BEGIN
        DECLARE temp1 int;
        DECLARE temp2 int default 0;
        set temp1 = 1;
        set r1 = i1 + i2 + temp1 + temp2;
        set i3 = i3 + 100;
    end\\
    delimiter ;
    
    -- 执行存储过程
    set @t1 =4;
    set @t2 = 0;
    CALL p1 (1, 2 ,@t1, @t2);
    SELECT @t1,@t2;

### 6.2 删除存储过程 ###

drop procedure proc_name;

### 6.3 执行存储过程 ###

-- 无参数
    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)

*  pymysql执行存储过程

#!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 执行存储过程
    cursor.callproc('p1', args=(1, 22, 3, 4))
    # 获取执行完存储的参数
    cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
    result = cursor.fetchall()
    
    conn.commit()
    cursor.close()
    conn.close()
    
    print(result)