python MySql

初识数据库

参考:MySQL数据库阶段学习目录

数据库优势

程序稳定性：程序崩溃不会影响数据和服务

数据一致性：所有数据存储在一起

并发：数据库本身支持并发

效率：使用数据库对数据进行的增删改查效率要高

 

数据:   描述事物的符号记录称为数据 （Data）
数据库: 专门用来存储数据          （DataBase，简称DB）
mysql:  数据库软件               （DataBase Management System 简称DBMS）
数据库管理员  管理数据库软件(DBA)
数据库服务器-：运行数据库管理软件
数据库管理软件：管理-数据库
数据库：即文件夹，用来组织文件/表
表：即文件，用来存放多行内容/多条记录

数据库分类

关系型数据库（表结构）：
　　特点相对慢，数据关联性强
　　关系型数据库 : mysql oracle sqllite sql server db2 access

非关系型数据库（key，value）：
　　特点相对快，数据与数据的关联性小
　　非关系型数据库 : redis mongodb memcache

区别：
　　关系型数据库存储效率相对低，数据与数据之间关联紧密
　　关系型数据库存储效率相对高，数据与数据之间的关系是key:value

Mysql语句分为三种（了解）

　　DDL 语句， 数据库定义语言：数据库，表，视图，索引，存储过程，例如create，drop，alter

　　DML 语句，数据库操纵语言：插入、删除、更新、查询数据，insert，delete，update，select

　　DCL 语句， 数据库控制语言：用户的访问权限，grant，revoke

 

Mysql 默认三个数据库：

　　mysql：保存mysql权限，配置参数，状态信息，主从配置

　　information_schema： 保存着mysql维护其他数据库信息，如库名，表和访问权限等

　　performance_schema：数据库存储引擎，监视数据库运行的资源消耗，资源等待等等

Mysql工作流程：

　　登录授权认证安全：
　　查询，解析，分析，优化，缓存
　　存储过程，触发器，视图
　　存储和提取数据
　　存储数据，表信息，用户操作日志

MySql 安装：

　　路径不能有中文

　　路径不能有特殊字符

环境变量

python -->python.exe
在任何目录下都能够找到python.exe文件
才能在任意位置输入python命令启动python解释器

Mysql

mysqld install  安装mysql服务  mysql服务就被注册到操作系统中
net start mysql 启动mysql服务
net stop mysql

启动客户端连接server
mysql -uroot -p123 -h192.168.14.12

mysql>select user();   查看当前登录的用户
mysql>set password = password('123'); 给当前用户设置密码

创建一个其他用户
create user 'guest'@'192.168.14.%' identified by '123';
给一个用户授权
grant 权限类型 on ftp.* to 'guest'@'192.168.14.%';
grant all
grant select on day37.* to 'guest'@'192.168.14.%';
grant select,insert

 

安装(启动,关闭)

cmd管理员

mysqld install  安装mysql服务  mysql服务就被注册到操作系统中
net start mysql 启动mysql服务
net stop mysql
View Code

默认用户登陆

# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
mysql -uroot -p   # mysql5.6默认是没有密码的
#遇到password直接按回车键
View Code

给当前用户设置密码

mysql>select user();   查看当前登录的用户
mysql>set password = password('123'); 给当前用户设置密码
View Code

启动客户端连接server

# 远程登陆
启动客户端连接server
mysql -uroot -p123 -h192.168.14.12 #-p可以加密码但是不建议 -h写连接server的ip地址
View Code

创建用户

# 创建一个其他用户
mysql>create user 'guest'@'192.168.14.%' identified by '123';
创建  用户   "guest"@"ip" identified 密码
# server查看用户
mysql>mysql -uguest -p123 -h192.16.14.200;
View Code

 

python MySql的安装 启动和基础配置---windows版本

链接

　　mysql为我们提供开源的安装在各个操作系统上的安装包，包括ios，linux，windows。

