SQL 进阶学习之一

2007/05/27 之学习SQL的新的练习和体会

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name
  FROM sys.databases
  WHERE name = N'Test'
)
DROP DATABASE Test
GO

CREATE DATABASE Test
GO

--
-- =========================================
-- Create table template
-- =========================================
USE Test
GO

IF OBJECT_ID('dbo.AC', 'U') IS NOT NULL
  DROP TABLE dbo.AC
GO

CREATE TABLE dbo.AC
(
 num int NOT NULL primary key,
 [name] nvarchar(30) NULL,
 sex nvarchar(10) null,
 [time] datetime default getdate(),
)
GO
--
select * from AC

--
insert AC
(num,[name],sex)
values (22,'kaka','M')
--
insert into AC
(num,[name],sex)
values (2,'inzaghi','M')
insert into AC
(num,[name],sex)
insert into AC
values (7,'dida','M','2005-09-9 11:00:00')
values (6,'oddo','M')
values (5,'nest','M')

values (7,'dida','M',2005-09-9 11:00:00)

--
CREATE TABLE dbo.AC2
(
 num int NOT NULL primary key,
 [name] nvarchar(30) NULL,
 sex nvarchar(10) null,
 [time] datetime default getdate(),
 banben smallint null
)
GO

--
insert into AC2
(num,[name],sex)
select num,[name],sex
 from AC
go

--
select * from AC2
--
delete from AC2

delete AC2
--from为可选关键字
--
truncate table AC2
--
insert top (2) AC2 -- top 为 SQL Server 2005 新增的
(num,[name],sex)
select num,[name],sex
 from AC
go

--插入固定内容
insert top (2) AC2
(num,[name],sex,[time],banben)
select num,[name],sex,[time],2007
 from AC
go

--
CREATE TABLE dbo.AC3
(
 num int primary key identity, --  标识列
 [name] nvarchar(30) NULL,
 sex nvarchar(10) null,
 [time] datetime default getdate(),
 banben smallint null
)
GO
--在数据中只插入默认值
insert into AC3
default values
go
--
select * from AC3

--**********************
--
--更新语句操作练习
--
update AC3
set [name]='me'
where (num=1)
go
--
update AC3
set sex='m'
where num=1
go
--引用其它表里的字段值来更新记录
update AC2
set [time] = AC3.[time],banben= AC3.banben
from AC3
where (AC2.num = AC3.num and AC2.banben=2007)
go
--使用top子句更新记录
update top(3) AC3
set [time]=AC.[time]
from AC
where (AC3.num = AC.num )
go
--按百分比更新
update top(40) percent AC3
set [name]=AC.[name]
from AC
where (AC3.num = AC.num )
go

--*****************************************
--delete语句的用法练习
--简单的删除语句
delete AC3
where
num = 8
--删除多条记录
delete AC3
where [time] > '2007-05-27 14:22:37'
go
--引用其它表里的字段值来删除记录
delete AC3
from AC
where (AC3.num = AC.num) and (AC3.[name]= 'pilro')

--&&&&&&&&&&&&&&&&&&&&&&&&&
--Truncate table 与 delete相比有以下几个不同点
/*
1）delete每删除一条记录，都会将操作过程记录在事务日志文件中，
而truncate table语句不会将删除记录的操作过程记录在事务日志文件中。
所以用truncate table 删除所有记录的速度快，但删除之后不能用事务日志文件恢复。
2）delete语句在删除记录时，要先将表中的各行锁定，才能再删除记录，
而truncate table 不会锁定各行，只锁定表和页
3）用truncate table 删除完记录后，自动增长的字段（标识列）会重新开始计数，
而用delete语句删除后，还会从上次最后记录为开始点继续计数。
4）如果要删除记录的表是其它表外键指向的表，那么不能用truncate table语句来删除，只能用delete语句删除。
5）truncate table 语句不能删除参与索引视图的表，而delete语句可以。
*/

--****************************************
-- select语句练习
--
drop table ASum
--
CREATE TABLE dbo.ASum
(
 id int primary key identity, --  标识列
 [name] nvarchar(30) NULL,
 [time] datetime default getdate(),
 num int NULL,
 price float NULL,
 sumprice float NULL,
)
GO

