笔记整理2.10-数据库结构约束-主键-索引-视图-级联更新-调优

null值和空值

create table worker(id int not null,name varchar(8) not null,pass varchar(20) not null);
mysql> insert into worker values(1,'HA','123456');
mysql> insert into worker values(1,'LB',null);
mysql> insert into worker values(2,'HPC','');
注：NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”

“空值” 和 “NULL”有什么不一样？
1、空值是不占用空间的
2、mysql中的NULL其实是占用空间的

为什么not null的效率比null高？ 
NULL会参与字段比较，所以对效率有一部分影响。

select * from table where column <> ''#排除空值和null

为字段指定默认的值

mysql> create table test2(name varchar(8) not null,dept varchar(25) default 'SOS');

 

 

清楚表中内容

mysql> delete from items;#清除表中所有记录 不会清零auto_increment值
mysql> truncate table items; #清除记录 新插入的记录从1开始

 

索引是记录的指针。更通俗的说，数据库索引是目录，能加快查询速度。

优点：为了加快搜索速度，减少查询时间 。
缺点：
1 .索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。
2.索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。
索引的类型
1、普通索引
2、唯一性索引
3、主键索引（主索引）
4、复合索引

1.普通索引
最基本的索引，不具备唯一性，就是加快查询速度
创建普通索引:
create table 表名(
列定义
index 索引名称 （字段）
index 索引名称 （字段）
)

create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );#注意:index和key是相同的
create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );

 当表创建完成后，使用alter为表添加索引：
alter table 表名 add index 索引名称 （字段1，字段2.....）;

注：如果Key是MUL, 就是一般性索引，该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。就是表示是一个普通索引。

mysql> alter table demo drop key pwd; #删除索引 注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段
mysql> alter table demo add key(pwd);#字段修改添加索引

 

2.创建表时，加唯一索引--
必须唯一,唯一性允许有NULL值<允许为空>。
create table 表名(
列定义：
unique key 索引名 （字段）;
)

mysql> alter table demo3 drop key uName;#删除唯一索引
mysql> alter table demo3 add unique(uName);#添加唯一索引

3.主键索引
查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。
主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空

create table demo5( id int(4) not null auto_increment, name varchar(20) default null,primary key(id));

创建表后添加<不推荐>修改主键索引 如果生产的数据无法保证唯一，创建主键报错

mysql> alter table demo5 change id id int(4) not null; 先取消自增长

mysql> alter table demo5 drop primary key; #再删除主键-删除报错就取消auto_increment就是上面的操作

4.复合索引

mysql> alter table demo5 change id id int(4) not null primary key auto_increment;

create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum('deny','allow') not null, primary key (host,port)); #联合主键

mysql> insert into firewall values('10.96.52.46',22,'deny');
mysql> insert into firewall values('10.96.52.46',21,'allow');
mysql> insert into firewall values('10.96.52.46',21,'allow');

建表的时候如果加各种索引，顺序如下：
create table 表名（字段定义，PRIMARYKEY (`bId`),UNIQUE KEY `bi` (`bImg`),KEY `bn` (`bName`),KEY `ba` (`author`))

5.全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。

它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，
然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

从MySQL 5.7.6开始 MySQL内置了ngram全文检索插件，用来支持中文分词
你的表当前默认的存储引擎:
mysql> show create table firewall;#查看表引擎

方法一：创建表时创建
create table 表名（
列定义，
fulltext key 索引名 （字段）;
）
方法二：修改表时添加
alter table 表名 add fulltext 索引名 （字段）;
ALTER TABLE `books` ADD FULLTEXT [索引名] (`author` )

索引设计原则：
1、索引并非越多越好；
2、数据量不大的不需要建立索引 ；
3、列中的值变化不多不需要建立索引 row id；
4、经常排序（order by 字段）和分组（group by 字段）的列需要建立索引
select a.bTypeId,(select b.bTypeName from category b where a.bTypeId = b.bTypeId) bn,count(*) from books a group by bTypeId;
5、唯一性约束对应使用唯一性索引

 

 一、创建外键约束：
外键： 每次插入或更新时，都会检查数据的完整性。
方法一：通过create table创建外键
语法：
create table 数据表名称(
...,
[CONSTRAINT [约束名称]] FOREIGN KEY [外键字段]
REFERENCES [外键表名](外键字段，外键字段2…..)
[ON DELETE CASCADE ]
[ON UPDATE CASCADE ]
)
关于参数的解释：
RESTRICT: 拒绝对父表的删除或更新操作。
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用
注意：on update cascade是级联更新的意思，on delete cascade是级联删除的意思，
意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。

精简化后的语法：
语法：foreign key 当前表的字段 references 外部表名 （关联的字段）ENGINE =innodb

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

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;
#创建时，如果表名是sql关键字，使用时，需要使用反引号``

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;

 

注：
1:on delete cascade on update cascade 添加级联删除和更新：
2: ：确保参照的表user中id字段存在。 组成外键的字段u_id被索引。 必须使用type指定存储引擎为：innodb。
外键字段和关联字段，数据类型必须一致。

mysql> insert into user(name,sex)values('HA',1),('LB',2),('HPC',1);
mysql> insert into `order` (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
mysql> select * from user;
mysql> select * from order;
mysql> delete from user where id=1; 删除user表中id为1的数据
mysql> update user set id=6 where id=2;

在order里面插入一条数据u_id为5用户，在user表里面根本没有，所以插入不进去
mysql> insert into user values(5,'Find',1);
mysql> insert into `order` (u_id,username,money)values(5,'Find',346); 这里u_id 只能是5

方法二：通过alter table 创建外键和级联更新，级联删除
语法：
alter table 数据表名称 add
[constraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...)
[on update cascade|set null|no action]
[on delete cascade|set null|no action]
)

mysql> 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;

mysql> 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 约束（外键）名称

mysql> alter table order1 drop foreign key bk;

mysql> show create table order1;

二、视图
创建
create view视图名称（即虚拟的表名） as select 语句
更新修改视图
alter view视图名称（即虚拟的表名） as select 语句。
update view视图名称（即虚拟的表名）set

mysql>use book;

mysql> 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

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 ;
update bc set bName='HA' where price=34;
drop view bc;删除视图

 

表结构优化

不允许有null值
合理规划表结构，用适当的数据类型
合理使用索引(查询多的表做索引 太多影响更新速度)
计划任务自动优化数据

0 3 * * * mysqlcheck -uroot -p123456 -r -o -A > /dev/null 2>&1 每天3点优化