MySql基础

基础语法1

• 修改：update 表名 set 字段=值 where 条件；
• 查询：select * from 表名 where 条件；
• 删除：delete from 表名 where 条件；
• 新增：insert into 表名 （字段1，字段2）values（值1，值2）；

基础语法2

• 判断库/表是否存在，如果不存在则创建
  create …if not exist；
• 创建一个和某个已存在的表相同结构的新表
  create table 新表 like 旧表；
• 修改列类型 / 列名和类型
  alter table 表名 modify 列名 数据类型；
  alter table 表名 change 旧列名 新列名 数据类型；
• 修改表名
  rname 旧表名 to 新表名；
• truncate 表名
  先删除表，后重构一张相同结构的新表：drop table 表名 —> create table 表名；
• distinct
  查询的结果不出现重复值：
  select distinct address from 表名；
• between
  用于范围查询
  select * from 表名 where 列名 between min and max；

聚合函数

聚合函数	作用
count(列名)	求记录数
sum(列名)	求和
max(列名)	求最大值
min(列名)	求最小值
avg(列名)	求平均值

其他函数

• group_concat
  用于将同一字段的不同值（多条数据）进行合并（一条数据），配合order by使用
  详情查看
• DATE_FORMAT
  用于以不同的格式显示日期/时间数据。
  详情查看

执行顺序

select 字段 from 表名 where 条件 group by 分组字段 having 过滤条件 order by 排序列 limit 跳过行，返回行；
select 要查询的字段
from 表名称
where 查询条件
group by分组
having 分组后的过滤条件
order by排序
limit 分页查询

• where和having的区别

where	having
后面不能跟聚合函数	可以
先过滤后分组	先分组后过滤

类型

BLOB类型

• BLOB用于存储二进制数据，MySQL有四种BLOB类型:

1. tinyblob:仅255个字符
2. blob:最大限制到65K字节
3. mediumblob:限制到16M字节
4. longblob:可达4GB

详细查看

detetime/timestamp

• 时间类型

1. CURRENT_TIMESTAMP
   在detetime或者timestamp默认值处填写，表示当插入数据的时候，该字段默认值为当前时间
2. CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   同上，表示当修改数据的时候，该字段默认值为当前时间

详情查看

约束

约束名	关键字
主键	primary key（auto_increment自增长）
唯一	unique
不为空	not null
默认	default
外键	foreign key (外键列名) references 主表(主键)

• 互联更新
  on update cascade
• 级联删除
  on delete cascade

表连接

内连接

• 隐式内连接
  select * from 表1，表2 where 条件；
• 显式内连接
  select * from 表1 inner join 表2 on 条件

外连接

• 左/右外连接
  select * from 左表 left/right join 右表 on 条件；
• 只保存左/右表独有的数据
  select * from 左表 left/right join 右表 on 条件 where 右表字段 is null；
• 全连接（查询左连接 + 右连接）
  select * from 左表 left join 右表 on 条件
  union（union 用于取数据交集，union all可出现重复值）
  select * from 右表 right join 左表 on 条件；

注: 内外查询都属关联查询。

子查询

一个查询的结果作为另一个查询的条件；是查询语句的嵌套；子查询一定要有括号。

• 子查询的结果是单行单列
  父查询使用比较运算符操作

-- 查询张三的工资
select salary from man where name=‘张三’;
-- 查询工资高于张三的其他人
select * from man where salary > (select salary from man where name=‘张三’);

• 子查询的结果是多行单列
  多行单列可认为是一个集合，父查询使用in，all，any操作。（all所有，any任意一个）
• 子查询的结果是多行多列
  父查询将子查询的结果作为一张表进行处理

注： 能使用关联查询就不使用子查询。

引擎

• 查看引擎
  show engines;
• 查看当前存储引擎
  show variables like ‘%storage_engine%’;

/	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表所	表锁，操作一条记录会锁住整张表	行锁，只锁操作的行
缓存	缓存索引，不缓存数据	缓存索引、数据，对内存要求高
全文索引	支持	不支持

锁

按照类型

• 读锁（共享锁）：(MyISAM) 多个线程可以同时访问一个资源，互不影响。
• 写锁（排它锁）：(InnoDB)当前写操作没完成时，会阻断其他写锁和读锁。

