修改数据.sql

简单的sql语句 数据与汇总。 高手多多指点。

select * from customers
insert customers
(customerid,companyname,contactname,contacttitle
,address,city,region,postalcode,country,phone
,fax)
values
('pecof','pecos coffee company','Michael Dunn'
,'owner','1900 oak street','vancouer','bc'
,'v3f 2k1','canada','[phone]'
,'[phone]')
go


select * from customers
where customerid='pecof'
select * from customers
/* 插入数据之插入单行数据*/

select * from employees
 
insert customers
 select substring(firstname,1,3) 
       + substring(lastname,1,2)
       ,lastname,firstname,title,address,city
       ,region,postalcode,country,homephone,null
 from employees

select * from customers
/*插入多行数据 */

select * from products

select productname as products,unitprice as price
into #pricetable
from products

select * from #pricetable 
/*创建临时表，相当于插入多行数据到一个临时表*/

select * from customers
select customerid,companyname,contactname,'int',contacttitle
from customers 
where customerid like 'a%' or customerid like 'w%'

select customerid,companyname,contactname,null,contacttitle
from customers 
where customerid like 'a%' or customerid like 'w%'
/*字面值知识点，null也可以作为字面字，即有些关键字可以作为字面值*/


select * from shippers
exec   sp_help shippers

insert shippers
(companyname)
values
('microsoft')

select * from shippers

insert shippers
(companyname,phone)
values
('haopeitao gongsi',default)

select * from shippers
/*插入部分数据和默认值*/

select * from abc

delete abc
where num in(7,8)
/*删除数据，行和多行*/

select * from [order details] 
select * from orders
delete from [order details]
     from orders as o
     inner join  [order details] as od
     on o.orderid=od.orderid
     where orderdate=4/14/1998
/*删除数据基于其他表，利用关联数据方法，即联接，还可以用子查询*/
 
select * from products

update products
set unitprice = (unitprice*1.1)
go

select * from products

update products
set unitprice=(unitprice/1.1)
go
/*数据更新,没有where子句限定更新行，所以是所有行更新*/

select * from products
select * from suppliers

select unitprice,country
from products
inner join suppliers
on products.supplierid=suppliers.supplierid
where country='usa'

update products
set unitprice =unitprice +2
from products
inner join suppliers
on products.supplierid=suppliers.supplierid
where suppliers.country='usa'
go

update products
set unitprice =unitprice -2
from products
inner join suppliers
on products.supplierid=suppliers.supplierid
where suppliers.country='usa'
go
/*更新数据，利用数据的关联，即联接*/

update products
set unitprice = unitprice + 2
where supplierid in (
                     select supplierid from suppliers 
                     where country = 'usa'
                    )
go


update products
set unitprice = unitprice - 2
where supplierid in (
                     select supplierid from suppliers 
                     where country = 'usa'
                    )
go
/*数据的更新，利用子查询*/