SQL语法速成手册 (1).png
关系型数据库的命名是因为数据库展现了表单形式的不同类型数据之间的关系。
SQL 是结构化数据查询语言，是我们用来像数据管理系统（Data Management system）下达指令时用到的.
Sqlite 是一个轻量的 DBMS。

SQL

SQL 既不是一个 API 也不是一种协议，而是一种声明式语言，只需要告诉它做什么即可。
它是关系型数据库的通用语言。SQL 查询是客户端发送给数据库服务器的文本字符串，指明需要执行的具体操作。

  • SQL is everywhere

  • SQL enables us to pull data from many sources

SQL 语句的三种类型：

  • DDL（Data Definition Language，数据定义语言）：处理用户、数据库以及表单的创建、删除、约束和权限等

  • DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。

  • DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

    • COMMIT ： 确认对数据库中的数据进行的变更

    • ROLLBACK ： 取消对数据库中的数据进行的变更

    • GRANT ： 赋予用户操作权限

    • REVOKE ： 取消用户的操作权限

基本的 DDL 命令

创建数据库 CREATE DATEBASE dbname
选择当前数据库 USE dbname
删除数据库以及表单 DROP DATABASE dbname
创建表单 CREATE TABLE tbname （col defs）
删除表单 DROP TABLE tbname
删除表单中的所有行 TRUNCATE TABLE tbname
修改数据库和表的结构 ALTER

DML 命令

需求 命令
增加行 INSERT INTO tbname VALUES（）
增加行的部分内容 INSERT INTO user(col_name) VALUES（）
删除部分行 DELETE FROM tbname WHERE condition
选择部分行和部分列 SELECT col_name FROM tbname WHERE condition
查询表长 count(*)避免缺失值的影响
查询不重复行 SELECT DISTINCT
限制显示的行数 limit 3
查询值命名 AS
列名可以紧跟在列后边，加一个’’（提倡）
# SElECT SUM(Total) ‘Total Students’ FROM recent_grads
名称有空格要加AS’’
# SELECT SUM(Total) AS ‘Total Students’ FROM recent_grads
（限制返回 2-5 行）查询所有行和所有列 SELECT * FROM tbname（LIMIT 2,5）
修改一列的部分行 UPDATE tbname SET col_name=value WHERE condition

列名也可以用数字代替（表的索引）

基本书写规则

  • 语句以；结束

  • 不区分关键字的大小写，插入表中的数据除外

    • win 系统不区分表名和字段 名的大小写

    • linux/mac 区分

表的创建

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

约束条件主要分为两种：

  • 非空约束：NOT NULL

  • 主键约束:PRIMARY KEY

  • 默认值约束：DEFAULT

数据类型：

  • INTEGER

  • CHAR

  • VARCHAR

  • DATE

表的删除和更新

表的删除：

DROP TABLE <表名>

表的修改（ALTER TABLE）：

ALTER TABLE 和 DROP TABLE 一样会删除整张表，但是 ALTER TABLE 可以进行列的增删:
添加列：ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >
删除列：ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

表的清空

TRUNCATE TABLE TABLE_NAME;
优点：相比 drop/delete，truncate 用来清除数据时，速度最快。

插入数据

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES 子句的值会默认按照从左到右的顺序赋给每一列。
很多 RDBMS 也支持同时插入多行数据：

-- Oracle中的多行INSERT
INSERT ALL INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')

数据的更新

UPDATE <表名>
SET <列名> = <表达式> [, <列名2>=<表达式2>...];
WHERE <条件>;  -- 可选，非常重要。
ORDER BY 子句;  --可选
LIMIT 子句; --可选

查询数据

数据运算与比较

算术运算与比较运算

image.png

  • IN：通常写在 where 从句中，用于匹配，也可以写进去 subquery
SELECT Major_category, Major
  FROM recent_grads
 WHERE Major_category IN
 (
 SELECT Major_category
  FROM recent_grads
 GROUP BY Major_category
 ORDER BY SUM(TOTAL) DESC
LIMIT 3
 )

逻辑运算

SQL 的逻辑运算并不只是简单的 True 和 False 的逻辑运算，因为 SQL 中可能出现的一种数据值为NULL。这使得 SQL 的逻辑运算变成了三值逻辑：
image.png

常用函数

数值计算函数

  • AVG()

  • SUM（col_name,int）

  • ROUND（）:向下取整

  • CAST（col_name AS Float）:整数列转化为实数列

文本处理函数

