mysql默认数据库

• information_schema: 数据库的所有信息，表，数据库名称，类型等等
• performance_schema: 收集数据库服务器性能参数
• sys: 把performance_schema的复杂度降低
• mysql：核心数据库，用户，权限，关键字等

mysql常用的命令

• 开启mysql服务：net start mysql
• 关闭mysql服务：net stop mysql
• 登录mysql：mysql -u 用户名 -p 密码
• 查看所有数据库：show databases
• 进入某个数据库：use 数据库名称
• 查看所有的表：show tables
• 查看表：select * from 表名
• 退出：exit;quit;

数据库命令

• 创建：create database 数据库名称
• 删除:drop database 数据库名称
• 存储引擎 (大题，不同存储引擎的特点) √
  • innodb（mysql默认的存储引擎）:
  • myisam:
  • memory:
  • merge:
  • archive:
  • federated:
  • blackhole:
• 查看存储引擎：show engines;
• 查看默认的存储引擎：show variables like ‘%storage_engine%‘
• 修改存储引擎：set default_storage_engine=<存储引擎名称>

操作表

• 创建表：create table 表名（字段 类型 长度）
• 删除表：drop table 表名
• 修改表名：alter table old表名 rename new表名
• 修改字段名：alter table 表名 change old字段名称 new字段名称 数据类型
• 添加字段：alter table 表名 add 新字段名称 数据类型....
• 删除字段：alter table 表名 drop 字段名称
• 查看表结构：desc 表名|describe 表名|explain 表名|show columns form 表名
• 新增表数据：insert into 表名（列名....） value|values（值...）,(值......)
• 修改表数据：update 表名 set 列=value ，列2 = value2 where.....
• 删除表数据：delete from 表名 where ....
• 清空表数据：truncate table 表名 √

DROP,TRUNCATA,DELETE区别

• delete 删除数据，保留表结构，可以回滚，如果数据量大，很慢
• truncate 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
• drop 删除数据和表结构,删除速度最快

sql语句定界符，一般默认的定界符是分号： √

• \g : ;
• \G:将查到的结构旋转90度变成纵向

数据类型

• 整数：int，
• 浮点：float，double，decimal
• 日期：date，time，year，datetime，timestamp
• 字符:char,varchar,blob,text
• 枚举:enum
• 集合类型：set

约束

• 非空：not null
• 唯一：unique key
• 主键：primary key
• 外键：foreign key
• 检查：check
• 自动增加：auto_increment
• 添加唯一约束：alter table 表名 add constrain 唯一约束名称 unique (<列名>)
• 外键约束：constraint <外键名> foreign key 列名 references 主表名称 主键列

查询

• 查询：select 字段1,字段2.... from 表名
  • where：过滤条件
  • distinct：排重
  • between and：在...和...之间
  • in：list比较
  • like：模糊匹配，%，_
  • is null:为空
  • order by：排序
  • limit：分页
  • group by：分组
  • having：分组条件过滤
  • 比较运算符（>,>=,=,<,<=）
  • 逻辑运算符（and,or,not,!）
• 多表查询 √
  • union：连接两个查询结果。数量相同，类型相似，且自动去掉重复字段。如果要全部显示，则使用all
  • 交叉查询： select * from a,b|select * from a class join b 笛卡尔积
  • 内连：inner join，取都满足条件的数据
  • 左外连：left join，展示左表所有数据
  • 右外连：right join，展示右表所有数据
• 子查询
  • any,some：满足其中任何一个
  • all：满足所有
  • in：多个匹配相等
  • exists：如果有返回值，继续查询。否则没有查询结果
  • regexp：正则表达式，^ $

常用的函数

• 字符串函数
  • concat: 链接字符串
  • lower: 小写
  • upper: 大写
  • length: 长度
  • char_length: 字符长度
  • trim: 去掉空格
  • repeat: 重复的次数
  • replace: 替换
  • substring: 截取
  • substr: 截取
• 数值常用函数
  • rand:随机数
• 时间函数
  • curdate: 当前日期
  • curtime：当前时间
  • now：当前日期和时间
  • week: 周
  • year: 年
  • date_format: 格式化
  • data_add: 添加时间
  • datediff: 时间差
• 流程函数
  • if(a,b,c): 如果a成立，返回b，否则返回c
  • ifnull(a,b): 如果a为null返回b，否则返回a
  • case 表达式

case 表达式 when value1 then result1 when value2 then result2 .... else result1end:表达式和那个value相等，返回对应的result

• 其他函数
  • md5：加密
  • count：个数
  • avg：平均数
  • sum：总和
  • max：最大
  • min：最小

索引

