MYSQL数据库学习

术语

mysql数据类型

1. 整型类型

   • 数值、日期/时间和字符串

2. 浮点型

   1. float：单精度型，只能保证6位有效数字的准确性
   2. double：双精度型，只能保证16位有效数字的准确性
   3. decimal：定点数，其中decimal(5,2)，代表共5位数字，其中2位是小数

3. 字符串

   1. char：定长字符串，指的是在创建表示，char字符占用硬盘空间大小已经固定

   2. varchat：变长字符串，指的是字段占用硬盘空间大小并不是固定的，而是根据内容确定。等于内容的长度+1个字节(字符串结束"\0")

   3. text:text不可以有默认值，其长度最大的是2的16次方-1

      选择字符串类型的原则

       1. varchar：适用于经常变化的字段    
       2. char：适用于知道固定长度的字符串，尽量用vrarchar     
       3. 超过255字节的只用varchar或者text，能用varchar的地方不用text

   4. 枚举类型

      枚举类型enum，在定义字段是预先固定好的几个值，然后插入记录是的值只能在这几个固定好的值中选择一个

      语法定义：

      gender enum()
      # 应用场景：性别，星期，月份，表示状态的(是,否)

   5. 时间类型

      • datetime 保存时间的范围: '1000-01-01 00:00:00'到'9999-12-31 23:59:59'
      • timestamp 保存时间范围: '1970-01-01 00:00:01'到'2038-1-19 03:14:07'

• 数据完整性：保证数据的正确性

• 约束：保证数据的完整性和一致性

  约束类型	约束学说明
  NOT NULL	非空约束(该字段不能为空)
  PRIMARY KEY	主键约束(唯一性，非空性)
  UNIQUE KEY	唯一约束(唯一性，可以为空，但只有一个)
  DEFAULT	默认约束(该数据类型默认约束)
  FOREIGN KEY	外键约束(需要建立两表的联系)

建表的三范式

1. 任何表都必须存在主键，且每个字段的原子性不可再分

2. 所有的非主键字段，完全依赖主键，不能产生部分依赖。否则容易产生冗余数据

3. 所有的非主键字段直接依赖主键，不能产生传递依赖

数据库操作

#查:
    select databases(); #查看当前使用的数据库
    show databases; #查看所有数据库
    show create databases 数据库名; #查看创建数据库的语句
#增:
    create databases 表名; #创建数据库
#删:
    drop databases 数据库名;

use 数据库的名字; #使用数据库

表数据操作

#表结构查看命令:
    show tables; #查看当前数据库所有表
    desc 表名; #查看表结构
    show create table 表名; #查看表的创建语句---详细过程
#创建表:
    create table 表名(字段 类型 约束,...)     
#表结构修改命令:
    alter table 表名 add 列名 类型; #添加字段
    alter table 表名 change 原名 新名 类型及约束; #重命名字段
    alter table 表名 modify 列名 类型; #修改字段类型
    alter table 表名 drop 列名; #删除字段
    drop table 表名; #删除表
#添加数据:
    insert into 表名 values(...); #全列插入
    #注意: 主键字段可以使用0，null，default来占位实现自增
    insert into 表名(列1,...)values(值1,...); #部分插入
    insert into 表名 values(...),(...); #一次性插入多行数据
    insert into 表名(列1,...)values(值1,...),(值2,...)...; #部分列多行插入
#查询修改数据
select * from 表名; #查询所有列数据
select 列1,列2... from 表名; #查询指定列数据
update 表名 set 列1=值1,列2=值2,... where 条件; #修改数据
    #注意:查询数据时，如果字段名或者表名过长可通过as起别名的方式
          select 列名 as 别名 from 表名 as 别名
#删除数据:
delete from 表名 where 条件; #删除数据

• 自增实现方法 !(不建议使用原因):

  修改主键类型时，增加约束。使用auto_increment方法
       语法: alter table 表名 modify 主键字段 数据类型 auto_increment;

查询语句

as #可以给字段起别名;可以给表起别名(用来起风相同字段的不同表)
select distinct;#可以去重查询

where之比较运算查询

