本文围绕SQL展开，介绍了SQL语句分类，包括DDL、DML、DCL，还有建库、建表、查询等语句。阐述了MyIASM、InnoDB等数据库引擎特点，表锁、行级锁等锁机制。详细讲解索引类型、优缺点及使用技巧，最后从表结构、索引和语句方面给出SQL优化建议。
登录mysql命令（mysql的bin目录下输入）
mysql -hlocalhost -uroot -p
SQL语句
sql分类
DDL（Data Definition Language，数据定义语言）
- CREATE ： 创建数据库和表等对象DROP ： 删除数据库和表等对象
- ALTER ： 修改数据库和表等对象的结构
DML（Data Manipulation Language，数据操纵语言）
- SELECT ：查询表中的数据
- INSERT ：向表中插入新数据
- UPDATE ：更新表中的数据
- DELETE ：删除表中的数据
DCL（Data Control Language，数据控制语言）
- COMMIT ： 确认对数据库中的数据进行的变更
- ROLLBACK ： 取消对数据库中的数据进行的变更
- GRANT ： 赋予用户操作权限
- REVOKE ： 取消用户的操作权限
建库语句
create database 库名;
建表语句
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
查询去重 DISTINCT
只能用在第一个列名之前。不能写成 regist _ date , DISTINCT product _ type
SELECT DISTINCT product_type, regist_date FROM Product;
关联查询
inner join on：
select * from tch_teacher inner join tch_contact on tch_teacher.Id = tch_contact.TId;
select * from tch_teacher,tch_contact
select * from tch_teacher join tch_contact
right/left join on:
select * from tch_teacher right/left join tch_contact on tch_teacher.Id = tch_contact.TId;
排序查询
order by
- ASC：默认，不写也是ASC：按照升序的方式排列;
- DESC：按照降序的方式排列；
SELECT * FROM emp ORDER BY sal DESC;//降序
SELECT * FROM emp ORDER BY sal ASC;//升序
SELECT * FROM emp ORDER BY sal;//升序
分组查询
HAVING核心：是对分组统计之后的结果集，进行数据的筛选
select GradeId,sex,COUNT(*) as renshu from student group by GradeId,Sex having count(*)>=3 order by GradeId
逻辑关键字
函数
sum
count
avg
max
min
where 与 having：
where 与 having关键字都用于设置条件表达式对查询结果进行过滤，区别是having后面可以跟聚合函数，而where不能，通常having关键字都与group by 一起使用，表示对分组后的数据进行过滤
引擎
MyIASM
支持全文检索，查询效率比较高，
不支持事务，表级锁
InnoDB
支持事务，外键，支持行级锁提高并发效率，自动加排它锁
TokuDB
写速度快，支持数据压缩存储，可以在线添加索引不影响读写操作，不适合大量读写操作
锁
表锁
开销小，加锁快，不会出现死锁，锁定力度大，锁冲突概率高，并发访问效率比较低
行级锁
开销大，加锁慢，会出现死锁，锁定力度小，锁的冲突概率低，并发访问效率比较高
共享锁
读锁，其他事物可以读，不能写
排它锁
写锁，其他事物不能读取也不能写，
索引
索引的类型
- UNIQUE(唯一索引)：
不可以出现相同的值，可以有NULL值 - INDEX(普通索引)：允许出现相同的索引内容
- PROMARY KEY(主键索引)：不允许出现相同的值
- fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
- 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一
添加索引
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
如：
alter table table_name add index index_name (column_list) ;
组合索引
ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);
建表时增加索引
CREATE INDEX index_name ON table_name(username(length));
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
索引的缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在要给大表上建了多种组合索引，索引文件会膨胀很宽
使用索引的技巧
-
索引不会包含有NULL的列
只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。 -
使用短索引
对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 -
索引列排序
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。 -
like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
-
不要在列上进行运算
-
不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
-
索引要建立在经常进行select操作的字段上。
这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 -
索引要建立在值比较唯一的字段上。
-
对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
-
在where和join中出现的列需要建立索引。
-
where的查询条件里有不等号(where column != …),mysql将无法使用索引。
-
如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。
-
在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。
索引实现
- FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
- HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
- BTREE
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
- RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
sql优化
表结构和索引优化
- 分库分表，读写分离
- 为字段选择合适的数据类型
- 将字段多的表分解成多个表，增加中间表
- 混用范式与反范式，适当冗余
- 为查询创建必要索引，但避免滥用
- 尽可能的使⽤用 NOT NULL
sql语句优化
- 寻找最需要优化的语句句：分析慢查询日志
- 利用分析工具：explain、profile
- 避免使用SELECT *，只取需要的列列
- 尽量量使用prepared statements
- 使⽤用索引扫描来排序
扫一扫
109
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
