数据库原理-中级SQL

1.关于多表查询

select employee_id,department_name
from employee,departments;

以上的记录将会查询到2889条记录,在这2889条记录正是employee的条数*departments的条数

这是因为from如果后面直接接多个表名,那么就会对这多个表做笛卡尔积。

小技巧,如果要计算数字,可以通过以下的select语句进行计算

select 107*27 from dual;

1.1 什么是笛卡尔积(交叉连接)

笛卡尔积是一个数学运算,对于任意的集合X和Y,那么X和Y的笛卡尔积就是X和Y的所有可能的组合,也就是说是第一个元素来自于X,第二个元素来自于Y,对于多个的笛卡尔积,那么就是先两个表做笛卡尔积,然后再和第三个表做笛卡尔积。

而这个错误出现的原因是因为我们没有指定连接的条件,引擎并不知道如何过滤数据,因此在cross join的条件下是将左表与右表做笛卡尔积。

---

写对多个表的连接条件

方法1:编写连接条件,使用where条件对连接的字段进行限定。

select employee_id,department_name
from employee,department
where employees.department_id = departments.department_id;

注意:这时候有一种情况,就是当员工没有部门归属的时候,这时候它的department_id就是null,而null去做等值比较运算的时候,这时候就不会返回true的结果,这时候就不会产生这一条语句。这种运算结果不算一种错误,但是对于我们的查询预期来说,是不符合的。

从sql优化的角度来看,建议多表查询的时候,为每个字段都指明其所在的表,这是因为在多表查询的时候,底层会根据你查询的字段,在你指定的表中找这些字段,相当于有n张表,你最坏情况下就会去遍历这n张表,但是如果你指定了字段名,就可以从表中直接取出来。

那么在对于可读性的优化的时候,通常会采取一种手段,就是对这些表起别名,如果我们一旦在from中指定这些别名的时候,就意味着原来的名字就被你所指定的别名替代了。

1.2 等值连接与非等值连接

select e.last_name,e.salary,j.grade_level
from employee e,job_grade j
where e.salary >= j.salary and e.salary <= j.highest.salary;

简单来说等值连接就是在限定条件上是等号条件的,而非等值连接则是在限定条件上是没有等号条件的。

1.3 自连接与非自连接

查询员工及其管理者的ID和姓名

select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id = m.employee_id

简单来说,自连接其实就是表自己连接自己,这个通常应用在求取一个表的内部关系之中进行使用。

1.4 内连接和外连接

内连接,一个典型的例子如:

select employee_id,department_name
from employee,department
where employees.department_id = departments.department_id;

在这个查询中我们做了什么呢?要根据限定条件来看,我们限定条件中将笛卡尔积中的department_id相等的字段进行了等值运算,那么也就是说在内连接中,我们只取出了符合限定条件的所有记录,这种连接就称为内连接。

合并具有同一列的两个以上表的行,结果集中不包含一个表与另一个表不匹配的行。

外连接:一般用来求差集,如果想要选出某些属性与某些属性不存在的关联的特定个体的时候,就可以使用外连接。

两个表在连接过程中除了返回满足连接条件的行以外还返回左右表中不满足条件的行,这种连接称为左(或右)外连接。

• 左外连接:如果是左外连接,则连接条件左边的表称为主表,右边的表称为从表

简单来说,就是保留左表的全部,对于右表中没有关联到左表的,我们将其置为null

• 右外连接:如果是右外连接,则连接条件右边的表称为主表,左边的表称为从表

也就保留右表的全部,对于左表中没有关联到右表的部分,我们将其置为null

• 满外连接

查询所有的员工的last_name,department_name信息

select employee_id,department_name 
from employees e,department d
where e.`department_id` = d.`department_id` 

在SQL99中使用join on的方式可以实现内连接。

select last_name,department_name
from employee e inner join departments d #可以是inner join
on e.`department_id` = d.`department_id`#填写连接条件
join loacation l
on d.`location_id` = l.`location_id`;#如果是还要多连一张表,那么就这样写。

实现外连接

关于左外连接的实现

select last_name,department_name
from employee e left outer join departments d #可以是inner join
on e.`department_id` = d.`department_id`#填写连接条件

关于右外连接的实现

select last_name,department_name
from employee e right outer join departments d #可以是inner join
on e.`department_id` = d.`department_id`#填写连接条件

关于满外连接:Mysql是不支持的,而oracle是支持,只需要将left/right修改为full即可

1.5 7种SQL Joins的实现

