sql 基础(primary and intermediate)

最新推荐文章于 2025-01-14 19:45:00 发布

Tonq_csdn

最新推荐文章于 2025-01-14 19:45:00 发布

阅读量1.3k

点赞数 4

分类专栏： database sql 文章标签： database system notebook

本文链接：https://blog.csdn.net/weixin_40509040/article/details/90137847

版权

database 同时被 2 个专栏收录

1 篇文章

订阅专栏

sql

1 篇文章

订阅专栏

(推荐食用方法：ctrl+f 搜索关键字；开头目录查找

sql primary

1. Basic type

简单例子如下：

	char(n) \ varchar(n)		//n is max_len; unit: 1Byte
	int \ smallint
	numeric(p,d)		//precision（有效数字）, digit(小数的位数)
	real \ double precision \ float(n)

sql基本类型详见：w3school SQL 数据类型

2. Creating table instruction

	create table r
		(Ai   Di,	//属性、基本类型
		...
		<integrity constraint>,
		...)	//主要是三大约束：主键、外键、not null

example:

常用建表约束:
primary key(attribute)
foreign key(attribute1) references oth_table(attribute2)
(如果attribute1和attribute2是相同的String，那么attribute2，可以省略，就是直接写oth_table即可)
attribute type not null
check 详见：w3school SQL CHECK 约束

3. Modification operations

(1) delete

delete from r
where P;			 删除r中满足谓词P的 某些行

delete from r		 删除所有行（tuple）,得到空关系

drop table r		 删除整个表

alter table r drop attri 删除一个属性 且 删除一纵列

(2) insert

(假设插入元组中不含有null，不考虑连接丢失问题 )

insert into r
	values(att1, att2,..., attn)		--deprecated

insert into r(att1, att2,..., attn)		--precated
	values(att1, att2,..., attn)	指定参数,排除database中各属性顺序的影响

insert into r				向表中插入表！
	+ sub-query clause
e.g. 
insert into instructor
	select ID,name,dept_name
	from student
	where dept_name='music' and tot_ced>144;

(3) update

update r
set attribute
where ...		约束条件

具体来看一个例子：
（例子中显示了set部分用{key=value}键值对集合 的形式
update stuDB 
set stuId='123',stuName='o',phoneNumber='o'
	,qqNumber='o',eMail='o' 
where stuId = '20217';


为避免更新顺序问题，可以使用 case clause（它可以出现在任何 该出现value的地方）
e.g.
update r
set salary = case
				when salary>100 then salary*1.05
				when salary between 90 and 100 then salary*1.04
				...
				else salary*1.00
			  end

4. Query operations

(1) single relation query

即只从一个 table（表）中查询
查询语句的principle：
1. 六大语句的逻辑顺序
2. 任何输入输出都是表(table or relation)
如何去重复？关键字：distinct,all(default)

	select distinct Ai
	from r

select clause 的参数可以是：某属性、常数、含有+ - * / 的算术表达式
where clause：是作用在from clause产生关系的属性上的谓词P
where clause 中可以使用:
逻辑连接词：and、or、not
比较运算符: <，<=,>,>=,=,<>(!=)

(2) multiple relation query

即从两个及以上的表中查询
cross join: from r₁,r₂,…r_n, 创建了一个大的笛卡儿积（元组乘元组）

(3) Natural Join

natural join连接两个关系的相同属性上 取值相同的所有元组
一般的使用格式：from E₁，E₂，…
（E_i can be single relation or multi-relation）
为了避免不必要(多余的)的相同属性带来的危险，使用join...using（Ai）
(显示指明相同属性,且仅限于属性)

(4) Additional Basic Operations

a) rename opr

格式： old-name as new-name
变量别名 vs 表别名（table alias, correlation name\variable, tuple variable）

b) string opr

