什么是数据库？

• 数据库（database）是按照数据结构来组织、存储和管理数据的仓库

数据库事务

事务四大特性（ACID):

• 原子性（atomicity）：原子只是一个比较就是最小的单位，在事务中意思就是一系列操作要保证完整性，不能只执行一部分
• 一致性（consistency）：一致性就是在没有提交事务之前，不管对数据进行多少次操作，真实的数据都不会改动，只有提交了事务数据才会更改
• 隔离性（Isolation）：隔离性就是在没有完成本次事务之前，不允许其他用户对数据进行操作
• 持久性（Durability）：一旦事务提交了，就永久写到数据库中了

事务列子：

银行转账

场景：A用户转账给B用户500块钱，如果转账过程中系统出问题了，A用户扣钱了，B用户却没有收到钱

解决方案：使用事务，在A转账前开启事务，如果给B转账中失败，只要不提交事务，数据就不会改动

索引

语法格式

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
);

• UNIQUE：可选。表示索引为唯一性索引。
• FULLTEXT；可选。表示索引为全文索引。
• SPATIAL：可选。表示索引为空间索引。
• INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
• 索引名：可选。给创建的索引取一个新名称。
• 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
• 长度：可选。指索引的长度，必须是字符串类型才可以使用。
• ASC：可选。表示升序排列。
• DESC：可选。表示降序排列。

含义：索引就好比书的目录
优缺点：
优点：数据大的速度快
缺点：如果建立索引的表经常修改，会影响性能，占用磁盘
什么时候使用：
数据量大，查询频率高，而且这个字段经常使用
原理：相当于把数据进行分块整理，在一小块中去查询，减少查询次数

pycharm操作数据库

from pymysql import connect


def search_all_info():
    """查询所有的商品信息"""
    # 从数据库得到数据
    # 链接数据库
    # 执行SQL语句
    # 关闭
    # 处理数据

    # 链接数据库
    conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
    # 游标对象
    cs1 = conn.cursor()

    # 执行SQL语句
    sql = """select * from goods;"""
    cs1.execute(sql)

    # 得到数据
    deta = cs1.fetchall()

    # 关闭
    cs1.close()
    conn.close()

    # 处理数据
    for temp in deta:
        print(temp)

MySQL储存原理

储存过程是一个可编程的函数，他在数据库中创建并保存。数据库的存储过程可以看做是对编程中面向对象方法的模拟
存储过程中优点：

• 存储过程 能实现较快的执行速度
• 存储过程允许标准组件是编程
• 存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
• 存储过程可被作为一种安全机制来充分利用
• 存储过程能减少网络流量

数据库优化查询

• 储存引擎选择：如果说数据表需要事务处理，应考虑使用InnoDB,因为他完全符合ACID特性，如果不需要事务处理，使用默认存储引擎MySAM是比较明智的
• 分表分库，主从
• 对查询进行优化，要尽量避免全表扫描，首先应考虑在where及order by 涉及的列上建立索引
• 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在where子句中使用！=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描
• update语句，如果只更改1、2个字段，不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

MySQL引擎

主要InnoDB和MYISAM

InnoDB	MYISAM
支持事务	不支持事务
适合频繁修改以及涉及到安全性较高的应用	适合查询以及插入为主的应用
支持外键	不支持外键
需要指定这个引擎	是默认引擎
必须包含只有该字段的索引	表中可以和其他字段一起建立联合索引
清空整个表时，说是一行一行删除，效率非常慢	会重建表

数据库优化

• 使用索引（B+、hash）
  应尽量避免全表扫描，首先考虑在order，group by涉及的列上建立索引
• 优化SQL语句
  1、避免使用select * from，用具体的字段代替*，不要返回任何用不到的字段
  2、不在索引列做运算或使用函数
  3、尽量使用limit减少返回的行数，减少数据传输时间和带宽浪费（单位时间内传输的最大的数据量）

• 优化数据库对象
  1、使用procedure analyse（）函数对表进行分析，该函数可以对表中列的数据类型提出优化建议，使用能真确的表示和存储数据的最短类型，这样可以减少对磁盘空间、内存、CPU缓存的使用
  2、设计表时严格按照设计范式来设计

• 使用缓存
  把经常访问到且不需要经常发生变化的数据放在缓存中，能节约磁盘IO

• 主从复制、读写分离、负载均衡
• 分布式

SQL注入是如何产生的如何防止

