mysql
目录数据库分类
'''市面上有很多的数据库，但是，大致分类两大类'''
1. 关系型数据库
MySQL、Oracle、SqlServer、PostgreSQL、sqlite、MariaDB、db2、access
MySQL：开源免费的
Oracle：不开源，收费
MariaDB:开源的
2. 非关系型数据库
Redis缓存数据库（新项目使用）memcahce缓存数据库(老项目有再用)、MongoDB(爬虫)
'''redis淘汰了memcahce'''
3. 二者的特点
"""
关系型数据库:
拥有固定的表结构，表与表之间可以建立关系
非关系型数据：
没有固定的表结构，它是以K:V键值对的形式存储数据
name------------------->kevin
age-------------------->20
"""
sql与NOsql
# 服务端为了兼容各个客户端的交互，统一规定了交互格式：
mysql >>>>>>: sql
redis >>>>>>>: nosql
mysql
1. 版本问题
# 4.x之前的版本直接忽略，不够稳定
5.6：老项目用的多，是最稳定的版本
5.7: 新老项目都在用
8.x：最新版本
'''在IT界，生产环境中尽量不要使用最新版本,测试环境中用哪个版本都可以'''
# 任何软件，生产环境中，都要使用稳定版本，而不是追求最新版本
# 教学使用5.6版本，如果你下载了其他版本也无所谓
'''如果你之前的电脑装过mysql，而卸载的方式又不对，就有可能存在很多的残留垃圾文件，如果不删除掉，就有可能导致新的mysql装不上，装上之后，有可能启动不起来，反正就是各种问题!!!'''
# 如果你的电脑中确实存在了残留文件，怎么办呢？
利用一些杀毒软件，去检测你电脑上的残留文件，然后把检测出来的垃圾文件全部删除即可
'''今天的任务，每个人必须把MySQL装成功!!!'''
2. 下载MySQL
# 1. Windows版本
# 2. mac版本
# 3. linux版本
我们使用 Windows版本的，mac版本有教程，linux版本这里不讲
3. 下载
去挂网下载：https://www.mysql.com/ >>>>>> DOWNLOAD >>>>
# 下载下来之后，是一个zip的压缩包，去解压文件
4. mysql目录介绍：
# bin
mysql.exe # 这个是mysql自带的客户端
mysqld.exe # 这个是mysql的服务端
# data
'''它是mysql的存储数据的地方'''
my-defalut.ini # 这个是mysql的配置文件
README # 是说明文件
5. 如何使用mysql
1. 先启动服务端，要去到bin路径下找到mysqld.exe启动文件
2. 启动客户端(新建一个cmd窗口出来)，链接服务端(服务端的cmd窗口不能关闭)
3. ctrl + c是停止服务的
4. 把mysqld服务端所在的路径加入到环境变量中
5. 制作系统服务
# 目的：就是可以随时关闭mysql的服务端，达到开启自启动，自关闭
1. 先关闭已经启动的服务端
2. 在打开cmd窗口：
mysqld --install （Install/Remove of the Service Denied!） # 没有权限
'''使用管理员方式启动cmd'''
# 第一次安装成功之后，服务并没有启动，需要手动启动一次
3. 启动服务的方式:
1. 鼠标点击启动按钮
2. net start mysql # （管理员权限）
4. 停止服务
1. 鼠标点击停止
2. net stop mysql # （管理员权限）
5. 如何卸载服务
1. 先关闭服务
2. mysqld --remove
# 如何退出客户端: exit
'''
打开服务的方式：
1. 在底部任务栏右键选择任务管理器 >>>>> 服务
2. 此电脑右键 >>> 管理 >>> 服务和应用程序 >>> 服务
3. win + r >>> 输入：services.msc >>> 回车
'''
修改管理员密码及忘记密码怎么办
1. 修改管理员密码
mysqladmin -u用户名 -p旧密码 password 新密码
# mysqladmin -u root -p password 123
2. set password=PASSWORD('密码')
'''管理员密码改完之后，一定要使用管理员账号登录：'''
mysql -u root -p密码
# 直接输入mysql登录是游客模式
# 以后都用管理员模式登录，不要使用游客模式，游客模式只能查看，权限很低
'''忘记密码了怎么办？'''
1. 先停止服务端
2. 用'跳过授权表'的形式再次启动服务端
mysqld --skip-grant-tables
'跳过授权表' >>>: 客户端在登录mysql的时候，只需要输入用户名即可，不在验证密码
3. 用管理形式登录成功之后，进行修改密码
# 下面的命令先了解，你看不懂
update mysql.user set password=password('123') where Host='localhost' and User='root';
4. 退出客户端，终止服务端
5. 不要在使用跳过授权表了，正常启动服务
概率补充
库 >>> 文件夹
表 >>> 文件夹下的文件
记录 >>> 文件夹下的文件下的一行行的数据
'''
information_schema库是mysql默认在内存中创建的库
'''
基本SQL语句
'''在mysql中，sql语句都要以分号结尾!!!'''
1. 如何查看所有的数据库
show databases; # 查看所有数据库
2. 如何选择数据库
use 库名;
user mysql;
3. 查看当前在哪个库下
select database();
针对库的sql语句
1. 查看库
show databases; # 查看所有库
show create dabatabse db1; # 查看具体库的信息
2. 增加库
create database 库名;
3. 修改库 # 一般很少用，甚至于不用
# alter database 库名 charset='gbk'
alter database db1 charset='gbk'
4. 删除库
drop database 库名;
drop database db1;
针对表的sql语句
1. 查看表
show tables; # 查看库下的所有表
desc t1; # 查看表结构
show create table t1;
2. 增加表
create table t1(id int, name varchar(10));
3. 修改
# 修改表明
alter table t1 rename tt1; # 重命名表名
4. 删除
drop table tt1;
针对记录的SQL语句
'''肯定是要先有库和表'''
# 记录：表里面的一行行数据
库 >>>> 表 >>>>> 记录
1. 增加记录
# 是全字段增加
insert into t1 values(1, 'kevin', 20); # 单条数据
insert into t1 values(3, 'kevin1', 20),(4, 'kevin2', 20),(5, 'kevin3', 20),(6, 'kevin4', 20);
# 部分字段添加数据，需要在表名后面指定字段名
insert into t1(id, name) values(1, 'kevin'); # 单条数据
2. 查看
select * from t1;
3. 改
update t1 set name='tank' where id=2;
update t1 set name='tank',age=30,key=value,key1=value1 where id=2;
update t1 set name='tank' where id=1;
update t1 set name='tony' where age=20;
update t1 set name='tony' where age=30 and name='tank';
############以后写更新语句的时候，千万检查要有where条件###############################
### 用户表 ### 3000万
update t1 set money=money+'1' ; # 影响的是全表
4. 删除
delete from t1 where id =1;
delete from t1 ;
配置文件
# 1. 复制my-default.ini文件
# 2. 命名为my.ini
# 3. 修改完配置文件只会，一定别忘重启服务端
存储引擎(面试用)
# 理论部分
'''存储引擎其实就是数据库存储数据的方式!!!'''
'''你们这个阶段，最好自己写一写，后面熟悉了在复制'''
# MySQL中有哪些存储引擎
show engines;
# MySQL一定支持9中存储引擎，我们只需要掌握两种即可
MyISAM
'''
MySQL5.5及之前的版本默认的存储引擎，它相对InnoDB的存取速度更快了，但是，相对InnoDB数据不够安全.
它不支持事务，行锁，外键;支持表锁
'''
InnoDB
'''
MySQL5.6及之后的版本默认的存储引擎，它相对MyISAM的存取速度更慢了，但是，相对MyISAM数据更安全.
它支持事务，行锁，外键;
'''
MEMORY
# 内存
'''数据存放在内存中，一旦断电，数据立马丢失，重启服务端数据就没了，不能长期保存数据'''
create database db4;
create table t1 (id int) engine=MyISAM;
create table t2 (id int) engine=InnoDB;
create table t3 (id int) engine=MEMORY;
'''
不同的存储引擎的区别：
1. MyISAM引擎产生3个文件
.frm >>> 表结构
.MYD >>> 存数据
.MYI >>> 存索引 >>> 目录
2. InnoDB 产生2个文件
.frm >>> 表结构
.ibd >>> 表结构+数据
3. MEMORY产生1个文件
.frm >>> 表结构
'''
MySQL的基本数据类型
1. 整型
tinyint smallint int bigint
# 存储数据的大小范围不一样
范围的比较：tinyint < smallint < int < bigint
tinyint: 1个字节 ---> 11111111 ----> 0-255 -> -128~127
smallint:2个字节 ---->16位 ------> 0-32 768
int:4个字节
bigint：8个字节
'''默认情况下，存储数据带不带符号'''
create table t4 (id tinyint);
create table t4 (id smallint);
create table t4 (id int);
create table t4 (id bigint);
# 默认情况存储是带符号的，其他整型也是如此
2. 浮点型
# 小数
float double decimal
'''总共255位，小数占30位'''
float(255, 30)
double(255, 30)
decimal(65, 30) '''总共65位，小数占30位'''
create table t5 (id float(255, 30));
create table t6 (id double(255, 30));
create table t7 (id decimal(65, 30));
insert into t5 values(1.1111111111111111111111);
insert into t6 values(1.1111111111111111111111);
insert into t7 values(1.1111111111111111111111);
# 得出结论：精确到不一样
decimal >>> double >>>> float
'''
根据多年工作经验来看，绝大部分来说，都选decimal
'''
3. 字符串
char(4) varchar(4)
char(4)：定长，超出4位，报错，不够4位，空格填充
varchar(4): 可变长，不够4位，有几位存几位，超出4位，有几位存几位
create table t8 (id int, name char(4));
create table t9 (id int, name varchar(4));
insert into t8 values(1, 'kevin');
insert into t9 values(1, 'kevin');
# mysql5.6 之前不会直接报错，需要设置一个参数，才会报错
show variables like '%mode%'; # 模糊查询
2中修改方式：
1. 配置文件修改---->一定要重启服务端
2. 临时修改
set global sql_mode='STRICT_TRANS_TABLES';# 退出
客户端，从新进
3. set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
#######研究定长和可变长
insert into t8 values(1, 'k');
insert into t9 values(1, 'e');
# 验证方法:char_length
select char_length(name) from t8;
select char_length(name) from t9;
4. 日期类型
date datetime time year
create table t13 (id int,
reg_time date,
login_time datetime,
logout_time time,
birth_day year
);
insert into t13 values(1, '2023-04-04','2023-04-04 11:11:11', '11:11:11', 1995);
# 用的最大的就是datetime
5. 枚举与集合
枚举：多选一 enum()
create table t14 (
id int, gender enum('male','female','other')
);
集合：多选多 set()
create table t15 (
id int,
hobby set('read','music','tangtou','xijio','anmo')
);
整型中括号内的数字作用
字符串中括号中得数字代表的就是：限制存储的长度
create table t11 (id int(3));
insert into t11 values(9999999);
"""整型中括号内的数字代表的不是长度，代表的是展示的位数"""
create table t11 (id int(9) zerofill);
insert into t12 values(9);
'''面试用'''
创建表的完整语法
CREATE TABLE `t15` (
`id` int(11) DEFAULT NULL,
`hobby` set('read','music','tangtou','xijio','anmo')
)
CREATE TABLE `t15` (
字段1 字段类型1 约束条件1 约束条件1 约束条件1,
字段2 字段类型2 约束条件1 约束条件1 约束条件1,
字段3 字段类型3 约束条件1 约束条件1 约束条件1,
字段4 字段类型4 约束条件1 约束条件1 约束条件1
);
"""
1. 字段和字段类型是必须要写的
2. 约束条件是可选的，并且，约束条件可以有多个，空格隔开
3. 最后一条数据的逗号不能加
"""
约束条件
# 就是在数据类型的基础上在添加额外的限制
id int unsigned
1. unsigend
create table t1(id int unsigned);
2. zerofill
3. not null
# 非空
create table t2(
id int,
name varchar(32)
);
create table t3(
id int,
name varchar(32) not null
);
4. default默认值
create table t4(
id int,
name varchar(32) default 'ly',
age int
);
5. 唯一
# 单列唯一
create table t6 (
id int,
name varchar(32) unique
);
# 联合唯一
create table t7 (
id int,
host varchar(32) ,
port varchar(32) ,
unique(host,port)
);
6. 主键
# 单纯从约束条件来看，它相当于是not null unique------>非空且唯一
'''主键可以加快查询速度!!!因为主键本质上也是一种索引!!!'''
InnoDB存储引擎规定一张表至少要有一个主键，你会发现，我们之前创建表的时候，并没有主键，但是，也创建成功了，为什么呢？
'''
InnoDB存储引擎内部有一个隐藏的字段，作为了主键，我们看不到，并且，这个隐藏的主键不能加快查询速度，就是因为InnoDB存储引擎建表就是有主键来构成的，加这个隐藏的字段作为主键，仅仅是为了把表创建成果.
'''
# 所以，以后我们在创建表的时候，都要创建一个主键字段，一般我们都给表中的id字段添加主键
id---->(uid, cid, sid, ....id)
# 如何给字段添加主键
id int primary key
create table t8 (
id int primary key, # 这个id字段就已经有了主键的特性
name varchar(32)
);
7. auto_increment
# 自增----->在原来的基础上，每次加1
create table t9 (
id int primary key auto_increment,
name varchar(32)
);
# 得出结论
'''以后我们在创建id字段的时候，固定语法结构：'''
id int primary key auto_increment
# 主键字段在添加值的时候，就不用在单独添加了，而是自动生成.
'''一张表中，我们可以通过主键字段来唯一确定一条记录'''
补充一些其他SQL语句
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
查询关键字
select--->跟字段名---->*----->所有字段
from----->指定表名
where------>查询条件
查询关键字之where筛选
# where------>筛选条件的
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%';
"""
where筛选功能
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%se';
"""
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL; # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
'''在sql中，NULL和''不一样''
查询关键字之group by分组
分组
"""
按照某个指定的条件将单个单个的个体分成一个个整体
eg: 按照男女将人分组
按照肤色分组
按照年龄分组
"""
# 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取
针对5.6需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
# 数据分组应用场景：每个部门的平均薪资，男女比例等
# 1.按部门分组
select * from emp group by post; # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post; # 验证
"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
统计的时候只要是非空字段 效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment
补充说明
# group_concat 分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post;
select post,group_concat(name,'|',sex, '|', gender) from emp group by post;
select post,group_concat(distinct name) from emp group by post;
select post,group_concat(distinct name separator '%') from emp group by post;
# concat 不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;
关键字之having过滤
"""
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
关键字where group by 同时出现的情况下，group by必须在where之后
where 先对整张表进行一次筛选，group by再对筛选过后的表进行分组
我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表
"""
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
关键字之distinct去重
# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct id,age from emp;
select distinct post from emp;
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;
关键字之limit分页
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
关键字之regexp正则
select * from emp where name regexp '^j.*(n|y)$';
清空表补充
1. delete from t; # 不能把主键重置
2. truncate t; # 可以把主键重置为1开始
'''所以，以后如果有清空表的需求，我们选择truncate，因为truncate清空表之后，还可以恢复数据'''
3. DBA可以恢复数据------>binlog日志----->记录你操作sql语句的过程
4. DBA:数据库管理员
表与表之间建关系
表关系总共就四种
一对多
多对多
一对一
没有关系
"""
判断表关系的方式:换位思考
"""
1.一对多
以员工和部门表为例
先站在员工表的基础之上
问:一个员工信息能否对应多个部门信息
答:不可以
再站在部门表的基础之上
问:一个部门信息能否对应多个员工信息
答:可以
结论:一个可以一个不可以 那么表关系就是"一对多"
员工表是多 部门表是一
"""
针对一对多的表关系 外键字段建在多的一方
"""
# 表关系没有'多对一'一说 都是'一对多'
# 在创建外键关系的时候，先创建出来基础字段，
"""
使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表
之后再添加外键字段
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
"""
外键约束
1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
如果想要数据之间自动修改和删除需要添加额外的配置
create table emp1(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
"""
由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
所以在实际开发项目中 有时候并不会使用外键创建表关系
而是通过SQL语句层面 建立逻辑意义上的表关系
eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
"""
多表查询
多表查询的思路：
1. 子查询
# 一条SQL的执行结果当成另外一条SQL语句的执行条件.
# 大白话：分布操作
2. 连表查询
# 把多张表拼接成一张虚拟表(不是真实存在的表，临时在内存中保存)，然后按照单表查询思路查询
# 如何拼接表？
'''一下四个关键字都是用来连表的'''
'''这里还可以给表明起别名... as '''
join left join right join
python操作MySQL(掌握)
1.下载模块
pip3 install pymysql
"""
python默认下载模块的地址是国外的网站
速度有时候会非常的慢 如果想要提升速度我们可以切换下载源
"""
（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.命令行切换源
pip3 install pymysql -i 源地址
# 2.pycharm永久切换
file
setting
interpreter
双击
manage 仓库
2.基本使用
import pymysql
# 创建链接对象
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1',
database='db4_3',
charset='utf8'
)
# 生成游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 括号内不写参数是元祖组织不够明确
# 定义SQL语句
# sql = 'show tables'
sql = 'select * from teacher'
# 执行sql语句
affect_rows = cursor.execute(sql)
print(affect_rows) # 执行SQL语句所影响的行数
# 获取结果
res = cursor.fetchall()
print(res)
SQL注入
利用特殊符号的组合绕过相应的机制
如何解决
不要自己手动处理敏感数据
"""
SQL注入的原因 是由于特殊符号的组合会产生特殊的效果
实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用
原因也是一样的
结论:设计到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
"""
jason' -- fdsfdsfsdf
xxx' or 1=1 -- fffdf
sql = "select * from userinfo where name=%s and password=%s"
# 执行sql语句
cursor.execute(sql,(username,password)) # 交由execute自动拼接 自动筛选
其他操作
针对增删改查
查重要程度很低 无序二次确认
增改删重要程度很高 都需要二次确认
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1',
database='db5',
charset='utf8',
autocommit=True # 自动二次确认
)
"""
在使用代码进行数据操作的时候 不同操作的级别是不一样的
针对查无所谓
针对增 改 删都需要二次确认
conn.commit()
"""
视图
什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
为什么要用视图
如果要频繁使用一张虚拟表，可以不用重复查询
如何用视图
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1;
"""
**强调**
1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于查询，尽量不要修改视图中的数据
drop view teacher2course;
思考:开发过程中会不会去使用视图
不会！视图是mysql的功能，
如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，
然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，
所以通常不会使用视图，而是通过重新修改sql语句来扩展功能
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，
这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法
"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
sql代码。。。
end
# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
sql代码。。。
end
# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
sql代码。。。
end
"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
delimiter $$
delimiter ;
该语法只在当前窗口有效
"""
事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
'''
#### 事务的作用
保证了对数据操作的数据安全性
案例:用交行的卡操作建行ATM机给工商的账户转钱
**事务应该具有4个属性：**原子性、一致性、隔离性、持久性。这四个属性通常称为**ACID特性**。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
'''
如何用
# 先介绍事务的三个关键字 再去用表实际展示效果
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
# 那如何检测异常？
存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数
'''
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去
)
begin
select * from user;
end $$
delimiter ;
# 调用
call p1()
'''
三种开发模型
"""
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""
"""
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""
"""
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
"""
创建存储过程
# 介绍形参特点 再写具体功能
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
如何用存储过程
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 1、直接在mysql中调用
set @res=10 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10); # 报错
call p1(2,4,@res);
# 查看结果
select @res; # 执行成功，@res变量值发生了变化
# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10)) # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')
# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
update user set balance=900 where id =1;
update user123 set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
函数
参考博客：<http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2>
流程控制
# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
索引
*索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。*
* primary key
* unique key
* index key
注意：上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，
而index key没有任何约束功能只会帮你加速查询