MySQL必知必会总结

<MySQL必知必会>知识点总结：

1. 选择数据库相关命令：

USE crashcourse;
SHOW DATABASE;
SHOW TABLES;
SHOW COLUMNS FROM customers;
SHOW STATUS;
SHOW CREATE DATABASE;
SHOW CREATE TABLES;
SHOW GRANTS;
SHOW ERRORS;
SHOW WARNINGS;

2. 检索数据库：

SELECT prod_name
FROM products;

SELECT prod_id, prod_name, prod_price
FROM products;

SELECT *
FROM products;

SELECT DISTINCT vend_id
FROM products;

不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。

SELECT prod_name
FROM products
LIMIT 5;

此语句使用SELECT 语句检索单个列。LIMIT 5指示MySQL返回不多于5行。

SELECT prod_name
FROM products
LIMIT 5, 5;

LIMIT 5, 5 指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。

MySQL 5支持LIMIT 的另一种替代语法，LIMIT 4 OFFSET 3 意为从行3开始取4行，就像LIMIT 3, 4 一样。

使用完全限定的表名：

SELECT products.prod_name
FROM products;

3. 排序检索数据

SELECT prod_name
FROM products
ORDER BY prod_name;

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

SELECT prod_id, prod_price, prod_name
FROM products

ORDER BY prod_price DESC, prod_name;

DESC关键字只应用到直接位于其前面的列名。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC
LIMIT 1;

在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。否则会产生错误信息。

4. 过滤数据

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.5;

WHERE子句操作符支持=, <>, !=, <, <=, >, >=, BETWEEN操作符。

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

NULL 无值，它与字段包含0， 空字符串或仅仅包含空格不同。IS NULL子句用来检查具有NULL值的列。

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

5. 数据过滤

组合WHERE子句，AND操作符， OR操作符。

计算次序的问题，SQL在处理OR操作符之前，优先处理AND操作符。

SELECT prod_name, prod_price
FROM products
WEHRE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

IN 操作符， 用来指定条件范围，范围内的每个条件都可以进行匹配。IN取合法值的由逗号分隔清单，全都括在圆括号内。

SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

NOT 操作符， 用来否定后跟条件的关键字。

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

6. 用通配符进行过滤

百分号（%）通配符

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

将检索任意以jet开头的词.%告诉MySQL接受jet之后的任意字符，不管它有多少字符。

需要注意的是，%可以匹配0个字符。

下划线(_) 通配符
下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

a) 不要过度使用通配符。如果其他操作符能达到相同的效果，应该使用其他操作符。

b) 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

7. 用正则表达式进行搜索

基本字符匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

LIEK与REGEXP之间有一个重要的差别：LIKE匹配整个列，如果被匹配的文本仅在列值中出现，LIKE将不会找到它，相应的行也不会被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP就会找到它，相应的行将被返回。

进行OR匹配，使用|。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

匹配几个字符之一

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

[123]也就是[1|2|3]的缩写。

匹配特殊字符，可以用\\为前导。\\-表示查找-，\\.表示查找.。

8. 创建计算字段

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;

Concat()拼接串，即把多个串联接起来形成一个较长的串，各个串之间用逗号分隔。

Trim函数：RTrim去除串右边的空格，LTrim去除串左边的空格，以及Trim去除左右两边的空格。

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

执行算术计算

SELECT prod_id,quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

9. 使用数据处理函数

SELECT vend_name, Upper(vend_name) AS vend_upcase
FROM vendors
ORDER BY vend_name;

常用的文本处理函数包括Left(), Length(), Locate(), Lower(), LTrim(), Right(), RTrim(), Soundex(), SubString(), Upper()。

常用的数值处理函数包括Abs(), Cos(), Exp(), Mod(), Pi(), Rand(), Sin(), Sqrt(), Tan()。

10. 聚集函数

SQL聚集函数包括AVG(), COUNT(), MAX(), MIN(), SUM()。

COUNT()函数有两种使用方式:

a) 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的空值(NULL），还是非空值。

b) 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

MAX函数返回指定列中的最大值。MAX要求指定列名。

对非数值数据使用MAX()：虽然MAX（）一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MIN()返回第一行。

SUM函数将忽略列值为NULL的行。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

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;

11. 分组数据

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

GROUP BY可以包含任意数目的列。如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。而且要求列出的每个列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句给出。如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。GROUP BY必须出现在WEHRE子句之后，ORDER BY子句之前。

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