产生：程序开发过程中不注意规范书写SQL语句和对特殊字符进行过滤，导致客户端可以通过全局变量post和get提交一些SQL语句正常执行
防止办法：

1. 过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤
2. 参数化查询（是访问数据库时，在需要填入数值或数据的地方，使用参数 (Parameter) 来给值）

Redis

定义：Redis 是一个基于内存的高性能key-value数据库

优点：

• 读写性能优异- 内存型数据库,读写速度极快
• 支持数据持久化- 可以将内存数据保存到磁盘中, 并提供多种存储机制。
• 数据备份 – 支持主从模式 (master - slave) 的数据备份。
• 数据结构丰富：支持string（字符串）、hash（哈希）、set（集合）、zset（sortedset有序集合)、list（列表）
• 丰富的特性:可用于缓存，消息，按key设置过期时间，过期后将会自动删除
• 原子性 – Redis的所有操作都是原子性的，并且支持事务 (不支持回滚)。
• 集群 – 支持集群, 并实现了哨兵机制。
  丰富的特性 – 设置过期时间, 发布/订阅, 通知等机制。

缺点：

• Redis不具备自动容错和恢复功能，主机从机的宕机都会导致前端部分读写请求失败，需要等待机器重启或者手动切换前端的IP才能恢复
• Redis较难支持在线扩容在集群容量到达上线时在线扩容会变得很复杂。
• 修改配置文件，进行重启，将硬盘中的数据加载进内存，时间比较久。在这个过程中，redis不能提供服务。
• 由于是内存数据库，所以，单台机器，存储的数据量，跟机器本身的内存大小。虽然redis本身有key过期策略，但是还是需要提前预估和节约内存。如果内存增长过快，需要定期删除数据。

Redis应用场景
对速度有要求， 需要设置过期时间， 并且数据关系不复杂

• session存储 (性能更好, 方便设置过期时间)
• 购物车 (以请求令牌token作为key，记录当前用户购物车中的商品及其数量)
• 网页缓存 (将 请求的url/返回的网页内容 作为redis的key/value进行缓存)
• 实时排行 (利用zset的数据权重分析数据排行, 按排名展示数据)

为什么需要把所有数据放到内存中

Redis为了达到最快的读写速度将数据都读到内存中，并通过异步的方式将数据写入磁盘。所以redis具有快速和数据持久化的特征。如果不将数据放在内存中，磁盘I/O速度为严重影响redis的性能

Redis默认端口、存储时间、value最大长度

Redis默认端口：6379
默认过期时间：可以说用不过期，一般情况下，当配置中开启了超出最大内存限制就写磁盘的话，那么没有设置过期时间的key可能会被写到磁盘上。如果没设置，那么Redis将使用LRU机制，将内存中的老数据删除，并写入新数据
value：最多可以容纳的数据长度是：512M
Redis库：16个

4. String (重点)

• 5个命令
• set 添加/修改数据
• get 获取
• mset 添加多个数据
• mget 获取多个数据
• append 拼接数据

5. 键命令 (重点)

• 6个命令
  • del 删除数据
  • keys [过滤条件] 查询数据
  • type 获取数据类型
  • exists 判断数据是否存在
  • expire 设置过期时间
  • ttl 获取过期时间

6. hash

• 7个命令
  • hset 添加/修改数据
  • hget 获取数据
  • hmset 添加多个数据
  • hmget 获取多个数据
  • hdel 删除数据的某个键值对
  • hkeys 获取某个数据所有的键
  • hvals 获取某个数据所有的值

7. list

• 5个命令
  • lpush 追加数据
  • linsert 插入数据
  • lrange 获取列表数据
  • lrem 删除数据
  • lset 修改数据

8. zset 和 set

• zset 有序集合 可以设置权重比例， 会根据权重进行排序
  • 6个命令
    • zadd 添加数据
    • zrange 获取数据
    • zrangebyscore 获取指定权重范围的数据
    • zrem 删除数据
    • zremrangebyscore 删除指定权重范围数据
    • zscore 获取数据中某个值的权重
• set 无序集合 可以去重
  • 3个命令
    • sadd 添加数据
    • smembers 获取数据
    • srem 删除数据

三. Redis高级配置 (了解)

1. 数据库主从 (双机热备)

• 数据库主从： 用于进行数据库备份
• 主数据库
  • 主要负责增删改数据
