2018-12-12

SQL

MySQL 个人笔记 Z_Oracle_复习笔记 N_PostgreSQL N_PowerDesigner

DDL, DML

DDL

数据(结构)定义语言DDL(Data Definition Language)创建和修改数据库表结构。常用的语句create,alter,drop,rename

DML

数据操纵语言DML（Data Manipulation Language）常用语句 insert,delete,update

查询插入

MYSQL

INSERT INTO SELECT

 
INSERT INTO table1 (column1, column2)
SELECT column3, column4 FROM table2 WHERE some_condition;

INSERT INTO SELECT 语句用于从一个表中选择数据并将其插入到另一个表中。它将已存在的表中的数据插入到目标表中。

INSERT INTO farming_record (id, orgFarmInfoId, startTime, endTime) SELECT id, orgFarmInfoId, orgLandInfoId, startTime, endTime FROM farming_record_模拟数据

ORACLE

INSERT INTO … select

 INSERT INTO "user" ("id" , "username" , "password", "user_type")  select  ID AS "id" ,ACCOUNT_ "username" , PASSWORD_ AS "password", 200 AS  "user_type"  FROM  JG_ACCOUNT

查询更新

MySQL

UPDATE student s , class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id
 
UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name='test11',c.stu_name='test11'
 
UPDATE student s LEFT JOIN class c ON s.class_id = c.id SET s.class_name='test22',c.stu_name='test22'
 
UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name=c.name , c.stu_name=s.name
 
 
update soil_test_result_temp_gl t join soil_test_result r on t.id  = r.id 
set t.latitude  = r.latitude, t.longitude  = r.longitude

ORACEL

--ORACEL
UPDATE  "user" u SET u."user_type" = 100   WHERE EXISTS(SELECT 1 FROM ORG_PERSON p WHERE u."id" = p.SYSTEMUSER_ID  );
 
UPDATE JG_OBJECT jg 
set jg.PHONE_ = (SELECT u."username"  FROM  "user" u  WHERE  jg.JG_ACCOUNT_ID = u."id" )
	WHERE EXISTS(SELECT 1 FROM  "user" u  WHERE  jg.JG_ACCOUNT_ID = u."id" )
    
UPDATE JY_QUARANTINE_ECARD_B_P t
	SET t.ECARDPERSON_ = (SELECT u."name" FROM "user" u WHERE u."id" =  t.SYSTEMUSER_ AND t.ECARDPERSON_ IS NULL AND t.SERIAL_ LIKE '%20210901%')
	WHERE EXISTS(SELECT 1 FROM "user" u WHERE u."id" =  t.SYSTEMUSER_ AND t.ECARDPERSON_ IS NULL AND t.SERIAL_ LIKE '%20210901%')
	
 
 
update A set A.c=(select e from B where B.b=A.b),
    A.d=(select f from B where B.b=A.b)
where exists(select 1 from B where B.b=A.b);

MYSQL

--MYSQL
UPDATE a INNER JOIN ( SELECT yy FROM b ) c ON a.id = c.id 
SET a.xx = c.yy
 
update base_area  INNER JOIN ( SELECT * FROM base_area ) p ON p.id = base_area.base_area_id  
set  base_area.COUNTY_ =  p.NAME_  where base_area.level_ = 4
 
update jg_object j inner JOIN `user` u ON j.JG_ACCOUNT_ID = u.id
set j.JG_ACCOUNT_ID = null where u.user_type = 0

查询删除

 
DELETE sur FROM sys_user_role sur 
left JOIN sys_user su ON sur.user_id = su.id 
WHERE su.id  IS NULL 
 
 
delete f FROM `od_postdocaccfile` f left join od_postdoc d on f.OD_POSTDOC_ID = d.id where d.CREATEDATE < '2019-01-01 000000'

ORACLE

DELETE  FROM  "user" u  WHERE EXISTS( SELECT 1 FROM JG_ACCOUNT acc  WHERE u."username" = acc. ACCOUNT_  );

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集;

UNION 内部的每个 SELECT 语句必须拥有相同数量的列; 列也必须拥有相似的数据类型; 同时, 每个 SELECT 语句中的列的顺序必须相同;
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名;

select col_1, col_2, col_3 from ex_813ff83074264c48b898b4edab63e98a where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_a1a7a2fa3b7a48cba8d6bb8e96fad7f9 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_07c8c10b349f4ba5afa59a25c606a392 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_54d65655a59d436d88fe1c99218ec358 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_b17cf21ee34c45a19ee18560ac0eaaa3 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_08c893f9c8e84ab78cd54b4a64a00657 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_ae5129181a554052ab805ccec27fefff where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_636e107c889041faaa47588951e04c89 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_5aa1aeb59e3e4f8787eef7e4a84383cd where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_77ef4ab5f4854266af93877693ace8b4 where col_1 like '%豆粕%' and col_2 like '%水分%' UNION
select col_1, col_2, col_3 from ex_813ff83074264c48b898b4edab63e98a where col_1 like '%豆粕%' and col_2 like '%水分%'

