SQL Servler 快速入门

约定不等于承诺〃 2022-05-22 10:26 156阅读 0赞

## SQL Servler 快速入门 ##

--------------------

\#\#\#\#都是些常用的

##### 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]%'表示找不是以大写母开头的字符串

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

1.  算数运算符

+ 加|
    - 减
    * 乘|
    / 除 (除法和c语言一样)
    % 取模|

1.  逻辑运算符

> 大于|
    < 小于
    = 等于|
    >= 大于等于
    <= 小于等于|
    <> 不等于

1.  其他  
    (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.  内连接 (进行表与表之间的笛卡尔积)  
    (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.  时间函数 (常用) 帮助文档 里 更多

(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

1.  数学函数 F1
2.  其他函数

(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

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

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

1.  事务

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

1.  视图–将select的结果集变成一张虚拟表,课直接使用,增删改结果影响原表

create view 视图名
    as
    select语句
    go

1.  索引

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

1.  存储过程

模板:

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)

格式:

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