SqlServer学习

用户、数据库名、Schema关系

一个用户对应多个数据库

一个数据库对应多个schema

数据库名不是schema

用户是用户，与数据库名和schema没有关系

SqlServer不区分大小写

单引号和双引号没什么区别

schema与table格式：[schema].[table]

SqlServer不支持正则

分页操作

SELECT
	RESULT_CODE result_code,
	TASK_CODE task_code,
	VER_CODE ver_code,
	DATA data 
FROM
	[BXM].[BXM_QUALITY_ERROR_DATA]
ORDER BY RESULT_CODE
OFFSET 0 ROWS
FETCH next 100 rows only

在2012之前的分页基本上两种方法

2000时代 top n where id not in (top n )

2005时代 row_number()方式

前者，如果分页过多话，查询后面的页，性能会快速下降，并且是2次扫描

后者，一次扫描，前几页的性能略逊于前者

一、row_number()进行分页操作

语法:ROW_NUMBER()OVER(ORDER BY COLUMN)

含义:从1开始，对每一条数据排序后返回一个数字;根据这个列进行分页，这样就可以有效防止id列有中断值的情况

下面使用ROW_NUMBER()查询31到40条数据:

二、用Offset-Fetch进行分页操作(SQLserver 2012以上版本)

Offset子句:用于指定跳过(Skip）的数据行;

Fetch子句:该子句在Offset子句之后执行，表示在跳过(Sikp）指定数量的数据行之后，返回一定数据量的数据行;

执行顺序:Offset子句必须在Order By子句之后执行，Fetch子句必须在Offset子句之后执行;

分页实现的思路：

1.在分页实现中，使用Order By子句，按照指定的columns对结果集进行排序

2.使用Offset子句跳过前N页:Offset (@Pagelndex-1)*@pageSize rows;

3.使用Fetch子句呈现当前Page: Fetch next @pageSize rows only;

使用Offset-Fetch子句跳过指定数目的数据行之后，返回指定数目的数据行

select * from table order by id ase offset 10 rows Fetch next 10 rows only

修改成分页的通用格式

空字符串与NULL的区别

总结：NULL和‘’并不相等

常见的SQL语句总结

创建schema：BXM

create schema BXM;

判断schema：BXM是否存在

利用SCHEMA_ID内置函数，传递schema值作为参数，如果存在返回该schema对应的id，否则返回null

select count(SCHEMA_ID('BXM')) as result from sys.schemas

创建BXM_QUALITY_ERROR_DATA数据表并添加注释

CREATE TABLE [bxm].[BXM_QUALITY_ERROR_DATA] (
	[RESULT_CODE] VARCHAR ( 200 ) NOT NULL,
	[TASK_CODE] VARCHAR ( 200 ) NOT NULL,
	[VER_CODE] VARCHAR ( 200 ) NOT NULL,
	[DATA] text NOT NULL 
);
EXEC sp_addextendedproperty 'MS_Description',
N'异常数据表',
'SCHEMA',
N'bxm',
'TABLE',
N'BXM_QUALITY_ERROR_DATA'

查询schema：BXM下所有的表

select * from information_schema.tables where table_schema='BXM'

判断schema：BXM下的BXM_QUALITY_ERROR_DATA是否存在

select * from information_schema.tables where table_schema='BXM' AND table_name='BXM_QUALITY_ERROR_DATA';

判断BXM数据库是否存在

select count(1) as result from master.dbo.sysdatabases where name='BXM';

查看SqlServer版本

select @@version

函数

concat

在sqlserver中concat中连接NULL与连接空字符串没有区别
当把字段转为字符串类型时–concat(字段,’’);

ISNULL

ISNULL(check_expression,replacement_value)

1、check_expression与replacement_value的数据类型必须一致

2、如果check_expression为NULL,则返回replacement_value

3、如果check_expression为NULL,则返回check_expression

CAST

CAST(x AS type)

SELECT count( distinct CAST([DATA] as VARCHAR) ) from BXM.BXM_QUALITY_ERROR_DATA bqed

异常总结

org.jkiss.dbeaver.model.exec.DBCException: SQL 错误 [245] [S0001]: Conversion failed when converting the varchar value ‘b’ to data type int.

select id from czy_table1 ct  where id in ('a','b')

原因：如上，表结构如图所示，当运行sql时，报错；原因是因为：因为id是int类型，而where后面的in里面的是字符串，sql执行过程中，会将a和b当做数字处理，而数字中没有a、b这个数字，导致sql报错

解决
（1）第一种方式是修改sql，in里面放数字

select id from czy_table1 ct  where id in (1,2)

（3）第二种方式是，将id类型转换，转换为字符串类型

select id from czy_table1 ct  where CAST(id AS VARCHAR) in ('a','b')

总结：在SqlServer中注意使用in条件时，列的类型后面in的内容的类型一致