MySQL入门
一、关于MySQL
MySQL（Structured Query Language）是一个关系型数据库管理系统，在数据库管理系统中“表（table）”表示某种特定类型数据的结构化清单，模式（schema）是关于数据库和表的布局及特性的信息，列（column）为表中的一个字段，数据类型（datatype） 为所容许的数据的类型，主键（primary key）是表中每一行可以唯一标识自己的一列或一组列。
- 注意：在选择主键时，不要在主键列中使用可能会更改的值。
二、检索数据
1.检索单个列
输入：SELECT prod_name
FROM products;
- 注意：多条SQL语句必须以分号(;)分隔，单条也可以使用；SQL语句不区分大小写。
2.检索多个列
输入：SELECT prod_id, prod_name, prod_price
FROM products;
image.png
- 注意：选择多列时，要在列名之间加上逗号，但最后一个列名不加
3.检索所有列
输入：SELECT *
FROM products;
4.检索不同的行
输入：SELECT DISTINCT vend_id
FROM products;
image.png
- 注意：DISTINCT关键字应用于所有列而不仅是前置它的列，不能部分使用DISTINCT
5.限制结果
输入：SELECT prod_name
FROM products
LIMIT 5; (不多于5行)
输入：SELECT prod_name
FROM products
LIMIT 5，5;（返回从行5开始的5行）
- 注意：检索出来的第一行为行0而不是行1，因此，LIMT 1，1 将检索出第二行而不是第一行；如果没有足够行，则返回能返回的那么多行；LIMT 2，3=LIMT 2 OFFSET 3
三、排序检索数据
1.排序数据
输入：SELECT prod_name
FROM products
ORDER BY prod_name;
image.png
2.按多个列排序
输入：SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name
image.png
3.指定排序方向
输入：SELECT prod_id,prod_price,prod_nam
FROM products
ORDER BY prod_price DESC;
image.png
如果是用多个列排序，第一个列用降序，第二个列用升序
输入：SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
image.png
- 如果想在多个列上进行降序排序，必须对每个列制定DESC关键字（升序为默认，ASC，ascending）
四、过滤数据
1.使用where子句
输入：SELECT prod_name,prod_price
FROM products
WHERE prod_price = 2.50;
WHERE 子句操作符
image.png
- 注意：WHERE子句中，如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号
2.范围值检查
输入：SELECT prod_name,prod_price
FROM products
WHERE pro_price BETWEEN 5 AND 10;
image.png
- 注意：数据匹配过滤或不匹配过滤时，不返回具有NULL的行。因此，在过滤数据时，需要验证返回数据中确实给出了被过滤列具有NULL的行。
3.AND操作符
输入：SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
4.OR操作符
输入：SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 OR prod_price <= 10;
- 在计算次序中的优先级：（）> AND > OR
5.IN操作符（指定条件范围）
输入：SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
- IN可以包含其他SELECT语句
6.NOT操作符（WHERE子句中的NOT只能用来否定它之后所跟的任何条件）
输入：SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
- MySQL仅支持使用NOT对IN、BETWEEN和EXISTS取反
五、用通配符进行过滤（通配符：用来匹配值的一部分的特殊字符）
1.LIKE操作符
LIKE指示MySQL，后跟的搜索模式利用通配符匹配而
不是直接相等匹配进行比较
2.百分号（%）通配符（%表示任何字符出现任意次数）
输入：SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘ jet%’;
image.png
-
通配符可在搜索模式中任意位置使用，并且可以使用多个通配符
输入：SELECT prod_id,prod_name
FROM products WHERE prod_name LIKE ‘%anvil%’;
image.png
- %可以匹配0个字符，但不能匹配NULL值
3.下划线（_）通配符（用途与%一样，但下划线只匹配单个字符而不是多个字符）
输入：SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil’;
image.png
- 通配符搜索的处理比其他搜索花的时间更长，所以不要把通配符用在搜索模式的开始处
六、用正则表达式过滤数据（正则表达式是用来匹配文本的特殊的串（字符集合））
1.基本字符匹配
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;
image.png
-
正则表达式中.000表示匹配任意一个字符
-
LIKE与REGEXP的区别：LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会被返回(除非使用通配符)；而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回
-
MySQL不区分大小写，若想区分大小写，可使用BINARY关键字：WHERE prod_name REGEXP BINARY ‘JetPack .000'
2.进行OR匹配
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000 | 2000’
ORDER BY prod_name;
- | 为正则表达式的OR操作符
3.匹配几个字符之一
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
-
[123]定义一组字符，表示1或2或3；[ ]是另一种形式的OR语句
-
要否定一个字符集，在集合的开始处放置一个，[123]，否定（1或2或3）
-
[1-9]表示范围1-9，.匹配任意字符，若要匹配特殊字符须用\为前导，\-表示查找-，\\表示查找\；正则表达式内具有特殊意义的所有字符都必须以这种方式转义。另外，\也用来引用元字符
image.png
- 通过正则表达式重复字符可以对匹配数目进行更强的控制
image.png
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)’
ORDER BY prod_name;
分析：正则表达式\\([0-9] sticks?\\)，\\(匹配(，[0-9]匹配任
意数字，sticks?匹配stick和sticks（s后的？使s可选，因
为？匹配它前面的任何字符的0次或1次出现)，\\)匹配）
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[[:dight:]]{4}’
ORDER BY prod_name;
image.png
分析：[:dight:]匹配任意数字，{4}确切地要求它前面的字符出现四次，所以[[:dight:]]{4}匹配连在一起的任意4位数字
4.定位符
image.png
输入：SELECT prod_name
FROM products
WHERE prod_name REGEXP’^[0-9\\.]’
ORDER BY prod_name
image.png
- LIKE和REGEXP的不同在于，：LIKE匹配整个串而REGEXP匹配子串，利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样
七、创建计算字段
1.拼接字段
拼接（concatenate，将值联结到一起构成单个值），这
个拼接的字段还需要用括号将vend_country括起来
SELECT Concat (vend_name,’(‘,vend_country,’)’)
FROM vendors
ORDER BY vend_name;
image.png
-
多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现，把SQL语句转换成MySQL语句时要记住这个区别
输入:SELECT Concat(RTrim(vend_name),’(‘,RTrim(vend_country),’)’)
FROM vendors ORDER BY vend_name; -
RTrim( )去掉串右边的空格，LTrim()去掉串左边的空格，Trim()去掉串左右两边的空格
2.别名
别名（alias）是一个字段或值的替换名，别名用AS关键字赋予
输入：SELECT Concat(vend_name),’(‘,RTrim(vend_country),’)’) AS
vend_title
FROM vendors
ORDER BY vend_name
image.png
3.执行计算
输入：SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 2005;
image.png
八、数据处理函数
1.文本处理函数
输入：SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
image.png
- Upper()是将文本转换为大写
image.png
image.png
输入：SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex(‘Y Lie’);
- WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值，SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法，也考虑了类似的发音音符和音节。
2.日期和时间处理函数
image.png
输入：SELECT cust_id,order_num
FROM orders
WHERE order_date = ‘2005-09-01’;
3.数值处理函数
image.png
九、汇总数据（汇总数据用到聚集函数，运行在行组上，计算和返回单个值的函数）
image.png
1.AVG()函数
输入：SELECT AVG(prod_price) AS avg_price
FROM products;
image.png
2.COUNT()函数
输入：SELECT COUNT(*) AS num_cust
FROM customers;
image.png
- 如果制定列名，则制定列的值为空的行被COUNT()函数斛律，但如果COUNT()函数中用*，则不忽略
3.MAX()函数
输入：SELECT MAX(prod_price) AS max_price
FROM products；
- 用于文本数据时，如果数据按相应的列排列，则MAX()返回最后一行
4.MIN()函数
MAX() MIN()函数都忽略为NULL的行
5.SUM()函数
输入：SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 2005;
输入：SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 2005
- SUM()函数忽略列值为NULL的行
十、分组数据
-
创建分组
输入：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; 
- 大部分类型的WHERE子句都可以用HAVING子句来替代，唯一的区别在于WHERE过滤行，而HAVING过滤分组。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
- 分组和排序
image.png
- SELECT子句顺序
image.png
image.png
十一、使用子查询
-
子查询过滤
输入：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2’);
-
在SELECT语句中，子查询总是从內向外处理
-
在WHERE子句中使用子查询，应保证SELECT语句具有与WHERE子句中相同数目的列
输入：SELECT cust_name， cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM orders ORDER BY cust_name; 
十二、联结表（联结分为：等值联结、自联结、自然联结和外部联结）
外键（foreign key），外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
笛卡尔积，由没有联结条件的表关系返回的结果为笛卡尔积。
-
创建等值联结（内部联结）
输入：SELECT vendor_name,prod_name,prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;  -
使用表别名
输入：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 = ’TNT2’; -
自联结
假如你发现某物品（ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产 的其他物品是否也存在这些问题。此查询要求首先找到ID为DTNTR的物品的供应商， 然后找到这个供应商生产的其他物品。 输入：SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’); 
使用自联结进行相同的查询：
输入：SELECT p1.prod_id,p1.prod_name
FROM products AS p1,product AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR’;
-
自然联结
无论何时对表进行联结，至少有一个列出现在不止一个表中，标准的联结返回所有的数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列都只返回一次。自然联结是这样一种联结， 你只能选择那些唯一的列，这一般是通过对表使用通配符（SELECT*），对其他表的列使用明确的子集来完成的。  -
外部联结
联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结 输入：SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; 
-
LEFT OUTER JOIN一个满足条件的行与另一个表的所有行进行匹配关联
-
on是关联条件，where是查询条件
-
带聚集函数的联结
输入：SELECT customers.cust_name, customers .cust_id, COUNT(orders.order_num) AS num_order FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;  
十三、组合查询
输入：SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);
相同的查询使用WHERE语句时：
输入：SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN(1001,1002)
十四、全文本搜索
-
全文本搜索
输入：SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’)  Match()指定被搜索的列 Against()指定要使用的搜索表达式 -
布尔文本搜索
输入：SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘heavy -rope* IN BOOLEAN MODE); 
十五、插入数据
-
插入行
SELECT INTO Customers VALUES（NULL， ‘Pep E. LaPew’, ‘100 Main Street’, ‘Los Angeles’, ‘CA’, ‘90046’ NULL, NULL); 更为安全的写法  -
插入多行
   -
