关系代数和关系数据库语言（SQL）

阅读提示：本篇文章较长，建议从目录上选取想看的内容。代码上的话，我习惯用小写，如果看不习惯建议跳过。有问题欢迎讨论！！！

一、基础概念

1.1数据库的概念

        数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，它能够高效地存储、检索和管理大量数据。

1.2数据库类型

• 关系型数据库，基于关系模型，数据以表格形式存储
• 非关系型数据库，包括文档型(MongoDB)、键值型(Redis)、列存储型(Cassandra)、图数据库(Neo4j)等

二、关系模型

2.1关系型数据库核心概念

• 模式：即逻辑模式，是数据库中全体数据的逻辑和特征的描述。
• 表：数据以行和列的形式组织，每张表有一个唯一名称。
• 属性：表中的一列即一个属性。
• 元组：表中的一行即一个元组。
• 字段：表中的列，表示数据的属性，每个字段有特定的数据类型(如整数、字符串、日期等)。
• 域：一组具有相同数据类型的值的集合。
• 码（键)：表中某一个属性或一组属性，其值可以唯一确定一个元组。
• 主键（主码）：唯一标识表中每条记录的字段或字段组，不能为NULL，且值必须唯一。
• 外键：一个表中的字段，引用另一个表的主键，用于建立表之间的关系。
• 索引：提高数据检索速度的数据结构，类似于书籍的目录。

2.2关系代数

2.2.1选择

定义：从关系R中选择满足给定条件F的元组，记作σ_F(R)

特点：水平操作（按行筛选）

sql语句为：

select *    --全体学生
from Student        --从Student表
where Smajor='信息安全'    ---条件

2.2.2投影

定义：从关系R中选择若干属性列组成新的关系，记作π_A(R)，其中A是属性列表

特点：垂直操作（按列筛选）；会去除重复元组；结果关系的属性是A中指定的属性

sql语句

select  distinct Sno,Smajor    --distinct表示去除重复的元素
from Student

2.2.3连接

• 等值连接

定义：从关系R和S的笛卡尔积中选取满足θ条件的元组，记作R⋈_{AθB}S.

                        /*笛卡尔积就不赘述*/

特点：θ为"="时称为等值连接；结果包含R和S的所有属性（可能有重复列）

sql语句：

select *
from R 
join S on R.A=S.B

• 自然连接

1. 定义：R和S在所有公共属性上等值连接，并去除重复列

特点：自动识别同名属性进行匹配；结果关系不包含重复属性；如果无公共属性则退化为笛卡尔积

sql语句：

select *
from table1
natural join table2

• 外连接

分类：

• 左外连接(R⟕S)：保留左边关系R的所有元组
•  右外连接(R⟖S)：保留右边关系S的所有元组
•  全外连接(R⟗S)：保留两边关系的所有元组

sql语句：

-- 左外连接
select * from R left join S on R.A=S.B;
-- 右外连接
select * from R right join S on R.A=S.B;
-- 全外连接
select * from R full join S on R.A=S.B;

2.2.4除

定义：给定关系R(X,Y)和S(Y)，其中X、Y是属性组，R÷S得到一个新的关系，包含所有满足"在R中存在与S中所有Y值对应的X值"的X值。

        /*定义相对有点抽象，需要用象集来定义除法，下面的字潦草点，请见谅*/

sql语句：

选择那些X值，使得不存在任何S中的Y值与该X值没有配对,即选择与S中所有Y值都配对的X值

select distinct R.X from R R1
where not exists (select S.Y from Swhere not exist(select * from R R2where R2.X = R1.X and R2.Y = S.Y)
);

三、关系数据库语言

3.1数据库

3.1.1创建

create database database_name

3.1.2删除

drop database database_name

代码：

create database HE	--创建数据库
use HE	--使用HE的数据库
drop database HE	--删除数据库

3.2数据库模式

3.2.1创建

create schema uu	--创建模式

create schema schema_name  authorization owner_name;
--创建模式，并可指定所有者

3.2.2查看

SELECT name FROM sys.schemas;
--查看所有模式

--查看特定模式的详细信息
SELECT * 
FROM sys.schemas
WHERE name = 'dbo';  -- 替换为您想查看的模式名

/*创建、查看的方式有很多，这里就简单介绍一下*/

3.2.3删除

drop schema schema_name	--删除模式

3.3表

3.2.1创建

create table table_name

create table 表名 (列名1 数据类型 [约束],列名2 数据类型 [约束],...[表级约束]
);

3.2.2删除

drop table table_name

3.2.3修改

alter table database_name

例子：

alter table Student
add constraint c1 check(Ssex='男'OR Ssex='女')
--给表Student中Ssex添加约束，并将约束命名为c1

3.2.4约束

• 主键约束

create table employees (employee_id int primary key,    --primary主码约束name varchar(50)
);-- 命名主键约束
create table students (student_id int,name varchar(50),constraint pk_student primary key (student_id)    --将主键命名为pk-studnet
);-- 复合主键
create table order_items (order_id int,product_id int,quantity int,constraint pk_order_items primary key (order_id, product_id)
);

• 外键约束

create table orders (order_id int primary key,customer_id int,constraint fk_customer foreign key (customer_id) references customers(customer_id)
);-- 带级联操作
create table order_details (detail_id int primary key,order_id int,constraint fk_order foreign key (order_id) references orders(order_id)
);

