【4】mysql 表的DDL

1. 创建表

1.1 创建表的语法形式

create table 【if not exists】 表名（

字段名 字段类型 【字段属性1 字段属性2 …】,
字段名 字段类型 【字段属性1 字段属性2 …】,
字段名 字段类型 【字段属性1 字段属性2 …】,
…
【，索引1，索引2，…,约束1，约束2，…】
）【表选项1，表选项2，….】;

说明：
字段名： 按命名规则自定义；

字段类型： int ，tinyint ，char ，varchar， datetime 等；

字段属性 包括如下6种：

1.auto_increment: 自动获得增长值，主要用于整数型，通常和primary key一起使用；

2.primary key：设定该字段为主键，主键具有唯一性，且不能为null,一个表只能有一个主键，还有一种主键叫联合主键，就是几个字段来确定唯一性；

3.default :给字段设置一个默认值；

4.unique:该字段具有唯一性，不允许重复；

5.not null:该字段不允许为空，每插入一条记录都必须给该字段赋值；

6.comment: 给字段添加描述信息。

create table t_sample(   id  int  auto_increment primary key,   name varchar(20) unique not null,   age  tinyint unsigned default 18,   height float not null comment '身高',   slary decimal(10,2) not null  comment '收入',   sex char(1) default '男',   birthday datetime not null default '2000-09-09 00:00:00' comment '生日');insert into t_sample (id,name,age,height,slary,sex,birthday) values (null,'tom',20,1.78,5000,'男',now());insert into t_sample (id,name,age,height,slary,birthday) values (null,'jack',28,180,6000,'1988-9-19 9:19:20');

1.2 索引

索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。
索引缺点：
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
所以对于不需要经常检索的字段是否要建立索引要斟酌。

索引的分类：
普通索引： key(字段名)只是加快查询速度而已;
主键索引： primary key(字段名)，具有唯一性，且不能为null;
唯一索引： uique key(字段名)，唯一性，可以为null;
全文索引： fulltext(字段名) 对大数据文本进行索引；
外键索引： foreign key (字段名) references 其他表 (其他表字段名)
外键索引能保证数据的一致性（设置了外键的字段的所有字段值，必须在references表中存在），其他表字段名必须是primary key ;如图：

 create table t_index(   id int auto_increment,   name varchar(20),   mobile char(11),   primary key(id),   unique key(name),   key(mobile) );

创建外键sql：

-- 班级表 create table t_class(  id int auto_increment primary key,  cname varchar(10) );-- 学生表 create table t_stu( id int auto_increment primary key, name varchar(20), cid int, /*班级id*/ foreign key  (cid) references t_class(id) );

1.3 约束

什么叫做约束？
约束，就是要求数据需要满足什么条件的一种“规定”。
主要有如下几种约束：

1. 主键约束：形式： primary key ( 字段名);
   含义（作用）：使该设定字段的值可以用于“唯一确定一行数据”，其实就是“主键”的意思。

2. 唯一约束：形式： unique key ( 字段名);
   含义（作用）：使该设定字段的值具有“唯一性”，自然也是可区分的。

3. 外键约束：形式： foreign key ( 字段名) references 其他表名(对应其他表中的字段名) ;
   含义（作用）：使该设定字段的值，必须在其谁定的对应表中的对应字段中已经有该值了。

4. 非空约束： 形式： not null，其实就是设定一个字段时写的那个“not null”属性。
   这个约束只能写在字段属性上；

5. 默认约束： 形式： default XX值；其实就是设定一个字段时写的那个“default 默认值”属性
   这个约束只能写在字段属性上；

6. 检查约束： 形式： check（某种判断语句），比如：

create  table  tab1 (age  tinyint,check  (age>=0 and age <100)    /*检查约束，其实检测约束在mysql不起真正左右*/ )

其实，主键约束，唯一约束，外键约束，只是“同一件事情的2个不同角度的说法”，他们同时也称为“主键索引”，“唯一索引”，“外键索引”。

1.4 表选项

表选项就是，创建一个表的时候，对该表的整体设定，主要有如下几个：

1. charset = 要使用的字符编码，
2. engine = 要使用的存储引擎（也叫表类型），
3. auto_increment = 设定当前表的自增长字段的初始值，默认是1
4. comment = ‘该表的说明文字’

说明：
1. 设定的字符编码是为了跟数据库设定的不一样。如果一样，就不需要设定了：因为其会自动使用数据库级别的设定；
2. engine（存储引擎）在代码层面，就是一个名词：InnoDB, MyIsam, BDB, archive, Memory。默认是InnoDB。

create table t_opt( id int, name varchar(10))charset = utf8 , engine = MyIsam , auto_increment = 33 , comment = '表选项示例';

2.修改表

1. 添加字段：alter table 表名 add [column] 新字段名 字段类型 [字段属性列表]；
2. 修改字段（并可改名）：alter table 表名 change [column] 旧字段名 新字段名 新字段类型 [新字段属性列表]；
3. 修改字段（只改属性）：alter table 表名 modify [column] 　字段名 新字段类型 [新字段属性列表]；
4. 修改字段名：灰常灰常抱歉，没有单纯修改字段名这个功能！
   删除字段：alter table 表名 drop [column] 字段名；
5. 添加普通索引：alter table 表名 add index [索引名] (字段名1[，字段名2,…])；
6. 添加主键索引(约束)：alter table 表名 add primary key (字段名1[，字段名2,…])；
7. 添加外键索引(约束)：alter table 表名1 add foreign key (字段1，[，字段名2,…]) references 表名2(字段1，[，字段名2,…])；
8. 添加唯一索引(约束)：alter table 表名 add unique (字段名1[，字段名2,…])；
9. 添加字段默认值(约束)：alter table 表名 alter [column] 字段名 set default 默认值；
10. 删除字段默认值(约束)：alter table 表名 alter [column] 字段名 drop default；
11. 删除主键：alter table 表名 drop primay key；#每一个表最多只能有一个主键
12. 删除外键：alter table 表名 drop foreign key 外键名；
13. 删除索引：alter table 表名 drop index 索引名；
14. 修改表名：alter table 表名 rename [to] 新表名；
15. 修改表选项：alter table 表名 选项名1=选项值1，选项名2=选项值2，…；

-- 添加字段 emailalter table t_opt add column email varchar(50) not null;-- email字段添加唯一索引alter table t_opt add unique key(email);

3. 删除表

drop  table  【if  exists】 表名；

4. 其他表相关语句

1. 显示所有表: show tables；2. 显示某表的结构: desc 表名； 或：describe 表名；3. 显示某表的创建语句：show create table 表名；4. 重命名表：rename table 旧表名 to 新表名；5. 从已有表复制表结构：create table [if not exists] 新表名 like 原表名;6. 创建索引：create [unique | fulltext] index 索引名 on 表名(字段名1[，字段名2,...])。这里省略unique或fulltext，那就是普通索引。实际上此创建索引语句，会在系统内部映射为一条“alter table”的添加索引语句。7. 删除索引：drop index 索引名 on 表名。实际上，此语句同样被映射为一条“alter table”的删除索引语句。

5.视图

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。 从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。
视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。
1. 创建视图
create view 视图名 【（字段名1，字段名2，字段名3，….）】 as select语句；
2. 使用视图，跟表使用相似
select 字段… from 视图名 条件;
3. drop view 【if exists】 视图名；

-- 创建视图vnamecreate view vname (f_pro_name,f_pinpai,f_price) as select pro_name,pinpai,price from product;-- 查询该视图 select f_price from vname;-- 删除视图的数据，也就是删除原来product表的数据delete  from vname where f_price = 1169;