• 从数据库
  • 主要负责对主数据库数据进行实时同步(备份主数据库)
  • 完成数据查询工作， 减轻主数据压力 （负载均衡）
  • 不能主动的增删改数据
• 配置从数据库
  • 设置slaveof参数
  • 设置自己的ip和端口port

2. 数据库集群

• 数据库集群： 当数据量过大， 一台计算机无法承载时， 需要配置数据库集群， 让多台计算机一起来分担数据保存的工作
• redis支持数据库集群， 至少3个主数据库， 3个从数据库
• redis会自动从集群中自动分配数据库来保存数据 （负载均衡）
• 配置
  • port
  • cluster-enabled yes
  • cluster-config-file nodes-7000.conf
  • cluster-node-timeout 15000
  • appendonly yes （开启aof机制， 每秒讲数据往硬盘中同步一次，为了避免集群数据迁移时发生丢失等情况）
• 访问集群 sudo redis-cli -p 7000 -c

ORM

• ORM框架: 对象关系映射, 可以让开发者以原生代码的方式来操作数据库
• 优点: 开发者不需要记忆SQL语句, 也不需要学习新的数据库语法
• 缺点: 存在一定的性能缺失

NoSQL和关系数据库的区别

• SQL数据库在特定结构的表中：而NoSQL则更加灵活和可拓展，存储方式可以省是JSON文档、哈希表或者其他方式
• SQL中可以使用JOIN表连接方式捋多个关系数据表中的数据用一条简单的查询语句查询出来，NOSQL暂未提供类似的JOIN的查询方式对多个数据集中的数据做查询。所以大部分NOSQL使用非规范化的数据存储方式存储数据

数据库操作

–01 mysql 数据库的操作

-- 链接数据库

mysql -uroot -pmysql

​

-- 不显示密码

-- 退出数据库
--quit/exit/ctrl + d

exit

-- sql语句最后需要有分号;结尾
-- 显示数据库版本 version
select version();

-- 显示时间 now
select now();

-- 查看当前使用的数据库
select database();

-- 查看所有数据库
show databases;

-- 创建数据库
-- create database 数据库名 charset=utf8;
create database python17 charset =utf8;

-- 查看创建数据库的语句
-- show create database 数据库名
show create database python17;

​

-- 使用数据库
-- use 数据库的名字
use python17;

-- 删除数据库
-- drop database 数据库名;
drop database python17;

–02 数据表的操作

-- 查看当前数据库中所有表
show tables;
show databases;

​

-- 创建表
-- int unsigned 无符号整形
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);

​

-- 查看表结构
-- desc 数据表的名字;
desc test;

​

-- 创建 classes 表(id、name)

create table classes (id int unsigned primary key auto_increment,name varchar(50) not null);

​
​

-- 创建 students 表(id、name、age、high (decimal)、
--gender (enum)、cls_id)

create table students(id int unsigned primary key auto_increment,name varchar(50) not null,age int unsigned,
high decimal(3,2),gender enum(“男”,“女”,“妖”,“保密”) default “保密”,cls_id int unsigned);

​

-- 查看表的创建语句
-- show create table 表名字;
show create table students;

-- 修改表-添加字段 mascot (吉祥物)
-- alter table 表名 add 列名 类型;
alter table classes add mascot varchar(30) not null;

-- 修改表-修改字段：不重命名版
-- alter table 表名 modify 列名 类型及约束;

alter table classes modify mascot varchar(50);

-- 修改表-修改字段：重命名版
-- alter table 表名 change 原名 新名 类型及约束;
alter table classes change mascot jxl int;

-- 修改表-删除字段
-- alter table 表名 drop 列名;

alter table classes drop jxl;

​

-- 删除表
-- drop table 表名;
-- drop database 数据库;
drop table students;
drop database python17;

​
–03 增删改查(curd)

-- 增加

±------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
±------±-----------------±-----±----±--------±---------------+
– 全列插入
– insert [into] 表名 values(…)
– 主键字段 可以用 0 null default 来占位
– 向classes表中插入 一个班级

    insert into 表(字段...) values(值...);

    insert into classes(id,name) values(0,"oldyang1");
        insert into classes(name) values("oldyang2");

    -- 向students表插入 一个学生信息