　　mysql的安装、启动和基础配置 —— linux版本 （https://www.cnblogs.com/Eva-J/articles/9664401.html）

　　mysql的安装、启动和基础配置 —— mac版本 （https://www.cnblogs.com/Eva-J/articles/9664401.html）

　　mysql的安装、启动和基础配置 —— windows版本 （https://www.cnblogs.com/Eva-J/articles/9669675.html）

存储引擎

数据的存储方式-->存储引擎

　　使用不同的存储引擎，数据是已不同方法存储的

　　查看存储引擎：show engines;　

 

Innodb:

Innodb存储引擎    mysql5.6之后的默认的存储引擎
数据和索引存储在一起 2个文件
    数据索引\表结构
数据持久化
支持事务   : 为了保证数据的完整性,将多个操作变成原子性操作   : 保持数据安全
支持行级锁 : 修改的行少的时候使用                        : 修改数据频繁的操作
支持表级锁 : 批量修改多行的时候使用                      : 对于大量数据的同时修改
支持外键   : 约束两张表中的关联字段不能随意的添加\删除      : 能够降低数据增删改的出错率

Myisam存储引擎

Myisam存储引擎    mysql5.5之前的默认的存储引擎
数据和索引不存储在一起  3个文件
    数据\索引\表结构
数据持久化
只支持表锁

Memory存储引擎

Memory存储引擎
数据存储在内存中, 1个文件
    表结构
数据断电消失

 

 

 

 

数据库DDL 语句

操作数据库
查看所有数据库  show databases;
创建一个数据库  create database 数据库名;
切换到这个库下  use 数据库的名字
查看这个库下有多少表 show tables;

操作表
创建一张表
create table student(name char(12),age int);
删除表名
drop table  student
查看表结构
desc student;

操作数据
插入数据 : insert into student values ('wusir',73);
查询数据 : select * from student;
修改数据 : update student set age=85 where name='alex';
删除数据 : delete from student where name = 'alex';

 

表和数据的基础操作

表的操作

三种方式

写入数据

insert into 表 values()

写入数据的方式
insert into 表 values (值1,值2,值3);
    这张表有多少的字段,就需要按照字段的顺序写入多少个值
insert into 表 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
    一次性写入多条数据
insert into 表 (字段1,字段3 ) values (值1,值3);
    指定字段名写入,可以任意的选择表中你需要写入的字段进行
View Code

查看中的数据

查表中的数据
    select * from 表

查看表结构
    desc 表名;
        能够查看到有多少个字段\类型\长度,看不到表编码,引擎,具体的约束信息只能看到一部分
    show create table 表名;
        能查看字段\类型\长度\编码\引擎\约束
View Code

数字类型

常用 int,float

定义无符号 unsigned

int 不约束长度,最多表示10位数
float(m,n)
    m 一共多少位,
    n 小数部分多少位
# int
create table t1(
    id int,               # 默认是有符号的
    age tinyint unsigned  # 如果需要定义无符号的使用unsigned
);

# float double
create table t2(
  f1 float(5,2),   # 保留2位小数 并四舍五入
  f2 float,
  f3 double(5,2),
  f4 double
)
insert into t2(f2,f4) values(5.1783682169875975,5.1783682169875975179);
# float decimal
create table t3(
  f1 float,   # 保留2位小数 并四舍五入
  d1 double,
  d2 decimal(30,20),
  d3 decimal
);
View Code

时间

常用 time,date,datetime

date  20190620
time  121953
datetime 20190620121900

datetime    年月日时分秒
year        年
date        年月日
time        时分秒
timestamp   时间戳

create table t4(
    dt datetime,
    y year,
    d date,
    t time,
    ts timestamp
);

mysql> create table t5(
    -> id int,
    -> dt datetime NOT NULL # 不能为空
       DEFAULT CURRENT_TIMESTAMP # 默认是当前时间
       ON UPDATE CURRENT_TIMESTAMP);    # 在更新的时候使用当前时间更新字段
