检索

Select

-- 单/多个字段检索
select ip,addres from table;
-- 检索所有字段
select * from table;

Distinct

用于检索不同的值

select distinct ip from table;

限制结果

-- 限制结果数量和偏移量 
SELECT "engName" FROM directus_users LIMIT 5 OFFSET 5;

排序

-- 按单列/多列排序
SELECT "engName","chnName" FROM directus_users ORDER BY "engName","chnName" desc ;
-- 可简化为2,3 表示选择select中的第二个和第三个字段进行排序
SELECT id,title,date_created FROM story ORDER BY 2,3 desc;

过滤数据

Where

-- 根据名称来过滤
SELECT id,title,date_created FROM story WHERE title='1111' order by title
-- 根据时间过滤
SELECT id, title, date_created
FROM story
WHERE date_created > now() - interval '1 week'
order by title;

范围过滤

-- 范围过滤 上周的数据
SELECT id, title, date_created
FROM story
WHERE date_created BETWEEN now() - interval '2 week' and now() - interval '1 week '
order by title

空值检查

SELECT id, title, date_created, pics
FROM story
WHERE date_created BETWEEN now() - interval '2 week' and now() - interval '1 week'
AND pics IS NULL
order by title

高级过滤

组合where子句

-- 多个add
SELECT id, title, date_created, pics
FROM story
WHERE date_created > now() - interval '3 week'
AND date_created < now() - interval '2 week'
AND pics IS NULL
order by id
-- 带上or
SELECT id, title, date_created, pics
FROM story
WHERE date_created > now() - interval '3 week'
AND pics IS NULL
OR id BETWEEN 300 AND 305
ORDER BY id

求值顺序

SELECT id, title, date_created, pics
FROM story
WHERE id = 305 OR (id > 304 AND pics IS NULL)
ORDER BY id

IN操作符

SELECT id, title, date_created, pics
FROM story
WHERE id IN (305,306)
ORDER BY id

NOT操作符

SELECT id, title, date_created, pics
FROM story
WHERE id NOT IN (2,3)
ORDER BY id LIMIT 5

通配符过滤

%通配符

SELECT id, title, date_created, pics
FROM story
WHERE title LIKE '1%3'
ORDER BY id LIMIT 5

_通配符

SELECT id, title, date_created, pics
FROM story
WHERE title LIKE 'sssss___'
ORDER BY id LIMIT 5

函数

这里不同数据库之间的差异比较大.后期实践再记录

汇总数据

聚集函数

-- 统计所有
SELECT
COUNT(*) as count
FROM story
-- 统计图片,会忽略掉null值
SELECT
COUNT(pics) as count
FROM story
-- 最近的记录
SELECT
MAX(date_created) as count
FROM story

组合聚集函数

SELECT
MAX(date_created) as lasted,
MIN(date_created) as old,
COUNT(pics) as picCount
FROM story

数据分组

-- 根据状态分组
SELECT status,
       COUNT(*) as count
FROM story
GROUP BY status

HAVING过滤分组

-- 过滤改状态下数量大于10的分组
SELECT status,
       COUNT(*) as count
FROM story
GROUP BY status
HAVING COUNT(*) > 10
-- 与where一起使用。统计五周内
SELECT status,
       COUNT(*) as count
FROM story
WHERE date_created > now() - interval '5 week'
GROUP BY status
HAVING COUNT(*) > 3
-- 与排序一起使用
SELECT status,
       COUNT(*) as count
FROM story
GROUP BY status
HAVING COUNT(*) > 3
ORDER BY count desc

子查询

子查询过滤

-- 寻找几个缺陷所属的项目
SELECT name
FROM project
WHERE id IN (SELECT project FROM story ORDER BY random() LIMIT 5);

作为计算字段使用子查询

-- 统计每个人的缺陷数量
SELECT id,
       "engName",
       (SELECT COUNT(*)
        FROM story
        WHERE story.user_created = directus_users.id) AS story_count
FROM directus_users
ORDER BY date_created
LIMIT 5;

联结表

内联结

-- 返回用户相关的缺陷标题
SELECT "engName", story.title
FROM directus_users,
     story
WHERE story.user_created = directus_users.id
LIMIT 8;

使用内联接,输出结果与上面一致

SELECT "engName", story.title
FROM directus_users
         INNER JOIN story
                    ON story.user_created = directus_users.id
LIMIT 8;

-- 找到用户下的项目的缺陷
SELECT "engName", project.name, story.title
FROM directus_users
         INNER JOIN story
                    ON story.user_created = directus_users.id
         INNER JOIN project
                    ON story.project = project.id
LIMIT 8;

创建高级联结

外联结

与内联结不同的是,外联结会展示所有用户的缺陷,即使用户没有缺陷,但是会展示成null

SELECT "engName", story.title
FROM directus_users
         LEFT OUTER JOIN story
                         ON directus_users.id = story.user_created
ORDER BY random()
LIMIT 20;

• 类似上一课提到的内联结，这条 SELECT 语句使用了关键字 OUTER JOIN来指定联结类型(而不是在 WHERE 子句中指定)。但是，与内联结关联 两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表 (RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表)。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表 (Customers 表)中选择所有行。为了从右边的表中选择所有行，需要使 用RIGHT OUTER JOIN

简单来说,LEFT,RIGHT决定使用哪张表的所有行

使用带聚集函数的联结

SELECT "engName", COUNT(story.title)
FROM directus_users
         INNER JOIN story
                    ON directus_users.id = story.user_created
group by "engName"
ORDER BY random()
LIMIT 10;

SELECT "engName", COUNT(story.title)
FROM directus_users
         LEFT OUTER JOIN story
                    ON directus_users.id = story.user_created
group by "engName"
ORDER BY random()
LIMIT 8;