|
insert sales values('O01','B','1',400) insert sales values('P02','B','2',350)
insert sales values('X03','B','3',120)
------------------------------------------------------------------------------
sp_help sales
----------------------------
select * from sales where product='l02' --使用where子句查询表中内容
select * from sales where quantity=300
select * from sales where color='R'
------------------------------------------------------------------------------
select product from sales --查询指定列的内容
select product,color from sales
select product,color,size from sales
------------------------------------------------------------------------------
select * from sales where> select * from sales where quantity>500
select * from sales where product=500
select * from sales where> --------------------------------------------------------------------------------
select * from sales where product like 'm%' --所有以M开头的
select * from sales where product like '%2%' --所有包含2的
select * from sales where product like '__2%' --第三个数字是2的
select * from sales where product like '[m,p]%' --所有以M或P开头的
select * from sales where product like '[^m,p]%' --所有不以M或P开头的
select * from sales where product like '[a-l]%' --从A到L中任意字母开头的
---------------------------------------------------------------------------------
select * from sales where product='L01' and color='B' --逻辑操作符
select * from sales where product='L01' and color='B' and> select * from sales where product='L01' or color='B'
select * from sales where not(product='L01' and color='B')
select * from sales where product in ('L01','M01','L03')
select * from sales where quantity between 500 and 1000 --从500到1000,包含了500和1000
--------------------------------------------------------------------------------------------
create table t1 --空值的插入和空值的查询
(c1 int not null,
c2 char(10) null,
c3 char(10) null)
go
insert t1 values(1,'a1','b1')
go
select * from t1
go
insert t1 values(2,'a2','') --用引号括起来不输值不代表空值
go
select * from t1 where c3 is null
go
insert t1 values(3,'a3',null) --插入空值
insert t1 values(4,null,'b4')
insert t1(c1,c3) values(5,'b5')
insert t1(c1,c2) values(6,'a6')
insert t1(c1) values(7)
go
select * from t1 where c3 is null --查询空值
select * from t1 where c2 is null
-----------------------------------------------------------------------------------------------
select * from sales order by product --order by排序,不指明则默认为asc
select * from sales order by product asc --asc:升序
select * from sales order by product desc --desc:降序
select * from sales order by product,color desc --product为升序,color为降序
select * from sales order by product,color,size desc
-----------------------------------------------------------------------------------------------
select product from sales --这样查询结果中存在重复结果
select distinct product from sales --distinct消除结果中的重复列
select distinct color from sales
select distinct product from sales order by product --同时对结果进行排序
select distinct product,color from sales --product和color的组合不存在重复结果
------------------------------------------------------------------------------------------------
select sale_id as 编号, --改变结果中列名的显示
product as 产品,
color as 颜色,
> quantity as 数量
from sales
---------------------------------------------------------------------------------------------------
select '编号'=sale_id, --改变结果中列名的显示
'产品'=product,
'颜色'=color,
'规格'=size,
'数量'=quantity
from sales
----------------------------------------------------------------------------------------------------
select '编号:',sale_id, --插入说明列
'产品:',product,
'颜色:',color,
'规格:',size,
'数量:',quantity
from sales
----------------------------------------------------------------------------------------------------
alter table t1 --在表中增加新列时新列的值必须允许为空或者有默认值
add c4 int null
----------------------------------------------------------------------------------------------------
update t1 set c4=100 where c1=1 --update:更改表中的数据
update t1 set c3='b2' where c2='a2' and c1=2
----------------------------------------------------------------------------------------------------
delete t1 where c1=7 --delete:删除表中指定行的数据
delete t1 where c1=3 and c2='a3'
----------------------------------------------------------------------------------------------------
alter table t1 drop column c4 --删除列
|
|