July 28 2014

sqlserver基础理论和基本操作(3)

T-SQL中的运算符

运算符 含义
= 等于
> 大于
< 小于
>= 大于或等于
<= 小于或等于
<> 不等于
!
通配符 解释 示例
‘_’ 一个字符 A Like ‘C_’
% 任意长度的字符串 B Like ‘CO_%’
[ ] 括号中所指定范围内的一个字符 C Like ‘9W0[1-2]’
[^] 不在括号中所指定范围内的一个字符 D Like ‘%[A-D][^1-2]’
逻辑表达式 说明 示例
AND 逻辑与 1 AND 1 =1 ; 1 AND 0 = 0; 0 AND 0 = 0;
OR 逻辑或 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;
NOT 逻辑非 NOT 1 = 0; NOT 0 = 1;

 

 

use UcJmh
go–在一行都会报错的
–不能显示的给标识列插入值
–标识列的值不能进行更新
–标识列的值只能为整数类型
—-使用DELETE删除数据时,不能删除被外键值所引用的数据行

–查询
/*SELECT <目标列表达式> [别名] [<目标列表达式> [别名]]…
INTO <新表名>
FROM <数据表名或视图名> [别名][,<数据表名或视图名> [别名]]…
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]]; */
SELECT完整的语法结构中包含
基本查询:条件查询、排序查询、分组查询、筛选查询等
高级查询:连接查询、子查询、联合查询等

TOP n查询 :返回满足查询条件的前n条记录

TOP n PERSENT:按百分比返回记录数
使用AS来命名列
使用=来命名列
select name ‘名字’ from student

/*函数:
SQL Server中的函数
字符串函数
日期函数
数学函数
系统函数*/

 

–字符串的处理
charindex()
–CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
–Is a character expression that contains the sequence to be found. expressionToFind is limited to 8000 characters.
–Returning the starting position of an expression
DECLARE @document varchar(64);
SELECT @document = ‘Reflectors are vital safety’ +
‘ components of your bicycle.’;
SELECT CHARINDEX(‘bicycle’, @document);
GO
–Searching from a specific position
DECLARE @document varchar(64);
SELECT @document = ‘Reflectors are vital safety’ +
‘ components of your bicycle.’;
SELECT CHARINDEX(‘are’, @document, 5);
GO

–Searching for a nonexistent expression

DECLARE @document varchar(64);
SELECT @document = ‘Reflectors are vital safety’ +
‘ components of your bicycle.’;
SELECT CHARINDEX(‘bike’, @document);
GO

–Performing a case-sensitive search
SELECT CHARINDEX ( ‘TEST’,
‘This is a Test’
COLLATE Latin1_General_CS_AS);

SELECT CHARINDEX ( ‘Test’,
‘This is a Test’
COLLATE Latin1_General_CS_AS);
–Performing a case-insensitive search
SELECT CHARINDEX ( ‘TEST’,
‘This is a Test’
COLLATE Latin1_General_CI_AS);

–len函数
–LEN ( string_expression )
–Returns the number of characters of the specified string expression, excluding trailing blanks.
select  Len(‘Hello World   ‘)–10
–这样的写法类似于oracle中的from dual 因为 oracle的一行查询如果没有from关键字是不可以执行的
–ORA-00923: 未找到要求的 FROM 关键字
–To return the number of bytes used to represent an expression, use the DATALENGTH function.
–当你想要不略去末尾空格的时候可以使用datalength函数
select  DATALENGTH (‘Hello World   ‘)–14
–当然还有一处区别是:
select len(‘中国我爱你’) as ‘长度’ –5
select datalength(‘中国我爱你’) as ‘所占的字节数’  –10
–在oracle中是length与lengthb  本来想写的是纯sqlserver的学习,但是我是oracle出身的,对把多种数据库进行对比 避免以后混淆
–upper和lower
–Returns a character expression with lowercase character data converted to uppercase
–UPPER ( character_expression )
–character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
–需要注意的是 这个参数必须可以隐式的转换成varchar 如果不可以 可以用cast或者convert这两个系统函数进行显示的转换
select UPPER(‘ucjmh’)–UCJMH

