第三章、SQL

木木彡、

已于 2023-06-04 17:19:49 修改

阅读量427

点赞数

分类专栏：数据库系统文章标签： sql 数据库 mysql

于 2023-05-13 01:01:59 首次发布

本文链接：https://blog.csdn.net/wuzhenbin11/article/details/130591670

版权

数据库系统专栏收录该内容

4 篇文章 0 订阅

订阅专栏

系列文章目录

文章目录

系列文章目录
前言
一、SQL数据定义语言DDL
- 1.创建一个表
- 2.删除一个表
- 3.表的更改操作
- - 1）增加属性操作
  - 2）删除属性操作
- 4.主码，外码的定义
二、SQL数据库处理语言DML
- 1. 插入：insert into命令
- 2.更新：update ...set...
- 3.删除：delete
三、数据查询：select
- 1.单关系查询
- 2、多关系查询
- - 内、外、全连接
四、附加的基本运算
- 1、更名运算‘as’
- 2、字符串运算
- 3、顺序控制order by
五、集合运算
- 1、union ——>∪
- 2、intersect——>∩
- 3、except ——> --
六、where中的一些谓词
- 1、between and
- 2、集合的比较
七、group by 与having
八、case表达式
- 1、简单CASE表达式
- 2、搜索CASE表达式
- 3、from子句中使用case
九、NULL
十、子查询
- 不相关子查询
- 相关子查询
十一、子查询应用
- with子句
- where子句中的子查询

前言

一、SQL数据定义语言DDL

数据类型
1、char（n）：固定长度的字符类型，无论里面多长，都按定义长度占用物理空间
2、varchar（n）:可变长度的字符类型
3、int：存储整数
4、smallint：存储整数，范围小
5、numeric（p，d）：指定数据的有效数字长度，就是指定精度
6、real，double precision：双精度浮点类型
7、float（n）：单精度浮点类型
8、date：日期类型
9、time：时间类型，精度到秒
10、timestamp：时间戳类型，精度到毫秒
11、interval：时间间隔类型，记录两个时间点间时间区间信息

1.创建一个表

定义表采用create table命令

create table r
(A1 D1,
  A2 D2,
  ...,
  An Dn,
  (integrity-constraint1),
  ...,
  (integrity-constraintk));

其中r是表名、（）是完整性约束.对应着各个属性（列）、A是属性名字，D属性类型
举例：

create table department
(dept_name varchar(20),
	building varchar(15),
	budget numeric(12,2));

2.删除一个表

使用命令
drop table r：直接删除表
delete from r：删除表中的所有元组，但保留表

3.表的更改操作

1）增加属性操作

alter table r add A D;
其中r为表名，A为待添加属性名称，D为待添加属性类型，表中所有元组在新属性取值为null

2）删除属性操作

altet table r drop A;
从表中去掉属性A

4.主码，外码的定义

primary key(A1,…,Am)：属性A1,…,Am构成主码，主码属性是非空且唯一的，主码可唯一标识每一行
foreign key(A1…An) references s：关系中的任意元组在属性A1,…,Am上的取值必须对应于表s中某元组在主码属性上的取值

举例：

create table course(
	#主码的定义
	course_id char(5) primary key,
	#或者primary key(course_id),
	title     varchar(20),
	#外码的定义
	dept_name varchar(20) references department
	#或者foreigh key(dept_name) references department
	)

二、SQL数据库处理语言DML

1. 插入：insert into命令

要往关系中插入数据，要么指定待插入的元组，要么写一条查询语句来生成待插入的元组集合

#在course表中插入Computer Science系开设的名为“Database Systems”的课程CS-437有4个学时 
insert into course
	values('CS-437','Datebase System','Comp.Sci.',4);

此例中元组的属性值的排列和关系模式中对应属性的排列顺序一致
同时，SQL允许在insert语句中指定属性

insert into course(title,course_id,credits,dept_name)
	values('Datebase System','CS-437',4,'Comp.Sci.');
