做电商哪几个设计网站比较好,网站内容优化方法有哪些内容,seo 网站推广入门,网站定制化开发DBMS
Query LanguagesInterface and maintaining tools(GUI)APIsClass Library
QL 不是图灵完备的#xff0c;不是一种编程语言。
QL
SQL是一种非过程化的查询语言。
DDL数据定义语言#xff1a;表#xff0c;视图QL 查询语言DML 数据操纵语言DCL 数据控制语言
Base t…DBMS
Query LanguagesInterface and maintaining tools(GUI)APIsClass Library
QL 不是图灵完备的不是一种编程语言。
QL
SQL是一种非过程化的查询语言。
DDL数据定义语言表视图QL 查询语言DML 数据操纵语言DCL 数据控制语言
Base table基表实际存在的表 View 视图不是实际存在的表虚表
SELECT [DISTINCT] target_list
FROM ralation_list
WHERE qualificationConceptual Evaluation Strategy
计算笛卡尔乘积用WHERE子句做筛选根据target_list做投影根据是否有DISTINCT消除重复
在不引起混乱的情况下多表查询可以不加别名。
使用distinct的时候需要注意一般必须含有主键或者有unique约束的键。
SQL支持like表达的模糊查询
查询实例
三张表 Sailors :sid sname age rating Boats : bid bname color Reserves : sid bid day
create table Sailors
(sid int primary key,sname nvarchar(20) not null,rating int not null,age float
)go create table Boats
(bid int primary key,bname nvarchar(20) not null,color nvarchar(20) not null
)go create table Reserves
(sid int foreign key references Sailors(sid),bid int foreign key references Boats(bid),day date not null,primary key(sid, bid)
)goinsert into Sailors values (22,dustin,7,45.0)
insert into Sailors values (31,lubber,8,55.0)
insert into Sailors values (58,rusty,10,35.0)
insert into Sailors values (28,yuppy,9,35.0)
insert into Sailors values (44,guppy,5,35.0)
insert into Sailors values(11,dustin,8,20)insert into Boats values (101,tiger,red)
insert into Boats values (103,lion,green)
insert into Boats values (105,hero,blue)insert into Reserves values (22,101,1996-10-10)
insert into Reserves values (58,103,1996-11-12)
insert into Reserves values (58,101,1996-12-12)
insert into Reserves values(58,105,1996-11-11)select * from Sailors
go
select * from Boats
go
select * from ReservesSELECT S.age,age1 S.age-5, 2*S.age As age2
FROM Sailors S
WHERE S.sname LIKE B_%B--查找名字以B开头结尾且至少有三个字符的人的年龄使用as给列起别名在有的系统中不适用。
查询预定过红色或者绿色船的水手信息 使用or或者分别查询后使用union。需要注意的是使用union的两张表需要满足并兼容的条件。这里的两张表是满足的。
查询预订过红色和绿色船的水手信息
做Reserves的自连接
SELECT S.sid
FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2
WHERE S.sidR1.sid AND S.sidR2.sid AND R1.bidB1.bid AND R2.bidB2.bid and (B1.colorred AND B2.colorgreen)使用INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sidR.sid AND B.bidR.bid AND B.colorred
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sidR.sid AND B.bidR.bid AND B.colorgreen需要注意的是集合的交INTERSECT的两个表必须并兼容。
嵌套查询
预定过编号103号船的水手的姓名 非关联子查询
SELECT S.sname
FROM Sailors S
WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid103)关联嵌套子查询
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *FROM Reserves RWHERE R.bid103 AND S.sidR.sid)关联嵌套子查询的效率一般比非关联嵌套子查询的效率低。
在子查询中内层查询可以直接使用外层查询的值相当于嵌套循环
预订过103号船并且只预订过一次船的水手的姓名
SELECT S1.sname
FROM (SELECT *FROM Sailors SWHERE (SELECT COUNT(*) FROM Reserves R WHERE R.sidS.sid)2) S1
WHERE S1.sid IN(SELECT R.sid FROM Reserves R WHERE R.bid103)预订过103号船并且只预订过一次103号船的水手的姓名 SELECT S.sname
FROM Sailors S
WHERE ((SELECT COUNT(*) FROM Reserves R WHERE R.sidS.sid AND R.bid103)1)查找只有一个人预订的船 不使用COUNT的方法从Resevers得到所有没有被其他人订过的船
SELECT B.bname
FROM Boats B,Reserves R1
WHERE B.bidR1.bid AND B.bid NOT IN (SELECT R2.bid FROM Reserves R2 WHERE R2.sidR1.sid)使用COUNT的方法
SELECT B.bname
FROM Boats B
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT R.bid,R.sid FROM Reserves R) R WHERE R.bidB.bid)1还可以使用UNIQUE和NOT UNIQUE
ANY ALL
找到比任意一个叫dustin的级别高的人的姓名
SELECT *
FROM Sailors S
WHERE S.rating ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.snamedustin)找到比所有叫dustin的级别高的人的姓名
SELECT *
FROM Sailors S
WHERE S.rating ALL (SELECT S2.rating FROM Sailors S2 WHERE S2.snamedustin)查找某个表中同时满足另一个表所有条件的信息的时候使用除法
查找预订过所有船的水手的姓名
Solution 1:否定之否定使用EXCEPT
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS((SELECT B.bid FROM Boats B)EXCEPT --集合差(SELECT R.bid FROM Reserves R WHERE R.sidS.sid))Solution 2:不使用EXCEPT
--不存在有船他没有租过的人
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS(SELECT * FROM Boats B WHERE B.bid NOT IN (SELECT R.bid FROM Reserves R WHERE R.sids.sid))--等价于
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS(SELECT * FROM Boats B WHERE NOT EXISTS (SELECT * FROM Reserves R WHERE R.sids.sid AND R.bidB.bid))聚合函数
COUNT(*)COUNT([DISTINCT] A)查询属性A有多少个不同的值SUM([DISTINCT] A )对不同的属性A进行求和AVG([DISTINCT] A)对不同的属性A求平均值MIN(A)MAX(A)
SELECT COUNT(DISTINCT S.rating)
FROM Sailors S
WHERE S.nameBob分组聚集group by
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification首先对from子句对表进行笛卡尔乘积根据where子句对元组进行筛选对筛选的结果根据group-by的值相同条件进行分组然后对计算的结果根据having后的条件对分组进行筛选最后再根据分组计算select子句后面的值这要求select和having后的值对每个组都是单一的是分组属性集的子集。
SQL无法简单的从语法上确定。
有了group by 以后计算是在分组上进行的如果没有是在表上进行的。
分组其实是做排序然后再将值相同的分组。
SELECT S.rating,MIN(S.age) as minage
FROM Sailors S
WHERE S.age18
GROUP BY S.rating
HAVING COUNT(*)1 AND EVERY(S.age60)--EVERY对应的还有ANYEVERY要求每个分组的每个元素都必须满足要求ANY要求每个分组至少有一个元素满足要求
查询每一条红船的预订人数
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Resevers R
WHERE R.bidB.bid AND B.colorred
GROUP BY B.bid下面的语句会报错
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Resevers R
WHERE R.bidB.bid
GROUP BY B.bid
HAVING B.colorred报错的原因是数据库的语法检查比较简单不会按照业务的语义来进行判断只会简单的判断SELECT 和 HAVING后面的子句有没有在GROUP BY后面出现
因为这里的每一种船只有一种颜色因此我们可以在聚合的时候加上B.color条件这样上面的查询就可以了。
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Reserves R
WHERE R.bidB.bid
GROUP BY B.bid,B.color
HAVING B.colorred对于每个至少有两个人的级别找出年龄大于18岁的最小年龄。
SELECT S.rating, MIN(S.age)
FROM Sailors S
WHERE S.age 18
GROUP BY S.rating
HAVING 1(SELECT COUNT(*) FROM Sailors S1 WHERE S1.ratingS.rating)对于组中属性的筛选只能通过WHERE子句筛选过的元组再进行分组的时候需要对分组进行筛选但是这里的分组已经不是以前的分组了因此需要在子句中再使用子查询。
查找平均年龄最小的级别
SELECT top 1 S.rating,AVG(S.age) as avgAge
FROM Sailors S
GROUP BY S.rating
ORDER BY avgAgeNULL值
不是0不是是不知道。
Case表达式
--Officers(name,status,rank,title)
SELECT name,Case statusWHEN 1 THEN Active DutyWHEN 2 THEN ReserveWHEN 3 THEN Special AssignmentWHEN 4 THEN RetiredELSE UnknownEND AS status
FROM Officers--Machines(serialno, type, year, hours_used, accidents)
--Find the rate of the accidents of chain saw in the whole accidentsSELECT sum(CASE WHEN typechain saw THEN accidentsELSE 0e0END)/sum(accidents)
FROM Machines注意如果CASE后面有字段名则WHEN后面应该是该字段名的值的情况如果没有的话WHEN后面应该是布尔表达式
还需要注意的是可以同时对两个聚合函数的值进行运算。
--查找每种设备的平均故障率SELECT type,CASE WHEN sum(hours_used)0 THEN sum(accidents)/sum(hours_used)ELSE NULLEND AS accident_rate
FROM Machines
GROUP BY type上面的查询语句使用CASE语句的主要原因是可能有的设备没有使用过因此没有故障率一说。如果非要计算的话有可能导致分母为0的情况。
CASE语句对于需要分情况处理的语句效果比较好。
对于含有GROUP BY语句的查询需要把SELECT里面的语句都对分组后进行处理。
对于上面的语句我们当然也可以在HAVING语句中对组进行筛选后再进行计算可是这样做的话就无法得到那些没有时长的组的信息。
子查询
标量子查询查询的结果是一个值一般使用聚合函数
在SQL语句中凡是可以出现一个值的地方都可以出现标量子查询。
SELECT d.deptno,d.deptname,(SELECT MAX(salary)FROM empWHERE deptnod.deptno) as maxpay
FROM dept as d
WHERE d.locationNEW YORK在SELECT语句中也可以使用子查询。 当然我们也可以使用联表查询。
表表达式查询的结果又是一张表
SELECT startyear,avg(pay)
FROM (SELECT name,salarybonus as pay, year(startdate) as startyearFROM emp )as emp2
GROUP BY startyearFROM子句中也可以出现子查询但是需要注意的是不可以在该语句的其他子查询直接使用该子查询得到的临时表。 表表达式一般出现在FROM子句中
公共表表达式如果多次使用同一个只定义一次多次使用
WITH子句定义公共子表达式其实是一个临时视图
--寻找部门总收入最高的部门
WITH payroll(deptno,totalpay) AS(SELECT deptno,sum(salary)sum(bonus)FROM empGROUP BY deptno)
SELECT deptno
FROM payroll
WHERE totalpay (SELECT max(totalpay) FROM payroll)--查找一个部门对第一个部门的平均工资大于第二个的两倍
WITH deptavg(deptno,avgsal) AS(SELECT deptno,avg(salary)FROM empGROUP BY deptno)
SELECT d1.deptno,d1.avgsal,d2.deptno,d2.avgsal
FROM deptavg AS d1, deptavg AS d2
WHERE d1.avgsal2*d2.avgsal需要注意的是WITH和SELECT之间没有没有逗号整个合在一起是一条语句。
外连接
有两种集合差操作
EXCEPT会消除重复元组需要排序EXCEPT ALL如果确信不会出现重复元组或者重复元组对结果没有影响就是用这个效率更高 上面所有都是一条SQL语句。
递归查询
联邦雇员FedEmp(name,salary,manager)
找到胡佛手下超过10万员的雇员包括简接雇员
WITH agents(name,salary) AS((SELECT name,salary)FROM empWHERE managerHoover)UNION ALL(SELECT f.name,f.salaryFROM agents as a,FedEmp as fWHERE f.managera.name))
SELECT name FROM agents WHERE salary100000DML
INSERT INTO table-name VALUES (); --插入一条元组DELETE FROM table-name WHERE 条件UPDATE table-list SET 字段名 WHERE 条件VIEW
普通视图
CREATE VIEW view-name AS (SELECT 语句)视图的定义会进行保存 - 虚表 - 实现数据的逻辑独立性 - 数据安全性 - 视图更新问题早期系统不能进行更新。如果视图中的信息可以和基表中的信心一一对应唯一映射的话就可以进行修改。不同产品可能不同。
临时视图 定义不会进行保存支持递归查询
WITH table-list() AS ()程序设计语言访问数据库
嵌入式SQL
以EXEC SQL开始以;结束
应用APIODBC——JDBC
类库
