MySQL学习小结

这一篇最主要是记录下命令，方便以后查找

使用Mysql

创建数据库

create database mysql_test;

展示所有数据库

show databases;

选择要使用的数据库

use Typeidea_db

获得表tables

show tables;

显示服务器状信息

show status;

更多show命令查询

help show

检索数据

• select + 列名 from + 表
• selcet、from 都为关键字
• 使用select语句从products表中检索一个名为prod_name的列

SELECT prod_name FROM products;

检索多个多个列

select prod_name, prod_id, prod_price from products;

检索所有列

select * from products;

检索不同的行

• DISTINCT关键字 “不同”
• 告诉MySQL只返回不同的vend_id行

selcet DISTINCT vend_id from products;

检索限制结果

• limit
• limit 5,5:第一个5开始位置，第二个5要检索的行数;
• limit 4 OFFSET 3 :从行3开始取4行
• 返回不多于5行

select prod_name from products LIMIT 5;

select prod_name from products LIMIT 5,5;

排序检索数据

• order by :排序
• 以字母顺序排序数据（A-Z）

select prod_name from products ORDER BY prod_name;

按多个列排序

select prod_name, prod_id, prod_price from products ORDER BY prod_price, prod_name;

image

指定排序方向

• DESC 降序排序
• Z-A
• 对产品价格进行排序

select prod_id, prod_price, prod_name from products ORDER BY prod_price DESC;

• 降序排序产品价格，再对产品名字排序

select prod_id, prod_price, prod_name from products ORDER BY prod_price DESC, prod_name;

使用order by 和limit 找出列中最高或最低的值

• 去掉DESC就是最低值
• limit 1 ：仅返回一行
• 使用order by 和limit子句时，limit子句必须在order by 子句之后

select prod_price from products ORDER BY prod_price DESC LIMIT 1;

过滤数据

where子句

• 从products表中检索两个列， 过滤（只返回prod_price = 2.5的行）
• 使用order by 和 where子句时，order by 必须在where之后

select prod_name, prod_price from products WHERE prod_price = 2.50;

检查单个值

where字句操作符

• =
• <
• !=
• <=

• =

• BETWEEN ：在指定的两个值之间

检查where prod_name = 'fuses'，返回prod_name 的值为fuses的一行

• 值为字符串时需要使用单引号

select prod_name, prod_price from products WHERE prod_name = 'fuses';

列出小于等于10美金的所有产品

select prod_price, prod_name from products WHERE prod_price <= 10;

不匹配检查

列出不是由供应商1003制造的所有产品

select vend_id, prod_name from products WHERE vend_id != 1003;

范围值检查

查询价格在5美金至10美金之间的所有产品

• ADN ：两个值之间必须要使用AND来连接
• BETWEEN

select prod_name, prod_price from products WHERE prod_price BETWEEN 5 AND 10;

空值检查
NULL：空值

select prod_name from products WHERE prod_price IS NULL;

文件中没有某位顾客的电子邮件

select cust_id from customers WHERE cust_email IS NULL;

组合WHERE子句
AND操作符
检索供应商1003制造且价格小于等于10美金的所有产品的价格名称、价格、ID

select prod_id, prod_price, prod_name from products WHERE vend_id = 1003 AND prod_price <=10;

OR操作符

• 指示MySQL检索匹配任一条件的行
• 检索任一个指定供应商制造的所有产品的名称和价格

select prod_name, prod_price from products WHERE vend_id = 1002 OR vend_id = 1003;

计算次序
例子：
检索出10美金以上且由1002或者1003制造的所有产品

select prod_name, prod_price from products WHERE vend_id = 1003 OR vend_id = 1002 AND prod_price >= 10;

image

• sql语句理解为：检索1003供应商制造的所有产品或者1002供应商制造的大于10美金的所有产品
• and 在计算次序中优先级更高
• 这里应该使用圆括号

select prod_name, prod_price from products WHERE(vend_id = 1002 or vend_id = 1003) AND prod_price >= 10;

IN 操作符
IN - 用来指定范围

检索由1002或1003供应商提供的所有产品的名称和价格，名称以升序排序