View Code

字符串

常用 char,varchar

char(18) 最多只能表示255个字符
定长存储,浪费空间,节省时间
'alex' 'alex '
varchar(18) 最多能表示65535个字符
变长存储,节省空间,存取速度慢
'alex' 'alex4'

适合使用char
    身份证号
    手机号码
    qq号
    username 12-18
    password 32
    银行卡号
适合使用varchar
    评论
    朋友圈
    微博

create table t6(c1 char(1),v1 varchar(1),c2 char(8),v2 varchar(8));
create table t6(c1 char,v1 varchar(1),c2 char(8),v2 varchar(8));
View Code

enum和set

enum 单选
枚举类型，它的值范围需要在创建表时通过枚举方式显示。
set 多选
set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

create table t8(
    id int,
    name char(18),
    gender enum('male','female')
)

create table t9(
    id int,
    name char(18),
    hobby set('抽烟','喝酒','烫头','洗脚','按摩')
); 
insert into t9 values (1,'太白','烫头,抽烟,喝酒,按摩');
insert into t9 values (1,'大壮','洗脚,洗脚,洗脚,按摩,打游戏'); #多选内容要在一个""里
View Code

约束

约束某一个字段
无符号的 int unsigned
不能为空 not null
默认值  default
唯一约束 unique
    联合唯一 unique(字段1,字段2)
自增 auto_increment
    只能对数字有效.自带非空约束
    至少是unique的约束之后才能使用auto_increment
主键 primary key
    一张表只能有一个
    如果不指定主键,默认是第一个非空+唯一
    联合主键 primary key(字段1,字段2)
外键 Foreign key
    Foreign key(自己的字段) references 外表(外表字段)
    外表字段必须至少是"唯一"的
View Code

无符号

unsigned

create table t10(
  id int unsigned
);
View Code

不能为空

 not null

create table t11(
  id int unsigned not null, 
  name char(18) not null
);
View Code

默认值

default

create table t12(
  id int unsigned not null,
  name char(18) not null,
  male enum('male','female') not null default 'male's
);
View Code

唯一约束(UNI)

unique

不能重复  unique   值不能重复,但是null可以写入多个
create table t13(
  id1 int unique,
  id2 int
)
View Code

 

联合唯一

unique

联合唯一 unique
create table t14(
    id int,
    server_name char(12),
    ip char(15),
    port char(5),
    unique(ip,port)
);
View Code

 

非空 + 唯一约束+主键

not null (不能为空)unique(唯一约束)

非空 + 唯一约束
第一个被定义为非空+唯一的那一列会成为这张表的primary key
一张表只能定义一个主键
create table t15(
    id int not null unique,
    username char(18) not null unique
);
create table t16(
    username char(18) not null unique,
    id int not null unique
);
create table t17(
    username char(18) not null unique,
    id int primary key
);
View Code

联合主键

primary key 和 not null unique

联合主键
create table t18(
    id int,
    server_name char(12),
    ip char(15) default '',
    port char(5) default '',
    primary key(ip,port)
);
View Code

 

自增

auto_increment

约束字段为自动增长，被约束的字段必须同时被key约束

create table t20(
    id int primary key auto_increment,
    name char(12)
);
insert into t20(name) values('alex');
View Code

外键

foreign key (class_id) references class (cid))

班级表
create table class(
    cid int primary key auto_increment,
    cname char(12) not null,
    startd date
)
学生表
create table stu(
    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(cid)
)


create table stu2(
    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(cid)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除 尽量不用
)
View Code

 

修改表结构

修改表名
alter table 表 rename 新表名
增加字段
alter table 表 add age int not null
更改字段顺序
alter table 表 add 新字段名 date after 字段名  
# 新字段名放在字段名下
删除字段名
alter table 表 drop 字段名
修改字段类型
alter table 表 modify 字段名 类型() not null
alter table 表 change 旧名字 新名字 类型(长度) 约束;
View Code