合并查询结果:UNION,利用这个关键字,可以给出多条select语句,并将他们的结果组成单条结果集。合并时,对应的两个表的列数和数据类型必须相同而且相互对应,各个select语句之间使用UNION或者UNION ALL关键字分隔

select colum,... from table1
union [ALL]
select cloum,... from table2

执行UNION ALL语句所需要的资源是比UNION语句要少的,如果明确知道合并数据后的结果是不需要去重的,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以便提高查询的效率。

# 中图,这个做的是内连接,取交集的部分
select employee_id,department_name
from employee e join department d
on e.department_id = d.department_id
# 左上图,做左外连接
select employee_id,department_name
from employee e left outer join department d
on e.department_id = d.department_id
# 右上图,做外连接
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
# 左中图,就相当于左上图的基础上把有交集的部分给它给去掉,那么怎么去掉呢,也就是把左边没有关联的右边的给删除掉即可
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
where d.department_id is null;
# 右中图
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
where e.department_id is null;
# 左下图,union而且要去重,满外连接
# 方式1:左上图 UNION ALL 右中图
select employee_id,department_name
from employee e left outer join department d
on e.department_id = d.department_id
union all
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
# 方式2:左上 union 右上
select employee_id,department_name
from employee e left outer join department d
on e.department_id = d.department_id
union
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
# 方式3:左中 UNION ALL 右上
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
where d.department_id is null;
union all
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
# 右下,直接左中+右中
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
where d.department_id is null
union
select employee_id,department_name
from employee e right outer join department d
on e.department_id = d.department_id
where e.department_id is null;

1.6 using和natural join

对于两个表中的相同字段,如果我们想要直接将这些相同的字段连接起来,可以使用自然连接

select *
from employee e natural join department d;

它会帮你自动查询所有相同的字段,然后进行等值连接。

using可以替换连接条件,当连接字段相同的时候,就可以使用using对这些字段进行连接。

select *
from employee e join department d
using (department_id);

超过三个表禁止进行join,需要join的字段,数据类型保持绝对一致,多表查询的时候,保证被关联的字段有索引。

2. 约束

2.1 约束(constraint)概述

为什么需要约束?

数据完整性是指数据的精确性和可靠性,它是防止数据库存在不符合语义规定的数据和防止因错误信息的输入和输出造成无效操作或错误信息而提出的。

为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制。

• 实体完整性:例如,同一个表中,不能存在两条完全相同而且无法区分的记录
• 域完整性:例如,年龄范围(0~120)性别范围男/女
• 引用(参照)完整性:例如,员工所在部门,在部门表中要能找到这个部门
• 用户自定义完整性:例如用户名唯一,密码不能为空,以及其他用户自定义的完整性。

2.2 什么是约束?

约束是表级的强制规定。可以在创建表的时候规定约束create table时加上相关的constraint的规定。

2.3 约束的分类

• 单列约束
• 多列约束
• 约束的作用范围
  • 列级约束:将此约束声明在对应字段的后面
  • 表级约束:在表中所有字段声明完毕后,在所有字段的后面声明的约束
• 约束的作用/功能
  • not null:非空约束
  • unique:唯一性约束
  • primary key:主键约束
  • foregin key:外键约束
  • check:检查约束
  • default:默认值约束

2.4 添加约束

create table#在创建表时添加约束
alter table#增加约束

如何查看表的约束

select * from information_schema.TABLE_CONSTRAINTS
where table_name = "advisor";
#这里应该要加上的是表的名字

2.5 非空约束

非空的约束的作用是用来限定某个字段为非空值

默认情况下,所有的类型都可以是NULL,包括INT、FLOAT等类型

一个表可以有很多列分别限定了非空

空字符串不等于NULL,0也不是NULL

添加约束的方法

create table car(
    car_id varchar(50)   not null,
    car_name varchar(50) not null,
    prices bigint        # 可以是还未定价,可以设置为null
);
desc car;#查看表的结构

alter tabel car
modify prices bigint not null;//语法格式modify 字段名 数据类型 约束

2.6 唯一性约束

用来限定某个字段只能够出现一次。

• 同一个表可以有多个唯一性约束
• 唯一性约束可以是某一个列的值唯一,也可以是某几列的值唯一
• 唯一性约束允许列值为空
• 在创建唯一性约束的时候,如果不给唯一约束起名字,就默认和列明相同
• Mysql会给唯一约束的列上默认创建一个唯一索引。

列级约束写法

create table car(
    car_id varchar(50)   unique
    car_name varchar(50) unique
    prices bigint        # 可以是还未定价,可以设置为null
);
desc car;#查看表的结构

表级约束写法

