Python学习笔记 第十二章 mysql

Python学习笔记 第十二章 mysql

1.初识数据库

一般为了高可用，还会备份大量的数据到备用数据库，当运行的数据库死掉之后可以接替原来的数据库

• 数据库的优势

  • 程序稳定性，这样任意一台服务器崩溃了，都不会影响数据和另外的服务
  • 数据一致性：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象
  • 并发：数据库可以良好的支持并发，所有程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不用自己写sicket
  • 效率：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

• 什么是数据

  • 描述事物的符号记录称之为数据，可以是数字、文字、图片、图像、声音、语言等
  • 在计算机中描述一个事物，需要抽取这事物的典型特征，组成一条记录，相当于文件的每一行

• 什么是数据库

  • 数据库即存放数据的仓库，只不过这个仓库是计算机存储设备上，而且数据是按一定的格式存放的
  • 数据库是长期存放在计算机内、有组织、可共享的数据集合
  • 数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和以扩展性，并可为各种用户共享

• 什么是数据库管理系统（DataBase Management System）简称DBMS

  • ​ 如何科学地组织和存储数据，如何高效获取和维护数据成了关键
  • 这用到一个系统软件-数据库管理系统：MySQL\Oracle\SQLite]Access\MS SQL Server
  • mysql主要用于大型门户，如搜狗、新浪，开源（甲骨文）
  • oracle用于银行、铁路、飞机场，费用高（甲骨文）
  • sql server，微软公司的产品，主要应用于大中型企业，如联想、方正
  • 数据库管理员（DBA）

• 概念

  • 表：文件 ，存放多行内容、记录
  • 数据库：文件夹，用来组织文件、表
  • 数据库管理系统：软件，如mysql，管理数据库
  • 数据库服务器：一台计算机（对内存要求较高），运行数据库管理软件

• 数据库的分类

  • 关系型：sqllite\db2\oracle\access\sql server\mysql
    • 相对慢
    • 需要表结构
    • sql语句通用
  • 非关系型：mongodb\redis\memcache
    • 相对快
    • key-value来存储的，没有表结构
    • {'apple':[名字 年龄 电话号码 地址]} 只能根据apple查询，而不能通过电话号码查询
    • 一般用于如：快递 用快递单号查询
    • 视频 电影的id：电影的内容/电影的地址

• mysql

  • 是一个关系型数据库，关系数据库将数据存放在不同的表中，而不是将所有的数据存放在一个大仓库内，增加速度和灵活性

2.安装数据库

https://downloads.mysql.com/archives/community/

下载之后点击安装包进行安装

环境变量的配置：

在任何目录下都能找到对应的文件

才能在任意位置输入命令启动该应用

windows下

mysql install 安装mysql服务 mysql服务就被注册到操作系统中

net start mysql 启动mysql

net stop mysql 关闭mysql

启动客户端连接server

mysql -u root -p

输入密码

或者 mysql -uroot -p123456

mysql -uroot -p123456

3.操作数据

mysql账号操作

#进入mysql客户端
$mysql
mysql> select user();  #查看当前用户
mysql> exit     # 也可以用\q quit退出

# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
$ mysql -uroot -p   # mysql5.6默认是没有密码的
#遇到password直接按回车键
mysql> set password = password('root'); # 给当前数据库设置密码

# 创建账号
# create user '用户名'@'主机ip/主机域名' identified by '密码';
mysql> create user 'wrr'@'192.168.10.%'   IDENTIFIED BY '123';# 指示网段
mysql> create user 'wrr'@'192.168.10.5'   # 指示某机器可以连接
mysql> create user 'wrr'@'%'                    #指示所有机器都可以连接  
mysql> show grants for 'wrr'@'192.168.10.5';查看某个用户的权限 
# 远程登陆
$ mysql -uroot -p123 -h 192.168.10.3

# 给账号授权
#grant select on 数据库名.* to '用户名'@'主机的ip/主机域名'
mysql> grant all on *.* to 'wrr'@'%';
#grant 权限类型
#grant all 给所有的权限
#grant select,insert 给查询和插入的权限
mysql> flush privileges;    # 刷新使授权立即生效

