practice：sqlite扩展运算实践

select 中的通配符

星号 * 可以用在 select 子句中表示所有的属性，考虑查询

sqlite> select instructor.*
   ...> from instructor, teaches
   ...> where instructor.id = teaches.id;
10101|srinivasan|comp. sci.|65000
10101|srinivasan|comp. sci.|65000
10101|srinivasan|comp. sci.|65000
12121|wu|finance|90000
15151|mozart|music|40000
22222|einstein|physics|95000
32343|el said|history|60000
45565|katz|comp. sci.|75000
45565|katz|comp. sci.|75000
76766|crick|biology|72000
76766|crick|biology|72000
83821|brandt|comp. sci.|92000
83821|brandt|comp. sci.|92000
83821|brandt|comp. sci.|92000
98345|kim|elec. eng.|80000

它返回了 instructor 中所有的属性

元组的排序

sql 为用户提供了一些结果显示的顺序的控制，order by 子句可以让查询结果中元组按升序显示，考虑按字典序列出 physics 系的所有教师，我们有

sqlite> select name
   ...> from instructor
   ...> where dept_name = 'physics'
   ...> order by name;
einstein
gold

为了使用降序，我们可以用 desc 表示降序，完整地说，可以用 asc 表示升序，此外，排序可以在多个属性上进行，例如我们希望按 salary 的降序列出整个 instructor 关系，如果有几名教师工资相同，则按姓名升序排列，我们有

sqlite> select * 
   ...> from instructor
   ...> order by salary desc, name asc;
22222|einstein|physics|95000
83821|brandt|comp. sci.|92000
12121|wu|finance|90000
33456|gold|physics|87000
98345|kim|elec. eng.|80000
76543|singh|finance|80000
45565|katz|comp. sci.|75000
76766|crick|biology|72000
10101|srinivasan|comp. sci.|65000
58583|califieri|history|62000
32343|el said|history|60000
15151|mozart|music|40000

行文至此处才发现一直输出的都是内容而没有列名，一旦列多起来之后实在不方便，可以在 sqlite 交互式界面下使用 .headers on 指令打开列名显示

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: university.db
sqlite> .headers on
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: university.db
sqlite> select *
   ...> from instructor
   ...> order by salary desc, name asc;
id|name|dept_name|salary
22222|einstein|physics|95000
83821|brandt|comp. sci.|92000
12121|wu|finance|90000
33456|gold|physics|87000
98345|kim|elec. eng.|80000
76543|singh|finance|80000
45565|katz|comp. sci.|75000
76766|crick|biology|72000
10101|srinivasan|comp. sci.|65000
58583|califieri|history|62000
32343|el said|history|60000
15151|mozart|music|40000

where 子句谓词

为了简化 where 子句，sql 提供 between 运算符来说明一个值落在一个闭区间内

考虑查询，找出工资在 90000 美元和 100000 美元之间的教师的姓名，直观地有

sqlite> select name 
   ...> from instructor
   ...> where salary <= 100000 and salary >= 90000;
name
wu
einstein
brandt

可以用 between 运算符改写为

sqlite> select name
   ...> from instructor
   ...> where salary between 90000 and 100000;
name
wu
einstein
brandt

类似的有 not between 运算符

sqlite> select name
   ...> from instructor
   ...> where salary not between 90000 and 100000;
name
srinivasan
mozart
el said
gold
katz
califieri
singh
crick
kim

where 子句还支持在元组上进行比较，考虑查询，查找 biology 系讲授了课程的所有教师的姓名和他们所讲授的课程

sqlite> select name, course_id
   ...> from instructor, teaches
   ...> where (instructor.id, dept_name) = (teaches.id, 'biology');
name|course_id
crick|bio-101
crick|bio-301