1、DDL（Data Definition Language）数据库定义语言

一、索引

1、普通索引(INDEX)

语法：ALTER TABLE 表名字 ADD INDEX 索引名字 ( 字段名字 )

　　-- –直接创建索引

　　CREATE INDEX index_user ON user(title)

 

　　-- –修改表结构的方式添加索引

　　ALTER TABLE table_name ADD INDEX index_name ON (column(length))

 

　　-- 给 user 表中的 name字段 添加普通索引(INDEX)

　　ALTER TABLE `table` ADD INDEX index_name (name)

 

　　-- –创建表的时候同时创建索引

　　CREATE TABLE `table` (

    　　`id` int(11) NOT NULL AUTO_INCREMENT ,

    　　`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    　　`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    　　`time` int(10) NULL DEFAULT NULL ,

    　　PRIMARY KEY (`id`),

    　　INDEX index_name (title(length))

　　)

 

　　-- –删除索引

　　DROP INDEX index_name ON table

 

2、主键索引(PRIMARY key)

语法：ALTER TABLE 表名字 ADD PRIMARY KEY (字段名字)

　　-- 给 user 表中的 id字段 添加主键索引(PRIMARY key)

　　ALTER TABLE `user` ADD PRIMARY key (id);

 

3、唯一索引(UNIQUE)

语法：ALTER TABLE 表名字 ADD UNIQUE (字段名字)

　　-- 给 user 表中的 creattime 字段添加唯一索引(UNIQUE)

　　ALTER TABLE `user` ADD UNIQUE (creattime);

 

4、全文索引(FULLTEXT)

语法：ALTER TABLE 表名字 ADD FULLTEXT (字段名字)

　　-- 给 user 表中的 description 字段添加全文索引(FULLTEXT)

　　ALTER TABLE `user` ADD FULLTEXT (description);

 

5、添加多列索引

语法：ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3)

-- 给 user 表中的 name、city、age 字段添加名字为name_city_age的普通索引(INDEX)

ALTER TABLE user ADD INDEX name_city_age (name(10),city,age); 

 

6、建立索引的时机

在WHERE和JOIN中出现的列需要建立索引，但也不完全如此：

• MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引
• 某些时候的LIKE也会使用索引。
• 在LIKE以通配符%和_开头作查询时，MySQL不会使用索引

-- 此时就需要对city和age建立索引，

-- 由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

　　SELECT t.Name  

　　FROM mytable t LEFT JOIN mytable m ON t.Name=m.username 

　　WHERE m.age=20 AND m.city='上海';

 

　　SELECT * FROM mytable WHERE username like'admin%'; -- 而下句就不会使用：

　　SELECT * FROM mytable WHEREt Name like'%admin'; -- 因此，在使用LIKE时应注意以上的区别。

 

索引的注意事项

• 索引不会包含有NULL值的列
• 使用短索引
• 不要在列上进行运算 索引会失效

 

二、表行列

1、添加列

语法：alter table 表名 add 列名 列数据类型 [after 插入位置];

示例：

　　-- 在表开头添加一个字段

　　ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL FIRST;

 

　　-- 在表students的最后追加列 address: 

　　alter table students add address char(60);

 

　　-- 在名为 age 的列后插入列 birthday: 

　　alter table students add birthday date after age;

 

2、修改列

语法：alter table 表名 change 列名称 列新名称 新数据类型;

　　-- 将表 tel 列改名为 telphone: 

　　alter table students change tel telphone char(13) default "-";

　　-- 将 name 列的数据类型改为 char(16): 

　　alter table students change name name char(16) not null;

　　alter table table_name MODIFY field_name VARCHAR(100) NOT NULL;

 

3、删除列

语法：alter table 表名 drop 列名称;

　　-- 删除表students中的 birthday 列: 

　　alter table students drop birthday;

 

4、重命名表

语法：alter table 表名 rename 新表名;

　　-- 重命名 students 表为 workmates: 

　　alter table students rename workmates;

 

5、清空表数据

语法：delete from 表名; truncate 表名;

　　-- 清空表为 workmates 里面的数据，不删除表。 

　　delete from workmates; 可以加where指定删除部分记录

　　truncate table_name; 删除所有记录

 

6、删除整张表

语法：drop table 表名;

　　-- 删除 workmates 表: 

　　drop table workmates;

 

7、删除整个数据库

语法：drop database 数据库名;

　　-- 删除 samp_db 数据库: 

　　drop database samp_db;

 

8、检查表

CHECK TABLE table_name;

9、修复表

REPAIR TABLE table_name;

10、优化表

OPTIMIZE TABLE table_name;

11、分析表

ANALYZE TABLE table_name;

12、显示指定表的表结构

DESCRIBE table_name;

show creata table table_name;

 

 

补充数据库结构优化：】

 

1）范式优化： 比如消除冗余（节省空间。。）

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4）拆分其实又分垂直拆分和水平拆分：

案例： 简单购物系统暂设涉及如下表：

　　1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势）

　　以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 

垂直拆分：

　　　　解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 

水平拆分： 

　　　　解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺

方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上