SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++.
What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.
(1) DDL(Data Definition Language), 数据定义语言, used to define database schema
(2) Query Language 数据查询语言，fetch data from database according to user requirements
(3) DML(Data Manipulation language), 数据操纵语言，modify database instance, i.e., insert, update or delete data
(4) DCL(Data Control language)，数据控制语言, includes database constraints, user authentication, etc.
A database schema comprises declarations for the relations (“tables”) of the database.
Many other kinds of elements may also appear in the database schema, including views, indexes, and triggers, which we’ll introduce later.
Simplest form is:
CREATE TABLE (
);
And you may remove a relation from the database schema by:
DROP TABLE ;
The principal element is a pair consisting of an attribute and a type.
The most common types are:
INT or INTEGER (synonyms).
REAL or FLOAT (synonyms).
CHAR(n ) = fixed-length string of n characters.
VARCHAR(n ) = variable-length string of up to n characters.
DATE and TIME are types in SQL.
The form of a date value is:
DATE ‘yyyy-mm-dd’
The form of a time value is:
TIME ‘hh:mm:ss’
with an optional decimal point and fractions of a second following.
An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE.
These attribute(s) functionally determine all the attributes of the relation schema.
There are a few distinctions to be mentioned later.
Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.
Example:
CREATE TABLE Beers (
name CHAR(20) UNIQUE,
manf CHAR(20)
);
A key declaration can also be another element in the list of elements of a CREATE TABLE statement.
This form is essential if the key consists of more than one attribute.
May be used even for one-attribute keys.
Example:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
PRIMARY KEY (bar, beer)
);
There exist these distinctions:
1.There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.
2.No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.
Two other declarations we can make for an attribute are:
1.NOT NULL means that the value for this attribute may never be NULL.
2.DEFAULT says that if there is no specific value known for this attribute’s component in some tuple, use the stated .
Example:
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT ‘123 Sesame St.’,
phone CHAR(16)
);
We may change a relation schema by adding a new attribute (“column”) by:
ALTER TABLE ADD ;
Remove an attribute from a relation schema by:
ALTER TABLE DROP ;
The principal form of a query is:
SELECT desired attributes
FROM one or more tables
WHERE condition about tuples of the tables
SELECT [ALL|DISTINCT] <目标列表达式>
? [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；
Begin with the relation in the FROM clause.
Apply the selection indicated by the WHERE clause.
Apply the extended projection indicated by the SELECT clause.
Operational Semantics
* In SELECT clauses
Renaming Attributes
Handle Duplicates
SQL allows duplicates in relations as well as in query results.
To force the elimination of duplicates, insert the keyword distinct after select**.**
Find the names of all branches in the loan relations, and remove duplicates
? SELECT DISTINCT branch_name FROM loan
The keyword all specifies that duplicates not be removed.
SELECT ALL branch_name FROM loan
Expressions in SELECT Clauses
Any expression that makes sense can appear as an element of a SELECT clause.
SELECT bar, beer, price * 6 AS priceInYuan FROM Sells;
expressions/constants/functions/Attribute alias/…
constants: SELECT drinker, ‘likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ‘Bud’;
functions: SELECT Sname，2006-Sage AS ‘Year of Birth: ’ ，LOWER(Sdept) FROM Student；
expressions: SELECT Sname AS NAME，‘Year of Birth: ’ AS BIRTH， 2006-Sage AS BIRTHDAY，LOWER(Sdept) AS DEPARTMENT FROM Student；
Complex Conditions in WHERE Clause
attribute names of the relation(s) used in the FROM.
comparison operators: =, <>, <, >, <=, >=, between, in
apply arithmetic operations: stockprice*2
operations on strings (e.g., “||” for concatenation).
Lexicographic order on strings.
Pattern matching: s LIKE p
WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.
General form:
LIKE
NOT LIKE
Pattern is a quoted string with % = “any string”; _ = “any character.”
ESCAPE character
When the string contains ‘%’ or ‘_’, you need to use ESCAPE character‘\’
Special stuff for comparing dates and times.
Range comparison: between
Set operator: in
IN <值表>, NOT IN <值表>
<值表>：用逗号分隔的一组取值
SELECT Sname，Ssex FROM Student WHERE Sdept IN ( ‘IS’，‘MA’，‘CS’ );
Important Points
Ordering the Display of Tuples
Null Values
Aggregations
SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.
Also, COUNT(*) counts the number of tuples.
计数
计算总和
计算平均值
求最大值
求最小值
DISTINCT短语：在计算时要取消指定列中的重复值
ALL短语：不取消重复值
ALL为缺省值
NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column.
But if there are no non-NULL values in a column, then the result of the aggregation is NULL.
Grouping
If any aggregation is used, then each element of the SELECT list must be either:
1.Aggregated, or
2.An attribute on the GROUP BY list.
HAVING Clauses
Extra Materials on Aggregation & Grouping
不要忘写COUNT (SELECT只能是键)
Almost the same as for single-relation queries:
1.Start with the product of all the relations in the FROM clause.
2.Apply the selection condition from the WHERE clause.
3.Project onto the list of attributes and expressions in the SELECT clause.
Sometimes, a query needs to use two copies of the same relation.
Distinguish copies by following the relation name by the name of a tuple-variable, in the FROM clause.
It’s always an option to rename relations this way, even when not essential.
From Beers(name, manf), find all pairs of beers by the same manufacturer.
对于教学数据库的三个基本表：
- S(S#, SNAME, AGE, SEX)
- SC(S#, C#, GRADE)
- C(C#, CNAME, TEACHER)
试用SQL的查询语句表达下列查询
--1.检索LIU老师所授课程的课程号和课程名
SELECT C#, CName
FROM C
WHERE TEACHER = ‘LIU‘
--2.检索学号为S3学生所学课程的课程名与任课老师名
SELECT CNAME, TEACHER
FROM SC, C
WHERE SC.C# = C.C#
AND S#=‘S3‘
--3.检索至少选LIU老师所授课程中一门课程的女学生姓名
SELECT SNAME
FROM S, SC, C
WHERE SC.C# = C.C# AND SC.S# = S.S#
AND SEX = ‘F‘
AND TEACHER = ‘LIU‘
SELECT SNAME
FROM S
WHERE SEX = ‘F‘
AND EXISTS (
SELECT *
FROM SC
WHERE SC.S# = S.S#
AMD EXISTS (
SELECT *
FROM C
WHERE C.C# = SC.C#
AND TEACHER = ‘LIU‘
)
)
--4.检索WANG同学不学的课程的课程号
SELECT C#
FROM C
WHERE NOT EXISTS (
SELECT *
FROM S, SC
WHERE S.S# = SC.S# AND SC.C# = C.C#
AND SNAME = ‘WANG‘
)
--5.检索至少选修两门课程的学生学号，==姓名==
SELECT DISTINCT X.S#, SNAME
FROM SC AS X, SC AS Y
WHERE X.S# = Y.S# AND X.C#<>Y.C#
--6.检索全部学生都选修的课程的课程号与课程名
SELECT C#, CNAME
FROM C WHERE NOT EXISTS(
SELECT *
FROM S
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.S# = S.S# AND SC.C# = C.C#
)
)
--7.检索选修课程包含刘老师所授课程的学生学号
--8.统计有学生选修的课程门数
SELECT COUNT(DISTINCT C#)
FROM SC
--9.求选修C4课程的的女学生的平均年龄
SELECT AVG(AGE)
FROM S, SC
WHERE S.S# = SC.S# AND C# = ‘C4‘ AND SEX = ‘F‘
--10.求LIU老师所授课程的每门课程的平均成绩
SELECT C.C#, AVG(GRADE)
FROM SC, C
WHERE SC.C# = C.C#
AND TEACHER = ‘LIU‘
GROUP BY C.C#
--11.统计每门课程的学生选修人数（超过10人的课程才统计）。显示课程号和人数，查询结果按人数降序排列，若人数 相同，按课程号升序排列
SELECT C#, COUNT(S#)
FROM SC
GROUP BY C#
HAVING COUNT(*) > 10
ORDER BY COUNT(S#) DESC, C#
--12.检索学号比WANG同学大而年龄比他小的学生姓名
SELECT SNAME
FROM S
WHERE S# > ALL(
SELECT S#
FROM S
WHERE SNAME = ‘WANG‘
) AND AGE < ALL(
SELECT AGE
FROM S
WHERE SNAME = ‘WANG‘
)
--13.在表SC中检索成绩为空值的学生学号和课程号
SELECT S#, C#
FROM SC
WHERE GRADE IS NULL
--14.检索姓名以L打头的所有学生的姓名和年龄
SELECT SNAME, AGE
FROM S
WHERE SNAME LIKE ‘L%‘
--15.求年龄大于女学生平均年龄的男学生姓名和年龄
SELECT SNAME, AGE
FROM S
WHERE SEX = ‘M‘ AND AGE > (
SELECT AVG(AGE)
FROM S
WHERE SEX = ‘F‘
)
--16.求年龄大于所有女学生年龄的男学生姓名和年龄
SELECT SNAME, AGE
FROM S
WHERE SEX = ‘M‘ AND AGE > ALL (
SELECT AGE
FROM S
WHERE SEX = ‘F‘
)
--17.往关系c中插入一个课程元组（’C8’,’VC++’,’BAO’)
INSERT INTO C VALUES (‘C8‘, ‘VC++‘