python学习 第十周知识回顾(MYSQL数据库)

橘子菌菌
133 阅读8分钟

数据库简单了解

数据存取演变史

文本文件>>>软件开发目录规范>>>数据库服务(完全统一位置与和格式)

数据库软件应用史

单机数据>>>联网数据
    不同计算机上的相同程序之间可以数据共享，单独在网络上架设远程数据库服务
       关于远程数据库服务 
         1.数据安全性问题 
         2.服务器负载问题 
         通过数据库集群解决，就是让多台服务器运行相同的数据库服务

数据库本质(cs架构)

  • 底层角度看数据库是专门操作数据进程的(运行内存中的代码)
  • 现实角度看数据库是操作界面的应用程序(操作进程的界面)

数据库分类

  • 关系型sql(有明确表结构，彼此建立数据库层面的关系) 
    MYSQL,PostgreSQL,MariaDB,Oracle,
  • 非关系型Nosql 
    redis(缓存型),mongoDB(稳定型),memcache

mysql配置

  • 1.下载安装与系统服务制作 
    1.管理员身份打开
2.执行系统服务命令        mysqld --install
3.启动服务端              net start mysql
4.查看系统服务命令        sercices.msc
5.关闭mysql服务端         net stop mysql
6.移除系统服务            确保服务已经关闭后，执行命令mysqld --remove
7.用户名密码登录          mysql -u用户名 -p密码 
8.退出                   exit 或 quit
  • 2.主要文件介绍 
    bin文件夹       MySQL.exe客户端，mysqld.exe服务端
data文件夹      存取文件
my-default.ini  默认配置文件
  • 3.数据库重要概念(库，表，记录)

mysql基本操作

增删改查

库:    增     create database 库名; 
       删     drop database 库名; 
       改     alter database 库名 charset='gbk'; 
       查     show databases; 
              show create database 库名;
              
        use 库名;   切换到库
              
表:    增     create table 表名(字段名 字段类型,字段名 字段类型);
       删     drop table 表名; 
       改     alter table 旧表名 rename 新表名; 
       查     show tables; 
              show create table 表名; 
              describe 表名; 
              desc 表名;

记录:  增     insert into 表名 values(数据,数据),(数据,数据); 
       删     delete from 表名;                   # 删除表中所有的数据 
              delete from 表名 where 筛选条件      # 按条件删除 
       改     update 表名 set 字段名=新数据 where 筛选条件; 
       查     select * from 表名;                  # 查所有字段 
              select 字段1,字段2 from 表名;         # 如果表中字段较多出现了错乱 可以结尾写\G

1.SQL语句结束符是分号                  ; 
2.取消SQL语句的执行                    \c 
3.查看所有的数据库                     show databases; 
4.查看所有的表                         show tables; 
5.查看user表里面所有的记录             select * from mysql.user;

SQL语句的补充

  • 修改表名
alter table 表名 rename 新表名;
  • 新增字段
alter table 表名 add 字段名 字段类型(数字) 约束条件; 
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段; 
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
  • 修改字段
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
  • 删除字段
alter table 表名 drop 字段名;

表查询关键字

  • select与from
    • select自定义查询表中字段对应的数据
    • from指定操作的对象(那几张表)
  • where筛选
    • where后面的是条件,用于分组之前的筛选
  • group by分组
    • 按照一些指定的条件将单个单个的数据分为一个个整体
    • 分组之后我们研究的对象应该是以组为单位
    • 不应该再直接获取单个数据项 如果获取了应该直接报错
    • select后面可以直接填写的字段名只能是分组的依据
    • 起别名使用as
最大值   max
最小值   min
平均值   avg
总和     sum
计数     count
  • having过滤
having用于分组之后的筛选
  • distinct去重
去重的前提是数据必须一模一样
  • order by排序
select * from emp order by salary asc;    # asc也可以不写，因为默认升序
select * from emp order by salary desc;   # desc降序
  • limit分页
# 限制要展示的数据条数 
    select * from emp limit 3; 
# 指定要展示的数据信息 0的位置是起始，5的位置是条数 
    select * from emp limit 0,5;
  • regexp正则
# 通过sql语句与正则表达式的结合筛选出符合条件的数据
   select * from emp where name regexp '^(j|o).*(n|y)$';

多表查询思路

    1. 子查询
    • 基于一个表的结果进行二次操作，第一次的结果变为第二次的条件
    1. 连表操作
    • 拼接所有涉及结果的表，在拼接后的表内查询数据
    内连接      inner join
左连接      left join
右连接      right join
两边连接    左边左连接，右边右连接，中间用union将语句连起来
  • SQL知识点补充 
    as              给字段起别名，给SQL语句取表名 
comment         给表，字段添加注释信息 
concat          用于分组之前多个字段数据的拼接 
concat_ws       与concat作用一致，但是可以多个减少代码 
exists exists   后面的sql语句有结果就执行前面的sql语句，反之不执行

编码与配置

\s                查看数据库基本信息
修改配置文件       my-default.ini拷贝重命名为my.ini 修改里面内容文件

