当前位置: 主页 > 学习教程 > 网络硬件教程 >
周浦电脑培训
周浦电脑培训-MSSQL数据库上课教案(3)

 第三课:访问和修改数据

本教案为内部培训专用,请勿转载!

周浦电脑培训,周浦sql培训,浦东sql培训

本章学习重点是查询、插入、更新数据,操作前要先打开表文件
1、SELECT语法
SELECT  <字段名列表>  [into  新表]  FROM  <表文件>  WHERE  <>  GROUP  BY  <>  HAVING  ORDER  BY  <字段名>  DESC
1、最简单查询
SELECT * FROM receive_detail
2、不重复记录查询
SELECT DISTINCT goods_id FROM receive_detail
3、带别名查询
SELECT detail_id AS 详细ID,goods_id 商品ID,code 代码,name 名称 FROM receive_detail
4、范围内但询
SELECT TOP 10 * FROM kc_detail
5、带简单条件查询
SELECT name,price,goods_id FROM output_detail WHERE quantity>20 and price>5
6、用BETWEEN、IN、LIKE条件查询
SELECT master_id,name,quantity,price FROM output_detail WHERE quantity BETWEEN 10 AND 50
SELECT master_id,name,goods_id FROM output_detail WHERE goods_id IN ('10878','9361')
SELECT master_id,name,goods_id FROM output_detail WHERE goods_id LIKE '1%'
7、分组查询(按商口号分组查询其总量)
SELECT  goods_id,pici,SUM(goods_id) AS 数量 FROM output_detail GROUP BY goods_id
8、带分组条件查询
SELECT  goods_id,pici,SUM(goods_id) AS 数量 FROM output_detail GROUP BY goods_id HAVING SUM(quantity)>400
9、查询时进行排序
SELECT master_id,name,goods_id,price FROM output_detail ORDER BY price DESC
10、不分组求总和、个数、平均值
SELECT master_id,name,price,goods_id FROM output_detail COMPUTE avg(price)
11、多表查询
SELECT * FROM receive_detail INNER JOIN output_detail ON  receive_detail.goods_id= output_detail.goods_id AND receive_detail.pici= output_detail.pici
12、嵌套查询(查询表中的字段必须在条件表中存在)
SELECT detail_id,goods_id,name,price FROM receive_detail where pici IN(SELECT pici FROM output_detail WHEREe (sale_price-price)*quantity>2)
13、数据的插入
INSERT INTO receive_detail(detail_id,goods_id,name) VALUES ('1100042829','1548545','aa')
14、数据的更新
UPDATE receive_detail SET name='BB' WHERE detail_id='1100042829'
15、数据的删除
DELETE receive_detail WHERE detail_id='1100042829'
16、删除表中所有数据
TRUNCATE TABLE receive1

分享到: