数据库相关知识积累(sqlserver、oracle、mysql)

数据库相关知识积累(sqlserveroraclemysql

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

上一篇:剑指 Offer 33. 二叉搜索树的后序遍历序列 + 根据二叉树的后序遍历序列判断对应的二叉树是否存在


下一篇:SQL语句-批量插入表(表数据插表)