(数据库原理及应用笔记)用户接口及SQL语言

Sally ·

更新时间:2024-09-21

· 865 次阅读

User interface of DBMS
1.Query language
2.Interface and maintaining tools(GUI)
3.APIs
4.Class Library Query Language
1.Formal Query Language
2.Tabular Query Language
3.Graphic Query Language
4.Limited Natural Language Query Language Relational model supports simple/powerful/OLs
1.Strong formal foudation based on logic
2.Allows for much optimization Query Language!=Programming Language
1.OLs not expected to be “Turing complete”(图灵完善)
2.OLs not intended to be used for complex calculations
3.OLs support easy/efficient access to large database SQL — No structured Query Language
1.Data Definition Language(DLL)
2.Query Language(QL)
3.Data Manipulation Language(DML)
4.Data Control Language(DCL) Important terms and concepts
1.Base table
2.View
3.Data type supported
4.NULL
5.UNIQUE
6.DEFAULT
7.PRIMARY KEY
8.FOREIGN KEY
9.CHECK(Integration contraint) Basic SQL Query

SELECT [DISTINCT]target-list FROM relation-list WHERE qualification

Conceptual Evaluation Strategy(SQL语句实现的步骤)
1.Compute cross-product of relation-list
2.Discard resulting tuples if they fail qualifications
3.Delete attributes that are not in target-list
4.If DISTINCT is specified/eliminate duplicate rows LIKE(模糊查询)

SELECT S.age,age1=S.age-5,2*S.age AS age2 FROM Sailors S WHERE  S.sname LIKE 'B_%B'

2.LIKE used for string pattern matching

UNION

SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color='red' OR B.color='green')

SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
UNION
SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green'

INTERSECT(相交)

SELECT S.sid 
FROM Sailors S,Boats B1,Reserves R1,Boats B2,Reserves R2 
WHERE S.sid=R1.sid AND R1.bid=B1.bid 
AND S.sid=R2.sid AND R2.bid=B2.bid 
AND (B1.color='red' AND B2.color='green')

SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
INTERSECT
SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green'

SELECT S.sname 
FROM Sailors S 
WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103)

EXISTS

SELECT S.sname 
FROM Sailors S 
WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)

NOT IN

SELECT bid 
FROM Reserves R1 
WHERE bid NOT IN (SELECT bid FROM Reserves R2 WHERE R2.sid ┐=R1.sid)

op ANY,op ALL,op IN,>,=,<=,┐=(逻辑运算符)

SELECT * 
FROM Sailors S 
WHERE S.rating > ANY(SELECT S2.rating FROM Sailors S2 WHERE S2.sname='Horatio')

SELECT S.sid 
FROM Sailors S,Boats B,Reserves R 
WHERE S.sid=R.sid AND R.bid=B.bid 
AND B.color='red' AND S.sid IN 
(SELECT S2.sid 
FROM Sailors S2,Boats B2,Reserves R2 
WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color='green')

Division in SQL,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.sid=S.sid))

Aggregate operation
1.COUNT(*)
2.COUNT([DISTINCT]A)
3.SUM([DISTINCT]A)
4.AVG([DISTINCT]A)
5.MAX(A)
6.MIN(A) GROUP BY AND HAVING

SELECT [DISTINCT] target-list 
FROM relation-list 
WHERE qualification 
GROUP BY grouping-list 
HAVING group-qualification

