MSQL知识点总结

MySQL知识点总结

文章目录

• • MySQL知识点总结
  • • • • • 下载和安装
          • 数据库管理
          • 数据行管理
          • 数据类型
          • 如何来避免SQL注入
          • 必备SQL和表关系
          • 外键约束
          • 常见索引
          • 命中索引
          • 执行计划和事务四大特性
          • 基于Python代码操作事务的示例：
          • 锁
          • 数据库连接池
          • 单例和方法
          • 上下文管理
          • python代码操作MySQL示例1
          • python代码操作数据库示例2
          • python代码操作数据库示例3
          • python代码操作事务
          • 创建用户并授权

1. 下载和安装

-- 下载文件
https://downloads.mysql.com/archives/community/

C:\Program Files\mysql-5.7.31-winx64

-- 创建配置文件
mysqld]
port=3306
basedir=C:\\Program Files\\mysql-5.7.31-winx64
datadir=C:\\Program Files\\mysql-5.7.31-winx64\\
data
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
user="root"
password="root123"

-- 初始化，系统终端输入以下命令自动执行配置文件里的操作
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure
win下初始化可能会出错，需安装两个补丁文件dxwebsetup.exe，vcredist_x64.exe

-- 临时启动
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"

-- 制作Windows服务
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57

-- 启动和关闭服务
net start mysql57
net stop mysql57

-- 删除MySQL服务
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57

-- 设置密码 Windows默认root账户没有密码，登录后可设置 或在配置文件中设置root账户密

set password = password('pwd123');

-- 测试连接
mysql -u root -p;

2. 数据库管理

-- 数据库管理 查看当前所有数据库
show databases;

-- 创建数据库
create database 数据库名 default charset 编码 collate 排序规则;
create database day25db default charset utf8 collate utf8_general_ci;

-- 删除数据库
drop database 数据库名
drop database day25db;

-- 进入数据库
use 数据库名
use day25db；

-- 数据库表管理 查看当前所有表
use day25db；
show tables；

-- 创建表结构
create table 表名(
	列名 数据类型,
	列名 数据类型,
	列名 数据类型
)default charset=utf8;

create table info(
	id not null auto_increment primary key,
	name varchar(16) not null,
	email varchar(32) not null,
	connent text not null,
	password varchar(64) not null,
	age int not null
)default charset=utf8;

-- 删除表
drop table 表名;
drop table info;

-- 清空表
delete from 表名;
delete from info;

truncate table 表名;(是的快，无法回滚)
truncate table info;

-- 修改表 添加列
alter table 表名 add 列名 类型;
alter table 表名 add 列名 类型 default 默认值;
alter table 表名 add 列名 类型 not null default 默认值;
alter table 表名 add 列名 类型 not null auto_increment primary key;

-- 删除列
alter table 表名 drop column 列名;
alter table info drop column email;

-- 修改列类型
alter table 表名 modify column 列名 类型;
alter table info modify column email varchar(32) default None;