sql中的字符串用单引号标识, 如 ‘str’ (这个容易忘)
如果 sql 的变量里想包含单引号，则用双引号表示(其实是双引号中的第一个单引号做转义标志)，如sql中有变量comment = ‘it"s right’，则 comment的值即为 it’s right
字符串函数: upper(s), trim(s), len(s)等等
例如：select trim(’ Sample '), upper(‘sample’), len(‘sample’), substring(‘sample’, 1, 3)
返回值：‘Sample’, ‘SAMPLE’, 5, ‘sam’
更多参考: w3c school T-SQL 字符串函数
基于like的模糊查找
(w3c school解释：用于在 where 子句中搜索 column 中的指定pattern
(like 在where子句中的 模糊查找 是相对于等号=之类运算符在where子句中的 精确查找。后者要求严格相等，前者只需要符合pattern即可

(1) % 描述任意str(0个或多个字符)
(2) _ 描述单个char(1个字符)
如：xx like ‘__Y%’ 表示 “前两个字符任意，第三个字符为Y，后面有任意长度字符的 string”，符合此模式的 xx 可能是：23Y, boYfriend 等string
(3) 对 _或% 表示要用到转义字符:
如：xx like '3_\%' 表示 "前三个字符为30%，后面有任意长度字符的 string "，符合此模式的 xx 可能是：30%，34%，38% 等string

例子：
```
SELECT * FROM Student
WHERE stu_name LIKE '%tom%' AND stu_age > 15
-- 混合使用精确查找、模糊查找
```

c) three other oprs

select *

select * from r					//select all attrs in r
select r1.* from r1,r2,...,rn	//select all attrs in ri

oreder by

order by attr1 desc, attr, ... //ascending by default

use predicate in where clause

两个区间表示法：
where SAL between 0 and 100			
where SAL not between 0 and 100	

n元组比较（字典序）：			sqlserver中不支持
select name, course_id				
from instructor, teaches
where (instructor.ID, dept_name)=(teaches.ID, 'Biology')

(5) Set Operations

参与运算的两个 set 模式要一致（即:参数数量相等，对应参数类型相容)，否则报错（这里的集合实际是表、关系）
集合运算结果自动去重，满足集合的数学特性

three operations: union(求并集)、intersect(求交集)、except(求差集)

subquery_clause(即SetA) + { UNION | INTERSECT | EXCEPT } + subquery_clause(即SetB)

(6) NULL

sql将涉及null的任何比较运算的outcome视为unknown ,而unknown作为和true、false并列的第三布尔值
test for null attributes: is null、is not null
当select distinct查询时，对null的处理不同于谓词中null的比较
(这里涉及了元组之间的比较运算，null=null返回不是unknown，而是true)

(7) Aggregate Functions

聚集函数是以值的集合为input，返回单个值的函数
有：avg, min, max, sum, count
(!!!这里要注意 sum 和 count 的区别，从数据库里写sql String时容易混淆，bug生产机)

a) basic aggregation

如何区别如下三个语句？
select count(distinct ID) from stu	计算有多少 ID不一样的 行数（除去null）
select count(ID) from stu	计算有多少 ID不为null 的行数（除去null有多少tuple）
select count(*)	from stu	计算关系中有多少行（统计有多少tuple）

聚合函数对null的处理规律:
除了count(*)不忽略 null ，其他所有聚合函数都忽略 null
编码规范:
不要使用count(列名)来代替count(*)，因为后者是SQ92定义的标准统计行数的语法，与null和非null的问题无关。

b) aggregation with Grouping

group by clause中所有属性上取值相同的元组将被分在一个组中
（依子句中的属性列表来分组，聚合函数的作用范围即为每个小组）
（没有group by clause的实际是把整个table当成一个分组）
每个group只输出一个tuple，所以要保证 select attr 和 group by attr 的同步
（select clause 中的 attribute 必须是 group by clause 中出现的，
（否则就必须以聚合函数参数的形式出现

c) ‘having’ clause

having clause 是在形成group之后才起作用，是对每个分组限制的子句
having clause 也要与 group by clause 同步

(8) Nested Sub-queries

a) test for Set Membership (in，not in 关键字)

测试属性对于 子查询集合 的成员资格

借助了in, not in关键字，一般有下面的用法：
attr in\not in (enum set, 枚举类型构成的集合)
attr in\not in (subquery_clause set)
(attr1, attr2, …) in\not in (subquery_clause set)

例1：测试单个attribute的成员资格
select * from T
where name in （'Mozart', 'Einstein'）

select dname from DEPT
where deptNo not in (select DEPTNO from EMP
		     		 where DEPTNO is not null)

例2：测试任意attribute的成员资格
select count(distinct ID)
from T
where (course_id, sec_id, semester, teach_year) 
	  in (select course_id, sec_id, semester, teach_year
	 	  from teaches
	 	  where teaches_id = 110)