HAVING支持所有WEHRE操作符。用来过滤分组。过滤是基于分组聚集值而不是特定行值的。它与WHERE的区别在于，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

SELECT子句及其顺序

SELECT => FROM => WHERE => GROUP BY => HAVING => ORDER BY =>LIMIT 

12. 使用子查询

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 = 'TNT2'));

SELECT cust_name,
             cust_state,
             (SELECT COUNT(*)
              FROM orders
              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

13. 组合查询

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

UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。

UNION中的每个查询必须包含相同的列，表达式或聚集函数。

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

UNION默认行为是去除了重复的行。如果不要求去除重复的行，可以用UNION ALL。

后加ORDER BY 可以进行查询结果的排序。

14. DELETE 表数据

如果想从表中删除所有的行，不要使用DELETE，可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际上删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

15. 数据库对象的基本操作

MySQL用户帐户和信息存储在名为mysql的MySQL数据库中。

USE mysql;
SELECT user FROM user;

将会所有用户帐号列表。

创建用户帐户

CREATE USER ben IDENTIFIED BY 'xxxx';

RENAME USER ben TO bforta;  // 5.0之后支持。
DROP USER bforta;

新创建的用户帐号没有访问权限。它们能登陆MYsql，但不能看到数据，不能支持任何数据库操作。

想要看到用户帐户的权限，可以使用SHOW GRANTS FOR.

SHOW GRANTS FOR bforta;

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

1) 要授予的权限；

2) 被授予访问权限的数据库或表；

3) 用户名。

GRANT SELECT ON $dbname.* TO bforeta;

授予访问权限。

GRANT的反操作是REVOKE， 用它来撤销特定的权限。

REVOKE SELECT ON $dbname.* FROM bforta;

而且被撤销的权限必须存在，否则将会出错。

GRANT和REVOKE可在几个层次上控制访问权限：

1) 整个服务器，使用GRANT ALL和REVOKE ALL

2) 整个数据库，使用ON database.*;

3) 特定的表，使用ON database.table;

4) 特定的列；

5) 特定的存储过程。

更改口令：

SET PASSWORD FOR bforta = Password("xxxx");

如果不指定用户名，SET PASSWORD就是为当前用户更改口令。

DROP永久地删除数据库对象(表，视图，索引等）。

DROP DATABASE|INDEX|PROCEDURE|TABLE|TRIGGER|USER|VIEW itemname;

INSERT 给表增加一行。

INSERT INTO tablename [(columns, ...)]

VALUES(values, ...);

INSERT INTO tablename [(columns, ...)]

SELECT columns, ... FROM tablename, ...

[WHERE ...];

SELECT 用于从一个或多个表（视图）中检索数据。

SELECT columnname, ...

FROM tablename, ...

[WHERE ...]

[UNION ...]

[GROUP BY ...]

[HAVING ...]

[ORDER BY ...];

UPDATE更新表中一行或多行。

UPDATE tablename

SET columnname = value, ...

[WHERE ...];

16. MySQL的基本数据类型

串数据类型

CHAR 1-255个字符的定长串，长度必须在创建时指定，否则MYSQL会认为是1.

ENUM 接受最多64k个串组成的一个预定义集合的某个串

LONGTEXT 与TEXT相同，但最大长度为4GB

MEDIUMTEXT 与TEXT相同，但最大长度为16M

SET 接受最多64K个串组成的一个预定义集合的零个或多个串

TEXT 最大长度为64K的变长文本

TINYTEXT 与TEXT相同，但最大长度为255字节

VARCHAR 长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可纯初0到n个字符的变长串(n<=255)

数值数据类型

BIT 位字段，1～64位

BIGINT 整数值

BOOLEAN 布尔标志，或者为0或者为1.

DECIMAL 精度可变的浮点值

DOUBLE 双精度浮点值

FLOAT 单精度浮点值

INT 整数值

MEDIUMINT 整数值

REAL 4字节的浮点值

SMALLINT 整数值，2字节

TINYINT 整数值，1字节。

(可支持unsigned 类型)

日期和时间数据类型

DATE 

DATETIME

TIMESTAMP 功能与DATETIME相同，但范围较小

TIME

YEAR

二进制数据类型

BLOB 最大长度为64KB

MEDIUMBLOB 最大长度为16MB

LONGBLOB 最大长度为4GB

TINYBLOB 最大长度为255字节