查询使用的数据库文件下载
1. 更名运算
-
old-name as new-name:
-
重命名属性:
-
select name as instructor_name,course_id from instructor,teaches where instructor.ID=teaches.ID;
-
-
重命名关系及其原因:
-
为了使用方便
select T.name, S.course from instructor as T,teaches as S where T.ID=S.ID;
-
为了适用于需要比较同一个关系中的元祖的情况:这种情况下,需要把一个关系同它自身进行笛卡尔积运算,如果不重命名的话,就不可能把一个元祖与其它元祖区分开
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Biology';
说明:像T和S那样被用来重命名关系的标识符在
SQL
标准中被称作相关名称correlation name
,但通常也被称作表别名table alias
,或者相关变量correlation variable
,或元祖变量tuple variable
; -
-
2. 字符串运算
- SQL 使用一对单引号来标示字符串(如果单引号是字符串的组成成分,就用双引号表示)
- SQL 标准中,字符串的相等运算值大小写敏感的(
MySQL
与SQL Server
不区分) - 字符串函数:串联,提取子串,计算字符长度,大小写转换,去掉字符串后面的空格(字符串函数集与名称具体参阅使用的数据库系统);
- 模式匹配:
- 百分号
percent
(%):匹配任意子串; - 下划线
underscore
(_):匹配任意一个字符; - 模式是大小写敏感的;
- Example:
- ‘Intro%’ 匹配任何以"Intro"打头的字符串;
- ‘%Comp%’ 匹配包含"Comp"子串的字符串;
- ‘___’ 匹配只包含三个字符的字符串;
- ‘___%’ 匹配至少包含三个字符的字符串;
- 百分号
- like
- 使用like进行模式匹配
- select dept_name,building from department where building like ‘%atson’;
- select dept_name,building from department where building like ‘%atson’;
- 使用not like比较运算符搜寻不匹配项;
- 使用like进行模式匹配
- escape
- SQL 允许定义转义字符(使用escape定义):
- select dept_name,building from department where building like ‘_%a%’ escape ‘_’;
- select dept_name,building from department where building like ‘_%a%’ escape ‘_’;
- SQL 允许定义转义字符(使用escape定义):
3. 排列元祖的显示次序
-
order by:
- select name from instructor where dept_name=‘Physics’ order by name;(默认使用升序)
- select name from instructor where dept_name=‘Physics’ order by name;(默认使用升序)
-
desc
降序,asc
升序; -
排序可在多个属性进行:
- select * from instructor order by salary desc, name asc;
- select * from instructor order by salary desc, name asc;
4. where子句谓词
-
between … and …
- select name from instructor where salary between 9000 and 10000;
-
not between … and …
- select name from instructor where salary not between 9000 and 10000;
-
and:查找 Biology 系的讲授了课的所有教师的姓名与他们讲授的课程名称
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and depat_name = "Biology";
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology')
References:
[1] Abraham Silberschatz, Henry F Korth, S Sudarshan. Database System Concepts. New York: McGraw-Hill, 2010
Database System Concepts