SELECT Temp.rating FROM (SELECT S.rating,AVG(S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS Temp WHERE Temp.avgage=(SELECT MIN(Temp.avgage) FROM Temp) Some New Features of SQL
1.CAST Expression
-> CAST(Expression AS Data type)

CREATE VIEW prospects(name,school,service) 
AS SELECT name,school,CAST(NULL AS Varchar(20)) 
UNION 
SELECT name,CAST(NULL AS Varchar(20)),service FROM Soldiers;

2.CASE Expression

SELECT name,CASE status 
	WHEN 1 THEN "Active Duty" 
	WHEN 2 THEN "Reserve" 
	WHEN 3 THEN "Special Assignment"
	WHEN 4 THEN "Retired"
	ELSE "Unknown"
END AS status
FROM Officers;

SELECT sum(CASE WHEN type="chain saw" THEN accidents ELSE 0e0 END)/sum(accidents)
FROM Machines;

SELECT type,CASE 
WHEN sum(hours_used)>0 THEN sum(accidents)/sum(hours_used) ELSE NULL END AS accident_rate 
FROM Machines 
GROUP BY type;

SELECT type,sum(accidents)/sum(hours_used) 
GROUP BY type 
HAVING hours_used>0

3.Sub-query
-> Scalar sub-query

SELECT d.deptname,d.location 
FROM dept AS d 
WHERE(SELECT avg(bonus) FROM emp WHERE deptno=d.deptno)>(SELECT avg(salary) FROM emp WHERE deptno=d.deptno)
SELECT d.deptno,d.deptname,
(SELECT MAX(salary) FROM emp 
WHERE deptno=d.deptno) AS maxpay 
FROM dept AS d 
WHERE d.location="New York"

-> Table expression

SELECT startyear,avg(pay) 
FROM (SELECT name,salary+bonus AS pay,year(startdate) AS startyear FROM emp) AS emp2
GROUP BY startyear

-> Common table expression

WITH 
payroll(deptno,totalpay) 
AS (SELECT deptno,sum(salary)+sum(bonus) FROM emp GROUP BY deptno)
SELECT deptno FROM payroll WHERE totalpay=(SELECT max(totalpay) FROM payroll)

4.Outer Join

WITH 
innerjoin(name,rank,subject,enrollment)
AS 
(SELECT t.name,t.rank,c.subject,c.enrollment FROM teacher AS t,courses AS c 
WHERE t.name=c.teacher AND c.quarter="Fall 96"),
teacher-only(name,rank) AS
(SELECT name,rank
FROM teachers 
EXCEPT ALL
SELECT name,rank
FROM innerkoin),
course-only(subject,enrollment) AS
(SELECT subject,enrollment
FROM courses
EXCEPT ALL
SELECT subject,enrollment
FROM innerjoin)

SELECT name,rank,subject,enrollment 
FROM innerjoin 
UNION ALL 
SELECT name,rank,CAST(NULL AS Varchar(20)) AS subject,
CAST(NULL AS Integer) AS enrollment
FROM teacher-only 
UNION ALL 
SELECT CAST(NULL AS Varchar(20)) AS name,
CAST(NULL AS Varchar(20)) AS rank,subject,enrollment 
FROM course-only;

5.Recursion

WITH 
agents(name,salary) AS 
((SELECT name,salary 
FROM FedEmp 
WHERE manager="Hoover")
UNION ALL
(SELECT f.name,f.salary 
FROM agents AS a,FedEmp AS f 
WHERE f.manager=a.name)) 
SELECT name

-> ppt Plane Trips

Data Manipulation Language Insert Delete Update View in SQL General View
-> Vitrual tables derived from base tables
-> Logical data independence
-> Security of data
-> Update problems of view Temporary view and recursive query
-> WITH
-> RECURSIVE Embedded SQL
-> How to accept SQL statements in programming language
-> How to exchange data and messages between programming language and DBMS
-> The query result of DBMS is a set,how to transfer it to the variablee in programming language
-> The data type of DBMS and programming language may not the same exactly
1.General Solutions
-> Embedded SQL
-> Programming APIs
-> Class Library
2.SQL statements can be used in C program directly
-> Begin with EXEC SQL ,end with";"
-> Though host variables to transfer information between C and SQL.Host variables should be defined begin with EXEC SQL
-> In SQL statements,should add ‘;’ before host variables to distinguish width SQL’s own variable or attributes’ name
-> In host language (such as C),host variables are used as general variables
-> Can’t define host variables as Array or Structure
-> A special host variable,SQLCA EXEC SQL INCLUDE SQLCA
-> Use SQLCA.SQLCODE to justify the state of result
-> Use indicator(short int) to treat NULL in host language
3.Executable Statements

EXEC SQL BEGIN DECLARE SECTION;
	char SNO[7];
	char GIVENSNO[7];
	char CNO[6];
	char GIVENCNO[6];
	float GRADE;
	short GRADEI;
EXEC SQL END DECLARE SECTION;

CONNECT : EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
Execute DDL or DML Statements : EXEC SQL INSERT INTO SC(SNO,CNO,GRADE) VALUES(:SNO,:CNO:GRADE);
Execute Query Statements : EXEC SQL SELECT GRADE INTO :GRADE,:GRADEI FROM SC WHERE SNO=:GIVENSNO AND CNO=:GIVENCNO;

Cursor

Define a cursor:
EXEC SQL DECLARE  CURSOR FOR 
SELECT ... FROM ... WHERE ...

EXEC SQL OPEN 
Fetch data from cursor : 
EXEC SQL FETCH  INTO :hostvar1,:hostvar2,...;
SQLCA.SQLCODE will return 100 when arriving the end of cursor
CLOSE CURSOR

EXEC SQL DECLARE C1 CURSOR FOR 
SELECT SNO,GRADE 
FROM SC 
WHERE CNO=:GICENCNO;
EXEC SQL OPEN C1;
if(SQLCA.SQLCIDE<0) exit(1);
while(1){
	EXEC SQL FETCH C1 INTO :SNO,:GRADE,:GRADEI
	if(SQLCA.SQLCODE==100) break;
	...
}
EXEC SQL CLOSE C1;
...

Dynamic SQL
-> executed directly
-> with dynamic parameters（:y，EXEC SQL PREPARE PURGE FROM ，EXEC SQL EXECUTE PURGE USING）
-> for query Stored Procedure

EXEC SQL
	CREATE PROCEDURE drop_student
		(IN student_no CHAR(7),
		OUT message CHAR(30))
	BEGIN ATOMIC
		DELETE FROM STUDENT 
			WHERE SNO=student_no;
		DELETE FROM SC
			WHERE SNO=student_no;
		SET message=student_no || 'droped';
	END;
EXEC SQL
...
CALL drop_student(...);
...

作者：zhuangww05

数据用户接口数据库原理 SQL 接口 sql语言数据库