表之间的关系

一对一
一对多
多对多

小结

存储引擎
    Innodb mysql5.6之后的默认存储引擎
        2个文件,4个支持(支持事务,行级锁,表级锁,外键)
    Myisam mysql5.5之前的默认存储引擎
        3个文件 支持表级锁
    Memory
        1个文件 数据断电消失
数据类型
    数字 : bool int float(7,2)
    日期 : date time datetime year
    字符串 :
        char    定长 效率高浪费空间 255
        varchar 变长 效率低节省空间 65535
    enum 和 set :
        单选和多选
约束
    unsigned 无符号的
    not null 非空
    default  设置默认值
    unique   唯一,不能重复
        unique(字段1,字段2,字段3) 联合唯一
     auto_increment 自增
        int 必须至少unique字段,自带not null
    primary key 主键
        not null + unique
        一张表只能有一个主键
    foreign key 外键
        a表中有一个字段关联b表中的一个unique
        a表中的是外键
建表
    create table 表名(
      字段名1 类型(长度) 约束,
      字段名1 类型(选项) 约束,
    );
修改表结构
    alter table 表名 rename 新名字;
    alter table 表名 add 字段名 类型(长度) 约束 after 某字段;
    alter table 表名 drop 字段名;
    alter table 表名 modify 字段名 类型(长度) 约束 first;
    alter table 表名 change 旧字名 新名字 类型(长度) 约束;
表之间的关系
    一对一
    一对多
    多对多
    
删除表
    drop table 表名;

 

数据的增删改查

增

增 
insert into 表(字段,...) values (值,...);
insert into t1 value (1,'大壮','male','上课,写作业');
insert into t1 values(2,'杜相玺','male','写作业,考试');
insert into t1 values(3,'b哥','male','写作业'),(4,'庄博','male','考试');
insert into t1(username,hobby) values ('杨得港','上课,写作业,考试'),('李帅','考试')
insert into t2(id,name) select id,username from t1;

insert into 表 values (值)
insert into 表(字段,字段2) values (值,值2)
insert into 表(字段,字段2) select 字段1,字段2  from 表2
View Code

删

删
清空表
    delete from 表;
        会清空表,但不会清空自增字段的offset(偏移量)值
    truncate table 表;
        会清空表和自增字段的偏移量
删除某一条数据
    delete from 表 where 条件;
View Code

改

改
    update 表 set 字段=值 where 条件;
    update 表 set 字段=值,字段=值 where 条件;
View Code

 

单表数据查询

select语句

最简单的
select *from 表;
select 字段 from 表;
View Code
重命名
select 旧字段 as 新字段 from 表;
select 旧字段 新字段 from 表;
View Code
去重

distinct

select distinct 字段 from 表;
select distinct  age,sex from 表; #显示表里的age,sex
View Code
连接字符串(分割)

concat() 函数用于连接字符串