±-------±------------------------------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------------------------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(10) unsigned | YES | | NULL | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum(‘男’,‘女’,‘中性’,‘保密’) | YES | | 保密 | |
| cls_id | int(11) | YES | | NULL | |
； – 全部插入
insert into students(id,name,age,high,gender,cls_id) values(0,‘y3’,18,1.75,‘保密’,1);
– 部分插入
– insert into 表名(列1,…) values(值1,…)
insert into students (name) values (“yy3”);

    insert into students (name,gender) values("yy4",1);
    -- 多行插入
    insert into students (name) values("yy5"),("yy6");

​

-- 修改
-- update 表名 set 列1=值1,列2=值2... where 条件;
    -- 全部修改
    update 表名 set 字段=值 where条件;

    update students set name = "oldyang"; 
    
    -- 按条件修改
    update students set name = "yy1" where id = 1;
    
    -- 按条件修改多个值
    -- update students set gender ="",name = "xxx" where ;
    update students set name = "yy2",gender = 2 where id = 2;
    
-- 查询基本使用
    -- 查询所有列
    -- select * from 表名;
    select * from students;

    ---定条件查询    
    select * from students where id = 1;

    -- 查询指定列
    -- select 列1,列2,... from 表名;
    select name,gender from students ;

    -- 可以使用as为列或表指定别名
    -- select 字段[as 别名] , 字段[as 别名] from 数据表;
    select name as '姓名' from students;

    -- 字段的顺序
    select gender,name from students;

​

-- 删除
    -- 物理删除
    -- delete from 表名 where 条件
    delete from students where id = 1;

    -- 逻辑删除
    -- 用一个字段来表示 这条信息是否已经不能再使用了
    -- 给students表添加一个 is_delete 字段 bit 类型
    --alter table 表名 add 字段 类型 default 默认值;
    alter table students add is_delete bit default 0; # 0代表没有删除,1表示删除
    update students set is_delete = 1 where id = 2;

        # 写代码的时候
        select * from students where name = "yy2" and is_delete = 0;

​

-- 数据库备份与恢复(了解)
    -- mysqldump –uroot –p 数据库名 > python.sql;
    -- mysql -uroot –p 新数据库名 < python.sql; # 注意导入的时候需要先要创建数据库

    mysqldump -uroot -p python17 > python17.sql;
    mysql -uroot -p 新数据库名 < python17.sql;

； --数据库操作前的准备
– 创建数据库
– create database python_test_1 charset=utf8;

– 使用数据库
– use python_test_1;

– students表
– create table students(
– id int unsigned primary key auto_increment not null,
– name varchar(20) default ‘’,
– age tinyint unsigned default 0,
– height decimal(5,2),
– gender enum(‘男’,‘女’,‘中性’,‘保密’) default ‘保密’,
– cls_id int unsigned default 0,
– is_delete bit default 0
– );

– classes表
– create table classes (
– id int unsigned auto_increment primary key not null,
– name varchar(30) not null
– );

– 查询练习
– 查询所有字段
– select * from 表名;
select * from students;

-- 查询指定字段
-- select 列1,列2,... from 表名;
select name,age from students;

​

-- 使用 as 给字段起别名
-- select 字段 as 名字.... from 表名;
select name as "name2" from students;

​

-- select 表名.字段 .... from 表名;
select students.name ,students.age from students;

​
​

-- 可以通过 as 给表起别名
-- select 别名.字段 .... from 表名 as 别名;
select s.name from students as s;

​
​

-- 消除重复行(查性别)

-- distinct 字段 
select distinct gender from students;

# 分组后期使用比较多,功能强大
select gender from students group by gender;

​

– 条件查询 > < != >= <= and or not
– 比较运算符
– select … from 表名 where …
– >
– 查询年纪大于18岁的信息

    select * from students where age > 18;

    -- <
    -- 查询年纪小于18岁的信息
    select * from students where age < 18;

​

    -- >=
    -- <=
    -- 查询小于等于18岁的信息
    select * from students where age <= 18;
    -- =
    -- 查询年龄为18岁的所有学生的名字
    select * from students where age = 18;

    -- != 或者 <>
    -- 查询年龄不为18岁的所有学生的名字
    select * from students where age != 18;
    select * from students where age <> 18;

​

-- 逻辑运算符
    -- and
    -- 18和28之间的所以学生信息
    select * from students where age > 18 and age < 28;

    -- 18岁以上的女性
    select * from students where age > 18 and gender = "女";

    -- or
    -- 18以上或者身高高过180(包含)以上
    select * from students where age > 18 or height >= 180;

    -- not
    -- 不在 18岁以上的女性 这个范围内的信息
    -- select * from students where not (age>18 and gender=2);
    select * from students where not( age > 18 and gender = '女');
    select * from students where not age > 18 and gender = '女';