select prod_name, prod_price from products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

NOT操作符

• 用来否定它之后的所跟的任何条件
• 匹配除开1002和1003外所有的供应商的产品名字和价格，排序以名称升序排序

select prod_name, prod_price from products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

用通配符进行过滤

• LIKE操作符: 匹配未知值
• %通配符：表示任何字符出现任意次数
• 通配符无法匹配以null值作为产品名的行

查找以'jet'开头的产品

select prod_id, prod_name from products WHERE prod_name LIKE 'jet%';

搜索模式‘%anvil%’表示匹配任何位置包含文本anvil的值，无论它之前或之后出现什么字符

找到以s起头e结尾的所有产品

select prod_name from products WHERE prod_name LIKE 's%e';

下划线（_）通配符

• 至匹配单个字符
• 下划线后有个空格
• .5 ton anvil没有被匹配，因为搜索模式要求匹配两个匹配符而不是一个”.一个“ ”5一个“

select prod_id, prod_name from products WHERE prod_name LIKE '_ ton anvil';

image

用正则表达式进行搜索

• REGEXP
• LIKE :如果匹配值在列中被找到将不会返回数据，like匹配未知值

检索prod_name文本1000的所有行

select prod_name from products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

匹配所有.000

select prod_name from products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

OR匹配

• 使用 | :为正则表达式中的OR操作，表示匹配其中之一

select prod_name from products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

匹配几个字符之一

[123] Ton

• [123]定义一组字符，匹配1或2或3

select prod_name from products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

或者这样写

select prod_name from products WHERE prod_name REGEXP '[1|2|3] Ton' ORDER BY prod_name;

匹配范围

• 用 - 来匹配
• [1-5]
• [a-z]

select prod_name from products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

匹配特殊字符

• 必须使用’\‘ \- \. 转义

select vend_name from vendors WHERE vend_name REGEXP '\.' ORDER BY vend_name;

匹配多个实例

select prod_name from products WHERE prod_name REGEXP '\([0-9]) sticks?\)' ORDER BY prod_name;

• \()
• [0-9]；匹配任意数字

select prod_name from products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

• [:digit:]；任意数字集合
• {4}：任意4位数

定位符

• 在集合中[^.....]表示否定该集合或者指定字符的开始处

创建计算字段

拼接字段

• concate()函数来实现拼接

名字和地址拼接起来

select Concat(vend_name, '(', vend_country, ')') from vendors ORDER BY vend_name;

image

• 删除右侧多余空格
• Concat()
• RTrim()函数

select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') from vendors ORDER BY vend_name;

使用别名

• alias 别名 AS

select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title from vendors ORDER BY vend_name;

执行算术计算

select prod_id, quantity, item_price from orderitems WHERE order_num = 2005;

使用数据处理函数

文本处理函数

select vend_name, Upper(vend_name) AS vend_name_upcase from vendors ORDER BY vend_name;

• AS(alias) 使用别名
• Upper()函数：将文本转换为大写

select cust_name, cust_contact from customers WHERE Soundex(cust_contact) = Soundex('Y Lie');

• Soundex 检索所有发音类似‘Y Lie’的联系名（cust_contact）

日期和时间处理函数

select cust_id, order_num from orders WHERE order_date = '2005-09-01';

如果要的是日期，就使用Date()

select cust_id, order_num from orders WHERE Date(order_date) = '2005-09-01';

检索2005年9月份下的所有订单

select cust_id, order_num from orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

select cust_id, order_num from orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

汇总数据

AVG（）函数

• AVG（）函数：返回某列的平均值
• AVG（）函数忽略列值为NULL的行
• AVG（）函数只能确定特定数值列的平均值

使用AVG函数f返回products表中所有产品的平均价格

select AVG(prod_price) AS avg_price from products;

返回特定供应商所提供产品的平均价格

• 使用where来过滤出vend_id = 1003的产品
• AS 给prod_price取别名

select AVG(prod_price) AS avg_price from products WHERE vend_id = 1003;

COUNT（）函数

• COUNT(*)对表中数目进行计数，不管表列中包含的是空值还是非空值
• COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

