结构化查询语句
基本表的定义，修改，删除
建表考虑列名，数据类型，列级完整性，表级完整性（参照性约束）
create table Salvaging
(
prj_num char(8) primary key,
prj_name varchar(50),
start_date datetime,
end_date datetime,
prj_status bit,
);
create table Stock
(
mat_num char(8) primary key,
mat_name varchar(50) not null,
speci varchar(20) not null,
warehouse char(20),
amount int,
unit decimal(18,2),
total as(amount*unit),
check(mat_num like '[m][0-9][0-9][0-9]'),
);
create table Out_stock
(
prj_num char(8),
mat_num char(8),
amount int,
get_date datetime default getdate(),
department char(20),
primary key (prj_num,mat_num),
foreign key(prj_num) references Salvaging(prj_num),
foreign key(mat_num) references Stock(mat_num),
);
alter table Salvaging add prj_director varchar(10);
alter table Salvaging drop column prj_director
drop table Salvaging
简单查询
单表查询
select prj_name 项目名称,start_date 开始日期,end_date 结束日期,DATEDIFF(day,start_date,end_date) 抢修天数
from Salvaging
select *
from Stock
where unit>=50 and unit<=100 --条件
in集合
select mat_num,speci,amount,warehouse
from Stock
where warehouse not in ('供电局1#仓库','供电局2#仓库')
字符匹配
select *
from Stock
where mat_name like '__绝缘%'
NULL值(is)
select *
from Stock
where unit is null
排序：只能对最后的查询结果排序
select *
from Stock
where mat_name='护套绝缘电线'
order by unit desc --默认从低到高(asc),desc从高到底
聚集函数
select MAX(amount),min(amount),avg(amount)
from Out_stock
where mat_num = 'm001'
分组
select prj_num 项目号,count(*) 物资种类
from Out_stock
group by prj_num
select prj_num 项目号,count(*) 物资种类
from Out_stock
group by prj_num
having count(*)>=2
连接查询
等值与非等值查询
等值 与非等值根据连接谓词
广义笛卡尔积不带谓词，没有意义；
自然连接：在等值连接的基础上，去除重复列
select Salvaging.prj_num,Salvaging.prj_name,mat_num,amount,get_date,end_date,department
from Salvaging,Out_stock
where Salvaging.prj_num = Out_stock.prj_num
外连接查询
连接操作中，如果有一个关系没有与之对应，就不会有输出，但是也丢失了另一个关系的基本情况，解决方案是外连接
外连接：左外连接，右外连接，全外连接
select Salvaging.prj_num,Salvaging.prj_name,mat_num,amount,get_date,end_date,department
from Salvaging left outer join Out_stock on (Salvaging.prj_num = Out_stock.prj_num)
复合条件查询
select distinct Salvaging.prj_num,Salvaging.prj_name
from Salvaging,Out_stock,Stock
where Salvaging.prj_num = Out_stock.prj_num and Out_stock.mat_num = Stock.mat_num and Stock.mat_name='护套绝缘电线'
自身连接查询
select A.prj_num
from Out_stock A,Out_stock B
where A.prj_num = B.prj_num and A.mat_num = 'm001' and B.mat_num = 'm002'
嵌套查询
带谓词in的嵌套查询（子查询往往是一个集合）
select Stock.mat_name,speci,amount
from Stock
where warehouse in ( -- = 亦可
select warehouse
from Stock
where speci = 'BVV-120' and mat_name = '护套绝缘电线'
)
/*
select Stock.mat_num,Stock.mat_name
from Salvaging,Out_stock,Stock
where Salvaging.prj_name='观澜站光缆抢修' and Salvaging.prj_num = Out_stock.prj_num and Out_stock.mat_num = Stock.mat_num
*/
select mat_num,mat_name
from Stock
where mat_num in (
select mat_num
from Out_stock
where prj_num in (
select prj_num
from Salvaging
where prj_name = '观澜站光缆抢修'
)
)
带比较运算符的嵌套查询
select mat_num,mat_name
from Stock s1
where amount > (
select avg(amount)
from Stock s2
where s2.warehouse = s1.warehouse
)
带any或all谓词的嵌套查询
select mat_name,speci,amount
from Stock
where warehouse <> '供电局1#仓库' and amount < ALL (
select amount
from stock
where warehouse = '供电局1#仓库'
)
带exists谓词的嵌套查询
select prj_name
from Salvaging
where exists (
select*
from Out_stock
where prj_num = Salvaging.prj_num and mat_num = 'm001'
)
sql中没有全称量词，把全称量词转换为存在量词
--查询被所有工程使用过了的物资——没有一个工程没有使用过他
select mat_name,speci
from Stock
where not exists (
select*
from Salvaging
where not exists (
select*
from Out_stock
where mat_num = Stock.mat_num and prj_num = Salvaging.prj_num
)
)
数据更新
插入数据
insert
into Salvaging
values ('20110011','观澜站电缆接地抢修','2011-2-3 0:00:00','2011-2-5 12:00:00',1)
插入查询结果
insert
into Prj_cost
select prj_num,sum(out_stock.amount*unit)
from Out_stock,Stock
where Out_stock.mat_num = stock.mat_num
group by prj_num
修改数据
update Stock
set unit = 44.5
where mat_num = 'm020'
删除数据
delete
from Out_stock
where prj_num = '20110001' and mat_num = 'm001'
视图
-
视图是数据库数据的特定子集。可以禁止所有用户访问数据库表，而要求用户只能通过视图操作数据，这种方法可以保护用户和应用程序不受某些数据库修改的影响。
-
视图是抽象的，他在使用时，从表里提取出数据，形成虚的表。 不过对他的操作有很多的限制 。
创建视图
create view s1_stock
as
select mat_num,mat_name,speci,amount,unit
from Stock
where warehouse = '供电局1#仓库'
查询视图
--像基本表一样查询视图
select *
from s1_stock
更新视图
--insert,delete
update s1_stock
set amount = 100
where mat_num = 'm001'
删除视图
drop view s1_stock