UNION 操作符选取不同的值; 如果允许重复的值, 请使用 UNION ALL

附维恩图

SQL Joins

LEFT JOIN 等同与 LEFT OUTER JOIN RIGHT JOIN 等同与 RIGHT OUTER JOIN

MySQL

MYSQL 事务脚本

1、用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN 或 START TRANSACTION开用于开始一个事务。
ROLLBACK 事务回滚，取消之前的更改。
COMMIT事务确认，提交事务，使更改永久生效。

-- 开始事务
START TRANSACTION;
 
-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
 
-- 判断是否要提交还是回滚
IF (条件) THEN
    COMMIT; -- 提交事务
ELSE
    ROLLBACK; -- 回滚事务
END IF;

2、直接用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

MYSQL 变量语句

临时变量

mysql中变量不用事前申明, 在用的时候直接用”@变量名”使用就可以了;

第一种用法 set @num=1; 或set @num=1; //这里要使用变量来保存数据, 直接使用@num变量

第二种用法 select @num=1; 或 select @num=字段名 from 表名 where ……

注意上面两种赋值符号, 使用set时可以用”=“或” =”, 但是使用select时必须用” =赋值”

实例使用

--第一次赋值, 第二次 使用
SELECT @abc=(case when sex='男' then 'yes男' else sex end), @abc as '结果', a.* FROM storage.test_inp a
 
 
select table_name as "table_name", COLUMN_NAME as "name", DATA_TYPE as "type" ,
@c_len=(case  when CHARACTER_MAXIMUM_LENGTH is null then 0 else CHARACTER_MAXIMUM_LENGTH end) ,
@n_len=(case  when NUMERIC_PRECISION is null then 0 else CHARACTER_MAXIMUM_LENGTH end) ,
(@c_len+@n_len) as "length"
from information_schema.columns where table_name='test_inp' and TABLE_SCHEMA = 'storage' ;
--
select table_name as "table_name", COLUMN_NAME as "name", DATA_TYPE as "type" ,
@len=(case  when NUMERIC_PRECISION is null then 0 else CHARACTER_MAXIMUM_LENGTH end)+(case  when CHARACTER_MAXIMUM_LENGTH is null then 0 else CHARACTER_MAXIMUM_LENGTH end) ,
(case  when @len is null then 0 else @len end) as "length"
from information_schema.columns where table_name='test_inp' and TABLE_SCHEMA = 'storage' ;

实例获取行号

SELECT @rowno=@rowno + 1 AS rowno,a.* FROM TEST_INP a,(SELECT @rowno=0) b

mysql本身不像oracle那样使用 rownum 来显示行号的功能.但是可以使用设置变量的方式来实现这一功能. mysql使用 @ 定义用户变量, @@ 定义系统变量.

可以把这条语句理解为a,b两张表的联合查询, a表more列, b表有rowNo一列, 且rowNo的初始值为0（@rowNum =0）, 以后每行的值依次加1（@rowNum=@rowNum+1）

全局变量

全局变量@@ 系统变量, 只能读取, 不能修改, 如@@error

ONLY_FULL_GROUP_BY

暂时会话设置 select 列不在 group by 里面的问题 SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

实例获取行号

SELECT @rowno=@rowno + 1 AS rowno,a.* FROM TEST_INP a,(SELECT @rowno=0) b

mysql本身不像oracle那样使用 rownum 来显示行号的功能.但是可以使用设置变量的方式来实现这一功能. mysql使用 @ 定义用户变量, @@ 定义系统变量.

可以把这条语句理解为a,b两张表的联合查询, a表more列, b表有rowNo一列, 且rowNo的初始值为0（@rowNum =0）, 以后每行的值依次加1（@rowNum=@rowNum+1）

注意 select有顺序问题, a.* 放后面

MySQL 存储过程

尽量别用..

菜鸟教程

开窗 over

注意 MySQL 8.0 以上版本 over 才可用

开窗, 主要作用是为每条数据提供分组的聚合函数结果, 参考下, [开窗返回行号 over()]

SELECT
 val,
 ROW_NUMBER() OVER w AS 'row_number',
 NTILE(2)     OVER w AS 'ntile2',
 NTILE(4)     OVER w AS 'ntile4'
FROM numbers
WINDOW w AS (ORDER BY val);
 
 
 
SELECT
	year, country, product, profit,
	SUM(profit) OVER() AS total_profit,
	SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;

like 难走索引的问题

为了解决这个问题，MySQL提供了ngram全文解析器。从版本5.7.6开始，MySQL包含ngram全文解析器作为内置服务器插件，这意味着MySQL在MySQL数据库服务器启动时自动加载此插件。MySQL支持InnoDB和MyISAM存储引擎的ngram全文解析器。

ngram 是一种基于字符的分词方法，可以将文本按照连续的n个字符进行分割，然后构建倒排索引以支持全文搜索。 MYSQL 支持

