Mysql(三)约束

一、视图

      视图是虚拟的数据表，本身不存储数据，而是提供数据的逻辑 展示。

      1、创建视图

create view stu_view as
select s1.id, s1.name, s2.room, s2.stay_time
from student s1, stay s2
where s1.id = s2.id;

创建视图后，就可以像查询数据表一样查询视图。  视图的字段就是我们从数据表中查询而来的字段。

select id from stu_view   desc stu_view
      2、修改视图

alter view stu_view as
select s1.id, s1.name, s2.room, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id;
select * from stu_view

　　3、删除视图

drop view stu_view;

       4、 在修改视图时，我们可以不使用alter，而是or replace。 在视图不存在时，创建视图，当视图存在时，替换视图。

create or replace view stu_view as
select s1.id, s1.name, s2.room, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id;

       5、视图只是一个预存储的查询语句。（as后面的查询语句）  当我们查询视图时，视图的查询语句就会展开。（就是从 视图存储的查询语句（结果集）中查询。

select * from stu_view
select * from (
select s1.id, s1.name, s2.room, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id) x;

　　视图的特征：

1 视图作为预存储的查询语句，不能提高性能。
2 视图可以简化我们的输入操作。
3 视图可以提供相同数据表的不同逻辑展示。

 

二、

    视图默认情况下，是以我们查询的字段命名。
#当视图的查询字段出现同名时（命名冲突）时，我们可以:
#1 使用别名 当使用别名时，视图字段会以别名来命名。
#2 自定义视图字段的名称

     1、别名

create or replace view stu_view as
select s1.id stu_id, s2.id stay_id, s1.name, s2.room, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id;
desc  stu_view;

　2、自定义视图字段的名称，指定的字段数量与查询的字段数量必须一致。

create or replace view stu_view(field1, field2) as
select s1.id, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id;
desc  stu_view;

 

三、索引

      索引是数据表中一个特殊的对象。

优点：索引可以加快记录的查询速度。

缺点：
#1 索引需要占用额外的硬盘空间。
#2 当数据表中记录发生变动时（增加，修改等），索引需要 进行重新变更（重新维护）。

索引使用的场合：

1 数据量很大（表中的记录多）
2 查询操作多
3 增加，删除等操作少。

      1、创建索引

create index stu_index on student(id);

create index 索引名 on 表名(字段1，字段2， ……字段n);     可以指定一列或者多列

      2、删除索引

drop index 索引名 on 表名

 

drop index stu_index on student

索引这就类似于图书的目录一样，如果我们想要查询某些内容， 我们可以从第一页开始，逐页进行查找，但这势必会耗费大 量的查找时间。但是，如果我们通过目录来查询，就可以快 速的定位到相关的页码上。 
 
四、数据库约束  

约束
约束，就是一种限制，其可以保证数据的正确性与完整性。
约束可以分为如下几类：
1唯一性约束
2非空约束
3主键约束
4外键约束
5检查约束

约束具有字段级语法与表级语法。

1、唯一性约束

唯一性约束保证约束列（一列或多列）的值不能出现重复。唯一性约束允许加入多个null值。因为MySQL中，null不等于任何值，包括其自身。因此，多个null值，彼此也是不等的。

字段级语法：

如果不存在创建表
create table if not exists t(
	id int primary key,
    age int unique
);

  如果表存在，删除。
  drop table if exists t;

　

insert into t(id, age) values (1, 1);
#错误，违法唯一性约束。
#insert into t(id, age) values (2, 1);
insert into t(id, age) values (3, null);
#允许插入多个null值。
insert into t(id, age) values (4, null);
select * from t;

表级语法：

唯一性约束也可以作用于多个字段。当作用于多个字段时， 只要多个字段的值不全相等，则认为是不重复的。

create table t(
	id int primary key,
    age int,
    name varchar(20),
    unique key (age, name)
);

与表级语法unique key (age, name)不同，对于表级语法， age，name只要有一个字段值不同即可，对于字段级语法， age与name两个字段值都不允许重复。

在建表之后增加唯一性约束：

对于建表之后加入的约束，一定要保证当前表中的数据  没有破坏该新增的约束，否则，约束就无法加入成功。

alter table t add unique key(age, name);
alter table t modify age int unique;

删除唯一性约束
alter table t drop index age;

2、非空约束

非空约束表示字段不允许为null值，该约束只有字段级语法，没有表级语法。

create table t(
id int primary key,
age int not null
);

在建表之后指定非空约束

create table t(
    id int primary key,
    age int
);
alter table t modify age int not null;　　

删除（取消）非空约束。
alter table t modify age int null;

3、主键约束

主键字段既不能为null，也不能重复。主键约束就是唯一性约束+非空约束。

字段级语法：

create table t (
id int primary key
);

表级语法：

create table t (
	id int,
    name varchar(10),
    primary key(id, name)
    #我们可以给主键命名，但仅仅是语法上支持，功能上不支持。
    #不管我们如何命名，MySQL主键名都是primary。
    #primary key pk(id, name)
);

