MySQL必知必会学习笔记-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40302781/article/details/136814557

MySQL必知必会

1MySQL简介

数据库：保存有组织数据的容器；

数据库管理系统（DBMS）:使用数据库管理软件创建和操作数据库；

表(table)：数据库中保存数据的特定结构化的清单；

列(column)：表中的字段，一张表有一个或多个列组成；

数据类型(datatype)：字段允许所保存的数据的类型；

行(row)：每一行都是一笔记录数据，也称为record;

主键（primary key）：每一行都有一个或一组字段区分唯一行，这一个或一组字段称为主键；

主键满足条件：任意两行数据不具有相同的主键，每行必须有一个主键值，不能为NULL;

多列作为主键时，某列值可以重复，但是所有列值的组合必须是唯一的；

如何更好定义主键？

不更新主键列值，不重用主键列值，不把要更新的列作为主键；

SQL(结构化查询语言):Structured Query Language。

MySQL:是一种DBMS,开源，免费，简单，易用；

DBMS分类：1.基于共享文件系统的DBMS,例如：Microsoft Access,FileMaker;

2.基于客户机-服务器的DBMS,例如：MySQL,Oracle;

MySQL执行工具：

Windows安装MySQL后,通过cmd,执行命令行

mysql命令行：

输入mysql

2．MySQL命令用“；”或“\g”结束，换行不结束；

示例：show databases;

3.输入“quit”或“exit”退出；

4.输入“help”或“\h”获取帮助；

MySQL Administrator:MySQL图形交互客户机；

MySQL Query Browser:MySQL图形交互客户机；

数据库相关命令行：

show databases,显示所有数据库;
use test,访问database:test;

show tables, 显示数据库下所有table;

show columns from test,显示test表所有列；

或者使用 describe test;

show status，显示服务器状态；
show grants,显示授权用户；
show errors或show warnings,显示服务器错误或警告信息；
使用root账号登陆，查看用户表信息；

命令：mysql -u root -p

命令：show databases,查看所有数据库；

命令：use mysql,操作mysql数据库；

2MySQL查询数据

查询单列值

#2.1查询单个栏位

SELECT prod_name FROM products;

注意：查询语句会返回表products所有prod_name；

查询多列值

#2.1查询多个栏位

SELECT prod_id,prod_name,prod_price FROM products;

查询所有列值

#2.1查询所有栏位

SELECT * FROM products;

查询某列非重复值

#2.1查询所有非重复供应商id

SELECT DISTINCT vend_id FROM products;

注意：当有多列时，使用Distinct会比较所有列值；

查询限定行数数据

#2.1查询2行数据

SELECT * FROM products LIMIT 2;

#2.1查询指定行数据

#从第3行开始，查询1行数据

SELECT * FROM products LIMIT 2,1;

#2.1避免误会，指定limit,offset

SELECT * FROM products LIMIT 1 OFFSET 2 ;

3MySQL排序数据

MySQL排序数据使用Order By子句。

使用查询的列排序

#3.1排序

#使用查询的列排序

SELECT prod_name FROM products ORDER BY prod_name;

注意：字符串类型列按照字符顺序大小排序

使用非查询的列排序

#使用非查询的列排序

SELECT prod_name FROM products ORDER BY prod_price;

注意：这里根据产品价格对查询的产品名进行排序；

多列排序

#多列排序

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

指定排序方向

#指定排序方向

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

注意：DESC表示降序，ASC表示升序，默认ASC可以不写；

4MySQL过滤数据

MySQL使用Where子句过滤数据

操作符：>,<,<=,>=,=,!=,<>,BETWEEN

筛选数据

#4.1筛选数据

#筛选产品价格大于20的产品

SELECT prod_name FROM products WHERE prod_price > 20;

#筛选产品价格在5~10之间的产品

SELECT prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;

空值筛选

#空值筛选

SELECT cust_name FROM customers WHERE cust_email IS NULL;

AND操作符筛选

AND可以连接多个筛选条件，筛选出符合所有条件的数据行。

#AND操作符

SELECT prod_name FROM products WHERE vend_id = '1001' AND prod_price > 10;

OR操作符筛选

OR可以连接多个筛选条件，只要数据行符合某一个筛选条件就可以筛选出来。

#OR操作符

SELECT prod_name FROM products WHERE vend_id = '1001' OR prod_price < 6;

IN操作符筛选

IN执行速度比OR更快，指定值清单，语句更清晰。

#IN操作符

SELECT vend_id,prod_name FROM products WHERE vend_id IN ('1001','1002');

NOT操作符筛选

使用NOT可以将IN,BETWEEN,EXISTS子句结果取反。

#NOT操作符