函 数 说 明
LEFT()（或使用子字符串函数） 返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()） 返回字符串的长度
LOWER()（Access使用LCASE()） 将字符串转换为小写
LTRIM() 去掉字符串左边的空格
RIGHT()（或使用子字符串函数） 返回字符串右边的字符
RTRIM() 去掉字符串右边的空格
SOUNDEX() 返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()） 将字符串转换为大写 
SELECT 'Major: '||LOWER(Major) AS Major,Total,Men,Women,Unemployment_rate,LENGTH(Major) AS Length_of_name
FROM recent_grads
ORDER BY Unemployment_rate DESC;

字符串拼接也可以用concat函数，索引则可以使用substring

字符串的模糊匹配（LIKE 字句）

在处理字符串相关的列时，往往会用到 like 子句：

  • where 进行筛选时，可能会用到模糊匹配

  • CASE 创建新的列时，可能会用到 like 子句

WHERE [column_name] LIKE "pattern"

image.png
当然也可以发现直接用列的值进行匹配，因为像是"jen"这样的值不能被匹配，在使用的时候可以考虑加一个 lower（）函数

得到新的列

一般来说可以通过列的运算与比较来创建新的列，除此以外也可以使用 CASE 语句来创建新的列,主要用于条件筛选的情况:

CASE
WHEN <condition_1> THEN <value_1>
WHEN <condition_2> THEN <value_2>
ELSE <value_3>
END AS <new_column_name>
#如果没有else，未匹配的值会被设为空值


SELECT Major,Sample_size,
        CASE
        WHEN Sample_size<200 THEN 'Small'
        WHEN Sample_size>=200 and Sample_size<1000 THEN 'Medium'
        ELSE 'Large'
        END AS 'Sample_category'
FROM recent_grads

分组

image.png
group by 根据指定的列进行分组，然后对每组进行操作（排序，聚合），将会显示出每一组的最后一个值

提取分组后的部分数据

在 sql 语句中，运行命令的顺序是：

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • SELECT

  • ORDER BY

  • LIMIT

也就是说，我们无法用 where 条件句来对 GroupBy 的结果进行提取，这个时候就引入了 having 命令（发挥的其实就是 where 命令的作用）

subquery（complex and dynamic queries）

A subquery is a query nested within another query.

子查询可以出现在很多地方（where 从句和 select 从句），但无论在哪都需要加’()'包围起来！
SQL_20221010160148

集合运算

每一个子查询可以看成一个 table，table 与 table 之间除了可以进行常见的算术运算和逻辑运算以外，SQL 还增加了一些 table 间进行比较的运算符：
image
例如


SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

ANY,ALL可以看做是一个函数(简单来说)

Combining Colums with Joining

SQL_20221010160243
join 命令可以用来将数据表联系起来，被联系的数据表既可以是已有的表，也可以是 subquery

Inner Join

这个命令主要用来查询两个表中匹配的信息，可以理解为两个表的交集。
SQL_20221010160303
The inner join clause is made up of two parts:

  • INNER JOIN, which tells the SQL engine the name of the table you wish to join in your query, and that you wish to use an inner join.

  • ON, which tells the SQL engine what columns to use to join the two tables.

SELECT * FROM facts AS f
INNER JOIN cities AS c ON c.facts_id = f.id

Left Join

A left join includes all the rows that an inner join will select, plus any rows from the first (or left) table that don’t have a match in the second table. We can see this represented as a Venn diagram.

SQL_20221010160323
该命令会把 b 中不匹配的一部分直接置为空值，然后如果需要调用这些不匹配的数据可以使用 where 从句匹配空值，这时用的是 is 判断
SQL_20221010160439

RIGHT JOIN

同时使用多张表时，可能会用到 RIGHT JOIN 从句
SQL_20221010160501

FULL JOIN

The other join type not supported by SQLite is a full outer join. A full outer join will include all rows from the tables on both sides of the join. We can see a full outer join in the Venn diagram below:

SQL_20221010160520

Self-join

Self-join 是将同一张表 join 起来的方法，一般会用做列与列之间有关系的表的查询，而且对这样的 join 一般只有 INNER 和 LEFT 两种方法。以一张既有劳工又有监督者的表格做例子
images.png

SELECT e1.first_name ||' '|| e1.last_name employee_name,e1.title employee_title,e2.first_name ||" "||e2.last_name supervisor_name,e2.title supervisor_title
FROM employee e1
LEFT JOIN employee e2 on e1.reports_to=e2.employee_id
ORDER BY employee_name

