示例脚本
创建数据库
- 创建
- 查看创建的信息
选择数据库
检索数据
排序检索数据
过滤数据
计算字段
分组
子查询
- 过滤
- 计算字段
联结
组合
全文搜索
- 文本搜索
- 布尔
插入数据
更新数据
删除数据
创建表
视图
事务
安全管理
- 权限
- 更改口令

示例脚本

创建数据库

创建

CREATE DATABASE IF NOT EXISTS crashcourse default CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看创建的信息

SHOW CREATE DATABASE crashcourse;

+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                                                       |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| crashcourse | CREATE DATABASE `crashcourse` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+

选择数据库

显示所有数据库

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| crashcourse        |
| information_schema |
| mismatch           |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+

使用数据库

# USE database
USE crashcourse;

Database changed

获取表

SHOW TABLES;

+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| productnotes          |
| products              |
| vendors               |
+-----------------------+

显示列

# SHOW COLUMNS FROM table;
# DESCRIBE customers;
SHOW COLUMNS FROM customers;
DESCRIBE customers;

+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+

检索数据

检索单个列

SELECT prod_name FROM products;

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

检索多个列

SELECT prod_id, prod_name, prod_price FROM products;

+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+

检索所有列

SELECT * FROM products;

+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+

检索不同行

SELECT DISTINCT vend_id FROM products;

+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+

限制结果

SELECT prod_name FROM products LIMIT 5;

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+

SELECT prod_name FROM products LIMIT 5, 5;
SELECT prod_name FROM products LIMIT 5 OFFSET 5;

+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+

使用限定的表名

SELECT products.prod_name FROM crashcourse.products;

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

排序检索数据

排序

SELECT prod_name FROM products ORDER BY prod_name;

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

多个列排序

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

+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
| FU1     |       3.42 | Fuses          |
| SLING   |       4.49 | Sling          |
| ANV01   |       5.99 | .5 ton anvil   |
| OL1     |       8.99 | Oil can        |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| DTNTR   |      13.00 | Detonator      |
| ANV03   |      14.99 | 2 ton anvil    |
| JP1000  |      35.00 | JetPack 1000   |
| SAFE    |      50.00 | Safe           |
| JP2000  |      55.00 | JetPack 2000   |
+---------+------------+----------------+

指定排序方向

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

+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+

过滤数据

过滤

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+

其它

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuces';
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
SELECT vend_id, prod_price FROM products WHERE vend_id <> 1003;
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
SELECT cust_id FROM customers WHERE cust_email IS NULL;

注意：
匹配过滤或匹配不过滤不返回具有 NULL 的行

组合

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 100;
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 OR vend_id = 1002;
SELECT prod_id, prod_price, prod_name FROM products WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price >= 10;
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id IN (1002, 1003);
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

通配符

% 匹配 0~多个字符

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

_ 匹配单个字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

注意事项：
不过度使用；
尽量不放在搜索开始处

正则

SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
# . 匹配单个字符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
# binary 区分大小写
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP BINARY 'JetPack .000' ORDER BY prod_name;
# | or
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
# [] 匹配几个字符之一，^ 否定
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
# 转义
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY prod_name;

字符类
[:alnum:] 任意字母和数字，同 [a-zA-Z0-9]
[:alpha:] 任意字符，同 [a-zA_Z]
[:blank:] 空格和制表，同 [\t]
[:cntrl:] ASCII 控制字符（ASCII 0~31, 127）
[:digit:] 任意数字，同 [0-9]
[:print:] 任意可打印字符
[:graph:] 同 [:print:] 但不包括空格
[:lower:] 任意小写字母，同 [a-z]
[:upper:] 任意大写字母，同 [A-Z]
[:punct:] 既不在 [:alnum:] 又不在 [:cntrl:] 中的任意字符
[:space:] 包括空格在内的任意空白符，同 [\f\n\r\t\b]
[:xdigit:] 任意十六进制数字，同 [a-fA-F0-9]

匹配多个实例
元字符

*	0个或多个匹配
+	1个或多个匹配，等于 {1,}
？	0或1个匹配，等于{0,1}
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围，m 不超过255
^	文本的开始
&	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks\\)' ORDER BY prod_name;
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[:digit:]{4}' ORDER BY prod_name;

正则测试

SELECT 'hello' REGEXP '[0-9]';

计算字段

Concat 拼接

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

+-------------------------------------------+
| Concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| ACME(USA)                                 |
| Anvils R Us(USA)                          |
| Furball Inc.(USA)                         |
| Jet Set(England)                          |
| Jouets Et Ours(France)                    |
| LT Supplies(USA)                          |
+-------------------------------------------+

Trim 裁剪

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

AS 别名

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;

函数

Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的第一个子串
Lower()	将串转为小写
LTrim()	去掉左侧的空格
Right()	返回串右边的字符
RTrim()	去掉右侧的空格
Soundex()	返回串中的 soundex 值
SubString()	返回子串的字符
Upper	将串转为大写

日期

AddDate()	增加一个日期（天、周）
AddTime()	增加一个时间（时、分）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间中日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	返回日期的星期
Hour()	返回日期的小时部分
Minute()	返回日期的分钟
Mouth()	返回日期的月份
Now()	返回当前日期和时间
Second()	返回日期的秒部分
Time()	返回日期的时间部分
Year()	返回日期的年份

SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理