-- 修改列 类型 + 名称
alter table 表名 change 原列名 新列名 新类型;
alter table info change password pwd varchar(64） not null;

-- 修改列 默认值
alter table 表名 alter 列名 set default 默认值;
alter table info alter age set default 99;

-- 删除 默认值
alter table 表名 alter 列名 drop default;
alter table info alter email drop default;

-- 添加主键
alter table 表名 add primary key(列名);
alter table info add primary key(id);

-- 删除主键
alter table 表名 drop primary key;
alter table info drop primary key;

3. 数据行管理

-- 数据行管理 新增数据
insert into 表名(列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

insert into info(name,email,connent,password,age) vaules(
'jack','jk258@qq.com','python全栈','jk258369',38);

-- 删除数据
delete from 表名;
delete from 表名 where 条件;

elete from tb1;
delete from tb1 where name="wupeiqi";
delete from tb1 where name="wupeiqi"
and password="123";
delete from tb1 where id>9;

-- 修改数据
update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;

update tb1 set name="wupeiqi";
update tb1 set name="wupeiqi" where id=1;
update tb1 set age=age+1; -- 整型
update tb1 set age=age+1 where id=2;
update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123") where id=2;
-- concat一个函数，可以拼接字符串

-- 查询数据库
select * from 表名;
select 列名,列名,列名 from 表名;
select 列名,列名 as 别名,别名 from 别名;
select * from 别名 where 条件;

select * from tb1;
select id,name,age from tb1;
select id,name as N,age, from tb1;
select id,name as N,age, 111 from tb1;
select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="wupeiqi"
and password="123";

4. 数据类型

-- 数据类型 整型
int[(m)][unsigned][zerofill]
int 表示有符号，取值范围：-
2147483648 ～ 2147483647
int unsigned 表示无符号，取值范围：0 ～4294967295
int(5)zerofill 
-- 仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

tinyint[(m)][unsigned][zerofill]
-- 有符号，取值范围：-128 ～ 127.
-- 无符号，取值范围：0 ～ 255

igint[(m)][unsigned][zerofill]
-- 有符号，取值范围：9223372036854775808 ～9223372036854775807
-- 无符号，取值范围：0 ～ 18446744073709551615

decimal[(m[,d])][unsigned][zerofill]
-- 准确的小数值，m是数字总个数（负号不算），
-- d是小数点后个数。 m最大值为65，d最大值为30。

-- 数据类型 小数
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
-- 单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

OUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
-- 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

-- 数据类型 字符串
char(m)
-- 定长字符串，m代表字符串的长度，最多可容纳255个字符。

text
-- 数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
-- 一般情况下，长文本会用text类型。例如：文章、新闻等。

mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

-- 数据类型 时间
datetime
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

timestamp
YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

date
-- YYYY-MM-DD（1000-01-01/9999-12-31）

time
HH:MM:SS（'-838:59:59'/'838:59:59'）

5. 如何来避免SQL注入

-- SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。
-- 用户输入'or 1=1 --'
select * from users where name='' or 1=1 -- ' and password='123'
cursor.execute("select * from users where name=%s and password=%s", [user, pwd])

6. 必备SQL和表关系

-- 根据条件查询 where 条件
select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4; -- id大于等于2、且小于等于4
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

-- 通配符 “% -”
select * from info where name like "%沛%";
select * from info where email like "_@live.com";

-- 映射(指定列)
select
 id,
 name,
 (select title from depart where depart.id=info.depart_id) as x1
from info;

select
 id,
 name,
 case depart_id when 1 then "第1部门" end v1,
 case depart_id when 1 then "第1部门" else "其他" end v2,
 case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
 case when age<18 then "少年" end v4,
 case when age<18 then "少年" else "油腻男" end v5,
 case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

-- 排序 
select * from info order by age desc; -- 倒序
select * from info order by age asc; -- 顺序

-- limit获取部分
select * from info limit 5; -- 获取前5条数据
select * from info order by id desc limit 3; -- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3; -- 先排序，再获取前3条数据

数据库表中：1000条数据
第一页：select * from info limit 10 offset 0;
第二页：select * from info limit 10 offset 10;
第三页：select * from info limit 10 offset 20;
第四页：select * from info limit 10 offset 30;
...

-- 分组
-- select 表名,count(1) from 表名 group by 表名；
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
select age,count(1) from info group by age;
select depart_id,count(id) from info group by depart_id;
select depart_id,count(id) from info group by depart_id having count(id) > 2;
select count(id) from info;
select max(id) from info;
select age,name from info group by age; -- 不建议
select * from info where id in (select max(id) from info group by age);
select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2;
-- 聚合条件放在having后面

-- 左右连表(outer可省略不写)
主表 left outer join 从表 on 主表.x = 从表.id 
select 
	* 
from 
	info 
	left outer join depart on info.depart_id = depart.id;
	
select 
	info.id,info.name,info.email,depart.title 
from 
	info 
	left outer join depart on info.depart_id = depart.id;

-- 内连表 inner
-- 表 inner join 表 on 条件
select * from info inner join depart on info.depart_id=depart.id;

-- 联合(上下连表)union
select id,title from depart
union
select id,name from info;
-- 列数需相同
select id,title from depart
union
select email,name from info;
-- 自动去重
select id from depart
union
select id from info;
-- 保留所有
select id from depart
union all
select id from info

-- 表关系 
-- 单表，单独一张表就可以将信息保存
-- 一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一 关系。
-- 多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间 多对多关系。

7. 外键约束

-- 外键约束
create table depart(
	id int not null auto_increment primary key,
	title varchar(16) not null
)default charset=utf8;
create table info(
	id int not null auto_increment primary key,
	name varchar(16) not null,
	email varchar(32) not null,
	age int,
	depart_id int not null,
	constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

-- 增加外键
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

-- 删除外键
alter table info drop foreign key fk_info_depart;

8. 常见索引

-- 主键索引：加速查找、不能为空、不能重复。 + 联合主键索引
create table 表名(
	id int not null auto_increment primary key, -- 主键
	name varchar(32) not null
);

create table 表名(
	id int not null auto_increment,
	name varchar(32) not null,
	primary key(id)
);

create table 表名(
	id int not null auto_increment,
	name varchar(32) not null,
	primary key(列1,列2) -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);

alter table 表名 add primary key(列名);
alter table 表名 drop primary key; -- 删除索引时可能会报错，自增列必须定义为

-- 唯一索引：加速查找、不能重复。 + 联合唯一索引
create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	unique ix_name (name),
	unique ix_email (email)
);

create table 表名(
	id int not null auto_increment,
	name varchar(32) not null,
	unique (列1,列2) -- 如果有多列，称为联合唯一索引。
);

create unique index 索引名 on 表名(列名);
drop unique index 索引名 on 表名;

-- 普通索引：加速查找。 + 联合索引
create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	index ix_email (email),
	index ix_name (name)
);

create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	index ix_email (name,email) -- 如果有多列，称为联合索引。
);
create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

