null和not null修饰符

create table test(col1 varchar(10) not null,col2 varchar(10) null)engine=myisam;

字段类型是not null ，为什么可以插入空值
为什么not null的效率比null高

‘’ 和null 有什么不一样？
‘’ 是不占用空间的
null 是占用空间的
null 空列需要行中的额外空间来记录其值是否为空。对于myisam表，每个null列需要一个额外的位，四舍五入到最接近的字节。

 为什么not null 比null效率高，因为可空列需要更多的存储空间，需要一个额外的字节作为判断是否为null的标志位，需要mysql内部进行特殊处理。
 

判断字段是否为‘’ 的时候，
select * from test where col1 ='';
select * from test where col1 !='';
判断字段是否为null的时候，
select * from test where col1 is null;
select * from test where col1 is not null;

 

default 设定字段的默认值

create table test2(name varchar(8) not null,dept varchar(25) default 'hello');
insert into test2(name) values ('hello');
select * from test2;
如果字段没有设定default，mysql依据这个字段时null还是not null，如果可以为null，则为null，不可以为null，则报错。
如果时间字段，默认为当前时间，插入0时，默认为当前时间。
如果时enum类型，默认为第一个元素。

auto_increment字段约束

自动增长
create table items (id int not null auto increment primary key,label varchar(20) not null);
自动增长字段，只能修饰int 字段。表示mysql应该自动为该字段生成一个唯一没有用过的数（每次在最大ID值的基础上加1，例如：目前最大id是34，然后删除34，新添加的会是35）对于主键，这是非常有用，可以为每条记录创建一个唯一的标识符。

清除表中的记录

delete 不加where条件，清空表所有记录，但是不会清零auto_increment值
delete from items;

truncate 删除表的所有记录，并清零auto_increment值，新插入的记录从1开始。
truncate table items;

索引

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本前书前面的目录，能加快数据库的查询速度。
优点：加快查询速度。
缺点:索引是文件存储的，索引过多，占磁盘空间较大，而且它影响insert,update,delete执行时间，索引中数据必须与数据表同步，如果索引过多，当表中数据更新的时候，索引需要同步更新，降低了效率。

索引类型：

1.普通索引

create table 表名 (列定义 index 索引名称 (字段))
create table 表名 (列定义 key 索引名称 (字段))

create table demo(id int(4),name varchar(20),pwd varchar(20),index(pwd)); 不加名称，默认用字段名当索引名
show create table demo;

create table demo2(id int(4),name varchar(20),pwd varchar(20),key index_pwd(pwd));
show create table demo2;

当表创建后，使用alter为表添加索引
alter table 表名 add index 索引名称(字段1，字段2) ;
alter table demo add key(pwd);
desc demo;

MUL就是一般索引，该列值可以重复，可以为null，建议索引尽量选用无重复值的列，否则索引意义不大。

删除索引
alter table 表名 drop key 索引名称;
alter table demo drop key index_pwd;  #  

2.唯一性索引

唯一性索引与普通索引的区别就是，唯一索引列所有值只能出现一次，必须唯一，不能重复，允许有null。
create table 表名(列定义:unique key 索引名 (字段));
create table 表名(列定义:unique index 索引名 (字段));
create table demo3(id int(4) auto_increment primary key,uname varchar(20),upwd varchar(20),unique index(uname));
修改表时，添加唯一索引
alter table 表名 add unique 索引名 (字段)；
alter table demo3 drop key uname;
alter table demo3 add unique(uname);

3.主键索引

查询数据库，按主键查询最快，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，不能重复，不能为null。
创建主键索引
create table demo5(id int(4) not null auto_increment,name varchar(20) defualt null,primary key(id));
create table demo5(id int(4) not null auto_increment primary key,name varchar(20) defualt null);
desc demo5;

show index from demo5\G

创建表后添加（不建议，因为生产数据无法保证唯一，创建主键会报错）
alter table demo5 drop primary key;
删除主键报错，是因为有自增长约束的原因，先删除自增长约束,在添加主键
alter table demo5 change id id int(4) not null;  #取消自增长
alter table demo5 drop primary key; #删除主键
alter table demo5 change id id int(4) not null primary key auto_increment; #添加主键

