SQL

创建: 2021/4/11

完成: 2021/4/13 

 

学习的背景: 以前一直都是用rails的model来处理数据库, 对于各种处理和操作都没什么问题，但是SQL会看不会写。

                 因为现在越来越多云端的操作都用到各种QL, 所以不仅要能看懂还得自己写，于是花点时间系统看下SQL。

第一次系统学习，因为急用，就放弃用英语学了。之后更进一步的学习全部用英文，到时候会重新从头再做一个SQL文档

此次用书: 『ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)』

安装(mac)与SQL的使用

安装 

 

brew install postgresql
brew services start postgresql # 开机自动启动服务器, 数据库不连接几乎不占资源, 自动就行
# 已经安装的可能新版本无法启动, 需要迁移数据。具体看brew的安装日志

 

SQL的种类 

 

DDL

(Data Definition Language)

CREATE	创建database或者table
DROP	删除database或者table
ALTER	修改database或者table的定义

DML

(Data Manipulation Language)

SELECT	搜索行(record)
INSERT	插入新行
UPDATE	更新行
DELETE	删除行

DCL

(Data Control Language)

COMMIT	写入进行的操作
ROLLBACK	取消进行的操作
GRANT	对用户授权
REVOKE	撤销对用户授权

 

 SQL的基本规则

 

句末要  ;  

不区分大写小

虽不区分大小写，一般情况下

关键词大写
table名首字母大写
其他小写

常数记法

