SQL笔记

AS: SQL支持列别名,AS关键词赋予 eg select Concat(RTRIM(vend_name), ‘(’,RTRIM(vend_country),’)’) AS vend_title FROM Vendors ORDER BY vend_name
RTRIM:除去检索出来的值的右边的空格
LTRIM:除去左边的空格
Concat:拼接值,构成单个值
IN:操作符优点
1:IN语法更清楚
2:IN执行的更快, 性能更优
3:IN最大的有点可以包含其他SELECT语句,能够更动态建立WHERE字句.
eg: select vend_name from vendors where vend_country IN(‘UAS’, ‘CHINA’) ORDER BY vend_name

通配符:
LIKE ‘FISH%’; LIKE ‘%@126.com%’ LIKE ‘CHI%GOOD’
执行算术计算:
select prod_id , c_nums , c_price , c_numsc_price AS c_all_price where order_num = 2008;
SELECT Trim(‘abc’)去掉空格
SELECT Now() ,Now返回当前日期和时间.
大写: UPPER() 小写:LOWER()
//日期处理(DATE_PART), 可查看https://www.w3school.com.cn/sql/func_datepart.asp
COUNT: 用法COUNT() 计算行,包括NULL, COUNT(cloumn):对特定列中具有值的行进行计数, 忽略NULL值

DISTINCT:只包含不同的值.
GROUP BY :分组, 通常和COUNT()一起检索, 查看分组的数, 比如查看各个厂商具有多少产品
HAVING: 过了分组, 分组后设定过滤条件, 检索出符合条件的行 eg: SELECT manu_name , COUNT() from Orders GROUP BY manu_name HAVING COUNT() >= 10; 检索出大于或等于10个产品的厂商.
ORDER BY :输出排列顺序.

内联结:select vend_name, prod_name, prod_price from Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
INNER JOIN 联结两个表 ON: 联结条件
主键: 表的主键key, 数据库自动自增.
外键:关联表的主键, 将表与表之间进行关联的key, 比如: 用户表 和 产品表, 用户id, 产品id
索引:查询时, 提高效率, 但删除, 更新操作会影响效率

select pg_size_pretty(pg_total_relation_size(‘channel_src’)); -----表的总大小，包括索引的大小 14GB
select pg_size_pretty(pg_relation_size(‘channel_src’)); -----查看表大小
select pg_size_pretty(pg_relation_size(‘channel_src’)); 12GB
select pg_size_pretty(pg_indexes_size(‘a’)); 查看索引大小
select pg_size_pretty(pg_table_size(‘a’)); 查看表空间大小

select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; ----查看所有数据库的大小

垃圾整理:
VACUUM 整理整个数据库
VACUUM table_name 整理表的垃圾数据
VACUUM FULL table_name 完全整理, 可以整理出更多空间
select pg_size_pretty(pg_total_relation_size(‘channel_src’));
VACUUM FULL channel_src;
select pg_size_pretty(pg_total_relation_size(‘channel_src’));

select query from pg_stat_activity where query like ‘%channel_src%’;
这样看下，当前关于这个表有什么操作
这个表现在多大？
select pg_relation_size(‘表名’);

postgresql DO匿名块打印: https://www.jb51.cc/postgresql/191981.html
使用RAISE NOTICE语句
postgres=# DO $$BEGINRAISENOTIC{E}^{\prime }Hello$$;
NOTICE: Hello pavel
DO

mybatis:
传递map的key
Map传递参数, 直接在sql中取出key即可!

创建外键:
ALTER TABLE channel_src ADD CONSTRAINT outKey FOREIGN KEY (c_device_code) REFERENCES device_src(c_index_code)—必须是表的主键才能建立外键
数据库中sequence:
设置:SELECT setval(‘index_code_seq’, 17);
查询当前值:SELECT currval(‘index_code_seq’);
查询下一个值:SELECT nextval(‘index_code_seq’);
迁移老数据到新的数据库，需要把id迁移过来，这时序列值就需要修改为当前最大值加1了。
使用这个语句就可以让s_seq的序列值从7开始了。
alter sequence s_seq restart with 7

	起来的时候, 设置下sequence从当前最大值开始计数
	
	
	select c_index_code from device_src AS D, data_dst AS DT, service_src AS S, access_group AS A , rule AS R  where c_index_code like 'iac%' AND char_length(c_index_code) = 11
	
	
	CREATE OR REPLACE FUNCTION f_seq(iac_imsdb text) RETURNS VOID AS

KaTeX parse error: Expected group after '_' at position 1: _̲
DECLARE
v_sql1 text;
v_sql2 text;
v_sql3 text;
v_id1 bigint;
v_id2 bigint;
v_record record;
BEGIN
FOR v_record IN SELECT table_name,column_name,substr(column_default,position(’’’’ in column_default)+1,position(’:’ in column_default)-2-position(’’’’ in column_default)) as seq_name FROM information_schema.columns where table_catalog=$1 and substr(column_default,1,7)=‘nextval’
LOOP
v_sql1:=‘select coalesce(max(’||v_record.column_name||’),0) from ‘||v_record.table_name||’;’;
v_sql2:=‘select coalesce(last_value,0) from pg_sequences where sequencename=’’’||v_record.seq_name||’’’;’;
execute v_sql1 into v_id1;
execute v_sql2 into v_id2;
IF v_id1>v_id2 THEN
v_sql3:=‘alter sequence ‘||v_record.seq_name||’ restart with ‘||(v_id1+1)::text||’;’;
execute v_sql3;
END IF;
END LOOP;
RETURN;
END;
KaTeX parse error: Expected group after '_' at position 1: _̲ LANGUAGE PLPGSQL;

select c_index_code from device_src where c_index_code like ‘iac%’ AND char_length(c_index_code) = 11 union select c_index_code from data_dst where c_index_code like ‘iac%’ AND char_length(c_index_code) = 11 union select c_index_code from service_src where c_index_code like ‘iac%’ AND char_length(c_index_code) = 11 union select c_index_code from access_group where c_index_code like ‘iac%’ AND char_length(c_index_code) = 11 union select c_index_code from rule where c_index_code like ‘iac%’ AND char_length(c_index_code) = 11 order by c_index_code desc limit 1

UPDATE channel_src AS C SET C.c_device_uuid = (select device_src.c_device_uuid FROM device_src WHERE C.c_device_code = device_src.c_index_code) WHERE C.c_index_code IN (SELECT C.c_index_code FROM channel_src AS C, device_src AS D WHERE C.c_device_code = D.c_index_code AND C.c_device_uuid != D.c_device_uuid)

UPDATE channel_src AS C SET c_device_uuid =‘index’ WHERE C.c_index_code IN (SELECT C.c_index_code FROM channel_src AS C, device_src AS D WHERE C.c_device_code = D.c_index_code AND C.c_device_uuid != D.c_device_uuid)

update channel_src a set c_device_uuid = (select c_device_uuid from device_src b where a.c_device_uuid != b.c_device_uuid and a.c_device_code = b.c_index_code);

update channel_src a set c_device_uuid = (select c_device_uuid from device_src b where a.c_device_code=b.c_index_code AND a.c_protocol = b.c_protocol)