适用于较小的数据集和简单的查询相对于其他全文搜索技术，如倒排索引，ngram在处理较小的数据集和简单的查询时具有较低的复杂性和资源消耗。

要创建一个FULLTEXT索引，使用NGRAM全文解析器，你加WITH PARSER ngram的CREATE TABLE，ALTER TABLE或CREATE INDEX声明。例如，以下语句创建新posts表，并将title和body列添加到FULLTEXT使用ngram全文解析器的索引。

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT ( title , body ) WITH PARSER NGRAM
)  ENGINE=INNODB CHARACTER SET UTF8MB4;

要查看ngram如何标记文本，请使用以下语句

SET GLOBAL innodb_ft_aux_table="mysqldemo/posts"; 
SELECT 
    *
FROM
    information_schema.innodb_ft_index_cache
ORDER BY doc_id , position;

ngram通配符搜索

SELECT 
    id, title, body
FROM
    posts
WHERE
    MATCH (title , body) AGAINST ('my*' );

设置ngram令牌大小 ngram中的令牌大小（n）默认为2.要更改令牌大小，请使用 ngram_token_size 配置选项，其值介于1和10之间。

在配置文件中

[mysqld]
ngram_token_size=1

MySQL ngram全文解析器简介

SQL Server

用户管理

1. 创建登录

使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 命令来创建登录。
SSMS在“Security”节点下右键单击“Logins”，选择“New Login”。
T-SQL使用 CREATE LOGIN 命令。

示例（T-SQL）

CREATE LOGIN sny_dev
WITH PASSWORD = 'dev@sny.com';

2. 创建用户

创建登录后，您可以创建一个用户来与数据库关联。
使用 CREATE USER 命令。

示例（T-SQL）

USE [db_suyuan_auth];
CREATE USER sny_dev FOR LOGIN sny_dev;

3. 分配权限

使用角色或直接分配权限给用户。
使用 GRANT 命令来授予权限。

示例（T-SQL）

GRANT SELECT, INSERT, UPDATE ON YourTable TO sny_dev;
-- 全部
GRANT CONTROL ON DATABASEdb_suyuan TO sny_dev;

分配架构

ALTER USER sny_dev WITH DEFAULT_SCHEMA = dbo;

4. 创建角色

使用 CREATE ROLE 命令来创建角色，然后将用户添加到角色中。

示例（T-SQL）

CREATE ROLE YourRoleName;
EXEC sp_addrolemember 'YourRoleName', 'YourUserName';

5. 修改用户属性

使用 ALTER USER 命令来修改用户属性，如更改密码或默认模式。

示例（T-SQL）

ALTER USER YourUserName WITH DEFAULT_SCHEMA = dbo;

6. 删除用户

使用 DROP USER 命令来删除用户。示例（T-SQL）

DROP USER YourUserName;

7. 删除登录

使用 DROP LOGIN 命令来删除登录。

示例（T-SQL）

DROP LOGIN YourLoginName;

孤立用户

数据库还原, 造成用户不能登陆的问题孤立用户

查询孤立用户

USE data_view 
GO 
sp_change_users_login @Action='Report' 
GO
 
USE data_view_source 
GO 
sp_change_users_login @Action='Report' 
GO

解决孤立用户问题

USE data_view 
GO 
sp_change_users_login @Action='update_one', 
@UserNamePattern='user_dev', 
@LoginName='user_dev' 
GO
 
USE data_view_source 
GO 
sp_change_users_login @Action='update_one', 
@UserNamePattern='user_dev', 
@LoginName='user_dev' 
GO

查询未提交的事务

USE master 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT
	es.session_id,
	es.login_name,
	es.host_name,
	est.text	,
	cn.last_read,
	cn.last_write,
	es.program_name 
FROM
	sys.dm_exec_sessions es
	INNER JOIN sys.dm_tran_session_transactions st --系统里还存在的事务
	ON es.session_id = st.session_id
	INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id CROSS APPLY sys.dm_exec_sql_text ( cn.most_recent_sql_handle ) est
	LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id 
AND er.session_id IS NULL

解决备用/只读

USE master RESTORE DATABASE his123 WITH RECOVERY;

 
--表结构 (desc)
sp_help  table_ 
 
select * from seed_instd_std_genrate_item
--获取ID
replace(NEWID(),'-','')
 
--菜单表元数据
 select * from bsmd_table order by id
 
insert into bsmd_table (id,name,code,bsmd_model_id,level_,kind,sta)values
('seed_std','常用国家种子标准库','seed_std','402880e43e69cbc7013e6b14f97c0018','模块','表','启用');
--insert into bsmd_table (id,name,code,bsmd_model_id,level_,kind,sta)values
--('test_template','报告模板管理','test_template','402880e43e69cbc7013e6b14f97c0018','模块','表','启用');

ALTER TABLE

