标签：product ym -- sql test id select

SQL 基础

条件查询

语法格式：

select
字段1,字段2...
from
表名
where
条件;

执行顺序：

先from，然后where，最后select.
<,>,<=,>=,<>,!=,and,between…and….,is null,is not null,or,in,not in,and和or联合使用(in等同于or)
模糊查询like

函数

字符函数:

length函数 select length('john');
concat函数拼接字符串
upper、lower函数改变大小写
substr、substring函数截取，索引从1开始
instr函数返回字符串第一次出现的索引，若找不到，则返回0

数学函数:

round()函数四舍五入
ceil函数向上取整
floor函数向下取整
truncate函数截断
mod函数取余

日期函数:

now函数
curdate函数返回当前日期，不包含时间
curtime函数返回当前时间，不包含日期
获取指定部分，年，月，日 select year(now()) as '年';
str_to_date函数
date_format函数

分组函数:

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
分组函数自动忽略NULL。

count(*) filter (where ..)

排序:

asc表示升序，desc表示降序。

完整的DQL语句：select … from … where … group by … having … order by … limit …
执行顺序 5 1 2 3 4 6 7

内连接

内连接之等值连接:

SQL92语法：
select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;

SQL99语法：
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;

内连接之非等值连接:

select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

内连接之自连接

查询员工的上级领导，要求显示员工名和对应的领导名？技巧：一张表看成两张表。
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

外连接

外连接（右外连接）：

select
e.ename,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将
这张表的数据全部查询出来，捎带着关联查询左边的表。
在外连接当中，两张表连接，产生了主次关系。

外连接（左外连接）：

select
e.ename,d.dname
from
dept d
left join
emp e
on
e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

全连接 full join：

FULL OUTER JOIN 关键字返回左表（left_table）和右表（right_table）中所有的行。

如果 "left_table" 表中的行在 "right_table" 中没有匹配或者 "right_table" 表中的行在

"left_table" 表中没有匹配，也会列出这些行。
a1

a2

select a1.ID, name, number, a2.ID, name1, age from a1 full join a2 on a1.ID=a2.ID

结果

子查询:

select
..(select).
from
..(select).
where
..(select).

union合并查询结果集

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
union的效率要高一些。对于表连接来说，每连接一次新表，
则匹配的次数满足笛卡尔积，成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下，
还可以完成两个结果集的拼接。

limit（非常重要）

完整用法：limit startIndex, length
startIndex是起始下标，length是长度。
起始下标从0开始。

缺省用法：limit 5; 这是取前5.

约束

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

postgresql数据库

数据库操作

\q:退出当前选择的库

\l:查看当前有多少数据库

\c 数据库名:切换数据库

create database 数据库名

drop database 数据库名

psql -d 指定的数据库 快速打开指定的数据库

数据表操作

\d:查看表信息

\d 表名: 查看表格信息

建表:

serial自增

create table student1(id serial primary key,name varchar(255),age int);

删表

DROP TABLE student;

数据

insert into student (name,age) values ('张三',20);

update student set name='李四' where id=1;

delete from student where id=1;

数据类型

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范 围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

字符串:

varchar(n),character varying(n), varchar(n) 变长,有长度限制

char(n) 定长,不足不空白

text:变长,character(n),无长度限制

日期:

timestramp:日期和时间

date:日期,无时间

time:时间

字符串函数

拼接字符串

select 'a' || 1;
select 2 || 'a' || 1;
select 2 || 44 || 'a' || 1; --Error

填充字符串

lpad(string text, length int [, fill text])是在字符串左边填充字符，如果不指定要填充的字符，则默认填充空格

select LPAD((99 - 1)::text, 6); -- 98
select LPAD((99 - 1)::text, 6, '0'); --000098
select LPAD((99 + 1)::text, 6, 'ab'); --aba100

大小写转换

upper和lower函数

select upper('test'); --TEST
select lower('TEST'); --test

获取字符串长度

length、char_length和character_length函数

select length('test'); --4
select char_length('test'); --4
select character_length('test'); --4

截取字符串

substring函数，支持下标范围截取或者正则表达式截取,

也可以用substr

select substring('PostgreSQL' from 2 for 4); --ostg
select substring('PostgreSQL' from '[a-z]+'); --ostgre

select substr('PostgreSQL', 2, 0); --空字符串
select substr('PostgreSQL', 2, 1); --o
select substr('PostgreSQL', 2, 4); --ostg
select substr('PostgreSQL', 2); --ostgreSQL