• 优点：

  • 加快对表的查询，
  • 快速定位，减少磁盘I/O
  • 独立存在，但是不独立存在
  • 数据库自动维护
  • 类似书的目录

• 设计原则：

  • 避免经常更新的数据
  • 避免数据量少的数据
  • 在常用的查询列
  • 不同值比较多的列
  • 唯一的列

• 自动创建索引：主键

• 手动创建索引：

  1. index 索引名称
  2. alter table 表名 add index 索引名称(属性名称 长度 asc|desc)
  3. create index 索引名称 on 表 (属性名 长度 排序,....)

• 删除索引：

  1. alter table 表名 drop index 索引名称
  2. drop index索引名称 on 表名

• 索引分类:
  普通索引、唯一索引、主键索引、单索引、组合索引、全文索引、空间索引

视图

• 优点：
  1. 限制对数据的访问
  2. 简化查询
  3. 提供了数据的独立性
  4. 对相同数据的不同显示
• 创建：create view 视图名称 as select语句
• 展示：select * from 试图名称
• 修改：alter view 视图名称 as select语句
• 删除：drop view 视图名称

触发器

CREATE [DEFINER = user] TRIGGER trigger_name `trigger_time` `trigger_event` ON tbl_name FOR EACH ROW [`trigger_order`] trigger_body`trigger_time`: { BEFORE | AFTER }`trigger_event`: { INSERT | UPDATE | DELETE }`trigger_order` { FOLLOWS | PRECEDES } other_trigger_name

create trigger `触发器名称` `time` `事件` on `表名` for each row trigger_sql`time`：before|after`事件`：insert|delete|update`trigger_sql`:begin... end

删除触发器：drop trigger if exists 触发器名称

修改结尾符号：delimiter

存储过程

语法： create 用户定义 procedure 存储过程名称（参数） 特性 存储过程sql 参数：in、out、inout 存储过程sql：begin end

-- CREATE PROCEDURE and CREATE FUNCTION Syntax CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bodyCREATE [DEFINER = user] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: COMMENT ‘string‘ | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement

调用存储过程：call 存储过程名称

存储函数

语法： create function 函数名称（参数） returns type 特性 函数sql 参数：只有in，可以忽略不写 type：mysql任意类型

调用：select 函数名称（参数）

变量

声明：

1. 局部：declare 变量名称 type [default value]（只能在begin和end之间定义局部变量）
2. 全局：set @[变量名称] = value

赋值：set 变量名称 = 值
使用：select 表达式 into 变量名称 from 表名

异常处理

• 定义条件：

declare 条件名称 condition for [type] type：字符串错误码，数值型错误码

• 处理程序：

declare [action] handler for [type] begin ... end action：continue，exit type：字符串错误码 数值型错误码 条件名称：SQLWARNING，NOT FOUND，SQL EXCEPTION

游标

• 声明游标：declare 游标名称 cursor for select语句
• 打开游标：open 游标名称
• 读取：fetch 游标名称 into 变量名称
• 关闭游标：close 游标名称
• 特点：
  1. 循环读取数据
  2. select语句返回个数和变量个数相同
  3. 最后一次读取数据之后，fetch就会报错1329

流程控制

-- if： if 条件表达式 then sql1 elseif 条件表达式2 then sql2 else sql3 endif;-- case： case 表达式 when value1 then 语句块1 when value2 then 语句块2 ..... else 语句块n end case； -- repeat： repeat 循环体 until 条件表达式 end repeat-- while： while 条件表达式 do 循环体 end while-- loop： loop 循环体 leave; end loop-- `leave`：跳出当前循环体-- `iterate`：结束本次循环，进入下一次循环

事务

• 提交语句：commit
• 回滚：rollback
• 开启事务：begin,start transaction
• 自动提交：autocommit
• 保存点：

 savepoint [保存点名称]; release savepoint [保存点名称]; rollback to savepoint [保存点名称];

事务的ACID特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation) (通过事务隔离级别和锁机制保证隔离性)
• 持久性(Durabilily)

锁：innodb（mysql默认的存储引擎）

• 类型：读锁，写锁
• 加锁：自动，手动
• 范围：表级，行级

• 表级锁

  • 显式 读：lock table 表名 read
  • 显式 写：lock table 表名 write
  • 自动 意向锁：行级添加锁时，自动添加

• 行级锁：

  • 显式 读：select * ...lock in share mode
  • 显式 写：select * ...for update
  • 自动 排它锁：insert、delete、update

隔离级别

• 读取未提交：脏读
• 读取已提交：不可重读
• 可重复读：幻读
• 串行：并行效率慢，锁等待，死锁

√

连表查询
存储引擎
主外键
like
where
having
commit