# 创建账号并授权
mysql> grant all on *.* to 'wrr'@'%' identified by '123' 

• 基本操作
  • show databases; 查看所有数据库
  • create database 数据库名; 创建一个数据库
  • use 数据库; 切换到数据库下
  • show tables; 查看这个库下有多少表
  • drop database 数据库名;删除数据库
• 操作表
  • create table 表名(name char(20), age int); 创建一张表
    • 字段名 类型[（宽度） 约束条件]
  • show create table 表名;
    • 能查字段、类型、长度、编码、引擎、约束
  • desc student; describe 表名; 查看表结构
    • 能看到多少个字段、类型，但是看不到编码，引擎，具体的约束信息只能看到一部分
  • drop table 表名; 删除表
• 操作数据
  • 插入数据：insert into student values('apple', 18);
    • 写入数据的方式
    • insert into 表 values (值1，值2， 值3) 这张表有多少的字段就需要按照字段的顺序写入多少个值
    • insert into 表 values (值1，值2，值3)，（值1，值2，值3）一次性写入多条数据
    • insert into 表 (字段1，字段2) values (值1，值2) 制定字段名写入，可以任意选择表中你需要写入的字段进行写入
  • 查询数据： select * from student;
  • 修改数据：update student set age=20 where name='apple'; 注意修改的需要加上where
  • 删除数据：delete from student where name='apple';
    • delete from 表; 是不会清空自增字段的offset(偏移量)值
    • truncate table 表; 会清空表和自增字段的偏移量

4.SQL语句

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

　　1、DDL语句 数据库定义语言（defined）： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

　　2、DML语句 数据库操纵语言（manager）： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

　　3、DCL语句 数据库控制语言（control）： 例如控制用户的访问权限GRANT、REVOKE

5.存储引擎- 存储数据的方式

show engines; 查看支持的存储引擎

show variables like '%engin%'; 查看默认的引擎

Support:mysql是否支持这种存储引擎

mysql5.6 默认为Innodb存储引擎

Myisam 在mysql5.5之前默认的存储引擎 表锁

操作数据的代码-》 存储引擎 - 〉数据

（1）数据存储在硬盘上

数据和索引存储在一起 2个文件 Innodb存储引擎 数据持久化

• 数据索引一个文件

• 表结构一个文件

• Transactions：事务，保持数据安全

  Supports transactions：支持事务，保证数据的完整性，将多个操作变成原子性操作

  开启事务-》 执行原子性操作-〉提交事务

  row-level locking：行级锁：修改的行少的时候使用，修改数据频繁的时候

  表级锁：批量修改多行，对于大量数据的同时修改

  foreign keys：外键，约束两张表中的关联字段不能随意的添加、删除，降低数据增删改的出错率

  学生表中可以添加学生的姓名和年龄，再设置一个id 1、2

  在班级表里设置id 1、2分别表示一班和二班，显示对应的班级人数

  但是想要在学生表中id可以设置为3，如果想要学生表的id根据班级表存在的id进行设置，那么可给id加上外键，当id设置为3会报错

  并且当学生表中有设置id为2的，那么班级表中的二班不能删除

数据和索引不需要存储在一起 3个文件 Myisam存储引擎 数据持久化

• 数据
• 表的结构
• 索引（查询的时候使用的一个目录结构）
• 只支持表锁

（2）数据存在内存中，也就是说断电数据消失 1个文件 Memory 存储引擎

• 表结构

• MEMORY:Hash based基于哈希的，存储在内存中，useful for temporary tables对临时表非常有用

一般像首页的热点新闻就会存储在memory中，查找不到再到大表中查找

面试题：

你了解mysql存储引擎吗？

你的项目用了什么存储引擎？