Joining for many tables

SELECT [column_names] FROM [table_name_one]
[join_type] JOIN [table_name_two] ON [join_constraint]
[join_type] JOIN [table_name_three] ON [join_constraint];

The SQL engine interprets joins in order, so the first join will be executed, and then the second join will be executed against the result of the first join. Because of this, we can first build our query in steps:

  • with 0 joins.

  • with 1 join.

  • with 2 joins.

Combining Rows with UNION

[select_statement_one]
UNION
[select_statement_two]

SQL 可以通过 UNION 来实现列拼接，然后需要注意的是拼接前后两个查询（表格的合并可以借助 select *）同一列的数据类型，int 和 float 是兼容的，但是 int 和 text 是不兼容的。
SQL_20221010160547

image.png

Thinking in SQL

  • Think about what data you need in your final output

  • Work out which tables you’ll need to join, and whether you will need to join to a subquery.

    • If you need to join to a subquery, write the subquery first.

  • Then start writing your SELECT clause, followed by the join and any other clauses you will need.

  • Don’t be afraid to write your query in steps, running it as you go— for instance you can run your subquery as a ‘stand alone’ query first to make sure it looks like you want before writing the outer query.

philosophy of writting SQL

在 sql 中大小写和空格是对查询没有影响的

  • If a select statement has more than one column, put each on a new line, indented from the select statement.

  • Always capitalize SQL function names and keywords

  • Put each clause of your query on a new line.

  • Use indenting to make subqueries appear logically separate.

  • use of alias names and shortcuts

  • 换行符可以随便使用但是不要出现空行

指代一个 subquery

Everything in SQL is a table。

with 临时指代(CTE)

子查询嵌套进查询语句中会降低代码的可读性，SQL 提供了 WITH 方法来对子查询进行一个化名（alias）

WITH [alias_name] AS ([subquery])

SELECT [main_query]


SELECT * FROM
    (
     SELECT
         t.name,
         ar.name artist,
         al.title album_name,
         mt.name media_type,
         g.name genre,
         t.milliseconds length_milliseconds
     FROM track t
     INNER JOIN media_type mt ON mt.media_type_id = t.media_type_id
     INNER JOIN genre g ON g.genre_id = t.genre_id
     INNER JOIN album al ON al.album_id = t.album_id
     INNER JOIN artist ar ON ar.artist_id = al.artist_id
    )
WHERE album_name = "Jagged Little Pill";
WITH
    [alias_name] AS ([subquery]),
    [alias_name_2] AS ([subquery_2]),
    [alias_name_3] AS ([subquery_3])

SELECT [main_query]

一下子创建多个时每一个是独立的，可以在第二个创建时调用第一个

CREATE VIEW 永久指代

我们可以理解为 with 命令用来定义一个临时的 subquery，如果想要永久地定义一个 sub（即view)，可以使用下列语句：

CREATE VIEW database.view_name AS
    SELECT * FROM database.table;

为了防止和其他 view 冲突，必须要在 view_name 前边声明 database 的名称（database_name.view/table_name），在定义的时候使用的任何数据库中的表都要在前边加上 database。如果是在本地使用，则不需要写明 database。
创建的一个 view 可以视作一个 table，且不能被重写。在使用是也要写成 database_name.view_name 的形式。

窗口函数

窗口函数(Online Anallytical Processing)也是在分组计算的语境下提出的，这点其实与pandas 中的apply函数有点类似——将已有的数据先进行分组然后再以组为单位运用特定的函数来进行操作。不过需要注意的是，与sql中group by的分组聚合功能相比，窗口函数最大的特点是其计算并不会改变数据的行数，或者可以说其返回值和输入的行数保持一致。

一般来说，窗口函数的语法为:

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>
                [frame_clause]
                )

其中frame_clause是以所在列为中心，通过选择固定数或者固定值的行作为一个frame，然后通过窗口函数进行计算，有点类似于pandas中的rolling函数，只不过更为灵活，具体语法可以看文档