9. innodb和myisam引擎的区别

myisam引擎，非聚簇索引（数据 和 索引结构 分开存储）
innodb引擎，聚簇索引（数据 和 主键索引结构存储在一起）

10. 命中索引

-- 命中索引 类型不一致
select * from big where name = 123;		-- 未命中
select * from big where email = 123;	-- 未命中

-- 特殊的主键：
select * from big where id = "123";	-- 命中

-- 使用不等于
select * from big where name != "武沛齐";				-- 未命中
select * from big where email != "wupeiqi@live.com";  -- 未命中

特殊的主键：
select * from big where id != 123;	-- 命中

-- or，当or条件中有未建立索引的列才失效
select * from big where id = 123 or password="xx";			-- 未命中
select * from big where name = "wupeiqi" or password="xx";	-- 未命中

-- 特别的：
select * from big where id = 10 or password="xx" and name="xx"; -- 命中

-- 排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引
select * from big order by name asc;     -- 未命中
select * from big order by name desc;    -- 未命中

-- 特别的主键：
select * from big order by id desc;  -- 命中

-- like，模糊匹配时
select * from big where name like "%u-12-19999";	-- 未命中
select * from big where name like "_u-12-19999";	-- 未命中
select * from big where name like "wu-%-10";		-- 未命中

-- 特别的：
select * from big where name like "wu-1111-%";	-- 命中
select * from big where name like "wuw-%";		-- 命中

-- 使用函数
select * from big where reverse(name) = "wupeiqi";  -- 未命中

-- 特别的：
select * from big where name = reverse("wupeiqi");  -- 命中

-- 最左前缀，如果是联合索引，要遵循最左前缀原则
-- 如果联合索引为：(name,password)
    name and password       -- 命中
    name                 	-- 命中
    password                -- 未命中
    name or password       	-- 未命中

11. 执行计划和事务四大特性

-- 执行计划
explain + SQL语句;

-- 事务 innodb引擎中支持事务，myisam不支持
-- 事务的具有四大特性（ACID）：
-- 原子性（Atomicity）
   -- 原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。

-- 一致性（Consistency）
  -- 执行的前后数据的完整性保持一致。

-- 隔离性（Isolation）
  -- 一个事务执行的过程中,不应该受到其他事务的干扰。

-- 持久性（Durability）
  -- 事务一旦结束,数据就持久到数据库

12. 基于Python代码操作事务的示例：

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, 
	user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # cursor = conn.cursor()
	
    # 开启事务
    conn.begin()

    cursor.execute("select id,age from tran where id=2 for update")
    # fetchall      ( {"id":1,"age":10},{"id":2,"age":10}, )   ((1,10),(2,10))
    # {"id":1,"age":10}   (1,10)
    result = cursor.fetchone()
    current_age = result['age']
    
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

13. 锁

-- 锁 MySQL自带锁 MYISAM支持表锁，不支持行锁；InnoDB引擎支持行锁和表锁。
	-- 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续
	-- 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续
	-- 当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。
	-- select则默认不会申请锁 select * from xxx;
	
-- `for update`，排它锁，加锁之后，其他不可以读写
begin; 
	select * from L1 where name="武沛齐" for update;    -- name列不是索引（表锁）
commit;

begin; -- 或者 start transaction;
	select * from L1 where id=1 for update;			  -- id列是索引（行锁）
commit;

-- `lock in share mode` ，共享锁，加锁之后，其他可读但不可写
begin; 
	select * from L1 where name="武沛齐" lock in share mode;    -- 假设name列不是索引（表锁）
commit;

begin; -- 或者 start transaction;
	select * from L1 where id=1 lock in share mode;           -- id列是索引（行锁）
commit;