–Returns a character expression after converting uppercase character data to lowercase
–返回将大写字符转换为小写字符后得到的字符表达式。
–LOWER(character_expression)
–注意点同上
select LOWER(‘UcJmh’) –ucjmh
–ltrim和rtrim和trim
–Returns a character expression after it removes leading blanks.
–LTRIM ( character_expression )
select LTRIM(‘    Hello’)
–rtrim则是删除了右边空格的
select RTRIM(‘a ‘)
–trim 是删除两侧空格的吗?  错了  在SQL SERVER中,没有trim这个函数,只有ltrim和rtrim,分别用于取掉左边的空格和右边的空格
select TRIM(‘   New York   ‘)–消息 195,级别 15,状态 10,第 1 行 ‘TRIM’ 不是可以识别的 内置函数名称。
–but in oracle
–SELECT TRIM(‘ a ‘) FROM dual;
–SELECT LTRIM(‘ a’) FROM dual;
–SELECT RTRIM(‘a ‘) FROM dual;
–以上三行sql都可以成功执行The above three lines of SQL can be executed successfully
–left和right
–返回的字符串的左部指定数量的字符Returns the left part of a character string with the specified number of characters.
–LEFT ( character_expression , integer_expression )
SELECT LEFT(‘abcdefg’,2);
GO
–right是相反的
SELECT Right(‘abcdefg’,2);
GO
–oracle中没有left 和 right的说法  在 Oracle 里可以用 SUBSTR(string,pos,len)
–oracle的substr也是很复杂的 有substr和substrb 其中substr是按照字来算的,而substrb()是按照字节来算的

 

–replace
–Replaces all occurrences of a specified string value with another string value.
–REPLACE ( string_expression , string_pattern , string_replacement )
–需要注意的是 参数不可以为null 不然结果肯定是null
SELECT REPLACE(‘abcdefghicde’,’cde’,’xxx’);
GO

–stuff
–Deletes a specified length of characters and inserts another set of characters at a specified starting point.
–删除一个指定长度的字符串 然后放一个新的字符串
–STUFF ( character_expression , start , length ,character_expression )
SELECT STUFF(‘abcdef’, 2, 3, ‘ijklmn’)
GO–aijklmnef

–日期函数
–getdate()
–Returns the current database system timestamp as a datetime value without the database time zone offset.
–This value is derived from the operating system of the computer on which the instance of SQL Server is running.
–上面两行废话就是返回系统当前时间

select GETDATE()–2014-07-28 11:11:48.403
–getting the current  system date and time
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
–only getting the current  system date
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, SYSDATETIMEOFFSET())
,CONVERT (date, SYSUTCDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE())
,CONVERT (date, GETUTCDATE());
–only getting the current system time
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, SYSDATETIMEOFFSET())
,CONVERT (time, SYSUTCDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE())
,CONVERT (time, GETUTCDATE());
–如何指定格式
declare @olddate varchar(20)
declare @newdate varchar(20)
set @olddate=’2007-04-25′
select @newdate=Rtrim(year(@olddate))+’年’+Rtrim(month(@olddate))+’月’+Rtrim(day(@olddate))+’日’
print @newdate

select Rtrim(year(GETDATE()))+’年’+Rtrim(month(GETDATE()))+’月’+Rtrim(day(GETDATE()))+’日’
–我TM崩溃了 没有能yyyy年MM月dd日的简单写法吗?!在我大oracle中SELECT to_char(SYSDATE,’YYYY”年”MM”月”DD”日”‘) FROM dual then ok
select CONVERT(VARCHAR(19),GETDATE(),126) –当然 使用convert可以转成你喜欢的 请参照 我的另一篇文章http://www.ucjmh.com/?p=274

–dateadd()
–Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
–DATEADD (datepart , number , date )
–关于 datepart的取值如下:

日期部分 缩写
year yy, yyyy
Quarter(季度) qq, q
month mm, m
Dayofyear(一年的第多少天) dy, y
day dd, d
Week(一周) wk, ww
Weekday(任一天) dw, w
Hour(小时) hh
Minute(分钟) mi, n
Second(秒) ss, s
Millisecond(毫秒) ms

 

 

 

–dayofyear, day, and weekday return the same value.

–If datepart is month and the date month has more days than the return month and the date day
–does not exist in the return month, the last day of the return month is returned.
–if the next month don’t has this day then retrun the month last day
–注意 第二个参数不可以超过Int的精度

SELECT DATEADD(month, 1, ‘2006-08-30’);
SELECT DATEADD(month, 1, ‘2006-08-31’);