#当有列表却没有列出来的属性会被设为null
#当没有列表的情况下，在无法给出值的列设为null
insert into student
	values('3003','Grenn'.'Finance',null);

2.更新：update …set…

在不改变一个元组所有值的情况下改变其某个属性的值

#所有教师的工资增长5%
update instructor
set salary=salary*1.05;
#这条更新语句在instructor的每个元祖上各执行一次
#如果只给工资低于70000的涨
update instructor
set salary=salary*1.05
where salart<70000;
#给工资低于平均值的涨
update instructor
set salary=salary*1.05
where salary < (select avg(salary)
				from instructor);

3.删除：delete

添加、更新、删除操作都只能同时对一个表进行操作

三、数据查询：select

在一个select语句中可以包含

select------5
from--------1（确定数据源，融合数据）
where-------2
group by----3
having------4
order by----6
#后面代表各语句的执行顺序

1.单关系查询

select +属性
from +表;

#找出instructor关系中所有教师的姓名
select name
from instructor
#找出所有教师所在的系名
select dept_name
from instructor

在某些情况下如果想强行去除表中的重复数据使用distinct关键字
使用all来显示指明不去除重复

select distinct dept_name
from instructor
#在此查询中，每个系名最多只能出现一次

select子句还可带有+、-、*、/的运算符算术表达式
where子句允许只选出那些在from子句的结果关系中满足特定谓词的元组

#找出Computer Scoence系中工资超过70000美元的所有教师的姓名
select name
from instructor
where dept_name = 'Computer Scoence' and  salary >70000

2、多关系查询

select 属性1，属性2，......
from 表1，表2...

把需要访问的表都放在from子句中，并在where子句中指定匹配条件

#找出所有教师的姓名，以及它们所在系的名称的系所在建筑的名称
select name,instructor.dept_name,building
from instructor,department
where instructor.dept_name=department.dept_name;

join on 语句
将两个表作为输入，产生唯一一个表作为输出，紧跟着一个on运算符说明两个表的连接条件
1、一个join运算符必须有两个表，且只能有两个表
2、输出是一个表
3、必须有一个配对的on来说明连接条件

#找出所有教师的姓名，以及它们所在系的名称的系所在建筑的名称
select name,instructor.dept_name,building
from instructor join department on instructor.dept_name=department.dept_name;

内、外、全连接

在这里插入图片描述
现在有两张表

1、内连接（inner join）：
内连接查询的是两张表的并集，也就是A表和B表都必须有数据才能查询出来

# join
select * from A  join B on A.id = B.id 

# inner join
select * from A inner join B on A.id = B.id 

逗号的连表方式就是内连接
select * from A , B where A.id = B.id

输出都是查询出id为1、2、3数据
在这里插入图片描述

2、外连接
- 左外连接
  - 以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分
  - 注：左表多出来的信息除了左表内数据以外，其余的用null补齐

                left join
select * from A left join B on  A.id = B.id 


                left outer join
select * from A left outer join B on  A.id = B.id

在这里插入图片描述
针对上面的表数据，能查询出id为1、2、3、4数据，其中id=4的数据中age一项为null

右外连接

-- right join
select * from A right join B on  A.id = B.id 

-- right outer join
select * from A right outer join B on  A.id = B.id

在这里插入图片描述

针对上面的表数据，能查询出id为1、2、3数据

全连接
- 全连接显示两侧表中所有满足检索条件的行

select * from a left join b on a.id = b.id
union
select * from a right join b on a.id = b.id
#等同于
select * from a full join b on a.id=b.id

在这里插入图片描述

一个SQL查询的含义如下：
- 为form子句中列出的关系产生笛卡尔积
- 在步骤1的结果上用where子句中指定的谓词
- 对于步骤2的结果中的每个元组，输出select子句中指定的属性

四、附加的基本运算

1、更名运算‘as’

as语句既可以出现在select中也可以出现在from子句中

select T.name,S.course.id
from instructor as T,teaches as S
where T.id=S.id;

2、字符串运算