-- 共享锁 `lock in share mode` 可以读，但不允许写（update、delete、insert）

14. 数据库连接池

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never, 1 = default = whenever it is requested, 
    # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

15. 单例和方法

import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper(object):

    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn, cursor)

        return result


db = DBHelper()

from db import db

db.exec("insert into d1(name) values(%(name)s)", name="武沛齐666")

ret = db.fetch_one("select * from d1")
print(ret)

ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)

ret = db.fetch_all("select * from d1")
print(ret)

ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)

16. 上下文管理

import threading
import pymysql
from dbutils.pooled_db import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from d1")
    print(ret)

    ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
    print(ret)

17. python代码操作MySQL示例1

import pymysql

# 连接MySQL，自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='
root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 1.新增（需commit）
""""
cursor.execute("insert into tb1(name,password) values('武沛
齐','123123')")
conn.commit()
""""

# 2.删除（需commit）
""""
cursor.execute("delete from tb1 where id=1")
conn.commit()
""""

# 3.修改（需commit)
""""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
""""

# 4.查询
"""‘
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""”

# 关闭连接
cursor.close()
conn.close()

18. python代码操作数据库示例2

import pymysql

# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='
root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 创建数据库
""""
cursor.execute("create database db4 default charset utf8
collate utf8_general_ci")
conn.commit()
""""

# 2. 进入数据库、查看数据表
""""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
""""

# 3. 进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
	id int not null primary key auto_increment,
	title varchar(128),
	content text,
	ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

# 4. 查看数据库中的表
""""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
""""
# 5. 其他 drop table... 略过
# 关闭连接
cursor.close()
conn.close()

19. python代码操作数据库示例3

import pymysql

# 连接MySQL（socket）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('
performance_schema',), ('sys',))

# 2. 创建数据库（新增、删除、修改）
# 发送指令
cursor.execute("create database db3 default charset
utf8 collate utf8_general_ci")
conn.commit()

# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('db3',), ('
mysql',), ('performance_schema',), ('sys',))

# 4. 删除数据库
# 发送指令
cursor.execute("drop database db3")
conn.commit()

# 5. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('
performance_schema',), ('sys',))

# 6. 进入数据库，查看表
# 发送指令
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_
cost',), ('event',), ('func',), ('general_log',),....
# 关闭连接

cursor.close()
onn.close()

20. python代码操作事务

import pymysql

conn = pymysql.connect(host='127.0.0.1',
port=3306, user='root', passwd='root123',
charset="utf8", db='userdb')
cursor = conn.cursor()

# 开启事务
conn.begin()
try:
 cursor.execute("update users set
amount=1 where id=1")
 int('asdf')
 cursor.execute("update tran set
amount=2 where id=2")
except Exception as e:
 # 回滚
 print("回滚")
 conn.rollback()
else:
 # 提交
 print("提交")
 conn.commit()
 
cursor.close()
conn.close()

21. 创建用户并授权

create user '用户名'@'链接地址' identified by '密码'；

create user 'jack'@'&' identified by 'root123';

-- 删除用户
drop user '用户名'@'链接地址'；

drop user 'jack'@'%';

-- 修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

rename user 'jack'@'127.0.0.1' to 'JackQi'@'localhost';

-- 修改密码
set password for '用户名'@'IP地址' = password('新密码');

set password for 'JackQi'@'localhost' = password('pwd123');

-- 授权管理
grant 授权 on 数据库.表 to '用户名'@'IP地址';

grant all privileges on *.* to 'JackQi'@'localhost';
-- 拥有所有数据库的所有权限

grant all privileges on day26db.* to 'JackQi'@'127.0.0.1';
-- 拥有数据库day26的所有权限

grant all privileges on day26db.info to 'JackQi'@'localhost';
-- 拥有数据库day26中info表的所有权限

grant select on day26db.info to 'JackQi'@'localhost';
-- 拥有数据库day6db中info表的查询权限

grant select,insert on day26db.* to 'JackQi'@'loxalhost';
-- 拥有数据库day6db所有表的查询很插入权限

-- 授权完成后必须 'flush privileges;' 将数据读取到内存，从而立即生效

-- 查看授权
show grant for '用户名'@'IP地址';

show grant for 'JackQi'@'%';
show grant for 'JackQi'@'localhost';
show grant for 'JackQi'@'127.0.0.1';

-- 取消授权
revoke 权限 on 数据库.表 from '用户名'@'IP地址';

revoke all privileges day26db.* 'JackQi'@'%';

-- 取消授权完成后必须 'flush privileges;' 将数据读取到内存，从而立即生效