按粒度区分

表锁

对读操作没有影响；写的话会将整张表锁起来，其他人无法访问;开销小，加锁快，无死锁。

1. 创建使用MyISAM作为引擎的表

CREATE TABLE `user` ( 
	 `id` INT(11) NOT NULL AUTO_INCREMENT,  
	 `name` VARCHAR(255) DEFAULT NULL, 
	  PRIMARY KEY (`id`) )
ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO USER(NAME) VALUES('孙悟空'),('猪八戒');

2. 查看是否上锁
   show open tables;
3. 加锁
   lock table user read, user write;
4. 解锁
   unlock tables；

行所

对操作的行进行上锁，开销大，加锁慢（算法维护)，会出现死锁，并发量高。

• 如何为指定的行加锁
  在查询之后添加 for update，其他才做会被阻塞，直到锁定的行提交commit；
• 查看行锁的使用信息
  show status like ‘innodb-row-lock%’;

页锁

---

数据库优化

1.数据库遵循三方式

• 1NF：每一列都是不可分割的基本数据项，列不可再拆分。
• 2NF：在满足1F的条件下，不产生局部依赖，表中的每列都完全依赖于主键，一张表只描述一件事。
• 3NF：在满足2F的条件下，不产生传递依赖，所有的列都直接依赖于主键，使用外接关联，外接都来源于其他表的主键。

2.建立索引

• 什么字段需建立索引：经常查询且值数量多。
• 实现原理：B-tree（多路搜索树/折半查找)。
  先折半取中间值，然后将小的数放左边，大的数放右边。
  查询速度为2的N次方，如：2的10次方 = 1024，即找10次就可以找到1024。
• B-Tree和二叉树区别：二叉树每个节点只能存储一个数据，B-Tree可存放多个数据。
• 为什么要用索引：提高查询效率。
• 缺点：当数据增加和删除时，会重新生成索引文件，增加内存消耗。
• explain 执行计划：加载查询语句前面可以查看是否使用了索引。

2.1 主键索引

• 主键索引是唯一性的所有，必须指定为“PRIMARY KEY”。
• 可以在创建表时指定：CREATE TABLE tablename ( […], PRIMARY KEY (列名));;
• 也可以在创建表之后指定：ALTER TABLE tablename ADD PRIMARY KEY (列名);

2.2 普通索引

• 加快对数据的访问速度。为经常出现在查询条件（where column=）或者是排序条件（order by column）中的数据列创建索引。

create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);

2.3 组合索引

• 创建组合索引：alter table 表名 add index 索引名 （列1，列2);
• 使用列1作为条件查询会使用索引。
• 使用列2作为条件查询不会使用索引。
• 使用列1+列2会使用索引。

2.4 唯一索引（不使用）

• 实际开发中不使用，主键索引也有唯一性，可以取代唯一索引；
• 和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

2.5 全文索引（不使用）

全文索引只有 MyISAM(数据库引擎，事务不安全) 支持(5.6后支持)，其他引擎都不支持,如InnoDB(事务安全)、DBD（事务安全），并且不支持中文分词，所以用第三方搜索取代（ElasticSearch，shiro等）。

3.分库分表

垂直分割（分库）

将一个数据库分离为多个数据库，互不影响，减低耦合度。一般在大型项目中使用。

水平分割（分表）

水平分表使用取模分表算法，数据库数量量庞大时使用（具体看业务需求）。

4.sql优化

• 在使用like查询语句时，如果前面使用了%符号，则不会使用索引；
• 在使用or时，只要一个条件没有索引，则整体不使用索引。

5.存储过程

存储过程和函数是事项经过编译和存储在数据库中的一段SQL语句的集合。

• 减少数据库和应用服务器之间的传输，提高数据处理的效率；
• 当sql语句发生变化时，不需要更改应用服务器。

-- 创建存储过程 
DELIMITER $$ 
CREATE PROCEDURE testa() 
BEGIN  
	SELECT * FROM student WHERE id=2;
END $$
-- 调用存储过程 
call testa();

• 存储过程和函数的区别：函数可以直接返回结果；函数使用SELECT调用，存储过程使用CALL调用；

6.读写分离

7.配置最大连接数（略）

8.清理碎片化（略）

9.服务器升级（略）