四、Python-数据库（笔记）

数据
- 描述事物特征的符号
记录
- 数据
表
- 文件
库
- 文件夹
数据库管理软件
- mysql、db2
- 关系型：有表结构
- 非关系型：key-value形式存储，没有表结构
数据库服务器
- 运行数据库管理软件的计算机

2、mysql安装

1、登录root
* mysql -u root -p
2. 修改root密码
* mysqladmin -uroot -p password "密新码"
3. 密码破解（略）

3、统一字符编码

默认utf8mb4

4、SQL语句

操作文件夹（库）
- 增
  
  create database 数据库名 charset gbk;
- 查
  
  show create database 数据库名;
  show databases;
- 改
  
  alter database 数据库名 charset utf8;
- 删
  
  drop database 数据库名;
- 切换库
  
  use 数据库名
- 查看当前所在库
  
  select database();
操作文件（表）
- 增
  
  create table 表名(字段类型[(大小)], 字段类型[(大小)]);
  create table 表名(id int, name char);
  ``
- 查
  
  show create table 表名;
  show create table 表名\G;
  show tables;
  desc 表名;
- 改
  
  alter table 表名 modify name char(8);
  alter table 表名 change name NAME char(6);
- 删
  
  drop table t1;
操作文件内容（记录）
- 增
  
  insert 表名(id,NAME) values(1,'name1'),(2,'name2'),(3,'name3');
  insert 表名 values(4,'name4');
  insert 表名(NAME,id) values('name5',5);
- 查
  
  select id,NAME from 数据库名.表名;
  select * from 表名;
- 改
  
  update 数据库名.表名 set NAME='小明';
  update 数据库名.表名 set NAME='张三' where id=2;
- 删
  
  delete from 数据库名.表名 where id=2;
  delete from 数据库名.表名;清空表
  truncate 数据库名.表名清空表
三种类型
1. DDL语句（数据库定义语言）
  - create创建库、表
  - alert修改库、表
  - drop删除库、表
2. DML语句（数据库操作语言）
  - insert插入数据
  - delete删除数据
  - update更新数据
  - select查询数据
3. DCL语句（数据库控制语言）
  - 控制用户访问权限
  - grant授权
  - revoke移除权限

5、存储引擎

存储引擎
- 即表的类型
查看mysql支持的存储引擎
- show engines;
指定表的存储引擎
- create table 表名(id int, name char)engines=innodb;

6、表的增删改查

修改表结构
- 修改表名
  
  alter table 表名 rename 新表名
- 增加字段
  
  alter table 表名 add 字段名类型[(约束条件)], add 字段名类型[(约束条件)];
  alter table 表名 add 字段名类型[(约束条件)] first;
  alter table 表名 add 字段名类型[(约束条件)] after 字段名;
- 删除字段
  
  alter table 表名 drop 字段名;
- 修改字段
  
  alter table 表名 modify 字段名类型[(约束条件)];
  alter table 表名 change 旧字段名新字段名类型[(约束条件)];
复制表
- create table 表名 select 字段名,字段名 from 表名;
复制表结构
- create table 表名 like 表名;

7、数据类型

数字类型
- 整型
  
  宽度指的是显示宽度
- 浮点型
  
  float（255，30）
  double（255，30）
  decimal（65，30）
日期类型
- year
- date
- time
- datetime（1001-9999）8字节
- timestamp（1970-2038）4字节
字符类型
- char：定长
  
  宽度指的是字符宽度
  字符+空格
  优点：存取速度快
  缺点：更占用空间
- varchar：变长
  
  1bytes+字符
  优点：节省空间
  缺点：存取速度慢
枚举类型
- enum(1,2,3)
- 不在范围内的数据，传空值
集合类型
- set(1,2,3)
- 不在范围内的数据，传空值

8、约束条件

Null、Default

Null默认YES
Default默认Null
create table t1(sex enum('male','female') not null default 'male');
Key
- unique key（唯一）
- 单列唯一
  
  create table t1(id int unique, name char unique);
  create table t1(id int, name char, unique(id), unique(name));
- 联合唯一
  
  create table t1(ip char(15), port int, unique(ip, port));
- primary key（不为空、且唯一）主键
- 单列主键
  
  create table t1(id int not null unique);
  create table t1(id int primary key);
  create table t1(id int, primary key(int));
- 复合主键
  
  create table t1(ip char(15), port int, primary key(ip, port));
Extra
- auto_increment（自增长，默认起始值1，默认步长1）
- 必须是key
  
  create table t1(id int primary key auto_increment);

9、建立表之间的关系

多对一
- foreign key(字段名) references 表名(字段名)
  
  先建立被关联的表，并保证被关联的字段唯一
  后建立主表
  create table t1(id int primary key, d_id int, foreign key(d_id) references dep(id));
  删除：先删除主表字段，在删除被关联表的对应字段
  直接删除被关联表字段，需要增加约束on delete cascade on update cascade
  create table t1(id int primary key, d_id int, foreign key(d_id) references dep(id)) on delete cascade on update cascade;
多对多
- 单独建立关系表
一对多
- foreign key + unique

10、单表查询