%：%字符匹配任意子串
_：_字符匹配任何一个字符
'Intro%'匹配以“Intro”打头的任意字符串
'_ _ _'匹配只含三个字符的任意字符串
'_ _ _ %'匹配至少含3个字符的任意字符串

3、顺序控制order by

order by子句让查询结果中的元组按指定的排列顺序显示
如：order by name就是按姓名升序排列
在缺省的情况下，order by默认按升序排序
desc表示降序、asc表示升序

select *
from instructor
order by salary desc,name asc
##按工资降序，如果有工资相同则按姓名升序排列

五、集合运算

SQL作用在关系上的union、intersect 和 except对应于集合中的∪、∩和-运算

1、union ——>∪

找出2017年秋季开课或2018年春季开课或两个学期都开课的所有课程集合

(select course_id
from section
where semester = 'Fall' and year = 2017)
union
(select course_id
from section
where semester = 'Spring' and year = 2018)

注：union运算自动去除重复
如果想保留重复项，就必须使用union all代替union

2、intersect——>∩

找出2017年秋季和2018年春季两个学期都开课的所有课程集合

(select course_id
from section
where semester = 'Fall' and year = 2017)
intersect
(select course_id
from section
where semester = 'Spring' and year = 2018)

注：intersect自动去除重复
如果想保留重复项，就必须使用intersect all代替intersect

3、except ——> –

找出在2017年秋季学期开课但不在2018年春季学期开课的所有课程

(select course_id
from section
where semester = 'Fall' and year = 2017)
except
(select course_id
from section
where semester = 'Spring' and year = 2018)

六、where中的一些谓词

1、between and

between and说明一个值小于或等于某个值，同时大于或等于另一个值

select name
from instructor
where salary between 90000 and 100000;
#等同于
select name
from instructor
where salary >= 90000 and salary <= 100000;

2、集合的比较

1.判断一个值是否属于某个集合in

select *
from emp join dept on dept.deptno=emp.deptno
where emp.deptno in (10,20)
#查询部门号为10或20的员工信息

select *
from emp join dept on dept.deptno=emp.deptno
where emp.deptno in (select deptno
						from dept d
						where loc='CHICAGO'
)and sal>1500
#查询工作部门在芝加哥且工资大于1500的员工信息

2.any、all、some

select *
from emp e
where sal>all(1000,2000,3000)
-- 工资比1000,2000,3000都高的员工信息
select *
from emp e
where sal>any(1000,2000,3000)
-- 工资比1000,2000,3000其中一个高的员工信息

select *
from emp e
where sal>all(select sal
				from emp join dept on dept.deptno=emp.deptno
				where emp.deptno in (select deptno
						from dept d
						where loc='CHICAGO'
)
)
-- 工资比工作在芝加哥的员工工资都高的员工信息

3.exists：判断集合是否为空集

输入为一个集合，
如果集合非空exists返回为true
如果集合为空，则返回false

难了一晚上的题

提供了工资在2000~5000之间的所有职位的部门名称

SELECT DISTINCT dname
from emp ,dept
where not EXISTS
((SELECT  DISTINCT job
FROM emp e
WHERE e.sal > 2000 AND e.sal < 5000)
MINUS
(SELECT a.job
from emp a
where a.deptno = emp.deptno)) and dept.deptno=emp.deptno

两个子查询，一个查工资在2000~5000的所有职位作为一个集合
一个查每个部门所包含的职位
用前者集合减去后者集合为空集则符合条件，即not exists成立
外层查询每次查一行，每一个将部门号导入内层查询，进行where判断
最外面select子句使用distinct去重

思路来源：
在这里插入图片描述
找到选修了所有生物系开设课程的学生
对于每个学生都判断一次是否选修了所有生物系开设课程
先找到生物系开设的所有课程，作为一个集合
再找到某一个学生A选修的课程，作为一个集合
按题意是r1包含r2
判断方法为r2-r1=∅
在这里插入图片描述
对外层查询的每一个学生执行一次内层查询

七、group by 与having

在这里插入图片描述