concat() 函数用于连接字符串
select concat('姓名: ',字段,'  年薪: ', 字段)  AS 重命名 from 表; 
concat_ws 第一个参数为分隔符
select concat_ws(':',字段,字段)  AS 新表名 from 旧表名
View Code
四则运算的
select 字段*数字 from 表; 乘法
select 字段*数字 as 重命名 from 表;
select 字段*数字 重命名 from 表;
View Code
使用判断逻辑
case when语句 相当于 if条件判断句
   select
       (
           case
           when emp_name = 'jingliyang' then
               emp_name
           when emp_name = 'alex' then
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   from
       employee;
View Code
查询重命名
1.where条件中不能用select字段的重命名
2.order by 或者having可以使用select字段的重命名
    主要是因为order by 在select语句之后才执行
    having经过了mysql的特殊处理,使得它能够感知到select语句中的重命名
View Code

 

拓展
在执行select语句的时候,实际上是通过where,group by,having这几个语句锁定对应的行
然后循环每一行执行select语句

 

where 筛选

筛选所有符合条件的行

where 筛选所有符合条件的行
    比较运算符
        > < >= <= <> !=
    范围
        between 10000 and 20000 要1w-2w之间的
        in (10000,20000)   只要10000或者20000的
    模糊匹配
        like
            % 通配符 表示任意长度的任意内容
            _ 通配符 一个字符长度的任意内容
        regexp
            '^a'
            'g$'
    逻辑运算
        not\and\or
View Code
单条件查询
select 字段 from 表 where  post='sale'; #  post='sale' 是判断条件
View Code

 

多条件查询
select 字段,字段 from 表名 where post="teacher" and salary>10000; #表名后面是判断条件
View Code
between and(范围)
select 字段0,字段1 from 表 where 字段1  between 10000 and 20000;    # 判断字段1在 10000和20000范围内
select 字段0,字段1 from 表 where 字段1  not between 10000 and 20000;# 判断字段1不在 10000和20000内
View Code
is判断是否为空
关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
select 字段1,字段2 from 表 where 字段2 is null;       #判断字段2为空的
select 字段1,字段2 from 表 where 字段2 is not null;   #判断字段2不为空的
View Code
in集合查询
select 字段1,字段2 from 表 where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;
select 字段1,字段2 from 表 where 字段2 in(3000,3500,4000,9000);
select 字段1,字段2 from 表 where 字典2 not in(3000,3500,4000,9000);
View Code
模糊匹配

like  regexp

like
    % 通配符 表示任意长度的任意内容
    select*from 表 where 字段 like  '%g%';
    _ 通配符 一个字符长度的任意内容
regexp
    正则
        '^a'      #开头^
        'g$'      #$结尾
View Code

 

逻辑运算

not\and\or

分组 group by

group by 根据谁分组,可以求这个组的总人数,最大值,最小值,平均值,求和 但是这个求出来的值只是和分组字段对应并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效.

单独使用GROUP BY关键字分组
select post from 表 group by post;
只能筛选行,不能筛选列

注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
group by与聚合函数一起使用
select sex,count(id) from 表 group by sex;
View Code

聚合分组 (根据分组做计数)

count  求个数
max    求最大值   
min    求最小值   
sum    求和                  
avg    求平均
View Code
举例
没有聚合函数
SELECT post,emp_name FROM employee GROUP BY post;
select 字段1,字段2 from 表 group by 字段1
View Code

having 过滤语句

# 执行优先级从高到低：where > group by > having 
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
View Code
having过滤
select post,avg(salary) from employee group by post having avg(salary)>10000;
select 字段, 聚合函数    from  表      group by 字段  having 聚合函数>10000
View Code

order by 排序

默认是升序  asc(升序)
降序  desc
select *from 表 order by age        #从小到大看年龄
select *from 表 order by age desc   #从大到小看年龄
select *from 表 order by 字段1,字段2 desc #字段1从小到大,字段2从大到小
优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排
View Code

limit限制查询的记录数

select *from 表 order by 字段 desc limit 1; #取第一个
select *from 表 order by 字段 desc limit 3; #取前三个
select *from 表 order by 字段 desc limit 2,1; #取第3个

limit m,n
    从m+1项开始,取n项
    如果不写m,m默认为0

# limit m,n 和 limit n offset m一样的意思
View Code

 

多表查询

有两种:
    连表查
        内连接  必须左表和右表中条件互相匹配的项才会被显示出来
            表1 inner join 表2 on 条件
        外链接 会显示条件不匹配的项
            left join 左表显示全部,右表中的数据必须和左表条件互相匹配的项才会被显示出来
            right join 右表显示全部,左表中的数据必须和右表条件互相匹配的项才会被显示出来
            全外连接
                left join
                union
                right join
    子查询
        select * from 表 where 字段 = (select 字段 from 表 where 条件)
        select * from 表 where 字段 > (select 字段 from 表 where 条件)
        select * from 表 where 字段 in (select 字段 from 表 where 条件)

 

连表查询

所谓连表
总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
再根据条件进行筛选就可以了

 

内连接

内连接 inner join ... on ...
    select * from 表1,表2 where 条件;(了解)
    select * from 表1 inner join 表2  on 条件
    select * from department inner join employee on department.id = employee.dep_id;
    select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id;
View Code

外连接

左外连接 left join ... on ...
    select * from 表1 left join 表2 on 条件
    select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
View Code

右外连接

右外连接 right join ... on ...
    select * from 表1 right join 表2 on 条件
    select * from department as t1 right join employee as t2 on t1.id = t2.dep_id
View Code

全外连接

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意：mysql不支持全外连接 full JOIN
# 强调：mysql可以下面的方式间接实现全外连接
View Code
全外连接 union
    select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
    union
    select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;
View Code

例子

# 1.找到技术部的所有人的姓名
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name from department d inner join employee e on e.dep_id = d.id where d.name='技术';

# 2.找到人力资源部的年龄大于40岁的人的姓名
# select * from department d inner join employee e on e.dep_id = d.id
# select * from department d inner join employee e on e.dep_id = d.id where d.name='人力资源' and age>40;

# 3.找出年龄大于25岁的员工以及员工所在的部门
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name,d.name from department d inner join employee e on e.dep_id = d.id where age>25;

# 4.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
# select * from department d inner join employee e on e.dep_id = d.id order by age;

# 5.求每一个部门有多少人
# select d.name,count(e.id) from department d left join employee e on e.dep_id = d.id group by d.name;
# 且按照人数从高到低排序
# select d.name,count(e.id) c from department d left join employee e on e.dep_id = d.id group by d.name order by c desc;

# 所谓连表就是把两张表连接在一起之后 就变成一张大表  从from开始一直到on条件结束就看做一张表
# 之后 where 条件 group by 分组 order by limit 都正常的使用就可以了
View Code

子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等
# 查询平均年龄在25岁以上的部门名
# select name from department where id in (
#     select dep_id from employee group by dep_id having avg(age)>25);

# 查看技术部员工姓名
    # 先查询技术部的部门id
    # select id from department where name = '技术';
    # 再根据这个部门id找到对应的员工名
    # select name from employee where dep_id =(select id from department where name = '技术');
    # select name from employee where dep_id in (select id from department where name = '技术');

# 查看不足1人的部门名
    # 先把所有人的部门id查出来
    # select distinct dep_id from employee;
    # 然后查询部门表,把不在所有人部门id这个范围的dep_id找出来
    # select name from department where id not in (select distinct dep_id from employee);

# 查询大于所有人平均年龄的员工名与年龄
    # 求平均年龄
    # select avg(age) from employee;
    # select * from employee where age >28;
    # select name,age from employee where age >(select avg(age) from employee);

# 查询大于部门内平均年龄的员工名、年龄
    # select dep_id,avg(age) from employee group by dep_id;
    # select name,age from employee as t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) as t2
    # on t1.dep_id = t2.dep_id where age>avg_age;
View Code

EXISTS关键字

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。 而是返回一个真假值。True或False 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)
View Code