　　当我们使用多个字段充当主键（联合主键），作为主键的多个字段只要不同时相同，就认为是不重复的，但是，每个字段都不允许为null。

建表之后增加主键：

create table t(
id int
);
alter table t add primary key(id);
alter table t modify id int primary key;

删除主键：

alter table t drop primary key;

4、外键约束：

如果B表中B1字段参照A表中的A1字段，则我们称B表为从表，A表为主表。B1字段的值或者为null，或者必须是A1字段中存在的值。A1字段必须是主键约束，或者是唯一性约束。

图书表
create table book(
id int primary key,
name varchar(30),
author varchar(30)
);

借书表
#字段级语法，MySQL仅支持语法，不支持功能。
create table borrow(
id int primary key,
book_id int references book(id),
borrow_person varchar(30)
);

#表级语法，MySQL支持
drop table if exists borrow;
create table borrow(
id int primary key,
book_id int,
borrow_person varchar(30),
foreign key(book_id) references book(id)
#也可以自定义外键的名字。
#constraint fk foreign key(book_id) references book(id)
);

insert into book(id, name, author) values(1, 'Java', 'abc');
insert into book(id, name, author) values(2, 'C++', 'def');
insert into book(id, name, author) values(3, 'C#', '张三');
insert into book(id, name, author) values(4, 'Hadoop', '小李');
select * from book;
insert into borrow(id, book_id, borrow_person)
values (1001, 3, '学生A');
#错误，违反外键约束。
#insert into borrow(id, book_id, borrow_person)
#values (1002, 5, '学生B');
insert into borrow(id, book_id, borrow_person)
values (1002, null, '学生B');

update book set id=10 where id=3
delete from book where id=3;

　　在建表之后增加外键：

create table borrow(
id int primary key,
book_id int,
borrow_person varchar(30)
);
alter table borrow add foreign key(book_id) references book(id);

指定外键名
alter table borrow add constraint fk   foreign key(book_id) references book(id);
删除外键

alter table borrow drop foreign key fk;

当主表的某条记录被从表所参照时，当主表记录修改或删除时，
从表的表现方式（行为）：
1restrict 当主表记录修改或删除时，拒绝执行。
2cascade 当主表记录修改或删除时，从表随之也修改或删除。
3set null 当主表记录修改或删除时，从表记录设置为null值。
4no action 等价于restrict
默认的行为为：restrict

   5、检查约束（MySQL仅支持语法，不支持功能）

create table t(
    id int primary key,
    age int,
    check (age > 0)
);
insert into t (id, age) values (1, -2);
select * from t;

　　

约束的表级语法与字段级语法。

1 相对于表级语法，字段级语法更简单些。
2 字段级语法只能作用于单个字段，而表级语法可以作用于多个 字段。例如：联合主键。
3 字段级语法不能为约束命名，而表级语法可以为约束命名。

 

五、union

union 用于合并多个结果集。

要求多个结果集的字段类型与字段数量一致。

union distinct 会去掉结果集中的重复记录。

union all 不会去掉结果集中的重复记录。

默认为union distinct

优先考虑使用union all（性能会好一些）。

select * from student
select * from stay;
select id from student union select id from stay;
select id from student union all select id from stay;

六、子查询

子查询即查询中还有查询（嵌套查询）
根据子查询出现的位置，可以将子查询分为两类：
1 出现在from之后，作为临时的数据表。
2 出现在where（having）之后，作为过滤条件。

子查询需要使用()括起。

子查询根据查询结果记录条数，可以将子查询分为：
1 单行子查询 返回一条记录
2 多行子查询 返回多条（一条）记录。

select id from (select id, name from student) x;
#查询与张三年龄相等的学生
#select id, name from student where age = 张三的年龄
#张三（id为1）的年龄？
#select age from student where id = 1;
#改进：
#select id, name from student where age =
#(select age from student where id = 1)
select * from student
select age from student where id = 2;
select id, name from student where age = 20;
select id, name from student where age =
(select age from student where id = 2);

　　

#=, >, <, >=, <=, <>(!=)，要求后面的子查询是单行子查询
#即最多只能返回一条记录。

#错误
#select id, name from student where age =
#(select age from student )

#any 表示任意一个（随便一个）
select id from student where id >
any (select id from stay)
#大于任意一个，相当于大于最小的。
select id from student where id >
(select min(id) from stay);

#all表示所有的
select id from student where id >
all (select id from stay);
#大于所有的，相当于大于最大的。
select id from student where id >
(select max(id) from stay);
#some 任意一个，等价于any
#in 在集合中（与集合中任意一个值相等）
select id from stay
select id from student where id in (1, 2, 3);
select id from student where id in
(select id from stay);
#exists 返回true或false，子查询有记录返回true，没有记录返回false。
select id from student s where exists
(select id from stay where id = s.id)