create table car(
    car_id varchar(50)   unique
    car_name varchar(50) unique
    prices bigint,        # 可以是还未定价,可以设置为null
    constraint uk_car_car_id unique(car_id);
    #意思是是为这个约束起一个名字,这个约束的作用是唯一性约束,约束了car_id必须是唯一的
);

NULL值的话可以这样理解,由于NULL是不确定的,因此每一个NULL值都是不确定的,所以的话不会破坏唯一性约束,或者说它就是这样规定的吧。

建表后加表的唯一性约束的方法

alter table car
add constraint uk_car_car_id unique(car_id);#通过add constraint的这种方式进行添加
alter table car
modify car_id varchar(50) unique;#这种是直接添加 modify 字段名 字段类型 unique
#如果是要加多个列的约束,那么这时候就需要进行表级约束的添加
create table car(
    car_id varchar(50)   unique
    car_name varchar(50) unique
    prices bigint,        # 可以是还未定价,可以设置为null
    constraint uk_car_name_id unique(car_id,car_name);#两种方式
    UNIQUE KEY(car_id,car_name)#两种方式
    #意思是是为这个约束起一个名字,这个约束的作用是唯一性约束,约束了car_id和car_name这个元组必须是唯一的
);

---

删除唯一性约束

添加唯一性约束的列上也会自动创建唯一索引

删除唯一性约束只能通过删除唯一索引的方式进行删除。

删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。

如果创建唯一约束时没有指定名称,如果是单列,就默认和列名相同,如果是组合列,那么就默认和排在第一个的列名相同,也可以自定义唯一性约束名

alter table car
drop index uk_car_id_name;

2.7 主键约束

用来唯一标识表中的一行记录,主键约束相当于唯一约束和非空约束的组合,主键约束列不允许重复,也不允许出现空值。

• 一个表中最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建
• 主键约束对应着表中的一列或者多列(复合主键)
• 如果是多列组合的复合主键约束,那么这些列值都不能是空,并且组合的值不能重复
• 当创建主键约束的时候,系统默认会在所在的列或者列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率将会更高),如果删除了主键约束了,主键约束对应的索引就自动删除了。
• 需要注意的一点是,不要修改主键字段的值,因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。

一般来说都不会去动主键的值的了,这是因为底层存储主键的是B+树,Mysql是根据这个B+实现快速查询的,如果我我们对一条的主键进行了修改,就相当于对这条记录在B+树上的位置进行了移动,那么底层就还要进行旋转等复杂数据结构操作,这将会大大影响性能,这样的操作是不推荐的。

create table car(
    car_id varchar(50)   unique
    car_name varchar(50) unique
    prices bigint,        # 可以是还未定价,可以设置为null
    constraint pk_car_id primary key(car_id);#设定主键,主键的约束名总是PRIMARY,你命名了也没有用
);

2.8 AUTO_INCREMENT(使得字段自增)

某个字段具有自增的属性

• 一个表中最多有一个自增长列
• 当需要产生唯一标识符或者顺序值的时候,可以设置自增长列
• 自增长列约束的列必须是键列(主键列?或者是唯一键列)
• 自增约束的列得到数据类型必须是整数类型
• 如果自增列指定了0和null,那么就会在最大值的基础上进行自增,如果自增列手动指定了具体值,直接赋值为你设定的具体值。注意这个值是要你原来数据库中不存在的,如果存在的话要检查唯一性约束。

在开发中,如果在主键或者唯一键上加入了AUTO_INCREMENT约束,那么就不需要为其赋值,让数据库为我们自动做这个事情。

alter table car
modify id int AUTO_INCREMTN;#添加自增列属性

alter table car 
modify id int;#删除属性

• 自增变量的持久化

这个自增变量的持久化是指在mysql中指定了AUTO_INCREMENT的属性之后,如果删除了连续字段的值,那么不会在被删除的字段后开始自增,而是在之前连续字段之前进行自增。在8.0之前,在服务器重启之后这个字段就会失效。

在Mysql5.7系统中,对于自增主键的分配规则,是由Innodb数据字典内部一个计数器来完成操作的。而该计数器只在内存中进行维护,并不会持久化到磁盘中。当数据库重启的时候,该计数器就会被初始化

而Mysql8.0之后,它会将自增主键的计数器持久化到重做日志中,每次计数器的值发生变化,都会将其写入到redo log中,如果数据库重启,Innodb会根据重做日志中的信息来初始化内存计数器的值。

2.9 外键(foreign key)约束

constraint 	fk_emp_dept_id foreign key(department_id) references dept1(dept_id);

