1.不存在项计数(left join)

在课程前面部分， 我们曾多次学到如何对单个表中的行进行计数。以 count 聚合函数聚合某一列后，将返回表中的行数，或者返回 group by 子句每个值的行数。
例如，
select count(*) from animals;
-- 返回动物园中动物的数量

select count(*) from animals where species = ‘gorilla’;
-- 返回大猩猩的数量

select species, count(*) from animals group by species;
-- 返回各物种的名称以及对应的动物数量

如果要对 join 的结果计数，情况会略为复杂。以我们在本课前面部分提到的某个商店的 products 和 sales 表为例：

image.png

假定我们想知道每种产品的售出次数。也就是说，对于 products 表中的每个 sku 值，我们需要知道它在 sales 表中出现的次数。可以从如下查询入手：

select products.name, products.sku, count(*) as num
  from products join sales
    on products.sku = sales.sku
  group by products.sku;

但此查询可能与我们的预期不尽相同。如果某个 sku 从未售出（即 sales 表中没有与之对应的条目），则此查询根本不会返回相应的行。
如果我们希望显示计数值为零的行，结果会令我们大失所望！
但有一种方法可以让数据库显示零计数。为此，我们需要对此查询做两处改动 —

select products.name, products.sku, count(sales.sku) as num
  from products left join sales
    on products.sku = sales.sku
  group by products.sku;

此查询将为 products 表中的每个产品返回一行，即使产品在 sales 表中没有销售记录亦是如此。
究竟做了怎样的改动呢？
首先，我们用 count(sales.sku) 代替了 count(*)。这意味着数据库仅对定义了 sales.sku 的行（而非所有行）进行计数。
其次，我们用 left join 代替了 join。

什么是 left join（左连接）？
SQL 支持多种连接类型。本课程前面部分提到的连接称为inner join（内连接），这是最常用的连接类型。在 SQL 中，连接默认指的就是内连接，不需特别说明。
其次常用的是left join（左连接）及其镜像伙伴right join（右连接）。“左”和“右”分别指连接运算符左侧和右侧的表。（在上例中，左侧的表是 products，右侧的表是 sales。）

常规（内）连接仅返回符合连接条件的两表共有条目的数据行。left join 则不仅返回这些行，还返回左侧表中有条目、但右侧表不存在的行。right join 同理，但用于右侧表。（就像“join”实际是“inner join”的简称一样，“left join”实际是“left outer join”的简称。但在 SQL 中直接输入“left join”即可，这样可减少键盘操作。我们不妨就这样做。）

练习
在此练习中，有一个表格描述了各种代码文件中的 bug。
表格如下所示：

create table programs (
    name text,
    filename text
 );

 create table bugs (
    filename text,
    description text,
    id serial primary key
 );

写一个查询，计算每个程序中的 bug 数量。使其包含没有 bug 的程序对应的行。

QUERY =
select programs.name, count(bugs.id) as num
   from programs left join bugs
        on programs.filename = bugs.filename
   group by programs.name
   order by num;

2.试用DB-API

2.1 python使用DB-API库访问数据库

import sqlite3                      #导入sqlite3库
# Fetch some student records from the database.     
db = sqlite3.connect("students")              #连接数据库 'students'是所连接的数据库的名字
cursor = db.cursor()
query = "select name, id from students order by name;"
cursor.execute(query)
results = cursor.fetchall()

# First, what data structure did we get?
print "Row data:"
print results

# And let's loop over it too:
print
print "Student names:"
for row in results:
  print "  ", row[0]

db.close()               #关闭连接

2.2 在 DB API 中插入

when you want to inset data into a table from your code,there's one more step you have to do after executing your insert queries.
you have to commit your changes to the database

import sqlite3
db = sqlite3.connect("testdb")
c = db.cursor()
c.execute("insert into balloons values ('blue', 'water') ")
db.commit()
db.close()

3.子查询

image.png

highest score per team:

query=
"select max(score) as bigscore from mooseball group by team"

average high-score's score:

query=
"select avg(bigscore) from
(selece max(score) as bigscore from mooseball group by team) as maxes"

SQL语法要求我们对子查询结果表格进行命名，这里取名叫maxes

练习

Find the players whose weight is less than the average.

cursor.execute("select name,weight from players,(select avg(weight) as av from players) as subs where weight < av;")
return cursor.fetchall()

4.视图

image.png

query=
"create view course_size as 
select course_id,count(*) as num from enrollment group by course_id"

5.习题

5.1 创建发票行

题目说明
重新构建你的数据库，并将数据导入新的系统中。
我们先仔细看看如何构建并填充你的本地数据库。
下面的方框展示了 Album 表格结构，包括主键和外键。
看看该表格以及下面的 CREATE TABLE 语句，了解相互之间的关系。
首先，与 Chinook 数据库断开连接。
> .exit
创建一个新的数据库，名称随意。
sqlite3 UdaciousMusic.db
现在可以用我们的首个表格填充该数据库了。
下图展示了关于 Album 表格的一些信息。
我们可以用它在我们的新数据库中构建一个表格。

