mysql 基础语法
// 数据库
//创建
CREATE DATABASE IF NOT EXISTS 【指定待创建数据库名称】 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//删除
drop database 【要删除的数据库名称】
表
CREATE TABLE 表名(
字段名 类型（长度）是否可以为null comment '字段说明',
主键
);
//定义student表信息并主键自增！
CREATE TABLE student(
id BIGINT(30) auto_increment not null comment '编号',
name VARCHAR(20) NOT NULL DEFAULT '' comment '学生名称',
birthday VARCHAR(20) NOT NULL DEFAULT '' comment '生日',
sex VARCHAR(10) NOT NULL DEFAULT '' comment '性别',
PRIMARY KEY(id)
);
删除
drop table 表名
insert
//前面为字段名，后面为这个字段要插入的值
INSERT INTO student (name, birthday, sex) VALUES ('赵三', 1990-02-02, '男');
select
//select * from 表名 *代表显示表中所有字段，具体应用中应显示所需字段
select * from student
update
//UPDATE 【表名】set 【update列】='修改后的值' where 条件
//修改name为赵三的学生改为王五
update student set name='王五' WHERE NAME = '赵三';
delete
//DELETE FROM 【表名称】 WHERE 【列名称】 = 值
DELETE FROM student WHERE NAME ='王五'
//update + delete 要注意where条件，如不加 update全表，delete全表
连接查询
准备数据
CREATE TABLE course(
c_id BIGINT(30) auto_increment not null ,
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id BIGINT(30) NOT NULL,
PRIMARY KEY(c_id)
);
CREATE TABLE teacher(
t_id BIGINT(30) auto_increment not null,
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
CREATE TABLE score(
id BIGINT(30) auto_increment not null,
c_id BIGINT(30),
s_score INT(3),
s_id BIGINT(30),
PRIMARY KEY(id)
);
//准备学生数据
DELETE FROM student
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('1', '赵雷', '1990-01-01', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('2', '钱电', '1990-12-21', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('3', '孙风', '1990-05-20', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('4', '李云', '1990-08-06', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('5', '周梅', '1991-12-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('6', '吴兰', '1992-03-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('7', '郑竹', '1989-07-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('8', '王菊', '1990-01-20', '女');
//准备老师数据
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('1', '张三');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('2', '李四');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('3', '王五');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('4', '见习老师');
//准备课程数据
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('1', '语文', '2');
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('2', '数学', '1');
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('3', '英语', '3');
//准备测试分数数据
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('1', '1', '1', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('2', '1', '2', '90');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('3', '1', '3', '99');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('4', '2', '1', '70');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('5', '2', '2', '60');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('6', '2', '3', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('7', '3', '1', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('8', '3', '2', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('9', '3', '3', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('10', '4', '1', '50');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('11', '4', '2', '30');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('12', '4', '3', '20');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('13', '5', '1', '76');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('14', '5', '2', '87');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('15', '6', '1', '31');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('16', '6', '3', '34');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('17', '7', '2', '89');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('18', '7', '3', '98');
内连接：
inner join（显性链接） 只连接匹配的行
image.png
进行连接的两个表对应的相配字段完全相同连接。
//如我们要查询记录在课程表里每一个课程的授课老师，我们使用内连接将课程中t_id 和 teacher中t_id进行连接
SELECT * FROM teacher t inner join course c on t.t_id = c.t_id
//等同于
SELECT * FROM teacher t join course c on t.t_id = c.t_id
//where，join，隐性链接
SELECT * from teacher t,course c WHERE t.t_id = c.t_id
//不过此语句实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。
//在笛卡尔连接中，在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。
// 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。
外连接：
左外联接 返回包括左表中的所有记录和右表中链接字段相等的结果记录！
image.png
//查询老师们的授课信息，以左表 teach 为主，
SELECT * from teacher t LEFT JOIN course c ON c.t_id = t.t_id
//等同于LEFT OUTER JOIN
SELECT * FROM teacher t LEFT OUTER JOIN course c ON c.t_id = t.t_id
左表独占，不与B共享（不连接B表内容）image.png
//如需求是查询没有授课的老师信息
SELECT * from teacher t
WHERE NOT EXISTS (SELECT * from course c WHERE c.t_id = t.t_id )
//等同于
SELECT * from teacher t
WHERE t.t_id NOT in (SELECT t_id from course c WHERE c.t_id = t.t_id)
//推荐，（在子查询中无需SELECT时考虑）
SELECT * from teacher t LEFT OUTER JOIN course c ON c.t_id = t.t_id WHERE c.t_id is NULL
右外链接，返回包括右表中的所有记录和左表中链接字段相等的记录，于LEFT JOIN相反，以右边表为主
image.png
//查询每老师们授课信息，以RIGHT JOIN实现
//右表带出所有记录，左边匹配的记录为链接字段结果集记录，不足以行null替补显示！
SELECT * from course c RIGHT JOIN teacher t ON c.t_id = t.t_id
//等同于
SELECT * from course c RIGHT OUTER JOIN teacher t ON c.t_id = t.t_id
右表独占，不与A共享（不连接A表内容）image.png
//如之前的需求是查询没有授课的老师信息，现在以右表独占实现
SELECT * from course C RIGHT OUTER JOIN teacher t ON t.t_id = c.t_id WHERE c.t_id is NULL
全外连接：两张表FULL JOIN 全连接
image.png
mysql不支持外连接，解决办法是使用union组合查询，把左连接和右连接的结果进行合并
SELECT * from teacher a LEFT JOIN course c ON a.t_id = c.t_id union SELECT * from course c RIGHT JOIN teacher t ON t.t_id =c.t_id
两张表没有共有部分，左右独立。
image.png
//需求：查询teacher中不存在于course中结果+查询course中不存在于teacher的结果
SELECT * from teacher a LEFT JOIN course b ON a.t_id = b.t_id WHERE b.t_id IS NULL
union
SELECT * from course a RIGHT JOIN teacher b ON a.t_id =b.t_id WHERE a.t_id is NULL
数据库常用函数
一、数学函数
abs(x) 返回x的绝对值
bin(x) 返回x的二进制（oct返回八进制，hex返回十六进制）
ceiling(x) 返回大于x的最小整数值
exp(x) 返回值e（自然对数的底）的x次方
floor(x) 返回小于x的最大整数值
greatest(x1,x2,...,xn)返回集合中最大的值
least(x1,x2,...,xn) 返回集合中最小的值
ln(x) 返回x的自然对数
log(x,y)返回x的以y为底的对数
mod(x,y) 返回x/y的模（余数）
pi()返回pi的值（圆周率）
rand()返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
round(x,y)返回参数x的四舍五入的有y位小数的值
sign(x) 返回代表数字x的符号的值
sqrt(x) 返回一个数的平方根
truncate(x,y) 返回数字x截短为y位小数的结果
二、聚合函数(常用于group by从句的select查询中)
avg(col)返回指定列的平均值
count(col)返回指定列中非null值的个数
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
ascii(char)返回字符的ascii码值
bit_length(str)返回字符串的比特长度
concat(s1,s2...,sn)将s1,s2...,sn连接成字符串
concat_ws(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果find_in_set(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果
left(str,x)返回字符串str中最左边的x个字符
length(s)返回字符串str中的字符数
ltrim(str) 从字符串str中切掉开头的空格
position(substr in str) 返回子串substr在字符串str中第一次出现的位置
quote(str) 用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)返回字符串str重复x次的结果
reverse(str) 返回颠倒字符串str的结果
right(str,x) 返回字符串str中最右边的x个字符
rtrim(str) 返回字符串str尾部的空格
strcmp(s1,s2)比较字符串s1和s2
trim(str)去除字符串首部和尾部的所有空格
ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果
四、日期和时间函数
curdate()或current_date() 返回当前的日期
curtime()或current_time() 返回当前的时间
date_add(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) 返回date是一个月的第几天(1~31)
dayofyear(date) 返回date是一年的第几天(1~366)
dayname(date) 返回date的星期名，如：select dayname(current_date);
from_unixtime(ts,fmt) 根据指定的fmt格式，格式化unix时间戳ts
hour(time) 返回time的小时值(0~23)
minute(time) 返回time的分钟值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名，如：select monthname(current_date);
now() 返回当前的日期和时间
quarter(date) 返回date在一年中的季度(1~4)，如select quarter(current_date);
week(date) 返回日期date为一年中第几周(0~53)
year(date) 返回日期date的年份(1000~9999)
一些示例：
获取当前系统时间：select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回两个日期值之间的差值(月数)：select period_diff(200302,199802);
在mysql中计算年龄：
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
这样，如果brithday是未来的年月日的话，计算结果为0。
下面的sql语句计算员工的绝对年龄，即当birthday是未来的日期时，将得到负值。
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') <date_format(birthday, '00-%m-%d')) as age from employee
五、加密函数
aes_encrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用aes_encrypt的结果是一个二进制字符串，以blob类型存储
aes_decrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
decode(str,key) 使用key作为密钥解密加密字符串str
encrypt(str,salt) 使用unixcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
encode(str,key) 使用key作为密钥加密字符串str，调用encode()的结果是一个二进制字符串，它以blob类型存储
md5() 计算字符串str的md5校验和
password(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法。
sha() 计算字符串str的安全散列算法(sha)校验和
示例：
select encrypt('root','salt');
select encode('xufeng','key');
select decode(encode('xufeng','key'),'key');#加解密放在一起
select aes_encrypt('root','key');
select aes_decrypt(aes_encrypt('root','key'),'key');
select md5('123456');
select sha('123456');
六、控制流函数
mysql有4个函数是用来进行条件操作的，这些函数可以实现sql的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。
mysql控制流函数：
CASE WHEN
case when[test1] then [result]...else [default] end如果test1是真，则返回result，否则返回default。
case [test] when[val1] then [result]...else [default]end 如果test和valn相等，则返回result，否则返回default。
IFNULL(arg1,arg2) arg1不是空返回arg1否则返回arg2
//准备数据
ALTER TABLE `course`
MODIFY COLUMN `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `c_id`;
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (4, NULL, 4);
//如
SELECT IFNULL(c_name,'null'),c_name from course
NULLIF(var1，var2)函数，如果var1==var2返回null；否则返回var1！
//如
SELECT NULLIF('1','1') from teacher
IF(expression，var1，var2) 如果expression是真，返回var1；否则返回var2。
// if(test,t,f)
SELECT t_name,IF(t_name='见习老师','见习老师代号为1','已转正老师代号为2')as code_name from teacher
Mysql 引擎
简介
数据库中的存储引擎其实是对使用了该引擎的表进行某种设置，数据库中的表设定了什么存储引擎，那么该表在数据存储方式、数据更新方式、数据查询性能以及是否支持索引等方面就会有不同的“效果。
2，mysql的各种引擎：
mySQL主要有以下几种引擎：ISAM、MyISAM、InnoDB、HEAP（也称为MEMORY）、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、 Berkeley、Merge、Federated和Cluster/NDB等，除此以外我们也可以参照MySQL++ API创建自己的数据库引擎。
ISAM
该引擎在读取数据方面速度很快，而且不占用大量的内存和存储资源；但是ISAM不支持事务处理、不支持外来键、不能够容错、也不支持索引。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。
MyISAM
它是MySql的默认引擎，5.5后改为InnoDB，由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却不提供事务的支持，也不支持行级锁和外键。除了提供ISAM里所没有的索引和字段管理等大量功能，MyISAM还使用一种表格锁定的机制来优化多个并发的读写操作，但是需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的 MyISAMPack工具。
机制
MyISAM强调了快速读取操作，主要用于高负载的select，这可能也是MySQL深受Web开发的主要原因：在Web开发中进行的大量数据操作都是读取操作，所以大多数虚拟主机提供商和Internet平台提供商（Internet Presence Provider，IPP）只允许使用MyISAM格式。MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。
存储结构
MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型。
静态型：指定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样MySQL就会自动使用静态MyISAM格式。使用静态格式的表的性能比较高，因为在维护和访问以预定格式存储数据时需要的开销很低；但这种高性能是以空间为代价换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。
动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时MyISAM就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生，随着数据变化的增多，碎片也随之增加，数据访问性能会随之降低。 对于因碎片增加而降低数据访问性这个问题，有两种解决办法：
a、尽可能使用静态数据类型；
b、经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失。如果存储引擎不支持 optimize table table_name则可以转储并重新加载数据，这样也可以减少碎片；
压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用。
InnoDB
该存储引擎为MySQL表提供了ACID事务支持、系统崩溃修复能力和多版本并发控制（即MVCC Multi-Version Concurrency Control）的行级锁;该引擎支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空则自动从现有值开始增值，如果有但是比现在的还大，则直接保存这个值; 该引擎存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。该引擎在5.5后的MySQL数据库中为默认存储引擎。
它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。
MyISAM VS Innodb
大容量 的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。
大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
HEAP（也称为MEMORY）
该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而其数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。这种存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。
CSV（Comma-Separated Values逗号分隔值）
使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。
BLACKHOLE（黑洞引擎）
该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用
ARCHIVE
该存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。
PERFORMANCE_SCHEMA
该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。
Berkeley（BDB）
该存储引擎支持COMMIT和ROLLBACK等其他事务特性。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。
Merge
该引擎将一定数量的MyISAM表联合而成一个整体,适用数据表记录很大。如日志数据，将不同月份的数据存入不同的表，然后使用myisampack工具压缩数据，最后通过一张MERGE表来查询这些数据。可以获得更快的速度。可以根据某种指标，将一张只读的大表分割成若干张小表，然后将这些小表分别放在不同的磁盘上存储。当需要读取数据时，MERGE表可以将这些小表的数据组织起来，就好像使用先前的大表一样，但是速度会快很多。
Federated
该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。
Cluster/NDB
该存储引擎用于多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大、安全和性能要求高的场景
mysql 索引 简介
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度，MySQL目前主要有以下几种索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
1：普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引
CREATE INDEX index_name ON table(column(length))
（2）修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
（3）创建表的时候同时创建索引
CREATE TABLE table (
id int(11)NOT NULL AUTO_INCREMENT,
title char(255) CHARACTER NOT NULL,
content text CHARACTER NULL,
time int(10) NULL DEFAULT NULL,
PRIMARY KEY(id),
INDEX index_name (title(length))
)
（4）删除索引
DROP INDEX index_name ON table
2：唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
（2）修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
（3）创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
UNIQUE indexName (title(length))
);
3：主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);
4：组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
（2）修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
（3）直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
缺点
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
五、注意事项
使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.尽量不要在列上进行运算
这将导致索引失效而进行全表扫描，例如
SELECT*FROM table_name WHERE YEAR (column_name)<2017;
6.尽量不使用not in和<>操作
网友评论