SQL必知必会学习笔记

一、检索数据
1.1检索单个列
SELECT row_name FROM TableName;

1.2检索多个列
SELECT row_name1, row_name2 FROM TableName;

1.3检索所有列
SELECT * FROM TableName;

1.4检索不同的值
SELECT DISTINCT row_name FROM TableName;

1.5查询前五个数据
SELECT * FROM TableName LIMIT 5;

1.6从第11条开始查询20条数据
SELECT * FROM TableName LIMIT 10,20;

1.7求值查询
SELECT score1, score2, score1+score2 AS total_score FROM TableName;

二、排序检索数据
2.1按列升序
SELECT row_name FROM TableName ORDER BY row_name;

2.2按列降序
SELECT row_name FROM TableName ORDER BY row_name DESC;

2.3多列排序
SELECT row_name1, row_name2 FROM TableName ORDER BY row_name1 DESC, row_name2;

三、过滤数据
3.1范围值查询(左右都是闭区间)
SELECT score FROM TableName WHERE score BETWEEN 90 AND 100;

3.2组合WHERE字句
SELECT id, score FROM TableName WHERE id > 1 AND score >= 60;

3.3指定查询
SELECT id FROM TableName WHERE id = 1 OR id = 60;
SELECT id FROM TableName WHERE id in (1, 60);

3.4指定排除查询
SELECT id FROM TableName WHERE NOT id = 1;
SELECT id FROM TableName WHERE id i != 1;
SELECT id FROM TableName WHERE id i <> 1;

四、通配符过滤
4.1%任何字符出现任意次数
SELECT name FROM TableName WHERE name LIKE '%name%';

4.2_任何字符出现一次
SELECT name FROM TableName WHERE name LIKE '_ name _';

五、函数处理
5.1求平均值
SELECT AVG(score) AS average_score FROM TableName;

5.2计数
SELECT COUNT(id) AS id_count FROM TableName;

六、分组数据
6.1建立分组
SELECT id, COUNT(*) AS id_count FROM TableName GROUP BY id;

6.2过滤数据
SELECT id, COUNT() AS id_count FROM TableName GROUP BY id HAVING COUNT() > 1;

七、子查询
7.1子查询作为过滤条件
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

7.2子查询作为检索数据
SELECT cust_name, (SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;

八、联表查询
8.1创建联结
SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

8.2多表联结
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 = 'RGAN01';

九、组合查询
9.1UNION组合前后查询
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All';

十、视图
10.1创建视图
CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num;