统计customers表中所有的行，不管各列有什么值

select COUNT(*) AS num_cust from customers;

只对具有电子邮箱地址的客户计数

select COUNT(cust_email) AS num_cust from customers;

MAX函数

• 返回指定列中的最大值
• 要求指定列名

检索products表中最贵物品的价格

select MAX(prod_price) AS max_price from products;

MIN函数

• 返回指定列的最小值

检索products表中最便宜i物品的价格

select MIN(prod_price) AS min_price from products;

SUM函数

• 返回指定列值的和

检索所订购物品的总数

• SUM(quantity):返回订单中所有物品数量之和
• WHERE子句保证只统计某个物品订单中的物品

select SUM(quantity) AS items_ordered from orderitems WHERE order_num = 20005;

检索20005订单中所有物品价格之和

• SUM(item_price*quantity)

select SUM(item_price*quantity) AS total_price from orderitems WHERE order_num = 20005;

DISTINCT

使用distinct后，平均值值考虑各个不同的价格

select AVG(DISTINCT prod_price) AS avg_price from products WHERE vend_id = 1003;

组合聚集函数
返回4个值

• 物品的所有数目
• 最低价格
• 最高价格
• 平均价格

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;

分组数据

• GROUP BY 子句
• HAVING 子句

创建分组

• vend_id；包含供应商的ID
• AS 使用别名
• GROUP BY vend-id 对vend_id进行分组

select vend_id, COUNT(*) AS num_prods from products GROUP BY vend_id;

image

ROLLUP

• 可以得到每个分组以及每个分组汇总级别（针对每个分组）的值
• null 值 也算了进去

select vend_id, COUNT(*) AS num_prods from products GROUP BY vend_id WITH ROLLUP;

image

过滤分组

• having ：过滤分组
• where ：过滤行

过滤那些COUNT()2的那些分组

select cust_id, COUNT() AS orders from orders GROUP BY cust_id HAVING COUNT() >=2;

同时使用WHERE和HAVING子句

• 价格大于等于10
• vend_id分组
• 过滤COUNT(*)>=2分组

select vend_id, COUNT() AS num_prods from products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT() >= 2;

分组和排序

检索订总计订单价格大于等于50的订单的订单号和总计订单的价格

SELECT order_num, SUM(quantityitem_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price) >= 50;

按总计订单价格排序输出，需要添加ORDER BY子句

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

image

使用子查询

需要列出订购物品TNT2的所有客户，应该怎样检索？
1. 检索包含物品TNT的所有订单的编号
2. 检索具有前一步聚列出的订单编号的所有客户的ID
3. 检索前一步聚返回的所有客户ID的客户信息

对于prod_id 为TNT2的所有订单物品，从orderitems表中检索其order_num列

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

image

查询具有订单20005和20007的客户ID

• IN 范围设定

SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

把第一步的查询变为子查询，组合两个查询
mysql实际执行了两个操作

• 执行查询：SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'; 返回20005和20007两个订单号
• IN操作，外部查询变成；SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

检索客户ID信息

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);

转换为子查询

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

作为计算字段使用子查询

对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询

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

联结表

• 这里有两张表， vendors和products，这就是联结的两个表的名字
• 用WHERE子句正确联结，过滤条件
• WHERE子句指示MYSQL匹配vendors表中的vend_id和products表中的vend_id

SELECT vend_name, prod_name, prod_price
-> FROM vendors, products
-> WHERE vendors.vend_id = products.vend_id
-> ORDER BY vend_name, prod_name;

笛卡儿积

• 由没有联结条件的表关系返回的结果为笛卡儿积
• 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

内部联结

• from子句的的组成由INNER JOIN指定
• 联结条件使用ON，而不是WHERE

SELECT vend_name, prod_name, prod_price
-> FROM vendors INNER JOIN products
-> ON vendors.vend_id = products.vend_id;

联结多个表

显示编号为20005订单中的物品

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;

创建高级联结

使用表别名

• concat :拼接字段
• RTrim :删除右侧多余空格
• AS使用别名

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

使用不同类型的联结

• 自联结
• 自然联结
• 外部联结