SELECT vend_id,prod_name FROM products WHERE vend_id NOT IN ('1001','1002');

LIKE通配符筛选

%表示任何字符出现任意次数。

_表示任意一个字符。

#LIKE通配符

SELECT prod_name FROM products WHERE prod_desc LIKE 'Jet%'

返回产品描述Jet开头的产品名

#_匹配单个字符

SELECT prod_id,prod_name FROM products WHERE prod_id LIKE 'TNT_';

返回产品ID：TNT开头，并且字符长度为4的产品

正则表达式筛选

使用正则表达式匹配指定字符进行数据筛选。

LIKE和REGEXP非常类似，但是LIKE是对完整列值进行匹配（不使用通配符情况），而REGEXP对列的值进行部分匹配，只要出现部分就算匹配。

#正则表达式筛选

SELECT prod_name FROM products WHERE prod_desc REGEXP 'Jet';

#筛选大小写区分

SELECT prod_name FROM products WHERE prod_desc REGEXP BINARY 'jet';

注意：默认MySQL不区分大小写，使用REGEXP BINARY区分大小写。

#筛选多个子字符串

SELECT prod_name FROM products WHERE prod_desc REGEXP '1|5';

注意：这里筛选产品描述中包含1或者5的产品

#匹配动态字符串，一部分固定，一部分变化

SELECT prod_name FROM products WHERE prod_desc REGEXP '[1-9] ton';

注意：这里匹配”1 ton”到”9 ton”，产品描述包含这些子字符串的产品

#. _特殊字符

#. 正则中表示任意字符

#_ 正则中表示任意一个字符

#如果要匹配.需要使用\\转义

SELECT prod_name FROM products WHERE prod_desc REGEXP '\\.';

字符集定义：

#字符集

SELECT prod_name FROM products WHERE prod_desc REGEXP 's[:alnum:]';

使用元字符进行匹配数目控制：

#控制匹配数量

SELECT prod_name,prod_desc FROM products WHERE prod_desc REGEXP '[[:digit:]]{4}';

使用定位符，对字符匹配位置进行控制：

#定位符位置匹配

SELECT prod_name,prod_desc FROM products WHERE prod_desc REGEXP '^[[:digit:]]|)$';

表示获取产品描述以数字开头，或者产品描述以)结尾的产品

5MySQL计算字段

当存储的字段是需要进行转换，计算或格式化的数据时，需要使用计算字段。

连接计算&算术运算

大多数DBMS使用“+”或者“||”连接字段，但是MySQL使用concat()函数进行字段拼接。

示例：

#5.1计算字段

#字段连接,返回拼接后字段

SELECT CONCAT(vend_name,'(',vend_country,')') AS vend_info FROM vendors;

#字段算术计算

#将每个item数量*单价，计算总价

SELECT prod_id,item_price,quantity,item_price * quantity AS total_price FROM orderitems;

文本函数运算

#函数计算

#大小写转换

SELECT vend_name,UPPER(vend_name) AS up_vend_name FROM vendors;

SELECT vend_name,LOWER(vend_name) AS low_vend_name FROM vendors;

#返回字符串左或右字符字串

SELECT LEFT(vend_name,10) FROM vendors;

SELECT RIGHT(vend_name,10) FROM vendors;

#返回字符串长度

SELECT vend_name,LENGTH(vend_name) FROM vendors;

#返回字符串子串位置，从1开始计算

SELECT vend_name,LOCATE('n',vend_name) FROM vendors;

#去除字符串左右空格

SELECT vend_name,LTRIM(vend_name) FROM vendors;

SELECT vend_name,RTRIM(vend_name) FROM vendors;

SELECT vend_name,TRIM(vend_name) FROM vendors;

#返回字符串字串

#substring(string,start,length)

SELECT vend_name,SUBSTRING(vend_name,1,5) FROM vendors;

#返回发音类似的字符串

#这里可以返回Jet Set

SELECT vend_name FROM vendors

WHERE SOUNDEX(vend_name) = SOUNDEX('Jit Set')

日期处理函数

#日期函数

#DATE日期

SELECT * FROM orders

WHERE DATE(order_date) = '2005-09-01';

#年月日函数

SELECT * FROM orders

WHERE YEAR(order_date) = 2005

AND MONTH(order_date) = 9

AND DAY(order_date) = 1

#时分秒函数

SELECT HOUR(NOW()),

MINUTE(NOW()),

SECOND(NOW())

FROM orders LIMIT 1;

#日期周几

#星期日为1，星期一为2

SELECT NOW(),DAYOFWEEK(NOW()) FROM orders LIMIT 1;

#当前日期时间

SELECT CURDATE(),CURTIME(),NOW() FROM orders LIMIT 1;

#日期计算

