黑马程序员技术交流社区

标题: sql快速入门 [打印本页]

---

作者: 1192160204    时间: 2019-4-4 14:41
标题: sql快速入门

都是些常用的
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 游标名删除游标
---------------------

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2