• 使用where子句对表中的数据筛选，结果为true的记录会出现在结果集中。

  • 支持多种运算符，进行条件的处理:

    • 比较运算符:

      等于：=

      大于：>

      大于等于：>=

      小于：<

      小于等于:<=

      不等于：!= 或 < >

      • 逻辑运算符：

        and：表示多个条件时，多个条件必须同时成立(值为Ture)

        or：表示多个条件时，满足任意一个条件时成立

        not：表示反取操作

    • 模糊查询：

      模糊查询关键字：like

      llike后跟：%表示任意多个任意字符，_表示任意字符

    • 范围查询：

      between-and：连续范围查询

      注意：between A and B在匹配数据的时候匹配范围空间是[A,B]

      in：非范围查询

    • 空值判断：

      判断为空：is null

      注意：null与 '' 是不同的

      判断非空：is not null

      注意：顺序不能错

聚合函数

​ count 计算总行数

​ max 求此字段最大值

​ min 求此字段最小值

​ sum 求此字段和

​ avg 求此字段平均值

​ round：四舍五入，保留小数

order排序查询

​ order排序查询语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
#说明：
#1、将行数据按照列1进行排序，如果某些行列1的值相同，则按照列2排序，以此类推
#2、asc是从小到大排序，即升序
#3、desc从大到小排序，即倒叙
#4、默认按照列值从小到大排列（即asc关键字）
#注意：
order by可以跟多个分组条件，中间用英文逗号分隔

group by分组查询

​ group by的含义：将查询结果按照1个或者多个字段进行分组，字段相同的为一组

​ group by可以用于单个字段分组，也可用于多个字段分组

​ group by + group concat(...)：放置每个分组中某字段的集合

​ group by + 聚合函数：

​ 聚合函数和group by结合使用的时候，统计的对象是每个分 组

​ group by + having：

​ having作用和where类似，但是having只能用于group by对分 组的每组数据过滤，而where是用来过滤表数据

​ group by + with rollup

​ with rollup 在数据表最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果

limit限制查询(分页查询)

​ limit限制取出记录数量，但limit要写在sql语句的最后

​ 语法：

​ limit 起始记录 记录数

​ 说明：

​ 起始记录是指从第几条数据开始取，第一条记录的下标是0

​ 记录数是从起始记录开始向后依次取的记录数

连接查询

内连接

• 内链接:两张或者多张表中按给定的连接条件查询满足条件的结果

• 语法: select 查询内容

• from table1,table2 where 关联条件**

• 外连接的主要特点：主表中的数据无条件的全部查询出来。

• 注意: inner join 关键词中的 inner 可以省略

  #表一和表二连接查询
  select * from students inner join classes;
  
  #查询有能够对应班级的学生以及班级信息
  
  select * from students inner join classes on students.cls_id = classes.id;
  
  #显示姓名，班级
  select s.name,c.name from students s inner join classes c on s.cls_id = c.id;
  

外连接

​ 外连接:不仅要查询两张或者多张表中满足条件的记录，还要查询某张表中不满足连接条件的记录

​ 语法: select 查询内容 from table1 left|right|join table2 on 连接条件

左连接

• 左外连接（左连接）：是指在两个表进行连接时，返回左表的全部记录及右表中符合条件的记录，右表没有匹配的记录用 null 补全

• 语法：select * from table1 t1 left join table2 t2 on(t1.column1 = t2.column2)

  #查询，有能够对应班级的学生和班级信息-左连接
  select s.name,c.name from students s left join classes c on s.cls_id = c.id where c.name is not null;

右连接

• 右连接刚好和左连接相反，返回右表的全部记录及左表中符合条件的记录，左表没有匹配的记录用 null 补全

• 语法：select * from table1 t1 right join table2 t2 on(t1.column1 = t2.column2);

  #查询，有能够对应班级的学生和班级信息-右连接
  select s.name,c.name from classes c right join students s on c.id = s.cls_id where c.name is not null;

自连接

• 关联的两张表是同一张表

笛卡尔积

• 笛卡尔积也叫交叉连接，原理就是一张表中的每一条记录都要和另一张表中的所有记录进行连接。如果两张表分别有 n 和 m 条记录，进行笛卡尔积的结果有 n*m 条记录。

• 笛卡尔积使用 join 来连接，基本语法如下：

  select * from table1 join table2;
  #警告：如果两个表关联查询，没有设置查询条件这返回两个表格的条目乘积，虽然sql语句不会报错但是返回的数据没有意义

• 子查询：把一个查询的结果，当做另一个查询条件

