1.表结构调整(alter table)
1)添加字段
- 在最后添加字段：alter table 表名 add 字段名 类型
- 在最前添加字段：alter table 表名 add 字段名 类型 first
- 在指定位置添加字段：alter table 表名 add 字段名 类型 after 字段名
eg:
create table student(stu_no varchar(32),stu_name varchar(128));
2)添加字段
alter table student add age int;--添加到最后
alter table student add id int first;--添加到最前
--在stu_name 后面添加tel_no字段
alter table student add tel_no varchar(32) after stu_name;
3)修改字段
- 修改类型:alter table 表名 modity 字段名 类型(宽度)
-修改名称:alter table 表名 change 原字段名 新字段名 类型(宽度)
-eg:
--修改学生名称长度64
alter table student modify stu_name varchar(64);
--修改age 为stu_age
alter table student change age stu_age int;
4)删除字段
- 语法：alter table 表名 drop 字段名
- eg：alter table student drop id;
2.约束(constraint)
1)什么是约束
- 为保证数据正确性,完整性,一致性,数据必须遵循的规则
2)约束类型
-非空约束：字段的值不能为空
-唯一性约束:字段值唯一
-主键约束:字段作为主键,非空,唯一性约束
-默认值：未填写值时，设置默认值
-自动增加：字段值自动增加
-外键约束
3)非空约束(not null)
- 指定字段的值不能为空,如果插入时该字段值为空,则报错,无法插入
-语法:字段名称 数据类型(宽度)not null
- eg：
creat table customer(cust_no varchar(32) not null,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;
--插入一笔带空值的数据
insert into customer(cust_no,cust_name)values('c0001','Jerry');
4)唯一约束(unique)
- 该字段的唯一,不重复
- 语法:字段名称 数据类型 unique
-eg: create table customer(cust_no varchar(32)unique,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;
insert into customer_new values('c001','Jerry','123456');
insert into customer_new values('c001','tom','123456');
5)主键(primary key,简写pk)
- 主键用来唯一标识表中的一笔记录,非空,唯一主键和一笔数据有唯一对应关系，
一个表最多只有一个主键
可以单个字段作为主键,也可以多个字段共同构成主键
-语法:字段名称 类型(宽度)primary key
-eg:
create table customer0(cust_no varchar(32)primary key,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;
insert into customer0 values('c001','Jerry','123456');
insert into customer0 values(Null,'tom','123456');
6)默认值(default)
- 指定某个字段的默认值,如果插入一笔数据,该字段没有值,系统自动填写一个默认值
- 语法:字段名称:类型(宽度)default 默认值
eg:
create table customer1(cust_no varchar(32)primary key,cust_name varchar(128) not null,tel_no varchar(32) not null,status tinyint default 0)default charset=utf8;
insert into customer1(cust_no,cust_name,tel_no) values('c001','Jerry','123456');
7)自动增长(auto_increment)
- 指定为自动增长的字段,插入时不需要设置值,系统在最大值基础上加1,可以和主键共同使用
-语法:字段名称 数据类型(宽度)auto_increment
-示例：
create table ai_test(id int primary key auto_increment,name varchar(32));
insert into ai_test values(null,'tom');
8)外键约束
- 什么是外键:在当前表中不是主键,在另一个表中是主键
- 外键的作用:保证数据一致性,完整性
- 使用外键的条件
a)表的存储引擎类型为innodb
b)被参照字段在外表中必须是主键
c)当前表和外表中类型必须一致
-语法
constraint 外键名称 foreign key (当前表字段)
reference 参照表(参照字段)
-eg：
create table account(acct_no varchar(32)primary key,
cust_no varchar(32) not null,
constraint fk_cust_no foreign key(cust_no) references customer1(cust_no)
)default charset=utf8;
--在account表中插入cust_no 为‘c0001’
-- 的数据,插入失败(account参照了一个不存在的实体)
insert into account values('62234560001','c0001');
--在customer表中插入一笔数据(cust_no为‘c0001’以满足account参照完整性 ,在执行上面的插入语句,则可以成)
insert into customer(cust_no,cust_name,tel_no)values('c0001','Jerry','13511220003');
--删除customer 表中cust_no 为‘c0001’实体，
--报错,因为删除以后又会造成参照不完整
delect from cu
3.索引
1)什么是索引
- 索引是提高查询效率的一种技术(相当于一本字典的索引或目录)
- 索引是一种单独存放的数据结构,包含着数据表中所有的记录的引用指针
- 根据索引能快速找到数据所在位置
- 通过避免全表扫描提高检索效率
2)索引类别
- 普通索引,唯一索引
- 单列索引,组合索引
3)如何创建索引
- 语法:
index|unique|primary key(字段名称)
- 说明:
index :创建普通索引
unique：创建唯一索引
primary key :主键,自动成为唯一索引
- 示例：
创建交易流水表,在流水号上创建唯一索引
create table acct_trans_detail(trans_sn varchar(32) not null,
trans_date datetime not null,acct_no varchar(32)not null,
trans_type int null,
amt decimal(10,2) not null,
unique(trans_sn),
index(trans_date)
);
insert into acct_trans_detail values('20180101',now(),'622000001',1,1000);
show index from acct_trans_detail;
show index from acct_trans_detail\G
- 示例:通过修改的方式创建索引
在acct_trans_detail表acct_no字段上创建普通索引
acct_trans_detail 表名
acct_no 字段名称
alter table acct_trans_detail add index idx_acct_no(acct_no);
或
create index idx_acct_no on acct_trans_detail(acct_no);
4)删除索引
- 语法:drop index 索引名称 on 表名
- 示例：drop index idx_acct_no on acct_trans_detail;
5)索引的优点
提高查询效率
唯一索引 能够保证数据的唯一性
在使用分组,排序等字句时,能提高效率
缺点：
索引需要额外的存储空间
维护索引结构需要额外的开销
会降低增,删,改的效率
6)索引使用原则
- 使用恰当的索引，索引不是越多越好
- 避免对经常更新的表使用过多索引
- 在经常作为查询条件的字段上建立索引
-字段值太少不宜使用索引(如性别，状态)
-主键和唯一索引查询效率较高
-在经常排序的字段上使用索引
- 数据量太少不适合使用索引
- 二进制类型字段不适合使用索引
4.表的复制,重命名
1)复制
- 完全复制
create table acct_new select * from acct;
- 部分复制(只复制满足条件的)
create table acct_new select * from acct where balance<2000;
- 只复制结构,不复制数据(没有满足条件的数据)
create table acct_new select * from acct where 1=0;
注意:该方法复制表,不会复制建的属性
2)重命名
- 格式：
alter table 原表名 rename to 新表名
- 示例：
alter table acct rename to acct_new;
作业:
1.修改表orders,在order_id 添加主键
2.在cust_id，order_date。products_num字段添加非空约束
3.在status字段上添加默认值,默认为1
4.在order_date字段上添加普通索引
1.alter table orders modify order_id varchar(32) primary key;
或
alter table orders add primary key(order_id)
2.
alter table orders modify cust_id varchar(32) not null;
3.
alter table order modify status enum('1','2','3','4','5','6','9') default 1;
4.
creat index idx_order_date on orders(order_date);