SELECT ADDDATE(CURDATE(),10) FROM orders LIMIT 1;

SELECT ADDTIME('10:10:20',-60*10) FROM orders LIMIT 1;

#日期之差

SELECT DATEDIFF('2005-10-04','2010-12-05') FROM orders LIMIT 1;

#灵活计算日期

#INTERVAL单位：

#MICROSECOND:微秒

#SECOND:秒

#MINUTE:分钟

#HOUR:小时

#DAY:天

#WEEK:周

#MONTH:月

#QUARTER:季度

#YEAR:年

SELECT DATE_ADD(NOW(),INTERVAL -25 HOUR) FROM orders LIMIT 1;

数值处理函数

#数值处理函数

#绝对值

SELECT ABS(-100) FROM orders LIMIT 1;

#余弦值

SELECT COS(1) FROM orders LIMIT 1;

#正弦值

SELECT SIN(1) FROM orders LIMIT 1;

#正切值

SELECT TAN(1) FROM orders LIMIT 1;

#随机数

SELECT RAND() FROM orders LIMIT 1;

#圆周率

SELECT PI() FROM orders LIMIT 1;

#平方根

SELECT SQRT(2) FROM orders LIMIT 1;

#e指数值

SELECT EXP(1) FROM orders LIMIT 1;

#余数10%4

SELECT MOD(10,4) FROM orders LIMIT 1;

聚集函数

AVG函数会忽略NULL值行；

COUNT(*)函数不会忽略NULL值行；

COUNT(column)函数会忽略NULL值行；

MAX函数忽略NULL值行；

MIN函数忽略NULL值行；

SUM函数忽略NULL值行；

#汇总函数

#均值

SELECT AVG(prod_price) AS avg_price FROM products;

#计算列数

SELECT COUNT(*) FROM products;

#最大值

SELECT MAX(prod_price) AS max_price FROM products;

#最小值

SELECT MIN(prod_price) AS min_price FROM products;

#加总

SELECT SUM(prod_price) AS sum_price FROM products;

#使用DISTINCT，汇总不同行

SELECT COUNT(DISTINCT prod_id),COUNT(*) FROM productnotes;

6MySQL数据分组

使用GROUP BY实现数据分组

#数据分组

#获取每个customer订单数

SELECT cust_id,COUNT(cust_id) FROM orders

GROUP BY cust_id;

#WITH ROLLUP

#返回每个分组统计值，以及汇总的统计值

SELECT cust_id,COUNT(cust_id) FROM orders

GROUP BY cust_id

WITH ROLLUP;

使用HAVING子句，对分组汇总数据进行筛选

#筛选订单数大于2的客户id

SELECT cust_id,COUNT(cust_id) FROM orders

GROUP BY cust_id

HAVING COUNT(cust_id) > 1;

注意：WHERE和HAVING区别在于WHERE在分组前过滤，HAVING在分组统计后过滤；

SELECT中各种子句执行顺序：

SELECT:查询语句

FROM:选择查询表

WHERE:行级过滤

GROUP BY:分组

HAVING:分组过滤

ORDER BY:排序

LIMIT:限制行数

7MySQL子查询

MySQL中不同信息存储在不同表中，当要查询复合信息时，需要用到子查询。子查询总是从里到外执行的。

示例：

查询购买了产品ID为’FB’的客户名称

#子查询

#分步执行

#获取订单号：20005，20009

SELECT order_num FROM orderitems

WHERE prod_id = 'FB')

#根据订单号获取订单对应客户id

SELECT cust_id FROM orders

WHERE order_num IN (20005,20009);

#获取客户信息

SELECT cust_name FROM customers

WHERE cust_id = 10001;

#使用子查询

SELECT cust_name FROM customers

WHERE cust_id IN(

SELECT cust_id FROM orders

WHERE order_num IN (

SELECT order_num FROM orderitems

WHERE prod_id = 'FB')

);

#将子查询作为计算字段

SELECT cust_name,(

SELECT COUNT(*)

FROM orders

WHERE cust_id = customers.cust_id) AS order_num

FROM customers;

8MySQL连接查询

MySQL使用JOIN进行连表查询。通过JOIN可以将多个Table信息进行连接查询。

#内连接查询

#查询供应商名称，地址，产品id,产品描述

SELECT

vend_name,

vend_address,

prod_id,prod_desc

FROM vendors

INNER JOIN products

WHERE vendors.vend_id = products.vend_id ;

自联结

使用别名简化表名

#自联结

#查找产品“FB"的供应商提供的所有产品id,产品名

SELECT

a.prod_id,

a.prod_name

FROM

products AS a

INNER JOIN

products AS b

WHERE a.vend_id = b.vend_id

AND b.prod_id = 'FB'