--
select * from Asum
--为查询列添加计算
select [name],[time],num,price,sumprice =((num-1)*price)
from ASum
go
-- as 用来指定别名
select [name],[time],num,price,(num-5)*price as [sum]--[sum]为虚拟列
from ASum
go
--
select *,(num-5)*price as [sum]--[sum]为虚拟列
from ASum
go
--查看最前的记录
select top 1 *
from Asum
--
select top 3 *
from Asum
order by price desc
--
insert into Asum
([name],num,price)
values ('xigua',50,2.99)
go
-- with ties 显示平局的记录
select top 3
with ties *
from asum
order by price
--查看不重复的记录
select distinct price
from asum
--可以用于指定多个字段
select distinct num,price
from asum

--查询表中的标识列或GUID列
select $identity
from asum
-- 注意： 一个表中只能有一个标识列或GUID列
-- ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
-- 用 join on 进行联合查询
select id,AC3.[name],asum.[name],sex,price
from AC3 join asum
on AC3.num = asum.id
-- 通过where子句实现 联合查询，但提倡使用join on
select id,AC3.[name],asum.[name],sex,price
from AC3 , asum
where AC3.num = asum.id

-- 从多个表中进行联合查询
select id,AC3.[name],asum.[name],AC3.sex,AC.[name],price
from
(AC3 join asum
on AC3.num = asum.id)
join AC
on asum.id = AC.num

--================
-- [inner] jion  只显示符合条件的记录
-- left[outer] join  显示左边表中的所有记录，以及右边表中符合条件的记录
-- right[outer] join  显示右边表中的所有记录，以及左边表中符合条件的记录
-- full[outer] join  显示所有表中的记录，包扩符合条件的记录和不符合条件的记录
-- cross join  将一个表的每一条记录和另一个表中的每一条记录搭配成新的记录，不需要用on来设置条件
--
select id,AC3.num,AC3.[name],asum.[name],sex,price
from AC3 left join asum
on AC3.num = asum.id
--
select id,AC3.num,AC3.[name],asum.[name],sex,price
from AC3 right outer join asum
on AC3.num = asum.id
--
select id,AC3.num,AC3.[name],asum.[name],sex,price
from AC3 full join asum
on AC3.num = asum.id
--
select *
from AC3 cross join Asum

-- 使用表别名，可以方便记忆和使用
select id,a.num,a.[name],b.[name],sex,price,b.price * b.num as [sum]
from AC3 as a inner join asum as b
on a.num = b.id

-- join自己

 

-- 在查询条件里使用函数
select * from AC3
where [time] > convert(datetime,'2007-05-25'，102) --最好使用convert来转化，养成好的编程习惯
go

-- 查询null字段的记录
select * from AC3
where sex is null
go

--将结果集作为查询条件
select * from AC3
where num in (1,2,10,11)
go

--
select * from AC3
where name in
(
select name
from AC
where AC.name = AC3.name
)
go

-- 模糊查询
select * from AC
where name like '%p%'

-- like 关键字使用的通配符有 %,_,[],[^]
-- %   替代零个或多个字符的任意字符串
-- _   替代任何单个字符
--[ ]  替代指定方位或集合中的任何单个字符
--[^]  替代不属于指定范围或集合的任何单个字符

-- 注意：通配符作为文字字符使用必须放在[]里
--
select * from AC
where name like '%i%'
go
--
select * from AC
where name like '%i'
go
--
select * from AC
where name like '___a'
go
--
select * from AC
where name like '[k]a%'
go
--
select * from AC
where name like '[^k]a%'
go
 
-- exists 的作用是用来检查在子查询中是否有结果返回，如果有结果则返回为真
-- 如果没有则返回结果为假
select * from AC
where exists
(
select * from AC3
where [name] = 'gatuo'
)
go

--
-- exists 为 true
select * from AC
where exists
(
select * from AC3
where [name] = 'kaka'
)
go

-- ======================================================================================
-- any 和 some 的用法 ： 都是在进行比较预算符时只要子查询中有一行能使结果为真，则结果为真
-- ======================================================================================
--
select * from AC
where [name] = any
(
select [name] from AC3
where sex is NULL
)
go
--
select * from AC
where [name] = some
(
select [name] from AC3
where sex is NULL
)
go
--
select * from AC
where [name] in
(
select [name] from AC3
where sex is NULL
)
go
-- 等效于上面的代码
select * from AC
join [AC3] on AC.[name] = AC3.[name]
where AC3.sex is null
go
--
-- all 要求子查询结果中的所有行都使结果为真，结果才为真
select * from AC
where [name] = all
(
select [name] from AC3
where sex is NULL
)
go

--
-- oreder by 子句来排序
-- 按一个字段来排序
select * from AC
order by [time] desc
go

-- 按多个字段来排序
select * from AC
order by [time] desc,num desc -- 以前面的字段来先排序，然后在其基础上再进行排序
go