窗口函数可以视为一个字段，一般跟在select后边实现不同的功能，窗口函数主要分为以下三种:

  1. 排序窗口函数，比如 rank(), dense_rank(), row_number()等【排序窗口函数没有输入值，一般以order by 后边的内容为排序依据】
    1. rank() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
    2. row_number() 1 2 3 4 5 6 (赋予唯一排名)
    3. dense_rank() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)
  2. 聚合窗口函数，如 sum,avg, count, max, min 等
  3. 求值窗口函数,如first_value(),last_value(),lead(),lag()等，这类函数与聚合函数相比很明显的区别是比较依赖于order by后的内容。
    对于窗口函数，有以下几点需要强调：
  • 窗口函数相当于是一个先分组（partition by）后排序(order by)的操作，所以一般跟在select后边,操作上晚于group by(一般情况下不把窗口函数和group by一起用，可以先将窗口函数建立成子查询)
  • 不改变原表的行数，常被用于动态分析
  • partition子句原则上可以省略，但是这就失去了窗口函数的意义
    窗口函数是实际业务中经常会用的的一种方法，接下来通过一些具体的应用场景来说明窗口函数的作用

连续登录问题

假设给出一张表log_table，记录了用户的登录信息，包括用户id(uid)，登录时间(in_time),现在需要统计每个用户每天连续登录的天数。这个问题可以用窗口函数来解决，具体的思路如下：

  1. 对于每个用户来说，假如按照登录时间做升序排序(考虑到一天多次登录的情况，需要使用DENSE_RANK()窗口函数并作去重处理),那么登陆日期与排名之差相同的时间(记为flag_time)即为一个连续登录的时间段。
  2. 得到连续登录的时间段后，有两种得到连续登录天数的方法：
  • 考虑到一个flag_time对应一个连续登录时间段，可以按照uid,flag_time分类后使用ROW_NUMBER()窗口函数得到每个时间段的连续登录天数，注意这种方法要事先对表格对uid,in_time排序，否则可能出现一个连续登录时间段内登录天数排列错误的情况；由此衍生出一种更优的策略，即排序字段使用in_time，然后使用DENSE_RANK()窗口函数得到的排序。
SELECT uid,`in_time`,DENSE_RANK() over(PARTITION BY uid,flag_time ORDER BY `in_time`)%7
FROM (
  SELECT uid,`in_time`,DATE_SUB(`in_time`,INTERVAL counter day) as flag_time
FROM(
SELECT DISTINCT uid, DATE(in_time)as in_time,DENSE_RANK() over(partition by uid order by DATE(in_time)) as counter
            FROM tb_user_log
            where artical_id =0 and sign_in=1 and DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' 
) at
)ft

上述问题也叫做最大连续问题，假如要求连续七天登录的用户数 ，对于记录了用户(uid)和登录日期(login_date)的表格，可以通过自连接实现:

  • 用户连续七天登录的充要条件是存在一个日期，其后有六个不同的日期与这一天的日期之差在0,6之间，从这个角度出发，可以对表格作self-join实现如下目的
SELECT t1.uid,t1.login_date as start_date
FROM login_table as t1 JOIN login_table as t2 on t1.uid=t2.uid and DATEDIFF(t2.login_date,t1.login_date) BETWEEN 0 AND 6
GROUP BY uid,t1.login_date
HAVING count(DISTINCT t2.login_date)=7

最大登录天数本质上是一个窗口滞后的问题，也可以通过lead()、lag()两个函数实现，这个和panda中的shift函数比较类似，只是因为两个函数都只接受正数所以分开了，lead(col,offset,default_return)函数会取出当前行往下第offset个值。对于七天登录问题，如果offset=6的情况下返回值与当前日期相差6，则说明当天开始未来六天该用户都有登录。

interact with a SQLite database in Python

SQLite is a database that doesn’t require a standalone server; it stores the entire database as a file on disk. This makes it ideal for working with larger datasets that can fit on disk but not in memory.

two ways to :

  • Sqlite Shell

  • sqlite3

Sqlite Shell

sqlite shell 是一个用来和 sqlite 进行交互的命令行窗口，接下来用它来演示如何进行数据库的创建和修改
Sqlite3 shell 写 query 的原则：

  • 要打开 sqlite shell，需要输入’sqlite3+db_file’

  • 换行只表示换行含义，当输入’;'表示输入的 query 完毕

  • sqlite 有许多点命令，可以用来进行全局设置的调整，一般情况下查询结果是不显示列名的，可以通过’.headers on’调整

  • 可以通过输入’.mode column’来更改输出格式，保证每一行的列宽相等

  • 常用的点命令

    • .help:显示所有点命令和功能

    • .tables:显示当前数据库的列名

    • .shell [command]:执行命令

    • .quit 退出

  1. 表的创建

在 sqlite shell 中，表格的创建借助 CREATE TABLE 命令，格式如下：