裁剪字符串

trim函数，从字符串的开头/结尾/两边（leading/trailing/both）尽可能多地裁剪指定的字符，不指定则裁剪空白符

select trim(leading 'x' from 'xTestxx'); --Testxx
select trim(trailing 'x' from 'xTestxx'); --xTest
select trim(both 'x' from 'xTestxx'); --Test

select trim(both from ' Test '); --Test
select trim(' Test '); --Test

也可以用ltrim，rtrim或者btrim函数，效果同上：

select ltrim('xTestxxy', 'xy'); --Testxxy
select rtrim('xTestxxy', 'xy'); --xTest
select btrim('xTestxxy', 'xy'); --Test

nullif函数

nullif(a, b)用来检测a参数是否与b参数相等，这里的a、b参数必须是同一种数据类型，否则会报错。当a参数与b参数相等时会返回null，否则返回a参数。

select nullif('test', 'unexpected');		--test
select nullif('unexpected', 'unexpected');	--null

select nullif(233, 111);		--233

判断是否包含字符串

position函数会返回字符串首次出现的位置，如果没有出现则返回0。因此可以通过返回值是否大于0来判断是否包含指定的字符串。strpos函数也是同样的效果：

select position('aa' in 'abcd');	--0
select position('bc' in 'abcd');	--2
select position('bc' in 'abcdabc');	--2

select strpos('abcd','aa');		--0
select strpos('abcd','bc');		--2
select strpos('abcdabc','bc');	--2

合并字符串

string_agg函数可以将一个字符串列合并成一个字符串，该函数需要指定分隔符，还可以指定合并时的顺序，或者是对合并列进行去重：

select ref_no from cnt_item where updated_on between '2021-05-05' and '2021-05-30 16:13:25';
--结果如下：
--ITM2105-000001
--ITM2105-000002
--ITM2105-000003
--ITM2105-000003

select string_agg(ref_no, ',') from cnt_item where updated_on between '2021-05-05' and '2021-05-30 16:13:25';
--合并结果：ITM2105-000001,ITM2105-000002,ITM2105-000003,ITM2105-000003

将字符串合并成一个数组

array_agg和string_agg函数类似，但会把一个字符串列合并成一个数组对象，同样支持指定合并顺序和去重操作；合并成数组后意味着你可以像数组那样去读取它，需要注意的是，数据库的数组下标是从1开始的，而不是从0开始：

select array_agg(distinct ref_no) from cnt_item where updated_on between '2021-05-05' and '2021-05-30 16:13:25';
--合并结果：{ITM2105-000001,ITM2105-000002,ITM2105-000003}

分割字符串

string_to_array函数可以分割字符串，返回值是一个数组：

select string_to_array('ITM2105-000001&ITM2105-000002&ITM2105-000003', '&');
--结果：{ITM2105-000001,ITM2105-000002,ITM2105-000003}

字符串转换

cast(i.monitoring_item_value as double precision)

cast(sum(no_month_flow)as decimal(18,2) )

extract(year from now())