Abs()	绝对值
Cos()	余弦
Exp()	指数
Mod()	除操作的余数
Pi()	圆周率
Rand()	随机数
Sin()	正弦
Sqrt()	平方根
Tan()	正切

聚集函数

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 = 1003;
SELECT COUNT(*) as num_cust FROM customers;
SELECT COUNT(cust_email) as num_cust FROM customers;
SELECT MAX(prod_price) as max_price FROM customers;
SELECT MIN(prod_price) as min_price FROM customers;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(quantity*item_price) AS total_price FROM orderitems WHERE order_num = 20005;
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;
SELECT COUNT(*) AS num_items, MAX(prod_price) AS max_price, MIN(prod_price) AS min_price, AVG(prod_price) AS avg_price FROM products;

分组

创建

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
# 分组汇总
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

过滤

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

分组排序

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY ordertotal;

字句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

子查询

过滤

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;

+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

联结

内联结

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

多表联结

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
    AND orderitems.prod_id = products.prod_id
  AND order_num = 20005;

+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

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

别名

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

自联结

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_name = 'DTNTR';

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
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';

外部联结

SELECT c.cust_id, o.order_num FROM customers AS C INNER JOIN orders AS o ON c.cust_id = o.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

左外部联结

SELECT c.cust_id, o.order_num FROM customers AS C LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust.id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

右外部联结

SELECT c.cust_id, o.order_num FROM customers AS C RIGHT OUTER JOIN orders AS o ON c.cust_id = o.cust.id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

聚集

SELECT c.cust_id, c.cust_name, COUNT(o.order_num) AS num_ord
FROM customers AS C INNER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;

+---------+----------------+---------+
| cust_id | cust_name      | num_ord |
+---------+----------------+---------+
|   10001 | Coyote Inc.    |       2 |
|   10003 | Wascals        |       1 |
|   10004 | Yosemite Place |       1 |
|   10005 | E Fudd         |       1 |
+---------+----------------+---------+

组合

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 (1002, 1003);
# 不去除重复行
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1002, 1003);
# 排序
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 (1002, 1003)
ORDER BY vend_id, prod_price;

全文搜索

文本搜索

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('rabbit');

布尔

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('heavy -rope*' IN BOOLEAN MODE);

+	必须包含
-	必须排除
<	包含，增加等级
>	包含，减少等级
()	子表达式
~	取消排序值
*	词尾的通配符
“”	定义一个短语

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('+rabbit +bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('rabbit bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('"rabbit bait"' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('>rabbit <bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST ('+safe +(<combination)' IN BOOLEAN MODE);

插入数据

INSERT INTO customers
VALUES(NULL, 'Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
# 指定列
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
# 低优先级
INSERT LOW_PRIORITY INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
# 插入多行
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),
('M Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
# 插入检索出的数据
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM customers WHERE cust_id = 10001;

更新数据

UPDATE customers SET cust_name = 'The Funds', cust_email = 'elmer@fundds.com' WHERE cust_id = 10005;
# 发生错误继续更新
UPDATE IGNORE customers SET cust_name = 'The Funds', cust_email = 'elmer@fundds.com'
# 删除列的值
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

删除数据

DELETE FROM customers WHERE cust_id = 10006;
# 删除所有行
TRUNCATE Table

创建表

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

引擎类型：
InnoDB: 支持事务、不支持全文搜索
MEMORY: 功能等同 MyISAM，数据存储在内存中
MyISAM: 不支持事务，支持全文搜索

更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);
# 删除
ALTER TABLE vendors DROP COLUMN vend_phone;
# 定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

删除表

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 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;

事务

rollback

SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;

commit

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT

保留点

SAVEPOINT delete1
ROLLBACK TO delete1

更改默认提交

SET autocommit=0;

安全管理

# 创建账号
CREATE USER u1 IDENTIFIED BY '123';
# 重命名
RENAME USER u1 TO u2;
# 删除
DROP USER u2;

权限

CREATE USER u1 IDENTIFIED BY '123';
SHOW GRANTS FOR u1;

+--------------------------------+
| Grants for u1@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%` |
+--------------------------------+

GRANT SELECT ON crashcourse.* TO u1;
# 撤销
REVOKE SELECT ON crashcourse.* FROM u1;

+---------------------------------------------+
| Grants for u1@%                             |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`              |
| GRANT SELECT ON `crashcourse`.* TO `u1`@`%` |
+---------------------------------------------+

更改口令

ALTER USER u1 IDENTIFIED BY '666';

MYSQL必知必会

示例脚本

创建数据库

创建

查看创建的信息

选择数据库

显示所有数据库

使用数据库

获取表

显示列

检索数据

检索单个列

检索多个列

检索所有列

检索不同行

限制结果

使用限定的表名

排序检索数据

排序

多个列排序

指定排序方向

过滤数据

过滤

组合

通配符

正则

计算字段

Concat 拼接

Trim 裁剪

AS 别名

算数计算

函数

日期

数值处理

聚集函数

分组

创建

过滤

分组排序

字句顺序

子查询

过滤

计算字段

联结

内联结

多表联结

别名

自联结

外部联结

聚集

组合

全文搜索

文本搜索

布尔

插入数据

更新数据

删除数据

创建表

更新表

删除表

重命名表

视图

事务

rollback

commit

保留点

更改默认提交

安全管理

权限

更改口令