sql_server

• xml path

select (select top 10 concat(',',id) from userInfo for XML path('') ) userinfo;

• 随机取一条数据

  SELECT top 1 * FROM [Table name] where [Column name]=XX ORDER BY NEWID()

• 获取主键id 名

SELECT COLUMN_NAME colname FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = ? AND constraint_name = ( SELECT B.NAME FROM SYSOBJECTS A JOIN SYSOBJECTS B ON A.ID = B.PARENT_OBJ AND A.XTYPE = 'U' AND B.XTYPE = 'PK' WHERE A.NAME = ? )

-- 分页获取数据
SELECT
  *
FROM
  PAYORDER
ORDER BY ID DESC OFFSET [count]*([page]-1) ROW FETCH NEXT [count] ROWS ONLY;

• 分页_2（2005/2008 两个嵌套）

SELECT
  *
FROM
  (
    SELECT
      *, ROW_NUMBER () OVER (ORDER BY id DESC) row
      FROM
        PayOrder
  ) temp
WHERE
  row BETWEEN 1
AND 3;

• 插入时返回主键id @@IDENTITY 一般系统定义的全局变量都是以@@开头的

String sql = "insert into testUser (name,password) values (?,?);select @@IDENTITY";
String sql = "insert into testUser (name,password) values (?,?);";
PreparedStatement preparedStatement = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
PreparedStatement preparedStatement = conn.prepareStatement(sql);

• SQL server 保留两位小数

SqlServer保留几位小数的两种做法


数据库里的 float momey 类型,都会精确到多位小数.但有时候 我们不需要那么精确,例如，只精确到两位有效数字。

解决：

1. 使用 Round() 函数，如 Round(@num,2) 参数 2 表示 保留两位有效数字。

2. 更好的方法是使用 Convert(decimal(18,2),@num) 实现转换，decimal(18,2) 指定要保留的有效数字。

这两个方法有一点不同：使用 Round() 函数，如果 @num 是常数，如 Round(2.3344,2) 则 会在把有效数字后面的 变为0 ，成 2.3300。但 Convert() 函数就不会。

select  Convert(decimal(18,0),2.36985);  -> 2
select ROUND(2.3698595,0); -> 2.00000

• 日期 和 时间

获取当前时间

Select GETDATE() as '当前日期',
DateName(year,GetDate()) as '年',
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(dw,GetDate()) as '星期',
DateName(week,GetDate()) as '周数',
DateName(hour,GetDate()) as '时',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒'

获取对应格式的时间

SELECT CONVERT(varchar(100), GETDATE(), 0) 05  9 2011  9:12AM
SELECT CONVERT(varchar(100), GETDATE(), 1) 05/09/11
SELECT CONVERT(varchar(100), GETDATE(), 2) 11.05.09
SELECT CONVERT(varchar(100), GETDATE(), 3) 09/05/11
SELECT CONVERT(varchar(100), GETDATE(), 4) 09.05.11
SELECT CONVERT(varchar(100), GETDATE(), 5) 09-05-11
SELECT CONVERT(varchar(100), GETDATE(), 6) 09 05 11
SELECT CONVERT(varchar(100), GETDATE(), 7) 05 09, 11
SELECT CONVERT(varchar(100), GETDATE(), 8) 09:13:14
SELECT CONVERT(varchar(100), GETDATE(), 9) 05  9 2011  9:13:14:670AM
SELECT CONVERT(varchar(100), GETDATE(), 10) 05-09-11
SELECT CONVERT(varchar(100), GETDATE(), 11) 11/05/09
SELECT CONVERT(varchar(100), GETDATE(), 12) 110509
SELECT CONVERT(varchar(100), GETDATE(), 13) 09 05 2011 09:13:14:670
SELECT CONVERT(varchar(100), GETDATE(), 14) 09:13:14:670

