随笔分类 - 00.Database(PostgreSQL/MS SQL)
数据据相关
摘要：coalesce(expr1,expr2,expr3...) 直到找到一个非null值返回，右边的表达式则不参与运算；若所有为null,返回null. eg:判断json是否包含某属性，若无，则取默认值。 testdb=# select val * 2 from (select id, coales
阅读全文
摘要：PostgresQL 字符串隐式转换JSON脚本： -- 隐式将varchar转换为json CREATE OR REPLACE FUNCTION json_in_varchar(varchar) RETURNS json AS $$ SELECT json_in($1::cstring); $$ LANGUAGE SQL IMMUTABLE; DROP CAST IF EXISTS (v...
阅读全文
摘要：https://www.postgresql.org/docs/9.6/static/functions-json.html Search Documentation: </form> Home → Documentation → Manuals → PostgreSQL 9.6 This page
阅读全文
摘要：testdb=# select * from public.hello_invoker_wrap('吴xx'); hello_invoker_wrap Hello [plpgsql_invoker_prefix]吴xx[python wrap suffix]! plpy.execute返回的结果集为
阅读全文
摘要：from: http://stackoverflow.com/questions/1251233/unable-to-run-postgresql-as-windows-servicepg_ctl -D "C:\Program Files\PostgreSQL\9.1\data" stoppg_ct
阅读全文
摘要：http://dba.stackexchange.com/questions/97095/set-session-custom-variable-to-store-user-id
阅读全文
摘要：http://stackoverflow.com/questions/2499332/how-to-check-if-a-constraint-exists-in-sql-server Easiest way to check for the existence of a constraint (a
阅读全文
摘要：PostgreSQL Cheat Sheet CREATE DATABASE CREATE TABLE (with auto numbering integer id) Add a primary key Create an INDEX Backup a database (command line
阅读全文
摘要：https://www.postgresql.org/docs/9.6/static/functions-json.html PostgreSQL 9.6.1 Documentation Prev Up Chapter 9. Functions and Operators Next 9.15. JS
阅读全文
摘要：https://zh.wikipedia.org/wiki/PostgreSQL PostgreSQL是自由的对象-关系型数据库服务器（数据库管理系统），在灵活的BSD-风格许可证下发行。它在其他开放源代码数据库系统（比如MySQL和Firebird），和专有系统比如Oracle、Sybase、IB
阅读全文
摘要：http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql create table sale ( id int, udid int, assid int ) create table ud ( id int, assid int ) select * from ...
阅读全文
摘要：ref: https://support.microsoft.com/en-us/kb/262499
阅读全文
摘要：[%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', N[%R%?[N]]%.q:COMMENT%, [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N
阅读全文
摘要：ALTER TABLE [table_name] ALTER COLUMN [column_name] [datetime] NOT NULL--datetime是列的类型
阅读全文
摘要：CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int);GOINSERT INTO pvt VALUES (1,4,3,5,4,4);INSERT INTO pvt VALUES (2,4,1,5,5,5);INSERT INTO pvt VALUES (3,4,3,5,4,4);INSERT INTO pvt VALUES (4,4,2,5,5,4);INSERT INTO pvt VALUES (5,5,1,5,5,5);GOselect * from pvtSELECT Ve...
阅读全文
摘要：重命名时，报错：The database could not be exclusively locked to perform the operation.解决办法:1.执行SQLALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE2. F2重命名或USE master;GOEXEC sp_renamedb N'db_name_new', N'db_name';GO3.执行SQLALTER DATABASE db_name SET MULTI_USER需要进入单用户模式才能进行此操作。
阅读全文
摘要：ALTER DATABASE DATABASE_NAME SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;select name, is_read_committed_snapshot_on from sys.databases查看是不是1。from:http://blog.csdn.net/szstephenzhou/article/details/7327732这两种隔离级别都是通过行版本控制在tempdb中生成数据的复本来解决数据的写和读的时候发生锁及阻塞的问题的。不过snapshot isolation需要在数据库中执行..
阅读全文
摘要：SELECTCASE WHEN COLUMN_NAME1 = VALUE1 AND COLUMN_NAME2 = VALUE2 THEN VALUE_WANTED1 ELSE VALUE_WANTED2END, * FROM TABLE_NAMESELECTCASE COLUMN_NAME1 WHEN VALUE1 THEN VALUE_WANTED1 WHEN VALUE2 THEN VALUE_WANTED2 ELSE VALUE_WANTED3END, * FROM TABLE_NAME
阅读全文
