SQLServer数据库快速入门学习笔记(基础)-职坐标

SQLServer数据库快速入门学习笔记(基础)

小标 2018-07-18 来源：阅读 1034 评论 0

摘要：本文主要向大家介绍了SQLServer数据库快速入门学习笔记(基础)，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQLServer数据库快速入门学习笔记(基础)，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

以下是我刚开始学习sql过程中自己写的一些笔记 ,希望对刚开始入门的朋友有点帮助

内容比较杂大家见谅

sql 语句

1.增加语句
(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]%'表示找不是以大写母开头的字符串

6. 条件运算符
(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 两个值

7. 算数运算符
+ 加| - 减
* 乘| / 除 (除法和c语言一样)
% 取模|

8. 逻辑运算符
> 大于| < 小于
= 等于| >= 大于等于
<= 小于等于| <> 不等于

9. 其他
(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 中一定要有只能多不能少

11. 内连接 (进行表与表之间的笛卡尔积)
(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关键字的右边表为主表注:...............................

13. 时间函数 (常用) 帮助文档里更多
(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

16. 数学函数 F1

15. 其他函数
(1) CONVERT ()将一种数据类型的表达式转换为另一种数据类型的表达式
格式: CONVERT (类型, 要被转换的表达式)
注: 类型见  F1 帮助文档 CONVERT函数

进阶

1. 建库
(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

2. 建表
(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

3. 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

4. 事务
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

5. 视图--将select的结果集变成一张虚拟表,课直接使用,增删改结果影响原表
create view 视图名
as
select语句
go

6. 索引
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 '李%'
表示用这个索引查找姓李的学生

7. 存储过程

模板:
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 游标名删除游标