【SQL】查询练习

/* 查询结果按照价格降序名称升序排序显示*/
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_price;

--条件查询
/*检索价格小于10的产品的名称和价格*/
SELECT prod_name,prod_price
FROM products
WHERE prod_price<10;
/*检索价格介于5到10之间的产品的名称和价格*/
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
/*检索电子邮箱为空的顾客名称*/
SELECT cust_name
FROM customers
WHERE cust_email IS NULL;
/*从products表中检索供应商为DLL01并且价格小于4的产品id、价格、名称，并按照价格、名称升序排序显示*/
SELECT prod_id,prod_name,prod_price
FROM products
WHERE vend_id='DLL01' AND prod_price<=4
ORDER BY prod_price,prod_name;
/*上句and改为or就是或者*/
/*从products表中检索供应商为DLL01或者BRS01、同时价格大于等于10的产品价格、名称*/
/*如果不加括号那么就会检索出供应商为BRS01同时价格大于等于10和供应商为DLL01的全部产品*/
/*因为AND优先级大于OR*/
SELECT prod_name,prod_price
FROM products
WHERE (vend_id='DLL01' OR vend_id='BRS01')
AND prod_price>=10;
/*检索出DLL01和BRS01供应商的产品的名称和价格*/
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN ('DLL01' , 'BRS01')
ORDER BY prod_name;
/*效果同上*/
SELECT prod_name,prod_price
FROM products
WHERE vend_id='DLL01' OR vend_id='BRS01'
ORDER BY prod_name;

/*检索出不是由DLL01供应商提供的产品的名称和价格*/
SELECT prod_name,prod_price
FROM products
WHERE NOT vend_id='DLL01';

/*检索出产品名称由fish起头的产品的名称和id*/
SELECT prod_name,prod_id
FROM products
WHERE prod_name LIKE 'fish%';

/*检索出产品名称任意位置包含bean bag的产品的名称和id*/
SELECT prod_name,prod_id
FROM products
WHERE prod_name LIKE '%bean bag%';

/*f%y表示开头是f结尾是y*/ 

/*检索出产品名称前两个字符随意，后变字符为inch teddy bear 的产品的名称和id*/
/*_只能代替一个字符，%可以代替任意长度字符*/
SELECT prod_name,prod_id
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

/*生成新列，新列格式为：供应商名称（供应商国家），并将新列命名为vend_title*/
SELECT concat(vend_name, ' (',vend_country,')') AS vend_title
FROM vendors
ORDER BY vend_name;

/*检索订单号为20008的产品id、数量、单价，同时生成新列计算不同产品总价并命名为expanded_price*/
SELECT prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num=20008;

/*查询现在的时间*/
SELECT NOW();

/*检索发音类似于Michael green的联系名*/
SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

/*检索订单年份为2012年的全部订单号*/
SELECT order_num
FROM orders
WHERE YEAR(order_date)=2012;

/*返回products表中供应商为DLL01的所有产品的平均产品价格（列平均）*/
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id='DLL01';

--count(*)数上了null，count(属性名)没数null
SELECT COUNT(*) AS num_cust
FROM customers;

--找最大值用MAX，最小值用MIN
SELECT MAX(prod_price) AS max_price
FROM products;

--和用SUM，也可以用于合计计算值
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num=20005;