• innodb
• 多个用户操作的过程中对同一张表的数据同时做修改
• innodb支持行级锁，所以我们是用来这个存储引擎
• 为了适应程序未来的扩展性，扩展新功能的时候可能会用到...涉及到要维护数据的完整性
• 项目中有一两张xx表，之间的外键关系是什么，一张表的修改或者删除比较频繁，怕出错所以做了外键约束

6.mysql数据类型

• 数字 和 bool

  • 默认是有符号的
  • 无符号 unsigned 0, + 如果填入负数会显示warning 并查看的时候显示为可表示范围的最小值如0
  • tinyint 一个字节 有符号数范围 -128，127
  • int 四个字节 有符号数范围 -2147483648，2147483647 超过最大的位数显示的还是2147483647 不约束长度
    • 最多表示10位数
  • float 四个字节 float(5,2) 123.32 前面表示一共多少位，后面表示小数点位数 会四舍五入
  • double 八个字节 同float 表示的范围更广
  • decimal 默认为decimal(10, 0) 十位整数 依赖于前面和后面的值

• 字符串

  • char 0-255字节 常用于身份证号、手机号、qq号（12位的qq更多）、username（12-18）、password（10-16）

    • 定长存储，浪费空间，节省时间
    • 'apple' 'apple .......'

  • varchar 0-65535字节 常用于评论、朋友圈、微博

    • 变长存储，节省空间，存取速度慢
    • 'apple' 'apple5'

    