4.复合索引

索引可以包含1个，2个或更多列，2个以上列上的索引被称为复合索引。
例如：创建一个表存放服务器允许或拒绝IP和port，要记录中ip和port要唯一。
create table firewall (host varchar(15) not null,port smallint(4) not null,access enum('deny','allow') not null,primary key (host,port));

insert into firewall values('192.168.0.64',22,'deny');
insert into firewall values('192.168.0.64',21','allow');
insert into firewall values('192.168.0.64','21','allow');

ip和端口，同时重复，就会报错，这就是复合索引。

索引设计原则
1.索引并非越多越好
2.数据量不大不需要建立索引
3.列中的值变化不多不需要建立索引 row id
4.经常排序（order by 字段） 和分组(group by 字段)  的列需要建立索引
5.唯一性约束对应使用唯一性索引
建表的时候如果加各种索引，顺序如下：
create table 表名（字段定义，PRIMARYKEY (`bId`),UNIQUE KEY `bi` (`bImg`),KEY `bn`(`bName`),KEY `ba` (`author`)）

外键约束

外键约束， foreign key 就是表与表之间某种约定的关系，因这种关系可以让表与表之间数据更加完整，关联性更强。
简而言之，就是同时删除，同时更改，两张表之间相互有关联的数据。
参数解释：
restrict 拒绝对父表的删除或更新操作
cascade 从父表删除或更新且自动删除或更新子表中匹配的行 
on update cascade 是级联更新的意思，on delete cascade 是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除

创建外键约束
create table 表名( constraint 约束名 foreign key 外键字段 references 外键表名(外键字段1,外键字段2) on delete cascade on update cascade) engine=innodb;
简化语法：                                          foreign key 当前表的字段 references 外部表名 （关联的字段） ENGINE =innodb

create table `order`(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;
 

创建成功，必须满足以下 4 个条件：
1. 确保参照的表和字段存在。
2. 组成外键的字段被索引。
3. 必须使用 ENGINE 指定存储引擎为：innodb。
4. 外键字段和关联字段，数据类型必须一致。

我们创建一个数据库，包含用户信息表和订单表
create database market;
use market;
create table `user`(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb;

create table `order`(o_id int(11) auto_increment, u_id int(11) default '0',username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;

插入测试数据
insert into user(name,sex)values('HA',1),('LB',2),('HPC',1);
insert into `order`(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
select * from `order`;

select id,name,sex,money,o_id from user,`order` where id=u_id;

delete from user where id=1;
select * from `order`;

通过 alter table 创建外键和级联更新，级联删除

alter table 数据表名称 add [constraint [约束名称] ] foreign key (本表外键字段,..) references 外表(关联字段,...)
[on update cascade|set null|no action]
[on delete cascade|set null|no action]
)
 create table order1(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id)) ENGINE=innodb;
alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade, ENGINE =innodb;
alter table order1 add constraint `bk` foreign key(u_id) references user(id) on delete cascade on update cascade,ENGINE=InnoDB;  指定外键名称
show create table order1;

删除外键
语法 ：alter table 表名 drop foreign key 约束（外键）名称
alter table order1 drop foreign key order1_ibfk_1;
show create table order1; 

视图:

视图就是一个存在于数据库中的虚拟表。
视图本身没有数据，只是通过执行相应的 select 语句完成获得相应的数据

视图能够对机密数据提供安全保护
用现有的视图进行查询可以极大的减小查询语句的复杂程度

创建视图
语法：create view 视图名称（即虚拟的表名） as select 语句
create view bc as select b.bName ,b.price ,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId ;

查看视图创建信息
show create view bc\G

查询视图中的数据
select * from bc where price<40\G

更新或修改视图
alter view 视图名称（即虚拟的表名） as select 语句
update view 视图名称（即虚拟的表名）set
alter view bc as select b.bName ,b.publishing ,c.bTypeId from books as b left join category as c on b.bTypeId=c.bTypeId;
select * from bc\G

删除视图
drop view 视图名
drop view bc;

总结：
字段修饰符
清空表记录
索引
外键
视图