• 唯一约束

create table products (product_code varchar(20) unique,    --unique唯一约束
);

• 检查约束

create table employees (age int check (age >= 18 and age <= 65),    Credit smallint not null check(Credit in('2','3','4')),Ssex char(2) check(Ssex in('男','女'))    --check检查约束
);

• 默认约束

create table orders (--默认约束 (default)status varchar(20) default 'pending'
);

• not null约束

create table customers (--非空约束 (not null)email varchar(100) not null
);

3.2.5插入

insert into 表名 (列1, 列2) 
values (值1, 值2);

例子：

insert into Student values    --没有指定特定的列表示每列都要插入
('2018001','李勇','男','2000-03-08','信息安全'),
('2018002','刘晨','女','1999-09-01','计算机科学与技术'),
('2018003','王敏','女','2001-09-01','计算机科学与技术'),
('2018004','张立','男','2000-01-08','计算机科学与技术');

3.2.6查询

1）单表查询

• 基础查询

select * from Student	--查看Student表中全部信息select Sno,Sname from Student	--查看Student表中关于学号和姓名的信息select Sname, year(getdate()) - year(Sbirthdate)as age	--计算并起别名为age
from Student	--查看Student表中姓名和年龄

• 条件查询

查询条件	谓词
比较	=，>,  <,   <=,   >=,   !=,   <>(不等于）,   !>（不大于,  !<（不小于）;    NOT +上述符号
确定范围	between and,        not between and
字符匹配	like ,        is not like
空值	is null,        is not null
多重条件（逻辑）	and ,        or,        not

 distinct消除重复列

select distinct Sno from SC		--distinct消除重复列

 where中确定条件

--查询专业为计算机科学与技术，性别为女的学生姓名
select Sname
from Student
where Smajor='计算机科学与技术'and Ssex='女';	

 建立视图，在视图中使用between and

--查询年龄在20和25之间的学生姓名、学号
--建立一个相关的视图
create view Sage as
select Sname, Sno, year(getdate()) - year(Sbirthdate)as age
from Student--在视图中查找
select Sname,Sno
from Sage
where age between 20 and 25;

 in 用来查找属性值属于集合的元组

--查找信息安全和计算机科学与技术学生姓名
--in 用来查找属性值属于集合的元组
select Sname 
from Student
where Smajor in('信息安全','计算机科学与技术')	

 字符匹配

--使用like进行匹配
select *
from Student
where Sname like '李勇'--%代表任意长度（长度可为0）的字符串
select *
from Student
where Sname like '刘%'	--查找姓刘的人--_(下划线）代表单个字符
select *
from Student
where Sno like '201800__'

排序

-- 单列排序
select * from 表名 
order by 列名 asc;  -- 升序(默认)-- 降序排序
select * from 表名 
order by 列名 desc;-- 多列排序
select * from 表名 
order by 列1 asc, 列2 desc;

聚集函数

select count(*) as 总数,sum(数值列) as 总和,avg(数值列) as 平均值,max(数值列) as 最大值,min(数值列) as 最小值
from 表名;

分组查询

-- 基本分组
select 列名, count(*) 
from 表名 
group by 列名;-- 分组后筛选
select 列名, avg(数值列) 
from 表名 
group by 列名 
having avg(数值列) > 100;

2）连接查询

/*之前在关系代数也说过就不举例子了*/

-- 内连接
select a.列, b.列 
from 表1 a 
inner join 表2 b on a.关联列 = b.关联列;-- 左连接
select a.列, b.列 
from 表1 a 
left join 表2 b on a.关联列 = b.关联列;-- 右连接
select a.列, b.列 
from 表1 a 
right join 表2 b on a.关联列 = b.关联列;

3）嵌套查询

--查询选修了"数据结构"课程的学生信息
select *
from Student
where Sno in (select Sno    --找与课程号相对应的学号from SCwhere Cno = (select Cno    --查找课程号from Coursewhere Cname = '数据结构')
);

4）集合查询

并操作：union

交操作：intersect

差操作：except

--查询选修了81001或81002课程的学生学号
select Sno
from SC
where Cno = '81001'    --选修了81001的学号
union                  --并操作
select Sno
from SC
where Cno = '81002';    --选修了81002的学号

--查询选修了81001但没有81002课程的学生学号
select Sno
from SC
where Cno = '81001'    --选修了81001的学号
except                 --差操作
select Sno
from SC
where Cno = '81002';    --选修了81002的学号

5）基于派生表的查询

--查询所有选修81001号课程的学生姓名
select Sname
from Student, (select Sno from SC where Cno='81001) as SCI
where Student.Sno=SCI.Sno

3.2.7更新

update 表名 
set 列1 = 值1 
where 条件;

3.4视图

3.3.1创建

create view 视图名 as
select 列1, 列2
from 表名
where 条件;

3.3.2删除

drop view 视图名

3.3.3查询、更新

与表的查询和更新差不多，只是把表名改为视图名

3.5索引

3.4.1创建

create index 索引名 
on 表名 (列名);

3.4.2删除

drop index 索引名;

/*很久没更了，一是准备比赛，二是github。不过我还是会在这上面更新一些基础内容，项目这些的话代码太多的就放在github上*/