like CONCAT('%', #{userId}, '%')

case when

第一种 格式 : 简单Case函数 :

case 列名
　　　　when 条件值1 then 选择项1
　　　　when 条件值2 then 选项2.......
　　　　else 默认值 end
　　　　
select
　　　　case 　　job_level
　　　　when     '1'     then    '1111'
　　　　when　   '2'     then    '1111'
　　　　when　   '3'     then    '1111'
　　　　else     'eee' end
　　　　from     dbo.employee

第二种 格式 :Case搜索函数

　case
　　　　when 列名= 条件值1 then 选择项1
　　　　when 列名=条件值2 then 选项2.......
　　　　else 默认值 end

eg:
　　　update   employee
　　　　set    e_wage =
　　　　case
　　　　when   job_level = '1'    then e_wage*1.97
　　　　when   job_level = '2'   then e_wage*1.07
　　　　when   job_level = '3'   then e_wage*1.06
　　　　else   e_wage*1.05
　　　　end

SQL中如何处理除数为0

情况一

例如

SELECT A/B FROM TAB
遇到这样的情况，一般的处理方法是用CASE WHEN来判断B的值

SELECT
CASE WHEN B=0 THEN 0 ELSE A/B END
FROM TAB
这样当B如果是0，我们直接赋一个值，避免A/B参与计算报错。

情况二

上面是一种常见的情况，但是如果遇到下面这样的聚合函数呢？

例如

SELECT SUM(A)/COUNT(B) FROM TAB
遇到这样的情况CASE WHEN 是不能判断COUNT(B)的值的，因为WHEN后面的条件不能使用聚合函数（语法要求），这个时候我们可以这样处理

SELECT
ISNULL(SUM(A)/NULLIF(COUNT(B),0),0)
FROM TAB
其中这里使用了两个函数，NULLIF()和ISNULL() NULLIF函数有两个参数,定义如下：

NULLIF( expression1 , expression2 )

其作用就是：如果两个指定的表达式相等，就返回NULL值。

ISNULL函数也有两个参数，定义如下：

ISNULL( expression1 , expression2 )

其作用是：如果第一个参数的结果为NULL，就返回第二个参数的值。

当COUNT(B)的结果为0时，恰好与第二个给定的参数0相等，这个时候NULLIF函数就会返回NULL，而SUM(A)在除以NULL时结果为NULL，外层使用ISNULL函数再对NULL值进行判断，这样最终结果就是0了。

Postgresql 实现数据不存在插入，存在更新

-- 语法形式
INSERT INTO 表名 VALUES ('值１', '值２', ...）
ON CONFLICT(唯一或排除约束字段名)  
DO UPDATE SET 列１='值', 列２='值', ...;

举例

CREATE TABLE test_update_insert (
	id varchar NOT NULL,
	"name" varchar NULL,
	age varchar NULL,
	CONSTRAINT test_update_insert_pk PRIMARY KEY (id)
);

insert into test_update_insert VALUES ('1','zhangsan','age') 
ON CONFLICT (id) 
DO UPDATE set id='1',"name"='zhangsan2', age='18'

什么是窗口函数

SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。

使用postgresql开放局域网访问的方法

1. 修改pg_hba.conf的配置文件
   找到postgresql当时的安装目录PostgreSQL/14/data/pg_hba.conf文件，用记事本或者notepad++等软件打开pg_hba.conf，在大约86行那里有:
   ’ # IPv4 local connections:
   host all all 127.0.0.1/32 scram-sha-256’
   在这个下一行添加一个新行：
   host all all 0.0.0.0/0 scram-sha-256
   然后，保存，重启postgresql服务2.修改防火墙的配置

2. 右键此电脑，属性，隐私和安全，windows安全中心，防火墙和网络保护，高级设置，入站规则，新建规则，在跳出的向导中点击端口，下一步，特定本地端口填写5432，之后一直下一步，最后命名点击完成即可；

3. 获取数据库电脑的ip地址
   win+r然后cmd 输入ipconfig获取本机的ip地址

4. 外网访问
   在另外一台电脑上，ip地址输入3中获取的，端口5432，用户名和密码用安装设置的，即可访问1中的postgresql数据库。

用row_number()实现数据去重

select * from (
	select a.*,row_number() over(partition by 分组字段 order by 排序字段) rn from 表名 a
) b where b.rn = 1

PostgreSQL中的递归

PostgreSQL中的递归常用于解决树形结构的模型，常用的需求有：查询根节点、层级、路径、是否叶子节点、是否死循环等，下面通过实例讲解如何解决这些问题。

• 实例数据准备

test=> CREATE TABLE sys_cbp_test (id INT,parent_id INT);
CREATE TABLE
test=> INSERT INTO sys_cbp_test
test-> VALUES
test->     (1, NULL),
test->     (2, 1),
test->     (3, 2),
test->     (4, 3),
test->     (5, 1),
test->     (6, 5),
test->     (7, 2),
test->     (8, 6),
test->     (5, 8),    --此次存在死循环
test->     (20, NULL),
test->     (21, 20),
test->     (22, 21);
INSERT 0 12
test=> SELECT * FROM sys_cbp_test;
 id | parent_id 
----+-----------
  1 |          
  2 |         1
  3 |         2
  4 |         3
  5 |         1
  6 |         5
  7 |         2
  8 |         6
  5 |         8
 20 |          
 21 |        20
 22 |        21
(12 rows)

• 查询节点层级、起始节点、节点路径、是否存在死循环、是否叶子节点等

test=> WITH RECURSIVE x(id, --节点ID
test(> parent_id,           --父节点ID
test(> level,               --节点层级
test(> path,                --节点路径
test(> root,                --起始节点
test(> cycle                --节点是否循环
test(> ) AS
test->  (--起始节点查询语句
test(>   SELECT id,
test(>          parent_id,
test(>          1,
test(>          ARRAY[id],
test(>          id    AS root,
test(>          FALSE
test(>     FROM sys_cbp_test
test(>    WHERE parent_id IS NULL  --查询的起始节点
test(>   UNION ALL
test(>   --递归循环语句
test(>   SELECT b.id,
test(>          b.parent_id,
test(>          level + 1,         --递归一层节点层级加1
test(>          x.path || b.id,    --把节点按照递归的次序依次存入数组
test(>          x.root,            --记录起始节点
test(>          b.id = ANY(path)   --判断当前节点是否已存在路径数组中，如果存在说明存在循环
test(>     FROM x,
test(>          sys_cbp_test b
test(>    WHERE x.id = b.parent_id --从起始节点往下递归
test(>      AND NOT cycle          --终止循环节点
test(>    )
test-> SELECT id,
test->        x.parent_id,
test->        level,
test->        array_to_string(path, '->') AS path,
test->        root,
test->        path,
test->        cycle,
test->        CASE 
test->            WHEN t.parent_id IS NULL THEN 
test->              TRUE 
test->            ELSE 
test->              FALSE 
test->        END AS isleaf  --是否叶子节点
test->   FROM x 
test->   LEFT JOIN (SELECT parent_id 
test(>                FROM sys_cbp_test 
test(>               GROUP BY parent_id) t 
test->     ON x.id = t.parent_id
test->--WHERE NOT cycle --去掉循环重复的节点，反过来也可以查找哪个节点存在死循环
test->  ORDER BY id;
 id | parent_id | level |     path      | root |    path     | cycle | isleaf 
----+-----------+-------+---------------+------+-------------+-------+--------
  1 |           |     1 | 1             |    1 | {1}         | f     | f
  2 |         1 |     2 | 1->2          |    1 | {1,2}       | f     | f
  3 |         2 |     3 | 1->2->3       |    1 | {1,2,3}     | f     | f
  4 |         3 |     4 | 1->2->3->4    |    1 | {1,2,3,4}   | f     | t
  5 |         8 |     5 | 1->5->6->8->5 |    1 | {1,5,6,8,5} | t     | f --此行一般需要过滤掉（NOT cycle）
  5 |         1 |     2 | 1->5          |    1 | {1,5}       | f     | f
  6 |         5 |     3 | 1->5->6       |    1 | {1,5,6}     | f     | f
  7 |         2 |     3 | 1->2->7       |    1 | {1,2,7}     | f     | t
  8 |         6 |     4 | 1->5->6->8    |    1 | {1,5,6,8}   | f     | f
 20 |           |     1 | 20            |   20 | {20}        | f     | f
 21 |        20 |     2 | 20->21        |   20 | {20,21}     | f     | f
 22 |        21 |     3 | 20->21->22    |   20 | {20,21,22}  | f     | t
(12 rows)

PG数据库与索引优化

# 创建索引
CREATE INDEX idx_commodity //索引名
 ON commodity //表名
 **USING** **btree**  //用B树实现
 (commodity_id); //作用的具体列
 
#删除索引
DROP INDEX idx_commodity;

#查询索引
select * from pg_indexes where tablename =‘commodity'; 

#重命名索引
ALTER INEDX idx_commodity rename to idx_commodity2;

SQL语句执行顺序

select 5 ..

from 1 ..

where 2 ..

group by 3 ..

having 4 ..

order by 6 ..

limit 7…

postgresql执行计划

通过在SQL语句前面加 explain操作，就可以获取到该SQL的执行计划.该SQL并没有实际执行。输出的执行计划有如下特点：
1.查询规划：以规划为节点的树形结构。树的最底节点是扫描节点：他返回表中的原数据行。
2.不同的表有不同的扫描节点类型：顺序扫描.索引扫描和位图索引扫描。
3.也有非表列源，如ALUES子句并设置FROM返回.他们有白己的扫描类型。
4.如果查询需要关联，聚合，排序观其他操作，会在扫描节点之上增加节点执行这些操作。
5.explain的输出是每个树节点显示一行，内客是基本节点类型和执行节点的消耗评估。可能会出现同级别的节点，从汇总行节点缩进显示节点的其他属性，第一行（最上节点的汇总行）是评估执行计划的总消耗，这个值越小好。

explain (analyze true|false,verbose true|false,costs true|false,buffers true|false,format text|xml|json|yaml)

• analyze：真实执行sql获取执行计划，dml语句不想改变数据库数据可放入事务，执行完后回滚，该选项默认值为false。
• verbose：用于显示计划的附加信息，附加信息有计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称，该选项默认值为false。
• costs：显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度，该选项默认值为true。
• buffers：显示关于缓冲器使用的信息，只能与analyze参数一起使用，显示的缓冲区信息包括共享块、本地块、临时块的读写块数，表、索引、临时表、临时索引及排序和物化计划中使用的磁盘块，上层节点使用的块数包含所有节点使用的块数。该选项默认值为false。
• format：指定数据格式，可以是text、xml、json、yaml，默认值为text。

执行计划路径方式

全表扫描（顺序扫描）：seq scan，所有数据块，从头扫到尾。
索引扫描：index scan，在索引中找到数据行的位置，然后到表的数据块中把对应的数据读出。
位图索引扫描：bitmap index scan，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图把表的数据文件中相应的数据读取出来。
条件过滤：filter
嵌套循环连接：nestloop join，外表（驱动表）小，内表（被驱动表）大
散列连接：hash join，用较小的表在内存中建立散列表，再去扫描较大的表，连接的表均为小表。
合并连接：merge join，通常散列连接比合并连接性能好，当有索引或结果已经被排序时，合并连接性能好。

表的统计信息

select
       -- 当前表所占用的数据页数量       
       relpages,
       -- 当前表一共有多少行组（记录）
       reltuples 
from pg_class 
where relname = #tableName#

单列统计信息

-- 查询某个表的某一列的统计信息
select 
	-- Null值率
	null_frac,
	-- 去重后的值个数/其与总元组比值的负数
	n_distinct,
	-- 高频值个数，简称MCV，由default_statistics_target（默认100）决定记录多少个
	most_common_vals,
	-- 高频值占比(与MCV一一对应)
	most_common_freqs,
	-- 等频直方图，剔除MCV后，每个区间范围中的元素在总元组中的占比一样
	histogram_bounds,
	-- 物理行序与索引行序的相关性
	correlation,
	-- 平均行宽度，单位Byte
	avg_width
  from pg_stats 
 where tablename = #tableName# 
   and attname = #columnName#

示例

--估算
chis=> explain select * from patient;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on patient  (cost=0.00..106205.66 rows=2406066 width=655)
(1 row)

--实际运行
chis=> explain analyze select * from patient;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on patient  (cost=0.00..106205.66 rows=2406066 width=655) (actual time=0.016..13198.645 rows=2406066 loops=1)
 Planning Time: 0.158 ms
 Execution Time: 13293.205 ms
(3 rows)

--查询页面读取数和扫描行数
chis=> SELECT relpages, reltuples FROM pg_class WHERE relname = 'patient';
 relpages |  reltuples
----------+--------------
    82145 | 2.406066e+06

--成本代价
cost = relpages(页面读取数)*seq_page_cost(1.0) + reltuples(扫描的行数)*cpu_tuple_cost(0.01) + reltuples(扫描的行数)*cpu_operator_cost(0.0025)

postgresql 索引

PostgreSQL提供了多种索引类型，主要的四种：B-Tree、Hash、GiST和GIN
每种索引使用了不同的算法，都有其适合的查询类型
缺省时，CREATE INDEX命令将创建B-Tree索引

1.b-tree 索引

是标准的索引类型，B代表平衡，主要用于等于和范围查询，
具体使用场景：
◆当索引列包含操作符"<、<=、=、>=和>"作为查询条件时
◆在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中
◆基于模式匹配操作符的查询，仅当模式存在一个常量，且该常量位于模式字符串的开头时 如col LIKE'foo%'索引才会生效，否则将会执行全表扫描，如：col LIKE'%bar

create index 索引名称 on 表名(字段名)

2.hash 索引

散列(Hash)索引只能处理简单的等于比较
当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引

3.GIST
GiST(通用搜索树提供了一种用于存储数据的方式来构建平衡的树结构
二维几何操作：点位面计算

4.多列索引

PostgreSQL中的索引可以定义在数据表的多个字段上

CREATE TABLE test2(major int,minor int,name varchar)
CREATE INDEX test2_mm_idx ON test2(major,minor);

·在大多数情况下，单一字段上的索引就己经足够了，并且还节约时间和空间

5.唯一索引

只有B-Tree索引可以被声明为唯一索引
如果索引声明为唯一索引，就不允许出现多个索引值相同的行(NULL值相互间不相等)

6.表达式索引

主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较时

CREATE INDEX test1_lower_.col1_idx ON test1(lower(col1)

--  1. BTREE索引：
create table t1 (id int, info text);
insert into t1 values(generate_series(1,100000), md5(random()::text));
analyze t1;
--不建立索引  顺序扫描
explain select * from t1 where t1.id = 10007;
--建立索引
create index on t1(id);
explain select * from t1 where t1.id =10007;
explain select * from t1 where t1.id <200;
--模糊查询使用索引
explain select * from t1 where t1.info like '0123%';


--2. Hash索引（简单的等值比较 ）当索引列涉及到等于操作比较时，优化器会考虑使用Hash索引。Hash索引是通过比较hash值来查找定位，如果hash索引列的数据重复度比较高，容易产生严重的hash冲突，从而降低查询效率，因此这种情况下，不适合hash索引。
create table t2 (id int, info text);
insert into t2 values(generate_series(1,100000), md5(random()::text));
create index on t2 using hash(id);
explain select * from t2 where id = 10008;
--非等于操作不会用到hash索引
explain select * from t2 where id < 10008;

--3. GiST索引  GiST可以用来做位置搜索，如包含、相交、左边、右边等。和Btree索引相比，GiST多字段索引在查询条件中包含索引字段的任何子集都会使用索引扫描，而Btree索引只有查询条件包含第一个索引字段才会使用索引扫描。GiST索引特定操作符类型高度依赖于索引策略（操作符类）。GiST跟Btree索引相比，索引创建耗时较长，占用空间也比较大。

create table t3(a bigint, b timestamp without time zone,c varchar(64));
insert into t3 values(generate_series(1,100000), now()::timestamp, md5(random()::text));
--建立了BTREE组合索引(a, b)，如果SQL where条件中有a或者a,b都可以使用该组合索引，但是如果where条件中只有b，则无法使用索引。
create index on t3(a, b);
explain select * from t3 where b = '2022-11-18 17:50:29.245683';
explain select * from t3 where a = 10000;
--GiST可以解决这种情况。
create extension btree_gist;create index idx_t3_gist on t3 using gist(a,b);
analyze t3;
explain select * from t3 where a = '10000' or b = '2022-11-18 17:50:29.245683';
explain select * from t3 where a = '10000' and b = '2022-11-18 17:50:29.245683';

--4. SP-GiST索引与GIST类似

--5. GIN索引
create table t4(id int, info text);
insert into t4 values(generate_series(1,10000), md5(random()::text));
create index idx_t4_gin on t4 using gin(to_tsvector('english',info));
analyze t4;
explain select * from t4 where to_tsvector('english', info) @@ plainto_tsquery( 'hello');

--6. BRIN索引 比较BTREE索引 占用空间小，单查询性能不如BTREE索引，BRIN索引适用于存储流式数据日志。
create table t5(id int, name text);
insert into t5 values(generate_series(1,100000), md5(random()::text));
create index idx_t5_brin on t5 using brin(id);
analyze t5;
explain select * from t5 where id > 98765;

--7.唯一索引
create table t6(id int, name text);
create unique index idx_t6_id on t6 (id);

--8. 多列索引（复合索引）  最左匹配原则
create table t7(c1 int ,c2 int,c3 int);
create index idx_t7 on t7 using btree(c1,c2,c3);
insert into t7 select random()*100,random()*100,random()*100 from generate_series(1,1000000);
explain analyze select * from t7 where c1=10 and c2=40 and c3 =80;
explain analyze select * from t7 where c1=10 and c2=40;
explain analyze select * from t7 where c1=10 and c3 =80;
explain analyze select * from t7 where c2=40 and c3 =80;
explain analyze select * from t7 where c2 =80;
explain analyze select * from t7 where c3 =80;

--9.表达式索引
create table t8(c1 int ,c2 varchar,c3 int);
create index idx_t8_c1 on t8((c2*10));

--10. .函数索引
create index idx_t8_c1 on t8(max(c3));

索引优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

7. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

· 第一步：根据where条件和统计信息生成执行计划，得到数据。

· 第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。

· 第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

11. 只需要一条数据的时候，使用limit 1

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

SELECT语句优化

1. 避免出现select *

首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

SQL执行采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

7.当只需要一条数据的时候，使用limit 1

查询条件优化

1. 对于复杂的查询，可以使用中间临时表 暂存数据

2. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL 

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION ALL 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

4.拆分复杂SQL为多个小SQL，避免大事务

· 简单的SQL容易使用到MySQL的QUERY CACHE；

· 减少锁表时间特别是使用MyISAM存储引擎的表；

· 可以使用多核CPU。

5. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

6. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：

select * from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
   order by gmt_create asc limit 0, 15) a, t 
      where a.id = t.id; 

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

查询正在执行的所有sql

SELECT
	pid,
	datname,
	usename,
	client_addr,
	application_name,
	STATE,
	backend_start,
	xact_start,
	xact_stay,
	query_start,
	query_stay,
	REPLACE ( query, chr( 10 ), ' ' ) AS query 
FROM
	(
	SELECT
		pgsa.pid AS pid,
		pgsa.datname AS datname,
		pgsa.usename AS usename,
		pgsa.client_addr client_addr,
		pgsa.application_name AS application_name,
		pgsa.STATE AS STATE,
		pgsa.backend_start AS backend_start,
		pgsa.xact_start AS xact_start,
		EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,
		pgsa.query_start AS query_start,
		EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,
		pgsa.query AS query 
	FROM
		pg_stat_activity AS pgsa 
	WHERE
		pgsa.STATE != 'idle' 
		AND pgsa.STATE != 'idle in transaction' 
		AND pgsa.STATE != 'idle in transaction (aborted)' 
	) idleconnections 
ORDER BY
	query_stay DESC

如果要释放掉慢查询资源

SELECT pg_terminate_backend(PID);

sql server 字符编码

先说下结论：

• 如果你想在数据库中存储emoji表情等特殊字符，就需要将varchar改为nvarchar并且在编写sql语句时使用大N（N'小明...'）。
• 默认的sqlserver中字符串的排序比较已忽略掉了全角/半角、大/小写的差别，所以不用担心因为大小写和全半角搜索不到数据的问题。

一、说说字符集、字符集编码和排序规则

字符集：罗列所有图形字符的一张大表。
比如：

GBK字符集（中国制造）: 罗列了所有的中文简体、繁体字的一张大表。
Unicode字符集（全世界通用）：罗列了世界上所有图形字符的一张大表。
字符集编码：将字符集上罗列的图形字符存储到计算机中的一种编码规则。
比如:

GBK字符编码（中国制造）：GBK本身既是字符集，也是编码规则；
UTF-16：存储Unicode字符集的一种编码规则，使用2个（中文）、4个（emoji表情）字节存储。
UTF-8：也是存储Unicode字符集的一种编码规则，使用1个、2个、3个、4个字节存储。
排序规则：定义各个图形字符之间的大小比较规则，比如：是否区分大小写，区分全角和半角等。
在软件使用中，一般我们只指定字符编码即可，因为确定了字符编码字符集自然就确定了。

但是在数据库类软件中，我们除了要指定编码规则，还需要指定排序规则，因为，数据库是要提供模糊匹配、排序显示功能的。

二、sqlserver中字符集编码和排序规则

上面虽然把字符集、字符集编码、排序规则的概念分的很清，但sqlserver中的配置并没有分的太清。

在sqlserver中没有单独设置字符集编码的地方，仅能设置排序规则。

至于最终使用什么字符集编码，则会受排序规则、数据类型(varchar、nvarchar）的影响。

一般我们在window或window server上安装sqlserver 2014，安装后默认排序规则是：Chinese_PRC_CI_AS。

Chinese_PRC：针对大陆简体字UNICODE的排序规则。
CI：CaseSensitivity，指定不区分大小写。
AS：AccentSensitivity，指定区分重音。

sqlserver设置排序规则有四个级别：服务器（示例级别）、数据库、 列级别、表达式级别：

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;
注意：
Chinese_PRC_CI_AS不是存储为UTF8，事实上，直到SqlServer2019才引入UTF-8的支持（Chinese_PRC_CI_AS_UTF8）。

参照：
《Introducing UTF-8 support for SQL Server》
《排序规则和 Unicode 支持》

附：查询排序规则元数据

-- 查询数据库的排序规则
select serverproperty(N'Collation');

--查询所有受支持的排序规则
select * from fn_helpcollations()

-- 查询列的排序规则
select name,collation_name from sys.columns where collation_name is not null

三、排序规则对sql语句的一影响

观察排序规则对sql语句影响的时候，我主要从以下两个方面考虑：

全角/半角
大写/小写
至于其他的重音、假名则是很难用到，直接用默认的即可。

分析其他数据库的排序规则时，也可以从这两个方面考虑，经过综合对比，sqlserver中的排序规则还是很贴近实际情况的，其他的数据库或多或少都有问题。

全角/半角对查询的影响：

我们期望的效果：当使用like查询或=比较符时，数据库能忽略掉全角“ａ”和 半角"a"，将它们判定相等。
sqlserver不负众望，默认情况下的比较是忽略全角/半角的，所以，sqlserver能做到判定它们相等。

看如下实验：

create table test(
	id int identity(1,1),
	name varchar(50)
);
insert into test values
('角ａ啊'),--全角ａ
('角a啊');--半角a

--测试like中的全角半角处理
select * from test where name like '%a%';--半角a
select * from test where name like '%ａ%';--全角ａ

--测试=中的全角半角处理
select * from test where name = '角a啊';--半角a
select * from test where name = '角ａ啊';--全角ａ

上面的查询结果均显示：

大小写对查询的影响：

我们期望的效果：当使用like查询或=比较符时，数据库能忽略掉大写和小写的区别，将它们判定相等。
sqlserver不负众望，默认情况下的比较是忽略大小写的，所以，sqlserver能做到判定它们相等。

看如下实验：

create table test(
	id int identity(1,1),
	name varchar(50)
)

insert into test values
('A'),('a');

select * from test where name like 'A';
select * from test where name like 'a';

select * from test where name = 'A';
select * from test where name = 'a';

上面的查询结果均显示：

四、sqlserver究竟会以何种编码存储字符

上面只说了sqlserver中的默认排序规则：Chinese_PRC_CI_AS，但是sqlserver中究竟是以哪种编码规则存储的呢？

具体用什么编码规则存储不仅受排序规则的影响，还受数据类型的影响（nvarchar、varchar）。

以Chinese_PRC_CI_AS排序规则为例：

当我们使用varchar类型时，存储到表里面的数据其实就是GBK编码，因为：Chinese_PRC对应的是区域编码（ANSI，活动代码页：936）是GBK。可以通过sql查询得知：

SELECT COLLATIONPROPERTY('Chinese_PRC_CI_AS', 'CodePage')

当我们使用nvarchar类型时，存储到表里面的是UTF-16的编码。

验证不同数据类型对应的编码规则：

首先，我们数据库的排序规则是：Chinese_PRC_CI_AS，已知 汉字“王”的各种格式编码如下：

参考：《细说ASCII、GB2312/GBK/GB18030、Unicode、UTF-8/UTF-16/UTF-32编码》

准备数据：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('王','王')

select 
	name,nname,
	convert (varbinary (20) , name) as name_binary,
	convert (varbinary (20) , nname) as nname_binary
from test

由此，可以看出，数据表中存储使用的字符编码和排序规则和数据类型都有关系。

具体可以参考：《nchar 和 nvarchar (Transact-SQL)》

五、sqlserver中数据类型varchar和nvarchar的区别、N’'的作用

其实从上面的实验中可以看得出来，对于Chinese_PRC_CI_AS排序规则来说：

varchar类型的列使用ANSI编码，也即GBK存储数据（不能存储emoji表情）；
而nvarchar类型的列使用UTF-16编码存储数据（能存储所有Unicode字符，包含emoji表情）。
我们知道，UTF-16编码规则最少使用2个字节存储字符，即使对于英文字母“W”也要使用两个字节，而GBK编码则可以使用1个字节存储英文字母“W”，所有当只有英文字母时，varchar显然要节省空间。

下面是存储英文字母“W”的示例：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('W','W')

select 
	name,nname,
	convert (varbinary (20) , name) as name_binary,
	convert (varbinary (20) , nname) as nname_binary
from test

nvarchar(8000)和varchar(8000) 中的8000指的是字节数，而不是字符数，GBK中一个字符可以是1个字节或两个字节，UTF-16中一个字符则是2个或4个字节，所以在计算最多存储多少文字时不要搞错了。

N’小明’ 的作用：

这个大N表示单引号中的字符串使用的是Unicode编码，当我们sqlserver引擎会用Unicode的方式去解析"小明"，而不是用GBK编码的方式。

一般来说，我们感觉不到加不加大N的区别，那是因为我们存储的数据都在Unicode的常见字符区域内，如果我们存储一个emoji表情，那么加不加大N的就立马看得出来了，看如下的实验：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('王','王')
insert into test values(N'王',N'王')
insert into test values('W','W')
insert into test values(N'W',N'W')
insert into test values('

标签：product,ym,--,sql,test,id,select	

From： https://www.cnblogs.com/liurui12138/p/17196307.html