MySQL基础知识

1.基本介绍

MySQL被划分为关系数据库管理系统（RDBMS）

• 数据库：用来存储信息的仓储。

  • 数据库里面的数据集都被组织成表
  • 每个表由多个行和列组成
  • 表中的每一行称为一条记录
  • 记录可以包含多项信息；表中的每一列都是一项信息
• 管理系统：是一个软件，我们可以通过它来增删查改记录
• 关系：能够通过查找两个表里的共同元素，将分别存在于两个表里的信息联系起来

2.查询操作

# 查询一列
SELECT prod_id FROM products

# 查询多列
SELECT prod_id, prod_name FROM products

# 查询所有
SELECT * FROM  products

# distinct查询一列，过滤相同的，即只查询不同的值
SELECT prod_id DISTINCT FROM products

# 查询从指定位置开始的指定列数的内容(limit指定数量，offset指定行,MySQL可以直接用limit)
SELECT prod_name FROM products limit 5  offset 5
SELECT prod_name FROM products limit 5, 5

3.排序操作

# 指定列排序
SELECT prod_name FROM products ORDER BY prod_name 

# 多个列排序
SELECT prod_name, prod_id FROM products ORDER BY prod_name, prod_id

# 按列位置排序
SELECT prod_name, prod_id FROM products ORDER BY 1,2

# 降序排序
SELECT prod_name FROM products ORDER BY prod_id DESC

4.过滤数据

# 查询两列，只返回prod_price=3.49的行
SELECT prod_name, prod_price From Products WHERE prod_price=3.49

# 根据运算的大小关系选出指定的行
SELECT prod_name, prod_price FROM Products WHERE prod_price < 10

# 列出所有不是供应商DLL01制造的产品（<>和 ！= 在MySQL中是通用的）
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01';

# BETWEEN用来查询介于两者之前的值，包括两端
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;

# 查询空值NULL而不是0
SELECT cust_name FROM CUSTOMERS WHERE cust_email IS NULL;

5.高级过滤

# AND操作符连接两个条件
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

# OR检索匹配任一条件的行。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，
就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

# 先执行后面AND连接的语句，vend_id = 'DLL01' OR (vend_id = ‘BRS01’ AND prod_price >= 10)
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’
AND prod_price >= 10;

# 改变执行顺序可以加括号
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = ‘BRS01’)
AND prod_price >= 10

# IN表示在每个列中，和OR的功能一样
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.用通配符进行过滤

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

# %表示任何字符出现任意次数
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

# _ 匹配单个字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

# 方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符
# MySQL不支持该语法
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

通配符的使用技巧：

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

7.计算字段

# expanded_price列是一个计算字段，此计算为quantity*item_price
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

# 返回 6
SELECT 3 * 2

#返回当前时间
SELECT Now()

8.函数

常用的文本处理函数：

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER()（Access使用LCASE()）	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）	将字符串转换为大写

# UPPER()将文本转换为大写
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name

# WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。（发音相同）
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

# YEAE() 根据指定字段条件找出指定的值
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012

常用的数值处理函数：

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

9.聚集函数

聚集函数（aggregate function） 对某些行运行的函数，计算并返回一个值。

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

# 求价格平均值
SELECT AVG(prod_price) AS avg_price
FROM Products;

# 指定条件下的平均值 
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

# COUNT（*）对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值
SELECT COUNT(*) AS num_cust
FROM Customers

# 对指定列中有值的行进行计数
SELECT COUNT(cust_email) 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 COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products

10.分组数据

# GROUP BY子句指示DBMS按vend_id排序并分组数据
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id

WHERE是过滤行，而HAVING过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

子句	说明
SELECT	要返回的列或表达式是
FROM	从中检索数据的表仅在从表选择数据时使用
WHERE	行级过滤否
GROUP BY	分组说明仅在按组计算聚集时使用
HAVING	组级过滤否
ORDER BY	输出排序顺序否