数据行中只可以有两种类型是属性值
一种就是分组规则中的表达式（group by 后面的分组遵循的属性），另一种就是各种统计值（聚集函数），不能有其他类型，因为原子性
常用聚集函数（除了count（*）外所有的聚集函数都忽略输入集合中的空值）
1、AVG
2、MIN
3、MAX
4、SUM
5、COUNT
6、STDDEV
注：聚集函数默认不去重，如果需要去重则在属性前加distinct
如：count(distinct job)

select deptno，avg(sal)
from emp e
group by deptno
--查询每个部门的平均工资

select dname,count(ename),count(distinct job),sum(sal),max(sal)
from emp e right join dept on e.deptno=dept.deptno
group by dname
--查询所有部门的名称，员工数，不同职位数，工资总数，最高工资

注：出现在SELECT子句中但没有被聚集的属性必须出现在group by子句中

having子句：过滤group by 子句过滤后生成的数据表，这个数据表每一行代表一个分组
where子句处理过滤from生成的底层数据行
having子句处理过滤from生成的底层数据行的分组的数据行
假设我们有一个名为"orders"的表，其中包含订单信息，并且我们想要找出每个客户的订单总数大于3的客户。表结构如下：

SELECT Customer, COUNT(*) as OrderCount
FROM orders
GROUP BY Customer
HAVING COUNT(*) > 3;
--这个查询将根据客户对订单进行分组，并计算每个客户的订单数量。然后，HAVING子句筛选出订单总数大于3的客户

结果如下：在这里插入图片描述

八、case表达式

CASE表达式是SQL中的一种条件表达式，它允许根据条件对结果进行条件性的操作和返回。CASE表达式有两种常见的形式：简单CASE表达式和搜索CASE表达式

1、简单CASE表达式

	使用固定值进行比较，并根据每个值的匹配情况返回相应的结果

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE result
END

expression：要进行比较的表达式或列。
value1, value2, …：要比较的固定值。
result1, result2, …：每个固定值匹配时返回的结果。
ELSE result：如果没有匹配的值，则返回ELSE部分定义的结果
举例：

select case job when 'CLERK' then '一般员工'
				when 'SALESMAN' then '一般员工'
				 when 'MANACER' then '中层干部'
				  when 'PRESIDENT' then '老板'
				  END
				  ,job
				  ,ename
from emp

在这里插入图片描述

2、搜索CASE表达式

根据条件进行比较，并根据第一个满足条件的分支返回相应的结果

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

condition1, condition2, …：用于评估为TRUE或FALSE的条件。
result1, result2, …：每个条件满足时返回的结果。
ELSE result：如果没有条件匹配，则返回ELSE部分定义的结果
举例：

select ename,sal,
		case when sal>3000 then 3
			when sal>2000 then 2
			else 1
			end grade
from emp

在这里插入图片描述

3、from子句中使用case

常见用途是在查询中创建临时表或派生表。CASE表达式可以用于根据条件动态地生成临时表，并在后续查询中引用该临时表
假设我们有一个名为"Orders"的表，包含以下字段：OrderID、CustomerID和OrderDate。我们希望根据订单日期(OrderDate)的年份创建两个临时表，一个包含2019年的订单，另一个包含2020年的订单

SELECT *
FROM
(
   SELECT OrderID, CustomerID, OrderDate
   FROM Orders
) AS o
INNER JOIN
(
   SELECT OrderID, CustomerID, OrderDate,
          CASE 
             WHEN YEAR(OrderDate) = 2019 THEN '2019 Orders'
             WHEN YEAR(OrderDate) = 2020 THEN '2020 Orders'
             ELSE 'Other Orders'
          END AS TempTable
   FROM Orders
) AS t
ON o.OrderID = t.OrderID
WHERE t.TempTable = '2019 Orders'

在上述查询中，我们首先选择订单表的原始数据，将其作为临时表"o"。然后在同一个FROM子句中，我们创建另一个派生表"t"，该表包含订单的OrderID、CustomerID、OrderDate字段，以及一个根据订单日期生成的CASE表达式，用于将订单归类到不同的临时表中。通过将临时表的名称存储在TempTable列中，我们可以在后续的查询中引用这些临时表。