自联结

• 两张相同的表products
• 使用别名p1,p2
• 联结表
• 按p2过滤数据

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

自然联结

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

外部联结

联结包含了那些在相关表中没有关联行的行

• OUTER JOIN: 指定联结类型
• 必须使用RIGHT或者LEFT关键字指定包括其所有行的表
• RIGHT :OUTER JOIN右边的表
• LEFT：OUTER JOIN左边的表
• 从from子句的左边表(customers)中选择所有行

SELECT customers.cust_id, orders.order_num
-> FROM customers LEFT OUTER JOIN orders
-> ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

检索所有客户及每个客户所下的订单数，使用COUNT（）函数

• INNER JOIN将customers和orders两张表关联起来
• GROUP BY 按客户分组数据
• COUNT(orders.order_num)对每个客户的订单技术

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

组合查询

创建组合查询

需要价格小于等于5的所有物品的一个列表，而且包括供应商1002和1001生产的所有物品

• union 操作符

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 all不取消重复行

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

对组合查询结果排序

• ORDER BY 不允许使用多条子句
• 组合查询中，虽然ORDER BY放在最后一条语句中，但实际上它可以排序所有SELECT语句

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

全文本搜索

Match()指定被搜索的列
Against()指定要使用的搜索表达式

• Match(note_text)： 对note_text列进行搜索
• Against('rabbit')： 指定词rabbit作为搜索文本

SELECT note_text
-> FROM productnotes
-> WHERE Match(note_text) Against('rabbit');

实用查询扩展

• with query expansion

SELECT note_text
-> FROM productnotes
-> WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

布尔文本搜索

• IN BOOLEAN MODE

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

匹配包含heavy但不包含以rope开始的行

• -排除一个词
• *截断操作符

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

插入数据

插入完整的行

• 存储到每个列表中的值在VALUES

INSERT INTO customers
-> VALUES(
-> NULL,
-> 'Pep E. LaPew',
-> '100 Main Street',
-> 'Los Angeles',
-> 'CA',
-> '90046',
-> 'USA',
-> NULL,
-> NULL);

Query OK, 1 row affected (0.01 sec)

编写更加安全的INSERT语句

• 插入行时，用VALUES列表中的相应值填入列表中的对应项

INSERT INTO customers(cust_name,
-> cust_address,
-> cust_city,
-> cust_state.
-> cust_zip,
-> cust_country,
-> cust_contact,
-> cust_email)
-> VALUES('Pep E. La Pew',
-> '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(
-> 'treehl',
-> 'baoshan',
-> 'shanghai',
-> 'sh',
-> '0000',
-> 'cn'
-> ),
-> (
-> 'yang',
-> 'baoshan',
-> 'shanghai',
-> 'sh',
-> '1111',
-> 'cn'
-> );

插入检索出的数据

INSERT SELECT

INSERT INTO customers (cust_id,
..............
)
SELECT cust_id,
...............
FROM ...;

更新和删除数据

更新数据

UODATE

• 更新表中所有行
• 更新表中特定行

更新表customers
set命令用来将新值赋给被更新的列，set子句设置cust_email为指定的列
WHERE子句：告诉MySQL更新哪一行

UPDATE customers
-> SET cust_email = 'elmer@fudd.com'
-> WHERE cust_id = 10005;

使用NULL来删除某个列的值

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

删除数据

DELETE

• 从表中删除特定的行
• 从表中删除所有行
• 不需要列名或通配符
• 删除整行而不是删除列

DELETE FROM - 指定从哪个表删除
WHERE- 子句过滤要删除的行

从customers表中只删除id为10006的

DELETE FROM customers WHERE cust_id = 10006;

创建和操作表

表创建基础

NOT NULL - 阻止插入没有值的列

