SQL server 基础操作总结
1创建和操纵表
1.1创建表
语法:
create table table_name(
字段名称1 字段类型 [default 默认值] [not null],--非空存在默认值
字段名称2 字段类型 [primary key],--主键
字段名称3 字段类型 [foreign key references table_name(字段名称)],--外键
字段名称4 字段类型 check(限制条件)--设置有约束
)
示例:
create table 学生表(
学号 nchar(5) primary key,
性别 nchar(1) check(性别='男' or 性别='女'),
班级号 ncahr(3) foreign key references 班级表(班级号),
爱好 nvarchar(100) default '无'
)
1.2更新表
1.2.1添加列
语法:
alter table table_name
add 字段名 字段类型
示例:
alter table 学生表
add 班级号 nchar(3)
1.2.2删除列
语法:
alter table table_name
drop column 字段名
示例:
alter table 学生表
drop column 备注
1.3删除表
语法:
drop table table_name
示例:
drop table 学生表
1.4重命名表
后面再补这个知识点
2检索数据
2.1检索单个列
语法:
select 字段名 from table_name
示例:
select 姓名 from 学生表
2.2检索多个列
语法:
select 字段名1,字段名2 from table_name
示例:
select 姓名,性别 from 学生表
2.3检索所有列
语法:
select * from table_name
示例:
select * from 学生表
2.4检索不同的值
语法:
select distinct 字段名 from table_name
示例:
select distinct 性别 from 学生表
2.5限制结果
语法:
返回前百分之n
select top n percent 姓名
from table_name
返回前n个
select top n 字段名
from table_name
示例:
返回前百分之30
select top 30 percent 姓名
from 学生表
返回前三个
select top 3 姓名
from 学生表
3排序检索数据
3.1排序数据
语法:
select 字段名 from table_name order by 字段名
示例:
select 身高 from 学生表 order by 身高
3.2按多个列排序
语法:
select 字段名1,字段名2 from table_name order by 字段名1,字段名2
示例:
select 字段名1,字段名2,字段名3 from 学生表 order by 3,2
3.3按列位置排序
语法:
select 姓名,身高,团员 from 学生表 order by 3,2
示例:
select 姓名,身高,团员 from 学生表 order by 3,2
3.4指定排序方向
3.4.1单个列排列
语法:
--升序排列
select 字段名 from table_name order by 字段名 asc
--降序排列
select 字段名 from table_name order by 字段名 desc
示例:
--升序排列
select 身高 from 学生表 order by 身高 asc
--降序排列
select 身高 from 学生表 order by 身高 desc
3.4.2多个列排列
语法:
--多个列排序
select 字段名1,字段名2 from table_name order by 字段名1 desc,字段名2
示例:
--多个列排序
select 团员,身高 from 学生表 order by 团员 desc,身高
4过滤数据
4.1使用where子句
语法:
select 字段名1,字段名2 from table_name where 限制条件
示例:
select *from 学生表 where 性别 = '男'
4.2where子句操作符
4.2.1检查单个值
语法:
select 字段名1,字段名2 from table_name where 限制条件
示例:
select *from 学生表 where 身高 > 1.60
4.2.2不匹配检查
语法:
select 字段名1,字段名2 from 学生表 where 字段名 <> 某个值
!= 和 <> 通常可以互换,但是并不是所有的DBMS都支持这两种不等于操作符。
示例:
列出不是团员的所有学生信息
select *from 学生表 where 团员 <> 1
4.2.3范围值检查
语法:
在where子句中使用between…and…
between匹配范围中所有的值,包括指定的开始值和结束值
示例:
查找身高在1.60~1.70之间的学生信息
select *from 学生表 where 身高 between 1.60 and 1.70
4.2.4空值检查
语法:
当一个列不包含值时,称其包含空值NULL
示例:
使用select语句的is null子句来检查空值
select *from 学生表 where 性别 is null
5高级数据过滤
5.1组合where子句
5.1.1and操作符
语法:
要通过不止一个列进行过滤,可以使用and操作符给where子句附加条件
示例:
select *from 学生表 where 性别 = '男' and 团员 = 1
5.1.2or操作符
语法:
or操作符与and操作符正好相反,它指示DBMS检索匹配任一条件的行。
示例:
select *from 学生表 where 性别 = '男' or 团员 = 1
5.1.3求值顺序
where子句可以包含任意数目的and和or操作符。允许两者结合以进行复杂、高级的过滤。
示例:
select *from 学生表 where (性别 = '女' or 团员 = 1) and 身高 > 1.70
SQL在处理or操作符前,优先处理and操作符,即and在求值过程中优先级更高。
5.2in操作符
语法:
in操作符用来指定条件范围,范围中的每个条件都可以进行匹配
in取一组由逗号分隔、括在圆括号中的合法值。
示例:
select *from 学生表 where 身高 in ('1.60','1.55','1.75')
5.3not 操作符
语法:
where子句的not操作符有且只有一个功能,否定器后所跟的任何条件
not关键字可以用在要过滤的列前,而不是列后。
示例:
select *from 学生表 where not 团员 = 1
6用通配符进行过滤
通配符搜索只能用于文本字段(字符串)
6.1like操作符
语法:
select 字段名1,字段名2 from table_name where 字段名1 like '字符'
6.1.1百分号(%)通配符
在搜索串中,%表示任何字符出现任意次数。
语法:
select 字段名1,字段名2 from table_name where 字段名1 like '%字符'--百分号在字符前
select 字段名1,字段名2 from table_name where 字段名1 like '字符%'--百分号在字符后
select 字段名1,字段名2 from table_name where 字段名1 like '%字符%'--百分号在字符前后均有
select 字段名1,字段名2 from table_name where 字段名1 like '字符%字符'--百分号在字符中间
示例:
select * from 学生表 where 姓名 like '%春'--百分号在字符前
--查找姓名最后一个字是春的同学信息
select * from 学生表 where 姓名 like '杨%'--百分号在字符后
--查找姓名第一个字为杨的同学信息
select * from 学生表 where 姓名 like '%大%'--百分号在字符前后均有
--查找姓名中任何姓名列中包含大的同学信息
select * from 学生表 where 姓名 like '杨%红'--百分号在字符中间
--查找姓名中以杨起头红结尾同学的信息
6.1.2下划线(_)通配符
与%用途类似,但只匹配单个字符
语法:
select 字段名1,字段名2 from table_name where 字段名1 like '_字符'
示例:
select 学号 from 学生表 where 学号 like '31202009710__'
6.1.3方括号([])通配符
语法:
方括号([])通配符用来指定一个字符集,他必须匹配指定位置(通配符的位置)的一个字符
此通配符可以使用前缀字符^(脱字号)来否定。
示例:
select 字段名 from table_name where 字段名 like '[字符]'
select 字段名 from table_name where 字段名 like '[^字符]'
7创建计算字段
7.1计算字段
计算字段并不实际存在于数据库中。
计算字段是运行在select语句内创建的。
只有数据库知道select语句中哪些列时实际的列表,哪些是计算字段。
从客户端来看,计算字段的数据与其他列的数据返回方式相同。
7.2拼接字段
拼接:
将值联结在一起(将一个值附加到了一个值)购车鞥单个值
在SQL中select语句中,可使用一个特殊的操作符(加号(+)或两个竖杠(||))来拼接两个列。
示例:
select 字段名称1 + '('+字段名称2+')'--结合成一个计算字段的两个列用空格填充
from table_name
order by 字段名称
select 字段名称1 + '('+rtrim((字段名称2)+')'--结合成一个计算字段的两个列没有空格
from table_name
order by 字段名称
7.3执行算术计算
对检索出的数据进行算术计算。
SQL支持加减乘除
圆括号可用来区分优先顺序
示例:
select 单价,卖出数量,单价*卖出数量 as营业额 from 销售表
8使用函数处理数据
8.1函数
常用的文本处理函数:
函数 | 说明 |
---|---|
left() | 返回字符串左边的字符 |
length() | 返回字符串长度 |
lower() | 将字符串转换为小写 |
ltrim() | 去掉字符串左边的空格 |
right() | 返回字符串右边的字符 |
rtrim() | 去掉字符串右边的空格 |
substr() 或substring() | 提取字符串的组成部分 |
soundex() | 返回字符串的soundex值 |
upper() | 将字符串转换为大写 |
8.2使用函数
9汇总数据
9.1聚集函数
函数 | 说明 |
---|---|
avg() | 返回某列的平均值 |
count() | 返回某列的行数 |
max() | 范恢复某列的最大值 |
min() | 返回某列的最小值 |
sum() | 返回某列之和 |
9.1.1AVG()
AVG()通过对表中行数计数并计算其列值之和,求得该列的平均值。
AVG()可以用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
示例:
求出成绩表中的平均成绩
select AVG(成绩) as 平均成绩 from 成绩表
9.1.2COUNT()
COUNT()函数进行计数
示例:
求出学生表中的总人数
select COUNT(*) as 总人数 from 学生表
注意:
1.使用COUNT(*)对表中的数据数目进行计数,不管表列中包含的是空值(NULL)还是非空值
2.使用COUNT(column)对特定列中具有值得行进行计数,忽略NULL值
9.1.3MAX()
MAX()返回指定列中的最大值
示例:
求出商品中价格最高的
select MAX(价格) as 最高商品价 from 商品表
MAX()函数忽略列值为NULL的行
9.1.4MIN()
MIN()返回指定列中的最小值
示例:
求出商品中价格最低的
select MIN(价格) as 最低商品价 from 商品表
MIN()函数忽略列值为NULL的行
9.1.5SUM()
SUM()用来返回指定列值的和(总计)。
示例:
检索订购物品的总数
select SUM(订购数) as 订购物品总数 from 订单表
SUM()函数忽略列值为NULL的行
9.2聚集不同值
以上五个聚集函数都可以如下使用:
1.对于所有执行计算,指定ALL参数或不指定参数(ALL为默认参数)
2.只包含不同的值,指定DISTINCT参数
9.3组合聚集函数
select语句可以根据需要包含多个聚集函数
示例:
select COUNT(*) as 商品数目
MIN(价格) as 最低商品价格
MAX(价格) as 最高商品价格
AVG(价格) as 商品均价
from 商品表
10分组数据
10.1数据分组
10.2创建分组
分组:
是使用select语句的group by子句建立的
示例:
求出每种商品的商品数量
select 商品名,COUNT(*) as 商品数量
from 商品表
group by 商品名
group by 子句知识DBMS分组数据,然后对每个组而不是整个结果进行聚集
使用group by子句的重要规定:
1.group by子句可以包含任意数目的列
2.如果group by 子句中嵌套了分组,数据将在最后指定的分组上进行汇总。
3.group by 子句中列出的每一列都必须是检索列或者有效的表达式
4.大多数SQL实现不允许group by列带有长度可变得数据类型
5.除聚集计算语句外,select 语句中的每一列都必须在group by子句中给出
6.如果分组列中包含具有NULL值得行,则NULL将作为一个分组返回,如果列中有多行NULL,他们将会分为一组
7.group by子句必须出现在where子句之后 order by 子句之前。
10.3过滤分组
where过滤行,having过滤分组
having支持所有where操作符
示例:
求出商品数量大于等于2的商品数量
select 商品名,COUNT(*) as 商品数量
from 商品表
group by 商品名
having COUNT(*)>=2
where在数据分组前进行过滤,having在数据分组后进行过滤
10.4分组和排序
order by | group by |
---|---|
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列,而且必须使用每个选择列的表达式 |
不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
一般在使用group by 子句时,也应该给出order by子句。
10.5select子句顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
select | 要返回的列或表达式 | 是 |
from | 从中检索数据的表 | 仅在从表中选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
11使用子查询
11.1子查询
子查询:
即嵌套在其他查询中的查询
11.2利用子查询进行过滤
在select 中子查询总是从内向外进行处理
作为子查询的select语句只能查询单个列,企图查询多个列将会返回错误。示例:
查询学生表中性别为男的同学的选课信息
学生表
学号 姓名 性别
选课表
学号 课程号 学分
select * from 选课表 where 学号 in
(select 学号 from 学生表 where 性别 = '男')
11.3作为计算字段使用子查询
查询学生的选课数目
select 学号,姓名,(select count(*)from 选课表 where 学生表.学号 = 选课表.学号 group by 学号) as 选课数
from 学生表
12联结表
12.1创建联结
创建联结,指定要联结的所有表以及关联他们的方式即可
示例:
将学生表与选课表进行联结
学生表
学号 姓名 性别
选课表
学号 课程号 学分
select 学生表.学号,姓名,课程号
from 学生表,选课表
where 学生表.学号=选课表.学号
12.1.1where子句的重要性
在一条select语句中联结几个表时,相应的管理是在运行中构造的
没有联结条件的表关系返回的结果为笛卡尔积
要保证所有联结都要where子句
12.1.2内连接
示例:
将学生表与选课表进行内连接
学生表
学号 姓名 性别
选课表
学号 课程号 学分
select 学生表.学号,姓名,课程号
from 学生表
inner join 选课表 on 学生表.学号=选课表.学号
12.1.3联结多个表
SQL不限制一条select语句中可以联结的表的数目。
创建联结的基本规则也相同。
首先列出所有表,然后定义表之间的关系。
联结的表越多,性能下降得越厉害。
13创建高级联结
13.1使用不同类型的联结
13.1.1自联结
示例:
查询出李明所在院系的所有学生信息,常规要两次:
1.查询李明所在院系
2.查询院系的学生信息
自联结实现功能:
select 学生表.*
from 学生表 as a,学生表 as b
where b.姓名='李明' and a.所属院系=b.所属院系
13.1.2自然联结
自然联结:
在等值连接的基础上,查询出需要的字段,将(*)替换为需要显示的字段。
示例:
select 学生表.*,成绩表.成绩,选课表.科目
from 学生表,成绩表,选课表
where 学生表.学号=成绩表.学号 and 成绩表.课程号=选课表.课程号
13.1.3外连接
13.1.3.1左外联结
A left join b ,生成的结果以左表a为准,a表的所有记录都列出来,如果b中不存在a中的部分记录,即可能部分a中的记录在b中无对应,则b中的数据以空值代表
示例:
例:学生表左连接
select 学生表.学号,姓名,性别,课程号,成绩 from
学生表 left join 成绩表 on 成绩表.学号=学生表.学号
13.1.3.2右外联结
A right join b 与上同理,查询结果以右边b表数据为准,如果a表中无记录对应,则以null值代表
示例:
select 课程号,成绩,学生表.学号
from 学生表 right join 成绩表 on 成绩表.学号=学生表.学号
13.1.3.3全外连接
a full join b
两个表的所有数据都要列出来,凡是对方表中无 对应的记录,则都以Null值代表
示例:
select 课程号,成绩,学生表.学号
from 学生表 full join 成绩表 on 成绩表.学号=学生表.学号
14组合查询
14.1组合查询
以下两种情况需要使用组合查询:
1.在一个查询中冲不同的表返回数据结构
2.对一个表执行多个查询,按一个查询返回数据
14.2创建组合查询
14.2.1使用union
语法;
select 字段名1,字段名2,字段名3
from table_name
where 限制条件1
union
select 字段名1,字段名2,字段名3
from table_name
where 限制条件2
示例:
select 姓名,性别
from 学生表
where 学号 in ('0001','0002','0003')
union
select 姓名,性别
from 学生表
where 学号 ='0004'
15插入数据
15.1数据插入
15.1.1基本的数据插入
语法:
insert into table_name
values(值1, 值2,....)
示例:
insert into 学生表01
values('李明','男','1.70')
15.1.2更安全的数据插入
语法:
insert into table_name(列名1,列名2)
values(值1, 值2)
示例:
insert into 学生表01(姓名,性别,身高)
values('李亮','女','1.60')
15.2从一个表复制到另一个表
15.2.1从一个表复制到另一个表
语法:
select *into table_name1 from table_name2
示例:
select *into 学生表 from 学生表01
15.2.2插入检索出的数据
语法:
insert into table_name1(列名1,列名2)
select 列名1,列名2
from table_name2
where 条件
示例:
insert into 学生表02(性别,姓名,身高)
select 性别,姓名,身高
from 学生表01
where 性别='男'
16 更新和删除数据
16.1更新数据
语法:
update table_name
set
字段名1='值',
字段名2='值'
where 限制条件
示例:
update 学生表
set
姓名='李明',
性别='女'
where 学号='0001'
16.2删除数据
语法;
delete from table_name
where 要删除行的信息
示例:
delete from 学生表
where 学号='0001'
17使用视图
17.1视图
数据库中的建立的表table叫物理表
view是查询结果的保存,它是虚拟表
视图分:
使用select命令查询出的结果,是临时,用完就关闭—临时的视图
某个查询结果我们要以后再使用,可以把查询的命令保存起来,叫虚拟表,永久视图。(视图本身不是物理表,视图本身是无数据,就是一个可简单可复杂的select命令)
17.2创建视图
语法:
create view table_name as
select 创建视图的相应信息
示例:
create view 学生成绩表 as
select 学生表.学号,课程名称,成绩
from 学生表,成绩表,课程表
where 学生表.学号 = 成绩表.学号
and 成绩表.课程编号 = 课程表.课程编号
18使用存储过程
18.1存储过程
18.2创建存储过程
语法:
create procedure 过程名 as
过程体语句(可是若干条命令)
18.2.1无参存储过程
无参传递过程,主调程序不需要传递实参给过程
示例:
create procedure p1 as
declare @xm nchar(6)
set @xm='李明'
select 学号,姓名,生日 from 学生表 where 姓名=@xm
18.2.2有参存储过程
示例:
create procedure p2 @xm nchar(6),@kch nchar(4)
as
select 成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
18.2.3有参且有返回值
create procedure p3 @xm nchar(6),@kch nchar(4),
@cj int output
as
declare @cj0 int
select @cj0=成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
set @cj=@cj0
18.3执行存储过程
18.3.1无参存储过程
示例:
execute p1
18.3.2有参存储过程
示例:
execute p2 '李亮','D003'
18.3.3有参且有返回值
示例:
declare @cj int
execute p3 '李亮','D001',@cj output
print @cj