小结

如果一个问题既可以使用连表查询解决,也可使用子表查询
推荐使用连表查询,因为效率高.

数据准备

open('file') as f
for line in f:
    pass
10个字节
一块内容也很快就会被用到
每一次读取硬盘的单位不是你要多少就读多少
每一次读取的数据块的大小都是固定的
4096个字节 - block块

 

索引的创建与删除

索引的创建与删除
创建主键 primary key 聚集索引 + 非空 + 唯一
创建唯一约束 unique  辅助索引 + 唯一
添加一个普通索引
添加:        create index 索引名 on 表(字段);
删除:        drop index 索引名 on 表;

新的数据结构 -- 树

# root 根节点
# branch 分支节点
# leaf 叶子节点

# 父子节点

b+树
    b是balance 平衡的意思
        为了保证每一个数据查找经历的IO次数都相同
    只在叶子节点存储数据
        为了降低树的高度
    叶子节点之前加入了双向连接
        为了查找范围的时候比较快
View Code

 

两种索引的差距

两种索引的差别
聚集索引 聚簇索引
    Innodb 必有且仅有一个 :主键
    innodb存储引擎中的 主键默认就会创建一个聚集索引
    全表数据都存储在叶子节点上 -- Innodb存储引擎中的主键
非聚集(簇)索引 辅助索引
    innodb
    myisam
    叶子节点不存放具体的整行数据,而是存储的这一行的主键的值

