MySQL

1.环境变量

  • python ----> python.exe

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

2.mysql安装、启动、关闭

  • mysql install 安装mysql服务 mysql服务就被注册到操作系统
  • net start mysql 启动mysql服务
  • net stop mysql
  • mysql -uroot -proot123 -h192.168.14.12 启动客户端连接server

3.mysql命令

  • select user(); 查看当前登录的用户
  • set password = password('123')；给当前用户设置密码
  • create user 'guest'@'192.168.14.%' identified by '123'；创建一个其他用户
  • grant 权限类型 给一个用户授权
    • grant all on 库.* to 'guest'@'192.168.14.%' identified by '123' ；
    • grant select；
    • grant select,insert；
  • flush privileges; 刷新使授权立即生效
  • show databases;查看所有数据库
  • create database 数据库名; 创建一个数据库
  • use 数据库名；切换到数据库
  • drop database 数据库名；删除数据库
  • show tables；查看库下的表
  • create table 表名(字段名 数据类型（长度），)；
  • desc 表名； 查看表结构 describe 表名；
  • 操作数据（增删改查）DML语句
    • insert into 表名 values(一行数据)，(一行数据）; 插入数据
    • select * from 表名；查询数据
    • update 表名 set 字段名=值，字段名=值 where 条件; 修改数据
    • delete from 表名 where 条件；删除数据

4.存储引擎—存储数据的方式

  • 数据都是存储在硬盘上，即数据持久化存储

    • InnoDB存储引擎：数据和索引存储在一起（2个文件）——数据索引/表结构

      • 数据持久化
      • 支持事务：为了保证数据的完整性，将多个操作变成原子性操作（数据安全）
      • 支持行级锁：修改的行少的时候使用（频繁修改数据）
      • 支持表级锁：批量修改多行时使用（大量数据修改）
      • 支持外键：约束两张表的关联字段、不能随意的添加删除（降低数据增删改的出错率）

    • MyISAM存储引擎：数据和索引不存储在一起（3个文件）——数据/索引/表结构

  • 数据存储在内存中，即数据在断电后消失

    • MEMORY存储引擎（1个文件）——表结构

  • 存储引擎相关sql语句

    • 查看当前默认存储引擎 show variables like '%engine%';
    • 查看当前数据库支持的存储引擎show engines；
    • 指定create table engine1(id int,name char(18)) engine = Myisam;

5.表

  • 创建表

    create table 表名（

    字段名 类型[(宽度) 约束条件]

    id int,

    name char(18),

    );

  • 删除表

    drop table 表名；

  • 写入数据

    insert into 表 （字段1，字段2） values（值1，值2）

  • 查看表结构

    • desc 表名 ；

      能查看有多少个字段、类型、长度，看不到表编码、引擎、具体的约束信息只能看到一部分

    • show create table 表名；

      能查看字段、类型、长度、编码

      、引擎、约束

6.数据类型

6.1数字

  • unsigned 无符号

  • tinyint-1字节

  • smallint-2字节

  • mediumint-3字节

  • int、integer-4字节 不约束长度、最多表示10位

  • bigint-8字节

  • float-4字节，单精度 float(6,2)-->保留两位小数

  • double-8字节，双精度

  • decimal-默认10位整数，可表示到小数点后32位

6.2日期

  • DATE(3字节)

    YYYY-MM-DD

  • TIME(3字节)

    HH:MM:SS

  • YEAR(1字节)

    YYYY

  • DATETIME(8字节)

    YYYY-MM-DD HH:MM:SS

  • TIMESTAMP 时间戳

  • 约束：NOT NULL、DEFAULT CURRENT_TIMESTAMP、ON UPDATE CURRENT_TIMESTAMP

6.3字符串
  • char
    • 最多只能表示255个字符
    • 定长存储、浪费空间、节省时间
    • 身份证号、手机号码、username、password
  • varchar
    • 最多能表示65535个字符
    • 变长存储、节省空间、但存取速度慢
    • 评论、朋友圈、微博
