SQL server学习（二）表结构操作、SQL函数、高级查询
数据库查询的基本格式为：
select ----输出（显示）你要查询出来的值 from -----查询的依据 where -----筛选条件（对依据（数据库中存在的表）） group by -----对筛选后的数据 进行分组 having ----- 筛选条件（对分组后的结果再次筛选） order by -----将结果进行排序
注意：基本语法格式顺序不能变，顺序不能变，但可以缺省
表结构操作
表结构操作是需要去修改表结构，使用的关键字是alter，其他方式无法对表进行操作的时候，比如，新增列，删除列，修改列类型等操作，就需要使用alter方法了。
新增列
alter table stu add sid int
删除列
alter table stu
drop column sid
修改列类型
alter table stu alter column sid char(1)
修改列类型需要注意的是：如果列中有数据，则不能修改该列数据类型
新增约束
alter table stu
add constraint ck_ssex check (ssex=0 or ssex=1)
新增两列
alter table stu
add siphone nchar(11)，smoney money
删除约束
alter table stu
drop ck_ssex
SQL函数
SQL函数分为五类，字符串函数，日期函数，数学函数，系统函数，聚合函数
字符串函数如下表所示：
|
函数名 |
描述 |
举例 |
|
charindex |
用来寻找一个指定的字符串在另一个字符串中的起始位置 |
select charindex('apple','this is apple',1)返回值为：9 |
|
len |
返回传递给它的字符串长度 |
select len('yihuqingjiu') 返回值为：11 |
|
lower |
把传递给它的字符串转换为小写 |
select lower('YIHUQINGJIU') 返回值为：yihuqingjiu |
|
upper |
把传递给它的字符串转换为大写 |
select upper('yihuqingjiu') 返回值为：YIHUQINGJIU |
|
ltrim |
清除字符左边的空格 |
select ltrim(' yihuqingjiu ') 返回值为：yihuqingjiu ，左边空格被清除 |
|
rtrim |
清除字符串右边的空格 |
select rtrim('yihuqingjiu ') 返回值为： yihuqingjiu，右边空格被清除 |
|
right |
从字符串右边返回指定数目的字符 |
select right('yihuqingjiu',3) 返回值为：jiu |
|
replace |
替换一个字符串中的字符 |
select replace('yihuqingjiu','yi','wen') 返回值为：wenhuqingjiu |
|
stuff |
在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串 |
select stuff('yihuqingjiu',3,6,'wen') 返回值为：yiwenjiu，3,6指的是，以第三个字符开始，往后6个字符都删除 |
日期函数如下表所示：
|
函数名 |
描述 |
举例 |
|
getdate |
获取当前的系统日期 |
select getdate() 返回值为：当前的时间日期 |
|
dateadd |
将指定的数值添加到指定的日期部分后的日期 |
select dateadd(dd,4,'17/6/17') 返回值为：2017-06-21，年为yy，月为mm，日为dd，所以上述代码在日为17的基础上加4 |
|
datediff |
两个日期之间的指定日期部分的区别 |
select datediff(mm,'17/8/15','17/10/15') 返回值为：2，月份相差2，年月日表示方法同上 |
|
datename |
日期中指定日期部分的字符串形式 |
select datename(dw,'17/10/7') 返回值为：星期六 |
|
datepart |
日期中指定日期部分的整数形式 |
select datepart(day,'17/10/7') 返回值为：7 |
日期参数及缩写如下表所示：
|
日期部分 |
缩写 |
|
year |
yy, yyyy |
|
quarter（表季度，返回1-4） |
qq, q |
|
month |
mm, m |
|
dayofyear（在当前时间的多少天） |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
数学函数如下表所示：
|
函数名 |
描述 |
举例 |
|
abs |
取数值表达式的绝对值 |
select abs(-1025) 返回值为：1025 |
|
ceiling |
返回大于或等于所给数字表达式的最小整数 |
select ceiling(28.9) 返回值为：29 |
|
floor |
取小于或等于指定表达式的最大整数 |
select floor(28.9) 返回值为：28 |
|
power |
取数值表达式的幂值 |
select power(8,2) 返回值为：64 |
|
round |
将数值表达式四舍五入为指定精度 |
select round(36.55789,3) 返回值为：36.55800 |
|
sign |
对于正数返回1，负数返回-1,0则返回0 |
select sign(36) 返回值为：1 |
|
sqrt |
取浮点表达式的平方根 |
select sqrt(16) 返回值为：4 |
系统函数如下表所示：
|
函数名 |
描述 |
举例 |
|
convert |
用来转变数据类型 |
select convert(nchar(3),123) 返回值为：123 |
|
current_user |
返回当前用户的名字 |
select current_user 返回值为：用户分组名，dbo或guest |
|
datalength |
返回用于指定表达式的字节数 |
select datalength('用户分组名dbo') 返回值为：13，汉字占两个字节 |
|
host_name |
返回当前用户所登录的计算机名字 |
select host_name() 返回值为：所登录的计算机名 |
|
system_user |
返回当前所登录的用户名称 |
select system_user 返回值为：当前登录名，比如sa |
|
user_name |
从给定的用户ID放回用户名 |
select user_name(1) 返回值为：dbo，括号中数字不同返回的数据也不同 |
聚合函数如下所示：
min ---最小 max ---最大 sum ---和 avg ---平均 count ---统计（个数）
需要注意的是：聚合函数需要和分组group by一起使用
例：
select avg（成绩）
from student
group by 科目
高级查询
查询中where与having的区别：
本质上没有区别，都是对数据做筛选，只不过 where对原始表中的数据筛选，而having是对分完组后的数据筛选
去重复的关键字是distinct
例：
select distinct depart
from teacher
多表查询
多表查询需要将表连接在一张表中做查询操作，连接表有如下几种方式
1.全连接（笛卡尔连接）
2.左连接
3.右连接
4.内连接
第一种连接方式语法为：
select *
from 表1，表2
where 表1.列=表2.列
第二种连接方式的语法为：
select *
from 表1 left join 表2 on 表1.列=表2.列
第三种连接方式的语法为：
select *
from 表1 right join 表2 on 表1.列=表2.列
第四种连接方式的额语法为：
select *
from 表1 inner join 表2 on 表1.列=表2.列
子查询
子查询分为四种，where子查询、from子查询、in子查询和exists子查询
where子查询是将一个查询的结果作为另一个查询的条件
from子查询是将一个查询的结果作为另一个查询的来源（表）
in子查询是指要查询的数据包含其中
exists子查询是判断某数据是否存在
where子查询例：
select stuName
from stuInfo
where stuNo=(select stuNo from stuMarks where writtenExam=60)
where条件查询：
a. 比较运算符 = ，!=（<>），< > <= >=
b. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符)
c. in , not in , between and
in,匹配多个数据
like,模糊查询，对字符、字符串使用
shop_price between 1000 and 3000
d. is null , is not null
通配符如下表所示：
|
通配符 |
解释 |
示例 |
|
‘_’ |
一个字符 |
A Like 'C_' |
|
% |
任意长度的字符串 |
B Like 'CO_%' |
|
[ ] |
括号中所指定范围内的一个字符 |
C Like '9W0[1-2]' |
|
[^] |
不在括号中所指定范围内的一个字符 |
D Like ‘%[A-D][^1-2]' |
from子查询例：
select * from (select * from score,grade where degree>low and degree< upp) as a where rank='A'
in子查询例：
select stuName
from stuInfo
where stuNo in (select stuNo from stuMarks)
exists子查询例：
if exists(select * from sys.databases where name='new') drop database new
其中sys.databases是指系统中所有的数据库，exists返回值是true或false
一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换。子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据，表连接更适合于查看多表的数据。
排序
排序分为降序和升序，很简单，看个例子就明白。
升序例：
select *
from student
order by sno asc
降序例：
select *
from student
order by sno desc
按多列排序：
select *
from student
order by sno asc，scj asc
排序需要注意的是，若排序字段没有name，在select显示是输出不了的。
限制固定行数显示：
select top 5 sname，saddress
from student
where ssex=0
也可以用*代表所有列
返回百分之多少行：
select top 20 percent sname，saddress
from student
where ssex=0