• 时间

  • datetime 八个字节20210415121900 表示范围1000-01-01 00:00:00/9999-12-31 23:59:59

  • year 一个字节 1901/2155

  • date 三个字节 20210415 表示范围1000-01-01/9999-12-31

  • time 三个字节 121900 表示范围 '-838:59:59'/'838:59:59'

  • timestamp 四个字节 1970-01-01 00:00:00/2038 默认值为当前时间戳 不能为空 更新其他数据的时候自动按照当前的时间戳更新最新的时间戳，像登陆的时候，可以记录最后一次登陆的时间

    • 如果像datetime也实现类似的功能，可以利用show create table 表名 查看timestamp的字段后面的内容
    • 在新建datetime数据类型的时候也加上同样的内容即可
    • create table t5(id int, dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

    

• enum/set

  按照一定的范围选择

  • enum 单选行为
    • gender enum('male', 'female'); 性别可以设置为单选
  • set 多选行为
    • hobby set('抽烟', '喝酒', '烫头'); 爱好可以多选

7.完整性约束

无符号的：只和数字有关 unsigned

不能为空 not null

默认值 default

不能重复 unique 值不能重复，null可以写入多个 第一个被定义为非空+唯一的那一列会成为这张表的primary key

联合唯一 unique

create table t14(
	id int,
  server_name char(12),
  ip char(15),
  port char(5),
  unique(ip, port)
);

自增 auto_increment 只能对数字有效，自带非空约束，至少是unique的约束之后才能使用auto_increment

create table t16(
	id int primary key auto_increment,
	name char(12)
)

主键：非空+唯一约束 = 主键 primary key 一张表只能定义一个主键 如果不指定主键 默认是第一个非空+唯一

联合主键 primary key( )

create table t17(
	id int,
  server_name char(12),
  ip char(15),
  port char(5),
  primary key(ip, port)
);

外键： foreign key (自己的字段) references 外表（外表字段）

外表字段必须至少是唯一的

学生表

create table student(
	id int primary key auto_increment,
  name char(12) not null,
  gender enum('male', 'female') default 'male',
  class_id int,
  foreign key(class_id) references class_t(cid)
);

班级表

create table class_t(
	cid int primary key auto_increment,
  cname char(12) not null,
  startd date
);

需要先建立班级表，并且写入数据，让学生表可以关联到

且学生表内有关联到班级表的，需要修改班级或者删除，才能对班级表进行修改或者删除

需要级联更新、级联删除，可以在foreign key(class_id) references class_t(cid) on update cascade on delete cascade

删除班级表中一个班，那学生表中关联这一个班的数据也会删除

注意空字符串' '和空null在数据库中不一样

8.修改表

语法：
1. 修改表名
      ALTER TABLE 表名 
                      RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 
                      DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名
                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

9.表与表之间的关系

多对一：

• 学生-班级

• 多个学生是一个班级的

• 学生表有一个外键关联班级

• 书籍-作者

• 商品-订单

多对多：

• 学生-班级 多对一 多个学生对应一个班级
• 班级-学生 多对一 多个班级对应一个学生
• 多创建一张表，关联两个外键，表示学生id和班级id

一对一：

• 客户-学生
• 客户id unique
• 学生id foreign key unique

10.查询

select * from employee;

select id,name from employee; 查询几个字段

select id,name as n from employee; 查询出来的时候重命名（加as）

select id,name n from employee; 查询出来的时候重命名（空格）

select distinct post from employee; 查询结果去除重复项

select distinct age,sex from employee; 查询的结果只有当年龄和性别都重复才会是重复项

select emo_name,salary*12 as annual_salary from employee; 四则运算（计算年薪）

select concat(emp_name,':',salary) from employee; 拼接concat，有时候姓和名的拼接

select concat_ws('|','apple','male'); 默认以第一个字符为拼接符进行拼接后面的内容

结合case语句 case ... end

selcet
	(
		case
    when emp_name = 'apple' then
    	emp_name
    when emp_name = 'banana' then
    	concat(emp_name, 'BIGSB')
    else
    	concat(emp_name, 'SB')
  	end
  ) as new_name
from employee;

where 约束

筛选符合条件的行

• 比较运算符： > < >= <= !=
• between 80 and 100 值在80到100之间
• in(80, 90, 100) 值是80或者90或者100
• like 'e%' 模糊匹配
  • 通配符可以是%或者'_'，%表示任意多个字符，'_'表示一个字符
• 逻辑运算符： and or not
• 正则
  • regexp '^j' 以j开头的
  • regex 'g$' 以g结尾

分组

group by ：

根据谁分组，可以求这个组的总人数，最大值，最小值，平均值，求和，但是求出来的值只和分组字段对应

并不和其他任何字段对应，这个时候查出来的所有字段都不生效

聚合

count（根据分组计数） max min sum avg

select sex,count(sex) from employee group by sex; 显示男女分别有多少人

select age,count(age) from employee group by age; 显示不同的年龄有多少人

更好的查看

select post,group_concat(emp_name) from employee group by post; 会自动拼接，但是拿不到每一个的值

选择各个部门最低工资的人

select post,emp_name,min(salary) from employee group by post; 这是错误的，因为分组显示的姓名不是真正工资最低的，是分组出来

找不到对应的人

having 过滤语句

在having条件中可以使用聚合函数

适合去筛选符合条件的某一组数据，而不是某一行数据

先分组，再过滤：求人数大于xx的性别，求年龄大于多少人的年龄

获取每个部门的平均工资筛选出平均值大于10000的部门

select post,avg(salary) from employee group by post having avg(salary) > 10000;

order by 查询排序

默认升序排 asc

降序 desc

select * from employee order by selery; 根据薪资从小到大排

select * from employee order by age desc; 根据年龄从大到小排

select * from employee order by age,salary desc; 先根据年龄从小到大排，年龄相同，再根据薪资从大到小排

limit m,n

limit n 取前几条数据 不写m默认为0

select * from employee order by selery desc limit 3; 只取薪资最高的前三个人的数据

limit m,n 从第m+1项开始取n项

limit n offset m 与上面相同，从m+1项开始取n项

11.多表查询

连表查询

一张department表，一张employee员工表

select * from department, employee; 会产生一个大表，表是笛卡尔积的形式产生的

select * from department, employee where department.id = dep_id; id有多个需要指定是department还是employee表里的

所谓连表：

• 总是在连接的时候创建一张大表，里面存放的是两张表的笛卡尔积
• 再根据条件进行筛选就可以了
• where 条件 group by 分组 order by limit 都可以正常使用

表与表之间的连接方式：

• select * from 表1，表2 where 条件
• 内连接 inner join ... on ...
  • 必须左表和右表中条件相互匹配的项才会显示出来
  • select * from 表1inner join 表2 on 条件
  • select * from department inner join employee on department.id = employee.dep_id;
  • select * from department as d1 inner join employee as e1 on d1.id = e1.dep_id; 注意是先做连表，所以重命名可以用
• 外连接
  • 左外连接 left join
    • 左表显示全部，右表中的数据必须和左表条件相互匹配的项才会显示出来
    • select * from 表1 left join 表2 on 条件
  • 右外连接 right join
    • 右表显示全部，左表中的数据必须和右表条件相互匹配的项才会显示出来
  • 全外连接 full join
    • mysql不支持 通过
    • select * from 表1 left join 表2 on 条件
    • union
    • select * from 表1 right join 表2 on 条件

12.子查询

• 带in的子查询 select * from 表 where 字段 in (select 字段 from 表 where 条件)
• 带比较子查询 select * from 表 where 字段 > (select 字段 from 表 where 条件)

查询平均年龄在25岁以上的部门

select dep_id from employee group by dep_id having avg(age)>25;

select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

查看技术部员工姓名

select name from employee where dep_id in (select id from department where name='技术');

查询大于所有人平均年龄的员工名与年龄

select name, age from employee where age>(select avg(age) from employee);

查询大于部门内平均年龄的员工名与年龄

select name,age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id=t2.dep_id where age>avg_age;

exists关键字表示存在，使用这个关键字之后内层查询语句不返回查询的记录，返回一个真假值，返回True，外层查询语句将进行查询，当返回值为False，外层查询语句不进行查询

select * from employee where exists (select id from department where id=200);

13.索引

工作中必备：

创建

删除

知道用了它会加快查询速度

面试：

• 数据准备
• 读取一次硬盘时间开销
  • 寻道时间 0.05ms
  • 3600转/7200转 每分钟 一圈 60/7200 = 0.09ms
  • 找到一圈上的数据平均时间为0.045ms
  • 从硬盘上读取一次数据的时间大概是0.1ms
  • cpu每秒可执行5亿条指令，0.1ms可执行5万条cpu指令
  • 磁盘的预读性原理：每一次读区硬盘的单位不是你要多少就读多少，每一次读数据块的大小都是固定的，linux 4096字节 -block
• 新的数据结构-树
  • root 根节点
  • branch 分支节点
  • leaf 叶子结点
• mysql中存储数据的两种方式
  • 平衡树 balance tree 简称b树：能够让查找某一个值的查找速度尽量平衡，平衡树不一定是二叉树
    • 如果不是平衡树，简单的树最差会退化为线性的
  • b树改进之后变成B+树
    • 在分支节点不会存储数据（让树的高度尽量矮，让查询一个数据的效率稳定），都是叶子结点存储数据
    • 叶子结点之间是双向链表，查找范围非常快
• 聚集索引（聚簇索引）和非聚集索引（非聚簇索引、辅助索引）都是用b+树进行存储的
  • 聚集索引的好处是一次就可以找到对应的值，不好的点就是存储的数据少，只有Innodb必有聚集索引，且仅有一个主键
    • innodb主键会默认创建一个聚集索引
    • 全表数据都存储在叶子结点上
  • 非聚集索引存储的是对应的地址，还要查表才能找到数据，优点是可以存更多数据，缺点是需要再查表，innodb myisam
    • 叶子结点不存放具体的整行数据，而是存放的这一行的主键的值
• 索引的创建与删除
  • 创建主键 primary key 聚集索引+非空+唯一
  • 创建唯一约束 unique 辅助索引+唯一
  • 添加一个普通索引
    • create index 索引名 on 表（字段）
    • drop index 索引名 on 表
• 正确使用索引
  • 对哪个字段创建了索引，就用哪个字段来查，查询的字段不是索引字段，查询的速度也不会加快
  • 在创建索引的时候，应该对区分度比较大的列进行创建索引
    • 1/10以下的重复率比较合适创建索引
  • 范围
    • 范围越大越慢
    • 范围越小越快
    • 如果like 'a%' 快
    • 如果like '%a' 慢
  • 条件列参与运算，或者使用函数会降低索引查询速度
  • and 和 or
    • 条件1and条件2 两个同时成立才会成立，但凡一个条件不成立则最终结果不成立
      • 条件1创建了索引，如果条件1不满足，那肯定不成立，不用再找条件2
    • 条件1or条件2 只要一个条件成立就成立
      • 条件1创建了索引，如果条件1不满足，那不一定不成立，还要找条件2，如果条件2每索引，那么查询的效率还是低
    • 多个条件的组合，如果使用and连接，其中一列使用索引，都可以加快查询速度，使用or连接，必须所有的列含有索引，才能加快查询速度
  • 联合索引:最左前缀原则
    • create index ind_mix on s1(id, name, email);
    • 必须带着最左边的列做条件，从出现范围开始整条索引失效

函数都是处理简单的逻辑

存储过程处理的逻辑比较复杂

复杂的逻辑都是用python代码来处理

简单的数据增删改查用sql解决

覆盖索引：

查询过程中不需要回表

索引合并：

分别创建的两个索引在某一次查询中临时合并成一条索引 a=1 or b=2

执行计划：

explain select 语句 能够查看sql语句有没有按照预期执行，可以查看索引的使用情况，type等级

慢查询优化：

• 首先从mysql的角度优化
  • 把每一句话单独执行，找哦到效率低的表，优化这句mysql
  • 了解业务场景，适当创建索引，帮助查询
  • 尽量用连表代替子查询
  • 确认命中索引的情况
• 考虑修改表结构
  • 拆表
  • 把固定的字段往前调整
• 使用执行计划，观察sql的type通过以上调整是否提高

mysql慢日志：

• 在mysql的配置中开启并设置一下
• 在超过设定时间后，这条sql总是会被记录下来，这个时候我们可以对这些被记录的sql进行定期优化

14.pymysql模块

一般都是通过python操作数据

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='123', database='homework')

