第六周总结

MySQL主键外键

主键：primary key

　　相当于not null加unique

　　InnoDB规定了一个表必须要有且只有一个主键

　　如果你不指定主键则会采用隐藏的字段作为主键

　　当表中没有主键但是有非空且唯一的字段则自动升级为主键（自上而下第一个）

　　（相当于目录，可以加快查询速度）

约束条件之自增：auto_increment

　　配合主键使用，让主键自动逐个加一

　　（自增不会因delete from删除数据而重置顺序号，但可以通过truncate重置）

外键：foreign key

　　记录表和表之间数据关系的字段

外键字段的特性

　　1.在创建表的时候一定要先创建被关联表（自身没有外键字段的表）

　　2.在插入数据的时候也是先插入被关联表再插入关联表

　　　　只能够填写被关联字段中出现的值

　　3.被关联表中的数据无法自有删除和修改

　　4.级联更新：on update cascade　　级联删除：on delete cascade

　　（写了级联更新，级联删除之后，对一个表中的字段进行更新和删除时，另一个表中的对应字段也会被修改和删除）

修改表相关SQL语句

1.修改表名 rename

　　alter table 表名 rename 新表名；

2.增加字段 add

2.1.

　　alter table 表名 add 字段名 数据类型 [完整性约束条件],

　　add 字段名 数据类型[完整性约束条件];

2.2.

　　alter table 表名 add 字段名 数据类型 [完整性约束条件]first;

　　alter table 表名 add 字段名 数据类型 [完整性约束条件]after 字段名;

 3.删除字段 drop

　　alter table 表名 drop 字段名；

4.修改字段 change

（modify只能改数据类型，完整约束，不能改字段名

change可以，建议用change）

　　alter table 表名 modify 字段名 数据类型 [完整性约束条件];

　　alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];

　　alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];

MySQL数据查询

where筛选

where后面写条件——根据该条件筛选

模糊查询：

　　关键字：

　　　　like

　　关键符号

　　　　%　　匹配任意个数的任意字符

　　　　_　　匹配单个个数的任意字符

group by分组

分组之后默认只可以直接获取到分组的依据，无法再获取内部单个个体数据，如果想要获取需要借助一些方法

set global sql_mode="strict_trans_tables,only_full_group_by"

融合函数

主要用于分组之后的数据处理

　　max　　最大值

　　min　　最小值

　　avg　　平均值

　　sum　　求和

　　count　　计数

having过滤

where用于分组之前的筛选

having用于分组之后的过滤

distinct去重

select distinct post from emp;

order by排序

　　select * from emp order by salary;　　默认是升序

　　select * from emp order by salary asc;　　手动升序，asc是升序关键字

　　select * from emp order by salary desc;　　降序，desc是降序关键字

　　select * from emp order by age asc,salary desc;　　先按age升序，再按salary降序

limit分页

　　select * from emp limit 5;　　一个数字表示条数

　　select * from emp limit 5,10;　　第一个数字表示起始位置，第二个表示条数

regexp正则（了解）

（正则：使用一些特殊符号的组合去字符串中筛选出符合条件的数据（爬虫再讲））

select * from emp where name regexp '^j.*(n|y)$';

group_concat()方法　　用于分组之后

获取除分组以外其他字段数据，本质可以理解为是拼接操作

　　select post,group_concat(name) from emp group by post;

　　select post,group_concat(name,':',salary) from emp group by post;

 2.concat()方法　　用于分组之前

　　select post,concat(name,':',salary) from emp;

3.concat_ws()方法　　用于分组之前，多个字段相同分隔符情况

　　select concat_ws('|',name,age,salary,gender) from emp;

MySQL多表查询

多表查询：所需数据来源于多张表数据的组合

多表查询之联表

　　inner join　　内链接（用的最多）

　　　　select * from emp inner join dep on emp.dep_id=dep.id;

　　　　（只链接两个表中都有对应的数据）

　　left join　　左链接

　　　　select * from emp left join dep on emp.dep_id=dep.id;

　　　　（以关键字左表为基础展示左表所有的数据，没有对应的以null填充）

　　right join　　右链接

　　　　select * from emp right join dep on emp.dep_id=dep.id;

　　　　（以关键字右表为基础展示右表所有的数据，没有对应的以null填充）

　　union

　　　　select * from emp left join dep on emp.dep_id=dep.id
　　　　union
　　　　select * from emp right join dep on emp.dep_id=dep.id;

 　　　　（同时以关键字两边为基础，展示两表所有的数据，没有对应的以null填充）

多表查询之子查询

（将一张表的查询结果用括号括起来当成另一条SQL语句的条件）

子查询即日常生活中解决问题的思路：分步操作

　　　　1.先查询技术和人力资源id号

　　　　　　select id from dep where name in ('技术','人力资源');

　　　　2.再去员工表里面根据部门id号筛选出员工数据

　　　　　　select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));

可视化软件之Navicat

在工作中有时候需要更加快速的完成一些基本操作，可视化软件可以大大提升工作效率，Navicat是一款可以操作多种数据库的软件，内部其实就是封装了相应的SQL语句

Navicat软件也是需要收费的，只能免费试用14天，但是我们可以使用破解版本

破解地址：自己百度去（当心垃圾软件）

pymysql模块

下载模块的基本语句

　　方式1：cmd终端

　　　　pip3 install pymysql

　　方式2：pycharm终端

　　　　pip3 install pymysql

