数据库选择

sqlite

  • 小巧
  • 无需安装
  • 支持标准sql 语法

数据库客户端

DBeaver

  • DBeaver (Community Edition)
  • 支持 MySQL, PostgreSQL, SQLite等数据库
  • 免费,开源
  • 跨平台

DBeaver 下载安装

DBeaver示例数据库-表介绍

  • DBeaver 默认自带的示例数据库
  • 使用的是业界非常著名的 Chinook 数据库。
  • 它模拟了一个 “数字音乐商店”（类似早期的 iTunes Store）的业务场景。
  • 这个数据库结构非常经典，特别适合用来练习 SQL 的 JOIN（连接查询）、GROUP BY（分组统计）
  • 以及理解主外键关系。

数据表

  • Album 音乐专辑表
  • Artist 艺术家/歌手
  • Customer 客户
  • Employee 员工
  • Genre 流派
  • Invoice 发票/订单头
  • InvoiceLine 发票明细/订单行
  • MediaType 媒体格式
  • Playlist 歌单
  • PlaylistTrack 歌单
  • sqlite_schema sqlite元数据
  • Track 歌曲/单曲

查看表关系

img_3.png

select 练习

1.select单表查询

select * from Track;

笔记:

  • *是通配符, 意思是: 所有列
  • sql语句以;结尾

2.select查询指定字段

select Name, Composer from Track;

3.where(带条件的查询)

-- TrackId=1的歌曲
select * from Track where TrackId = 1;

--- Composer=AC/DC
select * from Track where Composer = 'AC/DC';

-- 找出时长 (Milliseconds) 超过 5 分钟（300,000 毫秒）的歌
select * from Track WHERE Milliseconds > 300000;

4.where-and-or带条件的查询2

-- 时长超过5分钟并且作曲家是 'AC/DC' 的所有歌
select * from Track WHERE Milliseconds > 300000 and Composer = 'AC/DC';

-- 曲家是 U2 或者 AC/DC
select * from Track WHERE Composer='U2' or Composer = 'AC/DC';

5.order by 排序

-- 按文件大小 (Bytes) 从大到小排序(desc: 降序, asc: 长序)
select * from Track order by Bytes desc;

6.聚合函数

-- 歌曲总数
select count(*) as 总数 from Track;

-- 单价格最贵的歌曲
select max(UnitPrice) as 最贵单价 from Track;

-- 平均时长
select AVG(Milliseconds) as 平均时长 from Track;

select 
    count(*) as 总数,
    max(UnitPrice) as 最贵单价,
    avg(Milliseconds) as 平均时长
from Track;

as 是给字段取个别名, 避免显示为表达式

7.join连接查询

问题: 我想看每首歌的歌名，以及它属于哪张专辑的标题。 问题来了：

  • 歌名在 Track 表里。
  • 专辑标题在 Album 表里。
  • 它们是分开的
SELECT 
    Track.Name AS 歌名,
    Album.Title AS 专辑名
FROM Track
INNER JOIN Album ON Track.AlbumId = Album.AlbumId;

sql解释：

  • 从 Track 表开始。
  • 内连接 (INNER JOIN) 上 Album 表。
  • 连接条件 (ON) 是：Track 的身份证号 = Album 的身份证号。

8.group by分组统计

-- 每个作曲家 (Composer) 各有多少首歌
SELECT Composer, COUNT(*) AS 歌曲数量
FROM Track
GROUP BY Composer;

-- 排序与筛选 (谁是创作狂人？)
SELECT Composer, COUNT(*) AS 歌曲数量
FROM Track
WHERE Composer IS NOT NULL
GROUP BY Composer
ORDER BY 歌曲数量 DESC;

9.having子句

注意: WHERE 和 HAVING 的区别

  • WHERE：是在分组之前干活。它过滤的是原始数据
  • HAVING：是在分组之后干活。它过滤的是统计结果
-- 作品数量超过 10 首的专辑 (AlbumId)
SELECT AlbumId, COUNT(*) AS 歌曲数
FROM Track
GROUP BY AlbumId
HAVING COUNT(*) > 10
ORDER BY 歌曲数 DESC;