---改变字段
ALTER TABLE seed_std ALTER COLUMN id text; 
ALTER TABLE seed_std ALTER COLUMN id varchar(32) not NULL; 
ALTER TABLE seed_std ALTER COLUMN id varchar(32) NULL; 
 
--改列名
exec sp_rename 'dv_dataset_join.parent_id','parent_join_id','column';
 
--加字段
ALTER TABLE Rstd_type add creater_ varchar(50)
--删列
ALTER TABLE table_NAME DROP COLUMN column_NAME 
 
---加约束
ALTER TABLE seed_inspect_standard_type ADD CONSTRAINT unique_com_ UNIQUE(type_) 
--外键
ALTER TABLE Students ADD CONSTRAINT FK_DeptId FOREIGN KEY(DeptId) REFERENCES Department1(DeptId)
--默认
ALTER TABLE user  add constraint CON_NAME default '18' for age
 
--删除主键/约束
alter table 表名 drop constraint 主键名
 
--添加主键
alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
-- --自增注解
alter table tablename alter 主键 int Identity(1,1) not null
 
--自增主键
create table student1
(
	sid int primary key identity,
	sname nchar(8) not null,
	ssex nchar(1)
)

-- 多表更新更新
update rstd_std_item set rstd_std_item.rstd_std = rstd_std.id from rstd_std
	where rstd_std_item.rstd_type = rstd_std.rstd_type and rstd_std_item.info_ = rstd_std.name_ 
 
--查询插入
insert into jklims_jiangmen.dbo.resbs_area (id,name_,zipcode_,temp1,temp2) 
   (select replace(NEWID(),'-',''),lbCmpAreaName,lbCmpAreaCode,lbCmpAreaNo,lbCmpAreaNo2 from lbcmparea)
 
--转换函数
CONVERT(varchar(10),id)

SQL Server 拼接多行

// 下拼接所有表表名

select top 1 names=stuff((select ',' + name  from data_view_source.dbo.sysobjects where xtype = 'U' for xml path('')),1,1,'')
from data_view_source.dbo.sysobjects where xtype = 'U'

stuff(select ',' + fieldname from tablename for xml path('')),1,1,'') 这句话的作用便是将多行fieldname字段的内容拼成字符串, 并用逗号隔开; for xml path是SQL Server 2005以后版本支持的将查询内容拼成XML的方法; stuff函数的作用是去掉字符串最前面的一个逗号;

开窗返回行号 over()

开窗, 主要作用是为每条数据提供分组的聚合函数结果, in sort 为明细数据, 提供分组结果

 
--例如 求出列col_2 每行的数据, 占总值的 百分比
select col_2, (col_2/sum(col_2)over() *100) 
	from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4 
---
 
 
select col_2, (CAST(col_2 as decimal(18,2))/sum(col_2)over() *100) 
	from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4 
 
-- 开窗排序 over(partition by col_1 order by col_1 )
select  col_1, ROW_NUMBER() over(partition by col_1 order by col_1  ) as idex 
    from data_view_source.dbo.ex_76662ecc8eee49eab5fbc5e9d9d3b86a
---意思是 分析col_1列的数据, 相同的数据加1,  order by 指定排序列
over(partition by col_1 order by col_1 ) as idex
--该例 会出现类似 如下数据
col_1	idex
27.00	1 --计1
28.00	1 --非相同,重新累加 
28.00	2 --加1+1=2
28.00	3 --加2+1=3
--↓排序组↓(这里 排序列也是col_1, 看不出效果)
31.00	1 --计1
31.00	2 --...
31.00	3
31.00	4
31.00	5
31.00	6
31.00	7
31.00	8
31.00	9
--↑排序组↑

SQL HAVING()

--用于比较以某个聚合后的结果
 
--例如 求出 col_2值 小于(<) col_2分组总值(sum) 的结果
-- 该例子或者说只要列col_2 分组的 count(*)值 >=2 就会列出来
select col_2, sum(col_2)
	from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4 
	group by col_2 	HAVING col_2 < sum(col_2)

SQL Server 行号

--sql server 返回行号, 必须不重复 的某列 开窗( 参考,上开窗over)
select *, ROW_NUMBER() OVER (ORDER BY id) AS 行号 from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4
 
 
 
--求中位数
--需要分组的话(可以改为join)在 两张row_t,count_t子表中 group by ; 注意条件where要加上 共同值
select avg(row_t.col_2) as mid  from 
		(select col_2, ROW_NUMBER() OVER (ORDER BY col_2) AS rowNum 
			from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4 
            --有顺序, 行号的子表
		) row_t,
		(SELECT  COUNT(1) AS ofVal 
			from data_view_source.dbo.ex_3c94516838fb43cca17cd62297288ba4
            --计出总行数
		)count_t 
        --偶数和奇数的处理
	where count_t.ofVal %2 = 0 and row_t.rowNum in(count_t.ofVal/2, count_t.ofVal/2+1)
	or count_t.ofVal/2 = row_t.rowNum

