mysql 必知必会

#SHOW DATABASES
#USE user
#SELECT * FROM users ORDER BY name
#SELECT * FROM users WHERE name NOT IN ('4444','Hello World')
#SELECT * FROM users WHERE name LIKE '%4%' #百分号通配符 匹配多个
#SELECT * FROM users WHERE name LIKE '自动%4%'
#SELECT * FROM users WHERE name LIKE '4_44' #_只能替代一个字符与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。
#SELECT * FROM users WHERE name REGEXP '44'
#SELECT * FROM users WHERE name REGEXP '4444.1'
#SELECT * FROM users WHERE name REGEXP '4444|He' #搜索两个串之一
#SELECT * FROM users WHERE name REGEXP '[54]44'
#SELECT * FROM users WHERE name REGEXP '[:alpha:]'
#SELECT CONCAT(name,'(',age,')') AS kuohao,CONCAT(name,'&',age,'&') AS he FROM users
#USE user
#计算字段
#SELECT pro_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems
#SELECT name,Upper(name) AS upcase_name FROM users
#SELECT * FROM users
#SELECT AVG(age) AS avg_age FROM users # 求平均数
#SELECT COUNT(*) FROM users #聚焦函数
#SELECT MAX(age) FROM users #MIN(expr)
#SELECT SUM(age) AS sumage FROM users
#select version() from users;

#分组GROUP BY
#SELECT *,COUNT(*) AS age_nums FROM users GROUP BY age #按年龄分组 顺序 where 、GROUP BY、 ORDER BY
SELECT id,COUNT(*) AS age_count FROM users GROUP BY id HAVING COUNT(*)>20


#USE need
#SELECT * FROM customers
#SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
#SELECT cust_id FROM orders WHERE order_num IN (20005,20007)
-- SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (
-- 	SELECT cust_id FROM orders WHERE order_num IN (
-- 		SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
-- 	)
-- )

#联结表
-- SELECT vend_name, prod_name, prod_price
-- FROM vendors,products
-- WHERE vendors.vend_id = products.vend_id
-- ORDER BY vend_name, prod_name

#内部联结与上面的含义相同。
-- SELECT vend_name, prod_name, prod_price
-- FROM vendors INNER JOIN products
-- ON vendors.vend_id = products.vend_id

SELECT prod_name, vend_name, prod_price,quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005