SQL - SELECT Statement -1
Projection Queries
Projection Queries从关系表中选择整个列,并且没有WHERE语句。
找到所有部门的完整信息:
SELECT code, name, total_staff_number, chair, budget
FROM DEPARTMENT;
SELECT *
FROM DEPARTMENT;
关键字DISTINCT可用于消除查询结果中的重复项。
找出所有课程的独特学分:
SELECT DISTINCT credits
FROM COURSE;
使用行函数的查询 (Queries with row functions)
调用row function,并对从关系表中选择的每一行进行一次处理。
用大写字母列出部门名称:
SELECT UPPER(name)
FROM DEPARTMENT;
从所有课程代码和全部课程标题中找出首三个字符:
SELECT SUBSTR(cnum, 1, 3), title
FROM COURSE;
显示部门名称和增加了10%之后的预算:
SELECT name, 1.1*IFNULL(budget,0)
FROM DEPARTMENT;
使用组函数的查询 (Queries with group functions)
调用group functions,并对一组行进行一次处理。
找出课程总数:
SELECT COUNT(*)
FROM COURSE;
找出所有部门员工的总数:
SELECT SUM(total_staff_number)
FROM DEPARTMENT;
找出每个部门的平均预算:
SELECT AVG(IFNULL(budget, 0))
FROM DEPARTMENT;
AVG代表平均,同理MAX等等。。
特殊查询
比如把SQL作为计算器:
计算30小时*每小时90.30美元。
SELECT 30 * 90.30
FROM DUAL;
把SQL作为日记本:
明天是几号?
SELECT SYSDATE + 1
FROM DUAL;
为当前日期添加2个月:
SELECT ADD_MONTHS(SYSDATE, 2)
FROM DUAL;
自2021年1月1日至今已经过了多少天?
SELECT SYSDATE - '2021-01-01'
FROM DUAL;
简单条件查询
找到所有6或12个学分课程的标题:
SELECT title
FROM COURSE
WHERE credits IN (6, 12);
找到所有标题包含一个词“database”的标题和cnum:
SELECT title, cnum
FROM COURSE
WHERE UPPER(title) LIKE '%DATABASE%';
注意格式,字符串要用单引和百分号夹起来。
使用布尔表达式的查询
查找所有由物理部门提供的所有6个学分的课程名称:
SELECT title
FROM COURSE
WHERE (credits = 6) AND (offered_by ='Physics');
同理,如果是OR逻辑就把上面的AND换成OR即可。
查找所有不是由物理部门提供的所有6个学分的课程名称:
SELECT title
FROM COURSE
WHERE not (offered_by = 'Physics') AND (credits = 6);
还有一种写法:
SELECT title
FROM COURSE
WHERE (offered_by != 'Physics') AND (credits = 6);
!=就相当于不等于。
下篇文章:
SQL SELECT语句-2:
https://blog.csdn.net/Jifu_M/article/details/112683860
SQL SELECT语句-3:
https://blog.csdn.net/Jifu_M/article/details/112690498
SQL SELECT语句-4:
https://blog.csdn.net/Jifu_M/article/details/112705789
References
-
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.1 - 6.3.4 Data Manipulation, Pearson Education Ltd, 2015.
-
D. Darmawikarta, Oracle SQL : A Beginner’s Tutorial Brainy Software Inc. First Edition: June 2014.