cur = conn.cursor() #cursor游标 默认以元组返回
#cur = conn.cursor(cursor=pymysql.cursors.DictCursoor) #取出来的数据是以字典返回 更浪费空间
cur.execute('select * from student;')

ret = cur.fetchone()
print(ret)  # 拿到一条数据

ret2 = cur.fetchmany(10) # 拿到多条数据
print(ret2)

ret3 = cur.fetchall() # 拿到所有剩余的数据
print(ret3)
print(cur.rowcount) # 表示拿到多少条数据

cur.close()
conn.close()

#增 删 改
import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='123', database='homework')

cur = conn.cursor()

try:
	cur.execute('insert into student values(17, "男", 3, "apple")')  #执行了只相当于在内存中插入
	cur.execute('update student set gender ="male" where id=17') # 修改
  cur.execute('delete from student where id=17')
  conn.commit() #提交
except Exception as e:
  print(e)
  conn.rollback() # 回滚

cur.close()
conn.close()

实际在操作中会遇到的问题

结合数据库和python写一个登陆

import pymysql

usr = input('username :')
pwd = input('password :')
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='123',
                       database='test'
												)
conn.cursor()


cur.execute('select * from userinfo where user="%s" and password="%s"'%(usr, pwd))

# select * from userinfo where user = "apple" or 1=1;  --"...    后面的东西注释了
#sql注入

