常用SQL语句

/*期间 遇到的问题
1.if()里面只能是一个表达式 不能直接为 true 0等；
2.

*/
--数据类型
/*
image 二进制数据类型
char 固定长度的非Unicode字符数据类型  最大长度8000个字符
varchar 长度可变的非.........................
text 20亿字符

nchar 固定长度的Unicode数据 4000个字符
nvarchar 不固定长度的Unicode数据
ntext 10亿字符
--Unicode 所有字符都是用两个字节来编码 当内容为英文时 基本上Unicode比非Unicode数据大一倍
-- 具体的各种编码一般网络通信和视频音频流处理的书会详细介绍各种编码

money 货币数据类型 精确到小数点后4位（包含）
bit 1或0  一般1为是 0为非
datetime 日期时间
*/

--可以手动更新系统表
UPDATE  STATISTICS  GOODS --GOODS为某一个表

USE MASTER
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME='NEWBFM')--EXISTS() 存在检查  不会空时 返回true
DROP DATABASE NEWBFM

CREATE DATABASE NEWBFM
ON PRIMARY  --PRIMARY为文件组 可以更换 不写时也默认为PRIMARY
(
NAME='NEWBFM',
FILENAME='C:\NEWBFM.MDF',
SIZE=10,
FILEGROWTH=10%
),S
(
NAME='NEWBFM2',
FILENAME='C:\NEWBFM.NDF',
SIZE=20,
FILEGROWTH=10%
)
LOG ON
(
NAME='NEWBFM_LOG',
FILENAME='C:\NEWBFM_LOG.LDF',
SIZE=10,
FILEGROWTH=1
),
(
NAME='NEWBFM2_LGO',
FILENAME='C:\NEWBFM2_LOG.LDF',
SIZE=20,
FILEGROWTH=20%
)
/*建立表
*/
USE NEWBFM
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='GOODS')--查看系统表 SYSOBJECTS里是否存在有GOODS表
DROP TABLE GOODS
CREATE TABLE GOODS--创建表
(
[ID] INT IDENTITY(100,1),--标识种子 初始为100 每次递增1
[S_ID] INT NOT NULL,
[NMAE] VARCHAR(50) NOT NULL,
[AGE] INT NOT NULL,
[MONEY] MONEY ,
[BIANHAO] NUMERIC(18,0)--身份证 18位 0个小数位
--现在有位数为X的身份证 个人建议用varchar(18)因为他是数字和英文，
--使用非unicode的数据类型可以比nvarchar(18)节约一半的空间消耗
--身份证为18位或者15位

--SELECT * FROM GOODS
)
G0
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='USERS')--查看系统表 SYSOBJECTS里是否存在有GOODS表
DROP TABLE USERS
CREATE TABLE USERS
(
ID INT NOT NULL
)
GO

ALTER TABLE GOODS
ADD CONSTRAINT DF_IDD DEFAULT (1000) FOR [MONEY]--添加约束

ALTER TABLE GOODS
ADD CONSTRAINT PK_NO PRIMARY KEY(S_ID)--主键为 S_ID

ALTER TABLE GOODS
ADD CONSTRAINT UQ_ID UNIQUE(BIANHAO)--唯一约束] --默认约束

ALTER TABLE GOODS
ADD CONSTRAINT CK_AA CHECK(AGE BETWEEN 1 AND 109)--检查约束 1到109 岁

ALTER TABLE USERS
ADD CONSTRAINT FK_SS FOREIGN KEY([ID])  REFERENCES GOODS([S_ID]) --对USERS进行外键约束 外键为id 主键为goods表的s_id

GO

--添加SQL登陆账户
exec sp_addlogin 'lipeng','213321'
--添加windows登陆账户
exec sp_grantlogin  'home\123' --home为域或机器名 123为用户
--添加数据库用户
exec sp_grantdbaccess 'li','123312' --sql 登陆用户名和密码

--授权
USE GOODS
GO
GRANT SELECT ,INSERT,UPDATE ON GOODS TO li  --将对GOODS数据库的SELECT INSERT UPDATE 权限授权给 数据库用户li

-----------------------------------------添加数据------------------------------------------------------------------
PRINT CONVERT(VARCHAR(10) ,@@IDENTITY) --当表中有 标识列 时 @@IDENTITY 记录最后一次插入insert数据时该列的表示列;更新和查询不算

select * into newgoods from goods --将goods表中的数据插入goos表 如果goos表不存在 则新建 保持原有约束等信息

select *,IDENTITY(INT,100,1)AS ID INTO NewTable from table--向新表中设置新的标识列 但是如果新表复制了原表的标识列 这里将报错