数据库使用的时候注意事项

从搭建数据库的角度上来描述问题

建表的角度上
    1.合理安排表关系
    2.尽量把固定长度的字段放在前面
    3.尽量使用char代替varchar
    4.分表: 水平分,垂直分

使用sql语句的时候

使用sql语句的时候
    1.尽量用where来约束数据范围到一个比较小的程度,比如说分页的时候
    2.尽量使用连表查询而不是子查询
    3.删除数据或者修改数据的时候尽量要用主键作为条件
    4.合理的创建和使用索引

正确使用索引

1.查询的条件字段不是索引字段
    对哪一个字段创建了索引,就用这个字段做条件查询
2.在创建索引的时候应该对区分度比较大的列进行创建
    1/10以下的重复率比较适合创建索引
3.范围
    范围越大越慢
    范围越小越快
    like 'a%'  快
    like '%a'  慢
4.条件列参与计算/使用函数
5.and和or
    id name
    select * from s1 where id = 1800000 and name = 'eva';
    select count(*) from s1 where id = 1800000 or name = 'eva';
    多个条件的组合,如果使用and连接
        其中一列含有索引,都可以加快查找速度
    如果使用or连接
        必须所有的列都含有索引,才能加快查找速度
6.联合索引 : 最左前缀原则(必须带着最左边的列做条件,从出现范围开始整条索引失效)
    (id,name,email)
    select * from s1 where id = 1800000 and name = 'eva' and email = 'eva1800000@oldboy';
    select * from s1 where id = 1800000 and name = 'eva';
    select * from s1 where id = 1800000 and email = 'eva1800000@oldboy';
    select * from s1 where id = 1800000;
    select * from s1 where name = 'eva' and email = 'eva1800000@oldboy';
    (email,id,name)
    select * from s1 where id >10000 and email = 'eva1800000@oldboy';
7.条件中写出来的数据类型必须和定义的数据类型一致
    select * from biao where name = 666   # 不一致
8.select的字段应该包含order by的字段
    select name,age from 表 order by age;  # 比较好
    select name from 表 order by age;  # 比较差
View Code

 

分页

使用索引的时候分页不要用limit
使用where
300万条数据
分页
page = 1
num_per = 10
tmp = (page-1)*num_per = 1-1=0*10 = 0
select * from 表 where id between tmp and tmp+num_per
page +=1 = 2
tmp = (page-1)*num_per = 10
select * from 表 where id between 10 and 20

select * from 表 limit 10,10
select * from 表 limit 20,10

select * from 表 limit 2999990,10

 

索引合并

索引合并 :分开创建在查询过程中临时合并成一条 Using union(ind_id,ind_email)
    创建索引的时候
    create index ind_id on s1(id)
    create index ind_email on s1(email)
    select * from s1 where id=100 or email = 'eva100@oldboy'
    临时把两个索引ind_id和ind_email合并成一个索引
View Code

覆盖索引

覆盖索引:在查询过程中不需要回表   Using index
    对id字段创建了索引
    select id from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    select count(id) from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    select max(id) from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    select name from s1 where id =100   相对慢
View Code

执行计划