CREATE TABLE customers
-> (
-> cust_id int NOT NULL AUTO_INCREMENT,
-> cust_name char(50)NOT NULL,
-> cust_address char(50)NOT 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

使用NULL值

主键

定义主键例子

PRIMARY KEY(vend_id)

由多个列组成的主键

CREATE TABLE orderitems
-> (
-> order_num int NOT NULL,
-> order_item int NOT NULL,
-> prod_id char(10) NOT NULL,
-> quantity int NOT NULL,
-> item_price decimal(8, 2) NOT NULL,
-> PRIMARY KEY (order_num, order_item)
-> )ENGINE=InnoDB;

AUTO_INCREMENT
auto_increment- 自动增量

• auto_increment告诉MySQL，本列每当增加一行时自动增量，执行一个INSERT操作，自动对该列增量
• 每个表只允许一个AUTO_INCREMENT

cust_id int NOT NULL AUTO_INCREMENT

指定默认值

• default ；表示在没有给出数据时，默认就是1

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 一个可靠的事务处理引擎，它不支持全文本搜索
• MyISAM 一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
• MEMORY 功能等同于MyISAM，但由于数据存储在内存中，速度很快（适合临时表）

ENGINE=InnoDB

更新表

• ALTER TABLE + 表名

给vendors表增加vend_phone列，必须明确其数据char(20)

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除刚刚添加的列

• DROP COLUMN

ALTER TABLE vendors DROP COLUMN vend_phone;

删除表
假设有这个表

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 TO customers;

重命名多个表

RENAME TABLE backup_customers TO customers,
-> backup_vendors TO vendors,
-> backup_products TO products;

使用视图

• 用CREATE VIEW来创建
• 使用SHOW CREATE VIEW viewname 来查看创建视图的语句
• 用DROP删除视图

使用视图简化复杂的联结

创建了一个名为productcustomers的视图，它联结三个表

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;

检索订购了产品TNT2的客户

• 它将指定的WHERE子句添加到试图查询中的已有的WHERE子句中，以便正确过滤数据

SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

用视图重新格式化检索出的数据

创建视图

CREATE VIEW vendorlocations AS
-> SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
-> FROM vendors
-> ORDER BY vend_name;

检索出以创建所有邮件标签的数据

SELECT * FROM vendorlocations;

用视图过滤不想要的数据

定义customeremaillist视图，过滤没有邮箱的客户

CREATE VIEW customeremaillist AS
-> SELECT cust_id, cust_name, cust_email
-> FROM customers
-> WHERE cust_email IS NOT NULL;

检索由邮箱的客户

SELECT * FROM customeremaillist;

使用试图与计算字段

检索某个特定订单中的物品，计算每种物品的总价格

CREATE VIEW orderitemsexpanded AS
-> SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_orice
-> FROM orderitems;

检索订单20005的详细内容

SELECT * FROM orderitemsexpanded WHERE order_num = 20005;

使用存储过程

执行存储过程
执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);

创建存储过程

• 存储过程名为productpricing()
• BEGIN END用来限定过程存储体
• 这里SELECT语句结束后用分号有可能会报错
• DELIMITER // 分隔符

DELIMITER //
mysql> CREATE PROCEDURE productpricing()
-> BEGIN
-> SELECT Avg(prod_price) AS priceaverage
-> FROM products;
-> END //
Query OK, 0 rows affected (0.00 sec)

使用存储过程

• 使用分隔符（DELIMITER // 后需要再输入 DELIMITER ;）

CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;

使用参数

• decimal 十进制
• pl 存储最低价
• ph 存储最高价
• pa 存储产品平均价
• OUT 指出相应的参数用来从存储过程传出一个值
• 存储过程的代码位于BEGIN和END之间
• 指定INTO保存到相应的变量