插入检索出的数据
十六、更新和删除数据
-
更新数据
输入：UPDATE customers SET cust_email = ‘[element@163.com](mailto:element@163.com)’ WHERE cust_id = 1005; -
删除数据
输入：DELETE FROM customers WHERE cust_id = 10006;
十七、创建和操纵表
-
创建表
-
每个表只允许一个Auto_INCREMENT
-
可以指定默认值
- 各种引擎（外键不能跨引擎）
-
InnoDB，事务处理引擎，不支持全文本搜索；
-
MEMORY在功能等同于MyISAM，但由于数据储存在内存（不是硬盘）中，速度很快，适用临时表
-
MyISAM，高性能引擎，支持全文本搜索，不支持事务处理
-
更新表
输入：ALTER TABLE vendors ADD vend_phone CHAR(20); 输入：ALTER TABLE Vendors DROP COLUM vend_phone; 输入：RENAME TABLE customers TO customers;
十八、使用视图
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
-
创建视图
输入：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; 检索视图的语句与检索表格相同 -
重用视图
普通联结查询：  创建重用视图：  检索视图： 
十九、使用存储
存储过程简单来说，就是为以后的使用而保存起来的一条或多条MySQL语句的集合
变量，内存中一个特定的位置，用来临时存储数据
-
执行存储过程
输入：CALL productpricing(@pricelow, @pricehigh, @priceaverage);
- 所有MySQL变量都必须以@开始
-
创建存储过程
输入：CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price0 AS priceaverage FROM products; END; -
删除存储过程
输入：DROP PROCEDURE productpricing;
- 删除存储过程，没有使用后面的()
-
使用参数
-
记录集是不允许的类型，不能通过一个参数返回多个行和列
输入：CREATE PROCEDURE ordertotal( IN onumber INT, OUT ototal DECIMAL(8,2) ) BEGIN SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal; END ; 存储的完整过程如下：  
-
检查存储过程
输入：SHOW CREATE PROCEDURE ordertotal;
二十、使用游标
-
创建游标
输入：CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;
- 使用游标前，必须声明它，这个过程没有检索数据，只是定义要使用的SELECT语句
-
打开、关闭游标
输入：OPEN ordernumbers; 输入：CLOSE ordernumbers;
- 如果不明确关闭游标，MySQL将会在达到END语句时自动关闭它
-
使用游标数据
在游标被打开后，可以使用FETCH语句分别访问每一行 ，FETCH指定检索什么数据，检索出来的数据存储在什么地方