-- 模糊查询(where name like 要查询的数据)
    -- like 
    -- % 替换任意个
    -- _ 替换1个
    -- 查询姓名中 以 "小" 开始的名字
    select * from students where name like '小%';

    -- 查询姓名中 有 "小" 所有的名字
    select * from students where name like '%小%';

    -- 查询有2个字的名字
    select * from students where name like '__';

    -- 查询有3个字的名字
    select * from students where name like '___';

​

    -- 查询至少有2个字的名字
    select * from students where name like "__%";

-- 范围查询
    -- in (1, 3, 8)表示在一个非连续的范围内
    -- 查询 年龄为18或34的姓名
    select * from students where age = 18 or age = 34 or age = 12 or age = 56;
    select * from students where age in(18,34);

    -- not in 不非连续的范围之内
    -- 年龄不是 18或34岁的信息
    select * from students where age not in(18,34);

​

    -- between ... and ...表示在一个连续的范围内
    -- 查询 年龄在18到34之间的的信息
    select * from students where age > 18 and age < 34;

    select * from students where age between 18 and 34;

​

    -- not between ... and ...表示不在一个连续的范围内
    -- 查询 年龄不在18到34之间的的信息
    
    select * from students where age not  between 18 and 34;

-- 空判断
    -- 判空is null
    -- 查询身高为空的信息 
    select * from students where height is null;

    -- 判非空is not null
    select * from students where height is not null;

– 排序
– order by 字段
– asc
– asc从小到大排列，即升序
– desc
– desc从大到小排序，即降序
– 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序

select * from students where age between 18 and 34 and gender = '男' order by age asc;

​

-- 查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where age between 18 and 34 and gender = "女" order by height desc;

--;
) order by 多个字段
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序

select * from students where age between 18 and 34 and gender = “女” order by height desc,age asc;

-- 如果年龄也相同那么按照id从大到小排序
select * from students where age between 18 and 34 and gender = "女" order by height desc,age asc,id desc;

# 排序有优先级第一优先级最高

– 聚合函数

聚合函数不会把null计算进去

-- 总数
-- count
-- 查询男性有多少人
select count(*) from students;
select count(id) from students;

-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;

-- 查询女性的最高 身高
select max(height) from students where gender = "女";

-- 最小值
-- min
select min(height) from students where gender = "女";

​

-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;

​

-- 平均值
-- avg
-- 计算平均年龄
select avg(age) from students;

-- 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) from students;