-- group by 子句来分组
-- =====================================================================================================
-- 1） group by 子句里可以是字段名，也可以是包含字段值的表达式，但不能是汇总函数。
-- 2） 在select 子句里，除了汇总函数之外，其它所有出现的字段一定要在group by 子句里曾经出现过的字段才行。
-- 3） 在 select 子句里不一定要出现汇总函数，但至少要用group by 分组依据里的一项。
-- =====================================================================================================
-- 基本用法
select [name], count(num) as [sum] from AC
group by [name]
go
-- 没有汇总函数
select [name] from AC
group by [name]
go

-- 执行失败，因为sex字段没有在 group by 子句里出现
select [name],sex from AC
group by [name]
go

-- 使用表达式
select year([time]),[name], count(num) as [sum] from AC
group by year([time]),[name]
go
--
select day([time]),[name], count(num) as [sum] from AC
group by day([time]),[name]
go

-- ===============================================================
-- 使用 with cube 对所有字段进行汇总
-- 使用 with cube 对 group by 所列出的所有分组字段进行汇总运算
-- ===============================================================
select num, count(num) as [sum] from AC
group by num
 with cube
go
--
select [name], count(num) as [sum] from AC
group by [name]
 with cube
go
--
select * from AC
-- 对多字段分别进行汇总
select year([time]) as 年份,[name] as 球员姓名, count(num) as [sum] from AC
group by year([time]),[name]
 with cube
go

--
-- ==================================================================
-- 使用 with rollup 会对 group by 所列出的第一个分组字段进行汇总计算
-- ==================================================================
--
select year([time]) as 年份,[name] as 球员姓名, count(num) as [sum] from AC
group by year([time]),[name]
 with rollup
go
-- ==================================================
-- 使用 group by all 对所有数据分组
-- ==================================================
-- 使用 group by
select year([time]) as 年份,[name] as 球员姓名, count(num) as [sum] from AC
where year([time]) > 2005
group by year([time]),[name]
go
-- 使用 group by all
select year([time]) as 年份,[name] as 球员姓名, count(num) as [sum] from AC
where year([time]) > 2005
group by all year([time]),[name]
go

-- ==================================================
-- 使用 having 子句在分组中设置查询条件
-- ==================================================
-- 聚合不应出现在 WHERE 子句中
select [name] as 球员姓名, count(year([time])) as 年份 from AC
where count(year([time])) > 1
group by [name]
go

-- 改用 having 子句
select [name] as 球员姓名, count(year([time])) as 年份 from AC
group by [name]
having count(year([time])) > 1
go

-- ==================================================
-- 使用 compute 子句来归类
-- ==================================================
-- compute 用于分组统计，生成的统计作为附加的汇总列出现在结果集的最后
--
select year([time]) as 年份,[name] as 球员姓名 from AC
where year([time]) > 2005
compute count(year([time]))
go

-- ==================================================
-- 使用 compute by 归类
-- ==================================================
-- 计算依据列表中的所有表达式也必须同时出现在 order by 子句排序依据列表中。
--
select year([time]) as 年份,[name] as 球员姓名,num from AC
where year([time]) > 2005
order by num desc
compute count(year([time])) by num
go
--
select year([time]) as 年份,[name] as 球员姓名,num from AC
where year([time]) > 2005
order by [name] desc
compute count(year([time])) by [name]
go

-- ==================================================
-- 使用 union 子句来合并多个查询结果
-- ==================================================
-- union 运算不同于 join 运算
-- join 运算是将连两个或多个数据表的字段做左右水平合并，一般来说，合并后字段数会增加
-- 而 union 运算是将多个查询结果上下叠加，合并后字段不会增加，但记录总数会增加
--
-- ===================================================================================================
-- 使用 union 子句的注意条件
-- 1）所有查询中的列数和列的顺序必须相同
-- 2）要合并的数据类型必须兼容，即数据类型可以不同，但必须可以转化
-- 3）合并的查询结果集的字段名称以第一个查询结果的字段名称为名，其他查询结果集的字段名称将会被忽略
-- ===================================================================================================
--
-- union all
select * from AC2
union all
select * from AC3
go

-- union
-- 可以去掉重复记录
select * from AC2
union
select * from AC3
go

-- 利用 union 加入临时数据
--
select * from AC2
union
select * from AC3
union
select 2,'wo','M',year(getdate()),01
go

-- 在 union 的结果集里排序
--
select * from AC2
union
select * from AC3
order by [time] -- order by 只能用在整个union语句的最后，是针对union之后的结果集排序的
go
--
select * from AC2
union
select * from AC3
compute count([time]) -- compute 只能用在整个union语句的最后，是针对union之后的结果集排序的
go

