而_则代表一个字符。例如"ab_"代表以ab 开头且长度为3的所有字符串
[]用于表示0至9之间的字符。例如"ab[0-5]"用户表示一个以ab开头，后跟一个0至5之间数字的字符串。
[^6-9]表示不在某一范围内的字符..
select * from Customers where CustomerID like 'a[NR]%'
Union 相当于集合运算中的并操作运算(AUB)组合再次查询的结果，并除去两个查询结果中的重复记录。
except 相当于集合运算中的A-AnB从 操作数左边的查询中返回右边的查询未返回的所有非重复值
intersect相当于集合运算中的交集运算(AnB),返回即存在于查询结果1,又存在于查询结果2中的所有记录
select companyname,contactname from Suppliers where country = 'USA'
Union
select CompanyName,'没有联系人' from shippers
------case .... when ----------------------------------------------------..........
select ProductName,'产品类别'=case CategoryID
when '1' then 'Beverages' when '2' then 'Condiments' when '3' then 'Confections'
end from Products order by categoryID
SELECT ID,(CASE WHEN DATEPART(dw,GETDATE())=1 OR DATEPART(dw,GETDATE())=7 THEN APrice ELSE BPrice END) AS Price
From Hotel
CASE WHEN (SELECT DATEPART(weekday,GETDATE())) in (1,7) THEN HRSP_WeekenPrice ELSE HRSP_Price END AS Price
-------------------------------------------------------------------------
Year 缩写 Yy, Quarter(季度) Qq ,Month Mm, Day of Year(一年中的第几天) Dy ,Day(一个月的第几号) Dd
Week wk, weekday Dw, Hour Hh, Minute mi,second ss
print '今天的日期是 '+ Convert(varchar(12),getdate(),101)
print '今年是 ' + Convert(varchar(12),Year(getdate()))
print '本月是 ' + Convert(varchar(12),Month(getdate()))+ '月'
print '今天是 ' + Convert(varchar(12),day(getdate()))+ '号'
print '后天是 ' + Convert(varchar(12),dateadd(Dy,2,getdate()),101)+ '号'
print '与7月20号还差 ' + Convert(varchar(12),datediff(Dy,getdate(),'07/20/2009'))+ '天'
print '现在是星期 ' + Convert(varchar(12),datepart(Dw,getdate()))
----------游标----------------------------------------------------------------
Create Procedure AccessEmployees
(
@pCourtesy varchar(25),
@pCursor cursor varying output--需要将创建游标返回给调用本存储过程的主过程，使用关键字varying output
)
as
begin
set NoCount on
set @pCursor = cursor static for
select employeeID,firstName from employees where titleofCourtesy = @pCourtesy order by employeeID
open @pCursor
return 0
end
declare @gEmploy cursor
declare @lEmployeeID as int
declare @lfirstname as varchar(15)
declare @RecCount as int
declare @gCourtesy as varchar(25)
set @gCourtesy = 'Mr.' --'ms.'
Exec AccessEmployees @gCourtesy,@gEmploy output
if cursor_status('variable','@gEmploy') = 0
begin
print 'no records found'
return
end
else
begin
select @RecCount = @@Cursor_Rows
while @RecCount > 0
begin
fetch from @gEmploy
into @lEmployeeID,@lfirstname
set @RecCount = @RecCount -1
print '雇员号:'+convert(varchar(3),@lEmployeeID)+'雇员姓名:'+@lfirstname
end
close @gEmploy
deallocate @gEmploy
end
--上例没有在AccessEmployees 存储过程中释放游标变量@pCursor，因为在离开存储过程体时，已经将创建的游标传递给了@gEmploy,由于在存储过程
--AccessEmployees 之外，@pCursor 已失去其作用域，所以该变量占有的资源被自动是释放。无需再显式地释放它。
---------any()-----------------------------------------------------
select companyName,city from customers
where city = any
(select city from suppliers where customers.city = Suppliers.city)
order by city asc
---------exists()-----------------------------------------------------
select companyName,city from customers
where exists
(select * from Suppliers where customers.city = Suppliers.city)
order by city asc
-----子查询------------------
select ProductName,Productid,
(select count(productid) from [Order Details] od
where od.productID = ProductID and Products.Productid = od.productid)
as 订购次数 from products
-----Having子查询------------------.
select ProductID ,avg(unitprice),sum(quantity),avg(discount) from [Order Details]
group by productID having sum(quantity) >1000 order by productid
-----avg(distinct,*)------------------
select ProductID ,avg(distinct,unitprice) as 平均单位值 from [Order Details]
group by productID
order by productID
汇总功能
Transact-SQL 提供的分类汇总功能有三个,分别是CUBE ( ),ROLLUP ( ),COMPUTE
------------------------------------------------------------
sp_helpdb --数据库名称，大小，所有者等
sp_helpfile --当前数据库物理文件所在的位置
use Master
exec sp_helpfile
sp_helpserver --返回当前数据库服务器及相关信息
sp_helpuser --返回当前数据库服务器上的所有用户
sp_helptext sp_helpdb --显示sp_helpdb 代码
sp_helptext sp_helptext
exec sp_stored_procedures -- 查看指定数据库中所含的存储过程
sp_who---列举当前正在访问数据库的各个进程的的状态信息
sp_helptrigger @tabname--查看触发器的相关信息
sp_changedbowner -- 更改当前数据库的拥有者
==========================sql server系统存储过程===================
–1.给表中字段添加描述信息
Create table T2 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'MS_Description', 'Employee ID', 'user', dbo, 'table', T2, 'column', id
EXEC sp_updateextendedproperty 'MS_Description', 'this is a test', 'user', dbo, 'table', T2, 'column', id
–2.修改数据库名称
EXEC sp_renamedb 'old_db_name', 'new_db_name'
–3.修改数据表名称和字段名称
EXEC sp_rename 'old_table_name', 'new_table_name'–修改数据表名称
EXEC sp_rename 'table_name.[old_column_name]', 'new_column_name', 'COLUMN'–修改字段名称
–4.给定存储过程名，获取存储过程内容
exec sp_helptext sp_name
/*以下是有关安全控制的系统存储过程或 SQL 语句，详细语法查阅《联机丛书》相关内容*/
–创建新的 SQL Server 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server。
EXEC sp_addlogin @loginame = '', @passwd = '', @defdb = '', @deflanguage = NULL, @sid = NULL, @encryptopt = NULL
–使 Windows NT 用户或组帐户得以使用 Windows 身份验证连接到 SQL Server。
EXEC sp_grantlogin @loginame = ''
–删除 SQL Server 登录，以阻止使用该登录名访问 SQL Server。
EXEC sp_droplogin @loginame = ''
–阻止 Windows NT 用户或组连接到 SQL Server。
EXEC sp_denylogin @loginame = ''
–从 SQL Server 中删除用 sp_grantlogin 或 sp_denylogin 创建的 Windows NT 用户或组的登录项。
EXEC sp_revokelogin @loginame = ''
–更改登录的默认数据库。
EXEC sp_defaultdb @loginame = '', @defdb = ''
–更改登录的默认语言。
EXEC sp_defaultlanguage @loginame = '', @language = ''
–添加或更改 SQL Server 登录密码。
EXEC sp_password @old = '', @new = '', @loginame = ''
–添加服务器角色新成员。
EXEC sp_addsrvrolemember @loginame = '', @rolename = ''
–添加服务器角色某成员。
EXEC sp_dropsrvrolemember @loginame = '' , @rolename = ''
–为 SQL Server 登录或 Windows NT 用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限（授予默认的“public”数据库角色）。
EXEC sp_grantdbaccess @loginame = '', @name_in_db = NULL
–或
EXEC sp_adduser @loginame = '', @name_in_db = NULL, @grpname = ''
–从当前数据库中删除安全帐户。
EXEC sp_revokedbaccess @name_in_db = ''
–或
EXEC sp_dropuser @name_in_db = ''
–在当前数据库创建新数据库角色。
EXEC sp_addrole @rolename = '', @ownername = ''
–在当前数据库删除某数据库角色。
EXEC sp_droprole @rolename = ''
–在当前数据库中添加数据库角色新成员。
EXEC sp_addrolemember @rolename = '', @membername = ''
–在当前数据库中删除数据库角色某成员。
EXEC sp_droprolemember @rolename = '', @membername = ''
–权限分配给数据库角色、表、存储过程等对象
–1、授权访问
GRANT
–2、拒绝访问
DENY
–3、取消授权或拒绝
REVOKE
–4、Sample(pubs):
GRANT SELECT ON authors TO Limperator
DENY SELECT ON authors TO Limperator
REVOKE SELECT ON authors TO Limperator
以下来源：http://blog.csdn.net/t6786780/archive/2009/09/06/4525652.aspx
使用SQL Server 数据库基础
2. 数据库的管理
l 通配符
|
通配符 |
解释 |
实例 |
|
‘_’ |
一个字符 |
A LIKE ‘C_’ |
|
% |
任意长度的字符串 |
B LIKE ‘CO_%’ |
|
[] |
括号内制定范围内的一个字符 |
C LIKE ‘9W0[1-2]’ |
|
[^] |
不再括号中所制定范围内的一个字符 |
D LIKE ‘9W0[^1-2]’ |
l 使用T-SQL语句对数据库管理
|
类型 |
语法 |
|
新建库 |
Use master(注: 此功能用来控制进入master数据库) Go(注: 分割语句) create database 表名 on ( name = '数据库名',(数据库名称) filename = 'D:/表名.mdf',(数据库路径) size = 10mb,(数据库初始化大小) maxsize = 100mb,(数据库最大大小) filegrowth = 10%(数据库增长速度) ) |
|
新建表 |
create table UserInfo ( Id int identity(1,1) not null, (标识种子为1,增长一次+1) NickName char(10) not null, ) |
|
添加约束 |
约束包含主键约束, 外键约束, 默认约束, 检查约束, 唯一约束 添加约束以: (alter table 表名 add constraint 约束名) 开头, 后面追加 主键约束: primary key (id); 外键约束: foreign key(从表列) references bool(主表列); 默认约束: unique (列名); 检查约束: check(列名 like ‘1_’);结合通配符使用 唯一约束: unique(列名);
注意: 约束名一定要符合规范 例如添加默认约束: UK_约束名. 以此类推 |
|
事务 |
begin tran declare @errorSum int set @errorSum=0 set @errorSum=@errorSum+@@error if (@errorSum > 0) rollback tran else commit tran |
l 使用T-SQL语句对数据库数据管理
|
类型 |
语法 |
说明 |
||
|
插入数据 |
INSERT [INTO] 表名(列名) VALUES (列名) |
插入单行数据 |
||
|
INSERT 表名(列名) SELECT 列值 UNION SELECT 列值 |
插入多行数据 |
|||
|
修改数据 |
UPDATE 表名 SET 列名 = 更新值 WHERE 更新条件 |
一般情况下必须写更新条件 |
||
|
删除数据 |
DELETE FROM 表名 WHERE 删除条件 |
删除单行数据 |
||
|
TRUNCATE TABLE 表名 |
删除整个表中的数据 |
|||
|
查询数据 |
SELECT 列名 FROM 表名 WHERE 查询条件表达式 ORDER BY 排序的列名 [注: ASC或DESC(降序)] |
排序查询 |
||
|
SELECT 列名 AS 命名 FROM 表名 |
在查询中使用列名 |
|||
|
SELECT 列名 FROM 表名 WHERE 查询条件表达式 |
查询空行 |
|||
|
SELECT 新命名 = 列名, ‘值命名’ AS 新列命名 FROM 表名 |
在查询中使用常量列 |
|||
|
SELECT TOP 限制的行数(填写数字) 列名 FROM 表名 WHERE查询条件表达式 |
返回限制的行数 |
|||
|
SELECT * FROM 表名 WHERE查询条件表达式 LIKE 'ACCP%' |
使用LIKE模糊查询 |
|||
|
SELECT * FROM 表名 WHERE 表名 BETWEEN 条件一 AND 条件二(注: NOT) |
在某个范围内进行查询 |
|||
|
SELECT * FROM 表名 WHERE 列名 IN (列值1, 列值2)(注:Not) |
使用IN在列举值内进行查询 |
|||
|
SQL SERVER中使用的聚合函数 |
||||
|
SUM – 总和 Avg – 平均值 MAS(最大值) 和 Min(最小值) COUNT(非空值的计数) |
WHERE * GROUP BY * HAVING (使用WHERE,GROUP BY,HAVING的优先级) |
在查询中使用的聚合函数 |
||
|
SELECT COUNT(列名),列名 FROM 表名 GROUP BY 列名 |
分组查询(在分组查询的同时必须使用聚合函数) |
|||
|
HAVING 查询条件表达式 (举例: COUNT(Uclass) > 1) |
使用Having子句进行分组筛选 |
|||
|
内连接查询 |
||||
|
SELECT 表名1.列名,表名2.列名 FROM 表名1 INNER JOIN 表名2 ON (条件表达式) |
注: INNER JOIN *** ON 是内连接查询用的关键字,其中表名可以简化,但是前提在 FROM 中必须指示简化的表名是来源于哪个表 (举例:S.列名 FROM 表名 AS 列名或 INNER JOIN 表名) |
|||
|
外连接查询 |
||||
|
SELECT 表名1.列名,表名2.列名 FROM 表名1 LEFT OUTER JOIN 表名2 ON 条件表达式 |
左外连接, 与内连接不同的是,如果附表中数据不能满足主表则以NULL填充…… |
|||
|
SELECT 表名1.列名,表名2.列名 FROM 表名1 RIGHT OUTER JOIN 表名2 ON 条件表达式 |
左外连接(同上) |
|||
l 在查询中使用的函数
|
函数类型 |
函数名 |
描述 |
举例 |
|
字 符 串 函 数 |
CHARINDEX |
用来寻找一个指定的字符串在另一个字符串中的起始位置 |
SELECT CHARINDEX ('ACCP','MYACCPCOURSE',1) 返回: 3 |
|
LEN |
返回传递给他的字符串长度 |
SELECT LEN ('MYACCPCOURSE') 返回:12 |
|
|
UPPER |
把传递给他的字符串转换为大写 |
SELECT UPPER ('system') 返回: SYSTEM |
|
|
LTRIM |
清除字符左边的空格 |
|
|
|
RTRIM |
清除字符右边的空格 |
|
|
|
RIGHT |
从字符串右边返回指定数目的字符串 |
SELECT RIGHT ('北大青鸟',2) 返回: 青鸟 |
|
|
|
替换一个字符串中的字符 |
|
|
|
STUFF |
在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串 |
SELECT STUFF ('谢霆锋的老婆是谁',8,8,'张柏芝' ) 返回: 谢霆锋的老婆是张柏芝 |
|
|
日 期 函 数 |
GETDATE |
取得当前的系统日期 |
SELECT GETDATE() 返回: 今天的日期 |
|
DATEADD |
将指定的数值添加到指定的日期部分后的日期 |
SELECT DATEADD(dd,4,'01/01/2007') 返回: 2007-01-05 00:00:00.000 |
|
|
DATEDIFF |
两个日期之间的指定日期部分的区别 |
SELECT DATEDIFF(dd,'01/01/2007', '01/04/2007') 返回: 3 |
|
|
DATENAME |
日期中指定日期部分的字符串形式(dd,mm,dw,yy) |
SELECT DATENAME(dw,'01/05/2009') 返回: 星期一 |
|
|
DATEPART |
日期中指定日期部分的整数形式 |
SELECT DATEPART(YY,'01/05/2009') 返回: 2009 |