语法顺序
- select distinct 字段1,字段2,字段3 from 库名.表名
- where 条件
- group by 分组条件
- having 过滤
- order by 排序字段
- limit n;
distant 避免重复
四则运算查询
- select 字段名*12 as 新字段名 from 表名
定义显示格式
- select concat('名字：',字段名,'性别：',字段名) as 新名 from 表名
- select concat(字段名,':',字段名,':',字段名) as 新名 from 表名
- select concat_ws(':',字段名,字段名,字段名) as 新名 from 表名
where
- where不能用聚合函数
- 比较运算符
  
  >|<|>=|<=|<>|!=
- between A and B
- in(A,B,C)
- like
  
  like 'abc%'
  like 'abc__
- 逻辑运算符
  
  and
  not
  or
group by
- set global sql_mode='ONLY_FULL_GROUP_BY'只能取分组的字段
- 聚合函数
  
  max
  min
  avg
  sum
  count
- select 字段A, count(字段B) as 字段B别名 from 表名 group by 字段A;
- 对unique字段分组没有意义
- 分组之后，只能取分组的字段，和聚合函数结果
- 没有group by，则默认整体为一个组，也可以用聚合函数
- group_concat 拼接
  
  select 字段A,group_concat(字段B) from 表名 group by 字段A
having
- 分组之后过滤
- 可以用聚合函数
order by
- 排序
- 默认升序
  
  order by 字段A,字段B asc先排序A，A相同则按B排序
- 降序
  
  order by 字段名 desc
limit
- 限制显示条数
- limit a,b从第a个开始，往后显示b条记录
正则表达式
- where 字段名 regexp '正则表达式';

11、多表查询

连表操作
- 内连接：只取两张表的共同部分
  
  select * from 表A inner join 表B on 表A.id = 表B.id;
- 左连接：在内连接的基础上保留左边表的记录
  
  select * from 表A left join 表B on 表A.id = 表B.id;
- 右连接：在内连接的基础上保留左边表的记录
  
  select * from 表A right join 表B on 表A.id = 表B.id;
- 全外连接：在内连接的基础上保留左、右两边表没有对应的记录
  
  select * from 表A full join 表B on 表A.id = 表B.id;
  左连接 union 右连接
select完整语句
- select 7
- distinct <select_list> 8
- from <左表> 1
- <join_type> join <右表> 3
- on <条件> 2
- where <条件> 4
- group by <字段名> 5
- having <条件> 6
- order by <字段名> 9
- limit <条件> 10

12、子查询

把select语句放到括号里
- in
- 比较运算符
- exists

13、权限管理

创建账号
- 本地账号
  
  create user '用户1'@‘localhost’ identified by '密码'
  登录：mysql -u用户1 -p密码
- 远程账号
  
  create user '用户2'@‘192.168.1.10’ identified by '密码'
  create user '用户3'@‘192.168.1.%’ identified by '密码'
  create user '用户4'@‘%’ identified by '密码'
  登录：mysql -u用户 -p密码 -h 服务端IP
授权
- user：.
- db：库.*
- tables_priv：库.表
- columns_priv：字段A,字段B
  
  grant all on *.* to '用户'@'localhost'
  grant select on 库.* to '用户'@'localhost'
  revoke select on *.* from '用户'@'localhost'
  grant select(字段A),update(字段B) on 库.表 to '用户'@'localhost'
- flush privileges;

14、pymysql

例子

import pymysql

# 建立连接
conn = pymysql.connect(
    host='localhost',
    port='3306',
    usr='root',
    password='699050',
    db='db1',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句
usr = input('usr:')
pwd = input('pwd:')
# sql = 'select * from usr_info where id = %s, pwd = %s;' % (usr, pwd)
# rows = cursor.execute(sql)
# sql注入
sql = 'select * from usr_info where id = %s, pwd = %s;'
rows = cursor.execute(sql, (usr, pwd))
cursor.close()
conn.close()

# 判断
if rows:
    print('登录成功')
else:
    print('登录失败')

视图
- 重命名连表结果
  
  create view 视图名 as 连表
  alter view 视图名 as 新连表
  drop view 视图名

触发器

被动触发，表的增、删、改引起触发器

创建

delimiter //
create triggger 触发器名 after insert on 表名 for each row
begin
    if new.字段 = 值 then
        执行语句
    end if;
end//
delimiter ;

删除

drop trigger 触发器名;

存储过程

创建无参存储过程

delimiter //
create procedure p1()
begin
    SQL语句;
    if 条件1 then
        执行语句;
    elif 条件2 then
        执行语句；
    else
        执行语句;
    end if;
end //
delimiter ;

使用

mysqlcall p1();
pythoncursor.callproc('p1')

创建有参存储过程

delimiter //
create procedure p1(in n1 int,out res int,inout n3 int)
begin
    SQL语句;
    set res=1
end //
delimiter ;

使用

mysqlset @x=0 call p1(1,@x); select @x
python
py cursor.callproc('p1',(1,0)) # @_p1_0=1,@_p1_1=0 cursor.execute('select @_p1_1')

事务

start transaction;
SQL语句1;
SQL语句2;
rollback;
commit;

函数

四、Python-数据库（笔记）

1、数据

2、mysql安装

3、统一字符编码

4、SQL语句

5、存储引擎

6、表的增删改查

7、数据类型

8、约束条件

9、建立表之间的关系

10、单表查询

11、多表查询

12、子查询

13、权限管理

14、pymysql

四、Python-数据库（笔记）