1123

SQL —-查询语句

sql SERVER 和.net结合的和很好

绝大多数数据库，都需要数据库服务器才能运行

表间关联，外键
SQL Server数据库中不同数据类型的含义
数据类型 含义
int 每个数值占用 4字节,-2,147,483,648到2,147,483,647之间的整数
smallint 2个字节 存储范围是-32,768 到 32,767 之间的整数，用来存储限定在特定数值范围内的数据。
tinyint 1个字节 存储范围是0到255 之间的整数，用来存储有限数目的数值。
bigint 8个字节 存储范围是-9,223,372,036,854,775,808到 9,223,372,036,854,775,807之间的整数
bit 值只能是0或1，当输入0以外的其他值时，系统均把它们当1看待
float 浮点型，它是一种近似数值类型，float(n)可储存1-53的可变精度浮点数值
money 货币型 ,8个字节
datetime 储存有效日期范围是1753/1/1~9999/12/31 8字节
smalldatetime 储存有效日期范围是1900/1/1~2079/6/6，精确到分钟。 4 字节
char(m) 定长 字符串
varchar(m) 可变长度字符串 最大为m
nvarchar(m) 国际化可变长度字符串
text 可变长度字符串，最大长度为 231 - 1个字节。
ntext 国际化可变长度字符串，最大长度为 230 - 1个字符。
SQL语句中，字符串用单引号 ，大小写不敏感
SQL主要分为DDL DML

DDL:数据库定义语言 create drop

DML数据库操作语言 select insert update delete
设置主键自增（sql server 2017)

选择dbo.stu —–列—–右键id—-修改——列属性—-修改表示规范 是
主键以及GUID

在一个数据表中除了选择主键自增 作为唯一标识之外，还能选择guid自动生成的字符串做唯一标识符

主键自增的优点，占用空间小，无序开发人员干预，阅读性强，缺点，效率低，数据导入导出缓慢

GUID的优点，效率高，数据导入导出方便，缺点是占用空间大

在开发中唯一标识大部分时间是给电脑看的，导入导出不方便的缺点就能忽略，不过，在实际的开发中还是根据需求去选择响应的方式
设置唯一字段id

设置id的数据类型为uniqueidentifier
在添加的时候需要将id的值设置为 newid()

1
2

SQL语句中output的用法

首先update语句执行了两个操作，先删除，后添加可以在sqlserver中输出 查看

update stu2 set name=‘设置唯一字段3’
output deleted.name,inserted.name
where id=7

1
2
3

这段话的意思是，将id为7 的列的name修改为 ‘设置唯一字段3’，那么会输出，原本的name，以及修改之后的name值
SQL中的参数化操作

declare @id int = 7 //T-sql的语法 定义一个变量
update stu2 set name=‘zzz3’ where id=@id

1
2

模糊查询

语句

select * from stu where name like ‘%设置唯%’ order by id asc

1

order表示排序

like表示模糊查询 % 匹配任意字符串

“%唯一%”：查询表中所有含有“唯一” 字符串的行，

“%唯一”：查询表中以”唯一“字符串结尾的行

“唯一%”：查询表中以“唯一”字符串开头的行
SQL语句支持运算
数据分组 group by（如果有where，groupby必须在where之后）

select name,Count(*) from Stu2 group by name

1

这句话的意思是：查stu2表中name字段，并且按照name相同的列 进行分成一组。

max()取最大 avg（）取平均
聚合函数不能出现在where语句中要用having
having 是对分组后信息的过滤。不可代替where

select name,Count() from Stu2 group by name having Count()>1

1

TOP的用法

查询前三名

select top 3 from stu order by sclary desc

1

从id=6开始查询3条数据（6，7，8）

这个涉及到子查询

select top 3 * from stu
where id not in (select top 5 from stu order by id desc)
order by id desc

1
2
3

查询的时候去重(distinct)

select distinct name from stu

1

select distinct name,age from stu //这段删除的是name，age完全重复的列

1

把两个查询结果合并为一个查询结果（union）

select name from stu
union
select name from stu1

1
2
3

函数
数字函数
函数名 含义
abs() 绝对值
ceiling() 向上取整
floor() 向下取整
round() 四舍五入
字符串函数
函数名 含义
len() 计算字符串长度
lower() upper() 转换大小写
ltrim() 去掉左边的空格
rtrim() 去掉右边的空格
substring(string,start_position,length) string字符串，start_position开始位置，length()长度

select LTRIM(’ bb ‘),RTRIM(’ bb ')

//去掉左右两边 病统计长度
select LEN(LTRIM(RTRIM(’ ACV ')))

1
2
3
4

日期函数
函数 含义
getdate() 获取当前日期
类型转换
函数 含义
cast() 类型转化
convert() 类型转化

select cast(‘123456’ as int) //转化成数字
select cast(‘2018-08-08’ as datetime) //转成日期

convert(datetime,‘2018-08-08’)
convert(varchar(50),‘1223’)

1
2
3
4
5

空值处理函数
函数 含义
ISNULL(exp,value) exp不是空返回exp,否则返回value

select ISNULL(fNAME,‘OOO’) AS XINGMING FROM stu

1

case()函数

case i
when 1 then one
when 2 then two
else ‘nonono’
end

1
2
3
4
5

select name,(
case level
when 1 then ‘游客’
when 2 then ‘会员’
else ‘未知客户类型’
end
) as 等级 from person

//范围值的判断
select name,(
case
when level<= 2 then ‘游客’
when level<5 and level>=2 then ‘会员’
else ‘未知客户类型’
end
) as 等级 from person

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

练习

单号的字段名 order 金额的字段名 money 数据表名为table
select order，
（
case
when money>0 then money
else 0
end
）as 收入，
（
case
when money<0 then abs(money)
else 0
end
）as 支出 from table

1
2
3
4
5
6
7
8
9
10
11
12
13
14

//解题思路 首先用case()函数将声场统计成1，负场统计成0，在group by 分组
select Name,
Sum(
case N’胜’ then 1
else 0
end
) as 胜，
Sum(
case N’负’ then 1
else 0
end
) as 负
from table
group by Name

1
2
3
4
5
6
7
8
9
10
11
12
13
14

索引（目录） ——index

全表扫描：逐条查询数据 —–效率最差

创建目录 经常要查询（检索）的字段 ——-where

能提高查询效率，对写程序没有影响
索引占空间大，插入，删除，修改麻烦，因为需要更新索引

/* 即使创建了索引，仍然有可能全表扫描，比如使用like 函数 类型转换等
表链接Join（inner Join | Left Join | Right Join）

select t1.name,t2.age,t2.sex
from table1 as t1 join table as t2 on ti.cid=t2.pid

1
2

查询年龄大于15的人的name，age，sex

select t1.name,t2.age,t2.sex
from table1 as t1 join table as t2 on ti.cid=t2.pid
where t2.age > 15

1
2
3

查询年龄大于平均年龄的的人的name age sex

select t1.name,t2.age,t2.sex
from table1 as t1 join table as t2 on ti.cid=t2.pid
where t2.age > (select avg(age) from table2)

1
2
3

子查询

select t1.name,t2.age,t2.sex
from table1 as t1 join table as t2 on ti.cid=t2.pid
where t2.age > (select avg(age) from table2)

1
2
3

ROW_NUMBER() over()—排序（开窗函数）

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by order by 的执行。

select name,age,sex,ROW_NUMBER() OVER(order by age desc) as 0;

1

rank() over() 是跳跃排序 有两个第二名时接下来就是第四名（同样是在各个分组内）．
dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。