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的访问

使用查询缓存

负载均衡

采用分布式数据库架构

MySQl总结