MSSQL英儿版基础整理二

sql分享 

--修改“ Department ”名为“理科”，"Number=8"
update W set Number=8 where Department='理科'
--默认值。修改表改该列增加默认值
alter table W add constraint df_check_year default('1990-01-21') for [Year]
--查询“ [Year] ” 列的值为“ 1990-06-12 ”
SELECT [Year] FROM W WHERE [Year] = '1990-06-12'
--查询“ 孙开心 ”和“ 理科 ” 这条数据（AND运算符）
SELECT * FROM W WHERE [Name]='孙开心' AND Department='理科'
--查询“ 孙开心 ”和“ 理科 ” 的数据（OR运算符）
SELECT * FROM W WHERE [Name]='孙开心' OR Department='理科'
-- 将AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）如下:
SELECT * FROM W WHERE (Department='文科' OR Department='理科') AND [Name]='孙开心'
SELECT * FROM W 
--以文字顺序显示人名([Name]):
SELECT [Name], Department FROM W ORDER BY [Name]
--以文字顺序显示人名([Name])，并以数字顺序显示顺序号（Number）：
SELECT [Name], Number FROM W ORDER BY [Name], Number
--以逆人名顺序显示人名
SELECT [Name], Number FROM W ORDER BY [Name] DESC
--以逆人名顺序显示人名，并以数字顺序显示顺序号：
SELECT [Name], Number FROM W ORDER BY [Name] DESC, Number ASC
--将‘NAME’列值去重复
SELECT DISTINCT [Name] FROM W
--查询“ [Name],Department ”数据
SELECT [Name],Department FROM W
SELECT TOP number|percent column_name(s) FROM W
--从表中选取人名为 孙开心 和 于莎莎 的人：
SELECT * FROM W WHERE Name IN ('孙开心','于莎莎')

SELECT * FROM W

--查出BETWEEN '1900-01-01' 到 '1989-01-01'之间的日期
SELECT [Year] FROM W WHERE [Year] BETWEEN '1900-01-01' AND '1989-01-01'
--查出Department 理科和文科
SELECT * FROM W WHERE Department BETWEEN '理科' AND '文科'
--查出不包含理科和文科的数据
SELECT * FROM W WHERE Department NOT BETWEEN '理科' AND '文科'
--给[Name]取个别名“ 姓名 ”
SELECT [Name] AS 姓名 FROM W
--使用别名进行2表查询
SELECT j.[Name], w.[Name],w.Department
FROM W AS w, J AS j
WHERE w.[Name]='王俊英' AND j.[Name]='孙开心'
--给" [Name] "和" Department "取个别名
SELECT [Name] AS 姓名, Department AS 科系 FROM W
--2表联查
SELECT W.[Name], W.Department, J.[Name] FROM W, J WHERE W.[Name] = J.[Name]
--左链接
select w.[Name],j.[Name] from W left join J where [Name] W.id=J.id
--右链接
SELECT W.[Name], W.Department, J.[Name] FROM W RIGHT JOIN J ON W.id=J.id ORDER BY W.[Name]
--内链接
SELECT W.[Name], W.Department, J.[Name] FROM W INNER JOIN J ON W.id = J.id ORDER BY W.[Name]
--全连接 (全连接和内链接是一样的功能)
SELECT W.[Name], W.Department, J.[Name] FROM W FULL JOIN J ON W.id=J.id ORDER BY W.[Name]
--列出所有的姓和不同的公司 www.52mvc.com
SELECT [name] FROM W UNION SELECT [name] FROM J
--将W的数据复制到#W临时表中
SELECT * INTO #W FROM W
--将W表的" [Name] "和" Department " 2列复制到#C临时表中
SELECT [Name], Department INTO #C FROM W
--将W表属于“ 理科部 ”的数据复制到#C临时表中
SELECT [Name], Department INTO #C FROM W WHERE Department='理科部'
--创建一个名为 "#C" 的临时表，其中包含了从 W 和 J 两个表中取得的信息
SELECT W.Department, J.[Name] INTO #C FROM W INNER JOIN J ON W.Id=J.Id
--给“ W ” 表中字段 [Name] 创建索引
CREATE INDEX PersonIndex ON W ([Name]) 
--以降序索引某个列中的值
CREATE INDEX PersonIndex ON W ([Name] DESC) 
CREATE INDEX PersonIndex ON W ([Name], Department)
--删除“ W ” 表中字段 [Name]的索引(ACCESS数据库)
DROP INDEX PersonIndex ON W
--删除“ W ” 表中字段 [Name]的索引(SQL_SERVER数据库)
DROP INDEX W.PersonIndex
--清除表里的数据
TRUNCATE TABLE W
--删除表
DROP TABLE W
--删除数据库
DROP DATABASE WJY
--添加列为 “ Birthday ” 数据类型为 时间类型
ALTER TABLE J ADD Birthday datetime

更多http://www.52mvc.com/showtopic-1618.aspx