sql-server 数据表的简单操作

1.创建表

 

create table 学生信息
(
--定义表的列的数据格式
   学号 int not null,
   姓名 char(20),
   性别 char(4),
   年龄 int,
   班级 char(20),
   院系 char(30),
--添加相关约束
   constraint 性别 check(性别 = '男' or 性别 = '女'),  限制性别一列的数据只能是 男 女
   constraint 年龄 check(年龄 between 0 and 50)       限制年龄只能是0到50
)

2.修改表

--增加列
alter table 学生信息              --指定要修改的表
add 授课地点 char(50) not null    --增加列 授课地点

--修改列
alter table 学生信息              --指定要修改的表
alter column 授课地点 char(50)    --修改列（授课地点）的数据格式 

--删除列
alter table 学生信息              --指定要修改的表
drop column 授课地点              --删除列 授课地点  

--设置数据表的主键
alter table 学生信息 with nocheck
add constraint pk_num_id
primary key clustered(学号)

3.数据的插入

--向已创建的表中添加数据
insert into 职员信息(编号,姓名,部门编号,职务编号,年龄,现住址)
values(1,'张三','001','1001',25,'A'),
      (2,'李四','002','2001',19,'S')

--复制表
insert into DataBack(tid,tname,tdid,tmid,tage,taddress)  --选择和 职员信息 数据格式相同的表
select 编号,姓名,部门编号,职务编号,年龄,现住址              --选择对应的列
from 职员信息


--复制表
select 编号,姓名,部门编号,职务编号,年龄,现住址
into new_datas
from 职员信息
where 部门编号 is not null and 职务编号 is not null   --复制 职员信息 表中部门编号和职务编号不为空的列

4.表中数据的更新

 

--无条件更新
uodate 学生信息
set 院系='物联网工程学院'    --学生信息表中院系一列都被改成 物联网工程学院

--条件更新
update 学生信息
set 班级='甲班'
where 学号 < 14160150

--修改表中前n条数据
update top(n) 学生信息
set 班级='甲班'

5.删除表中的数据

--删除表中所有数据
delete from 职员信息

--按条件删除表中数据
delete from 职员信息
where 编号 <5

--truncate table删除表中数据
truncate table 学生信息    --使用truncate table 删除比select速度快很多

6.查询表中数据

--查询全部列
select * from 学生信息         --查询 学生信息 表中所有的列

--查询部分列
select 学号，姓名
from 学生信息                  --查询 学生信息 表中 学号，姓名 两列

--查询时使用别名  
select 学号 as no,姓名 as name
from 学生信息                  --查询 学生信息 表中 学号，姓名 两列，并分别使用 no,name 表示

--条件查询
select 姓名 from 学生信息
where 学号 between 1 and 4     --查询 学生信息 表中 学号在1到4之间的姓名列

--运算符查询
select * from 学生信息
where 性别 = '男' or 学号 > 2 and 年龄 < 22  --or表示或，and表示和 且and的优先级比or高

--模糊查询
select * from 学生信息
where 班级 like '%联网--'  --% 表示所在位置（可前中后）还有字符（数量不定），- 表示一个未知字符
where 班级 like '物联网[-]2'    -- 表示查询 物联网-2 班级的数据 [-]就是实际意义的-

--去重复记录
selcet distinct(数量),单价
from 商品库存                   --将 数量 一样的列放在一起

--查询前n条记录
select top 4 *
from 学生信息                   --查询前4条记录

--查询信息排序
select * from 学生信息
order by 学号
 

# 7.连接查询

--多表连接查询
select * from 学生信息,班级信息
where 学生信息.班级=班级信息.班级            --列出两张表中满足约束的所有数据 结果如图Fig3

--内链接查询
select * from 学生信息 inner join 班级信息
on 学生信息.班级=班级信息.班级    --列出两张表中满足约束的数据（和where限制类似） 结果如图Fig4

