目录
1.1.1、字段类型是not null,为什么可以插入空值?
4.3、方法二:通过alter table创建外键和级联更新,级联删除
1、字段修饰符(约束)
1.1、null和not null 修饰符
有not null修饰的字段不能插入null值,可以插入“空值”。
1.1.1、字段类型是not null,为什么可以插入空值?
空值是不占用空间的,null是占用空间的。比如,一个杯子,空值代表是真空的,null代表杯子中装满了空气,虽然看起来被试是空的,但是里面是有空气的。
1.1.2、为什么空值的效率比null高?
null占用空间,所有mysql在进行比较的时候,null会参与字段比较,对效率有一部分影响
1.2、default设定字段的默认值
如果字段没有设定默认值,并且这个字段有not null修饰,那么当给表插入数据时,没有给该字段插入值,mysql会报错。
1.3、auto_increment自动增长
auto_increment只能修饰int字段。MySQL自动为该字段生成一个没有用过的数(每次在最大id值的基础上加1。特例:如果目前最大id是34,然后删除34,新添加的会是35)。经常用来修饰主键,因为它可以为每条记录创建一个唯一的标识符。
2、清除表中的记录
2.1、方法一:delete
delete不加where条件,清空所有表记录。但是delete不会清零auto_increment值。
语法:delete from 表名;
例如:delete from items;
2.2、方法二:truncate
truncate删除表的所有记录,并清零auto_Increment值。新插入的记录从1开始。
语法:truncate table 表名;
例如:truncate table items;
3、索引
3.1、索引介绍
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表中所有记录的引用指针。更通俗的说数据库索引好比一本书前面的目录,能够加快数据库的查询速度。
优点:加快搜索速度,减少查询时间。
缺点:
1、索引是以文件存储的,如果索引过多,占用磁盘空间较大。而且它影响增删改执行时间。
2、索引中数据必须和数据表数据同步:如果索引过多,当表中数据更新的时候,索引也要同步更新,这就降低了增删改的效率。
3.2、索引类型
1、普通索引
2、唯一性索引
3、主键索引
4、复合索引
3.2.1、普通索引
最基本的索引,不具备唯一性,就是加快查询速度
3.2.1.1、创建普通索引:
方法一:创建表时添加索引:
create table 表名(
列定义......,
index 索引名称(字段),
index 索引名称 (字段)
);
注:可以使用key,也可以使用index。i索引名称,可以加,也可以不加,不加索引名默认使用字段名作为索引名。
create table demo(id int(4),name varchar(20),pwd varchar(20),index(pwd));
create table demo(id int(4),name varchar(20),pwd varchar(20),key(pwd));
create table demo(id int(4),name varchar(20),pwd varchar(20),index index_pwd(pwd));
create table demo(id int(4),name varchar(20),pwd varchar(20),key index_pwd(pwd));
注意:index和key在这里是相同的。
扩展:index和key的区别:
mysql的key同时具有constraint(偏重于约束和规范数据库的结构完整性)和index(辅助查询用的)的意义,index只是单纯的索引,用于辅助查询。可以形象的比喻,constraint拥有双重国籍,中国国籍和美国国籍,所以constraint即是中国人又是美国人,而index只拥有中国国籍,它只是单纯的中国人。
创建表时创建key有3种方式(适用于唯一索引、主键索引;下面以创建主键索引为例):
(1)在字段级以key方式建立, 如 create table t (id int primary key);
(2)在表级以constraint方式建立,如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
(3)在表级以key方式建立,如create table t(id int, primary key (id)); #可以看作是第二种方式的简写
在前面创建一种,在后面创建两种;
前两种不适用于创建普通索引,第三种适用于创建普通索引。
创建表时创建index(适用于唯一索引)
(2)在表级以constraint方式建立,如create table t(id int, CONSTRAINT uk_t_id unique index(id));
(3)在表级以key方式建立,如create table t(id int, unique index(id)); #可以看作是上一种方式的简写
总结:
创建普通索引可以用index或者key;
创建唯一索引可以用index或者key;
创建主键索引不能用index,只能用key;
一张图了解创建表时创建索引的所有方式:
注意:创建唯一索引时,index或key可以省略。
方法二:当表创建完成后,使用alter为表添加索引:
先删除索引:
alter table demo drop key pwd;此处的的pwd指的是索引的名称
再用alter添加:
alter table demo add key(pwd)
3.2.1.2、查看索引:
1、desc demo
mul:普通索引;可以是单列普通索引,或者是复合索引中非唯一索引的第一列,又或者是复合索引中唯一索引的非前导列;列值可以重复,可以为空值,可以为null(应尽量避免,对NULL列加索引需要额外的磁盘空间,会使索引、索引统计和值的比较运算更加复杂)。
uni:唯一索引;可以是单列唯一索引,或者是复合索引中唯一索引的第一列;列值不可以重复,可以为空值或者null(尽量避免)。
pri:主键索引;该列是主键索引或者主键索引的组成部分;列值不可以重复,可以为空值但是不能为null;
2、show index/keys from 表名;
3.2.1.3、删除普通索引:
alter table 表名 drop key/index(索引名);
3.2.1.4、补充
使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句的。便于分析你的查询语句或者是表结构的性能瓶颈。
3.2.2、唯一索引
唯一索引与普通索引基本相同,索引列的值可以为null或空值,但是有一个区别:唯一索引索引列的多有值都只能出现一次,即必须唯一,不能有重复的值,用来约束内容。
3.2.2.1、创建唯一索引
唯一索引的创建同样有两种方法:
方法一:创建表时添加唯一索引
create table 表名(
列定义,
unique key 索引名1(字段),
unique key 索引名2(字段)
);
注意:唯一索引常用在值不能重复的字段上,比如说用户名,电话号码,身份证号。
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.2.2.2、删除唯一索引
语法:alter table 表名 drop key/index 索引名;
3.2.3、主键索引
设置主键就会在该列上自动创建主键索引,每个表最多只能有一个主键。主键列要求列的所有内容必须唯一,主键索引是一种特殊的唯一索引,但不允许有空值。
not null + 唯一索引 = 主键索引
create table t(id int not null unqiue);
desc t;
此时索引列显示的是pri而不是uni
3.2.3.1、创建主键索引
方法一:创建表创建主键索引
create table demo5(id int(4) not_null auto_increment primary key,name varchar(20) default null);
或者
create table demo5(id int(4) not_null auto_increment,name varchar(20),primary key(id));
方法二:创建表后添加
不推荐使用这种方法,如果生产的数据无法保持唯一,创建主键会报错,主键索引一般在设计数据库时,创建表来添加。
先删除主键索引,再创建
alter table demo5 drop primary key;
删除遇到这种报错,是auto_increment的原因。先取消自增长
alter table demo5 change id int(4) not null;
alter table demo5 drop primary key;
3.2.3.2、删除主键索引
alter table 表名 drop primary key;(因为一张表种只能有一个主键索引,所以删除主键索引不用指定索引名)
3.2.4、复合索引
索引可以包含一个、两个或更多个列。两列或更多个列上的索引被称作复合索引
例:创建一个表,存放服务器允许或拒绝的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));
复合主键必须有一个字段的值是不能重复的。
为了进一步提升MySQL的效率,就要考虑建立复合索引。
create table mytable(id int not null,username varchar(16) not null,city varchar(50) not null,age int not null);
把username,city,age建到一个索引里。
alter table mytable add index username_city_age(username,city,age);
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
username,city,age username,city username 为什么没有city,age这样的组合索引呢?这是因为MySQL复合索引“最左前缀”的结果。就是只从最左边的列开始组合。并不是只要包含这三列的查询都会用到该索引。
如果对多列进行索引(复合索引),列的顺序非常重要,MySQL只能对索引最左边的前缀进行有效的查找。
下面的几个SQL就会用到这个组合索引:
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"
3.3、索引设计原则
1、索引并非越多越好,每个额外的索引都要占用额外的磁盘空间,并降低增删改的性能,因此,索引越多,所花的时间越长。
2、数据量不大不需要建立索引
3、在where子句种出现的字段,常排序(order by)和分组(grou by)的列需要建立索引。
4、唯一性约束对应使用唯一性索引
5、MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符开头的情形)。
4、外键约束
4.1、什么是外键约束
foreign key 就是表与表之间某种特定的关系,由于这种关系的存在,我们能让表与表之间的数据,更加的完整,关联性更强。
关于完整性,关联性我们举个例子:
有两张表,一张是用户表,一张是订单表:
1、如果我删除了用户里的用户,那么订单表里面与这个用户有关的数据,就变成了无头数据,不完整了。
2、如果我在订单表里面,随便插入一条数据,这个数据在用户表里面,没有与之对应的用户,这样数据也不完整了。
如果有外键的话,就方便多了,可以不让用户删除数据,或者删除用户的话,通过外键同样删除订单表里面的数据,这样也能使数据完整。
如果表A的主键字段是表B中的字段,则该字段称为表B的外键,表A成为主表,表B成为从表。外键是用来实现参照完整性的,外键约束方式可以让两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的引用完整性和一致性。
4.2、创建外键约束方法一:
外键:每次插入或更新时,都会检查数据的完整性。
4.2.1、语法:
create table 表名(
...,
[constraint [外键名]] foreign key [字段名1,字段名2]
references [主表名] (主键字段1,主键字段2)
[on delete cascade|restrict]
[on update cascade|restrict]
);
4.2.2、关于参数的解释:
restrict:拒绝对父表的删除或更新操作。
cascade:从父表删除或更新则自动删除或更新字表中匹配的行。
on delete cascade和on update cascade可以同时使用。
注意:on delete update是级联更新的意思,on delete cascade是级联删除的意思,意思就是说当你更新或删除主键表,那外键表也会跟随一起更新或删除。
例如:
创建主表,并将sid设为主键
create table stu(sid int primary key,name varchar(50) not null);
创建从表,并将sid设为外键
create table score(score double,sid int,constraint fk_stu_score_sid foreign key(sid) reference stu(sid));
注:创建成功,必须满足以下4个条件:
1、确保参照的表和字段都存在
2、组成外键的字段被索引。
3、必须使用engine指定存储引擎为:innodb
4、外键字段和关联字段,数据类型必须一致。
4.2.3、创建一个数据库,包含用户表和订单表
create database market;
use market;
create table `user`(id int(11) not null auto_increment primary key,name varchar(50) not null default '',sex int(1) not null default '0') 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(uid) references user(id) on delete cascade on update cascade) engine=innodb;
注:
1、on delete cascade on update cascade 添加级联删除和更新。
2、确保参照表user中id字段存在。组成外键的字段u_id被索引。
3、外键字段和关联字段,数据类型必须一致。
4.2.4、插入测试数据
insert into `user`(name,sex) values('zs',1),('ls',2),('ww',1);
insert into `order`(u_id,username,money) values(1,'zs',234),(2,'ls',146),(3,'ww',256);
4.2.5、测试级联删除:
delete from user where id=1;删除user表中id为1的数据
再查看order表
select * from `order`;
发现order表中u_id=1的数据被同时删除了
4.2.6、测试级联更新:
update user set id=6 where id=2;
再查看order表
发现order表中u_id=2同步更新为u_id=6
4.2.7、测试数据完整性
在order表中插入一个不存在的用户
insert into `order` (u_id,username,money) values(7,'tom',246);
报错是因为外键约束,order表受user表的约束,在order表里面插入一条数据u_id为7用户,在user表里面根本没有,所以插入不进去。
解决:
先在user表插入id为7的用户
insert into user values(7,'tom',10) ;
再再order表中插入u_id为7的用户
insert into `order`(uid,username,money) values(7,'tom',346);
4.3、方法二:通过alter table创建外键和级联更新,级联删除
语法:
alter table 表名 add
[constraint[约束名称]] foreign key(外键字段,...) references 主表(参照字段,...)
[on update cascade]
[on delete cascade]
创建order1表:
create table order1(o_id int(11) auto_increment primary key,u_id int(11) default '0',username varchar(50),money int(11),index(u_id)) engine=innodb;
创建外键约束:
alter table order1 add foreign key(u_id) reference user(id) on update cascade on delete cascade,engine=innodb;
或者:
alter table order 1 add constraint 'order_fk' foreign key(u_id) references user(id) on delete cascade on update cascade,engine=innodb;
4.4、删除外键
语法:alte table 表名 drop foreign key 约束(外键)名
alter table order1 drop foreign key order_fk;