如果要添加外键约束,则你关联的字段必须已经建立了索引。

在Mysql中,外键约束是有成本的,需要消耗系统资源的,对于高并发的SQL操作,有可能是不适合的,可能会因为外键约束的系统开销而变得非常慢,Mysql允许你不使用系统自带的外键约束,而在应用层面对数据的插入和删除进行一致性的逻辑规定,所以,即使你不使用外键约束,也要通过应用层面进行逻辑的负责,来实现外键约束的效果,确保数据的一致性。

2.10 Check约束

检查某个字段是否符合check中的约束。使用实例

create table car(
    varchar(50) car_id check(car_id between "10000" and "50000")#检查这个字段是否符合check的条件
);

1.为什么建表的时候要加not null或者default ‘’ 或者default 0?

不想让表中出现null

2.为什么不要null值?

不好比较,这是因为null值是一个有特殊的值,它对于常规的bool运算以及等值运算都会产生意外的结果,比较的时候需要使用专门的not null和is not null关键字进行比较

效率不高,影响索引的效率,这就相当于将索引值设置为空了,因此在建表的时候通常会使用not null default '' 0

3.带AUTO_INCREMENT约束的字段的字段值是从1开始的吗?

在Mysql中,默认AUTO_INCREMENT的字段是1,每新增一条记录,字段值就会自动+1,设置自增属性的时候,是可以指定第一条插入记录的自增字段的值的,这样的话就会从初始值开始递增。

4.并不是每个表都可以任意选择存储引擎?

外键约束是不能够跨引擎使用的,Mysql支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是,外键约束是用来保证数据库数据一致性的(参照完整性),如果表之间需要关联外键,而却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。

3. 视图的使用

3.1 视图的理解

视图可以帮助我们使用表的一部分而不是使用所有的表,视图它是一种虚拟表,本身是不具有数据的,占用很少的内存空间,视图建立在已有表的基础上,视图赖以建立的这些表称为基表

视图的创建和删除只影响视图的本身,而不影响对应的基本,但是如果我们对视图中的数据进行增加、删除和修改操作,数据库也会相应地发生变化,反之亦然。

向视图中提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句。

在数据库中,视图不会保存数据,真正的数据是保存在基表中的,当对视图中的数据进行CRUD的时候,基表中的数据就会发生变化。视图是向用户提供基表数据的另一种表现形式。

3.2 视图的创建与使用

create [or replace]
view  视图名称[(字段列表)]
as 查询语句
[with[CASCADED|LOCAL]CHECK OPTION];

创建视图

create view
as
select employee_id
from employee
where empolyeeid between 10 and 20

查看数据库的表的对象、视图对象

show tables;

查看视图的结构

desc vue_emp1;

查看视图的属性信息

show table status like 'vue_emp1';

查看视图的详细定义信息

show create view vue_emp1;

3.3 不可更新的视图

要使得视图可更新,视图中的行和底层基本表中之间必须存在一对一的基本关系 ,当视图定义出现如下的情况下的时候,视图是不支持更新操作的。

• 在定义视图的时候指定了 algorithm = temptable,视图将不支持insert和delete操作
• 视图中不包含基表中所有定义为非空又未指定默认值的列,视图将不支持insert操作
• 在定义视图的select语句中使用join联合查询,视图将不支持insert和delete操作
• 在定义视图的select语句的字段列表中使用了数学表达式或者子查询,视图将不支持insert也不支持update使用了数学表达式、子查询的字段值。
• 在定义视图的select语句后的字段列表中使用distinct、聚合函数、group by 、having、union等,视图将不支持insert、update、delete
• 在定义视图的select语句中包含了子查询,而子查询中引用了from后面的表,视图将不支持insert、update、delete
• 视图定义基于不可更新的视图。

3.4 修改视图

• 使用create or replace view子句修改视图

create or replace view empt#存在则替换掉
(id_number)
as 
select employee_id,last_name,salary,email
from emps
where salary > 7000;

• 使用alter方式

alter view vue_empt1
as
select employee_id,last_name,salary,email,hire_date
from emps;

3.5 视图的优点

• 操作简单
• 减少数据冗余
• 数据安全:Mysql将用户对数据的访问限制在某些数据的结果集上,而这些数据的结果集可以使用视图来实现。永不不必直接查询或者操作数据库表,这也可以理解为视图具有隔离性,视图相当于在用户和实际的数据表之间加了一层虚拟表。

3.6 视图的不足

如果我们在实际数据表的基础上创建了视图,那么,如果实际数据表的结构变更了,我们就需要对视图进行相应的维护。