mysql基础知识_MySql基础知识整理

学习教材：MySql必知必会、SQL菜鸟教程；SQL练习：SQLZOO。

一、检索数据检索单个列

SELECT name FROM teachers;检索多个列

SELECT name,course FROM teachers;检索所有列

SELECT * FROM teachers;检索不同的行

SELECT DISTINCT pro_id FROM products;限制结果

SELECT name FROM teachers LIMIT 4;使用完全限定的表名

SELECT products.prod_name FROM crashcourse.products;

二、排序检索数据排序数据

SELECT name FROM teachers ORDER BY name;按多个列排序

SELECT name, yr, course FROM teacher order by name, yr;指定排序方向：默认升序，降序指定关键字DESC

SELECT name ,yr FROM teacher order by yr DESC;

三、过滤数据使用WHERE子句

SELECT p_name, p_price FROM products WHERE p_price = 5;WHERE子句操作符：检查单个值

SELECT p_name, p_price FROM products WHERE p_name = 'apple';不匹配检查

SELECT id, p_name FROM products WHERE id <> 001;范围值检查

SELECT p_name, p_price FROM products WHERE p_price between 5 and 10;空值检查

SELECT p_name, p_price FROM products WHERE p_price IS NULL;

四、数据过滤AND操作符

SELECT id, p_price FROM products WHERE id = 002 AND p_price >7;OR操作符

SELECT id, p_price FROM products WHERE id = 002 OR id = 004;IN操作符

SELECT id, p_price FROM products WHERE id IN (002, 003) ORDER BY p_price;NOT操作符

SELECT id, p_price FROM products WHERE id NOT IN (002, 003) ORDER BY p_price;

五、用通配符进行过滤LIKE操作符：百分号%通配符

SELECT id, p_name FROM products WHERE p_name LIKE 'a%';下划线通配符

SELECT id, p_name FROM porducts WHERE p_name LIKE '_ ton';

六、正则表达式搜索基本字符匹配:REGEXP

SELECT p_name FROM products WHERE p_name REGEXP '1000' ORDER BY p_name;进行OR匹配

SELECT p_name FROM products WHERE p_name REGEXP '1000|2000' ORDER BY p_name;匹配几个字符之一

SELECT p_name FROM products WHERE p_name REGEXP '[123] Ton' ORDER BY p_name;匹配范围

SELECT p_name FROM products WHERE p_name REGEXP '[1-5] Ton' ORDER BY p_name;匹配特殊字符

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;匹配多个实例

SELECT p_name FROM products WHERE p_name REGEXP '\\([0-9] sticks?\\)' ORDER BY p_name;定位符

SELECT p_name FROM products WHERE p_name REGEXP '^[0-9\\.]' ORDER by p_name;

七、创建计算字段拼接字段

SELECT Concat(vend_name, '(',vend_country,')') FROM vendors ORDER BY vend_name;使用别名

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;执行算术计算

SELECT pro_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

八、用数据处理函数文本处理函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;日期和时间处理函数

SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';

九、汇总数据AVG()函数

SELECT AVG(prod_price) AS avg_price FROM products;COUNT()函数

SELECT COUNT(*) AS num_cust FROM customers;MAX()函数

SELECT MAX(prod_price) AS max_price FROM products;MIN()函数

SELECT Min(prod_price) AS min_price FROM products;SUM()函数

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;聚集不同的值

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;组合聚集函数

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM proucts;

十、分组数据创建分组

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;过滤分组

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id

HAVING COUNT(*) >= 2;分组和排序

SELECT order_num, SUM(quantity*item_price) AS ordertotal from orderitems

GROUP BY order_num HAVING SUM(quantity*item_price) >= 50

ORDER BY ordertotal;

十一、使用子查询利用子查询进行过滤

SELECT cust_id FROM orders WHERE order_num IN (

SELECT order_num FROM order_num FROM orderitems

WHERE prod_id = 'TNT2');作为计算字段使用子查询

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id

= customers.cust_id) AS orders FROM customers

ORDER BY cust_name;

十二、联结表创建联结

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 cust_name, cust_contact

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id

AND orderitems.order_num = orders.order_num

AND prod_id = 'TNT2';

十三、创建高级联结自联结

SELECT p1.prod_id, p1.prod_name

FROM products AS p1, products AS p2

WHERE p1.vend_id = p2.vend_id

AND p2.prod_id = 'DTNTR';自然联结

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price

FROM customers AS c, orders AS o, orderitems AS oi

WHERE c.cust_id = o.cust_id

AND oi.order_num = o.order_num

AND prod_id = 'FB';外部联结

SELECT customers.cust_id, orders.order_num

FROM customers LEFT OUTER JOIN orders

ON customers.cust_id = orders.cust_id;使用带聚集函数的联结

SELECT customers.cust_name, customers.cust_id,

COUNT(orders.orders_num) AS num_ord

FROM customers INNER JOIN orders

ON customers.cust_id = orders.cust_id

GROUP BY customers.cust_id;

十四、组合查询使用UNION