MySQL基础（2）-白红宇

MySQL基础（2）

阅读量：5939 次

发布时间：2019-06-19

本文共 4626 字，大约阅读时间需要 15 分钟。

一. 字段约束条件

Not Null 非空值

Key 索引

Default 字段的默认值，默认值的值是null

create table t8 (    name  char(10)   not  null ,    age   tinyint(2) unsigned  default  22 ,    sex   enum("boy","girl","no") not  null  default "boy",    likes set("it","book","film","game")  default "it,book");insert into  t8(name) values("bob");insert into  t8  values("jim",29,"no","film,game");insert into  t8  values("",NULL,"boy",NULL);insert into  t8  values("NULL",NULL,"boy",NULL);insert into  t8  values(null,NULL,"boy",NULL); //报错

二. 修改表结构

alter table 表名执行动作；

执行动作：

add 添加字段

drop 删除字段

modify 修改字段类型

change 修改字段名

rename 修改表明

1. add 添加新字段

add 字段名类型（宽度）约束条件；

add 字段名类型（宽度）约束条件，add 字段类型（宽度）约束条件；

add 字段名类型（宽度）约束条件 first；

add 字段名类型（宽度）约束条件 after 字段名；

alter table t8 add mail varchar(30) not null default "student@tedu.cn";alter table t8 add qq varchar(11) after sex, add tel char(11) after age, add stu_id char(7) first;

2. drop 删除已有字段

drop 字段名;

alter table t8 drop stu_id,drop sex;

3. modify 修改字段类型 (当和字段已存储的数据冲突时，不允许修改)

modify 字段名类型（宽度）约束条件;

modify 字段名类型（宽度）约束条件, modify 字段名类型（宽度）约束条件;

alter table t8 modify tel varchar(11);alter table t8 modify qq  char(11) after name;

4. change 修改字段名

change 原字段名新字段名类型（宽度）约束条件；

alter table  t8 change tel iphone varchar(11);

5. rename 修改表名

alter table 原表名 rename [to] 新表名；

alter table  t8 rename  stuinfo;

二. MySQL索引

1. 什么是索引？

对记录集的多个字段进行排序的方法

索引类型：Btree(默认)、B+tree、hash

2. 索引的优缺点

优点：加快查询记录的速度

缺点：占用物理存储空间，会减慢写速度（update delete insert）

3. 索引类型

index 普通索引

unique 唯一索引

primary key 主键

foreign key 外键

4. 查看索引

desc 表名; //查看key字段

show index from 表名;

5. index 普通索引

(1) 使用规则

一个表中可以有多个INDEX字段

字段的值允许有重复，切可以赋NULL值

经常把做查询条件的字段设置为INDEX字段

INDEX字段的KEY标志是MUL

(2) 建表时创建索引

create table t9(    name  char(10),    age   tinyint(2) unsigned,    sex   enum("m","w") default "m",    index(name),    index(sex)); show index from t9\G;    //结果竖着显示

(3) 在已有表里创建索引

create index 索引名 on 表名(字段名)；

create index aaa on t9(name);

(4) 删除索引

drop index 索引名 on 表名;

drop  index  aaa  on  t9;

6 primary key 主键

(1) 使用规则

一个表中只能有一个主键

不能重复且不能空

如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。

通常与 AUTO_INCREMENT 连用

经常把表中能够唯一标识记录的字段设置为主键字段（编号）

主键字段的KEY标志是PRI

(2) 建表时创建主键

create table t10(    stu_id  char(9),    name    char(10),    age     tinyint(2) unsigned,    sex     enum("m","w") default "m",    primary key(stu_id)); create table t10(    stu_id  char(9) primary key,    name    char(10),    age     tinyint(2) unsigned,    sex     enum("m","w") default "m");

(3) 在已有表创建主键

alter table 表名 add primary key(字段名)；

alter table t10 add primary key(name);

(4) 删除主键

alter table 表名 drop primary key;

【注】如果主键有auto_increment字段，需要先移除该字段，再删除主键。

alter table t10 drop primary key;

(5) 复合主键

create table t11(    clientip  varchar(15),    serport   smallint(2),    status    enum("allow","deny") default "deny",    primary   key(clientip,serport));insert into t11 values("1.1.1.1",21,"deny");insert into t11 values("1.1.1.1",80,"allow");insert into t11 values("2.1.1.1",80,"deny");alter table t11 drop primary key;

在已有表里创建复合主键：

alter table t24 add primary key(字段名列表);

(6) 主键为数值类型与auto_increment连用

create table t12(    id   int(2) zerofill  primary key auto_increment,    name char(10),    age  tinyint(2) unsigned,    pay  float(7,2) default 18800);insert into t26(name,age,pay) values("bob",29,20000);insert into t26(id,name,age,pay) values(9,"bob",29,20000);

7. foreign key 外键

(1) 功能

给当前表的指定字段赋值时，字段的值在指定表中字段值里选择。

(2) 条件

表的存储引擎必须是innodb

字段的类型必须相同

被参考字段必须是索引的一种（通常是primary key）

create table cwb(    cwb_id int(2)  primary key  auto_increment,    name   char(10),    pay    float(7,2) default  18800)engine=innodb;create table xstab(    stu_id  int(2),      name    char(10),    foreign key(stu_id) references cwb(cwb_id) on update cascade on delete cascade)engine=innodb;insert into xstab values(9,"lucy");insert into xstab values(1,"bob");update cwb  set cwb_id=8  where cwb_id=1;delete from cwb where cwb_id=3;

(3) 删除外键

show create table 表名;

alter table xstab drop foreign key 外键名;

alter table  xstab drop  foreign key xstab_ibfk_1;

(4) 添加外键

alter table xstab add foreign key(stu_id) referencescwb(cwb_id) on update cascade on  delete  cascade;

8. unique 索引

(1) 使用规则

一个表中可以有多个UNIQUE字段

对应的字段值不允许有重复

UNIQUE字段的KEY标志是UNI

UNIQUE字段的值允许为NULL，当将其修改为不允许为NULL时与主键相同

create table t12(    name   char(10),      sh_id  char(18),    dirver char(10),    unique(sh_id),    unique(dirver));insert into t12 values("bob","123456","abcdef");insert into t12 values("bob","123456",null);

(2) 在已有表里创建unique