　　方式3：pycharm快捷方式

　　　　settings>>>

（详见pymysql模块 - 雾雨黑白 - 博客园 (cnblogs.com)）

远程仓库

　　pip3下载模块的时候默认都是从国外的仓库下载模块数据的

　　下载过程有时候会非常的慢，我们可以切换到国内的仓库

（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣 http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/

切换方法：

　　方式1：cmd命令临时切换

　　　　pip3 install 模块名 -i 仓库地址

　　方式2：pycharm更改仓库地址

　　　　settings 下载模块界面下方点击manage后添加网址

　　方法3：永久更新

　　　　需要修改python解释器内置的配置文件

pip3下载模块报错

　　1.报错信息内包含timeout关键字

　　　　原因：当前计算机网络不稳定

　　　　措施：try again and again

　　2.报错信息里面含有warning警告版本过低

　　　　原因：pip3工具版本过低需要更新

　　　　措施：直接拷贝提示的更新命令输入即可更新

　　3.报错信息里面没有任何关键字就是一堆红色字体

　　　　原因：可能是即将下载的模块对计算机环境有要求

　　　　措施：下载之前需要先准备好环境（百度去）

pymysql

基本格式

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',  # 支持简写passwd
    database='db6',  # 支持简写db
    charset='utf8',
    autocommit=True  # 自动确认
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

使用python操作数据库有权限要求，因此pymysql模块针对增删改，需要二次确认，可以在基本格式里加上自动确认

autocommit=True

SQL注入

　　pymysql登录代码使用%插入数据时，将插入数据写在后面时会引发严重漏洞

　　因此在SQL注入中涉及到关键性的数据不要自己手动拼接
交由固定的方法拼接(方法会自动过滤掉特殊符号)

固定方法：

sql = "select * from userinfo where name=%s and password=%s"
affect_rows = cursor.execute(sql, (username, password))

用户管理

1.创建用户

　　create user 用户名 identified by '密码';
　　"""修改密码"""
　　set password for 用户名 = Password('新密码');
　　set password = Password('新密码'); # 针对当前登录用户

2.重命名

　　rename user 新用户名 to 旧用户名;

3.删除用户

　　drop user 用户名;

4.查看用户访问权限

　　show grants for 用户名;

5.授予访问权限

　　grant select on db1.* to 用户名;
　　# 授予用户对db1数据库下所有表使用select权限

6.撤销权限

　　revoke select on db1.* from 用户名;

其他权限范围：

整个服务器
　　grant all/revoke all
整个数据库
　　on db.*
特定的表
　　on db.t1

事务（四大特性:ACID）

修改数据之前先开启事务操作

　　start transaction;

修改操作

　　update user set balance=900 where name='jason'; #买支付100元
　　update user set balance=1010 where name='kevin'; #中介拿走10元
　　update user set balance=1090 where name='tony'; #卖家拿到90元

回滚到上一个状态

　　rollback;

确认事务没有问题

　　commit;  # 确认之后就无法再回退

视图

　　视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可

　　create view teacher2course as
　　select * from teacher inner join course on teacher.tid = course.teacher_id;

　　别用

 

触发器

　　在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

语法结构

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end

针对触发器的名字有一个小习惯

　　tri_before_insert_t1
　　触发器简写_之前或之后_操作_表名

存储过程

相对于python中的自定义函数

delimiter $$
create procedure p1()
begin
　　select * from cmd;
end $$
delimiter ;

调用

call p1()

函数

　　相对于python中的内置方法

（ps:可以通过help 函数名    查看帮助信息!）

1.移除指定字符

Trim、LTrim、RTrim

2.大小写转换

Lower、Upper

3.获取左右起始指定个数字符

Left、Right

4.返回读音相似值(对英文效果)

Soundex

5.日期格式:date_format

（在MySQL中表示时间格式尽量采用2022-11-11形式）
CREATE TABLE blog (
　　id INT PRIMARY KEY auto_increment,
　　NAME CHAR (32),
　　sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
　　('第1篇','2015-03-01 11:31:21'),
　　('第2篇','2015-03-11 16:31:21'),
　　('第3篇','2016-07-01 10:21:31'),
　　('第4篇','2016-07-22 09:23:21'),
　　('第5篇','2016-07-23 10:11:11'),
　　('第6篇','2016-07-25 11:21:31'),
　　('第7篇','2017-03-01 15:33:21'),
　　('第8篇','2017-03-01 17:32:21'),
　　('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;

更多日期处理相关函数

　　adddate 增加一个日期
　　addtime 增加一个时间
　　datediff 计算两个日期差值
...

流程控制

if条件语句

if i = 1 THEN
　　SELECT 1;
ELSEIF i = 2 THEN
　　SELECT 2;
ELSE
　　SELECT 7;
END IF;

while循环语句

SET num = 0;
WHILE num < 10 DO
　　SELECT
　　　　num;
　　SET num = num + 1 ;
END WHILE;

索引

索引就是一种数据结构，类似于书的目录。
　　意味着以后在查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构
　　主键 primary key
　　　　除了可以加快查询之外还有其他的功能
　　唯一键 unique
　　　　除了可以加快查询之外还有其他的功能
　　索引键 index key
　　　　除了可以加快查询之外没有其他的功能
　　外键 foreign key
　　　　跟索引半毛钱关系都没有 也不存在提升查询速度一说

索引的影响
　　* 在表中有大量数据的前提下,创建索引速度会很慢
　　* 在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低