外连接

左外联和右外联

左外联：保留左边表所有数据

右外联：保留右边表所有数据

#左外联

#统计所有客户各自订单数

SELECT

a.cust_name,

a.cust_id,

COUNT(b.order_num) AS n_order

FROM customers AS a

LEFT OUTER JOIN orders AS b

ON a.cust_id = b.cust_id

GROUP BY a.cust_id;

UNION合并查询：

UNION必须由两条或以上SELECT语句组成。

UNION的语句查询列必须相同。

UNION会自动去除重复行。

UNION ALL会保留所有行。

#组合查询

#查询供应商id:1001,1002数据

#查询产品ID为’Safe'数据

#UNION合并输出

SELECT prod_id,prod_name,vend_id

FROM products

WHERE vend_id IN (1001,1002)

UNION

SELECT prod_id,prod_name,vend_id

FROM products

WHERE prod_name = 'Safe';

9MySQL全文搜索

MySQL并非所有引擎支持全文搜索,只有MyISAM支持全文搜索。InnoDB不支持全文搜索。

示例表productnotes使用MyISAM引擎。

如何启用全文索引支持？

通过CREATE TABLE,FULLTEXT指定支持全文索引支持的数据列

注意：一般在导入数据时不要启用FULLTEXT,需要在导入数据后在更新表启用FULLTEXT

###########################

# Create productnotes table

###########################

CREATE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL,

note_date datetime NOT NULL,

note_text text NULL ,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

使用Match()和Against()进行全文搜索

#全文搜索

SELECT note_text

FROM

productnotes

WHERE

MATCH(note_text)

AGAINST('heavy');

实现方式：将查询字符串匹配程度作为计算列note_rank，没有匹配的值为0，然后筛除note_rank为0的数据，然后对note_rank排序。

#对全文搜索匹配程度排序

#最先找到匹配文本的优先度更高

SELECT

note_text,

MATCH(note_text) AGAINST('heavy') AS note_rank

FROM

productnotes

WHERE

MATCH(note_text)

AGAINST('heavy');

查询拓展：

当‘heavy’直接查询到是权重最高的；

出现‘heavy‘所在行的其他字串在其他地方出现，权重变低；

#查询拓展

SELECT

note_text

FROM

productnotes

WHERE

MATCH(note_text)

AGAINST('heavy' WITH QUERY EXPANSION);

布尔文本搜索：

使用IN BOOLEAN MODE进行布尔文本搜索；

没有FULLTEXT索引，也可以使用布尔文本搜索；

示例1：包含多个单词‘heavy’,’flying’

#必须包含heavy flying

SELECT

note_text

FROM

productnotes

WHERE

MATCH(note_text)

AGAINST('+heavy +flying' IN BOOLEAN MODE);

示例2：包含多个单词中某一个

#包含heavy,stars,Item中某一个单词

SELECT

note_text

FROM productnotes

WHERE

MATCH(note_text)

AGAINST('heavy stars Item' IN BOOLEAN MODE);

示例3：包含短语，整个匹配才返回

#包含短语

SELECT

note_text

FROM productnotes

WHERE

MATCH(note_text)

AGAINST('"Please note"' IN BOOLEAN MODE);

示例4：排除单词

#排除单词

SELECT

note_text

FROM productnotes

WHERE

MATCH(note_text)

AGAINST('rabbit -food -heavy' IN BOOLEAN MODE);

10MySQL数据插入

MySQL使用INSERT INTO执行数据插入。

#插入语句

#这种方式不推荐

#当表结构改变时，这种语句就会报错

#指定对应列每个值

INSERT INTO

customers

VALUES(

NULL,

'tomas',

'445 street',

'Londom',

'BA',

'44443',

'USA',

NULL,

NULL

)

#指定列名对应每个列值

#不需要强制给值的列可以不用

INSERT INTO

customers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

)VALUES(

'tomas',

'445 street',

'Londom',

'BA',

'44443',

'USA'

)

插入多条数据语句：

#插入多条语句

INSERT INTO

customers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

)VALUES(

'tom',

'225 street',

'Londom',

'BA',

'44443',

'USA'

),(

'tian',

'425 street',

'Afeidil',

'CA',

'44423',

'USA'

),(

'jim',

'485 street',

'dayrain',

'CA',

'43323',

'USA'

);

插入查询的数据：

#创建一个新表

#插入筛选后的数据

CREATE TABLE custnew