• 子查询分为三类：

  1、标量子查询：子查询返回的结果是一个数据(一行一列)

  2、列子查询：返回的结果是一列(一列多行)

  3、行子查询：返回的结果是一行(一行多列)

自连接

• 自连接是一种特殊的表连接，它是指相互连接的表在物理上同为一张表，但是逻辑上是多张表。自连接通常用于表中的数据有层次结构，如区域表、菜单表、商品分类表等。

• 语法：

  select A.column,B.column

  from table A,table B

  where A.column = B.column;

  #查询省的名称为陕西省的所有城市
  select city.* from areas city inner join areas province on city.pid = province.aid where province.atitle = '陕西省';
  
  #or也是可以起到过滤的作用，加上and拼接过滤陕西省的数据
  select city.* from areas city inner join areas province on city.pid = province.aid and province.atitle = '陕西省';

MYSQL高级进阶

• 将查询的数据直接插入表中

  insert into 表名(字段名) select cate_name from goods group by cate_name;

• 连表更新

  update (表1 join 表2 on 连接条件)set 某表字段值=值;

外键的使用

• 作用：防止无效信息插入

• 注意：

  • 关键字foreign key 只有 innodb数据库引擎

  • 外键本身也是一种约束和 not null 一样

删除外键：

#取消外键约束
#1、获取外键的名称,该名称系统自动生成，可以通过创建表语句查询
show create table 表名;
#2、删除外键约束
alter table 表名 drop foreign key 外键名;

注意：使用到外键会极大的降低表的更新笑了，所以在追去读写效率的场景下很少使用外键

视图

• 视图就是一个能够把复杂的sql语句的功能封装起来的一个虚表
• 视图不存储具体的数据，基本表发生改变，视图也会跟着改变
• 视图方便操作，特别是查询操作，减少复杂的sql语句，增强可读性，复用性；

#创建视图
create view 视图名称(一般使用v开头) as select语句;
#查看视图
show tables;
#使用视图
select * from 视图名称;
#删除视图
drop view 视图名称;

事物

• 事物Transaction，是指作为一个基本工作单元执行的一系列sql语句的操作，要么完全执行，要么完全不执行

事物的四大特性(ACID)：

• 原子性(Atomicity)

  一个书屋必须被视为一个不可分割的最小工作单元，整个事物中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事物来说，不可能执行其中一部分操作，这是事物的原子性

• 一致性(Corsistency)

  数据总是从一个一致性的状态转换到另一个一致性的状态

• 隔离性(Isolation)

  一个事物所做的修改在最终提交前，对其他事物是无可见的

• 持久性(Durability)

  一旦事物提交，则其所作的修改会永久保存到数据库

事物的使用

• 开启事物

  开启事物后执行修改命令，变更会委会到本地缓存中，而不是维护到物理表中

  begin 或者start transaction

• 提交事物

  将缓存中的数据变更维护到物理表中

  commit

• 回滚事物

  放弃缓存中变更的数据，表示事物执行失败，应该回到开始事物前的状态

  rollbact

  注意：innodb能使用事物

索引

• 索引可以理解为字典的目录
• 索引的本质是一种特殊的文件(innodb数据表上的索引是表空间的一个组成部分，他们包含着对数据表里所有记录的位置信息)

索引的使用

• 查看表中已有的索引

show index from 表名;

• 创建索引

alter table 表名 add index 索引名[可选]（字段名,...）

• 使用索引

select * from 表名 force index(索引名) where title = 值;

• 删除索引

drop index 索引名称 on 表名；

• 优点

  加快数据查询速度

• 缺点

  创建索引会浪费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间会越来越多

• 使用规则

  1、经常发生数据更新的表避免使用过多的索引

  2、数据量晓得表没有必要使用索引

  3、数据量较大同时不会频繁发生数据更改的表可以使用索引

E-R模型

• E - R模型即实体-关系模型
• E - R 模型是描述数据库存储数据的结构模型

SQL注入

• 用户提交带有恶意的数据与sql语句进行字符串方式拼接，从而影响SQL语句的语义，最终产生数据泄露的现象
• 防止sql注入，使用sql语句参数化

#1、构造列表参数
lists = [find_name]
#2、执行select语句
sql = "select * from students where name = %s;"
#  %s不需要加''
count = cst.execute(sql,list)