SQL Server 日期分组

 
-- DATEPART() 函数 根据参数返回对应的 int类型
select DATEPART(yy,'2018-08-05') as 年 , DATEPART(mm,'2018-08-05') as 月 ,  DATEPART(dd,'2018-08-05') as 日
 
--日期分组
select '2018年的每月的最高气温总和'=SUM( CONVERT(numeric(9, 2), col_1) ),
        '月份'=DATEPART(mm,col_3) 
	from data_view_source.dbo.ex_76662ecc8eee49eab5fbc5e9d9d3b86a
    where  DATEPART(yy,col_3) = 2018 group by DATEPART(mm,col_3)--条件2018年, 以月分组
--获得月份-1的时间
dateadd(mm,-1,getdate())
--获得月
select month(dateadd(mm,-1,getdate()))
--获得年
select year(dateadd(mm,-1,getdate()))
 
--当前系统日期, 时间
select getdate()

SQL Server 分组求中位数

只能单分组, 该需求只能自己写自定义聚合函数[见下 SQL Server自定义聚合函数]

--测试数据
CREATE TABLE test_median (Name  varchar(10),val iNT);
INSERT INTO test_median
  SELECT 'A',  1000  UNION ALL
  SELECT 'A',  2000  UNION ALL
  SELECT 'A',  3000  UNION ALL
  SELECT 'A',  4000  UNION ALL
  SELECT 'A',  5000  UNION ALL
  SELECT 'B',  100   UNION ALL
  SELECT 'B',  200   UNION ALL
  SELECT 'B',  300   UNION ALL
  SELECT 'B',  400   UNION ALL
  SELECT 'B',  7000  UNION ALL
  SELECT 'B',  10000
GO
 
--语句
SELECT
  data_with_rownumber.Name,
  AVG(data_with_rownumber.val) AS median
FROM
  (SELECT  ROW_NUMBER() OVER(PARTITION BY Name ORDER BY val) AS seq, Name,val 
    FROM test_median
  ) data_with_rownumber JOIN--找出所有带行号的数据
  (SELECT Name, COUNT(1) AS NumOfVal
    FROM test_median GROUP BY Name
  ) data_count--分组 和 小组总数
  ON (--join
      data_count.Name = data_with_rownumber.Name
 AND (--奇偶条件
  (data_count.NumOfVal % 2 = 0 AND data_with_rownumber.seq IN (data_count.NumOfVal / 2, (data_count.NumOfVal / 2) + 1))
  OR
  (data_count.NumOfVal % 2 = 1 AND data_with_rownumber.seq = 1 + data_count.NumOfVal / 2)
  )
)
GROUP BY
  data_with_rownumber.Name

SQL Server 转换函数

　CAST, CONVERT都可以执行数据类型转换; 在大部分情况下, 两者执行同样的功能, 不同的是CONVERT还提供一些特别的日期格式转换, 而CAST没有这个功能;

语法　　CAST (expression AS data_type [ (length ) ]) 　　CONVERT (data_type [ ( length ) ] , expression [ , style ])

SQL Server 系统视图

sysobjects

在数据库内创建的每个对象（约束, 默认值, 日志, 规则, 存储过程等）在表中占一行;

列名数据类型描述 name sysname 对象名,常用列 id int 对象标识号 xtype char(2) 对象类型; 常用列; xtype可以是下列对象类型中的一种 C = CHECK 约束　　D = 默认值或 DEFAULT 约束　　F = FOREIGN KEY 约束　　L = 日志　　FN = 标量函数 IF = 内嵌表函数　　P = 存储过程　　PK = PRIMARY KEY 约束（类型是 K）　　RF = 复制筛选存储过程 S = 系统表　　TF = 表函数　　TR = 触发器　　U = 用户表　　UQ = UNIQUE 约束（类型是 K） V = 视图　　X = 扩展存储过程 uid smallint 所有者用户对象编号**(注意这个是 sysusers 表的用户id, guest如果有表的话也会查出来, 结果不一致)** info smallint 保留; 仅限内部使用 status int 保留; 仅限内部使用 base_schema_ ver int 保留; 仅限内部使用 replinfo int 保留; 供复制使用 parent_obj int 父对象的对象标识号（例如, 对于触发器或约束, 该标识号为表 ID）; crdate datetime 对象的创建日期; ftcatid smallint 为全文索引注册的所有用户表的全文目录标识符, 对于没有注册的所有用户表则为 0 schema_ver int 版本号, 该版本号在每次表的架构更改时都增加; stats_schema_ ver int 保留; 仅限内部使用; type char(2) 对象类型; 可以是下列值之一 C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束 L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程 userstat smallint 保留; sysstat smallint 内部状态信息 indexdel smallint 保留 refdate datetime 留用 version int 保留 deltrig int 保留 instrig int 保留 updtrig int 保留 seltrig int 保留 category int 用于发布, 约束和标识 cache smallint 保留

