MySQLdb和pymysql区别

感谢51CTO的De8ug的教学

一、什么是MySQL

MySQL是DBMS数据库管理系统的一种，用来管理多个数据库的操作。

二、MySQL的安装,以MariaDB为例

1、在windows上安装数据库

        1、下载MySQL WorkBench，这个是数据库客户端的管理。
              MySQL :: MySQL Community Downloads         2、下载MariaDB Server，这个是操作数据库的可视化DBMS。安装时自动安装HeidiSQL，我们在这上操作数据库

2、在windows上登录使用

         1、方法一：在桌面直接点开HeidiSQL就可以

         2、方法二：在菜单--搜索里输入MySQL Client进入命令窗口，然后在这里输入数据库密码就可以进入管理了。注意每次操作数据库后在HeidiSQL里看不到变化的时候，点击HeidiSQL页面上的刷新就可以了。

3、在linux上安装

        1、配置仓库（因为默认的仓库里没有新的mariadb源）;vim /etc/yum.repos.d/Mariadb.repo新建文件。然后在文件里写上以下内容：

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64/
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

        2、然后ping baidu.com查看一下连接是否正常，若不正常则看一下设置和ip等
        3、yum check-update更新一下源
        4、下载安装yum install MariaDB-server MariaDB-client
        5、安装完成后，可以用systemctl status mysql查看状态,systemctl start mysql开启sql

4、在linux上登录

        1、mysql -u root连接数据库。Ctrl+C退出
        2、mysqladmin -u root password "xxxxx"，设置密码。此时mysql -u root就登录不进去了，就需要mysql -u root -p xxxxx来登录
        3、mysql -h ip地址 -u root -p xxxxx，可以指定主机的数据库登录，-hlocalhost访问主机数据库

5、访问远程的数据库--示例：用windows本地MySQL Client访问linux上的数据库

        1、如果权限允许，在windows系统上的MySQL Client用mysqladmin -uroot password "xxxxx"就可以连接了，

        授予权限：在数据库所在的系统上进入数据库设置。
        设置方法：GRANT语句可以授予权限。*.*表示所有的数据库；root表示用户名；‘%’表示所有的IP地址；ON后面是数据库；TO后面是被授予的用户；IDENTIFIED BY后面是数据库密码。授予之后需要刷新一下。若还不能登陆，用netstat -anp |grep 3306查看3306端口是否被占用，systemctl stop firewalld.service关闭防火墙等方法。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXXXXX' WITH GRANT OPTION;
意思是：root用户的所有IP地址可以通过XXXXXX这个验证密码连接本系统的所有数据库，且开放所有的权限。
刷新：FLUSH PRIVILEGES;

三、MySQL的基本使用

- 连接/退出    mysql -h主机名 -u用户 -p密码  --------- 这个语句是用在命令行窗口时登录用的