表格：Album
| 列 | 数据类型 | 主键 | 外键|
| AlbumId INTEGER YES NO |
| Title TEXT NO NO |
| ArtistId INTEGER NO YES |
| UnitPrice REAL NO NO |
| Quantity INTEGER NO NO |
+====================+===============+=================+==============+
我们可以根据该信息决定我们的架构外观。

CREATE TABLE Album
(
    AlbumId INTEGER PRIMARY KEY,
    Title TEXT,
    ArtistId INTEGER,
    FOREIGN KEY (ArtistId) REFERENCES Artist (ArtistId) 
);

尝试将该架构粘贴到你的本地数据库中。
我们看看是否会发生什么。
sqlite> .tables
Album <--- 你能看到 Album 表格吗？希望能看到！
我们的新表格中有任何数据吗？
sqlite> SELECT * FROM Album;
你能看到数据吗？希望没看到，因为我们还没添加任何数据呢！
打开 Album.sql 标签。你可以将这几行内容直接复制粘贴到你的 sqlite 终端里。
现在尝试再次运行查询。你看到数据了，不错！

根据上一个示例构建 InvoiceLine 表格。

QUERY='''CREATE TABLE InvoiceLine
(
    InvoiceLineId INTEGER PRIMARY KEY,
    InvoiceId INTEGER,
    TrackId INTEGER,
    UnitPrice REAL,
    Quantity INTEGER,
    FOREIGN KEY (InvoiceId) REFERENCES Invoice (InvoiceId)
    FOREIGN KEY (TrackId) REFERENCES Track (TrackId)
);

insert 语句：

INSERT INTO [InvoiceLine] ([InvoiceLineId], [InvoiceId], [TrackId], [UnitPrice], [Quantity]) VALUES (15, 4, 54, 0.99, 1);

准备好后，运行查询以创建 InvoiceLine 表格，并使用 InvoiceLine.sql 文件中的数据填充该表格。

5.2 在 SQLite 中使用 CSV

在 SQLite 中使用 CSV 非常简单 :)
SQLite 支持导入和导出 CSV 文件。

sqlite> .mode csv                                <--- set mode
sqlite> .output newFile.csv                  <--- 

将 CSV 文件导入表中

sqlite3 new.db   <--- 如果要将 csv 文件导入新数据库中，切记要先创建该数据库。
sqlite> CREATE TABLE myTable() <--- 构建模式！
sqlite> .import newFile.csv myTable <---将文件newFile.csv导入表myTable

5.3 DB-API操场

import sqlite3

# Fetch records from either chinook.db
db = sqlite3.connect("chinook.db")
c = db.cursor()
QUERY = "SELECT * FROM Invoice;"
c.execute(QUERY)
rows = c.fetchall()

print "Row data:"
print rows


print "your output:"
for row in rows:
    print "  ", row[0:]

import pandas as pd    
df = pd.DataFrame(rows)
print df
db.close()

5.3 本地查询

5.3.1 本地查询 - 连接媒体类型与音轨

how many 'pop' songs have an 'MPEG audio file' type?

import sqlite3

db = sqlite3.connect("chinook.db")
c = db.cursor()
QUERY = '''SELECT COUNT(*)
FROM Genre 
JOIN Track on Genre.GenreId=Track.GenreId 
JOIN MediaType on Track.MediaTypeId = MediaType.MediaTypeId 
WHERE Genre.Name='Pop' and MediaType.Name='MPEG audio file'
GROUP BY Genre.Name;
'''
c.execute(QUERY)
rows = c.fetchall()

print "Row data:"
print rows
db.close()

5.3.2 本地查询 - 爵士乐音轨

how many unique customers have purchased a jazz track?

import sqlite3
db = sqlite3.connect("chinook.db")
c = db.cursor()
QUERY = '''SELECT COUNT(DISTINCT Customer.CustomerId)
FROM Customer 
join Invoice ON Customer.CustomerId=Invoice.CustomerId
join InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceId
join Track on InvoiceLine.TrackId = Track.TrackId
join Genre on Track.GenreId=Genre.GenreId
WHERE Genre.name='Jazz';
'''
c.execute(QUERY)
rows = c.fetchall()
print rows
db.close()

5.3.3 低于平均值的歌曲长度

import sqlite3
db = sqlite3.connect("chinook.db")
c = db.cursor()
QUERY = '''SELECT Genre.Name,COUNT(Genre.Name) as num 
FROM Genre,Track,
((SELECT avg(Milliseconds) as average 
FROM Genre,Track WHERE Genre.GenreId=Track.GenreId) as subquery)
WHERE Genre.GenreId=Track.GenreId
and Track.Milliseconds<average
GROUP BY Genre.name
ORDER BY num DESC
LIMIT 1;
'''
c.execute(QUERY)
rows = c.fetchall()