#最好使用execute自动拼接
cur.execute('select * from userinfo where user=%s and password=%s', (usr, pwd)) #不用拼接，也不用加""

cur.close()
conn.close()

15.数据备份和事务

事务

• 锁

  begin #开启事务
  select age from userinfo where id=1 for update; # 查询id值，for update 加上行级锁
  update emp set salary=100000 where id=1; # 完成更新
  commit #提交事务
  

备份

表和数据的备份

mydqldump.exe

在cmd命令直接执行

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件名.sql

回复数据 在mysql中执行命令

source D:\python\备份文件名.sql

16.小结

数据的

增

• insert into 表 values(值)
• insert into 表(字段1，字段2) values(值1，值2)
• insert into 表(字段1，字段2) select 字段1，字段2 from 表 2

删

• delete from 表 where 条件
• truncate table 表名

改

• update 表 set 字段=值 where 条件

查

• select 字段 from 表

  • where 条件 根据条件筛选符合条件的行
  • group by 分组
  • having 过滤条件 根据分组之后的内容进行组的过滤
  • order by 排序
  • limit m,n 从m+1项开始取n项

• 顺序：from 表--> where -> group by -> having -> select -> order by -> limit m,n

  • 注意重命名不是什么时候都可以使用的
  • where 条件不能使用select字段的重命名
  • select emp_name as name, age as a from employee order by a; #order by 可以使用select之后的age as a
  • 在mysql中做了having的优化，可以先找前面是否有重命名，可以在having后面使用重命名，oracle则没有