- 查看数据库：SHOW DATABASES;
- 新建数据库：CREATE DATABASE `数据库名`; 注释：db2两边是反引号`，tab键上面的那个，目的是为了把数据库的名和SQL里自带的一些关键词区分开。
- 删除数据库：DROP DATABASE 数据库名;
- 使用数据库：USE 数据库名;
- 查看数据表：SHOW TABLES;
- 创建数据表：CREATE TABLE 表名 （列名，数据类型);
- 删除数据表：DROP TABLE 表名¶
- 清空数据表：TRUNCATE TABLE 表名

四、操作示例与讲解

1、创建数据表示例：

-- ---------------------  [1]
-- Create Products table      
-- ---------------------
CREATE TABLE Products    [2]   
(
  prod_id [3]   char(10) [4]   NOT NULL,[5][6]    
  vend_id    char(10)    NOT NULL,    
  prod_name    char(255)    NOT NULL,   
  prod_price   decimal(8,2)[7]  NOT NULL,   
  prod_desc   text[8]    NULL
); [9][10]

讲解：

1、--  两个小杠杠是注释的意思
2、创建表
3、列名：prod_id:一般表格都有id，为了我们更好掌握，一般都是自己写一个id
4、数据类型：char():括号里写的10是数据长度。表示10位
5、NOT NULL：表示不能为空，必须有值。NULL：表示可以为空。
6、有时NOT NULL的位置是AUTO_INCREMENT 自动增长的意思。
7、decimal:小数，(8,2)指长度一共8位，小数点后面有2位。
8、text：文本，一般不限制长度。
9、注意最后有分号。
10、以前的版本还要在后面加上ENGINE=INNODB表示引擎的意思

2、修改表结构----添加主键示例：

-- ---------------
-- Define primary keys
-- ---------------
ALTER TABLE Products ADD PRIMARY KEY(prod_id);
ALTER TABLE OrderItems ADD PRIMARY KEY(order_num,order_item);

讲解：

1、ALTER TABLE 表名 ADD PRIMARY KEY(主键名);
2、每一条语句后有分号。
3、因为每一条语句都非常的相似，所以这时候就可以联系其他语言来循环一下。

3、修改表结构----添加外键示例：

-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);

讲解：

1、ALTER TABLE 要添加的表名 ADD CONSTRAINT FK_要添加的表名_相关联的表明 FOREIGN KEY (外键名) REFERENCES 相关联的表名 (相关联的表名的属性名);

4、填充（插入）数据示例：

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id,vend_id,prod_name.prod_price,prod_desc)
VALUES('BRO1','BRSO1','8 inch teddy bear',5.99,'8 inch teddy bear,comes with cap and jacket');

讲解：

1、INSERT INTO 表名(属性名)   注意：属性名可以不喝表里的顺序一致；
2、ALUES(值)   注意：值得顺序要和前面的属性名顺序一一对应；字符串数据需要加单引号
3、最后以分号;结尾

五、数据库的关联

数据库表格设计：避免数据重复
一类数据一个表，相互之间有关联
主键，外键关联多个表
了解连接（联结，join）方法

不同表之间的联结关系：

（1）自然联结：重复列合成一列。

（2）内联结：自然联结下，仅选择数值相等时。

（3）外联结：保留任一方的所有行，把另一方没有的行设置为空值。分为左右两方向。

（4）笛卡儿积：第一个表中的每一行与第二个表中的每一行配对。

六、练习：参考《SQL必知必会》

1、检索属性列：

-- 检索一列属性
SELECT prod_name
FROM products;

-- 检索多列属性：
SELECT prod_name, prod_id, prod_price
FROM products;

-- 检索所有列属性（慎用，因为表达的话可能会卡住）：
SELECT *
FROM products;

-- 检索不同的值，即去重：
SELECT DISTINCT vend_id
FROM Products;

2、限制输出的结果（LIMIT, OFFSET）：

-- 输出结果不超过5行
SELECT prod_name
FROM Products
LIMIT 5;

-- 输出结果从第2行数据开始往后的不超过5条结果。因为第一条数据是第0行，所以实际输出的是从第3个开始的最多不超过5个数据
SELECT prod_name
FROM Products
LIMIT 2 OFFSET 5;

-- 也可以这么写
SELECT prod_name
FROM Products
LIMIT 2,5;

3、将结果排序（ORDER BY, DESC）：

-- 排序参照一列数据
SELECT prod_name, prod_id, prod_price
FROM products
ORDER BY prod_price;

-- 排序参照多列，先按照价格排序，价格相同时按照名字排序
SELECT prod_name, prod_id, prod_price
FROM products
ORDER BY prod_price, prod_name;

-- 按照参照位置排序，即先按价格排序，再按照名字排序
SELECT prod_name, prod_id, prod_price
FROM products
ORDER BY 3, 1;

-- 指定排序方向，默认为升序排序，若想指定降序排序，则加上DESC，如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
SELECT prod_name, prod_id, prod_price
FROM products
ORDER BY 3 DESC, 2 DESC;

4、过滤数据，即加条件：WHERE在FROM后，ORDER BY在WHERE之后

-- 输出价值为3.49的结果
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

-- ！=不等于的用法
SELECT vend_id, prod_name
FROM Products
WHERE vend_id != 'DLL01';

-- <>不等于的用法
SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

-- 添加范围，在...和...之间
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

-- 无值/空值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
--这条语句返回所有没有价格（空prod_price字段，不是价格为0）的产品，由于表中没有这样的行，所以没有返回数据
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

5、高级过滤数据（AND, OR ,IN ,NOT）:

-- AND操作符，要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

-- OR操作符，指示DBMS检索匹配任一条件的行。
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

-- AND 和 OR 连用时，AND优先级高于OR，若想改变优先级，可以加括号()

-- 由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

-- 由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的价格为10美元以上的所有产品
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

-- IN操作符，用来指定条件范围，范围中的每个条件都可以进行匹配，与OR操作符效果相类似
-- IN操作符的优点：
    在有很多合法选项时，IN操作符的语法更清楚，更直观。
    在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
    IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。
    IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。第11课会对此进行详细介绍。
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

-- NOT操作符,不是的意思， 与!=和<>效果类似
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

6、用通配符进行过滤（LIKE,REGEXP，%， _，[]，\^）：利用通配符，可以创建比较特定数据的搜索模式。

通配符（wildcard）：用来匹配值的一部分的特殊字符。

搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。

谓词（predicate）：操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

-- 百分号（%）通配符：在搜索串中，%表示任何字符出现任意次数
-- 查找prod_name里以Fish开头的数据
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

区分大小写

根据DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则'fish%'与Fish bean bagtoy就不匹配。
搜索模式'%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它之前或之后出现什么字符。
-- 找出以F起头、以y结尾的所有产品 
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

-- 下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。想匹配几位就写几个_。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

-- 方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。注意是一个字符
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[J]%'
ORDER BY cust_contact;

-- 注意;在MariaDB里修改了一些规则，LIKE后的搜索模式不能加[]，而是用REGEXP,此时就不用%了，且不区分大小写的
-- 这个语句是
SELECT cust_contact
FROM customers
WHERE cust_contact REGEXP '[JM]'

-- 前缀字符^（脱字号）来否定。例如，下面的查询匹配不以J或M起头的任意联系人名（与前一个例子相反）：
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

-- NOT通配符，也可以使用NOT操作符得出相同的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法：
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

6、拼接字段：将值联结到一起（将一个值附加到另一个值）构成单个值（Concat，RTRIM , LTRIM）。

-- 注意：SELECT语句可以很好地拼接地址字段。但是，这个新计算列没有名字，它只是一个值，客户端四没有办法引用的，所以经常搭配AS关键字
SELECT CONCAT(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;

-- 使用别名：别名（alias）是一个字段或值的替换名。别名用AS关键字，可以自定义。
SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title
FROM Vendors
ORDER BY vend_name;

-- 使用RTRIM()函数去掉值右边的所有空格。
-- 使用LTRIM()函数去掉值左边的所有空格。
SELECT Concat(RTRIM(vend_name) , ' _' , RTRIM(vend_country))
FROM Vendors
ORDER BY vend_name;

-- 执行算数计算：
SELECT prod_id, quantity, item_price,order_num,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20008;

7、使用函数处理数据

①、提取字符串的组成部分

Access使用MID()；DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()；MySQL和SQL Server使用SUBSTRING()

②、数据类型转换

Access和Oracle使用多个函数，每种类型的转换有一个函数；DB2和PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用CONVERT()

③、取当前日期

Access使用NOW()；DB2和PostgreSQL使用CURRENT_DATE；MariaDB和MySQL使用CURDATE()；Oracle使用SYSDATE；SQL Server使用GETDATE()；SQLite使用DATE()
-- 查找order_date为2012的order_num列
SELECT order_num
FROM orders
WHERE YEAR(order_date) = 2012;

④、常用的文本处理函数

LEFT()（或使用子字符串函数）
    返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）
    返回字符串的长度
LOWER()（Access使用LCASE()）
    将字符串转换为小写
LTRIM()
    去掉字符串左边的空格
RIGHT()（或使用子字符串函数）
    返回字符串右边的字符
RTRIM()
    去掉字符串右边的空格
SOUNDEX()
    返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）
    将字符串转换为大写
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较
MySQL和MariaDB具有各种日期处理函数，但没有DATEPART()。MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份：

⑤、常用数值处理函数

ABS()
    返回一个数的绝对值
COS()
    返回一个角度的余弦
EXP()
    返回一个数的指数值
PI()
    返回圆周率
SIN()
    返回一个角度的正弦
SQRT()
    返回一个数的平方根
TAN()
    返回一个角度的正切

8、聚集函数：确定表中行数（或者满足某个条件或包含某个特定值的行数）；获得表中某些行的和；找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。上述例子都需要汇总表中的数据，而不需要实际数据本身。

SQL聚集函数
    AVG()
        返回某列的平均值。AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
    COUNT()
        返回某列的行数。（1）使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值；（2）使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
    MAX()
        返回某列的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。MAX()函数忽略列值为NULL的行。
    MIN()
        返回某列的最小值
    SUM()
        返回某列值之和

-- 示例：计算vend_id为DLL01的prod_price的平均值
-- 注意：只用于单个列
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

-- 聚集不同值：DISTINCT
-- 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

-- 组合聚集函数：
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;

9、分组数据：两个新SELECT语句子句：GROUP BY子句和HAVING子句。

①、创建分组：GRUOP BY

-- GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
-- 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。
-- GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

-- 将数据按照vend_id分组
-- 注意此时得到的信息是分组后每组的第一个值。
SELECT vend_id, prod_id
FROM Products
GROUP BY vend_id;

-- 将数据按照vend_id分组，并返回每组的数目。
SELECT COUNT(*) AS num_prod, vend_id
FROM Products
GROUP BY vend_id;

②、过滤分组：HAVING。所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

-- 从products数据表里查找价格大于等于4的记录并按vend_id分组，然后输出记录大于等于2的分组的vend_id和记录数。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

③、分组和排序：ORDER BY与GROUP BY

ORDER BY
    对产生的输出排序
    任意列都可以使用（甚至非选择的列也可以使用）
    不一定需要
GROUP BY
    对行分组，但输出可能不是分组的顺序
    只可能使用选择列或表达式列，而且必须使用每个选择列表达式
    如果与聚集函数一起使用列（或表达式），则必须使用

-- 从orderitems数据表中对order_num进行分组，然后过滤出来记录大于等于3条的组，然后根据items和orde_num排序，最后输出ordr_num和记录数。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

10、子查询，实际上就是嵌套，在原有的语句里嵌套了有一个新的SELECT语句。实际使用时由于性能的限制，不能嵌套太多的子查询。

①、 利用子查询进行过滤

-- 每个订单包含订单编号、客户ID、订单日期，在Orders表中存储为一行。顾客的实际信息存储在Customers表中。货品清单在order_items表里。
-- 列出订购物品编号为RGAN01的所有顾客
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
    WHERE prod_id = 'RGAN01');

②、作为计算字段使用子查询

-- 显示Customers表中每个顾客的订单总数
SELECT cust_name, cust_state, (SELECT COUNT(*)
                        FROM Orders
                        WHERE Orders.cust_id = Customers.cust_id
                      ) AS orders
FROM Customers
ORDER BY cust_name;

11、联结表：联结是利用SQL的SELECT能执行的最重要的操作。

DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

①、联结类型：

内联结（INNER JOIN）。
外联结：左外联结（LEFT JOIN），右外联结（RIGHT JOIN）,完全联结（FULL JOIN）

②、创建联结

-- 最大的差别是所指定的两列（prod_name和prod_price）在一个表中，而第三列（vend_name）在另一个表中。
-- 需要使用完全限定列名
-- 要保证所有联结都有WHERE子句
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

③、笛卡儿积（cartesian product）：又称为叉联结

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

④、等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）

-- 这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
-- INNER JOIN 与 JOIN效果相同，是内连接的意思。
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

⑤、SQL不限制一条SELECT语句中可以联结的表的数目。

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';

-- 上面的语句和下面得语句效果一样
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 = 'RGAN01'));

12、创建高级联结（如何使用表别名，如何对被联结的表使用聚集函数。）

①、使用表别名（AS）：优点：缩短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';

②、使用不同类型的联结：。

-- 假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。下面是解决此问题的一种方法：
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                FROM Customers
                WHERE cust_contact = 'Jim Jones');

-- 查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次          
-- 以下的语句和上面的语句效果一样：
-- 在自己的表里联结查找称为自联结
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';

③、外联结：事实上，我们迄今为止建立的每个内联结都是自然联结；联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。外联结的类型有两个：左外联结（ LEFT OUTER JOIN）和右外联结（ RIGHT OUTER JOIN）。有的数据库会直接写为LEFT JOIN 和 RIGHT JOIN

LEFT JOIN 关键字会从左表 那里返回所有的行，即使在右表 中没有匹配的行。

RIGHT JOIN 关键字会从右表 那里返回所有的行，即使在左表 中没有匹配的行。

-- 下面的SELECT语句给出了一个简单的内联结。它检索所有顾客及其订单：
SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

-- 外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTERJOIN从FROM子句左边的表（Customers表）中选择所有行。

左外联结和右外联结：之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

④、使用带聚集函数的联结：聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。

-- 检索所有顾客及每个顾客所下的订单数：
-- SELECT语句使用INNER JOIN将Customers和Orders表互相关联。GROUP BY子句按顾客分组数据
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;

-- 使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单。
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

13、组合查询：SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

①、主要有两种情况需要使用组合查询：

在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据。

②、组合查询和多个WHERE条件

多数情况下，组合相同表的两个查询所完成的工作与具有多个WHERE子句条件的一个查询所完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询，在下面可以看到这一点。

③、创建组合查询

-- 需要Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的Fun4All。
-- 单条语句实现：
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

-- union组合语,注意union组合的几个sql语句不加分号;
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';

-- 给出使用多条WHERE子句而不是UNION的相同查询：
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
OR cust_name = 'Fun4All';

-- UNION ALL：显示全部匹配行
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

④、UNION关键字的相关知识;

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION从查询结果集中自动去除了重复的行；换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。这个特性是UNION的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用UNIONALL而不是UNION。
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后，DBMS会自动将OEDER BY排序SELECT所有的结果

-- ORDER BY举例： 
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;

14、数据插入：INSERT用来将行插入（或添加）到数据库表。使用INSERT语句可能需要客户端/服务器DBMS中的特定安全权限。在你试图使用INSERT前，应该保证自己有足够的安全权限。

①、插入有几种方式：插入完整的行；插入行的一部分；插入某些查询的结果。

-- 向customers数据表里插入一条信息
-- 如果某列没有值，如上面的cust_contact和cust_email列，则应该使用NULL
INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
-- 虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。编写INSERT语句的更安全（不过更烦琐）的方法如下：其优点是，即使表的结构改变，这条INSERT语句仍然能正确工作。因为给出了列名，所以插入结果仍然正确：
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
-- 省略的列必须满足以下某个条件。该列定义为允许NULL值（无值或空值）。在表定义中给出默认值。这表示如果不给出值，将使用默认值。

②、INSERT还存在另一种形式，可以利用它将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT。INSERT通常只插入一行。要插入多行，必须执行多个INSERT语句。INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。

-- 这个例子从一个名为CustNew的表中读出数据并插入到Customers表。
INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

15、处理数据：UPDATE， DELETE，时刻注意WHERE过滤条件。

①、更新数据（UPDATE）：基本的UPDATE语句由三部分组成：要更新的表；列名和它们的新值；确定要更新哪些行的过滤条件。

-- 更新customers表里的cust_email属性值
-- 没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行。
UPDATE Customers
SET cust_email = '[e-mail]'
WHERE cust_id = '1000000005';

-- 在更新多个列时，只需要使用一条SET命令
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = '[e-mail]'
WHERE cust_id = '1000000006';

②、删除数据（DELETE，TRUNCATE TABLE）：

-- 从Customers表中删除一行
DELETE FROM Customers
WHERE cust_id = '1000000006';

-- 要删除莫一列：
-- 可设置它为NULL（假如表定义允许NULL值）。其中NULL用来去除cust_email列中的值。这与保存空字符串很不同（空字符串用''表示，是一个值），而NULL表示没有值。没有WHERE的话是删除表里所有列
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

-- 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

16、视图：是一个虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。可以认为像python封装一样把查询语句封装起来。

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';此查询用来检索订购了某种产品的顾客。任何需要这个数据的人都必须理解相关表的结构，知道如何创建查询和对表进行联结。检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。假如可以把整个查询包装成一个名为ProductCustomers的虚拟表，则可以如下轻松地检索出相同的数据：
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
这就是视图的作用。ProductCustomers是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询（与上面用以正确联结表的相同查询）。

①、为什么使用视图？

重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

②、创建视图（CREATE VIEW）：

-- 创建ProductCustomers视图
-- 这条语句创建一个名为ProductCustomers的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行SELECT * FROM ProductCustomers，将列出订购了任意产品的顾客
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;

-- 也可以拼接字段：

七、索引：The Essentials of an Index - MariaDB Knowledge Base

1、索引的概念：索引是允许更快地检索记录的一种优化方法。使用B+ tree或者hash算法，建立了数据与地址的对应关系

2、索引的作用？索引为出现在索引列中的每个值创建一个记录。索引包含了排序的数据，和一个指向原始数据的链接。在查询数据时，避免了一行行的进行查找而加快速度。

3、索引的类型

Primary(主键)
Unique（数据唯一，但可以为NULL） 例如员工的编码，就需要唯一确定，不可以重复
Multiple-column Indexes（多列合作当主键）

4、索引的操作

-- 可以在创建表的时候确定主键，确定唯一值，
CREATE TABLE Employees (
    ID TINYINT(3) UNSIGNED NOT NULL,
    First_Name VARCHAR(25) NOT NULL，
    Employee_Code VARCHAR(25) NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE KEY (Employee_Code)
)

-- 也可以在之后的操作里添加
ALTER TABLE Employees ADD PRIMARY KEY(ID)
ALTER TABLE Employees ADD UNIQUE empcode(Employee_Code)
(ADD UNIQUE之后的这个名字可以随便起，但是在之后的括号里的名字是唯一确定的，需要对应写)