-- 四舍五入 round(123.23 , 1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数

select round( avg(age),2) from students;

-- 计算男性的平均身高 保留2位小数
select round(avg(height),2) from students where gender = "男";

– 分组

-- group by
-- 按照性别分组,查询所有的性别
select 分组字段 from 表名  group by 分组字段;

select gender from students group by gender;

-- 计算每种性别中的人数
select gender,count(*) from students group by gender;

-- group_concat(...)
-- 查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;

-- 查询每组性别的平均年龄
select gender,avg(age) from students group by gender;

-- having(注意having和group by 连用 having后通常也要跟 聚合函数)
-- 查询平均年龄超过30岁的性别，以及姓名
select gender,avg(age),group_concat(name) from students group by gender having avg(age) > 30;

​

-- 查询每种性别中的人数多于2个的信息
select gender,count(*) from students group by gender having count(*) > 2;

-- with rollup 汇总的作用(了解)
--select gender,count(*) from students group by gender with rollup;

select gender,count(*) from students group by gender with rollup having count(*) > 2;

– 分页
– limit start, count
– limit 放在最后面(注意)

起始位置:(页数-1) * 每页的个数

-- 限制查询出来的数据个数
-- 查询前5个数据
select * from students limit 0,5;

-- 每页显示2个，第1个页面
select * from students limit 0,2;

-- 每页显示2个，第2个页面
select * from students limit 2,2;

-- 每页显示2个，第3个页面
select * from students limit 4,2;

-- 每页显示2个，第4个页面
select * from students limit 6,2;

​

-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
select * from students order by age asc limit 10,2;

​
​
​

– 连接查询
– inner join … on
– select … from 表A inner join 表B;
– 查询 有能够对应班级的学生以及班级信息

select * from students inner join classes on students.cls_id = classes.id;

select * from students inner join classes on students.cls_id = classes.id where gender = "女";

# 三个条件
select * from 表名 where # 数据的条件
select 分组字段 from  表名 group by 分组字段 having # 分组的条件
select * from 表A inner join 表B on 两张表连接的条件;

-- 按照要求显示姓名、班级
select students.name,classes.name from students inner join classes on students.cls_id = classes.id;

-- 给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.

select students.*,classes.name from students inner join classes on students.cls_id = classes.id;

-- 在以上的查询中，将班级名显示在第1列

-- 查询 有能够对应班级的学生以及班级信息, 按照班级名进行排序
select classes.name ,students.*from students inner join classes on students.cls_id = classes.id order by classes.name;

-- 当时同一个班级的时候，按照学生的id进行从小到大排序
select classes.name ,students.*from students inner join classes on students.cls_id = classes.id order by classes.name asc,students.id desc;

-- left join
-- 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;

-- 查询没有对应班级信息的学生
select * from students left join classes on students.cls_id = classes.id where classes.id is null;

​

-- right join   on
-- 将数据表名字互换位置，用left join完成

select * from classes right join students on students.cls_id = classes.id;

​

– 子查询
– 标量子查询: 子查询返回的结果是一个数据(一行一列)
– 列子查询: 返回的结果是一列(一列多行)
– 行子查询: 返回的结果是一行(一行多列)

-- 查询出高于平均身高的信息(height)
# 第一步应该要知道平均身高是多少
select avg(height) from students;
# 第二步进行判断
select * from students where height >"172.076923";

select * from students where height >(select avg(height) from students);

-- 查询学生的班级号能够对应的 学生名字
# 第一步查询有多少班级号
select id from classes;
# 查询学生是这些班级
select * from students where cls_id in(1,2);

select * from students where cls_id in(select id from classes);

​

--数据操作前的准备
--创建数据库表
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
--从sql文件中导入数据
-- source 具体地址/areas.sql;

SOURCE areas.SQL;

--查询一共有多少个省
select * from areas where pid is null;

--例1：查询省的名称为“山西省”的所有城市
select aid from areas where atitle = "山西省";
select * from areas where pid = (select aid from areas where atitle = "山西省");

select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle = "山西省";

--例2：查询市的名称为“广州市”的所有区县
select * from areas where pid = (select aid from areas where atitle = "广州市");
    select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle = "广州市";

​

 -- sql强化演练( goods 表练习)

– 查询类型 cate_name 为 ‘超级本’ 的商品名称 name 、价格 price

select name,price from goods where cate_name = “超级本”;

– 显示商品的种类
– 1 分组的方式( group by )
select cate_name from goods group by cate_name;

– 2 去重的方法( distinct )
select distinct cate_name from goods;

– 求所有电脑产品的平均价格 avg ,并且保留两位小数( round )

select round( avg(price),2) from goods;

– 显示 每种类型 cate_name (由此可知需要分组)的 平均价格
select cate_name,avg(price) from goods group by cate_name;

– 查询 每种类型 的商品中 最贵 max 、最便宜 min 、平均价 avg 、数量 count

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

– 查询所有价格大于 平均价格 的商品，并且按 价格降序 排序 order desc
–1.平均价格
select avg(price) from goods;

–2.大于平均价格
select * from goods where price > (select avg(price) from goods) order by price desc;

– 查询每种类型中最贵的电脑信息(难)

– 1 查找 每种类型 中 最贵的 max_price 价格
select cate_name,max(price) from goods group by cate_name;

– 2 关联查询
select * from goods inner join (select cate_name,max(price) as price_max from goods group by cate_name) as goods_max
on goods.cate_name = goods_max.cate_name and goods.price = goods_max.price_max;

–第四部 修改表结构(重点是思路)

– 创建表格的格式
–create table if not exists 表名 (
–id int unsigned primary key auto_increment, 这个是主键
–name varchar(40) not null); 这个名称

–第一步 创建 “商品种类表” -goods_cates
–id跟名称

create table goods_cates(id int unsigned primary key auto_increment,name varchar(50) not null);

–第二步 创建 “商品品牌表” -goods_brands
–id跟名称

create table goods_brands(id int unsigned primary key auto_increment,name varchar(50) not null);

–第三步 更新种类信息表
–1. 要把类型查询出来

select cate_name from goods group by cate_name;

–2. 批量插入数据
–insert into goods (name) values(‘123’);
insert into goods(name) values(“123”); # 单条确定数据的插入

批量插入

insert into goods_cates(name) (select 批量的数据写到这里);

insert into goods_cates(name) (select cate_name from goods group by cate_name);

–第四 更新品牌信息表
–1. 要把品牌信息查询出来
select brand_name from goods group by brand_name;

–2. 批量插入数据
insert into goods_brands(name) (select brand_name from goods group by brand_name);

–第五 更新种类信息成种类信息表中的id
–1. 种类信息跟要替换成表进行内联
select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;

–2. 通过特殊的update 进行更新

如果是查询出来的表必须把 select * from去掉

update (goods inner join goods_cates on goods.cate_name = goods_cates.name ) set goods.cate_name = goods_cates.id;

–第六 更新品牌信息成品牌信息表的id
–1. 把要更新的表两张表进行内联
select * from goods inner join goods_brands on goods.brand_name = goods_brands.name;

–2.通过特殊的update语句进行更新
update ( goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;

–第七步 修改字段(注意类型必须跟外键的主键类型一致)
– alter table 表名 change 旧字段 新字段 类型
alter table goods change cate_name cate_id int unsigned;
alter table goods change brand_name brand_id int unsigned;

– 外键的使用(了解)

– 向goods表里插入任意一条品牌数据"老王牌拖拉机"
– 你会发现我们外键不能创建,因为没有对应的品牌

– 约束 数据的插入 使用 外键 foreign key
– alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);

alter table goods add foreign key(brand_id) references goods_brands(id);

– 创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)
– foreign key (主表的外键) references 外键表(主键)
create table if not exists goods_key(
id int primary key auto_increment not null,
name varchar(40) default ‘’,
price decimal(5,2),
cate_id int unsigned,
brand_id int unsigned,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(brand_id) references goods_brands(id)

);