(

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;

#将cust_concact为null的数据行插入新表

INSERT INTO

custnew(

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_contact IS NULL;

注意：INSERT INTO对应列名可以和SELECT语句中列名不一样，他们是通过位置对应的。

11MySQL数据更新、删除

MySQL使用UPDATE进行数据更新。

示例：

#更新语句

#1.更新单个栏位

UPDATE customers

SET cust_email = 'Coyot@33.com'

WHERE cust_id = 10002;

#2.更新多个栏位

UPDATE customers

SET cust_name = CONCAT(cust_name,'(flag)'),

cust_email = 'test@33.com'

WHERE cust_id = 10005;

#3.更新多行时，当某一行出错时，忽略继续更新

UPDATE IGNORE products

SET prod_price = prod_price + 10;

MySQL使用DELETE删除数据。

示例：

#删除语句

#删除特定行

DELETE FROM customers

WHERE cust_id = 10009;

#删除所有行

DELETE FROM customers;

12MySQL表的操作

MySQL不仅可以操作表数据，还可以操作表。

可以使用交互式创建和管理表的工具；

也可以直接使用SQL语句创建表；

创建表

示例：

#创建Table

#当创建tables，table名称在当前数据库中必须唯一

#可以使用IF NOT EXISTS,如果table不存在才创建

CREATE TABLE IF NOT EXISTS table1

(

id int NOT NULL AUTO_INCREMENT,

name char(50) NOT NULL,

address char(255) NULL,

notes text NULL,

PRIMARY KEY (id)

) ENGINE = InnoDB;

#创建table

#使用DEFAULT设置默认值，当插入数据时，没有设置值时使用默认值

CREATE TABLE IF NOT EXISTS table2

(

id int NOT NULL AUTO_INCREMENT,

name char(50) NOT NULL,

address char(255) NULL,

relation text NULL,

account float DEFAULT 0.0,

PRIMARY KEY (id)

) ENGINE = InnoDB;

数据库引擎类型

InnoDB:可靠的事务处理引擎，不支持全文搜索

MEMORY:和MyISAM,数据存储在内存，速度更快，适合临时表；

MyISAM:高性能引擎，支持全文搜索，不支持事务处理；

不同的表可以使用不同的引擎。但是有外键关系的表之间不能使用不同引擎。

表的更新删除

示例：

#更新表

#添加列

ALTER TABLE table1

ADD age int DEFAULT 0;

#添加多列

ALTER TABLE table1

ADD flag1 char(1),

ADD flag2 char(1);

#删除列

ALTER TABLE table1

DROP COLUMN flag2;

#删除多列

ALTER TABLE table1

DROP COLUMN flag1,

DROP COLUMN age;

#删除Table

#注意：此操作彻底删除table,不能撤销

DROP TABLE table2;

表添加删除外键

#添加外键

ALTER TABLE table2

ADD CONSTRAINT fk_t1_t2

FOREIGN KEY (t1_id) REFERENCES table1(id);

#直接删除外键

ALTER TABLE table2

DROP FOREIGN KEY fk_t1_t2;

表的重命名

使用RENAME TABLE进行表的重命名。

#重命名Table

RENAME TABLE table1 TO t_table1;

#重命名多个table

RENAME TABLE table1 TO t_table1,

table2 TO t_table2;

注意：复杂表结构更改一般步骤

创建新的列顺序的新表；

使用INSERT SELECT语句复制数据到新表；

检查新表数据；

更改旧表名称；

更改新表名到旧表名；

根据需要创建触发器、存储过程、索引外键；

13MySQL视图操作

MySQL数据库中不同数据存储在不同表中，但是往往业务需要返回的时多个表的联合数据。那么将常用的混合表数据做成视图，简化查询操作。

视图的作用：

1.重用SQL语句；

2.简化复杂的SQL语句；

3.使用表部分数据；

4.保护数据；

5.更改数据格式和表示；

创建视图

#创建视图

#客户购买产品视图

CREATE VIEW cust_order_prod AS

SELECT

cust_name,

prod_name,

prod_price,

quantity

FROM customers,orders,orderitems,products

WHERE customers.cust_id = orders.cust_id

AND orders.order_num = orderitems.order_num

AND orderitems.prod_id = products.prod_id ;

#查询视图

SELECT * FROM cust_order_prod;

使用视图筛选掉部分数据：

#使用视图筛选数据

CREATE VIEW cust_mail_not_null AS

SELECT * FROM customers

WHERE customers.cust_email IS NOT NULL;

#查询视图

SELECT * FROM cust_mail_not_null;

使用视图创建计算列

#使用视图计算列

CREATE VIEW order_calculate AS

SELECT *,

quantity * item_price AS sum_price

FROM orderitems;

#查询视图

SELECT * FROM order_calculate;

视图更新或删除：

#查看view创建的语句

SHOW CREATE VIEW cust_mail_not_null;

#删除视图

DROP VIEW cust_mail_not_null;

#创建或替换视图

CREATE OR REPLACE VIEW cust_mail_not_null AS

SELECT * FROM customers

WHERE customers.cust_email IS NOT NULL;

14MySQL存储过程操作

存储过程：是一系列SQL语句的集合，为了实现满足某个或多个业务流程对数据库数据进行操作。

创建存储过程：

#创建存储过程

#创建procedure

CREATE PROCEDURE pro_order_total()

BEGIN

SELECT

cust_name,

SUM(quantity) AS total_quan

FROM cust_order_prod

GROUP BY cust_name;

END;

#调用存储过程

CALL pro_order_total();

删除存储过程：

#删除存储过程

DROP PROCEDURE pro_order_total;

创建带参数的存储过程：

#使用有参数的存储过程

CREATE PROCEDURE pro_order_total1(

IN i_name char(50),

OUT o_quan decimal(8,2)

)

BEGIN

SELECT

SUM(quantity) AS total_quan

FROM cust_order_prod

WHERE cust_name = i_name

GROUP BY cust_name

INTO o_quan;

END;

#调用存储过程

CALL pro_order_total1('Coyote Inc.',@total);

#查询返回值

SELECT @total;

15MySQL游标操作

当我们需要在检索数据前进或后退几行是，需要使用MySQL的游标。

在MySQL中，游标只能在存储过程或函数中使用。

#游标使用示例

#只能在存储过程或函数中使用

CREATE PROCEDURE pro_cursor()

BEGIN

#定义局部变量

DECLARE total DECIMAL(8,2);

DECLARE name CHAR(50);

DECLARE v_rep BOOLEAN DEFAULT 0;

#游标

DECLARE custname CURSOR FOR

SELECT DISTINCT cust_name FROM cust_order_prod;

#定义循环结束响应条件

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_rep = 1;

#创建table存储结果

CREATE TABLE IF NOT EXISTS cust_total(

cust_name char(50),

total decimal(8,2)

);

#打开游标

OPEN custname;

#循环处理

REPEAT

#逐行访问name

FETCH custname INTO name;

#调用procedure获取total数量

CALL pro_order_total1(name,total);

#结果插入数据表

INSERT INTO cust_total(cust_name,total)VALUES(name,total);

#循环结束条件

UNTIL v_rep

END REPEAT;

#关闭游标

CLOSE custname;

END;

#调用过程

CALL pro_cursor();

16MySQL触发器操作

触发器：当执行DELETE，UPDATE,INSERT操作时，MySQL自动执行一句或一组SQL语句。

只有表支持触发器；

每个表最多支持6个触发器；

INSERT，UPDATE,DELETE之前或之后触发；

示例：

#创建log表

CREATE TABLE t_log(

id int NOT NULL AUTO_INCREMENT,

t_name char(50) NOT NULL,

opt char(100) NOT NULL,

op_date date NOT NULL,

op_time time NOT NULL,

PRIMARY KEY(id)

)ENGINE = InnoDB;

#创建触发器

CREATE TRIGGER newcustomer

AFTER INSERT ON customers

FOR EACH ROW

INSERT INTO t_log(t_name,opt,op_date,op_time)

VALUES('customers','INSERT',CURDATE(),CURTIME());

CREATE TRIGGER changecustomer

AFTER UPDATE ON customers

FOR EACH ROW INSERT INTO t_log(t_name,opt,op_date,op_time)

VALUES('customers','UPDATE',CURDATE(),CURTIME());

CREATE TRIGGER deletecustomer

AFTER DELETE ON customers

FOR EACH ROW INSERT INTO t_log(t_name,opt,op_date,op_time)

VALUES('customers','DELETE',CURDATE(),CURTIME());

#删除触发器

DROP TRIGGER newcustomer;

DROP TRIGGER changecustomer;

DROP TRIGGER deletecustomer;

INSERT触发器

可以使用变量NEW操作被插入的数据；

在BEFORE触发器中，可以更新NEW中被插入的值；

单AUTO_INCREMENT的列，在BEFORE触发器中是0，AFTER触发器中是新的生成值；

BEFORE触发器主要用于数据清洗，数据验证；

AFTER触发器主要用于保存日志等；

#INSERT

#BEFORE触发器

#插入时如果邮件地址为空，更新为none

CREATE TRIGGER insert_before_customer

BEFORE INSERT ON customers

FOR EACH ROW

BEGIN

DECLARE v_email char(255);

SELECT NEW.cust_email INTO v_email;

IF v_email IS NULL THEN

SET NEW.cust_email = 'none';

END IF;

END;

NEW可以直接使用，不用定义变量

#INSERT

#BEFORE触发器

#插入时如果邮件地址为空，更新为none

CREATE TRIGGER insert_before_customer

BEFORE INSERT ON customers

FOR EACH ROW

BEGIN

#DECLARE v_email char(255);

#SELECT NEW.cust_email INTO v_email;

IF NEW.cust_email IS NULL THEN

SET NEW.cust_email = 'none';

END IF;

END;

DELETE触发器

可以使用OLD访问被删除的数据；

#创建log表记录删除customer

CREATE TABLE IF NOT EXISTS log_del_cus(

id int NOT NULL AUTO_INCREMENT,

cust_id int,

cust_name char(50),

cust_address char(50),

cust_city char(50),

op_date date NOT NULL,

op_time time NOT NULL,

PRIMARY KEY(id)

)ENGINE = InnoDB;

#DELETE

#BEFORE触发器

CREATE TRIGGER delete_before_customer

BEFORE DELETE ON customers

FOR EACH ROW

BEGIN

INSERT INTO log_del_cus(

cust_id,

cust_name,

cust_address,

cust_city,

op_date,

op_time)

VALUES(

OLD.cust_id,

OLD.cust_name,

OLD.cust_address,

OLD.cust_city,

CURDATE(),

CURTIME());

END;

#测试删除数据

DELETE FROM customers

WHERE cust_name = 'Ling';

UPDATE触发器

可以使用OLD访问更新前数据；

可以使用NEW访问更新后数据；

NEW中数据可以被更新；

OLD中数据只可以被访问；

示例：

#UPDATE

#BEFORE触发器

#固定cust_name首字母大写

CREATE TRIGGER update_befor_customer

BEFORE UPDATE ON customers

FOR EACH ROW

BEGIN

SET NEW.cust_name = CONCAT(

UPPER(SUBSTRING(NEW.cust_name,1,1)),

SUBSTRING(NEW.cust_name,2)

);

END;

#执行更新

UPDATE customers

SET cust_name = 'ling zi'

WHERE cust_name = 'tom'

注意：触发器不支持CALL存储过程,只能将整个存储过程语句copy到触发器语句中。

17MySQL事务操作

MySQL使用ROLLBACK,COMMIT来管理事务。

事务处理是一种机制，用来保证业务过程中，一些必须成批执行的MySQL操作。保证数据库数据完整性。

事务：一组SQL语句；

回退：ROLLBACK,撤销SQL语句;

提交：COMMIT,将为存储的SQL语句结果执行写入数据库；

保留点：SAVEPOINT,事务处理中，临时占位符，可以回退到保留点，不回退整个事务；

事务只能管理UPDATE,INSERT,DELETE操作，不能影响SELECT;

事务也不能回退CREATE,DROP操作；

示例：

#事务处理

#事务开始，一旦COMMIT,ROLLBACK事务结束

SELECT * FROM t_log;

START TRANSACTION;

DELETE FROM t_log;

SELECT * FROM t_log;

ROLLBACK;

SELECT * FROM t_log;

查询t_log表，显示有数据；

Start transaction,开始事务；

Delete删除t_log表数据；

再次查询t_log表，显示没有数据；

执行rollback,回退；

再次查询t_log表，显示有数据；

COMMIT提交事务：

#提交事务

START TRANSACTION

DELETE FROM table2;

DELETE FROM table1;

COMMIT;

SAVEPOINT保留点:

保留点可以定义多个，灵活决定事务执行过程。

保留点在事务COMMIT,ROLLBACK后，自动释放，也可以通过RELEASE SAVEPOINT释放。

#保留点

SELECT * FROM custnew WHERE cust_id = 1;

#开始事务

START TRANSACTION;

UPDATE custnew

SET cust_city = 'sanfi'

WHERE cust_id = 1;

#查看数据

SELECT * FROM custnew WHERE cust_id = 1;

#定义保留点

SAVEPOINT update1;

#更新语句

UPDATE custnew

SET cust_city = 'roll'

WHERE cust_id = 1;

#查看数据

SELECT * FROM custnew WHERE cust_id = 1;

#回滚到保留点

ROLLBACK TO update1;

#查看数据

SELECT * FROM custnew WHERE cust_id = 1;

#释放保留点

RELEASE SAVEPOINT update1;

MySQL更改默认提交模式

#设置自动提交

#当autocommit=0关闭自动提交

SET autocommit = 1;

18MySQL全球化本地化

如何查看MySQL支持的字符集

#MySQL字符集

SHOW CHARACTER SET;

如何查看MySQL校对?

校对：规定字符如何比较的指令；

某些字符集对应的校对可能有多个，default表明默认使用的collation。

创建数据库时可以指定使用的字符串和校对；

#查看字符集校对

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation%';

创建Table时指定字符集和校对

示例：

#创建Table时指定字符集，校对

CREATE TABLE table3(

id int NOT NULL AUTO_INCREMENT,

name char(50),

PRIMARY KEY(id)

)DEFAULT CHARACTER SET utf8

COLLATE utf8_general_ci;

查询时可以指定校对

#查询时指定使用指定校对

SELECT * FROM vendors

ORDER BY vend_name COLLATE utf8_general_ci;

19MySQL安全管理

数据库包含许多关键业务数据，为确保数据安全以及完整需要对数据库用户进行访问控制和用户权限管理。

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

示例：

#使用MySQL数据库

#查看用户信息

USE mysql;

SELECT * FROM user;

创建用户账号

使用CREATE USER创建用户账号；

使用IDENTIFIED BY设置用户密码；

可以看到创建出来的用户Host栏位为%，这里值任何IP的主机都可以通过user_select和密码连接数据库。

#创建用户账号

#使用IDENTIFIED BY指定密码

CREATE USER user_select IDENTIFIED BY 'user_select123';

重命名用户账号

RENAME USER重命名用户账号

#重命名用户账户名

RENAME USER user_select TO userSelect;

删除用户账号

使用DROP USER删除用户账号

#删除用户账号

DROP USER userSelect;

设置用户权限

设置用户权限前，需要查看用户所拥有权限

GRANT USAGE ON *.* 表示用户没有任何权限

#查看用户权限

SHOW GRANTS FOR userSelect;

#设置用户权限

#赋予数据库crashcorse所有表查询权限

GRANT SELECT ON crashcourse.* TO userSelect;

赋予权限后查看：

移除用户权限

使用REVOKE FROM将用户权限移除

#移除用户权限

REVOKE SELECT ON crashcourse.* FROM userSelect;

权限清单：

ALL 除GRANT OPTION外的所有权限；

ALTER 使用ALTER TABLE；

ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE；

CREATE 使用CREATE TABLE；

CREATE ROUTINE 使用CREATE PROCEDURE；

CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE；

CREATE USER 使用CREATE USER、DROP USER、RENAME USER和REVOKE

ALL PRIVILEGES；

CREATE VIEW 使用CREATE VIEW；

DELETE 使用DELETE；

DROP 使用DROP TABLE；

EXECUTE 使用CALL和存储过程；

FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE；

GRANT OPTION 使用GRANT和REVOKE；

INDEX 使用CREATE INDEX和DROP INDEX；

INSERT 使用INSERT；

LOCK TABLES 使用LOCK TABLES；

PROCESS 使用SHOW FULL PROCESSLIST；

RELOAD 使用FLUSH；

REPLICATION CLIENT 服务器位置的访问；

REPLICATION SLAVE 由复制从属使用；

SELECT 使用SELECT；

SHOW DATABASES 使用SHOW DATABASES；

SHOW VIEW 使用SHOW CREATE VIEW；

SHUTDOWN 使用mysqladmin shutdown（用来关闭MySQL）；

SUPER 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER

和SET GLOBAL。还允许mysqladmin调试登录；

UPDATE 使用UPDATE；

USAGE 无访问权限；

示例：

#创建ALL权限账号

CREATE USER userAll IDENTIFIED BY 'userAll123';

#赋予权限

#ALL表示除了GRANT的其他所有权限

GRANT ALL ON crashcourse.* TO userAll;

#查看权限

SHOW GRANTS FOR userAll;

示例：注意权限赋予时，账号的主机地址；

#创建只能call procedure权限账号

#通过@指定host

CREATE USER userPro@127.9.1.1 IDENTIFIED BY 'userPro123';

#赋予权限

#这里我们赋予权限时，自动创建了userPro@%

GRANT EXECUTE ON crashcourse.* TO userPro;

#真实我们需要赋予权限的是userPro@127.9.1.1;

GRANT EXECUTE ON crashcourse.* TO userPro@127.9.1.1;

#查看权限

SHOW GRANTS FOR userPro;

SHOW GRANTS FOR userPro@127.9.1.1;

设置用户密码

使用SET PASSWORD更新密码。

#设置用户密码

SET PASSWORD FOR userPro = password('userPro111')

#不指定用户，默认更新当前用户密码

SET PASSWORD = password('defaultUser123')

20MySQL数据库维护

MySQL可以使用ANALYZE TABLE，CHECK TABLE对表进行检查。

示例：

#检查Table，键值是否正确

ANALYZE TABLE orders;

#检查Table,

CHECK TABLE orders,orderitems;

#只检查最后一次检查，修改过的表

CHECK TABLE orders,orderitems CHANGED;

#EXTENDED彻底检查

CHECK TABLE orders,orderitems EXTENDED;

#FAST只检测未正常关闭的表

#MEDIUM检查所有被删除的链接，进行键检查；

#QUICK快速扫描