6.4enum/set
  • enum单选
    • gender enum('male','female')
  • set多选
    • hobby set(’抽烟‘,'喝酒','烫头')

7.完整性约束

  • 无符号 int unsigned

  • 不为空 NOT NULL

  • 默认值 default

  • 值不重复 unique （除NULL）

    • 联合唯一 unique (字段1，字段2)

  • 自增 auto_increment

  • 主键—非空+唯一约束

    • primary key(字段1，字段2)

  • 外键

    • foreign key (字段) references 外表（外表字段）
    • 外表字段必须至少是“唯一”的

8.修改表结构

  • 修改表名

    alter table 表名 rename 新表名；

  • 增加字段

    alter table 表名 add 字段名 数据类型 [完整性约束条件]；

  • 删除字段

    alter table 表名 drop 字段名；

  • 修改字段

    alter table 表名 modify 字段名 数据类型 [完整性约束条件]；

    alter table 表名 change 旧字段名 新字段名 数据类型 [完整性约束条件]；

9.多表结构的创建

表与表之间的关系

  • 一对多 （班级、学生）foreign key
  • 多对多 （作者、书籍）foreign key +一张新表
  • 一对一 foreign key+unique

10.数据的删除

  • 清空表

    delete from 表：会清空表，但不会清空自增字段的offset(偏移量)值

    truncate table 表：会清空表和自增字段的偏移量

  • 删除某一条数据

    delete from 表 where 条件；

11.单表数据查询

  • selcect

    • select * from 表；
    • select 字段... from 表；
    • select 字段 as 新名 from 表；
    • select 字段 新名 from 表；
    • select distinct 字段 from 表；
    • select concat(字段,':',字段) from 表；连接字符串
    • select concat_ws('|','pork','eat'); 第一个参数为分隔符
    • CASE 语句 CASE WHEN...THEN ELSE...THEN 相当于if判断句

  • where

    • 比较运算符 > < >= <= <> !=

    • 范围

      • between ...and ...
      • in

    • 模糊匹配

      • like %通配符 _通配符
      • regexp ^ $

    • 逻辑运算

      not、and、or

    • 不能用select字段的重命名

  • group by

    • 聚合函数
      • count()
      • max()
      • min()
      • sum()
      • avg()
    • group_concat()

  • having过滤语句

    • 在having条件中可以使用聚合函数，在where中不行
    • 适合筛选符合条件的某一组数据
    • 先分组再过滤

  • order by

    • 默认是升序 asc
    • 降序 desc

  • limit m,n

    • 从m+1想开始，取n项
    • m不写，默认为0
    • limit n offset m

12.多表查询

  • 表与表之间的连接方式

    • 内连接

      • select * from 表1，表2 where 条件

      • select * from 表1 inner join 表2 on 条件

      • select * from 表1as 别名 inner join 表2 as 别名 on 条件

    • 外连接

      • 左外连接left join...on...
      • 右外连接right join...on...
      • 全外连接full join用左外连接union右外连接实现

  • 子查询

13.索引

  • b+树

    • 平衡树（btree--balance tree)能够让查找某一个值经历的查找速度尽量平衡
    • 分支结点不存储数据—让树的高度尽量矮，让查找一个数据的效率尽量的稳定
    • 在所有叶子结点之间加入双向的地址链接—查找非常快

  • mysql中存储数据的两种方式

    • 聚集（簇）索引—全表数据都存储在叶子结点上、Innodb 必且仅有一个主键
    • 非聚集（簇）索引—辅助索引、叶子结点不存放具体的整行数据、只存储这一行的主键的值

  • 索引的创建与删除

    • 创建主键primary key 聚集索引+非空+唯一
    • 创建唯一约束unique 辅助索引+唯一
    • 添加普通索引
      • create index 索引名 on 表名（字段）
      • drop index 索引名 on 表名

  • 正确使用索引

    • 查询的字段不是索引字段

    • 对哪一个字段创建了索引，就用哪个字段做条件查询

    • 对区分度大的列创建索引，1/10以下的重复率比较适合创建索引

    • 范围越大越慢，like 'a%'快

    • 条件列不能参与计算、使用函数

    • and和or

      • and连接，一列含有索引都可加快查找速度
      • or连接，都含有索引才可以加快查找速度

    • 联合索引：最左前缀原则（必须带着最左边的列做条件，若出现范围，整条索引失效）

    • 条件中的数据类型和实际字段的类型必须一致

    • select字段中应该包含order by中的字段

  • 覆盖索引：查询过程中不需要回表

  • 索引合并：分别创建的两个索引在某一次查询中临时合并成一条索引

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

  • 慢查询优化

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

  • mysql的慢日志

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