• 拓展

  • 在执行select语句的时候，实际上是通过where,group by,having这几个语句锁定对应的行

  • 然后循环每一行执行select语句

  • select name from t where id>2;

    先from t,再where id>2拿到数据，再循环拿到的数据,筛选name字段

  • select name,(select_now()) from t; 同样也是循环拿到数据进行筛选

如果一个问题既可以使用连表查询也可以使用子查询解决，推荐那个连表查询，效率更高

习题：

查询至少有一门可与学号为1的同学所学课程相同的同学的学号和姓名

select sid,sname from student right join (

select distinct student_id from score where cid in (select course_id from score where student_id=1) and student_id != 1;

) as t in student_sid = t.student_id;

查询课程编号为‘2’的成绩比课程编号‘1’低的所有同学的学号、姓名

• 思路：
  • 先把课程编号2的所有学生id，学生成绩找出来
  • select student_id as sid2,num as n2 from score where course_id=2;
  • 再把课程编号1的所有学生id，学生成绩找出来
  • select student_id as sid1,num as n1 from score where course_id=1;
  • 连表取到满足条件的学生的学号
  • select t1.sid from (select student_id as sid2,num as n2 from score where course_id=2) as t2
    • inner join
    • (select student_id as sid1,num as n1 from score where course_id=1) as t1
    • on t1.sid1=t2.sid2 and t2.n2 > t1.n1;
  • 连student表找到姓名
    • select student_id,sname from student right join
    • (select t1.sid1 from (select student_id as sid2,num as n2 from score where course_id=2) as t2
    • inner join
    • (select student_id as sid1,num as n1 from score where course_id=1) as t1
    • on t1.sid1=t2.sid2 and t2.n2 > t1.n1) as tmp
    • on student_id = tmp.sid1;

正确的使用mysql数据库

• 从库的角度
  • 搭建集群
  • 读写分离
  • 分库
• 从表的角度
  • 合理安排表与表之间的关系：该拆的拆，该合的合
  • 把固定长度的字段放在前面
  • 尽量使用char而不是varchar
• 从操作数据的角度
  • 尽量在where字段约束数值到一个比较小的范围：分页
    • where a between value1 and value2
  • 尽量使用连表查询代替子查询
  • 删除数据和修改数据的时候条件尽量使用主键
  • 合理的创建和使用索引
    • 创建索引，选择区分度比较大的列，尽量选长度比较短的字段创建索引，不要创建不必要的索引（拖慢写入速度，占空间）
    • 使用索引
      • 查询的字段不是索引
      • 在条件中使用范围，结果的范围越大速度越慢
      • like 'a%' 快，like '%a' 慢且不命中索引
      • 条件列不能参与计算、不能使用函数
      • and / or
        • and 只要有一列索引即可命中
        • or 所有列都有索引才会命中
      • 联合索引，遵循最左前缀原则，且从出现范围开始索引失效
      • 条件中的数据类型和实际字段中的类型必须一致
      • select 字段中应该包含order by中的字段