SQL语句小结

建立数据库的完整过程（先只记录一些不常用的，备忘）

create database pages
on primary --建立主文件
(
name = pages,
filename = 'E:\myWebSite\myTest\App_Data\pages.mdf',
size = 5MB,      --初始大小
maxsize = 10MB, --最大
filegrowth = 1MB --每次增加大小
),      --主次文件间是有豆号的，
filegroup subFile --建立次要文件，可以建立若干个
(
name = subPages,
filename = 'E:\myWebSite\myTest\App_Data\subPages.ndf',
size = 1MB,
maxsize = 10MB,
filegrowth = 1MB
)
log on --建立日志文件
(
name = logPages,
filename = 'E:\myWebSite\myTest\App_Data\logPages.ldf',
    maxsize = 10MB,
filegrowth = 1MB
)

collate chinese_PRC_CI_AS
--排序规则chinese_PRC_简体中文 CI 不分大小写
--AS 不区分重音

-- Alter database XX 修改数据库
-- Add file 或 (add log file)修改文件或修改日志文件
--(
--)
--Drop database XX 删除数据库

=====================================================

use vote -- 应用某数据库
go
SELECT * FROM voteDetails
GO
SELECT voteItem FROM voteDetails
GO
SELECT DISTINCT voteItem FROM voteDetails --查找无重复的记录
GO
SELECT * FROM voteDetails WHERE id= 1 OR voteDetailsID = 5
GO
SELECT * FROM voteDetails WHERE voteItem is null --查询某列为空
--select 语句可以进行数学、逻辑运算
SELECT voteItem,voteNum,voteNum+100 AS TEMP FROM voteDetails
GO
SELECT voteItem,voteNum,voteNum-100 TEMP FROM voteDetails
GO
SELECT voteItem,voteNum,voteNum*100 FROM voteDetails
GO
SELECT voteItem AS TEMPItem,voteNum,voteNum/100 FROM voteDetails
GO
SELECT voteItem,voteNum,voteNum%100 AS TEMP FROM voteDetails
GO
--逻辑运算符
< > = <= >= != <> AND OR NOT
--不精确匹配 like %代表多个字符　_ 代表一个字符

SELECT voteItem FROM voteDetails WHERE voteItem LIKE '很%' --查询以很开头的
GO
SELECT * FROM voteDetails WHERE voteItem LIKE '_满%'
GO
UNION -- 将两个查询结果去掉重复部分组合起来
UNION ALL
INTERSECT --返回两个表中共有的行

SELECT name1 FROM table1 WHERE name1= 'lkf' UNIOn SELECT name2 FROM table2 WHERE name2= 'lkf'

--SQL 语句的优化
SELECT * FROM voteDetails WHERE voteItem = '很好' OR voteItem = '满意' OR voteItem ='不满意'
--优化后
SELECT * FROM voteDetails WHERE voteItem IN('很好','满意' ,'不满意')
GO
SELECT * FROM voteDetails WHERE voteNum >100 AND voteNum <10000
GO
SELECT * FROM voteDetails WHERE voteNum BETWEEN 100 AND 10000
GO
UPDATE voteDetails SET voteNum =10 WHERE voteDetailsID =8

--█████████████████████████████████████████████████████
-- SQL函数
--avg 计算平均值
SELECT AVG(voteNum) FROM voteDetails
--count
SELECT COUNT(id&voteDetailsID) FROM voteDetails
--min max 最小值最大值
SELECT MIN(voteNum) FROM voteDetails
GO
SELECT MAX(voteNum) FROM voteDetails
--sum 求和
SELECT SUM(voteNum) FROM voteDetails

SELECT GETDATE(),GETUTCDATE(),YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE())
GO
SELECT DATEPART(d,GETDATE()),DATENAME(d,GETDATE())--DATENAME返回指定日期部分的字符串
GO
SELECT DATEDIFF(d,GETDATE(),GETDATE()+2)--开始时间与被比较的时间的差值 d表示差的天数,s表示秒

SELECT DATEADD(d,2,GETDATE())--当前时间加上指定时间后的时间