insert gooos  select * from goods--如果gooos不存在 报错
--同时插入多行-----------------------
insert goods(id,name,age)
select 2,'lipeng',24 union
select 3,'li',22
-------------------------------------------------------
insert goods(name,age) values('lipeng',24)

-----------------------------------------查询数据-------将字段加[]显式告诉sql 这是一个列名-------------------------------------------------------------
select id,name from goods where id>22 group by id,name  order by [id]desc
select distinct goods.* from goods where id>20 --如果返回的结果集中 存在完全相同的几行数据 则相同的数据只返回一条
select top 2 * from a  order by  id ASC--返回10行

-- A表   id (默认排序 asc)
------------
 1
 2
 3
 4
----------- 一定要注意下面的事情 sql语句是先执行FROM后面的语句 最后才执行要返回的字段语句 
select top 50 percent * from  A  order by  id asc--返回整个表前50% 返回 1 2
select top 50 percent * from  A  order by  id desc--返回整个表前50% 返回 4 3

--1.性能方面 能用where筛选的 尽量不必留到having里筛选。
--2.并不是在列上建立了索引 查询该列时必然用到该索引；sql运行时会自动判断 如果使用索引反而慢时 sql会放弃使用索引。
--3.非聚集所以索引，聚集索引，全文索引。
SELECT id2,id3 FROM A where id2>10 group by id2,id3  having by max(id3)>20

-----------------------------------------修改数据---------------------------------------------------------------------
update goods set [name]=replace([name],'li','wang') where age=24--将年龄为22岁的 姓名里的'li'全部转换成'wang'

-----------------------------------------删除数据---------------------------------------------------------------------
delete  from goods where name='lipeng'  --当删除的行中有主键值 被其他表引用的列时 删除失败 注意：这里不能指出* 或字段；

truncate table goods --删除整张表 留一个空表 结构等保留
--Truncate 比delete 执行速度快 系统资源和日志暂用的更少

drop table A --将彻底删除表

--局部变量
declare @u_name nvarchar(50)
--赋值方法1
set @u_name='lipeng'
--赋值方法2
select @u_name='lipeng'

--如果返回多条记录 @u_name的值保留最后一条数据中的相关值
select @u_name=name from users where age=22

--输出显示
--方法1
print 'lipeng'
--方法2
select 'lipeng'as 姓名

--类型转换
print convert(varchar(50),44)+'11'--输出 4411
print convert(varchar(50),44)+convert(varchar(50),11)--输出 4411
print 88+'11' --输出99

------------------------------------控制语句-----------------------------------------
IF(2>1)
BEGIN
--........
END
ELSE
BEGIN
--.........
END
----------------WHILE循环 ---BREAK--------  CONTINUE----
WHILE(2>1)
BEGIN
--...........
END

---------------
表A
id2
1
2
3
4

SELECT id2=CASE
  WHEN id2=1 THEN 'A'
  WHEN id2>1 and id2<4 THEN 'B'
  WHEN ID2=4 THEN 'C'
FROM A WHERE id2<5
---------结果-----
A
B
B
C
------为了提高性能 尽量使用批处理器的形势执行任务----------------------------------------------------------------
--好处
/*
1.简化数据库管理
2.SQL SERVER 将批处理编译成一个执行单元 提高效率
3.一般将批处理单元放在服务器上 如：存储过程
*/--------------------------------------------------------------------------------------
--事务
--1.隐性事务
--2.显式事务 
--用 BEGIN TRANSACTION 显式开始事务

GO
declare @error int
BEGIN TRANSACTION
SELECT * FROM GOODS
set @error=@error+@@error
SELECT * FROM GOODS
set @error=@error+@@error
SELECT * FROM GOODS
set @error=@error+@@error

IF (@error<>0)--当系统中记录着最后一次错误的信息时 不为0
BEGIN
--SET XACT_ABORT ON --可以加上
ROLLBACK TRANSACTION--回滚
END
ELSE
BEGIN
COMMIT TRANSACTION --无错则提交
END
select * from delete
--3.回滚失败
SET XACT_ABORT OFF  --当为OFF(默认的) 事务回滚可能因为各种数据完整性限制失败
--当设为 ON时 可避免上述问题 但是这是失败了  详细在网上搜索

--索引  UNIQUE(唯一索引) CLUSTERED (聚集索引)
-- 数据多或者数据不相同的多或者需要按某列排序可用索引
--当数据不多时 用索引性能(查索引表的时间+查表的时间)比直接对表查询更慢

--创建唯一约束 则自动创建唯一索引 但是它的性能不如主键约束好
--创建主键 也自动创建唯一索引 默认为聚集索引 主键它是特殊的唯一索引
--聚集索引 --逻辑顺序 --物理顺序
--聚集索引比非聚集索引速度更快 但聚集索引只能有一个 非聚集索引可以有多个

