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