--左外连接
select * from 学生信息 left join 班级信息
on 学生信息.班级=班级信息.班级       --显示左表（学生信息）的所有信息，右表补空  结果如图Fig5


--右外连接
select * from 学生信息 right join 班级信息
on 学生信息.班级=班级信息.班级       --显示右表（班级信息）的所有信息，左表补空  结果如图Fig6

--全外连接
select * from 学生信息 full outer join 班级信息
on 学生信息.班级=班级信息.班级       --显示两表的所有信息，不足补空  结果如图Fig7

--自连接查询
select * from 班级信息 as a1,班级信息 as a2
where  a1.平均分>85                --自连接是使用别名与其自身连接查询 结果如图Fig8

--交叉查询
select * from 学生信息 cross join 班级信息
where 班级信息.平均分 >85           --类似自连接查询 结果如图fig9

实验的使用了两张表--学生信息（Fig1）和班级信息（Fig2）

Fig1 学生信息表

 

Fig2 班级信息表

实验结果图：Fig3-9

Fig3

Fig4

Fig5

Fig6

Fig7

 

Fig8

 

Fig9

#8 联合查询

--UNION语句查询
select 编号,名称,数量,入库日期
from 商品库存
union
select 编号,名称,数量,入库日期
from 货物信息                            --将两张表中相同部分合并，不同部分显示 如图Fig3

--INTERSECT语句查询
select 编号,名称,数量,入库日期
from 商品库存
intersect
select 编号,名称,数量,入库日期
from 货物信息                            --显示两张表中查询列完全相同的部分显示  如图Fig4

--EXCEPT语句查询
select 编号,名称,数量,入库日期
from 商品库存
except
select 编号,名称,数量,入库日期
from 货物信息                            --显示两张表中查询列不相同的部分显示  如图Fig5

实验的使用了两张表--货物信息（Fig1）和商品库存（Fig2）

Fig1 货物信息

 

Fig2 商品库存

实验结果如图Fig3-5

Fig3

Fig4

Fig5

#9.子查询

--SELECT语句子查询
select
avg(数量) as 库存平均数量,
(select avg(数量) from 货物信息)  as 货物平均数量,
max(数量) as 库存最大数量,
(select avg(数量) from 货物信息)  as 货物最大数量 
from 商品库存                                       --在商品库存查询时嵌入了货物信息查询


--IN语句查询
select
编号,名称,数量
from 货物信息
where 数量 in (select 数量 from 商品库存 where 数量<300)  
                                      --找出货物信息中数量等于商品库存中数量小于300的数量的列


--EXISTS语句查询
select
编号,名称,数量
from 货物信息 as a
where exists (select 名称 from 商品库存 where 名称=a.名称) 

--ANY语句查询
select
编号,名称,数量
from 货物信息
where 数量= any(select 数量 from 商品库存) --显示货物信息中数量在商品库存中数量集合中的数据


--
select
编号,名称,数量
from 货物信息
where 数量 > all(select 数量 from 商品库存) --显示货物信息中数量大于商品库存中所有数量的数据

#10.交叉表查询

--使用CASE语句实现交叉表查询
select
(case 名称 when '大米' then 数量 end)  as 大米每次进货数量,
(case 名称 when '小米' then 数量 end)  as 小米每次进货数量,
(case 名称 when '黄豆' then 数量 end)  as 黄豆每次进货数量
from 商品库存                          --搜索名称的每一行提取相应名称的数量形成新表  结果Fig1


--使用PIVOT语句实现交叉表查询
select 入库日期,
        a.[大米] as '大米',
        a.[小米] as '小米',    
        a.[黄豆] as '黄豆'
from 商品库存
     pivot(sum(数量) for 名称 in([大米],[小米],[黄豆])) as a

order by 入库日期                        --结果如图 Fig2

实验的使用了一张表 商品库存（和第9节相同）

结果图：

Fig1

Fig2