sysforeignkeys

每个外键约束占一行

fkeyid 关联 syscolumns/sysforeignkeys 的 id ; fkey 关联 syscolumns的 colid

syscolumns

每个表和视图中的每列(字段) 在表中占一行

id 关联 sysobjects id xtype 关联 sys.types user_type_id!

sys.columns

每个表(字段) 在表中占一行,拥有精度信息, 没有类型

其 object_id关联 sysobjects id
其 system_type_id 关联 sys.types id

sysindexes

数据库中的每个索引和表在表中各占一行;

id 关联 sysobjects id

sys.types, systypes

每个系统字段类型在表中占一行

sysusers

系统表sysusers出现在master数据库和每个用户自定义的数据库中, 它对整个数据库中的每个Windows NT用户, Windows NT用户组, SQL Server 用户或者SQL Server 角色含有一行记录

sys.extended_properties

针对当前数据库中的每个扩展属性返回一行;

class 标识其上存在属性的项类; 可以是下列值之一 0 = 数据库 1 = 对象或列 2 = 参数 3 = 架构 4 = 数据库主体 5 = 程序集 6 = 类型 7 = 索引 10 = XML 架构集合 15 = 消息类型 16 = 服务约定 17 = 服务 18 = 远程服务绑定 19 = 路由 20 = 数据空间（文件组或分区方案） 21 = 分区函数 22 = 数据库文件 27 = 计划指南

minor_id 其上存在扩展属性的项的辅助 ID, 根据项类进行解释; 对于大多数项, ID 为 0; 否则, ID 为下列值之一如果 class = 1, 则 minor_id 在项为列的情况下等于 column_id, 在项为对象的情况下等于 0; 如果 class = 2, 则 minor_id 为 parameter_id; 如果 class = 7, 则 minor_id 为 index_id;

例如字段注释, 表注释.

如果是用户表 major_id 是 sysobjects id 如果是字段major_id 是 sysobjects id , minor_id 是 syscolumns 的 colid

一些元数据查询

-- 字段
select 
允许空 =case when c.isnullable=1 then'Y'else'N'end,
默认值 = isnull(e.text,''),
类型 = ct.name,
列名 = c.name,
表名 = o.name,
长度 = COLUMNPROPERTY(o.id,c.name,'PRECISION')
from  syscolumns c 
inner join sysobjects o  on c.id = o.id 
left join systypes ct on ct.xtype=c.xtype 
left join  syscomments e 
on c.cdefault=e.id
where o.xtype='U' and o.name='ttttc' 
 
--字段 包括精度
select
	'table_name' = o.name, 
	'type' = st.name,
	'name' = c.name,
	'length' = COLUMNPROPERTY(o.id,c.name,'PRECISION'),
	'precision' = case is_ansi_padded  when 0 then c.precision else c.precision end,
	'scale' = c.scale,
	'num'=ROW_NUMBER()  over(partition by st.name order by st.name  )
	from  sys.columns c 
		inner join sysobjects o  on c.object_id = o.id 
		inner join sys.types st on c.system_type_id = st.system_type_id
	where o.xtype='U'
--字段查询 主键, 外键
select 
	c.colid,
	列名 = c.name,
	允许空 = c.isnullable,
	类型 = ct.name,
	字段注释 = cmt.value,
	是否外键 = fkt.rkeyid ,
	主键colid = pkk.colid ,
	键 = case 
            when c.colid = pkk.colid then 'PRI' 
            when ISNULL(fkt.rkeyid,'') = '' then '' else 'MUL' end,
	extra = null
from  syscolumns c 
	inner join sysobjects t  on c.id = t.id 
	left join systypes ct on ct.xtype=c.xtype 
	left join sys.extended_properties cmt  on  c.colid  = cmt.minor_id and cmt.major_id  = c.id 
	left join sysforeignkeys fkt  on c.colid  = fkt.fkey and fkt.fkeyid = c.id
	
	left join sysobjects pko  on c.id = pko.parent_obj and pko.xtype = 'PK'
	left join sysindexes pki  on c.id = pki.id and pko.name = pki.name
	left join sysindexkeys pkk on c.id = pkk.id and pkk.indid = pki.indid
where t.xtype='U' and t.name='test_book' 
 
 
 
 
 
drop table testee
 
--表
select * from sysobjects o where o.xtype='U' and name = 'ttttc'
select t.name as tableName, c.value as tableComment, t.crdate as createTime 
	from sysobjects t left join sys.extended_properties c  on c.major_id = t.id where t.xtype='U' --表,字段 注释表 sys.extended_properties
 
 
-- 唯一索引
SELECT
  idx.name
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol  ON (idx.object_id = idxCol.object_id  AND idx.index_id = idxCol.index_id AND idx.is_unique_constraint = 1)
    JOIN sys.tables tab ON (idx.object_id = tab.object_id)
    JOIN sys.columns col ON (idx.object_id = col.object_id AND idxCol.column_id = col.column_id)
