sql查询句子大全(合集40句)

1、查询所有客户信息：SELECT*FROMcustomers;

2、查询所有订单信息：SELECT*FROMorders;

3、查询商品表中所有商品名称和价格：SELECTproductName,priceFROMproducts;

4、查询客户表中所有地址信息：SELECTaddressFROMcustomers;

5、查询订单表中所有订单号和订单日期：SELECTorderNumber,orderDateFROMorders;

6、查询商品表中所有价格不超过1000的商品：SELECT*FROMproductsWHEREprice<=1000;

7、查询客户表中所有电话号码以'415-'开头的客户：SELECT*FROMcustomersWHEREphoneLIKE'415-%';

8、查询订单表中所有订单总金额大于5000的订单：SELECTorderNumber,SUM(priceEach*quantityOrdered)ASorderAmountFROMorderDetailsGROUPBYorderNumberHAVINGorderAmount>5000;

9、查询商品表中所有商品价格按从高到低排序：SELECT*FROMproductsORDERBYpriceDESC;

10、查询客户表中所有客户按最后一次购买日期排序：SELECT*FROMcustomersORDERBYlastPurchaseDate;

11、查询订单表中所有订单日期在2019年之后的订单：SELECT*FROMordersWHEREorderDate>='2019-01-01';

12、查询商品表中所有含有'Book'关键字的商品：SELECT*FROMproductsWHEREproductNameLIKE'%Book%';

13、查询客户表中所有注册日期在2020年之后的客户：SELECT*FROMcustomersWHEREregistrationDate>'2020-01-01';

14、查询订单表中所有按客户ID分组后每组订单总金额的平均值：SELECTcustomerNumber,AVG(orderAmount)ASavgOrderAmountFROM(SELECTcustomerNumber,orderNumber,SUM(priceEach*quantityOrdered)ASorderAmountFROMorderDetailsGROUPBYorderNumber)ASorderAmountsGROUPBYcustomerNumber;

15、查询商品表中所有价格介于100和500之间的商品按价格升序排列：SELECT*FROMproductsWHEREpriceBETWEEN100AND500ORDERBYpriceASC;

16、查询客户表中所有居住xx为'USA'且邮政编码以'9'结尾的客户：SELECT*FROMcustomersWHEREcountry='USA'ANDpostalCodeLIKE'%9';

17、查询订单表中所有订单日期在2019年和2020年之间且订单状态为'InProcess'的订单：SELECT*FROMordersWHEREorderDateBETWEEN'2019-01-01'AND'2020-12-31'ANDstatus='InProcess';

18、查询商品表中所有价格最高的前5个商品：SELECT*FROMproductsORDERBYpriceDESCLIMIT5;

19、查询客户表中所有注册日期最近的前10个客户：SELECT*FROMcustomersORDERBYregistrationDateDESCLIMIT10;

20、查询订单表中所有订单编号不在orderDetails表中出现过的订单：SELECT*FROMordersWHEREorderNumberNOTIN(SELECTorderNumberFROMorderDetails);

21、查询商品表中所有商品大于等于100个库存的且价格在500和1000之间的商品：SELECT*FROMproductsWHEREquantityInStock>=100ANDpriceBETWEEN500AND1000;

22、查询客户表中所有电子邮件地址中包含'@gmail.com'的客户：SELECT*FROMcustomersWHEREemailLIKE'%@gmail.com';

23、查询订单表中所有订单日期不重复的日期：SELECTDISTINCTorderDateFROMorders;

24、查询商品表中所有价格大于等于1000的商品并且按价格从高到低排序并返回前3个：SELECT*FROMproductsWHEREprice>=1000ORDERBYpriceDESCLIMIT3;

25、查询客户表中男性和女性的数量：SELECTgender,COUNT(*)AScountFROMcustomersGROUPBYgender;

26、查询订单表中所有订单的购买产品数量总和：SELECTSUM(quantityOrdered)AStotalQuantityFROMorderDetails;

27、查询商品表中所有生产商为'Toyota'的商品：SELECT*FROMproductsWHEREmanufacturer='Toyota';

28、查询客户表中所有客户姓名中包含'Mike'的客户：SELECT*FROMcustomersWHEREcustomerNameLIKE'%Mike%';

29、查询订单表中所有订单状态不是'Cancelled'的订单总数：SELECTCOUNT(*)ASorderCountFROMordersWHEREstatus!='Cancelled';

30、查询商品表中所有价格不重复的价格：SELECTDISTINCTpriceFROMproducts;

31、查询客户表中所有客户按照所在州分组后每组客户总数：SELECTstate,COUNT(*)AScustomerCountFROMcustomersGROUPBYstate;

32、查询订单表中所有订单的购买数量超过40个的产品的订单详情：SELECT*FROMorderDetailsWHEREquantityOrdered>40;

33、查询商品表中所有颜色为'Blue'且生产商为'Toyota'的商品：SELECT*FROMproductsWHEREcolor='Blue'ANDmanufacturer='Toyota';

34、查询客户表中所有公司名称以'Inc.'结尾的客户：SELECT*FROMcustomersWHEREcustomerNameLIKE'%Inc.';

35、查询订单表中所有订单的订单总金额：SELECTorderNumber,SUM(priceEach*quantityOrdered)ASorderAmountFROMorderDetailsGROUPBYorderNumber;

36、查询商品表中所有价格高于平均价格的商品：SELECT*FROMproductsWHEREprice>(SELECTAVG(price)FROMproducts);

37、查询客户表中所有购买产品总金额超过10000的客户：SELECTcustomers.customerName,SUM(priceEach*quantityOrdered)AStotalAmountFROMcustomersINNERJOINordersONcustomers.customerNumber=orders.customerNumberINNERJOINorderDetailsONorders.orderNumber=orderDetails.orderNumberGROUPBYcustomers.customerNumberHAVINGtotalAmount>10000;

38、查询订单表中所有订单的状态和订单详情中商品的总金额：SELECTorders.orderNumber,orders.status,SUM(priceEach*quantityOrdered)ASorderAmountFROMordersINNERJOINorderDetailsONorders.orderNumber=orderDetails.orderNumberGROUPBYorders.orderNumber;

39、查询商品表中所有生产商出现次数超过2次的生产商：SELECTmanufacturer,COUNT(*)AScountFROMproductsGROUPBYmanufacturerHAVINGcount>2;

40、查询客户表中所有注册日期在2020年1月1日之后且购买总金额超过1000的客户：SELECTcustomers.customerName,SUM(priceEach*quantityOrdered)AStotalAmountFROMcustomersINNERJOINordersONcustomers.customerNumber=orders.customerNumberINNERJOINorderDetailsONorders.orderNumber=orderDetails.orderNumberWHEREregistrationDate>'2020-01-01'GROUPBYcustomers.customerNumberHAVINGtotalAmount>1000;