– 如何取消外键约束
– 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
–show create table goods;
show create table goods;

– 获取名称之后就可以根据名称来删除外键约束
–alter table students drop foreign key students_ibfk_1;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop key brand_id;

总结:在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

– 事物(ACID)

开启事务

begin;
或者
start transaction;

-- 原子性 一致性
    第一步 打开 终端1 终端2
    第二步 终端1 打开事物 begin/start transaction
           终端1 update 表名 set 字段="xxx" where ...;
           终端1 select * from 表名;  发现数据改变
    第三步 终端2 select * from 表名;  
           发现数据其实并没有改变 其实这个时候对数据的相关操作信息存在缓存中,
           当commit之后,这些操作才会一次性的完成
    第四步 终端1 commit 数据数数据真的改变
           终端2 select * from 表名,数据改变了
        
-- 隔离性
    第一步 打开 终端1 终端2
    第二步 终端1 打开事物 begin
           终端1 update 表名 set 字段="xxx" where ...;
    第三步 终端2 update 表名 set 字段="yyy" where ...;
           发现 处于阻塞状态 
    第四步 终端1 commit
           终端2 阻塞状态解除 数据修改成 yyy
           
-- 回滚(rollback)
    第一步 打开 终端1 begin
    第二步 终端1 update 表名 set 字段="xxx" where ...;
    第三步 rollback 数据返回最开始的原始值

​
​

-- 持久性
    -- 一旦事务提交，则其所做的修改会永久保存到数据库
    
-- 注意 
    -- innodb能使用事物
    -- 使用python操作数据库的时候 默认开启事物的 
    -- 但是python对数据库进行增删改的时候 需要手动commit
    
    -- 使用终端操作数据库(也就是mysql的客户端)的时候 也是默认开始事物的
    -- 只是在回车确认操作的时候 终端会默认的commit 所以我们不需要commit

​
– 事物最主要解决的问题
– 某些事情需要一次性完成 中途不允许出现中断 例如银行取钱 事物可以解决这种问题

– 什么是视图?
– 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
– 所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

– 视图的特点
– 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，
– 不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

– 视图的最主要的作用
– 如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，
– 则需要在多个地方进行修改，维护起来非常麻烦,这个时候使用视图就可解决这个问题

– 视图的定义方式
– create view 视图名称(一般使用v开头) as select语句;
select goods.name,goods.price,goods_cates.name as cate_name from goods inner join goods_cates on goods.cate_id = goods_cates.id;

