使用python操作Mysql

一、SQL机构化查询语言

1、数据库相关

    展示所有数据库：
        show databases；
    查看当前使用数据库：
        select database();
    创建一个数据库：
        create database 数据库名;
            指定编码格式
                create database 数据库名 charset=utf8;
            不能存在才创建
                create database if not exits 数据库名 charset=utf8;
    使用一个数据库
        use 数据库名;
    删除一个数据库
        drop database 数据库名;

---

 2、表相关

    查询当前数据所有表
        show tables;
    创建表
        create table 表名 ( 列名 类型 约束信息，列名 类型 约束信息 );
    查看表信息
        desc 表名
    删除表
        drop table 表名
    修改表名字
        rename table 原始表名 to 新表名
    alter table 表名
        add 列名 类型 约束信息；
            添加列
        drop 列名；
            删除列
        change 原始列名 新列名 类型 约束信息

---

3、数据相关

    查询数据
        select * from 表名
        select 列1，列2，列3... from 表名
        select * from 表名 where id < 5;
    插入数据
        方式一
            全列插入
                注意
                    需要给每一列都赋值
                insert into 表名 values (列1，列2，列3...);
                插入多行
                    insert into 表名 values (),(),(),()
        方式二
            缺省插入1
                需要指定列 指定值
                    有默认值的可以省略
                insert into 表名 (列1，列2) values (值1，值2)；
                插入多行
                    insert into 表名 (列1，列2) values (),(),(),()
            缺省插入2
                insert into 表名 set 列1 = 值1，列2 = 值2...
                只能写入一行
    修改数据
        update 表名 set 列1=值1，列2=值2 where 条件；
            如果不带where则会修改整个表
    删除数据
        delete from 表名 where 条件；
            如果不带where则会清空整个表

---

二、数据库列的约束

数据库的列约束
    主键
        primary key
        一个表中必须要有一个列是主键
        主键默认不能重复
            一般都是有一个独立的列id
        定义
            可以直接在列类型之后使用primary key
            在定义完所有列以后单独使用primary key(列名)
    非空
        not null
    自增长
        auto_increment
    唯一
        unique
    默认
        default
    外键
        foreign key
        和其他表发生关联
        一个表中的外键是另一个表的主键

---

三、数据库中列的数据类型

数据库中列的数据类型
    数字
        int
            整数
            4个字节
        bigint
            整数
            8个字节
        float
            浮点数
            4个字节
        double
            双精度
            8个字节
        decimal
            高精度
                需要指明精度小数位数
    字符串
        char
            长度
        varchar
            可变长度
        text
            长文本
    布尔
        bool
            1
            0
    时间日期
        date
        time
        datetime
        timestamp
    枚举
        enum
            罗列所有可能

---

四、外键

创建表时添加
create table 表名 （列....，constraint 外键名 foreign key（外键列名）references 主表（主键）on update cascade on delete cascade）；
创建表之后添加
alter table 表名 add constraint 外键名 foreign key（外键列名）references 主表（主键）on update cascade on delete cascade；
删除外键
alter table 表名 drop foreign key 外键名；
外键的值 在主表中必须存在
外键的修饰选项
        RESTRICT
            拒绝
                假如有外键使用到主表中的主键
                    在修改删除主键时不能操作
        CASECADE
            级联
                修改删除主表时
                    外键对

应的内容直接删除更新

---

五、常用技术

常用技术
    系统函数函数调用
        select
            user()
                当前用户
            version()
                当前数据库版本
            database()
                当前数据库
            current_date
                当前日期
            current_time
                当前时间
            current_timestramp
                当前日期时间
    聚合函数
        max()
        min()
        avg()
        sum()
        count(任意列名)
    排序(重要)
        order by 列名 排序方式， 列名 排序方式
            asc
                默认升序
            desc
                降序
    分页
        limit
            方式一
                limit n
                    显示前n个
            方式二
                limit m,n
                    从索引m开始显示n个
                        第一个索引是0
                    显示第page页，每页显示size个
                        limit(page-1)*size,size
    分组
        group by
            针对查询结束进行分组
                select count(*), 列名 from 表名 where 条件
                    group by 列名
            having指的是针对分组结果进行处理
                having 列名条件
            select sex, count(*) from teacher where id > 3 group by sex having sex in ('男','女');
            select count(*) as count, sex from teacher where id > 3 group by sex having count > 1;
    去重
        distinct
            去重某一列

---

六、关联查询

嵌套查询
一个查询的结果 作为另外一个查询的内容
select * from student where tid in ( select id from teacher where name = 't1' or name = 't2' );

笛卡尔连接
组合两个表中的所有数据
 一个有m行 一个有n行 最终有m*n行
select * from student, teacher;

连接查询
内连接
select student.name as 学生名,teacher.name as 教师名 from student inner join teacher on student .tid = teacher.id;
左外连接
内连接结果 + 左表内容(右侧补NULL)
select student.name , teacher.name from student left join teacher on student.id * 3= teacher.id;
右外连接
内连结果+右表内容(左侧补NULL)
select student.name , teacher.name from student right join teacher on student.id * 3 = teacher.id;
全连接
左连接 union右外连接
select student.name , teacher.name from student left join teacher on student.id * 3 = teacher.id union select student.name , teacher.name from student right join teacher on student.id *3 = teacher.id;

---

七、用户授权

查看当前用户
select user()
创建用户
create user 'temp1'@'%' identified by '123456';
分配权限
grant all on *.* to 'temp1'@'%';
grant select, insert on mydb.* to 'temp2'@'%';
删除用户
drop user 'temp2'@'%';
刷新权限
flush privileges;

---

八、使用python操作数据库

pymysql
    需要使用pymysql模块
        pip install pymysql
    使用流程
        1.导入模块
        2.构建连接
        3.构建游标
        4.通过游标执行sql
        5.处理sql结果
        6.释放游标与连接