WHERE
  tab.name = 'bs_user_1' and col.name = 'name'
 
---------------------------- 约束查询
SELECT 
    约束ID =b.constid
		,主表名=object_name(b.fkeyid)  
    ,主表列ID=b.fkey 
    ,主表列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)   
    ,外键表ID=b.rkeyid   
    ,外键表名称=object_name(b.rkeyid)   
    ,外键列ID=b.rkey   
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)   
    ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')   
    ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')   
FROM sysobjects a   
    join sysforeignkeys b on a.id=b.constid   
    join sysobjects c on a.parent_obj=c.id   
where a.xtype='f' AND c.xtype='U'    
         and object_name(b.rkeyid)='ttc'  
    
---------------------------- 表字段查询
 
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
sys.extended_properties   g
on
    a.id=G.major_id and a.colid=g.minor_id 
left join
 
sys.extended_properties f
on
    d.id=f.major_id and f.minor_id=0
where
    d.name='test_book'

SQL Server 自定义聚合函数

一个求中位数的自定义聚合函数(环境SQL server 2008 r2)

多分组,求中位数, SQL Server语句无法实现

参考官方文档, 自定义聚合

https//docs.microsoft.com/zh-cn/sql/relational-databases/user-defined-functions/create-clr-functions?view=sql-server-2017

接口参阅

创建 Visual C 类库

vs2010 创建 Visual C 类库

完整C#代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
 
[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,/** Sql server 查询器优化 参数, 注意设置错误将导致不对的结果*/
    IsInvariantToNulls = false,
    IsInvariantToDuplicates = true,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)
]
/**
 * 
 *@descipion 
 *SQL Server 2008 R2 自定义 求中位数 的聚合函数 CLR 接口
 *
 *参阅 https//docs.microsoft.com/zh-cn/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregates?view=sql-server-2017
 *@date 2018-10-14
 */
public class C_MID IBinarySerialize
{
    /// <summary>  
    /// The variable that holds the intermediate result of the concatenation  
    /// </summary>  
    private List<decimal> list;
 
    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()
    {
        this.list = new List<decimal>();
    }
 
    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="value"></param>  
    public void Accumulate(SqlDecimal value)
    {
        this.list.Add(value.Value);
    }
 
    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate.  
    /// </summary>  
    /// <param name="other"></param>  
    public void Merge(C_MID other)
    {
        this.list.AddRange(other.list);
    }
 
    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation.  
    /// </summary>  
    /// <returns></returns>  
    public SqlDecimal Terminate()
    {
        SqlDecimal ret = new SqlDecimal();
        if (this.list.Count == 0)
        {
            return ret;
        }
        else
        {
            this.list.Sort();
        }
        if (this.list.Count % 2 == 0)//偶数
        {
            int index = (int)(this.list.Count / 2);
            decimal avg = (this.list[index-1] + this.list[index]) / 2;
            ret = new SqlDecimal(avg);
 
        }else
        {//奇数
            int index = this.list.Count / 2;
            ret = new SqlDecimal(this.list[index]);
        }
        return ret;
    }
 
    public void Read(BinaryReader r)
    {
        list = new List<decimal>();
        //第一个 int 是总计
        int count = r.ReadInt32();
        for (int i = 0; i < count; i++)
        {
            decimal d = r.ReadDecimal();
            list.Add(d);
        }
    }
 
    public void Write(BinaryWriter w)
    {
        w.Write(this.list.Count);
        for (int i = 0; i < this.list.Count; i++)
        {
            w.Write(this.list[i]);
        }
    }
}

生成DLL

dll添加到sql server(创建程序集)

 
CREATE ASSEMBLY [C_MID] AUTHORIZATION [dbo]
FROM 'F\Work\project vs\c_mid\c_mid\bin\Release\c_mid.dll'
WITH PERMISSION_SET = SAFE;

创建聚合函数

CREATE AGGREGATE [dbo].[C_MID] (@FieldValue [decimal](18,2))
RETURNS [decimal](18,2)
EXTERNAL NAME [C_MID].[C_MID];

sql server 开启 CLR支持

EXEC sp_configure ‘clr enabled’, 1 RECONFIGURE WITH OVERRIDE GO

测试使用

select dbo.c_mid(col_1) from wwt

踩坑

一定要实现序列化接口函数!!!
查询优化参数, 必须设置正确
注意参数类型映射
CREATE AGGREGATE 语句与C#类名相关的

vs 2010 项目文件(c_mid.rar)

Oracle

参考 Z_Oracle_复习笔记.md Z_Oracle_复习笔记

系统视图

表和列

DBA_TABLES, ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息; DBA_TAB_COLUMNS, ALL_TAB_COLUMNS和USER_TAB_COLUMNS显示了每个数据库表的列的信息;

user_tab_comments 表注释 user_col_comments 列注释

注意 DBA_OBJECTS, ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息, 包括表;

