输入 help 或 \h 获得帮助,也可以输入更多的文本获得特定命令的帮助(如,输入 help select 获得试用 SELECT 语句的帮助);
输入 quit 或 exit 退出命令行。
连接数据库
连接数据库需要以下信息:
主机名(计算机名)——如果连接到本地 MySQL 服务器,为 localhost ;
端口(如果使用默认端口 3306 之外的端口);
一个合法的用户名;
用户口令(如果需要)
例如下面的指令:
mysql -u root -h localhost -P 3306 -p
数据库的登录和成员管理
访问控制
MySQL 服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权,既不能多也不能少。
需要给用户提供他们所需的访问权,且仅提供他们所需的访问权。这就是所谓的访问控制。访问控制的目的不仅仅是防止用户的恶意企图,访问控制也有助于避免很常见的无意识错误的结果,如错打 MySQL 语句,在不合适的数据库中操作或其他一些用户错误。
管理用户
查询已有用户
MySQL 用户账号和信息存储在名为 mysql 的 MySQL数据库中。一般只有在需要获得所有用户账号列表时才会直接访问。
# 输入USE mysql;SELECT user FROM user;# 输出+------------------+| user |+------------------+| test || root |+------------------+
创建用户账号
1、使用 CREATE USER 语句(推荐)
# 输入CREATEUSERchenfangxu IDENTIFIED BY'123456';SELECT user FROM user;#输出+------------------+| user |+------------------+| chenfangxu || test || root |+------------------+
在创建用户账号后,必须接着分配访问权限。新创建的用户账号没有访问权限。他们能登录 MySQL ,但不能看到数据,不能执行任何数据库操作。
查看赋予用户账号的权限SHOW GRANTS FOR
# 输入SHOW GRANTS FOR chenfangxu;# 输出+----------------------------------------+| Grants for chenfangxu@% |+----------------------------------------+| GRANT USAGE ON*.*TO`chenfangxu`@`%` |+----------------------------------------+
权限 USAGE ON *.* ,USAGE表示根本没有权限,这句话就是说在任意数据库和任意表上对任何东西没有权限。
添加(更新)用户权限GRANT privileges ON databasename.tablename TO 'username'@'host';
# 输入GRANTSELECTON performance_schema.*TO chenfangxu@'%';SHOW GRANTS FOR chenfangxu;# 输出+------------------------------------------------------------+| Grants for chenfangxu@% |+------------------------------------------------------------+| GRANT USAGE ON*.*TO`chenfangxu`@`%` || GRANTSELECTON`performance_schema`.*TO`chenfangxu`@`%` |+------------------------------------------------------------+
撤销用户的权限REVOKE privileges ON databasename.tablename FROM 'username'@'host';
# 输入REVOKESELECTON performance_schema.*FROM chenfangxu@'%';SHOW GRANTS FOR chenfangxu;#输出+----------------------------------------+| Grants for chenfangxu@% |+----------------------------------------+| GRANT USAGE ON*.*TO`chenfangxu`@`%` |+----------------------------------------+
重命名
重命名:RENAME USER 'username' TO 'newusername';
# 输入RENAME USER test TO test1;SELECT user FROM user;# 输出+------------------+| user |+------------------+| test1 || root |+------------------+
更改用户密码(mysql 8.0.11后)
更改用户密码:SET PASSWORD FOR 'username'@'host' = 'newpassword';
SETPASSWORDFOR chenfangxu@'%'='654321';# 更改root密码ALTERUSER'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY'yourpasswd';
删除用户
删除用户:DROP USER 'username'@'host';
# 输入DROPUSER chenfangxu@'%';SELECT user FROM user;#输出+------------------+| user |+------------------+| test || root |+------------------+
MySQL 5 以前, DROP USER 只能用来删除用户账号,不能删除相关的权限。因此,如果使用旧版的 MySQL 需要先用 REVOKE 删除与账号相关的权限,然后再用 DROP USER 删除账号。
# 检索单个列,例如从 products 表中检索一个名为 prod_name 的列。SELECT prod_name FROM products;# 检索多个列。注意,列名之间要用逗号分隔,最后一个列名后不要加逗号,会报错。SELECT prod_id, prod_name, prod_price FROM products;# 检索所有列。SELECT*FROM products;# 只检索出不同的行, DESTINCT 关键字可以让指令只返回不同的值。如果指令,products 表中可能一共有14行,现在只返回不同(唯一)的 vend_id 行,可能就只返回4行了。SELECT DISTINCT vend_id FROM products;# 限制结果, LIMIT5 表示只返回不多于5行。SELECT prod_name FROM products LIMIT5;# LIMIT5, 5 表示返回从行5开始的5行。SELECT prod_name FROM products LIMIT5, 5;# 或者使用 LIMIT5 OFFSET 5, 跟上面结果相同。SELECT prod_name FROM products LIMIT5 OFFSET 5;# 注意,返回行数是从 0 开始的。所以,LIMIT1, 1 将检索出第二行,而不是第一行。SELECT prod_name FROM products LIMIT1,1;
排序检索数据 ( ORDER BY )
不使用排序时,其实检索出的数据并不是以纯粹的随机顺序显示的,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序,但是,如果数据后来进行过更新或者删除,则此顺序将会受到 MySQL 重用回收存储空间的影响。因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序。
关系数据库设计理论认为:如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
ORDER BY 子句,可以给 SELECT 语句检索出来的数据进行排序。 ORDER BY 子句取一个或多个列的名字。据此对输出进行排序。
# 没有排序SELECT prod_name FROM products;# 对 prod_name 列以字母顺序排序数据SELECT prod_name FROM products ORDER BY prod_name;# 按多个列排序:如下会先按照 prod_price 排序,# 只有出现相同的 prod_price 时,才会再按照 prod_name 排序。SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;# 指定排序方向,默认是升序,例如按照 prod_price 降序排序(最贵的排在最前面)SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;# 多个列排序,例如按照 prod_price 降序,最贵的在最前面,然后在对产品名排序SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;# ORDER BY 和 LIMIT 搭配,可以找出一个列中最高或最低的值。SELECT prod_price FROM products ORDER BY prod_price DESCLIMIT1;
注意:
ORDER BY 子句中使用的列不一定非得是检索的列,用非检索的列排序也是完全合法的。
如果想在多个列上进行降序排序,必须对每个列指定 DESC 关键字。
ASC 是升序排序,升序是默认的,不指定 DESC ,那就是按照 ASC 升序排序。
ORDER BY 子句必须位于 FROM 子句之后,如果使用 LIMIT ,它必须位于 ORDER BY 之后。
过滤数据 ( WHERE )
数据库包含大量的数据,但是我们很少需要检索表中所有的行。只检索所需数据需要指定过滤条件,在 SELECT 语句中,数据根据 WHERE 子句中指定的搜索条件进行过滤。
# 检索 pro_price 为 2.50 的行SELECT prod_name FROM products WHERE prod_price =2.50;# 执行筛选匹配时默认不区分大小写,所以 fuses 可以检索出 FusesSELECT prod_name, prod_price FROM products WHERE prod_name ='fuses';# 输出+-----------+------------+| prod_name | prod_price |+-----------+------------+| Fuses | 3.42 |+-----------+------------+# 检索出 vend_id 不等于 1003 的行SELECT vend_id, prod_name FROM products WHERE vend_id <>1003;# 检索 prod_price 在 5 到 10 之间的所有行SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN5AND10;# 检查具有 NULL 值的列,用 ISNULL 子句SELECT cust_id FROM customers WHERE cust_email ISNULL;
WHERE 子句操作符
操作符
说明
=
等于
<>
不等于
!=
不等于
<
小于
<=
小于等于
>
大于
>=
大于等于
BETWEEN
在指定的两个值之间
注意:
WHERE 语句的位置:在同时使用 ORDER BY 和 WHERE 子句时,应该让 ORDER BY 位于 WHERE 之后,否则将会产生错误。
WHERE 子句中使用的条件,如果将值与串类型(例如字符串)比较,需要加引号,用来与数值列进行比较的值不用引号。
NULL 无值(no value),它与字段 0 、空字符串或仅仅包含空格不同。
数据过滤( AND、 OR、 IN )
MySQL 允许组合多个 WHERE 子句。这些子句分为两种方式使用:以 AND 子句的方式或 OR 子句的方式使用。
### AND 操作符# 检索出 vend_id 等于 1003 并且 prod_price 小于等于 10 的行SELECT prod_price, prod_name FROM products WHERE vend_id =1003AND prod_price <=10;#### OR 操作符# 检索出 vend_id 等于 1002 或 vend_id 等于 1003 的所有行SELECT prod_name, prod_price FROM products WHERE vend_id =1002OR vend_id =1003;# AND 和 OR 合用,AND 优先级高。# 下面检索出的结果是 vend_id 是 1003 并且 prod_price 大于等于 10 的和所有 vend_id 是 1002 的行。SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id =1002OR vend_id =1003AND prod_price >=10;# 输出结果+---------+----------------+------------+| vend_id | prod_name | prod_price |+---------+----------------+------------+| 1002 | Fuses | 3.42 || 1002 | Oil can | 8.99 || 1003 | Detonator | 13.00 || 1003 | Bird seed | 10.00 || 1003 | Safe | 50.00 || 1003 | TNT (5 sticks) | 10.00 |+---------+----------------+------------+# 如果想检索出 vend_id 是 1003 并且 prod_price 大于等于 10 的和 vend_id 是 1002 并且 prod_price 大于等于 10 的行,需要加括号。SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id =1002OR vend_id =1003) AND prod_price >=10;### IN 操作符,指定条件范围,范围中的每个条件都可以进行匹配。IN 取值是全部括在圆括号中的由逗号分隔的列表。SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003);### NOT 操作符,否定它之后的任何条件SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOTIN (1002, 1003);
注意
WHERE 可包含任意数目的 AND 和 OR 操作符,并且允许两者结合以进行复杂和高效的过滤。不过 SQL 语言在处理 OR 操作符前,会优先处理 AND 操作符。
任何时候使用具有 AND 和 OR 操作符的 WHERE 子句, 都推荐使用圆括号明确地分组,不要过分依赖默认计算次序。
IN 和 OR 具有相同的功能,但是 IN 操作符有以下优点
过滤的字段太多的时候,IN 操作符的语法更清楚且更直观
IN 操作符一般比 OR 操作符执行的更快
IN 最大的优点是可以包含其他 SELECT 语句,能更动态地建立 WHERE 子句。
MySQL 支持使用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。
用通配符过滤
百分号(%)通配符
% 表示任何字符出现任意次数,可以使0次,1次,n次
### 找出所有以 jet 开头的产品SELECT prod_id, prod_name FROM products WHERE prod_name LIKE'jet%';+---------+--------------+| prod_id | prod_name |+---------+--------------+| JP1000 | JetPack 1000 || JP2000 | JetPack 2000 |+---------+--------------+### 通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。SELECT prod_id, prod_name FROM products WHERE prod_name LIKE'%anvil%';+---------+--------------+| prod_id | prod_name |+---------+--------------+| ANV01 | .5 ton anvil || ANV02 | 1 ton anvil || ANV03 | 2 ton anvil |+---------+--------------+
下划线通配符
下划线 _ 只能匹配单个字符,只能匹配一个,不能多也不能少。
### 对比一下下面两个通配符结果SELECT prod_id, prod_name FROM products WHERE prod_name LIKE'_ ton anvil';+---------+-------------+| prod_id | prod_name |+---------+-------------+| ANV02 | 1 ton anvil || ANV03 | 2 ton anvil |+---------+-------------+SELECT prod_id, prod_name FROM products WHERE prod_name LIKE'% ton anvil';+---------+--------------+| prod_id | prod_name |+---------+--------------+| ANV01 | .5 ton anvil || ANV02 | 1 ton anvil || ANV03 | 2 ton anvil |+---------+--------------+### 下划线通配符比百分号通配符少了一个 .5 的数据
### 例如用 Y. Lie 把 Y Lee 搜出来,因为它们发音类似SELECT cust_name, cust_contact FROM customers WHERESoundex(cust_contact) =Soundex('Y. Lie');+-------------+--------------+| cust_name | cust_contact |+-------------+--------------+| Coyote Inc. | Y Lee |+-------------+--------------+
日期和时间处理函数
函 数
说 明
AddDate()
增加一个日期(天、周等)
AddTime()
增加一个时间(时、分等)
CurDate()
返回当前日期
CurTime()
返回当前时间
Date()
返回日期时间的日期部分
DateDiff()
计算两个日期之差
Date_Add()
高度灵活的日期运算函数
Date_Format()
返回一个格式化的日期或时间串
Year()
返回一个日期的年份部分
Month()
返回一个日期的月份部分
Day()
返回一个日期的天数部分
DayOfWeek()
对于一个日期,返回对应的星期几
Hour()
返回一个时间的小时部分
Minute()
返回一个时间的分钟部分
Second()
返回一个时间的秒部分
Now()
返回当前日期和时间
### 检索出日期为 2005-09-01 这天的订单记录SELECT cust_id, order_num FROM orders WHERE order_date ='2005-09-01';### 上面的检索有个问题,如果 order_date 存储的带有时间,例如 2005-09-0111:30:05 ,就检索不到了,解决办法是让仅将给出的日期与列中的日期部分进行比较SELECT cust_id, order_num FROM orders WHEREDate(order_date) ='2005-09-01';### 如果想检索出2005年9月的所有订单### 方法一,得记住每个月有多少天,甚至要知道是不是闰年的2月SELECT cust_id, order_num FROM orders WHEREDate(order_date) BETWEEN'2005-09-01'AND'2005-09-30';### 方法二, 使用 Year() 和 Month() 函数SELECT cust_id, order_num FROM orders WHEREYear(order_date) =2005ANDMonth(order_date) =9;
### 列出至少有两个订单的所有顾客SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVINGCOUNT(*) >=2;### 列出具有2个(含)以上、价格为10(含)以上的产品的供应商SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >=10GROUP BY vend_id HAVINGCOUNT(*) >=2;### 列出总计订单价格大于等于50的订单,并按照总价排序SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
+-----------+------------+| order_num | ordertotal |+-----------+------------+| 20006 | 55.00 || 20008 | 125.00 || 20005 | 149.87 || 20007 | 1000.00 |+-----------+------------+
HAVING 跟 WHERE 类似,但是 WHERE 在数据分组前进行过滤,HAVING 在数据分组后进行过滤。
SELECT 子句顺序
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
SELECT cust_name, cust_contact FROM customersWHERE cust_id IN (SELECT cust_id FROM ordersWHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id ='TNT2'));+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+
SELECT cust_name, cust_contact,(SELECTCOUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS ordersFROM customers ORDER BY cust_name;+----------------+--------------+--------+| cust_name | cust_contact | orders |+----------------+--------------+--------+| Coyote Inc. | Y Lee | 2 || E Fudd | E Fudd | 1 || Mouse House | Jerry Mouse | 0 || Wascals | Jim Jones | 1 || Yosemite Place | Y Sam | 1 |+----------------+--------------+--------+
联结表
### 等值联结(equi join),它基于两个表之间的相等测试。这种联结也称为 内部联结。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;
### 上面提到用子查询,返回订购产品 TNT 的客户列表,现在改成联结表的方式,可以跟子查询的方式对比一下。SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';
注意
应该保证所有的联结都有 WHERE 子句,否则 MySQL 将返回比想要的数据多得多的数据。
创建高级联结
表别名和自联结
### 使用表别名,返回订购产品 TNT 的客户列表SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id = 'TNT2';
### 使用自联结,查找商品 ID 为 DTNTR 的供应商供应的所有产品SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
外部联结
联结包含了那些在相关表中没有关联行的行,外部联结的两种基本形式:左外部联结(LEFT OUTER JOIN 即 LEFT JOIN)和右外部联结。它们之间唯一差别是所关联的表的顺序不同。更具体可以看一下 JOIN详解。
### 列出每个客户下的订单,包括那些至今未下订单的客户SELECT customers.cust_id, orders.order_num FROM customers LEFT JOIN orders ON customers.cust_id = orders.cust_id;+---------+-----------+| cust_id | order_num |+---------+-----------+| 10001 | 20005 || 10001 | 20009 || 10002 | NULL || 10003 | 20006 || 10004 | 20007 || 10005 | 20008 |+---------+-----------+#### 对每个用户下的订单计数,包括那些至今没下订单的客户SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS order_count FROM customers AS c LEFT JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;
+----------------+---------+-------------+| cust_name | cust_id | order_count |+----------------+---------+-------------+| Coyote Inc. | 10001 | 2 || Mouse House | 10002 | 0 || Wascals | 10003 | 1 || Yosemite Place | 10004 | 1 || E Fudd | 10005 | 1 |+----------------+---------+-------------+
组合查询
MySQL 允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询称为并(union) 或 复合查询(compound query)。
有两种基本情况,其中需要使用组合查询:
在单个查询中从不同的表返回类似结构的数据;
对单个表执行多个查询,按单个查询返回数据。
### 查询价格小于等于5的所有物品并且查出供应商 1001 和 1002 生产的所有物品(不考虑价格)### 先用 WHERE 多个子句来实现。SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <=5OR vend_id IN (1001,1002);### 使用组合查询实现,会自动去除重复的行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);
### 使用组合查询查所有符合条件的列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 (1001, 1002);
### 组合查询排序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) ORDER BY vend_id, prod_id;
注意
UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔。
UNION 中的每个查询必须包含相同的列,表达式或聚集函数(不过各个列不需要以相同的次序列出)。
对组合查询结果排序时,只能使用一条 ORDER BY 子句,它必须出现在最后一条 SELECT 语句之后。
更新视图时,可以先 DROP 然后再 CREATE 或者使用 CREATE OR REPLACE VIEW
### 之前有用联表查询 prod_id 是 TNT2的购买用户信息,但是如果还想看 prod_id 是其他值的时,还得重新查一遍,这样的场景就能用到视图了。### 创建一个包含 cust_name, cust_contact, prod_id 的视图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;
### 现在就能看到 productcustomers 视图能查询的信息了select*from productcustomers;+----------------+--------------+---------+| cust_name | cust_contact | prod_id |+----------------+--------------+---------+| Coyote Inc. | Y Lee | ANV01 || Coyote Inc. | Y Lee | ANV02 || Coyote Inc. | Y Lee | TNT2 || Coyote Inc. | Y Lee | FB || Coyote Inc. | Y Lee | FB || Coyote Inc. | Y Lee | OL1 || Coyote Inc. | Y Lee | SLING || Coyote Inc. | Y Lee | ANV03 || Wascals | Jim Jones | JP2000 || Yosemite Place | Y Sam | TNT2 || E Fudd | E Fudd | FC |+----------------+--------------+---------+### 如果想再查询出 prod_id 为 TNT2 的客户信息就很简单了SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id ='TNT2';+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+
注意
视图必须唯一命名(不能跟别的视图和表重名)
对于可以创建的视图数量没有限制。
视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个新的视图。
ORDER BY 可以用在视图中,但如果从该视图检索数据的 SELECT 中也含有 ORDER BY ,那么视图中的 ORDER BY 会被覆盖。
### 创建存储过程DELIMITER //CREATEPROCEDURE productpricing (OUT pl DECIMAL(8,2),OUT ph DECIMAL(8,2),OUT pa DECIMAL(8,2))BEGINSELECTMin(prod_price) INTO pl FROM products;SELECTMax(prod_price) INTO ph FROM products;SELECTAvg(prod_price) INTO pa FROM products;END//### 调用存储过程CALL productpricing(@pricelow,@pricehigh,@priceaverage);### 查看3个变量SELECT @pricelow,@pricehigh,@priceaverage;+-----------+------------+---------------+| @pricelow | @pricehigh | @priceaverage |+-----------+------------+---------------+| 2.50 | 55.00 | 16.13 |+-----------+------------+---------------+
建立智能存储过程
需要获取订单合计,并且对某些顾客的合计增加营业税。
### 存储过程全过程
DELIMITER //
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal (
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
-- Is this taxable
IF taxable THEN
-- Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- And finally, save to out variable
SELECT total INTO ototal;
END //
### 调用,看一下 order number 是 20005 订单关于加不加营业税的区别
### 不加营业税
CALL ordertotal(20005, 0, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
### 加营业税
CALL ordertotal(20005, 1, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
上面代码中做些必要的解释
添加了两个 IN 类型参数,其中 taxable 为布尔值。
-- 添加注释,在存储过程复杂是,注释很有必要。
DECLARE 定义局部变量,需要指定变量名和数据类型,支持可选的默认值
COMMENT 关键字,不是必需的,如果添加了,在 SHOW PROCEDURE STATUS 的结果中显示。
如果删除不存在的存储过程时,会报错,可以使用 DROP PROCEDURE IF EXISTS ,只有当过程存在时才删除。
MySQL 支持 IN(传递给存储过程)、OUT(从存储过程传出)、INOUT(对存储过程传入和传出)三种类型的参数。SELECT 检索出来的值通过 INTO 保存到相应的变量。特别注意,参数的数据类型不能是一个集合,所以例子中才用了三个参数输出3个数。
如果存储过程要求3个参数,就必须正好传递3个参数。
SHOW PROCEDURE STATUS 可以列出所有存储过程,也可以使用 LIKE 指定一个过滤模式: SHOW PROCEDURE STATUS LIKE 'ordertotal';
使用游标
游标(cursor)是一个存储在 MySQL 服务器上的数据库查询,它不是一条 SELECT 语句,而是被该语句检索出来的结果集。
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o,1,t);
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END//
DELIMITER ;
SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
DECLARE 命名游标,并定义相应的 SELECT 语句,根据需要带 WHERE 和其他子句。
OPEN ordernumbers; 打开 ordernumbers 游标,在处理 OPEN 语句时执行查询,存储检索出的数据以供浏览和滚动。
CLOSE ordernumbers; 关闭 ordernumbers 游标,CLOSE 释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭,如果没有明确关闭游标,MySQL 将会在到达 END 语句时自动关闭它。在一个游标关闭后,如果没有重新打开,就不能使用它。但是,使用声明过的游标不需要再次声明,用 OPEN 语句打开就行。
如果 BEFORE 触发器失败,MySQL 将不执行请求的操作。如果 BEFORE 触发器或语句本身失败, MySQL将不执行 AFTER 触发器(如果有的话)。
MySQL 的 TRIGGER 和 FUNCTION 中不能出现 SELECT * FROM table 形式的查询,因为其会返回一个结果集,而这在 MySQL 的 TRIGGER 和 FUNCTION 中是不可接受的,但是在存储过程中可以。在 TRIGGER 和 FUNCTION 中可以使用 SELECT ... INTO ... 形式的查询。
使用 TRIGGER 的时候没有 INTO 的时候会报这样一种错误 not allowed to return a result set from a trigger
MySQL 触发器中不支持 CALL 语句,这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
管理事务处理
事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的 MySQL 操作要么完全执行,要么完全不执行。
事务处理是一种机制,用来管理必须成批执行的 MySQL 操作,以保证数据库不包含不完整的操作结果。使用事务处理,可以保证一组操作不会中途停止,它们要么整体执行,要么完全不执行(除非明确指示)如果没有错误发生,整租语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。
事务(transaction)指一组 SQL 语句,语句 START TRANSACTION
回退(rollback) 指撤销指定的 SQL 语句的过程,语句 ROLLBACK TO onename
# 用于显示广泛的服务器状态信息
SHOW STATUS;
# 显示创建特定数据库的MySQL语句,例如展示 learnsql 数据库的创建语句
SHOW CREATE DATABASE learnsql;
# 显示创建特定表的MySQL语句,例如展示 customers 表的创建语句
SHOW CREATE TABLE customers;
# 显示服务器的错误信息
SHOW ERRORS;
# 显示服务器的警告信息
SHOW WARNINGS;
# 显示所有允许的 SHOW 语句
HELP SHOW;