14.正确的使用mysql数据库

  • 从库的角度
    • 搭建集群
    • 读写分离
    • 分库
  • 从表的角度
    • 合理安排表与表之间的关系
    • 把固定长度的字段放在前面
    • 尽量使用char而不是varchar
  • 从操作数据的角度
    • 尽量在where字段就约束数值到一个比较小的范围
    • 尽量使用连表查询
    • 删除数据和修改数据的时候尽量使用主键
    • 合理的创建和使用索引
      • 创建索引
        • 选择区分度比较大的列
        • 尽量选择短的字段创建索引
        • 不要创建不必要的索引，及时删除不用的索引
      • 使用索引

15.pymysql模块

  • python操作mysql数据库

    • 连接数据库
    • 获取游标
    • 执行sql(增删改查)
    • 若修改，则需要进行提交
    • 关闭游标、关闭连接
    import pymysql
conn = pymysql.connect(host="127.0.0.1", user="root", password="root123", database="call")
cur = conn.cursor() #cursor游标
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor) #查询返回字典
#查询
try:
    cur.execute('select * from callinfo;')
    # ret1 = cur.fetchone()#获取一条结果
    # print(ret1)
    # ret2 = cur.fetchmany(3)#获取多条结果
    # print(ret2)
    # ret3 = cur.fetchall()#获取全部结果
    # print(ret3)
    # print(cur.rowcount)
    for i in range(cur.rowcount):
        ret = cur.fetchone()
        print(ret)
except pymysql.err.ProgrammingError as e:
    print(e)

#增加 删除 修改
# try:
#     cur.execute('insert into callinfo values("7","小明","上海市","18090820823","780280","19091309@qq.com","8021818139")')
#     cur.execute('update callinfo set cname="小南" where cid=2')
#     cur.execute('delete from callinfo where cname="小明"')
#     conn.commit()
# except Exception as e:
#     print(e)
#     conn.rollback()
cur.close()
conn.close()

  • sql注入

    • 传参数，通过execute方法
    • execute(sql语句）

    结合数据库、python写登录：

    import pymysql
user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="127.0.0.1", user="root", password="root123", database="homework")
# sql = "select * from info where user='%s' and password='%s';"%(user,pwd)
sql = "select * from info where user=%s and password=%s;"
print(sql)
cur = conn.cursor()
cur.execute(sql,(user,pwd))
print(cur.fetchone())

#sql注入
#select  * from userinfo where user="biu"-- " and password="123";

16.事务

  • 开启事务begin
  • 添加行级锁select * from 表 where 条件 for update
  • 完成更新
  • 提交事务commit

17.数据的备份和恢复

  • 单库备份—在cmd命令行直接执行
    • mysqldump -uroot -proot123 -h127.0.0.1 数据库 >路径
  • 多库备份
    • mysqldump -uroot -proot123 --databases 数据库1 数据库2...>路径
  • 备份所有库
    • mysqldump -uroot -proot123 --all-databases >路径
  • 恢复数据—在mysql中执行命令
    • 切换到一个要备份的数据库中
    • source 路径
posted @ 2020-04-14 22:02  guguda  阅读(123)  评论(0)    收藏  举报