首页 > 代码库 > SQL SERVER命令总结
SQL SERVER命令总结
SQL SERVER命令总结
查询语句:
SELECT [ALL/DISTINCT][TOP]
<目标列> [as 别名][,<目标列1> as 别名]…
FROM <表名或视图名>[as 别名]…
[WHERE 条件表达式…]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC/DESC]]
应该注意:SELECT语句的顺序:
SELECT-->FROM-->WHERE-->GROUP BY-à HAVINGàORDER BY
SELECT INTO:生成新表格
DISTINCT:唯一
GROUP BY:分组汇总
ORDER BY:排序,默认情况下为升序.
ASC:升序
DESC:降序
AS:起别名
HAVING:筛选分组汇总后的行
SELECT TOP n<column_name>FROM<表名>:表示查询前N行
SELECT TOP n PERCENT*FROM<表名>:表示按一定的百分比提取
LIKE:模糊查询,仅于CHAR或是VERCHAR以及通配符连用
其他模糊查询:
IN:用于返回给定的值与列表中的值相匹配的行
BETWEEN…AND…:在..之间查询
IS NOT NULL:查询不为空的数据
查询中使用的常量:一般与 “+”连用.起到一个组合的目的
注意
1. SELECT后面的字段如果不包括聚合函数,必须参加分组(GROUP BY
2. GROUP BY后面不能使用聚合函数
3. 查询时,可以使用2个聚合函数
使用INSERT插入数据
ü 添加完整记录:
INSERT [INTO]<目标表名>VALUES<values>
VALUES:字段值列表(顺序与建表的顺序必须一样)
ü 添加不完整记录:
INSERT [INTO]<目标表名>(字段名列表)VALUES<字段值列表>
特别的记住:字段名列表和字段值列表是一一对应的.即后面的字段值随着前面的字段名的改变而改变.
另一种方法:
语法:INSERT<表名>SELECT<column_list>FROM<表名2)
还可以将数据有选择性的添加到另一个表中
语法:INSERT INTO Sales(Stor_id)SELECT stor_id FROM Stores
UPDATE语句
更新一行:
UPDATE<新表名>SET<目标列=值>[WHERE<条件>](范围)
更新多行:
UPDATE<表名>SET<目标列=值>;{<---没有WHERE}
更新一行和更新多行的区别在于有没有WHERE的存在
DELETE语句
1:删除一行:
语法:DELETE FROM<表名>[WHERE<条件>]
2:删除多行:
语法:DELETE FROM<表名>
删除一行和删除多行区别在于有没有WHERE的存在
3:TRUNCATE TABLE
用于删除表中所有行的命令.DROP删除表
与DELETE的区别在于:TRUNCATE不写日值文件,无法恢复数据
JOIN语句 联接信息:
Table_a AS table_alias_a JOIN table_b AS table_alias_b
On
table_alias_a .<common_field>=table_alias_b.< common_field>
?--------------------公共字段---------------------------à
A:内联接
INNER JOIN:查询两个表中的公共部分
B:外联接
左外联接:left OUTER JOIN
右外联接:right OUTER JOIN
完整联接:FULL OUTER JOIN
C:自联接
创建表的结构
1.名称:表名 字段名
2.数据类型: 系统定义的
用户定义的: exec sp_addtype 用户定义的数据类型名 , 数据类型(系统有的),not null(是否允许为空)
3.大小:
4.约束:
主键约束 PRIMARY KEY create table 表名(字段 数据类型 primary key /
primary key(字段…))
唯一约束 UNIQUE create table 表名(字段数据类型 unique)
默认约束DEFAULT create table 表名(字段 数据类型 default ‘值/表达式’)
检查约束 CHECK create table 表名(字段 数据类型 check (字段的表达式)
外键约束 FOREIGN KEY create table表名(字段 数据类型 foreign key references(应用) 另一个表名(字段))
更新表的结构
1.修改数据类型:alter table 表名 alter column 列名 数据类型
2.添加一列: alter table 表名 add 列名数据类型
3.删除一列: alter table 表名 drop column 列名
4.增加约束: alter table 表名 add constraint 约束名 约束表达式
主键约束: primary key(列名列表)
唯一约束: unique(列名)
默认约束: default ‘值’for 列名
检查约束: check (列的检查约束表达式)
外键约束: foreign key(列名)references 表名(列名)
删除表的结构
drop table 表名
子查询
Select 字段 from 表(select查询)
子查询就是内层查询产生一个用于外层查询的条件
子查询经常使用的是条件运算符(>,<,<=,>=,=,!=)
子查询与in联合使用经常用于判断一列数据
子查询与exists联合使用经常用于判断一张表的字段
触发器
1。触发器:是一种特殊存储过程,它能对有触发器的表中的数据进行保护.
2。触发器主要通过操作事件(insert,update,delete)进行触发而被自动执行,
不能被调用,也不能传递参数
3。a)触发器根据数据修改语句可分为:insert触发器,update触发器,delete触发器
b)根据引起触发时刻可分为after触发器,instead触发器。
i.after触发器是在执行数据操作之后激发
ii.instead of 触发器是在执行数据操作之前激发
iii.一个表可以建立多个after触发器,可以建立一个instead of触发器
触发器中的inserted表和deleted表
1。触发器运行后在内存中自动创建。
2。他们用于对触发器执行后某些数据的测试条件
3。用户不能直接操作2个表
4。delete触发器将删除的内容放在deleted表中。
insert触发器将添加的内容存在inserted表中。
update触发器将替换前的内容放在deleted表中,将替换后的新内容放在inserted表中。
创建触发器:
CREATE TRIGGER
触发器名
ON
表名/视图名
FOR
触发器类型
AS
……T-SQL
语句
例如:
/*如果被删除的学生有考试成绩的话,就不允许删除这个学生*/
create trigger t_stu
on tbl_stu
for delete
begin
declare @id int
select @id = stuid from deleted
if exists(select * from tbl_cj where stuid = @id)
begin
print ‘学生有成绩,不可删除!!‘
rollback transaction
end
end
delete tbl_stu where stuid = 1007—当删除时,看消息框提示
删除触发器:drop trigger 触发器名
例如:drop trigger t_stu
修改触发器:将创建触发器的create关键字更换成alter
存储过程
存储过程:是存储在salserver服务器中的一组预编译过的t-sql语句,当第一次调用后,就驻留在内存中,以后调用时不必再进行编译。优点在于一次编写,多次调用。调用过程在服务器端执行,执行速度快。
存储过程分为:系统存储过程,系统存储过程。
存储过程语法:
create procedure 存储过程名 @参数 参数类型
as
任意数量的T-SQL语句
例如:
create procedure stu_name @stuname char(10)
as
select * from stu
调用存储过程 exec 存储过程名 参数
例如:exec stu_name ‘aa‘
事务
--显示事务
Create table aaa
(
Aid int ,
Aname char(10),
Asex char(2) check(asex in (‘m’,’n’))
)
Set xact_abort on
Begin transaction
Intsert into aaa values(101,’aa’,’n’)
Intsert into aaa values(102,’bb’,’m’)
Intsert into aaa values(103,’cc’,’n’)
Intsert into aaa values(104,’dd’,’a’)
Intsert into aaa values(105,’ee’,’m’)
If @@error = 0
Commit
Else
Rollback
Go
Select * from aaa
Drop table aaa
--隐性事务
Set xact_abort on
Set implicit_transactions on
Create table aaa
(
Aid int ,
Aname char(10),
Asex char(2) check(asex in (‘m’,’n’))
)
Intsert into aaa values(101,’aa’,’n’)
Intsert into aaa values(102,’bb’,’m’)
Intsert into aaa values(103,’cc’,’n’)
Intsert into aaa values(104,’dd’,’a’)
Intsert into aaa values(105,’ee’,’m’)
Commit
--默认提交事务
Create table aaa
(
Aid int ,
Aname char(10),
Asex char(2) check(asex in (‘m’,’n’))
)
Intsert into aaa values(101,’aa’,’n’)
Intsert into aaa values(102,’bb’,’m’)
Intsert into aaa values(103,’cc’,’n’)
Intsert into aaa values(104,’dd’,’a’)
Intsert into aaa values(105,’ee’,’m’)
/*-------------------------------------------------------------------
Commit :提交事务中的一切操作,使得事务对数据库的修改有效
Rollback:回滚事务中的一切操作,使得事务对数据库的操作无效
--------------- ---------------------------------------------------
事务控制语句
1. 设置隐性事务模式
1) set implicit_transactions on启动隐性事务模式
2) set implicit_transactions off 关闭隐性事务模式
2. 设置自动回滚模式
1) set xact_abort on 当事务中任意一条语句产生运行时错误,整个事物将中止,并整体回滚。
2) set xact_abort off 当事务中语句产生运行时错误,将中止本条语句并且回滚本条语句。
注意:set xact_abort 的设置是在执行或运行时设置,而不是在分析时设置。
--------------------------------------------------------------
首次执行下列语句都会自动启动一个隐性事务:
Alter table,create,delete,drop,fetch(读取),grant(授予),insert,open,revoke(撤销),select,truncate table,update
事务中不可以使用的语句:
1. 数据库创建:create database
2. 数据库修改:alter database
3. 数据库删除:drop database
4. 数据库备份:dump database,backup database
5. 数据库还原:load database,restore database
6. 事务日志备份:dump transaction,backup log
7. 事务日志还原:load transcation,restore log
8. 配置:reconfigure
9. 磁盘初始化:disk init
10. 更新统计数据:updata statistics
11. 显示或设置数据库选项:sp_dboptin
游标
游标:允许用户能够从select语句查询的结果集中,逐条访问纪录,可以根据用户需要逐行的显示、修改、删除这些记录的数据访问处理机制。(游标就是数据表上的指针)
使用游标的步骤:
(1) 声明游标
declare 游标名 cursor for select 语句
(2) 打开游标
open 游标名
1) 当游标被打开时,指针会自动指在第一行;
2) 打开游标后,如果@@error=0表示游标打开操作成功;
3) 打开游标后,可以用@@cursor_rows返回纪录行数;
(3) 处理数据:
a) 移动到当前行并读取数据
fetch游标名
b) 删除当前行数据
delete from 表名/视图名 where current of游标名
c) 修改当前行数据
update from表名/视图名 set 列名=表达式 where current of 游标名
(4) 关闭游标
close 游标名
(5) 释放游标
deallocate 游标名
游标类型:
(1)static:静态,当用户A察看表中记录的同时,如果有用户B修改表中记录,A用户将不会看到B修改的纪录,只看A使用open打开表时所看到的纪录。
(2)Dynamic:动态,当用户A察看表中记录的同时,如果有用户B修改表中记录,A用户将看到B修改的纪录,也就是表中记录会不断的刷新,但是会耗费较大的系统资源。
(3)Forward Only:仅向前,游标仅能向前滚动。
(4)Scroll:滚动,游标可以前后左右滚动。
select * from student
/*打开游标,读取数据*/
declare c_stu cursor keyset for select * from student
open c_stu
if @@error = 0
print ‘学生总数‘ + convert(char(5),@@cursor_rows)
else
print ‘读取学生数出错!‘
close c_stu
deallocate c_stu
---------------------------------------------------------------------------------------------------------
--keyset:键集游标,当游标打开时,在tempdb数据库中自动创建keyset表,用来记录游标读取的数据(仅纪录表示字段)
---------------------------------------------------------------------------------------------------------
/*使用游标处理数据*/
--使用游标,在表中逐行读取
select * from student
declare c_stu cursor for select * from student
open c_stu
fetch next from c_stu
while @@fetch_status = 0
fetch next from c_stu
close c_stu
deallcoate c_stu
-------------------------------------------------------------------------------------------------------
--fetch的使用
--1.first:移动到第一行,并作为当前行
--2.next:移动到下一行,并作为当前行
--3.prior:移动到上一行,并作为当前行
--4.last:移动到最后一行,并作为当前行
--5.absolute n:n>0时,从第一行开始,移动n行,作为当前行,n<0时,从最后一行倒数n行,作为当前行。
--6.relative n:n>0时,从当前行正数移动n行,作为当前行,n<0时,从当前行倒数n行,作为当前行。
--7.打开游标后第一次执行fetch next可以得到表中第一条数据,执行fetch prior得不到任何数据
--8.可用@@fetch_status返回执行fetch操作后游标的状态:
---- 0表示成功读取。
---- -1表示读取操作超出结果集。
---- -2表示行在表中不存在。
-------------------------------------------------------------------------------------------------------
--修改数据,将数据表中的第2行数据的姓名改成“张三”
select * from student
declare c_stu cursor for select * from student
open c_stu
fetch c_stu
fetch c_stu
update student set sname = ‘张三‘ where current of c_stu
close c_stu
deallocate c_stu
------------------------------------------------
--update student set sname = ‘张三‘ where current of c_stu
--如果去掉where current of c_stu,不进行指定,将更新表中所有字段
------------------------------------------------
--删除数据,将表中第2行数据删除
select * from student
declare c_stu cursor for select * from student
open c_stu
fetch c_stu
fetch c_stu
delete from student where current of c_stu
close c_stu
deallocate c_stu
函数
/*--------------------------------------
自定义函数的种类:
√ 1.标量函数
√ 2.内嵌表函数
3.多语句表值函数
---------------------
标量函数基本语法
create function 函数名
returns 返回的参数类型 as
begin
函数体
return 函数返回的标量值
end
--------------------------
内嵌表函数语法
create function 函数名
returns table as
return (select查询语句)
-------------------------------------*/
/*----------------------------------------
注意:函数是可以带参数的,但是参数不可以是:
1.时间戳(timestamp)
2.游标(cursor)
3.表(table)
----------------------------------------*/
/*------------------------------------------------------------------------
标量函数
create function chinacode(@str varchar(255))
returns char(2) as
begin
declare @i int,@j int
set @i = len(@str)
set @j = 1
while (@j<=@i)
begin
if(unicode(substring(@str,@j,1))<256)
return ‘否‘
set @j = @j + 1
end
return ‘是‘
end
select dbo.chinacode(‘我是中国人‘)
select dbo.chinacode(‘我,是中国人‘)
select dbo.chinacode(‘ilovechina‘)
-------------------------------------------------------
substring(expression , start , length ) 字符串截取函数
expression :字符串
start :是一个整数,表示截取的位置
length :是一个整数,表示一次截取的长度
-------------------------------------------------------
-------------------------------------------------------------------------*/
/*------------------------------------------------------------------------
内嵌表函数
-------------------------------------------------------------------------
1.内嵌表
create function age(@maxage int,@minage int)
returns table as
return(select * from tbl_stu where stuage < @maxage and stuage > @minage)
select * from age(13,11)
------------------------------------------------------------------------
2.内嵌视图
create function grade(@ss float)
returns table as
return(select * from stu_cj_view where sumscore > @ss)
select * from grade(200)
-------------------------------------------------------------------------*/
/*---------------------------------------
修改函数
把create function写成alter function
删除函数
drop function 函数名
----------------------------------------*/