-- 在 union 的结果集里分组
-- ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
select [name],count(num) as person
from
(
select * from AC2
union
select * from AC3
 -- order by [time]
) as TempTable
group by [name]
go

-- ==================================================
-- 用 select into 创建一个新表
-- ==================================================
--
select AC.num ,[name]
into new_AC
from AC
go

--
select * from new_AC
go

--
drop table new_AC
--
-- 用select into 设置复杂的查询条件
select AC.num ,AC.[name],AC2.num as num2
into new_AC2
from AC join AC2
on AC.num = AC2.num
go
--
select * from new_AC2

-- 使用 select into 语句复制表结构
-- 只要where子句返回false，查询出来的结果集为空，就可以不在新建的表里插入数据，只建立一个新表
select *
into new_AC3
from AC
where 0 <> 0
go

--
select * from new_AC3

-- 可以在不同的数据库里进行查询，需要精确的指定表名即可。

-- 默认情况下，与 null 作比较返回的结果都是false
-- 如果将系统选项ANSI_NULLS设为off，可以对 null 做相等比较，其他比较返回还是false
-- 对null进行排序，null字段永远都是最小的值。

-- =========================================
-- 使用 isnull 函数替换 null 值
-- isnull (check_expression,replacement_value)
-- 为空时返回replacement_value的值
-- =========================================
--
select * from AC3
go
--
select num,isnull(banben,2) as name3 from AC3

-- ***********************************************************
-- SQL Server 2005 新增功能
-- ====================================
-- select
-- 1） with 公用表达式
-- 公用表达式就是指定临时命名的结果集。
-- 2） 用于选择用户定义的类型值的类型列名
-- ====================================
--
with temp (id,[name]) as
(
select num,[name] from AC
)
select * from temp
go

-- ====================================
-- insert
-- 1） with 公用表达式
-- 2） top 表达式
-- 3） output 子句
--   使用 output 子句可以返回插入到数据表里的记录
-- 4） 允许插入用户定义的类型值
-- ====================================
--
insert top (2) into new_AC
output inserted.num,inserted.[name]
select num,[name]
from AC

--
truncate table new_AC
go
-- 不会返回记录
insert top (2) into new_AC
select num,[name]
from AC

-- ====================================
-- update
-- 1） with 公用表达式
-- 2） top 表达式
-- 3） output 子句
--   使用 output 子句可以返回插入到数据表里的记录
-- 4） 允许插入用户定义的类型值
-- 5）write 子句
-- ====================================
-- .write (expression,@offset,@length)
-- 使用 write 子句可以修改指定列中的值的一部分，但必须是varchar(max),nvarchar(max),
-- 或 varbinary(max)类型的列才能使用 write 子句。
--
select * from new_AC
go
alter table new_AC
alter column [name] nvarchar(max)
go

update new_AC
set [name].write('sheva',0,1)
where num = 2
go
select * from new_AC

-- ====================================
-- delete
-- 1） with 公用表达式
-- 2） top 表达式
-- 3） output 子句
-- ==================================== 

 

 

 

 

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

附上生成的脚本文件

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AC3]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AC3](
 [num] [int] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](30) NULL,
 [sex] [nvarchar](10) NULL,
 [time] [datetime] NULL DEFAULT (getdate()),
 [banben] [smallint] NULL,
PRIMARY KEY CLUSTERED
(
 [num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ASum]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ASum](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](30) NULL,
 [time] [datetime] NULL DEFAULT (getdate()),
 [num] [int] NULL,
 [price] [float] NULL,
 [sumprice] [float] NULL,
PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[new_AC]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[new_AC](
 [num] [int] NOT NULL,
 [name] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[new_AC2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[new_AC2](
 [num] [int] NOT NULL,
 [name] [nvarchar](30) NULL,
 [num2] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[new_AC3]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[new_AC3](
 [num] [int] NOT NULL,
 [name] [nvarchar](30) NULL,
 [sex] [nvarchar](10) NULL,
 [time] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AC]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AC](
 [num] [int] NOT NULL,
 [name] [nvarchar](30) NULL,
 [sex] [nvarchar](10) NULL,
 [time] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
 [num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AC2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AC2](
 [num] [int] NOT NULL,
 [name] [nvarchar](30) NULL,
 [sex] [nvarchar](10) NULL,
 [time] [datetime] NULL DEFAULT (getdate()),
 [banben] [smallint] NULL,
PRIMARY KEY CLUSTERED
(
 [num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END