数据库常用语句及优化思想

基础操作

管理：
- use DB；
- show database
- show tables
- show columns form 表
- show index form 表
- exit
数据库操作：
- 创建：CREATE DATABASE 数据库名;
- 删除：drop database 数据库名;
数据表操作：
- 创建：CREATE TABLE table_name ('列名' 类型属性, 列名类型, 主键('xxx'));
```
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT, // 自增
   `runoob_title` VARCHAR(100) NOT NULL, // 非空
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` ) // 主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
- 删除：DROP TABLE table_name ;
- 插入：INSERT INTO xxx (field1, field2,...fieldN) VALUES(field1, field2,...,fieldN)
- 查询：
```
SELECT column_name, column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
```
- LIMIT 4读取四条；LIMIT 2, 4:第三条起读取四条。分页LIMIT startRow,pageSize;
- limit 4 offset 9：返回四行，从第十行开始
- 更新：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
- 删除：DELETE FROM table_name WHERE age<20
- Like：条件， %字符来表示任意字符，类似正则*。'%COM'
- UNION：连接两个查询的集合，删除重复数据。
- ```
SELECT A, B FROM tables WHERE XX
UNION [ALL | DISTINCT]
SELECT A, C FROM tables WHERE XX;
```
- DISTINCT默认就是删除重复，加不加无用。ALL返回所有结果集，包含重复的
- ORDER BY：ORDER BY XXX DESC，默认升序。
- GROUP BY：按字段分组，并统计数目。GROUP BY name得到：name | count数目
- 连接：
- INNER JOIN（内连接, 或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
- Alter：修改数据表名或字段。
- ALTER TABLE testalter_tbl DROP i; 删除表的i字段
- ALTER TABLE testalter_tbl ADD i INT; 表添加字段，默认是末尾
- ALTER TABLE testalter_tbl ADD i INT FIRST;第一列添加
- ALTER TABLE testalter_tbl ADD i INT AFTER c;c列之后添加
- ALTER TABLE testalter_tbl MODIFY c CHAR(10);改类型
- ALTER TABLE testalter_tbl CHANGE i j BIGINT;改i为j，类型为BIGINT
- ALTER TABLE testalter_tbl RENAME TO alter_tbl; 改表名

InnoDB和MySAM

InnoDB和MySAM
操作性：
- InnoDB具有事务，支持四个事务隔离级别。
- 适用于大量INSERT或UPDATE操作。
- 不支持全文索引，新版本支持。
- 支持外键
- MyISAM管理非事务表
- 它提供高速存储和检索，以及全文搜索能力。
- 如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择
- 不支持事务、外键
存储：
- MyISAM在磁盘上存储成三个文件。表定义 .frm，数据文件.MYD，索引文件.MYI。跨平台转移麻烦
- InnoDB：空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小
索引：
- InnoDB（索引组织表）：能缓存索引，也能缓存数据，必须得有主键
- MyISAM（堆组织表）：只能缓存索引。非聚类
InnoDB 在做SELECT的时候，要维护缓存数据和索引和其余的，慢一些。MyISAM只缓存索引块
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
- B+树搜索算法搜索索引-取出data值-以此为地址读取数据记录
- 主索引要求key是唯一的，而辅助索引的key可以重复
InnoDB也使用B+Tree作为索引结构，数据文件本身就是索引文件。
- 叶节点data域就是数据记录，称之聚类索引
- 本身要按主键聚类，所以必须要主键（设置自增主键），没有的话分裂维持特性会十分低效。没指定的话会自动选择合适的或自动生成一个隐含字段。长度为6的长整型
- 辅助索引data存储的主键值

索引

索引可以看作一张表，指向实体表记录
唯一索引，保证每行数据的唯一性
加大检索速度，特别是分组和排序
加速表和表之间的连接速度
缺点：创建和维护要耗时（增删时要动态维护），占用物理空间
总结：会提高查询速度，但是DML会变慢（更新维护索引）
语法：
- CREATE INDEX indexName ON mytable(username(length));
- ALTER table tableName ADD INDEX indexName(columnName)
- DROP INDEX [indexName] ON mytable;
添加主键命令：

  mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
  mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

导出数据

mysqldump -u root -p dbs_name table_name > dump.sql [--all-databases]
mysql -u root -p dbs_name < dump.sql

create database adc;
use abc;
set names ytf8;
source /xx/xx/xx/abc.sql

范式

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
第四范式：要求把同一表内的多对多关系删除。
第五范式：从最终结构重新建立原始结构。
BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性。若关系模式R属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。

优化方法

微博整理
- null值容易引发灾难，id is null而不是id=null
- 用union代替or
数据库设计

避免全表扫描，在where 及 order by 涉及的列上建立索引
避免在 where 子句中对字段进行 null 值判断，引擎会放弃索引而使用全局扫描。可以考虑把null默认值设为0
一个表的索引数最好不要超过6个，在一些不常用到的的列不需要索引。因为会降低插入、修改的速度
避免更新索引数据，若该列频繁更新，考虑索引的必要性
字符会一一比较，数字只需要比较一次。所以能用数值尽量不要用字符
尽可能的使用 varchar/nvarchar 代替 char/nchar，变长字段存储空间小，可以节省存储空间
尽量少使用临时表

SQL语句

避免在Where使用 != 或 <>，以及or，否则引擎放弃使用索引而进行全表扫描。

   select id from t where num=10 or num=20
   select id from t where num=10 union all select id from t where num=20

能用 between 就不要用 in，not in
where 子句中使用参数，也会导致全表扫描。强制加上索引

   select id from t where num=@num
   select id from t with(index(索引名)) where num=@num

避免在 where 子句中对字段进行表达式操作：=”左边进行函数、算术运算或其他表达式运算

   select id from t where num/2=100 
   select id from t where num=100*2

具体的字段列表代替“*”，不要返回用不到的任何字段。
避免使用游标，超过一万行就考虑改写
避免大事务操作

April 1, 2019 | 15:12

数据库常用语句及优化思想

基础操作

InnoDB和MySAM

索引

导出数据

范式

优化方法