什么是mysql的执行计划?用过explain么?
    在执行sql语句之前,mysql进行的一个优化sql语句执行效率的分析(计划),可以看到有哪些索引,实际用到了那个索引,执行的type等级
    id name email
    select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';
        有没有索引
        有几个
        用哪一个索引比较效率高
    explain select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';

 

慢查询优化

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

 

mysql的慢日志

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

 

SQL SERVER 索引名前缀代表的意思

PK-主键

IX-非唯一索引

AK-唯一索引（AX应该是AK（备用键））

CK-检查约束

DF-默认约束

FK-外键

 

pymysql模块

import pymysql

db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接
cursor.execute("SELECT VERSION()")                    # 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone()                              # 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close()                                            # 关闭数据库连接
View Code
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password="123",
                       database='homework')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 查询返回字典
cur = conn.cursor()  # cursor游标
cur.execute('select * from student;')
print(cur.rowcount)   # 获取查出多少行,便于使用fetchone取所有结果
# for i in range(cur.rowcount):
#   ret = cur.fetchone()      # 获取一条结果
#   print(ret)
try:
    cur.execute('select * from student;')
    ret = cur.fetchone()      # 获取一条结果
    print(ret)
    ret2 = cur.fetchmany(10)  # 获取多条结果
    print(ret2)
    ret3 = cur.fetchall()     # 获取全部结果
    print(ret3)
except pymysql.err.ProgrammingError as e:
    print(e)
cur.close()
conn.close()
View Code

增 删 改

# 增加 删除 修改
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password="123",
                       database='homework')
cur = conn.cursor()  # cursor游标
try:
    cur.execute('insert into student values(18,"男",3,"大壮")')     # 增
    cur.execute('update student set gender = "女" where sid = 17')  # 改
    cur.execute('delete from student where sid = 17')               # 删
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()     # 可以试一下 myisam
cur.close()             # 关闭游标
conn.close()            # 关闭库
View Code

登陆pymysql

结合数据库 和python 写一个登录
user = input('username :')
pwd = input('password :')
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password="123",
                       database='day42')
sql = 'select * from userinfo where user = %s and password = %s'
cur = conn.cursor()
cur.execute(sql,(user,pwd))
print(cur.fetchone())
sql注入
        传参数,注意sql注入的问题,传参数通过execute方法来传
        execute('select * from 表 where name = %s',('alex',))
# select * from userinfo where user = "1869" or 1=1;-- " and password = "3714";
注入;-- 
注释掉后面的加--
View Code

 

 

 

事务和锁

事务介绍

事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败

事务的四大特性:

1.原子性(Atomicity)
　　事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。
2.一致性(Consistency)
　　事务发生前和发生后，数据的完整性必须保持一致。
3.隔离性(Isolation)
　　当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。也就是其他人的操作在这个事务的执行过程中是看不到这个事务的执行结果的，也就是他们拿到的是这个事务执行之前的内容，等这个事务执行完才能拿到新的数据。
4.持久性(Durability)
　　一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也不允撤销，只能通过'补偿性事务'。
事务的开启：
　　数据库默认事务是自动提交的，也就是发一条sql他就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql会自动回滚事务。或者我们使用rollback命令手动回滚事务。


begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务
View Code

表和数据的备份

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

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql
View Code

数据恢复

#方法一：
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql
View Code
D:\python_22\day42\tmp.sql

表和数据的备份
    备份数据 在cmd命令行直接执行
    mysqldump -uroot -p123 -h127.0.0.1 homework > D:\python_22\day42\tmp.sql

    恢复数据 在mysql中执行命令
    切换到一个要备份的数据库中
    source D:\python_22\day42\tmp.sql

备份库
    备份
    mysqldump -uroot -p123 --databases homework > D:\python_22\day42\tmp2.sql
    恢复
    source D:\python_22\day42\tmp2.sql
View Code
posted @ 2019-08-15 00:11  驰念  阅读(965)  评论(0)  编辑  收藏  举报