DELIMITER //
mysql> CREATE PROCEDURE productpricing(
-> OUT pl DECIMAL(8,2),
-> OUT ph DECIMAL(8,2),
-> OUT pa DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Min(prod_price)
-> INTO pl
-> FROM products;
-> SELECT Max(prod_price)
-> INTO ph
-> FROM products;
-> SELECT Avg(prod_price)
-> INTO pa
-> FROM products;
-> END //

为调用此修改过的存储过程，必须指定三个变量名

• 所有MySQL变量名都必须以@开始

CALL productpricing(@pricelow,
-> @pricehigh,
-> @priceaverage);

检索出产品的平均价格

SELECT @priceaverage;

检索平均价格，最高价格，最低价格

SELECT @pricehigh, @pricelow, @priceaverage;

IN 和OUT参数

• onumber定义为IN
• ototal定义为OUT
• where子句使用onumber选择正确的行
• INTO使用ototal存储计算出来的合计

ordertotal接受订单并返回该订单的合计

• onumber定义为IN
• ototal定义为OUT
• where子句使用onumber选择正确的行
• INTO使用ototal存储计算出来的合计

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 //

调用新存储过程

• 第一个参数为订单号
• 第二个参数为包含计算出来的合计的变量名

CALL ordertotal(20005, @total);

显示合计

SELECT @total;

建立智能存储过程

检查存储过程

• 获得何时、由谁创建等详细信息的存储过程列表

SHOW CREATE PROCEDURE ordertotal;

使用游标

游标（cursor）：是一个存储在MySQL服务器上的数据库查询

创建游标
定义了ordernumbers的游标

• DECLARE语句定义和命名游标ordernumbers
• 存储过程处理完成后，游标就消失（因为它仅限于存储过程）

CREATE PROCEDURE processorders()
-> BEGIN
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> END //

打开或关闭游标

OPEN ordernumbers;

CLOSE ordernumbers;

使用游标数据

• FETCH语句分别访问它的每一行

从游标中检索单个行

CREATE PROCEDURE

使用触发器

创建触发器

CREATE TRIGGER - 建立触发

CREATE TRIGGER newproduct AFTER INSERT ON products
-> FOR EACH ROW SELECT 'Product added';

删除触发器

DROP TRIGGER newproduct;

管理事务处理

• InnoDB引擎支持事务处理管理

• transaction 事务处理 指一种SQL语句

• rollback 回退 指撤销指定SQL语句的过程

• commit 提交 指将未存储的SQL语句结果写入数据库表

• savepoint 保留点 指事务处理中设置的临时占位符

• START TRANSACTION 标识事务的开始

使用ROLLBACK

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

1. 执行一条select语句显示该表不为空
2. 开始食物处理
3. 删除表中所有的行
4. select 验证表确实为空
5. 回退到START TRANSACTION之后的所有语句
6. 最后一条select显示该表不为空

使用COMMIT

使用保留点

SAVEPOINT delete1;

更改默认的提交行为

• autocommit 标志决定是否自动提交更改

SET autocommit=0;

全球化和本地化

使用字符集和校对顺序

SHOW CHARACTER SET;

查看所支持校对的完整列表

SHOW COLLATION

给表指定字符集和校对

• 创建一个包含两个列的表
• 指定一个字符集
• 制定一个校对顺序

CREATE TABLE mytable
-> (
-> column1 INT,
-> column2 VARCHAR(10)
-> ) DEFAULT CHARACTER SET hebrew
-> COLLATE hebrew_general_ci;

安全管理

获得所有用户账号的列表

USE mysql

SELECT user FROM user;

创建用户账号

• identified 确定

CREATE USER treehl IDENTIFIED BY '密码';

重新命名一个帐户

RENAME USER treehl TO treehl1;

删除用户账号

DROP USER treehl1;

设置访问权限
查询用户权限

SHOW GRANTS FOR treehl;

image

USAGE表示没有权限

用GRANT设置权限

GRANT SELECT ON MySQL_test.* TO treehl;

显示更改后的权限

show grants for treehl;

撤销权限

REVOKE SELECT ON MySQL_test.* FROM treehl;

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

1. 整个服务器，GRANT ALL REVOKE ALL
2. 整个数据库 使用ON databases.*
3. 特定的表 使用ON database.table

更改口令

SET PASSWORD FOR treehl = Password('密码');

数据库维护

刷新数据

FLUSH TABLES;

进行数据库维护

analyze table - 分析表

ANALYZE TABLE orders;

check table - 检查表

CHECK TABLE ORDERS, ORDERITEMS;

争端启动问题

mysqld

改善性能

查看所有活动进程

SHOW PROCESSLIST;

查看当前设置

SHOW VARIABLES;
SHOW STATUS;

GitHub
欢迎访问博客Treehl的博客