在上述示例中，我们选择了2019年的订单，并通过与临时表的名称进行匹配，只返回TempTable为’2019 Orders’的结果

九、NULL

和null进行逻辑比较结果均为null
在where子句中用is null判断是否为null
使用聚集函数时，除了count(*)都忽略null
如果全是null则count(*)=0
nvl()函数：nvl()接收两个参数NVL(expression1, expression2)
如果第一个参数为NULL则返回第二个参数，否则返回第一个参数
nullif()函数：nullif()函数是一种用于比较两个表达式并返回NULL的函数。它接受两个参数，如果这两个参数的值相等，则返回NULL；否则，返回第一个参数的值。

假设我们有一个名为"Products"的表，包含以下字段：ProductID、ProductName和UnitsInStock。我们想要从表中选择库存数量为0的产品，但是将库存为0的产品的UnitsInStock值替换为NULL

SELECT ProductID, ProductName, NULLIF(UnitsInStock, 0) AS UnitsInStock
FROM Products
WHERE UnitsInStock = 0

在order by子句中null被看为无穷大处理，不过可以使用null last和null first将null始终排到前面或后面

select * from emp order by comm desc null last

在这里插入图片描述

select * from emp order by comm asc null first

在这里插入图片描述

十、子查询

子查询是嵌套在另一个查询中的select-from-where表达式
子查询分为两类：不相关子查询、相关子查询

不相关子查询

子查询的执行不依赖于子查询所嵌入的外部查询。
子查询的执行和外层查询的数据源无关
例：

select *
from emp
where deptno in (select deptno
					from dept
					where loc='CHICAGO')
--子查询语句可单独执行--
select deptno
					from dept
					where loc='CHICAGO'

对于不相关子查询会首先执行子查询内容，然后把子查询的计算结果当做一个常量放到外层查询中
由内而外

十一、子查询应用

在from子句中可以使用子查询当做数据表来使用，绝大多数是不相关子查询
from子句中子查询必须有一个别名作为结果表的名称

with子句

with子句提供了一个定义临时视图的方法，即所定义的视图只是对于包含with子句的查询是有效的
对上图进行改写
with定义多个临时视图时，两个临时视图用逗号分隔

--查询工资大于所在部门的平均工资的员工的姓名与工资--
select e.ename,e.sal
from emp e join (select deptno,avg(sal) avg_sal 
				from emp
				group by deptno) d 
on e.deptno=d.deptno
where e.sal>d.avg_sal
-----------------------可改写为
with d as
(select deptno,avg(sal) avg_sal 
				from emp
				group by deptno)
select e.ename,e.sal
from emp e join d one.deptno=d.deptno
where e.sal>d.avg_sal

where子句中的子查询

标量子查询，子查询结果为一个标量

select e.name,e.sal
from emp e
where e.sal>(select avg(sal) from emp where deptno = e.deptno)

子查询的结果为一个集合，即表
in / not in

--在芝加哥工作的，且工资大于所在部门的平均工资的员工的姓名，工资，员工部门编号，员工所在部门的平均工资
select e.name,e.sal,e.deptno,avg_sal
from emp e join(slect deptno,avg(sal) avg_sal 
				from emp
				group by deptno) d on e.deptno = d.deptno
where e.sal>d.avg_sal 
	and e.deptno in (select deptno from dept where loc = 'CHICAGO')

在这里插入图片描述

木木彡、

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
2
评论
第三章、SQL

数据类型1、char（n）：固定长度的字符类型，无论里面多长，都按定义长度占用物理空间2、varchar（n）:可变长度的字符类型3、int：存储整数4、smallint：存储整数，范围小5、numeric（p，d）：指定数据的有效数字长度，就是指定精度6、real，double precision：双精度浮点类型7、float（n）：单精度浮点类型8、date：日期类型9、time：时间类型，精度到秒10、timestamp：时间戳类型，精度到毫秒。
复制链接

扫一扫