zhazhahui

导航

MySQl总结

SQL基础

SQl简介

SQL全称是structure qurry language，由IBM在上个世纪70年代开发出来。

SQL语句的分类

  • DDL（数据定义数据）
    • 主要是数据库内部对象的创建，删除，修改的操作语言
    • 创建数据库
      • create database name
    • 使用数据库
      • use dbname
    • 删除数据库
      • drop database name
    • 创建表
      • create table tablename（column_name_1,column_name_2）
    • 删除表
      • drop table name
    • 修改表
      • alter table name modify ename varchar（20）
      • alter table name add age int (3)
      • alter table name drop column age
      • alter table name change age age1 int(4)
      • alter table emp add birth date arter ename
      • alter table name rename new_name
  • DML（数据操作数据）
    • 插入数据
      • insert into tablename(field1,field2 ) values(value1,value2)
    • 更新数据
      • update tablename set field1 = value1,....where condition
    • 删除记录
      • delete from tablename where condition
    • 查询记录
      • select * from tablename where condition
      • 查询不重复的记录
        • select distinct columname from tablename
      • 条件查询
        • select * from tablename where condition
      • 排序和限制
        • select * from tablename where condition order by field1 [desc|asc]
        • select * from limit offset_start,row_count
      • 聚合函数
        • select [field1,field2,...field] fun_name from table_name [where condition] [gruop by field1] [having condiyion]
      • 表连接
        • 左连接
        • 右连接
      • 子查询
        • in
        • not in
        • =
        • !=
          等等
      • 记录联合
        • union 或者union all
  • DCL（数据控制语句）

数据支持类型

数值类型

  • tingint
  • smallint
  • mediumint
  • int
  • bigint
  • float
  • double

日期时间类型

  • date
  • datetime
  • timestamp
  • time
  • year

ENUM类型

SET类型

MYSQL运算符（非常重要，特殊查询时能够用到）

  • 算数运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符
  • 运算符的优先级

常用函数

  • 字符串函数
  • 数值函数
  • 日期和时间函数
  • 流程函数
  • 其他函数

SQL开发

Mysql引擎概述

引擎的优缺点

设置引擎的方式

MyISAM的特点

InnoDB的特点

MERGE的特点

MeMORY的特点

合适的数据类型

  • char与varchar
  • TEXT与BLOB
  • 浮点数与定点数
  • 日期类型选择

字符集

  • Unicode
  • 汉字以及一些常见的字符集
  • 怎么选择合适的字符集
  • 字符集的设置
    • 服务器级
    • 数据库级
    • 表级
    • 字段级
  • 数据库字符集和校验规则
  • 表字符集和校对规则
  • 列字符集和校对规则
  • 连接字符集与校对规则
  • 字符集的修改步骤

索引的设计和使用

设计索引的原则

  • 使用唯一索引
  • 使用短索引
  • 不要过度使用索引

BTREE索引与HASH索引

视图

视图的特点

  • 简单
  • 安全
  • 数据独立

视图操作

  • 创建视图
  • 删除视图
  • 查看视图

存储过程和函数

  • 什么是存储过程和函数
  • 创建，修改存储过程或者函数
  • 删除存储过程或者函数
  • 查看存储过程和函数
    • 状态
    • 定义
    • 信息
  • 变量使用
    • 定义
    • 赋值
  • 定义条件和处理
    • 条件的定义
    • 条件处理
  • 流程控制
    • if
    • case
    • loop
    • leave
    • iterate
    • repeat
    • while

触发器

  • 创建触发器
  • 删除触发器
  • 查看触发器
  • 触发器的使用

事务控制和锁定语句

  • lock table 和 unlock table
  • 事务控制
  • 分布式事务的使用
    • 原理
    • 语法
    • 可能的问题

SQL安全问题

sql注入

  • 应对措施
    • prepareStatement + Bind-variable
    • 使用转换函数
    • 自定义函数进行校验

常用SQL技巧与常见问题

正则表达式

rand()提取随机行

group by 的with rollup 字句做统计

big group function做统计

SQL优化

优化sql的一般步骤，查询sql的执行频率，对频率高的sql语句进行优化能够达到效率提升的作用

查询SQL的执行频率

定位执行效率低下的sql语句

  • 日志文件

分析低效sql的执行计划

确定问题并采用相应的优化措施

MySQL使用索引

  • 使用索引
    • 对于创建的多列索引，只要查询条件用到了最左边的列，索引一般会使用
    • 对于like查询，后面如果是常量并且只有%号不在第一个字符串，索引才可能会被使用
    • 对大的文本进行搜索，使用全文索引而不使用like'%...%'
    • 列名是索引，使用column_name is null 将会使用索引
  • 存在索引但不适用索引
  • 查看索引的使用情况

两个简单的优化方法

  • 定期分析表和检查表
  • 定期优化表

常用的sql优化

  • 大批量的插入数据
  • 优化insert语句
  • 优化group by 语句
  • 优化order by语句
  • 优化嵌套查询

使用sql提示

  • use index
  • ignore index\
  • force index

优化数据库对象

  • 优化表的数据类型
  • 拆分提高表的访问效率
  • 逆规范化
  • 使用中间表

锁问题

  • 表级锁
  • 行级锁
  • 页面锁

myISAM表锁

  • 查询表级锁的争用的情况

Mysql表级锁的锁模式

  • 表共享读锁
  • 表独占写锁

如何加表锁

并发插入

MyISAM的锁调度

事务及其ACID

  • A 原子性
  • C 一致性
  • I 隔离性
  • D 持久性

并发事务处理带来的问题

  • 更新丢失
  • 脏读
  • 不可重复读
  • 幻读

事务的隔离级别

  • 未提交读
  • 已提交读
  • 可重复度
  • 可序列化

间隔锁

InnoDB在不同级别隔离下的一致性读记锁的差异

什么时候使用表锁

死锁

应用优化

使用连接池

减少对MYSQL的访问

使用查询缓存

负载均衡

采用分布式数据库架构

posted on 2021-04-04 20:37  zhazhahui  阅读(58)  评论(0)  编辑  收藏  举报