–具体场景使用
create view v_python17 as select goods.name2 as name,goods.price,goods_cates.name as cate_name from goods inner join goods_cates on goods.cate_id = goods_cates.id;

    v_python17 = select goods.name,goods.price,goods_cates.name as cate_name from goods inner join goods_cates on goods.cate_id = goods_cates.id;

– 解决数据库发生改变 python程序也需要改变的问题
–复杂的sql可以使用视图代替

– 删除视图
– drop view ;
drop view v_goods;

– 注意
– 视图只能用于查询

– 视图作用总结

– 1 提高了重用性，就像一个函数
– 2 对数据库重构，却不影响已经编写好的程序运行
– 3 让数据更加清爽

– 视图最主要解决的问题
– 程序对数据库操作,一旦数据库发生变化,程序需要修改,这时如果使用视图就可以解决这个问题

​
– 索引
– 注意
– 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。
– 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，
– 对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

-- 建立索引会占用磁盘空间

– 索引最主要解决的问题
– 当数据非常庞大时,并且这些数据不需要经常修改,为了加快查询速度,我们会使用索引

– 创建一张表
create table test_index(title varchar(10));
– python插入10条数据

–测试步骤
1. 开启运行时间监测：
set profiling=1;
2. 查找第1万条数据ha-99999
select * from test_index where title=‘ha-99999’;
3. 查看执行的时间：
show profiles;
4. 为表title_index的title列创建索引：
create index 索引名称 on 表名(字段名称)

        create index my_index on test_index(title);
5. 执行查询语句：
    select * from test_index where title='ha-99999';
6. 再次查看执行的时间
    show profiles;

--查看索引
    -- show index from 表名;
    show index from test_index;

--删除索引

    ---drop index 索引名 on 表名;
    drop index my_index on test_index;

​
​
​
​
– 权限管理(了解) 对用户的管理

-- 查看有哪些账户
    1 使用root账户登录
    2 使用mysql数据库
    3 用户的信息存放在 user 表中 
        -- select host,user,authentication_string from user;
            Host表示允许访问的主机 % localhost
            User表示用户名
            authentication_string表示密码，为加密后的值
        select host,user,authentication_string from user;
            
-- 创建账户、授权 
    
    -- 案例一
    1 使用root账户登录
    
    2 创建账户并授予所有权限(部分权限)
    -- grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';(语法格式)
    grant select on jing_dong.* to 'python17'@'localhost' identified by '123';

​

        -- 注意
        -- 1 访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
        -- 2 访问主机可以设置成 localhost 或具体的ip，表示只允许本机或特定主机访问
        
    -- 查看用户有哪些权限
    -- show grants for 用户@访问主机;
    show grants for python17@'localhost';

​

    3 退出root的登录 使用新账户登录
    -- 使用查询操作是可以的,(插入也可以)
    
    -- 使用其他操作是不可以的

​
​

    -- 案例二
    1 使用root账户登录
    
    2 创建账户并授予所有权限(所有权限) all privileges
    --grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';(语法格式)
    -- 注意 访问链接设置成 % 十分危险 不要使用
        grant all privileges on jing_dong.* to 'python17'@'%' identified by '222';

            % 可以远程 localhost只能本地 以后尽量不要开远程
            
    -- drop user 删除
    -- grant 授权
    
-- 修改权限(增加)
    1 使用root账户登录
    
    2 修改用户权限
    -- grant 权限名称 on 数据库 to 账户@主机 with grant option;(语法格式)
    grant update on jing_dong.* to 'python17'@'localhost' with grant option;
    
    3 刷新权限
        flush privileges;
        
-- 修改密码
    1 使用root账户登录
    
    2 选择mysql数据库
    
    3 使用password()函数进行密码加密 对user表进行修改
    -- update user set authentication_string=password('新密码') where user='用户名';(语法格式)
    
    例:update user set authentication_string=password('333') where user='python17';

    4 刷新权限
        flush privileges;
    
-- 删除用户
    1 使用root账户登录
    
    2 删除用户
        第一种方式  drop user '用户名'@'主机';(语法格式)  卸载
        
                    例:drop user 'python17'@'%';

​

        第二种方式   delete from user where user='用户名';(语法格式)  手动删除
        
                    例:delete from user where user='python17';
                    
                    -- 操作结束之后需要刷新权限
                    flush privileges
                    
    -- 推荐使用语法1删除用户, 如果使用语法1删除失败，采用语法2方式

​

-- 远程登录(谨慎使用)

​
​

​
​