SELECT CONVERT(varchar(100), GETDATE(), 20) 2011-05-09 09:13:14
SELECT CONVERT(varchar(100), GETDATE(), 21) 2011-05-09 09:13:14.670
SELECT CONVERT(varchar(100), GETDATE(), 22) 05/09/11  9:15:33 AM
SELECT CONVERT(varchar(100), GETDATE(), 23) 2011-05-09
SELECT CONVERT(varchar(100), GETDATE(), 24) 09:15:33
SELECT CONVERT(varchar(100), GETDATE(), 25) 2011-05-09 09:15:33.140

SELECT CONVERT(varchar(100), GETDATE(), 100) 05  9 2011  9:15AM
SELECT CONVERT(varchar(100), GETDATE(), 101) 05/09/2011
SELECT CONVERT(varchar(100), GETDATE(), 102) 2011.05.09
SELECT CONVERT(varchar(100), GETDATE(), 103) 09/05/2011
SELECT CONVERT(varchar(100), GETDATE(), 104) 09.05.2011
SELECT CONVERT(varchar(100), GETDATE(), 105) 09-05-2011
SELECT CONVERT(varchar(100), GETDATE(), 106) 09 05 2011
SELECT CONVERT(varchar(100), GETDATE(), 107) 05 09, 2011
SELECT CONVERT(varchar(100), GETDATE(), 108) 09:16:38
SELECT CONVERT(varchar(100), GETDATE(), 109) 05  9 2011  9:16:38:543AM
SELECT CONVERT(varchar(100), GETDATE(), 110) 05-09-2011
SELECT CONVERT(varchar(100), GETDATE(), 111) 2011/05/09
SELECT CONVERT(varchar(100), GETDATE(), 112) 20110509
SELECT CONVERT(varchar(100), GETDATE(), 113) 09 05 2011 09:17:19:857
SELECT CONVERT(varchar(100), GETDATE(), 114) 09:17:19:857
SELECT CONVERT(varchar(100), GETDATE(), 120) 2011-05-09 09:17:19
SELECT CONVERT(varchar(100), GETDATE(), 121) 2011-05-09 09:17:19.857
SELECT CONVERT(varchar(100), GETDATE(), 126) 2011-05-09T09:17:19.857
SELECT CONVERT(varchar(100), GETDATE(), 130)  6 ????? ??????? 1432  9:17:19:857AM
SELECT CONVERT(varchar(100), GETDATE(), 131)  6/06/1432  9:17:19:857AM

SQL server获取昨天的时间

-- 获取昨天的当前时间
    select dateadd(day, -1, getdate());
-- 获取当前月
    select DATENAME(MM,GETDATE()) --05
    select DATENAME(MONTH,GETDATE()) --05
    select DATEPART(MM,GETDATE()) --5
    select DATEPART(MONTH,GETDATE()) --5
    select MONTH(GETDATE()) --5
-- YEAR 年；YYYY 年；YY 年；
-- QUARTER 季度；QQ 季度 ；Q 季度；
-- MONTH 月（05）；MM 月（05）；M 月（05）；
-- WEEKDAY 星期几；DW 星期几；W 星期几；
-- WEEK 一年的第几周；WK 一年的第几周；WW 一年的第几周；ISOWK 一年的第几周；ISOWW 一年的第几周；ISO_WEEK 一年的第几周；
-- DAY 天；DD 天；D 天；
-- DAYOFYEAR 一年的第几天；DY 一年的第几天；Y 一年的第几天；
-- HOUR 小时；HH 小时；
-- MINUTE 分钟；MI 分钟；N 分钟；
-- SECOND 秒；SS 秒；S 秒；
-- MCS 微秒（略）；
-- MICROSECOND 微秒（略）；
-- MILLISECOND 毫秒（略）；
-- MS 毫秒（略） ;
-- NANOSECOND 十亿分之一秒(略)；
-- NS 十亿分之一秒(略);

操作日期型数据和日期函数