-- 表
select table_name from user_tables; //当前用户拥有的表      
select table_name from all_tables; //所有用户的表 
select table_name from dba_tables; //包括系统表
 
-- 字段
select * from user_tab_columns where Table_Name='';
select * from all_tab_columns where Table_Name='';
select * from dba_tab_columns where Table_Name='';
 
 
 
--注释
select * from user_tab_comments;//表注释
select * from user_col_comments;// 列注释

约束信息 /主键/外键/

DBA_CONSTRAINTS ALL_CONSTRAINTS 和 USER_CONSTRAINST 显示有关约束的一般信息;

DBA_CONS_COLUMNS ALL_CONS_COLUMNS 和 USER_CONS_COLUMNS 显示有关列的相关约束的一般信息;

-- 约束

-- queryDBFieldStruct
select f.TABLE_NAME AS "tableName",  tc.COMMENTS as "tableComment",
	f.COLUMN_NAME AS "name",f.DATA_TYPE AS "type", f.DATA_LENGTH AS "length",
	f.DATA_PRECISION AS "precision", f.DATA_SCALE AS "DATA_SCALE", f.DATA_DEFAULT AS "defauleValue",
	fc.COMMENTS AS "comment",
	 (CASE WHEN f.NULLABLE = 'Y' THEN 1 ELSE 0 END ) AS "notNull",
	 (CASE WHEN uc.CONSTRAINT_TYPE = 'P' THEN 1 ELSE 0 END ) AS "isPk", 
	 (CASE WHEN uc.CONSTRAINT_TYPE = 'R' THEN 1 ELSE 0 END ) AS "isFk"
from user_tab_columns  f 
	left join user_tab_comments tc on f.table_name = tc.table_name 
	left join user_col_comments fc on f.table_name = fc.table_name AND f.COLUMN_NAME = fc.COLUMN_NAME 
	left join user_cons_columns ucc on f.table_name = ucc.table_name AND f.COLUMN_NAME =  ucc.COLUMN_NAME AND ucc.POSITION IS NOT NULL 
	left join user_constraints uc on  f.table_name = uc.table_name AND f.COLUMN_NAME =  ucc.COLUMN_NAME AND uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME
 
 
 
-- queryDBReferenceStruct
SELECT   A.OWNER 外键拥有者,
　　　　A.TABLE_NAME 外键表,
　　　　C.COLUMN_NAME 外键列,
　　　　B.OWNER 主键拥有者,
　　　　B.TABLE_NAME 主键表,
　　　　D.COLUMN_NAME 主键列,
　　　　C.CONSTRAINT_NAME 外键名,
　　　　D.CONSTRAINT_NAME 主键名
FROM USER_CONSTRAINTS A,
　　　USER_CONSTRAINTS B,
　　   USER_CONS_COLUMNS C, --外键表
　　   USER_CONS_COLUMNS D --主键表　　
WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME 　　
　　AND A.CONSTRAINT_TYPE = 'R' 　　
　　AND B.CONSTRAINT_TYPE = 'P' 　　
　　AND A.R_OWNER = B.OWNER 　　
　　AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 　　
　　AND B.CONSTRAINT_NAME = D.CONSTRAINT_NAME 　　
　　AND A.OWNER = C.OWNER 　　
　　AND A.TABLE_NAME = C.TABLE_NAME 　　
　　AND B.OWNER = D.OWNER 　　
　　AND B.TABLE_NAME = D.TABLE_NAME

Quartz 4

Explorer

N_SQL

SQL

DDL, DML

DDL

DML

查询插入

MYSQL

INSERT INTO SELECT

ORACLE

查询更新

MySQL

ORACEL

MYSQL

查询删除

ORACLE

UNION

附 维恩图

MySQL

MYSQL 事务脚本

MYSQL 变量语句

临时变量

实例 获取行号

全局变量

ONLY_FULL_GROUP_BY

实例 获取行号

MySQL 存储过程

开窗 over

like 难走索引的问题

SQL Server

用户管理

1. 创建登录

2. 创建用户

3. 分配权限

4. 创建角色

5. 修改用户属性

6. 删除用户

7. 删除登录

孤立用户

查询孤立用户

解决孤立用户问题

查询未提交的事务

解决备用/只读

ALTER TABLE

SQL Server 拼接多行

开窗返回行号 over()

SQL HAVING()

SQL Server 行号

SQL Server 日期分组

SQL Server 分组求中位数

SQL Server 转换函数

SQL Server 系统视图

sysobjects

sysforeignkeys

syscolumns

sys.columns

sysindexes

sys.types, systypes

sysusers

sys.extended_properties

一些元数据查询

SQL Server 自定义聚合函数

创建 Visual C 类库

dll添加到sql server(创建程序集)

创建聚合函数

sql server 开启 CLR支持

测试使用

踩坑

Oracle

系统视图

表和列

约束信息 /主键/外键/

Graph View

Table of Contents

附维恩图

实例获取行号

实例获取行号