--创建聚集索引
IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME='PK_GOODS_ID')
DROP INDEX GOODS.PK_GOODS_ID
CREATE CLUSTERED INDEX PK_GOODS_ID
ON GOODS([ID]) WITH FILLFACTOR=50--填充因子  具体填充因子的用处搜索
GO
--创建非聚集索引
IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME='IX_GOODS_ID')
DROP INDEX GOODS.IX_GOODS_ID
CREATE NONCLUSTERED INDEX IX_GOODS_ID
ON GOODS([ID,[ID2]]) WITH FILLFACTOR=50 -- 填充因子 具体搜索 可以同时添加多个列
GO

--为了保证索引设置完了 怕系统未及时更新 可以手动更新
UPDATE  STATISTICS  GOODS --GOODS为某一个表
/* 资料
SQL   SERVER在做查询优化时，会参考表里各字段的分布统计信息，根据它来判断是否利用索引、
如何进行表关联等决定。所以这个统计信息是否是最新的，会对查询语句的执行效率有些影响。  
为了保证SQL   SERVER能做出最合适的判断，一般都是将统计设为自动更新。  
上面的语句是手工进行重新更新的。
在指定的表或索引视图中，对一个或多个统计组（集合）有关键值分发的信息进行更新。
*/
--在一个表上创建的非聚集索引或列统计不能超过249 个
/*资料
每个表249个索引统计的上限包括索引，用户建立的统计，和系统自动生成统计。往往情况下，如果缺乏索引，
单列的筛选条件会造成系统自动生成一个单列的统计信息，几个列的复合筛选，比如col1='x' and col2='y'，
会自动生成另一个统计。每一个自动生成的统计都和索引和用户建立统计一样，都占有一个位置。
如果表里列很多，系统又设定自动生成统计数据，有可能会有很多自动生成的统计。一方面表示了索引的缺乏，
另一方面也有可能使用的大量不同的即时查询语句。
上述的是比较长远的做法，目前的解决方案，可以看一下系统自动生成的统计数据，把不需要或重复的drop掉
*/
--查询时SQL 自动选择使用还是不使用索引 但是也可以手动使用索引 。
SELECT * FROM GOODS (INDEX PK_GOODS)-- PK_GOODS为索引名
WHERE  ID>10

--视图
--1.方便 2.可以用来控制用户浏览的数据 可以用来做权限设计
--3.隐藏不必或者不该返回的列
--不建立视图索引时 视图不只是一个完全的虚拟表每次被使用视图时 视图才去查询得到该视图表的内容
--如果视图调用的比较多 建议使用视图索引

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='VIEW_GOODS2')
DROP VIEW VIEW_GOODS2
GO
CREATE VIEW VIEW_GOODS2
AS
SELECT GOODS.*,GOODS3.ID FROM GOODS LEFT JOIN GOODS3 ON GOODS.ID=GOODS3.ID
GO

--存储过程
--优点 放在服务器数据库上面 已经经过编译
--系统常用的存储过程

exec sp_databases   --返回服务器上所有的数据库
exec sp_tables   --返回数据库中都有哪些表

sp_columns --返回某个表 列的信息 如：这个表里有几行数据
sp_helptext --返回存过过程，视图 触发器 的创建文本 （加密的不能返回）
sp_renamedb --调用该储存过程可以修改数据库名称 --如果目录不存在 则报错

xp_cmdshell Dos命令 [NO_OUTPUT] --可执行DOS命令  NO_OUTPUT为是否显示返回信息
exec xp_cmdshell 'MD d:\BUF'
--创建存储过程--------------------------------
USE BUF
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PROC_ADD')
DROP PROCEDURE PROC_ADD

CREATE PROCEDURE PROC_ADD
@ID INT =60,--输入变量  默认为60
@ID2 INT =60--输入变量  默认为60
AS
SELECT * FROM GOODS WHERE ID=@ID
GO
--不带输出
--exec proc_add ，60  错误
--exec proc_add @id2=40
--exec proc_add 40,40
--exec proc_add
USE BUF
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PROC_ADD2')
DROP PROCEDURE PROC_ADD2
GO

CREATE PROCEDURE PROC_ADD2
@sum int output ,--输出变量
@ID INT =60,--输入变量  默认为60
@ID2 INT =60--输入变量  默认为60
AS
SELECT @sum=count(*) FROM GOODS WHERE ID=@ID and id2=@id2
PRINT @SUM
GO
--带输出
declare @sumsize int

set @sumsize=40
exec proc_add2 @sumsize output ,50,50

 

转载于:https://www.cnblogs.com/yueliang/archive/2010/04/19/1715540.html