SELECT ASCII(voteItem) FROM voteDetails
GO
SELECT CHAR(65)
GO
SELECT NCHAR(65)--UNIC
GO
SELECT SOUNDEX(2)
GO
SELECT SPACE(12345)
GO
SELECT LEFT('ABCDEFGHRJKLMN',5)--左边取5个字符
GO
SELECT RIGHT('ABCDEFGHRJKLMN',5)
GO
SELECT LEN('ABCDEFGHRJKLMN')-- 字符串长度
GO
SELECT LOWER('FVVCFGGGFFFFFFFGHH'),UPPER('sffgcg hcfddfcdf')
GO
SELECT LTRIM(' ffffffff'),RTRIM('vvgjsv ')--去掉左右空格

--███████████████████████投票系统SQL脚本██████████████████████

create database vote
go
use vote
go
--创建管理员信息表

create table admin
(
id int identity(1,1) primary key,--主键，标识字段
userName varchar(20) not null,--管理员登录帐号
password varchar(20) not null--登录密码
)
go
insert into admin(userName,password) values('fengyan','fengyan')
go
select * from admin
go
--投票主表
create table voteMaster
(
id int identity(1,1) primary key,
voteTitle varchar(30) not null,--投票的标题，（针对什么内容进行投票）
voteSum int default(0)--相应标题下投票的总票数，初始值为0
)
go
insert into voteMaster(voteTitle) values('对我们教程的满意度')
insert into voteMaster(voteTitle) values('你从哪里得知浪曦在线')

select * from voteMaster
select * from voteDetails
--创建投票从表
go
create table voteDetails
(
id int foreign key references voteMaster(id),--外键约束，确保数据的完整性
voteDetailsID int identity(1,1) primary key,--主键，标识字段
voteItem varchar(20) not null,--参与投票的选项
voteNum int default(0)--相应选手得到的票数
)
go
insert into voteDetails(id,voteItem) values(1,'非常满意')
insert into voteDetails(id,voteItem) values(1,'比较满意')
insert into voteDetails(id,voteItem) values(1,'很一般')
insert into voteDetails(id,voteItem) values(1,'有待改进')
insert into voteDetails(id,voteItem) values(2,'友情连接')
insert into voteDetails(id,voteItem) values(2,'百度')
insert into voteDetails(id,voteItem) values(2,'广告')
insert into voteDetails(id,voteItem) values(2,'杂志')
go
--创建更新总票数的触发器
create trigger updateVoteSum
on voteDetails
for update
as
begin
update voteMaster set voteSum = voteSum+1 where id=(select top 1 id from inserted)
end
go
--创建系统配置信息表
create table voteConfig
(
id int foreign key references voteMaster(id),--设置系统前台需要调查的投票项
checkIP int default(0),--系统是否限制IP，0为限制，1为不限制
checkTime int default(0)--限制时间间隔，单位（分钟）
)
go
insert into voteConfig(id) values(1)
select * from voteConfig
go
--创建投票者信息表
create table voter
(
id int foreign key references voteMaster(id),--该用户针对哪项调查进行投票，也加上外键约束
ip varchar(30) not null,--该用户的IP
voteTime datetime default(getdate()),--用户的投票时间
voteNum int default(1) --该用户针对此项目投票的次数
)
go
insert into voter(id,ip) values(1,'222.125.35.65')
go
select * from voter

--█████████████████建立关系级联更新删除████████████████████████

一、触发器方式：
create trigger trg_A
on A
for update,delete
as
begin
    if exists(select 1 from inserted)
        update B set Name=(select Name from inserted) where Name=(select Name from deleted)
    else
        delete B where Name=(select Name from deleted)
end
go

二、级联更新和级联删除方式：

ALTER TABLE [dbo].[T_USERGROUP] ADD
CONSTRAINT [FK_T_USERGROUP_T_ACCTTEMPLATE] FOREIGN KEY
( [ATNAME] )

REFERENCES [dbo].[T_ACCTTEMPLATE]

( [ATNAME] ) ON UPDATE CASCADE ON DELETE CASCADE

================================================================================

ALTER TABLE 表名1 add constraint 约束名 foreign key（字段) references 表名2（字段）on update cascade

on delete cascade

==================================================================

Ms Sql	ADCCESS
binary	VarBinary
bit	Boolean
char	Char
datetime	Date
decimal	Numeric
float	Double
int	Integer
money	Currency
nchar	WChar
ntext	LongVarChar
numeric	Numeric
nvarchar	VarWChar
real	Single
smalldatetime	Date
smallint	SmallInt
text	LongVarChar
timestamp	Binary
tinyint	UnsignedTinyInt
UniqueIdentifier	GUID
varBinary	VarBinary
varChar	VarChar