存储引擎

  • mysql大小写不敏感
MYISAM     速度快，功能少，安全低                底层文件：  .frm/.myd/.myi
InnoDB     速度较慢，功能多，安全高                         .frm/.ibd
Memory     速度快，基于内存，断电丢失数据                    .frm
BlackHole  黑洞，写入立刻丢失                               .frm

创建存储引擎语法:  create table 表名(字段名 字段类型) engine=存储引擎;   

创建表的完整语法:  create table 表名( 
                      字段名1 字段类型(数字) 约束条件，
                      字段名2 字段类型(数字) 约束条件， 
                      字段名3 字段类型(数字) 约束条件 )
                      engine=存储引擎;

字段类型

  • 括号内数字基本都是用来限制字段的存储长度(除了整型)
    • 修改数据库内插入超出范围的报错提示 
      临时修改：  
    set session sql_mode='strict_trans_tables'  # 当前客户端的操作界面有效
    set global sql_mode='STRICT_TRANS_TABLES'   # 服务端不重启永久有效
配置文件永久修改：
    [mysqld] 
        sql_mode='STRICT_TRANS_TABLES'

字段类型之整型

tinyint       1bytes 
smallint      2bytes 
int           4bytes 
bigint        8bytes

字段类型之浮点型

     # 第一个数表示总共多少位,第二个数表示小数占多少位 
 float   使用方式: float(255,30)
 double  使用方式: double(255,30) 
 decimal 使用方式: decimal(65,30)
  
 数据精准度：   float < double < decimal 一般情况下float足够使用了

字段类型之字符型

   # 就最大能存储几个字符，超出范围报错
char    定长   char()括号里是几, 不超出范围，用空格填充到几个字符
varchar 变长   varchar()括号里是几, 不超出范围，能有几位就存几位

char    整存整取，速度快，但是浪费存储空间
varchar 节省存储空间，但是速度比char慢

字段类型之枚举与集合

  • 枚举enum(多选一)
    • 只能选择一个在里面的，不在选择里面的写入会报错
  • 集合set(多选多)
    • 可以选择里面的多个或一个，不在选择里面的写入会报错

字段类型之日期类型

date     年月日 
datetime 年月日时分秒 
time     时分秒 
year     年

字段的约束条件

1. unsigned     无负号
2. zerofill     零填充
3. not null     非空
4. defaule      默认值
5. unique       唯一值

主键外键

主键(primary key)

  • InnoDB引擎规定一张表只能有一个主键
1.不设主键，会将唯一非空默认为主键，没有设置InnoDB隐藏的字段
2.主键可以加快数据查询
3.联合主键       id1 int,id1 int,primary key(id1,id2)
  • 主键自增
      1. 自增设置关键字 auto_increment
      1. 主键的自增作为约束条件要跟在键的后面，无法单独使用
      1. 在删除数据时，自增操作不会回退与重置，想要重置主键，需要对表格式化
      truncate 表名;

外键(foreign key)

  • 外键字段专门用于记录表与表之间的数据关系

表关系(基于多张表的创建才能设置表关系)

# 数据的关系有四种 
     1. 一对一的关系     外键设置在查询频率高的
     2. 一对多的关系     外键设置在多的一边
     3. 多对多的关系     外键建在开设的第三张关系表。存储数据关系
     4. 没有关系
  • 级联更新
on update cascade # 级联更新
  • 级联删除
on delete cascade # 级联删除

Nacicat下载

  • 百度

使用python操作数据库

  • 模板
import pymysql 
    # 1.链接服务端 
conn = pymysql.connect( 
     host='127.0.0.1',     # ip地址 
     port=3306,            # mysql端口号 
     user='root', password='', 
     database='db',        # 库名 
     charset='utf8mb4', autocommit=True ) 
     
     # 2.产生一个游标对象      
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 
     # 3.sql语句编写 
sql1 = 'select * from class'    # 操作class表
     # 4.发送给服务端 
cursor.execute(sql1)
     # 5.获取命令的执行效果
res = cursor.fetchone()         # fetchone()可更换 
print(res)

cursor.fetchone() # 获取光标所在位置往后的一条数据 
cursor.fetchall() # 获取光标所在位置往后的所有数据 
cursor.fetchmany() # 获取光标所在位置往后指定条数的数据 
cursor.scroll(0, mode='absolute') # 基于数据集开头的位置往后移动，0处代表移动的位数 
cursor.scroll(2, mode='relative') # 基于当前位置往后移动，2处代表移动的位数

SQL注入

 为了避免用户输入的特殊符号可能会变为sql的语法这情况出现，
     我们需要将格式化获取到的用户数据操作放到 cursor.execute()方法内.

视图

  • 视图时一张被查询出来的表，无法进行删除。通常只用作查询
  • 关键字:view 
    模板：  create view 视图名 as SQL语句

触发器(trigger)

  • 触发器是针对表数据的增改删前后自动触发的功能
语法结构： 
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row 
begin 
    sql语句 
end
  • 使用触发器前，需要先对结束符进行修改，因为触发器会用到分号结束符
