都是些常用的
sql 语句
增加语句
(1)insert into <表名>([列名,列名])
values (对应列的值, 对应列的值)
其中若值为NULL 则用default 填充, 字符串用'' int不用 该语句不能多行输入
(2)insert into <表名> (列名, 列名)
select 对应值, 对应值 union
select 对应值, 对应值 union
select 对应值, 对应值
该行可以一次性多行输入. 不能输入空值
(3)insert into <表名1> (列名, 列名)
select 列名, 列名 from <表名2>
从表2 中选出 两列值添加到表1 的两列中 表1必须是已经存在的表
(4)select 列名, 列名 into 表名1 from 表名2
新建一个表1, 将表二的两列添加到这个新表中. 表1必须是不存在的表
若要重复执行该语句应该把表1删除了
drop table 表1 删除该表
表1 前面加一个# 代表者新建临时表
2.更新语句
(1) update 表名 set 列名=.., 列名2 = ...
where 条件 如果条件为真则对列进行操作否则不操作
判断是否为空值
where 列 is NULL or 列=''
3.删除语句
(1)delete from 表名
where 条件
4.查询语句
全句:select 列名 from 表名 where 条件 group by 分组 having 再次筛选 order by 排序 (asc&desc)
5.模糊搜索 (当条件搜索的列中有NULL则跳过对应行)
列名 like '(三个通配符 %, _, [],)加内容'
% :表示位数任意 字符任意
eg: '许% ' 表示找到以许开头的字符串 许后续有多少位都没关系
'%@% ' 表示找内容有 @ 的字符串
_ :表示一个任意字符
eg: '许_' 表示找两位字符组成的字符串 开头是 许
[]:表示 取一个范围 只表示一位
eg: '[A-Z]%'表示找以大写字母开头的字符串
'[^A-Z]%'表示找不是以大写母开头的字符串
条件运算符
(1) between …. and …..
eg: 列名 between 起始值 and 终止值 (值可以是时间和数值)
(2) or 或 , and 与 , not 非
注 : and 的优先级 高于or
(3) IS NULL & NOT IS NLL
注 : 判断是否为空
(4) in (合法值, 合法值…)
eg: 列名 in (1,5) 表示这个列 要等于 1 或者 5 两个值
算数运算符
+ 加|
- 减
* 乘|
/ 除 (除法和c语言一样)
% 取模|
逻辑运算符
> 大于|
< 小于
= 等于|
>= 大于等于
<= 小于等于|
<> 不等于
其他
(1) top 行数
eg: select top 5 列名 from 表名 显示找出的所有记录的前五条记录
(2) as 别名
eg: select 列名 as 别名 from 表名 as 别名
给想要附上别名的列附别名. 表也可以 别名只有本个select语句中生效
(3)列链接符 +
eg: select 列名+列名+'字符串' from 表名 + 号可以连接起前后两个列 或者字符串(常量列)
10.聚合函数
(1) count (* || 列名)计算一共有多少行 包括 null
(2) sum (列名) 计算这一列数值的总和 (空值NULL跳过)
(3) avg (列名) 计算这一列的数值的平均值 (空值NULL跳过)
(4) max (列名)||min (列名)找出这一列的最大或则最小值
注: 聚合函数一般与group by 一起使用
eg: select 列名1,列名2, ...count (*) from 表名 group by 列名1, 列名2..
select 中有点列名 group by 中一定要有 只能多不能少
内连接 (进行表与表之间的笛卡尔积)
(1)inner join
eg : select 表名1.列名1, 表名2.列名2 ...
from 表名3 (一般是主表 既主键)
inner join 表4 (外键) on 表4.列名 = 表3,列名
inner join 表5 (外键) on 表5.列名 = 表.列名 注: 这表示表5和上面连接后的表连接
这里的表名都是乱填的 看情况连接
(2)from 多个表
eg: select 表名1.列名, 表名2.列名...
from 表1 , 表2
where 表1.列名= 表2.列名
12.外连接 (格式和inner join 一样)
(1) left join 关键字的左边表为主表 注:如果主表有23行则连接后的表也为23行
(2) right join关键字的右边表为主表 注:...............................
时间函数 (常用) 帮助文档 里 更多
(1)时间缩写 year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
Hour Hh
Minute mi、n
Second ss, s
(2)DATEPART ()返回一个具体日期的日期部分的整数。
格式: DATEPART (要返回的部分,'具体时间')
eg : DATEPART (YY, '1994-1-5')返回1994
(3)DATEADD () 返回将指定时间加上具体时间后的时间
格式: DATEADD (要加的部分, 要加多少, '具体时间')
eg: DATEADD (YY, 6, '1994-1-5') 返回:2000-1-5...
(4)DATEDIFF ()返回两个时间时间的差
格式: DATEDIFF (要返回的部分, '时间1', '时间2') 时间2-时间1 单位为 要返回的部分
eg: DATEDIFF (MM, '1994-1-1', '1995-1-1')返回:12
14.字符串函数(常用) F1 (字符串函数 (Transact-SQL))看帮助文档
(1)CHARINDEX ()在一个字符串中寻找另一串字符的位置并返回它的位置
格式: CHARINDEX ('要找的字符串', '被找的字符串', 开始寻找的位置)
eg: CHARINDEX ('EF','AB C DEFGH', 2)返回在字符串中的绝对位置 7
CHARINDEX ('EF','AB C DEFGH', 3)2和3都是表示开始寻找的位置还是返回 7
(2)SUBSTRING ()将字符串截取一部分出来并返回
格式: SUBSTRING ('字符串', 起始位置, 截取位数)
eg: SUBSTRING ('ABCDEFGH', 2, 4)返回 BCDE
SUBSTRING ('ABCDEFGH', 3, 4)返回 CDEF
(3)LEN () 返回指定字符串表达式的字符数,其中不包含尾随空格
格式: LEN ('要计算位数的字符串')
eg: LEN ('ABCDEF')返回 6
(4)LEFT () 返回字符串中从左边开始指定个数的字符
格式: LEFT ('字符串', 要返回的位数)
eg: LEFT ('ABCDEFGH', 3)返回 ABC
(5)LOWER () 将大写字符数据转换为小写字符数据后返回字符表达式。
UPPER () 将小写字符数据转换为大写字符数据后返回字符表达式。
eg: LOWER ('ABCDEF')返回 abcdef
UPPER ('abcdef')返回 ABCDEF
(6)LTRIM () 删除字符串的前导空格
RTRIM () 删除字符串的尾随空格
格式: LTRIM ('字符串')
RTRIM ('字符串')
(7) RIGHT () 同理LEFT ()
(8) REPLACE ()
用另一个字符串值替换出现的所有指定字符串值
格式: REPLACE ('具体字符串', '将被替换的部分', '用来替换的部分')
eg: REPLACE ('abcdefghicde','cde','xxx')返回 abxxxfghixxx
(9) STUFF ()
将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符;然后将第二个字符串插入第一个字符串的开始位置。
格式: STUFF ('字符串1', 开始位置, 删除的位数, '字符串2')
eg: STUFF('abcdef', 2, 3, 'ijklmn')返回 aijklmnef
数学函数 F1
其他函数
(1) CONVERT ()将一种数据类型的表达式转换为另一种数据类型的表达式 格式: CONVERT (类型, 要被转换的表达式) 注: 类型见 F1 帮助文档 CONVERT函数
进阶
建库
(1)先查看要创建的数据库是否存在和开启xp_cmdshell
use master
go
if exists (select * from sysdatabases where name = '数据库名')
drop database 数据库名
exec sp_configure 'show advanced options',1--0|关闭;1|打开
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec xp_cmdshell 'mkdir d:\sqldata'--表示在d:\下建一个sqldata文件,rmdir表示删除
reconfigure
go
–开始建库
create database 数据库名
on [primary] --primary定义主文件可省略
(
name = '数据文件名',--逻辑名
filename = 'D:\SqlData\数据文件名.mdf',--物理名
size = 5mb, --初始大小
maxsize = 100mb,--增长最大值
filegrowth = 15%--增长率
),
() --要是还有数据文件可逗号然后括号继续如上添加
log on --建日志文件
(
name = '日志文件名',--逻辑名
filename = 'D:\SqlData\数据文件名.ldf',--物理名
size = 1mb, --初始大小
maxsize = 10mb,--增长最大值
filegrowth = 1--增长率
),
() --要是还有日志文件可逗号然后括号继续如上添加
go --建完要注意go一下 才能引用
(2)库级操作
execute sp_helpdb--省略数据库名则查询所有数据库信息
go
execute sp_helpdb DBONE--查询数据库DB1的信息
go
–更新数据库名称
execute sp_renamedb DB1,DBone--将DB1改为DBONE
GO
SELECT * FROM sys.databases where name='DBONE'--查询DBONED 的信息.或者用sp_helpdb
go
–增加数据库的数据文件 alter database 数据库名
alter database DBONE
add file
(
name=DB11_data,
filename='E:\SQLDB\DB11_data.mdf',
size=3,
maxsize=8,
filegrowth=10%
)
go
–增加数据库的日志文件
alter database DBONE
add log file
(
name=DB11_log,--没咧出来的项 系统赋默认值
filename='E:\SQLDB\DB11_log.ldf',
filegrowth=10%,
size=2
)
GO
–修改数据库文件
alter database DBONE
modify file
(
name=DB1_data,--指定被要修改的文件
size=7, --要修改的值
maxsize=15
)
go
–同理修改日志文件
alter database DBONE
modify file
(
name=DB1_log,
size=3
)
go
–删除数据库文件
alter database DBONE
remove file DB11_log
建表
(1)约束constraint
NOT NULL | NULL:不允许为空和允许为空
PRIMARY KEY:主键,不能为空,且唯一,
UNIQUE:值唯一,允许一个空值
REFERENCES:外键
CHECK:取值范围
identity(1,1):自增
default('默认值'):默认值
(2)常用的表
sysdatabasses :所有的数据库信息
sysobjects :所有表信息(约束,视图,索引)
syscolums :字段信息
systypes : 对于每种系统提供数据类型和用户定义数据类型,均包含一行信息.
sysindexes :索引信息
(3)建表步骤
–先判断表存不存在
if exists (select * from sysobjects where name = '表名')
drop table 表名
–创建局部临时数据库表在表名前面加#,如:create table #table1
–创建全局临时数据库表在表名前面加##,如:create table ##table1
create table 表名
(
列名 类型 各种约束 --约束之间没有区分顺序以空格隔开,
列名 类型 各种约束 --约束之间没有区分顺序以空格隔开
)
go
example:
create table table1
(
StoreHouseID int constraint pk_SH1 PRIMARY KEY,--constraint 约束名,可省略如:StoreHouseID int PRIMARY KEY,
StoreHouseND varchar(50) constraint uk_SH1 UNIQUE,
City varchar(50) NOT NULL,
--City varchar(50) default '上海'--这里省略了constraint
Area int constraint ck_A1 CHECK (Area>600 and Area<1800)
)
go
create table table3
(
StaffID int primary key,
StaffND varchar(50) unique,
StoreHouseND varchar(50),
Salary int
)
go
create table table4
(
OrderID int primary key,
OrderND varchar(50) unique,
StaffND varchar(50) references table3(StaffND),
--references后面为主表,要先建主表才能建附表,然后关联
--table3的staffND为主键 table4的为外键
PayData datetime,
PayMoney int
)
go
–创建含有计算字段的表
create table table5
(
StaffID int identity(1,1) primary key,
--自编号列,identity(1,1) 从1开始每次加1
StaffND varchar(50) unique,
StoreHouseND varchar(50),
基本工资 int default 900 check(基本工资>800 and 基本工资<2000),
--默认值:default 值
加班工资 int,
奖金 int,
扣率 int,
应发工资 as(基本工资+加班工资+奖金-扣率)--计算字段
)
go
(4)表外添加约束
alter table 表名
add constraint 约束名 约束
example :
alter table table1--给table1 添加主键组StoreHouseID, StoreHouseND
add constraint pk_StoreHouseID primary key (StoreHouseID, StoreHouseND)
alter table table1
--给table15 添加默认值
add constraint dk_基本工资 default (900) for 基本工资
alter table Student--添加外键foreign key (外键) references 主表 (主键),
add constraint FK_Student_GradeId foreign key (GradeId) references Grade (GradeId)
alter table table5
add constraint CK_基本工资 check(基本工资>800 and 基本工资<2000)
(5)表级操作
–查看表信息
–sp_help 方式查看
execute sp_help --省略表名,查看所有表的基础信息
go
execute sp_help table1 --查看table1表的具体信息
go
–select 方式查看
select * from sysobjects where type='U'--查看所有表的基础信息
–重命名表
–格式:execute sp_rename ‘oldname’,’newname’
execute sp_rename 'table11','table1'
execute sp_help table2
–增加表的列, 可以附带各种约束
–格式: alter table 表名 add 列名 类型 约束
alter table table1 add Email varchar(50) not null check (Email='1')
go
execute sp_help table1
–在SQL sever 中可以修改非空列的大小
–有约束或者默认值的列不能修改
–要指明哪个表,哪个列
alter table table1
alter column Email varchar(100)
go
execute sp_help table1
–删除列,指定表,指定列
– 要是该列有约束就要先删除约束 如:主外键.ck.pk.uk 才能删掉
–先删除列的约束
alter table table1
drop constraint CK__table1__Email__1920BF5C
go
–在删除列
alter table table1
drop column Email
go
T-SQL 编程
(1) 变量
变量定义 : declare @变量名1 类型, @变量名2 类型
赋值 : set @变量名1 = 值 --set 只能给单一变量赋值
select @变量名1 = 值, @变量名2 = 值--可以多个变量赋值
select @变量名1 = 列名 from 表
–这样到这列的最后一个值(重复赋值,所以要确保只有一条)
当表达式返回多个值时: set 出错
select 将最后一个值赋给变量
当表达式未返回值时: set 变量被赋null值 select 变量保持原值
常用全局变量:
@@ERROR最后一个错误的错误代码
@@IDENTITY 最后一次插入的主键值
@@servername 本地服务器的名称
@@version SQL Server的版本信息
(2)流程控制
条件: if ()
begin
end
else
bengin
end
循环:
while ()
begin
break --跳出循环
end
(用在查询的时候)多分支:
case
when 条件1 then 结果1
when 条件2 then 结果2
.....
else 其他结果
end
事务
begin tran --开始事务
rollback tran --回滚事务
commit tran --提交事务
example:
BEGIN tran --转账业务
declare @error int,@debt numeric(18,2)
set @error = 0
set @debt = 800
UPDATE Account SET accountMoney= accountMoney-@debt
WHERE accountNo = '001'
SET @error = @error+@@error
UPDATE Account SET accountMoney= accountMoney+@debt
WHERE accountNo = '002'
SET @error = @error+@@error
if @error>0
rollback tran
else
commit tran
SELECT * FROM Account
视图–将select的结果集变成一张虚拟表,课直接使用,增删改结果影响原表
create view 视图名
as
select语句
go
索引
unique 唯一索引
primary 主键索引
clustered 聚集索引
non-clustered 非聚集索引
(1)建索引
if exists (select * from sysindexes where name = '索引名' )
drop index 表名.索引名
create 索引类型 index 索引名
on table_name (列名)--在那一列建
[with fillfactor = x]--填充因子:指定0-100之间,表示索引页填充的百分比
(2)使用索引查询
select * from student
with (index = 索引名)
where studentname like '李%'
表示用这个索引查找姓李的学生
存储过程
模板:
CREATE proc sp_name1--命名sp_开头 关键字 proc
(
@参数3 参数类型 output,
@参数5 参数类型 = 默认,
@参数1 参数类型, --形参列表 默认input输入参数 output为输出参数
@参数2 参数类型
@参数4 参数类型 = 默认
)
as
--这里填操作语句
GO
调用:exec sp_name1 @参数3 output, default, @参数1, @参数2
注意事项:
(1)输出参数和输入参数的引用一样
(2)输出参数也可以输入
(3)输出形参也可以带默认值
(4)输出参数一般放置在所有参数之前
(5)return 结束存储过程
(6)raiserror ('提示信息',错误级别,状态)
(7)指定参数赋值
(8)默认参数放在所有参数之后
(9)一般结合事务使用,事务嵌套存储过程
8.触发器
注意:
(1)inserted 表 和 deleted 表
(2)增加时先添加到inserted表,删除时先把要删除的放在deleted表
(3)修改是把原来的删掉,在插入新的,所以原来的在deleted表要插入的在inserted表
(4)after 表示已经插入后触发,instead of 表示还没插入的时候触发 都放在inserted表和deleted表
(5)触发器是更高级的事务
(6)rollback tran 回滚事务 有问题就回滚
(7)instead of 成功的话要再次执行一次操作一次而且要用变量
insert into score (sno,cno,grade) values (@sno,@cno,@grade)
格式:
create trigger 触发器名
on 表名
[after | instead of] [insert | delete | update]
as
--执行内容
go
或者
create trigger 触发器名
on 表名
for [insert | delete | update] --默认after
as
--执行内容
go
9.游标
格式:
declare @变量1 类型, @变量 类型 ....
声明一些变量来传递值
declare 游标名 cursor for
声明游标
select 列1, 列2 from 表 where ...指定在哪个结果集里遍历
open 游标名 打开游标
fetch next from 游标名第一次将游标指向下一个,也就是第一条记录
into @变量1,@变量2
while @@fetch_status = 0当@@fetch_status = 0 时表示遍历完了
begin
操作语句
fetch next from 游标名循环将游标指向吓一条记录
into @变量1,@变量2
end
close 游标名 关闭游标
dealllocate 游标名删除游标
---------------------
|
|