数据库相关知识积累（sqlserver、oracle、mysql）
1. sqlserver ：断开所有连接： （还原数据库）
1.数据库 分离
2.
USE master
GO
ALTER DATABASE [GPOSDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--查看是否还有用户连接
SELECT * FROM sys.[sysprocesses] WHERE DB_NAME([dbid])='gposdb'
GO
ALTER DATABASE [GPOSDB] SET MULTI_USER
GO
2.SQL server：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。（SQLSERVER数据库）
原因：默认情况下，如果在子查询，函数，视图中尝试使用order by，就遇到这种错误，原因是针对一个表的select其实并不是返回一个表，而是一个游标（不知道Oracle数据库是不是相同情况）
3. sqlserver插入单引号：在sqlserver中用两个单引号就代表一个单引号
insert into tb(field) values('a''bc');
mysql中用\'
insert into tb(field) values('a\'bc');
4. sqlser替换某字段某字符串：
update sysmailtemplate set body = REPLACE(convert(varchar(8000),body),'http://www.xxx.com','javascript:void(window.open("http://www.xxx.com", "newwindow", "width=1050,height=650,scrollbars=yes"))')
5. oracle小数转字符串
1.保留小数点前后的0
to_char('120.50','fm99990.009')：120.500 保留三位小数，fm表示去掉因为9占位而多出来的空格
to_char('0.25','fm99990.09')：0.25 保留两位小数
2.如果是整数，上述方法会在后面多一个小数点
to_char('120','fm99990.09')：120.
用trim()去掉最后的小数点：trim('.' from to_char('120','fm99990.09'))
或者to_char('120','fm99990.00')
6. sqlserver数据库create表：select * into tablenew from tableold
与Oracle不同：create table tablenew as select * from tableold
7. mysql创建索引：
两种方式，一种alter table，一种create
1.alter table
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2.create --create 不能创建primary key 索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引：
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下：
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
8.sqlserver，row_number不排序方法：select a.*,row_number() over (order by (select 0)) as rn from SYSUSER a
9.SQL server创建索引：
create [unique] [clustered] [nonclustered] index index_name
on {tabel/view} (column[dese/asc][....n])
create unique clustered index index_name
on kshouse (Id)
10. sqlserver建表(主键)：
CREATE TABLE [dbo].[house](
[Id] [nvarchar](36) NOT NULL,
[CreateDate] [datetime] NULL,
[HouseCode] [nvarchar](50) NULL,
[ImgUri] [nvarchar](200) NULL,
[Display] [nvarchar](1000) NULL,
[Yezhushuo] [nvarchar](50) NULL,
[HouseName] [nvarchar](200) NULL,
[HouseDescribe] [nvarchar](200) NULL,
[Area] [nvarchar](200) NULL,
[HouseType] [nvarchar](100) NULL,
[PostMessage] [nvarchar](200) NULL,
[TotalPrice] [nvarchar](50) NULL,
[UnitPrice] [nvarchar](50) NULL,
CONSTRAINT [PK_house] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
11.sqlser连接远程数据库：
--连接远程数据库 1
exec sp_addlinkedserver 'zc','','SQLOLEDB','129.204.88.98'
exec sp_addlinkedsrvlogin 'zc','false',null,'sola','Z342516106c'
insert into zc.[Crawler].[dbo].[Orders](Customer,OrderDate) select Customer,OrderDate from Orders
--以后不再使用时删除链接服务器
exec sp_dropserver 'zc', 'droplogins'
12. sqlserver相关子查询Exists
相关子查询：子查询的查询条件依赖外层查询 ，带Exists的查询就是相关子查询。
Exists 子查询语句不关心查询结果具体是啥，只关心结果集是否为空，若结果集为空则返回false，若不为空则返回true（Not Exists正好相反）；
SELECT 姓名
FROM 学生表
WHERE EXISTS
(
SELECT *
FROM 选课表
WHERE 学生表.学号 = 选课表.学号 AND 课程号 = 'C1'
);
相关子查询执行过程：先在外层查询中取“学生表”的第一行记录，用该记录的相关的属性值（在内层WHERE子句中给定的）处理内层查询，若外层的WHERE子句返回“TRUE”值（内层where有数据），则这条记录放入结果表中。然后再取下一行记录；重复上述过程直到外层表的记录全部遍历一次为止。
13.sqlser：游标更新列
declare
@id as nvarchar(36),
@Year as nvarchar(10),
@Month as nvarchar(10),
@Day as nvarchar(10)
declare cursor_ymd cursor fast_forward for
select id,
datename(year,createdate) as Year,
convert(int,datename(month,createdate)) as Month,
datename(day,createdate) as Day
from house;
open cursor_ymd;
fetch next from cursor_ymd into @id,@Year,@Month,@Day while @@FETCH_STATUS = 0
begin
update house set Year = @Year,Month = @Month, Day = @Day where id = @id;
fetch next from cursor_ymd into @id,@Year,@Month,@Day;
end
close cursor_ymd;
deallocate cursor_ymd;
14. sqlserve返回两个日期之间的时间:DATEDIFF(datepart,startdate,enddate)：select datediff(day,getdate(),getdate() + 1) -- 1
datepart取值：day、month、year、week
15.pl/sql安装：
1.首先安装oracle客户端
2.打开plsql，第一次进先点cancel，然后tool->preference
3.重启，输入用户名密码，看到database为空不要着急，也不要配环境变量，直接写数据库地址、端口号、服务名即可：172.20.70.112:1521/feiliextsrv
16. sqlserver 数据库，区分大小写查询
select * from WorkflowInstance where xaml COLLATE Chinese_PRC_CS_AS like '%Car批准%'
17. oracle字符集，客户端与服务端字符集不一致，常常导致查询出错或中文乱码
select userenv('language') from dual;--客户端
select * from NLS_DATABASE_PARAMETERS;--服务端字符集
SELECT * FROM V$NLS_PARAMETERS;--客户端字符集
--修改客户端字符集要改环境变量，不知道改注册表改的是哪里的字符集，注册表与环境变量不一致，好像采用环境变量的配置
18.sqlplus使用：sqlplus projtest/projtest@127.0.0.1:1521/orcl
19.sqlserver类似宏定义的东西：
use Oncontrol_Test;--加上这个，说明查的是Oncontrol_Test里面的表（数据库名或用户名），这是一个更改数据库的操作，改完后，所有操作都执行在当前库中
select * from task;
20.sqlserver 查询表属性：
--1.
select
syscolumns.name as "列名",
case when syscolumns.isnullable = '0' then '否' else '是' end as "可否为空",
systypes.name as "数据类型",
syscolumns.[length] as "类型长度",
case when ISNULL(sys.identity_columns.is_identity,0) = '0' then '否' else '是' end as "是否主键",
ISNULL(sys.extended_properties.value,'') as "备注"
from sysobjects
join syscolumns on sysobjects.id = syscolumns.id
join systypes on syscolumns.xusertype = systypes.xusertype
left join sys.identity_columns
on sys.identity_columns.object_id = syscolumns.id and sys.identity_columns.column_id = syscolumns.colid
left join sys.extended_properties on sys.extended_properties.major_id = syscolumns.id
and sys.extended_properties.minor_id = syscolumns.colid
where sysobjects.name = 'PlanFlows'
--2.
SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '1'
ELSE ''
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN '1'
ELSE ''
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN '1'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = 'SYSGROUPPERMISSION'--表名
ORDER BY col.colorder ;
21.sqlserver触发器与Nhibernate冲突：
--创建触发器
--与NHibernate冲突，添加set nocount
create trigger tri_task on TASK
after update
as
declare @ID varchar(50)
if update(Status)
begin
SET NOCOUNT ON
select @ID = ID from inserted;
update DATASYNC set MODIFYTIME = GETDATE(),ISUPDATE = '1' where DATAID = @ID;
SET NOCOUNT OFF
End
22.获取字符串长度
sqlserver：len(colname)、datalength(colname)
oracle：length(colname)
23.ORACLE数据库创建用户：
-- 创建表空间
CREATE TABLESPACE CHAOSHIGW
LOGGING
DATAFILE 'G:\app\oradata\orcl\chaoshigw.DBF' -- ORACLE安装目录
SIZE 100M
AUTOEXTEND ON
NEXT 30M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE CHAOSHIGW_TEMP
TEMPFILE 'G:\app\oradata\orcl\chaoshigw_temp.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- 创建用户
CREATE USER chaoshigw IDENTIFIED BY chaoshigw123
ACCOUNT UNLOCK
DEFAULT TABLESPACE CHAOSHIGW
TEMPORARY TABLESPACE CHAOSHIGW_TEMP;
-- 授权
GRANT CONNECT,RESOURCE TO chaoshigw;
GRANT DBA TO chaoshigw;
24. oracle远程连接数据库，实现跨库查询（数据迁移），创建dblink
create database link database_link_test --数据库别名
connect to chaoshigw identified by "database!pass" -- 用户名/密码
using '127.0.0.1:1521/orcl';
--不含lob类型字段，可以直接查询
select * from goods@database_link_test
--含lob类型字段的表，查询报错ora-22992，采取临时表的方式
create table temptable as select * from webpart@database_link_test--建临时表
insert into webpart select * from temptable
--断开dblink
drop database link database_link_test