数据库中常用的sql语句有哪些

Question

如题所述

Answer 1

1.检索数据
SELECT prod_nameFROM Products;
#检索单列

SELECT prod_id, prod_name, prod_priceFROMProducts;
#检索多列

SELECT * FROM Products;
#检索所有列

SELECT DISTINCTvend_id FROMProducts;
#检索不同的值

SELECTprod_name FROM Products LIMIT 5;
#返回不超过5行数据

SELECTprod_name FROM Products LIMIT 5 OFFSET 5;
#返回从第5行起的5行数据。LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪儿开始。
2.排序检索数据
SELECTprod_name
FROMProducts
ORDER BYprod_name;
#排序数据

SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY prod_price, prod_name;
#按多个列排序

SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY 2, 3;
#按列位置排序，第三行表示先按prod_price, 再按prod_name进行排序

SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY prod_priceDESC, prod_name;
#prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序

3.过滤数据
SELECT prod_name, prod_price
FROMProducts
WHERE prod_price< 10;
#检查单个值

SELECT prod_name, prod_price
FROMProducts
WHERE vend_id <> ‘DLL01’;
#不匹配检查

SELECT prod_name, prod_price
FROMProducts
WHERE prod_priceBETWEEN 5 AND 10;
#范围值检查

SELECT cust_name
FROMCUSTOMERS
WHERE cust_emailIS NULL;
#空值检查

4.高级数据过滤
SELECTprod_id, prod_price, prod_name
FROMProducts
WHERE vend_id = ‘DLL01’ANDprod_price <= 4;
#AND操作符

SELECTprod_name, prod_price
FROMProducts
WHEREvend_id=’DLL01’ OR vend_id=’BRS01’;
#OR操作符

SELECTprod_name, prod_price
FROMProducts
WHERE (vend_id = ’DLL01’ORvend_id=’BRS01’)
ANDprod_price >= 10；
#求值顺序 AND的优先级高于OR

SELECTprod_name, prod_price
FROMProducts
WHERE vend_idIN (‘DLL01’,’BRS01’)
ORDER BY prod_name;
#IN操作符

SELECT prod_name
FROMProducts
WHERE NOTvend_id = ‘DLL01’
ORDER BY prod_name;
#NOT 操作符

SELECT prod_name
FROMProducts
WHEREvend_id <> ‘DLL01’
ORDER BY prod_name;
#NOT 操作符

Answer 2

select
into
from语句
要求目标表table_4不存在，因为在插入时会自动创建表table_4，并将table_3中指定字段数据复制到table_4中。
可以考虑使用如下语句:
insert
into
dbo.table_4
(sname,
semail)
(select
sname,
semail
from
table_3);