字符串	`  包围 例: `字符串`
日期	同字符串
数值	直接写就完了

 

 单词间带空格或改行

 注释

 

单行注释	-- 单行注释
多行注释	/* 多行注释 多行注释 多行注释 */

 

 

创建, 删除, 修改表格

创建数据库

 

CREATE DATABASE 数据库名;

--例: CREATE DATABASE sample;

 

 

创建表格 

 

CREATE TABLE <table-name> (
    < column1> <type1> <options>,
    <column2>  <type2> <options>,
    ...
    <table_options>
);

名称规则

可用文字	字母，数字，_
第一个字符必须是字母
同一个database里table名不能重复

数据类型

mysql

INTEGER	整数
CHAR(n)	固定长字符串  ● 不足的填充空格
VARCHAR(n)	可变长字符串  ● 不填充空格
Date	年月日

 

options

NOT NULL	必须有值
PRIMARY KEY()	主key

 

 删除表格

 

DROP TABLE <table-name>;
-- 例: DROP TABLE Samples;

 

 ● 删了不能回恢复

 

 修改表格定义

 

加列	ALTER TABLE <table-name> ADD COLUMN <column-definition>; -- 例: ALTER TABLE Samples ADD COLUMN age INTEGER NOT NULL;
删列	ALTER TABLE <table-name> DROP COLUMN <column-name>; -- 例: ALTER TABLE Samples DROP COLUMN age;
改表名	ALTER TABLE <table-name> RENAME <new-name>; -- 例: ALTER TABLE Samples Rename NewSamples;
改列名	ALTER TABLE <table-name> RENAME COLUMN <old-name> to <new-name>;

 ● 删了不能回恢复

 

搜索

 SELECT

 

SELECT <column-name>, ... FROM <table-name>;

  ● 运行顺序 

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

*  指代所有列, 使用则不能对列排序。使用创建表格时的列顺序
给列设置别称 AS	SELECT <column-name> AS <anyname-u-like>, ... FROM <table-name>;    ● 别名可以用汉字等, 要双引号包围
输出常数	SELECT shohin_id AS "随便啊", 'a' AS b FROM shohin;    ● 适用常数限制(字符串和日期用 `...` , 数字直接写
去掉重复	SELECT后面加上DISTINCT SELECT DISTINCT ...;  ● 也作为一类显示
条件式WHERE	SELECT ... FROM ... WHERE ...;

 

 

 算术运算符与比较运算符

 

算术运算符

 

+, -
*, /

 ● 含NULL的运算结果都是NULL

 

比较运算符

 

=, >=, <=, >, <	● NULL不参与比较
<>或!=	不等  ● NULL不参与比较
IS NULL
IS NOT NULL

 

 逻辑运算符

NOT
AND
OR

  ● 一边有NULL则结果为UNKNOWN

 

统计与排序

 统计

 统计函数都会先剔除NULL

COUNT()	●   *  统计全行  ●  其他参数则忽略为指定列为null的行
SUM()	先剔除NULL
AVG()	先剔除NULL
MAX()
MIN()

 

 ● 去掉重复再统计, 在参数前加上 DISTINCT 

 

 分组

 

SELECT ...
FROM ...
WHERE ...
GROUP BY <column-1>, <column-2>, ...;

 

分组后的SELECT可以用的参数	● 常数  ● 统计函数  ●  GROUP BY的参数(列)
分组后输出的顺序随机

 

 对统计结果设条件

 

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING <group-condition>;

 

可作HAVING的参数	和分组后的SELECT一样  ● 常数  ● 统计函数  ●  GROUP BY的参数(列)
能用where的别用having

 

 对统计结果排序

 

SELECT ...
FROM ...
WHERE ...
ORDER BY <column1>, <column2>, ...;

顺序

升序	默认就是 ASC
降序	DESC

 

NULL

用了排序则全在开头或结尾

具体开头还是结尾看DB种类

可以用别名

  AS  后面的那个就是别名

可用的参数 

 

没有GROUP BY	所有列
有GROUP BY	GROUP BY的则和分组后的SELECT一样  ● 常数  ● 统计函数  ●  GROUP BY的参数(列)

 

别用数字 

 可以用数字的顺序指代SELECT里指定的列(从1开始)

但是别用，实际开发里眼睛都花了。

 

更新数据

INSERT 

 

 

INSERT INTO <table-name> (columns) INTO VALUES (values);
-- 例
INSERT INTO shohin (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi)
            VALUES (100, 'dummy', 'dummy_bunrui', 1500, 500, '2020-1-1');

 

省略 (columns) 部分则默认全部列，按创建顺序
省略一部分列	● 有DEFAULT的使用DEFAULT值  ● 没有的自动填入NULL, 不能NULL则报错
插入NULL	直接写NULL, 不带任何符号
默认值	设定有DEFAULT的, 有两种方法代入默认值  ● 直接写DEFAULT  ● 省略有DEFAULT的列
复制别的表的数据	INSERT INTO <table-name> (columns) SELECT ...;    ● 可以接所有SELECT可用的, ORDER BY用了也没效果

 

 DELETE

 

DELETE FROM <table-name>; -- 删除所有行
DELETE FROM <table-name> WHERE ...; -- 删除满足条件的行

 

 

 UPDATE

 

 

UPDATE <table-name> SET <column-name> = <value>; -- 更新所有行
UPDATE <table-name> SET <column-name> = <value> WHERE ...; -- 更新满足条件的行

-- 一次更新多个列
UPDATE <table-name> SET (columns) = (values) WHERE ...;

 

 

TRANSACTION

 

BEGIN TRANSACTION -- MYSQL为 START TRANSACTION 或 BEGIN
    DML1 (DML, 即INSERT/DELETE/UPDATE)
    DML2
    ...
COMMIT/ROLLBACK

 

开始transaction

 

SQL Server, PostgreSQL	BEGIN TRANSACTION
MYSQL	START TRANSACTION 或 BEGIN
Oracle, DB2	不需要，默认连接到db后就是transaction, 全靠用户来commit/rollback

 

 transaction模式

 

默认一条SQL一个transaction	SQL Server, MySQL, PostgreSQL等
默认全局transaction	Oracle

 

 

 

 

复杂的sql

 VIEW

 

--创建VIEW
CREATE VIEW (<column1>, <column2>, ...) AS SELECT ...

--删除VIEW
DROP VIEW <view-name>;

和table一样的用法
不保存数据，保存的是VIEW定义的SELECT
VIEW不可以用ORDER BY
可更新内容的VIEW	● SELECT里不含DISTINCT  ● FROM只包含一个表  ● 没有GROUP BY  ● 没有HAVING

 

 

SubQuery 

SELECT里面套SELECT

FROM里面的必须加个别名，   AS  可省略
scalar query	只返回一个值的query 如统计函数 SELECT COUNT(*) FROM sample;    ● 可以写在SELECT的任何地方

 

相关联Query 

 

SELECT
    shohin_mei,
    hanbai_tanka
FROM
    shohin S1
WHERE
    hanbai_tanka > (
        SELECT
            AVG(hanbai_tanka)
        FROM
            shohin S2
        WHERE
            S1.shohin_bunrui = S2.shohin_bunrui);

 

嵌入的SELECT需要和主SELECT相比较的Query

函数, 断言, case

 函数

 

 

算数函数

+, -, *, /
ABS(v)	绝对值
MOD(v1, v2)	v1%v2
ROUND(v, n=0)	四舍五入 n: 位数，默认0

字符串函数

s1 || s2	s1+s2
LENGTH(s)	字符串长度
LOWER(s) UPPER(s)	全小写 全大写
REPLACE(o, s1, s2)	o里所有的s1都替换成s2
SUBSTRING(s FROM p FOR n)	获取s的从p开始的n个字符 p: 从1开始

日期函数

CURRENT_DATE

现在日期

CURRENT_TIME

现在时间

CURRENT_TIMESTAMP

现在日期+现在时间

EXTRACT(t FROM time)

 

 

t	目标 YEAR MONTH DAY HOUR MINUTE SECOND
time	时间日期等

 

变换函数

 

CAST(v AS type)	v: 需要转型的值 type: 转向的类型
COALESCE(...)	返回第一个非NULL的值

统计函数

COUNT()
SUM()
AVG()
MAX(), MIN()

 

 

 断言

 返回Boolean(TRUE/FALSE/UNKNOWN)的函数

LIKE

模式匹配

%	0个以上的任意字符
_	任意的一个字符

 ● 字符串的=是完全一致判断

BETWEEN

 范围内

a BETWEEN b AND C

 

包含b和c, 不包含用 < AND >

IS NULL

IS NOT NULL

IN

NOT IN

 

a IN (v1, v2, v3, ...)

 

 ● 不能用来判断NULL

 ● 参数可以是SELECT句

EXIST

 判断是否存在满足条件的record

EXISTS (subquery)

 

 ● 参数是相关联SELECT

 ● SELECT的第一参数随意, 因为只判断是否存在

    一般用*

 

 

 CASE

 

-- 普通体, 比较都是=
CASE <target>
     WHEN <condition1> THEN <exp1>
     WHEN <condition2> THEN <exp2>
     ...
     ELSE <expn>
END

-- 完全体
CASE
     WHEN <condition1> THEN <exp1>
     WHEN <condition2> THEN <exp2>
     ...
     ELSE <expn>
END

 

 

省略ELSE则为 ELSE NULL
返回值必须是都是一个类型

 

集合运算与结合

集合运算的注意

指定的列必须一样(类名称, 数量, 数据类型)
ORDER BY只能写在最后	因为运行顺序 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

 

UNION

 

SELECT ... FROM ...
UNION
SELECT ... FROM ...;

  ● 接上ALL显示重复的

SELECT ... FROM ...
UNION ALL
SELECT ... FROM ...;

 

 

INTERSECT

 

SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...;

 

 

EXCEPT

 

SELECT ... FROM ...
EXCEPT
SELECT ... FROM ...;

 

  

INNER JOIN 

 只取能结合的部分

SELECT ...
FROM t1 AS A INNER JOIN t2 AS B on A.dummy_id = B.dummy_id
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY...;

-- 例
SELECT users.username, authors.name FROM authors INNER JOIN users on authors.user_id = users.id;

FROM里有多个个表(所以才叫结合)
SELECT里的列，只有两者都有的才必须加表名。但是为了便于理解，一般所有列都加表名
复合表格在SELECT执行完后就没了 要想留着就创建个VIEW

 

 

 OUTER JOIN

 输出所有主表信息， 没有内容结合的填充NULL

SELECT ...
FROM A LEFT OUTER JOIN B
ON A.b_id = b.id;

两个表的顺序就是输出结果的左右顺序
LEFT/RIGHT	设定左边和右边哪边是主表

 

结合三个及以上

 

SELECT
    users.username,
    authors.name,
    fan_comments.body
FROM
    users
    LEFT OUTER JOIN authors ON users.id = authors.user_id
    LEFT OUTER JOIN fan_comments ON authors.id = fan_comments.author_no;

 

INNER/OUTER都可以
每一次结合都是两者之间, 所有OUTER的LEFT/RIGHT是指该次指令前的表和该次指令的表的左右 /*之前的都是左*/ LEFT OUTER JOIN /*这里是右*/

CROSS JOIN

直积, 即所有组合, 行数为两表行数的乘积 

SELECT ...
FROM a CROSS JOIN b;

 

 

高级处理

 window函数

 

<window-function> () OVER ([PARTITION BY <columns>] ORDER BY <sort-column>)

-- 指定行范围
-- 到当前行
<window-function> () OVER ([PARTITION BY <columns>] ORDER BY <sort-column> ROWS m PRECEDING)
-- 到之后n行
<window-function> () OVER ([PARTITION BY <columns>] ORDER BY <sort-column> ROWS n FOLLOWING)
-- 前后
<window-function> () OVER ([PARTITION BY <columns>] 
  ORDER BY <sort-column> ROWS BETWEEN m PRECEDING AND n FOLLOWING)

 

所有统计函数都可做window函数使用

SUM, AVG, COUNT, MAX, MIN

window专用函数

RANK	排序, 同位置的则后面的跳过一部分 例: 1, 2, 2, 4
DENSE_RANK	不跳跃的排序 例: 1, 2, 2, 3
ROW_NUMBER	不重复的号码

 ● window函数的  ()  不能省略

 ●   PARTION BY  可省略

 ● 只能用在SELECT里

 ● window函数的ORDER BY与SELECT的互不影响, SELECT可以用 ORDER BY 

 ● 指定每一次运行函数的范围

前m行~当前行	ROWS m PRECEDING
当前行~后n行	ROWS n FOLLOWING
前m行~后n行	ROWS BETWEEN m PRECEDING AND n FOLLOWING

 

 

GROUPING

 

GROUPING	GROUPING(<column-name>)   如果是GROUPING生成的record, 返回1 不然返回0
ROLLUP	SELECT shohin_bunrui, SUM(hanbai_tanka), AVG(hanbai_tanka) FROM shohin GROUP BY ROLLUP(shohin_bunrui);   对所有统计函数, 都做一个总计  ● 相当于GROUP BY (), GROUP BY (c1), GROUP BY (C1, C2), ...这样  ● GROUP次数 = 0 + 1 + 2 + .... + n = n(n+1)/2
CUBE	● 相当于对参数的所有组合都进行GROUP BY ● GROUP次数 = 2n
GROUPING SETS	●  相当于对每一个元素做一次GROUP  ● GROUP次数 = n

 

java driver