定义主要就是DDL语句,也叫数据定义语句(主要由create,alter,drop和truncate(性能优于delete)四个关键字完成)。修饰主要有以下几种操作:加约束,索引和视图,定义的部分都结合到修饰里来说,这里主要说说修饰
数据库约束
约束概述
NOT NULL:非空约束,指定某列不得为空。
UNIQUE:唯一约束,指定某列或者几列的组合不能重复。
PRIMARY KEY:主键:指定该列的值可以唯一的标识该记录。例如一个学生有很多属性,可以把学号作为主键,这样重名也没关系
FOREIGN KEY:外键,指定该行记录从属于主表中的一条记录,主要用于保证参照完整性。
CHECK:检查,指定一个布尔表达式,用于指定对应列的值必须满足该表达式。
约束通常被写到系统表里,依据约束范围可以分为:单列约束:每个约束只约束一列。多列约束:每个约束可以约束多个数据列。依据约束指定时机可以分为:建表开始的时候为对应数据列指定约束。建表后创建,以修改的方式来增加约束。约束通常只能被建立和删除不能修改。
非空约束 NOT NULL
所有数据库中的数据类型都可以为null,空字符串和0含义并不等于null; 只能作为列级约束来使用
1,建表时确立非空约束
creat table TML
(
TML_id int not null, //id不能为空
TML_name varchar(255) default 'xyz' not null, //名字不能为空
TML_gender varchaer(2) null //gender是性别的意思
);
2,修改表时增加或删除非空约束
alter table TML
modify TML_id int null; //可以为空
modify TML_name varchar(255) default 'aaa' null;
##唯一约束UNIQUE约束
可以用于保证某列或者指定列组合不出现重复值,但可以出现多个null值。唯一约束既可以进行列级唯一约束,也可以进行表级唯一约束。如果只是为一列建立唯一约束那么只需要用列级,该唯一约束默认与列同名,并且会创建唯一索引。如果要为多列建立唯一约束或者是想自行制定约束名,那么需要使用表级唯一约束。
1,建表时创建唯一约束如下:
列级唯一约束:
create table tml
(
tml_id int not null,
tml_name varchar(255) unique
);
表级唯一约束:========================================
creat table tml
(
tml_id int not null,
tml_name varchar(255),
tml_gender varchar(255),
unique (tml_name), //使用表级约束语法建立唯一约束,李明这个名字在该姓名列必须只出现一次
constraint tml_nb unique(tml_gender) //使用表级唯一约束建立约束并且给该约束命名,两个属性各自都具有唯一性
)
creat table tml
(
tml_id int not null,
tml_name varchar(255),
tml_gender varchar(255),
constraint tml_verynb unique(tml_id,tml_name) //指定两列的组合不允许出现重复,即两个属性不能完全相同(15,李明),(16,李明)也可以
2,修改表时增加唯一约束用add增加表级唯一约束
alter table tml
add unique (tml_name,tml_id);
用modify为增加列级唯一约束
alter table tml
modify tml_name varchar(255) unique;
3,删除唯一约束
alter table tml
drop index tml_nb; //直接删除约束名
主键约束 PRIMARY KEY约束
主键约束相当于非空约束加唯一约束,主键约束的列既不能出现重复值,也不能出现null值,如果是多列组合的主键则要求每一列都不能有null值,但对于唯一性约束则只是要求其组合满足唯一约束条件。主键约束既可以用列级约束也可以用表级约束,但无论用户对约束做怎样的命名,主键约束名最终都是PRIMARY
1,建表时建立主键约束:
建立列级主键约束
creat table tml
(
tml_id int primary key,
tml_name varchar(255)
);
建立表级主键约束
create table tml
(
tml_id int not null,
tml_name varchar(255),
tml_gender varchar(255),
constraint tml_nb primary key(tml_id) //指定主键约束名对大多数数据库有效,但对mysql无效
);
建立表级组合主键约束
create table tml
(
tml_id int not null,
tml_name varchar(255),
tml_gender varchar(255),
primary key(tml_id,tml_name)//指定为主键约束后即使单个列没有非空约束也会自动具有非空约束
) ;
2,修改表时建立主键约束
建立列级主键约束
alter table tml
modify tml_id int primary key;
建立表级主键约束
alter table tml
add primary key(tml_id,tml_name);
3,删除主键约束
alter table tml
drop primary key;
4,设置自增长主键约束
自增长主键要求主键列数据类型必须为整型,通常用于设置逻辑主键列(没有任何物理信息,仅用于标识每行记录),向该表插入记录时,可以不为该列指定值,该主键列的值由数据库自动生成。
create table tml
(
tml_id int auto_increament primary key,
tml_name varchar(255),
tml_pass varchar(255)
);
外键约束FOREIGN KEY约束
几个概念
外键:是构建于一个表的两个字段或者两个表的两个字段之间的参照关系
主表:存放被参照对象的表。
从表:存放外键列的表。
几点规则
1,从表外键列的值必须在主表被参照列的值范围之内,或者为空。
2,外键约束通常定义于两个实体之间一对多(多的一端增加外键列)//一个老师对多个学生,在学生实体上添加外键列
3,当主表记录被从表参照时,主表记录不允许被删除,必须先把从表里该主表外键列全部删除才可以,除非在删除主表时级联删除从表所有信息
4,一对一的关系的时候,可以选择任意一方当作外键列,,如果是多对多的关系,则需要额外增加一个连接表记录他们的关联关系
5,从表外键参照的只能是主表的主键列或者是唯一键列(索引)
建立外键约束
1,建表时增加外键约束
采用列级约束语法建立外键约束 #为保证主表存在,必须先建立主表==================
create table tml(主表)
(
tml_id int auto_increment primary key,
tml_name varchar(255) not null,
);
creat table student(从表)
(
stu_id int auto_increment primary key,
stu_name varchar(255),
#指定java_tml 参照到tml_id列
java_tml int references tml_table(tml_id)
); //这种外键约束建立方法并没有什么用,仅仅是为了mysql保持兼容性而已
采用表级约束语法建立外键约束============================================
create table tml
(
tml_id int auto_increament primary key,
tml_name varchar(255) not null,
);
creat table student
(
stu_id int auto_increment primary key,
stu_name varchar(255),
#指定java_tml 参照到tml_id列
java_tml int ,
foreign key (java_tml) references tml_table (tml_id)
); //创建外键时如果没有指定名字,则默认名为table_name_ibfk_n,table_name 是从表的表名
表级语法显式的指定外键约束的名字=========================================
create table tml
(
tml_id int auto_increament,
tml_name varchar(255) not null,
primary key(tml_id)
);
creat table student
(
stu_id int auto_increment primary key,
stu_name varchar(255),
#指定java_tml 参照到tml_id列
java_tml int ,
constraint student_tml_fk foreign key (java_tml) references tml_table (tml_id) //为外键约束指定名称
);
采用表级语法建立多列组合的外键=========================================
create table tml
(
tml_id int ,
tml_name varchar(255) not null,
primary key(tml_id,tml_name) //首先在主表里建立联合主键
);
creat table student
(
stu_id int auto_increment primary key,
stu_name varchar(255),
#指定java_tml 参照到tml_id列
java_tml_name int ,
java_tml_id int ,
constraint student_tml_fk foreign key (java_tml_name,java_tml_id) references tml_table (tml_id,tml_id)
);
2,修改表时增加外键约束
不能使用列级,只能使用表级,你想啊,是添加了一个字段,约束给之前没有的字段往哪儿填去
alter table tml
add foreign key (java_tml_id,java_tml_name) references tml_table(tml_id,tml_name);
3,建立自关联的外键约束
例如员工分经理和普通员工一样
create table person
(
person_id int auto_increment primary key,
person_name varchar(255),
#本表的refer_id列参照到本表的person_id 列
refer_id int,
foreign key ( refer_id) references person_table(person_id)
);
4, 删除外键约束
只删除从表的外键约束
alter table tml
drop foreign key student_tml_fk: //删除其约束名即可
定义删除主表时级联删除从表外键或将其外键设置为null //不是删除语法,是定义删除的操作
级联删除从表外键
create table teacher
(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key (teacher_id)
);
creat table student
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key (java_teacher) references teacher_table(teacher_id )
on delete cascade
//定义级联删除,或者可以on delete set null把从表记录设置为空
);
CHECK约束
create table check
(
check_id int auto_increment,
check_name varchar(255),
check_salary decimal,
primary key (check_id),
#建立check约束
check(check_salary>0)
); //通常来说check约束并没有什么用
数据库索引
索引是存放在模式中的一个数据库对象,从属于数据表,作用是加速对表的查询(通过快速路径访问来访问数据,减少磁盘的I/O),索引在数据字典里独立存放,但不能独立 存在,必须从属于某个数据表。
创建(删除)数据库索引
自动:当表上定义foreign key,unique, primary key.时,系统会自动为其创建索引(当数据表被删除时,索引自动被删除)
手动:用户通过create index….语句创建索引(drop index….)
创建、删除索引语句
#创建表tml基于字段tml_id的查询速度
create index tml_tml_id_idx //注意命名规范
on tml(tml_id);
#同时对多列创建索引
create index tml_tml_id_idx2
on tml(tml_id,tml_name);
#删除tml表上tml_tml_id_idx2 索引
drop index tml_tml_id_idx2
on tml; //mysql只要求本表内索引不重名,所以建立索引时必须指定表
数据库视图
视图并非数据表,不能存储数据,只是数据的逻辑显示,所谓视图就是一条被命名的SQL查询语句
#创建视图的语法
create or replace view 视图名
as
subquery
*************如果该视图不存在,则创建视图,如果存在,则使用新视图替换已有,后面的subquery就是一个查询语句
#创建视图,通常对视图的操作仅限于查询,没有修改
create or replace view view_test
as
select teacher_name ,teacher_pass from teacher_table
with check option; //强制要求不能对该视图进行修改
#删除视图
drop view view_test;