DECLARE @datetime2 datetime2 = ‘2007-01-01 13:10:10.1111111’;
SELECT ‘1 millisecond’, DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT ‘2 microseconds’, DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT ’49 nanoseconds’, DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT ‘150 nanoseconds’, DATEADD(nanosecond,150,@datetime2);

DECLARE @datetime2 datetime2 = ‘2007-01-01 13:10:10.1111111’;
SELECT ‘year’, DATEADD(year,1,@datetime2)
UNION ALL
SELECT ‘quarter’,DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT ‘month’,DATEADD(month,1,@datetime2)
UNION ALL
SELECT ‘dayofyear’,DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT ‘day’,DATEADD(day,1,@datetime2)
UNION ALL
SELECT ‘week’,DATEADD(week,1,@datetime2)
UNION ALL
SELECT ‘weekday’,DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT ‘hour’,DATEADD(hour,1,@datetime2)
UNION ALL
SELECT ‘minute’,DATEADD(minute,1,@datetime2)
UNION ALL
SELECT ‘second’,DATEADD(second,1,@datetime2)
UNION ALL
SELECT ‘millisecond’,DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT ‘microsecond’,DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT ‘nanosecond’,DATEADD(nanosecond,1,@datetime2);
DECLARE @datetime2 datetime2;
SET @datetime2 = ‘2007-01-01 01:01:01.1111111’;
–Statement                                 Result
——————————————————————-
SELECT DATEADD(quarter,4,@datetime2);     –2008-01-01 01:01:01.110
SELECT DATEADD(month,13,@datetime2);      –2008-02-01 01:01:01.110
SELECT DATEADD(dayofyear,365,@datetime2); –2008-01-01 01:01:01.110
SELECT DATEADD(day,365,@datetime2);       –2008-01-01 01:01:01.110
SELECT DATEADD(week,5,@datetime2);        –2007-02-05 01:01:01.110
SELECT DATEADD(weekday,31,@datetime2);    –2007-02-01 01:01:01.110
SELECT DATEADD(hour,23,@datetime2);       –2007-01-02 00:01:01.110
SELECT DATEADD(minute,59,@datetime2);     –2007-01-01 02:00:01.110
SELECT DATEADD(second,59,@datetime2);     –2007-01-01 01:02:00.110
SELECT DATEADD(millisecond,1,@datetime2); –2007-01-01 01:01:01.110

DECLARE @days int = 365,  –if this number is 366 then retuned 2001-01-01 01:01:01.110 becouse 2000 was a leap year
@datetime datetime = ‘2000-01-01 01:01:01.111’;
SELECT DATEADD(day, @days, @datetime);

–datediff()
–Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
–就是返回指定detepart的start date 和end date的差
–DATEDIFF ( datepart , startdate , enddate )
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
–datename()–返回一个字符串,它表示指定datepart指定的日期
–DATENAME ( datepart , date )
SELECT DATENAME(year, GETDATE())
,DATENAME(month, GETDATE())
,DATENAME(day, GETDATE())
,DATENAME(dayofyear, GETDATE())
,DATENAME(year, ”);–then the default date is 1900年01月01日

select YEAR(GETDATE())–这样也是可以的

–数学函数
–关于数学函数 我就不写了,我对不起我数学老师 请参照文档 http://msdn.microsoft.com/en-us/library/default.aspx
–abs()
–floor
–power
–round()
–sign()
–sqrt()

–系统函数
–CAST and CONVERT
–http://msdn.microsoft.com/en-us/library/ms187928.aspx
Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

DECLARE @myval decimal (5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5));
— Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval));
SELECT CAST(10.6496 AS int);

DECLARE @x NVARCHAR(10) = ‘ab’ + NCHAR(0x10000);
SELECT CAST (@x AS NVARCHAR(3));

–select convert(varchar(100),29.87)+’15’   –字符串的拼接
–select current_user       –dbo是个特权用户,在每个数据库中都存在此用户,此用户不能被删除
–select host_name() as ‘所登录的计算机名’ –UCJMH-PC
–select system_user as ‘当前所登录的用户的名称’    –sa

 

 



Copyright 2019. All rights reserved.

Posted 2014年7月28日 by ucjmh in category "sqlserver

Leave a Reply

Your email address will not be published. Required fields are marked *