(in\not in 关键字效率低，最好避免
(有一句话是：where clause 和 join clause 均可实现 “过滤作用”

b) Set Comparison

some，any，all 量词

attr > some(subquery_set) attr至少比集合中的某一个要大,返回真 //另外： =some() <=> in
attr > all(subquery_set) attr比集合中所有元素都大 //另外： <>all() <=> not in
// some 和 any 一样的用法

e.g.找到工资 高于30号部门某一个员工工资的 员工姓名与工资
select ename,sal
from emp 
where sal > some(select sal from emp
				where deptno=30)

c) test for Empty Relation

exists + subquery clause （子查询结果存在！）
not exists + subquery clause （子查询结果为空）

not exists(B except A) 可以表示A包含B关系 ( B差集A返回空，当然B是A的子集了 )

Q:找出选修了biology系开设的所有课程的 学生
select S.ID
form student as S
where not exists( 
  (select course_id from course			--biology系开设的所有课程set
	where dept_name = 'biology')
	except
  (select T.course_id from takes as T	--相关子查询！
    where T.ID = S.ID)					--找出每个S.ID（学生）选修的课程set
				);

d) test for Duplicate Tuples

e) sub-query in ‘from’ clause

select ...
from (select ... from ... where...) 
	  as new_name_tuple(att1,att2,...,attn)
... 
// from 接受的是 subquery返回的表，用as重命名了这个表和它的属性

e.g. 找出所有系中工资总额最大的系:
select max(tot_sal)
from (select dept_name, sum(salary)
	  from instructor
	  group by dept_name) 
	  as dept_total(dname, tot_sal)

f) ‘with’ clause

with clause先定义好子查询表，主查询直接引用

with t1 as ( 
query clause 
), t2 as(
query clause
)...

select ...	--在主语句中,t1 t2作为已知的表
...

g) scalar query(标量子查询)

相关子查询（correlated sub-query）
–运行机制：从外部查询开始，对外部查询的每一行，执行一遍内部查询。
–可以把sub-query当作func()，只不过它的参数是外部查询每一行的某个信息
–下面的例子即是：传入动态变化的dept.deptno为标量子查询的参数，动态地向外部返回标量结果: 部门号为deptno的内部员工数量

Q:找出 内部员工数量大于3 的所有部门
select dname
from dept
where (select count(*) from emp		  
	where emp.deptno = dept.deptno) > 3 
	// 这个子查询不能单独运行,
	// 因为 dept.depto 不是子查询的内部值，而是来自外部
	// 这种内外相互关联的查询就叫相关子查询

标量子查询（scalar sub-query）
–sub-query which returns scalar value
（a table having single attribute and single tuple，返回单个值的意思
（可以替代group by语句，用在seclect clause内
（即对于外部动态传入的attr，在内部找出满足这个attr的结果，再动态地返回

Q: 列出所有的系 和 他们拥有的教师数量
select dept_name, (select count(*) from instructor
					where instrcutor.dept_name = department.dept_name
					) as num_instructor	
					对每一个dept_name（外部查询），返回单个值：教师数量
from department

5. Join Expression

连接条件： natural(share attrs)、using(A₁,A₂,…,A_n)、on(predicate)
连接类型： (inner) join、outer join(left、right、full)
（外连接通过在outcome中创建null项的方式，保留了那些连接丢失的tuple）
(因为不满足join…on…的条件而从结果中被自动舍弃)
类型可以和条件任意组合！(sql server 中不支持natural join and using)

sql intermediate

View (杂记)

区别基本表base table和 伪表
(object of view 本身并不存储数据，只不过动态执行到view时，解析了view 所定义的语句罢了）
create view name_xx as ( query expression )

drop view name_xx

insert into name_xx values (attr₀,attr₁,…)
(插入一个新 tuple)
视图更新，是更新所查询的base table（改了库，慎重）
Update name_xx
Set (sequence)
视图不可更新

条件约束：
create view xx as (sql query expression)
with check option
view + 权限 -> 解决数据安全问题

example:
	grant select,update on user3.emp to user2 		对象权限(DML)     
	grant create any table to user2					系统权限(DDL) 
    revoke select on user3 .emp from user2 			回收权限