delimiter 要修改后的结束符 
     # 结束符修改后仅限临时使用，结束后要修改回来

drop trigger tri_after_insert_cmd;
     # 第一次给触发器起名要做到见名知意

1. 创建一个表，记录每次执行插入的数据
2. 创建另一个表，记录每次执行错误的数据
3. 使用触发器自动触发针对表数据的增改删    delimiter $$ # 修改结束符
4，往第一个创建的表中插入数据，触发触发器，依据if条件判断，是否插入错误日志数据
5. 查询错误日志表的记录

事务

事务的四大特征(ACID)

A: 原子性 : 必须保证事务语句操作，同时成功或者同时失败,发生错误回退，返回至上一状态 
C: 一致性 : 事务前后数据的完整要保持一致，与原子性密切相关 
I: 隔离性 : 多个用户并发访问数据库，每一个开启的用户事务互不干扰，隔离开。 
D: 持久性 : 事务提交后改变是永久的，数据库发生问题不会影响
  • 事务的创建 
    关键字  start transaction

   # 创建事务的操作
1. 创建一个用户表
2. 插入用户数据
3. 开启事务操作                     start transaction;
4. 修改数据操作
5. 使用rollback返回到上一次状态      rollback;
6. 将修改后的数据刷到硬盘中          commit;
  • 知识扩展
    • MYSQL中提供两种事务存储引擎InnoDB(重要)与NDB cluster，及三方的XtraDB，PBXT。
    • 事务处理相关词汇 
      事务(transaction)
回滚(roolback)
提交(commit)
保留点(savepoint)
   保留点违背了事务的原则，有些数据库不支持保留点
      创建占位符： savepoint sp01; # sp01是自己创建占位符 
      回滚占位符： rollback to sp01;

事务的隔离级别

  • InnoDB支持所有的隔离级别
语法: set transaction isolation level 级别
  • 四种隔离级别
# 1. read uncommitted (未提交读) 
  可以读取到事务里修改了但未提交的数据，也被称为"脏读",其他事务对脏读是可见的 
# 2. read committed (提交读) 
  能读取到提交后的数据，但是读取不了提交之前的所作修改的数据，也叫做"不可重复读" 提交读是大部分数据库默认的隔离级别 
# 3. repeatable read (可重复读) 
  可以解决脏读，无法解决幻读，幻读指的是事务读取某个范围的记录时，插入的新的记录， 
  事务再次读取会产生幻行，InnoDB要通过多版本并发控制(MVCC)及间隙锁策略解决问题 
# 4. serializable (可串行读) 
  强制事务串行执行，很少使用该级别
  • 事务的日志
记录事务修改存储记录，将其持久到硬盘上的事务日志中
  • MVCC只在提交读(read committed)和可重复读(repeatable read)两种隔离级别下工作
  • 存储过程 
    类似于python中的自定义函数

模板: 
    delimiter 临时结束符 
    create procedure 名字(参数,参数) # 加的参数要给出名字和数据类型 
    begin 
        sql语句; 
    end 临时结束符 
    delimiter ;
    • 调用需要使用call

内置函数

1. 移除指定字符                     Trim，LTrim,RTrim
2. 大小写转换                       Lower,Upper
3. 获取左右起始指定个数字符          Left,Right
4. 返回读音相似值                   Soundex
5. 日期格式                        date_format
      # 相关日期函数 
         adddate 增加一个日期 
         addtime 增加一个时间 
         datediff 计算两个日期差值

## 流程控制

  • if条件
  • while循环

索引

  • 相当于目录，加快查找速度
  • 在mysql中实现索引的方式叫做键，作为一种数据结构，大致有三种 
    三种键:
primary key 主键 
unique key  唯一键 
index key   索引键，无任何意义

区别:  
 primary key,unique key 加快了数据查询，但是有额外限制 
 index key 没有额外限制，加快数据查询 
底层:
 其实就是树,树是一种数据结构，主要用于优化数据查询的操作.

树

  • B树
    • 除了叶子节点外的其他节点最多只能有两个分支(所有节点都能直接存放完整大小的数据块)
  • B+树
    • 只有叶子节点存放完整的数据，其他节点只存主键值
  • B* 树
    • 在树节点添加了其他节点的通道，减少查询次数

慢查询优化

  • explain命令 
    语法 : explain select 字段名 from 表名 where 筛选条件;
    • 两个变种 
      explain extended        可以在mysql5.6显示filtered字段。
explain partitions      可以在mysql5.6显示partitions字段
 
show warnings;          在explain操作结束后，对mysql内部优化后的结果按照展示的结果执行。
  • explain命令应用
    • 查询数据的方式
      • 全表查询 
        什么时候出现全表查询? 
  1. 业务需要获取所有的数据 
  2. 不执行索引导致要全表扫描 *
     2.1 没有索引 
     2.2 索引创建有问题 
     2.3 语句有问题
 mysql内要避免全表扫描，因为在进行全表扫描时会降低mysq的性能
      • 索引扫描 
        常见的索引扫描类型:
index,range,ref,eq_ref,const,system,null
avatar
文章
阅读
粉丝