year(date)                      返回年份
month(date)                     返回月份
day（date）                     返回日数
getdate()                        以sql规定的标准内部格式返回系统当前的日期和时间
dateadd(datepart,number,date)   返回datetime或smalldatetime类型的数据，其值为date值加上datepart和number参数指定的时间间隔
datediff（datepart,date1,date2)  返回date1和date2间的时间间隔，其单位由datepart参数决定
datename（detepart，date）       返回日期中指定部分的字符串
datepart（datepart，date）       返回日期中指定对应的整数值

日期元素对应取值范围

元素名称 缩写  取值范围
year          yy      1753-9999
quarter       qq       1-4
month         mm       1-12
day of year   dy      1-366
day           dd       1-31
week          wk       0-51
hour          hh       0-23
minute        mi       1-59
second        ss       1-59
millsecond      ms      0-999

• 获取表的字段值

-- 查看表结构
SELECT syscolumns.name, systypes.name AS type, syscolumns.isnullable, syscolumns.length, syscomments. TEXT defaultvalue, CONVERT ( nvarchar (50), ISNULL( extended_properties. VALUE, '' )) express FROM syscolumns LEFT JOIN syscomments ON syscomments.id = syscolumns.cdefault LEFT JOIN sys.extended_properties extended_properties ON extended_properties.major_id = syscolumns.id AND syscolumns.colid = extended_properties.minor_id JOIN systypes ON syscolumns.xusertype = systypes.xusertype WHERE syscolumns.id =
object_id('LessonUserRegister')

• 替换语法：

REPLACE ( ''string_replace1'' , ''string_replace2'' , ''string_replace3'' )

• sql 思路

SELECT
    auw.Id id,
    MAX (auw.wxname) name
    ,COUNT (*) COUNT
FROM
    UserTryCourseGroupUserR utcgur
JOIN UserTryCourseMessRecord utcmr ON utcgur.MessRecordId = utcmr.Id
right JOIN AdminUserWx auw ON utcmr.AdminWxId = auw.Id
WHERE
    auw.AdminId = 817
AND utcgur.GroupId IS NULL
GROUP BY
    auw.Id

data:
146 鸭题库龙小昕  4
341 鸭题库莫素素老师    1

不能包含统计数量为0的，即左右关联也不能解决

通过其他方式解决

SELECT
    auw1.id,
    auw1.wxname name,
    (
        SELECT
            COUNT (*) num
        FROM
            UserTryCourseGroupUserR utcgur
        LEFT JOIN UserTryCourseMessRecord utcmr ON utcgur.MessRecordId = utcmr.Id
        LEFT JOIN AdminUserWx auw ON utcmr.AdminWxId = auw.Id
        WHERE
            auw.id = auw1.Id
        AND utcgur.GroupId IS NULL
    ) COUNT
FROM
    AdminUserWx auw1
WHERE
    auw1.AdminId = 817

data:
146 鸭题库龙小昕  4
341 鸭题库莫素素老师    1
342 鸭题库燕玲老师 0

通过更换主表，其他数据通过子查询可以实现

• 清空表数据

-- 清空tableName表
TRUNCATE  TABLE  [tableName]

SQL2008 SQL Server身份认证方式登录失败（错误18456）笔记

• Sql Server REPLACE函数的使用
• SqlServer索引的原理与应用
• sql server 报错
• 索引
• 索引
• SQL server时间函数
• sql server 视图
• SQL教程
• SQL查询语句练习题27道
• SQL数据库经典练习题
• 如何在SQLServer中处理每天四亿三千万记录的（数据库大数据处理）
• 使用 SqlBulkCopy大批量插入数据到数据库
• SQL Server 教程
• SQL Server2012 安装方法详解
• SQLSERVER 怎样去除重复记录
• sql server中case when的用法
• 数据库类型，varchar和nvarchar的区别
• sql server：删除表数据，标识列Id从1开始
• sql语句中----删除表数据drop、truncate和delete的用法
• sql server 报错
• sql server中截取字符串的常用函数