一、基本概念
数据库、表、列、行、主键、外键、关键字
二、检索
1.检索列
SELECT prod_name(id)(*)
FROM Products;
2.检索不同值
SELECT DISTINCT vend_id
FROM Products;
三、排序
1.单列
ORDER BY prod_name;
--此句必须位于末尾
2.多列
ORDER BY prod_price, prod_name; --按列名
ORDER BY 2, 3; --按列位置
3.指定排序方向
ORDER BY prod_price DESC, prod_name; --仅前者降序
四、过滤数据
1.where 子句
WHERE prod_price = 3;
操作符 =//<>/>=/…
2.范围值检查
WHERE prod_price BETWEEN 5 AND 10;
3.空值检查
WHERE prod_price IS NULL;
五、高级数据过滤
1.组合WHERE子句
AND操作符 OR操作符
WHERE vend_id = 'DLL01' AND prod_price <= 4;
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
求值顺序
用（圆括号）明确对操作符分组，保证顺序理想
2.IN操作符
WHERE vend_id IN ( 'DLL01', 'BRS01' );
3.NOT操作符
WHERE NOT vend_id = 'DLL01' ; --相当于<>
六、用通配符进行过滤
1，LIKE
LIKE是谓词而不是操作符
百分号%，星号*，表示任何字符出现任意次数
WHERE prod\_name LIKE 'Fish%';
WHERE prod\_name LIKE '%bean bag%';
下划线_,匹配单个字符
WHERE prod_name LIKE '_inch teddy bear';
方括号[]，匹配指定位置的一个字符
WHERE cust_contact LIKE '[JM]%'
LIKE '[^JM]%' or LIKE '[!JM]%'(否定)
2、技巧
尽量不要把它们用在搜索模式的开始处
不要过度使用
七、创建计算字段
1，计算字段
计算字段是运行时在SELECT语句内创建的。
2，拼接字段
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
去掉空格
TRIM() LTRIM() RTRIM()
去掉右边的空格
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
别名，是一个字段或值的替换名，用AS关键字赋予
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' AS vend_title
3，执行算数计算(+ - * /)
SELECT prod_id, quantity, item_price,
quantity*item_price AS expanded_price
八、使用函数处理数据
1，函数
与SQL 语句不一样，SQL 函数不是可移植的。
2，使用函数
文本处理函数
返回字符串旁边的字符 LEFT() RIGHT()
返回字符串的长度 LENGTH() or LEN()
转换字符串大小写LOWER() UPPER()
对字符串进行发音比较的转换 SOUNDEX()
日期和时间处理函数
WHERE DATEPART(yy, order_date) = 2018;(SQL Server)
WHERE YEAR(order_date) = 2018;(MySQL)
WHERE DATEPART('yyyy', order_date) = 2012;(Access)
数值处理函数
返回绝对值 ABS()
返回正余弦 SIN() COS()
返回平方/指数 SQRT() EXP()
九、汇总数据
1，聚集函数
返回单列平均值 AVG()
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01'
返回列的行数 COUNT()
包括NULL COUNT(*)
具有特定值 COUNT(column)
返回数的最值 MAX() MIN()
用于文本数据时，MAX()MIN()返回按该列排序后的最后一行/最前面的行
返回和 SUM()
SELECT SUM(quantity) AS items_ordered
FROM Order_num = 2005;
2.聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
注：DISTINCT不能用于Access，不能用于COUNT(*)，用于求最值无意义
3.组合聚集函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
十、分组数据
1，创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
按vend_id排序并分组数据，对每个vend_id而不是整个表计算num_prods
GROUP BY 子句必须出现在WHERE 子句之后，ORDER BY 子句之前。
2，过滤分组
HAVING 支持所有WHERE 操作符
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING子句过滤COUNT(*) >= 2（两个以上订单）的那些分组
注：使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤
3，分组与排序
ORDER BY
一般在使用GROUP BY 子句时，应该也给出ORDER BY 子句。以保证数据排序正确。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
Access 不允许按别名排序，可用实际的计算或字段位置替换
即ORDER BY COUNT(*), order_num 或ORDER BY 2, order_num
4.SELECT子句及其顺序
子 句 |说 明 |是否必须使用
SELECT |要返回的列或表达式 |是
FROM |从中检索数据的表 |仅在从表选择数据时使用
WHERE |行级过滤 | 否
GROUP BY |分组说明 | 仅在按组计算聚集时使用
HAVING |组级过滤 | 否
ORDER BY |输出排序顺序 | 否
十一、使用子查询
1.利用子查询进行过滤
SELECT cust_id
FROM Orders
WHERE order_num IN(SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
子查询总是由内向外处理。
2.作为计算字段使用子查询
从Customers表中检索顾客列表；
对于检索出的每个顾客，统计其在Orders表中的订单数目。
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语句中操作多个表，就应使用完全限定列名来避免歧义。
十二、联结表
1.关系表
分解信息，按类写表，互相关联，节省空间，方便管理
2.创建联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
使用完全限定列名
保证所有联结都有WHERE子句
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
以上语法为等值联结（内联结）
3.用联结优化子查询
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';
前两个关联联结中的表，后一个过滤产品RGAN01的数据。
十三、高级联结
1.使用表别名
缩短SQL语句，允许在一条SELECT语句中多次使用相同的表。
SELECT cust_name, cust_contact
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 = 'RGAN01';
表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端
2.使用不同类型的联结
1)自联结(self-join)
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。
2)自然联结(natural join)
自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。
事实上，我们迄今为止建立的每个内联结都是自然联结。
3)外联结(outer join)
联结包含了那些在相关表中没有关联行的行。
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
外联结必须指明左、右
LEFT OUTER JOIN是从FROM子句左边的表中选择所有行
4)全联结(full outer join)
检索两个表中的所有行并关联那些可以关联的行
3.使用带聚集函数的联结
检索所有顾客及每个顾客所下的订单数。
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
4.使用联结和联结条件
应该总是提供联结条件，否则会得出笛卡尔积
十四、组合查询
1.使用情境
在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据。
2.创建组合查询
2.1 使用UNION
在各条SELECT语句之间放上关键字UNION就行。
2.2 UNION规则
UNION中的每个查询必须包含相同的列、表达式或聚集函数。
列数据类型不一定完全相同，但必须兼容。
2.3 包含或取消重复的行
UNION从查询结果集中自动去除了重复的行。
想返回所有的匹配行可使用UNION ALL。
2.4 对组合查询结果排序
只能使用一条ORDER BY子句，且必须位于最后一句SELECT语句之后
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'
ORDER BY cust_name, cust_contact;
使用UNION可极大地简化负责的WHERE子句，简化从多个表中检索数据的工作。
十五、插入数据(INSERT)
1.数据插入
三种方式：插入完整的行，插入行的一部分，插入某些查询的结果
1.1插入完整的行
INSERT INTO Customers(cust_id,cust_name,cust_email)
VALUES('10086','Toy Land',NULL);
给出明确列，可以保证在表结构发生变化时语句仍有效。
1.2插入部分行
INSERT INTO Customers(cust_id,cust_name)
VALUES('10086','Toy Land');
省略某些列必须满足的条件：
定义为允许NULL值，或表定义中给出默认值。
1.3插入检索出的数据
INSERT SELECT语句，可以插入SELECT返回的多行
注：数据库不关心SELECT返回的列名，它使用的是列的位置。
2.从一个表复制到另一个表
用SELECT INTO导出数据
SELECT \*
INTO CustCopy
FROM Customers；
MySQL语法如下
CREATE TABLE CustCopy AS
SELECT \* FROM Customers；
注：不论从多少个表中检索数据，数据都只能插入到一个表中。
网友评论