CREATE TABLE [table_name] (
  [column1_name] [column1_type] PRIMARY KEY,
  [column2_name] [column2_type],
  [column3_name] [column3_type],
  [...]
);

需要注意的是 sqlite 支持五种（通用）数据类型：

  • TEXT

  • INTEGER

  • REAL:DOUBLE+FLOAT

  • NULL

  • BLOB：二进制

可以使用点命令’.schema [tb_name]'查看表格图解
image.png

  1. 表的删除
DROP TABLE [table_name];

sqlite3

sqlite3 是 python 中一个用来处理处理关系型数据库的标准库，把数据文件存储在了普通文件中。该模块是在 DB-API 的基础上建立的，很多地方是一致的。DB-API 的主要函数：

  • connect():连接数据库

When we’re connected to a database, SQLite locks the database file and prevents any other processes from connecting to the database simultaneously. The SQLite team designed the module this way to keep the database lightweight and to avoid the complexity of multiple processes interacting with the same database.

  • cursor():创建一个 cursor 对象管理查询，作用

    • Run a query against the database

    • Parse the results from the database

    • Convert the results to native Python objects

    • Store the results within the Cursor instance as a local variable

  • execute()和 executemany（）:对数据库执行一个或者多个 SQL 命令（The SQLite library actually allows us to skip creating a Cursor altogether by using the execute method within the Connection object itself，SQLite will create a Cursor instance for us under the hood and run our query against the database, allowing us to skip a step.）

  • fetchone()、fetchmany()、fetchmany(n)：Cusor method（ Fetch the full results set as a list of tuples）

import sqlite3
con = sqlite3.connect('example.db')
cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()
for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)
#在不使用时，记得关闭打开的游标和连接
cur.close()
con.close()

MySQL

image.png

SQLAlchemy

SQLAlchemy 是 python 中用来消除不同数据库语言差异的一个库，虽然不在标准库当中，但是被广泛认可，使用者众多。
SQLAlchemy 不需要导入驱动程序，初始化的连接字符串会做出分配，例如：
dialect + driver :// user : password @ host : port / dbnam

  • dialect: the type of db

  • driver:特殊驱动程序

  • host/port:数据服务器的位置(只有特定情况下会使用端口号 port)

  • dbname:连接到的数据库名字，可以/开头表示绝对路径

image.png
以内置的 sqlite 为例，省略 host、port 等参数（引擎层）

import sqlalchemy as sa
conn=sa.create_engine('sqlite:///:memory:')

SQL 表达式语言

除了基础的 SQL 表达式语言以外，sqlalchemy 还提供了多种 SQL 操作的函数，这种函数调用能解决不同 sql 语言之间的差异，对于关系型数据库应用是一种中间型方案。

from mysqlx import Column
import sqlalchemy as sa
conn=sa.create_engine('sqlite:///Mydb1')
#创建表格
meta=sa.MetaData()
Stu_info=sa.Table('Stu_info',meta,
              sa.Column('Student Name',sa.String,primary_key=True),
              sa.Column('Student Age',sa.INT)
              )
meta.create_all(conn)
#插入数据
conn.execute(Stu_info.insert(("xiaoming",18)))
conn.execute(Stu_info.insert(("xiaohong",27)))
#select all
result=conn.execute(Stu_info.select())
rows=result.fetchall()
print(rows)

对象关系映射

除了通过定义好的 SQL 函数来处理数据库,sqlalchemy 还提供了对象关系映射(ORM)来使用 SQL 表达式语言,隐藏了实际数据库的机制,通过自己定义的对象,ORM 负责处理如何对数据库进行相关处理.有点类似于本土化处理，让操作数据的方式与 python 更加接近！

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
#连接数据库
conn=sa.create_engine('sqlite:///Mydb.db')
Base=declarative_base()
class Stu_info(Base):
    __tablename__='stu_info'
    name=sa.Column('Student Name',sa.String,primary_key=True)
    age=sa.Column('Student Age',sa.INT)
    def __init__(self,name,age) -> None:
        self.name=name
        self.age=age
    def __str__(self) -> str:
        return "<Stu_info({},{})>".format(self.name,self.age)
#创建数据库和表单
Base.metadata.create_all(conn)
first=Stu_info("xiaoming",18)
second=Stu_info("xiaohong",23)
print(first)
#将创建的数据写入数据库
Session=sa.orm.sessionmaker(bind=conn)
session=Session()
session.add(first)
session.add(second)
#session.add_all([first,second])
session.commit()

三个层级属于 sqlalchemy 不同的用法