可以使用 “Ctel+F”快速定位

MySQL数据库常用命令

创建数据库：create database 数据库名 [charset] [字符编码] [collate] [校验规则];

查看创建结果：show create database 数据库名;

查看所有数据库：show databases;

查看当前使用的数据库：select database();

命令行方式连接数据库：mysql -u 数据库用户名 -p 数据库密码

打开指定的数据库：use 数据库名;

命令行方式连接数据库：mysql -u数据库用户名 -p数据库密码

命令行方式备份数据库：mysqldump -u 数据库用户名 -p 目标数据库名 > 备份文件名.sql;

命令行方式恢复数据库：：mysql -u 数据库用户名 -p 目标数据库名 < 备份文件名.sql;

修改数据库：alter database [数据库名] [default] character set <字符集名> [default] collate <校对规则名>;

SQL语句方式删除数据库：drop database 数据库名;

在命令提示窗口中删除数据库：mysqladmin -u root -p drop 数据库名

设置主键：create table 数据表名(主键字段名 数据类型 unsigned PRIMARY KEY auto_increment, ...);

                create table 数据表名(字段名 数据类型, ...,primary key (主键字段名));

添加主键:  alter table 数据表名 add primary key(主键字段名);

删除主键：alter table 数据表名 drop primary key;

在创建数据库时设置外键：create table 数据表名(constraint 外键名 foreign key(自己的字段) references 主表(主表字段));

指定外键：alter table 表名 add constraint 外键名 foreign key(自己的字段) references 主表(主表字段);

删除外键：alter table 表名 drop foreign key 外键名称;

创建表：create table 表名(字段名 类型 约束, 字段名 类型 约束 ...) [引擎][字符集];

查看创建的表：show create table 表名;

创表语句：判断表是否存在，存在时先删除再创建

DROP TABLE IF EXISTS 表名;

CREATE TABLE 表名(字段名1 类型 约束, 字段名2 类型 约束, ...) ENGINE=INNODB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_general_ci;

CREATE TABLE 表名(字段名1 类型 约束, 字段名2 类型 约束, ...) ENGINE=INNODB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

unsigned：无符号（正负符号，无符号即为正）；

auto_increment：自增长；

设置自增长起始值：auto_increment=起始值

查看所有表：show tables;

查看表信息：show create table 表名;

查看表结构(字段)：desc 表名;

修改数据库字符集：alter database 数据库名 character set 字符集名 collate 校对规则;

修改表的字符集：alter table 表名 convert to character set 字符集名 collate 校对规则;

修改表的存储引擎:  alter table 表名 engine=引擎名;

复制表：

复制表结构：create table 新表名 like 旧表名;

同时复制结构和数据：create table 新表名 as (select * from 旧表名);

*前者会复制旧表完整结构，包括约束条件，但不复制数据；后者只复制基本结构和数据，但不复制约束条件。

删除数据表格式一：drop table 表名;

删除数据表格式二：drop table if exists 表名;

修改表名：alter table 原表名 rename 新表名;

修改字段数据类型：alter table 表名 modify 字段名 数据类型;

修改ENUM字段： alter table 表名 modify 字段名 enum("值1","值2",...,"值n");

修改SET字段： alter table 表名 modify 字段名 set("值1","值2",...,"值n");

修改表的字段为唯一性约束：alter table 表名 modify 字段名 数据类型 unique;

                                或者：alter table 表名 change 原字段名 新字段名 数据类型 unique;

删除表的字段唯一性约束：alter table 表名 drop index 唯一性字段索引;

                             或者：drop index 唯一性字段索引 on 表名; 

修改字段顺序：alter table 表名 modify 字段名 数据类型 first;

                      alter table 表名 modify 字段名1 数据类型 after 字段名2;

修改字段名称：alter table 表名 change 原字段名 新字段名 数据类型;

添加字段：alter table 表名 add 字段名 数据类型；

在表的第一列添加字段：alter table 表名 add 字段名 数据类型 first；

在表的指定列之后添加字段：alter table 表名 add 字段名 数据类型 after 字段名；

删除字段：alter table 表名 drop 字段名;

添加数据格式一：所有字段设置值，值的顺序与表中字段的顺序对应

说明：主键列是自动增长，插入时需要占位，通常使用0或者default或者null来占位，后以实际数据为准。

语法格式：insert into 表名 values(...)

 

添加数据格式二：部分字段设置值，值的顺序与给出的字段顺序对应

 语法格式：insert into 表名(字段1, ...) values(值1, ...)

添加多行数据：

写一条 insert 语句，设置多条数据，数据之间用英文逗号隔开

格式一：insert into 表名 values(...), (...) ...;

格式二：insert into 表名(列1, ...) values(值1, ...), (值2, ...) ...;

替换已存在主键值的记录数据：

replace into 表名(列1, ...) values(值1, ...), (值2, ...) ...;

*如果表中已经存在主键值，需要插入一个同键值数据，因为违反唯一性规则是不允许的，但是可能用另一记录替换这一记录的。

修改数据：update 表名 set 列1=值1, 列2=值2 ... where 条件;

删除数据格式一：delete from 表名 where 条件;       * 注意：where不能省略，否则会删除全部数据

删除数据格式二：truncate table 表名;        （删除表的所有数据，保留表结构）

删除数据格式三：drop table 表名;         （删除表，所有数据和表结构都删掉）

查询所有字段：select * from 表名;

查询部分字段：select 字段1, 字段2, ... from 表名;

查询时为了书写简单，给起别名：select 别名.字段1, 别名.字段2, ... from 表名 [as] 别名;

查询结果为了显示为与字段不同的名称，给字段取别名：select 字段1 as 别名1, 字段2 as 别名2, ... from 表名;

查询去重，只取唯一值：select distinct 字段1, ... from 表名;

复杂查询：

定义：在基础查询基础上，根据需求描述关系进行查询；实际应用中往往是多种复合查询的组合使用

分类：条件查询、聚合函数、排序查询、分组查询、分页查询、连接查询、自关联查询、子查询等

条件查询：select 字段1, 字段2 ... from 表名 where 条件;

* 说明：where后面支持多种运算符，进行条件的处理

比较运算符：=、>、<、>=、<=、!=或<>

逻辑运算符：and(与)、or(或)、not(非)

模糊查询：like "%、_等通配符组合"  （%表示任意多个任意字符，_表示一个任意字符）

（举如：name like '孙%';    name like '孙_';）

范围查询：

 in：表示在一个非连续的范围内  格式：in(..., ...)  例如：where hometown in('北京', '上海', '深圳');

 between ... and ... ：表示在一个连续的范围内   例如：age between 18 and 20;

空判断：

判空：is null            例如：card is null;

判非空：is not null   例如：card is not null;

* 注意：在MySQL中，只有现实为NULL的才为空！

            空格/制表符/换行符等是空白符号，用' '表示；

            NULL与' '是不同的。

聚合函数：

常用聚合函数：count()、max()、min()、sum()、avg()

*聚合函数不能在where子句中使用。

count()：查询总记录数   格式：count(字段名)，count(* )表示计算总行数

* 注意：统计数据总数，建议使用*，如果使用某一特定字段，可能会造成数据总数错误！

max()：查询最大值   格式：max(字段名)

min()：查询最小值    格式：min(字段名)

sum()：求和             格式：sum(字段名)

avg()：求平均值       格式：avg(字段名)

排序查询：select * from 表名 order by 列1 asc/desc, 列2 asc/desc, ...;

* 注意：默认升序，asc可省略；desc降序。

分组查询: select 字段1, 字段2, 聚合函数 ... from 表名 group by 字段1, 字段2...

* 例1：查询各种性别的人数：select sex, count(* ) from students group by sex;

* 例2：查询每个班级中各种性别的人数：select class, sex, count(* ) from students group by class, sex;

分组后的数据筛选：select 字段1, 字段2, 聚合 ... from 表名 group by 字段1, 字段2, 字段3 ... having 字段1, ... 聚合函数 ...

* 注意：

1）group by后面增加过滤条件时，需要使用having关键字；group by和having一般情况下需要配合使用

2）group by后边不推荐使用where进行条件过滤，推荐使用having进行条件过滤

3）having关键字后面可以使用的内容与where完全一致（比较运算符/逻辑运算符/模糊查询/判断空）

4）having关键字后面允许使用聚合函数，where后面不允许使用聚合函数

* 举例：select sex, count(* ) from students group by sex having sex='男';

            select sex, count(* ) from students group by sex having count(*)>=7;

* where 与 having 的区别：

1）where是对from后面指定的表进行数据筛选，属于对原始数据的筛选；

2）having是对group by的结果进行筛选；

3）having后面的条件可以用聚合函数where后面不可以。

例：查询班级平均年龄大于22岁的班级有哪些：select class from students group by class having avg(age)>22;

分页查询：select * from 表名 limit start, count;

分行格式(查 m ~ n 行的数据)：select * from 表名 limit m-1, n-m+1;

* 注意：

1）limit start, count；start：起始行号；count：显示数据行数（增加的行数）。

2）计算机的计数是从0开始，因此start默认的第一条数据应该为0。

3）start索引从0开始，如果默认从第一条数据开始获取，则0可以省略

即显示前5条记录有以下两种写法：select * from 表名 limit 0, 5;     或者select * from 表名 limit 5;

查询分页后某页的数据：select * from 表名 limit (n-1)* m, m;

* 说明：m为每页显示数据条数，n为需显示的页码

例每页显示10条数据，需要查询第6页的数据

select * from 表名 limit 50,10;

查询语句子句书写顺序：select-from-where-group by-having-order by-limit

连接查询：

内连接：inner join ... on ...

(旧式写法：select * from 表1, 表2 where 表1.列=表2.列;)

左连接：left join ... on ...

右连接：right join ... on ...

* 语法格式：select * from 表1 inner join 表2 on 表1.列=表2.列;

                   select * from 表1 left join 表2 on 表1.列=表2.列;

                   select * from 表1 right join 表2 on 表1.列=表2.列;

自关联查询：

自关联：将同一表通过起别名的方式，自己和自己关联。是给表起别名和连接查询的联合使用。

语法格式：select * from 表 as 表1 inner join 表 as 表2 on 表1.列=表2.列;

                select * from 表 as 表1 left join 表 as 表2 on 表1.列=表2.列;

                select * from 表 as 表1 right join 表 as 表2 on 表1.列=表2.列;

组合查询：

* MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。

* 组合查询两种基本情况：

在单个查询中从不同的表返回类似结构的数据；

对单个表执行多个查询，按单个查询返回数据。

创建和使用组合查询：

* 关键字：UNION

* 创建UNION：SELECT语句 UNION SELECT语句 UNION ...

* UNION规则：

1）UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。

2）UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

3）列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

包含或取消重复的行：

* UNION的默认从查询结果集中自动去除了重复的行（即 它与单条SELECT语句中使用多个WHERE子句条件一样）。

* MySQL使用UNION ALL来显示全部行，包括重复的行。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

* 举例：

SELECT vend_id, prod_id, prod_price 

FROM products 

WHERE prod_price <= 5 

UNION ALL 

SELECT vend_id, prod_id, prode_price 

FROM products 

WHERE vend_id IN (1001, 1002);

对组合查询结果排序：

* 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

* 举例：

# 举例：

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

UNION

SELECT vend_id, prod_id, prode_price

FROM products

WHERE vend_id IN (1001, 1002)

ORDER BY vend_id, prod_price;

UNION 和 多条WHERE子句：

* 使用UNION可极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。

* 对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。

* 使用UNION的组合查询还可以应用不同的表。

* 举例：

# 举例：

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

UNION

SELECT vend_id, prod_id, prode_price 

FROM products

WHERE vend_id IN (1001, 1002);

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

OR vend_id IN (1001, 1002);

子查询：

* 定义：在一个查询套入另一个查询的过程（充当条件或者数据源）

* 说明：查询语句中包含另一个查询语句，分为主查询和子查询，充当子查询的语句需要使用括号括起来（运算优先级括号最高！）

主查询：外层的?select?语句称之为主查询语句

子查询：在一个?select?语句中，嵌入了另外一个?select?语句，那么嵌入的?select?语句称之为子查询语句

主查询和子查询的关系：

1）子查询是嵌入到主查询中的

2）子查询是可以独立使用的语句，是一条完整的?select?语句

3）子查询是辅助主查询的，要么充当条件，要么充当数据源

子查询语句充当条件：

* 需求：查询价格高于平均价的商品信息

* 语句：

select * from goods

where price > (select avg(price) from goods);

子查询语句充当数据源：

* 需求：?查询所有来自并夕夕的商品信息，包含商品分类

* 语句：

SELECT * FROM  (

    SELECT * FROM  goods go

    LEFT JOIN category ca

    ON go.typeId=ca.typeId) new

WHERE new.company='并夕夕';

* 问题：连接查询的结果中，表和表之间的字段名不能出现重复，否则无法直接使用

* 解决：将重复字段使用别名加以区分（表.*?：表示当前表的所有字段）：

* 举例：

SELECT * FROM 

    (SELECT go.* ,

        ca.id cid,

        ca.typeId ctid,

        ca.cateName

    FROM goods go

    LEFT JOIN category ca

    ON go.typeId=ca.typeId) new

WHERE new.company='并夕夕';

三、数据库进阶

1、索引

* 定义：快速查找特定值的记录

* 作用：提高查询排序的速度，即 可以大幅度提高查询语句的执行效率

* 个数：一个表主键只能有一个

设置索引：

* 语法格式：CREATE INDEX 索引名称 ON 表名(字段名称(长度));

* 注意：

1）表已存在的时候创建索引

2）如果大量增加索引设置，会严重影响除数据查询操作以外的其他操作（增/删/改）的操作效率，不方便过多添加。

* 举例：CREATE INDEX name_index ON create_index(name(10));

删除索引：

* 语法格式：DROP INDEX 索引名称 ON 表名;

* 举例：DROP INDEX name_index ON create_index;

扩展：验证索引效果案例实现步骤

# 1）开启运行时间监测：

set profiling=1;

# 2）查找第一万条数据10000：

select * from test_index where num='10000';

# 3）查看执行时间：

show profiles;

# 4）为表 test_index 的 num 列创建索引：

create index test_index on test_index(num);

# 5）执行查询语句：

select * from test_index where num='10000';

# 6）再次查看执行时间：

show profiles;

2、视图

* 定义：视图是虚拟的表。其包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

* 注意：

1）MySQL 5添加了对视图的支持。本文内容适用于MySQL 5及以后的版本。

2）视图仅仅是用来查看存储在别处的数据的一种设施。其本身不包含任何列和数据，它包含的是一个SQL查询，其返回的数据是从其他表中检索出来的。另外，在添加或更改这些表中的数据时，视图将返回改变过的数据。

3）视图的使用与表基本相同，如：可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据，但添加和更新数据会存在某些限制。

4）性能问题：在使用视图时，都必须处理查询执行时所需的任一个检索。如果要用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

* 作用：

1）重用SQL语句。

2）简化复杂的SQL操作。视图可以方便地被重用，而不必知道其查询细节。

3）保护数据。视图使用的是表的组成部分而不是整个表，因此可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

4）更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图：

* 关键字：CREATE VIEW

* 语法格式：CREATE VIEW 视图名 AS SELECT语句;

* 注意：

1）创建视图必须具有足够的访问权限

2）创建的视图数目没有限制

3）视图名字必须唯一命名，不能与别的视图或表有相同的名字

4）视图可以嵌套使用，即可以利用从其他视图中检索数据的查询来构造一个视图

5）视图可以和表一起使用。如，编写一条联结表和视图的SELECT语句

6）视图中的ORDER BY和其检索数据的SELECT语句中同时含有ORDER BY时，那么该视图中的ORDER BY将被覆盖。

7）视图不能索引，也不能有关联的触发器或默认值。

查看视图：

* 关键字：SHOW

* 语法格式：SHOW CREATE VIEW 视图名；

修改视图：

* 方法一：先用DROP再用CREATE。

DROP CREATE VIEW 视图名;

CREATE VIEW 视图名 AS SELECT语句;

* 方法二：CREATE OR REPLACE VIEW 视图名 AS SELECT语句;

如果要更新的视图不存在，则会创建一个视图；

如果要更新的视图存在，则会替换原有视图。

更新视图“数据”：

* 更新视图“数据”的本质：视图本身没有数据，所以对视图增加或删除行，实际上是对其基表增加或删除行。

* 更新视图的限制：

通常视图是可更新的（即，视图可以使用INSERT、UPDATE和DELETE），但并非所有视图都是可更新的，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。

视图定义中有分组（使用GROUP BY和HAVING）、联结、子查询、?并（and）、?聚集函数（Min()、Count()、Sum()等）、DISTINCT、?导出（计算）列等的操作时，视图不能进行更新。

更新视图虽然有这么多限制，但请记住视图主要还是用于数据查询，如果要更新数据，可以到基表中进行更新。

删除视图：

* 关键字：DROP

* 语法格式：DROP CREATE VIEW 视图名;

应用场景：

1）利用视图简化复杂的联结：

* 视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。

* 举例：

# 编写基础SQL，创建视图：

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的客户：

SELECT * FROM productcustomers

WHERE prod_id = 'TNT2';

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

* 视图的另一常见用途是重新格式化检索出的数据。

* 举例：

# 编写基础SQL，创建视图：

CREATE VIEW vendorlocations

AS SELECT Concat(

    RTrim(vend_name),

    '(',

    RTrim(vend_country),

    ')' AS vend_title FROM venders

    ORDER BY vend_name;

# 使用视图查询出以创建所有邮件标签的数据（基础`SQL`：在单个组合计算列中返回供应商名和位置）：

SELECT * FROM vendorlocations;

3）用视图过滤不想要的数据：

* 视图对于应用普通的WHERE子句也很有用。

* 举例：

# 编写基础SQL，创建视图：

CREATE VIEW customeremaillist

AS SELECT cust_id,

    cust_name,

    cust_email

FROM customers

WHERE cust_email IS NOT NULL;

# 使用视图，查询出有邮件地址的客户数据：

SELECT * FROM customeremaillist;

4）使用视图与计算字段：

* 视图对于简化计算字段的使用特别有用。

* 举例：

# 编写基础SQL，创建视图：

CREATE VIEW orderitemsexpanded 

AS SELECT order_num,

        prod_id, quantity,

        item_price,

        quantity* item_price AS expanded_price

    FROM orderitems;

使用视图，查询订单20005的详细内容包括每种物品的总价格的数据：

SELECT * FROM orderitemsexpanded

WHERE order_num = 20005;

* 注意：?视图中的WHERE子句与基础SQL语句中的WHERE子句同时出现时，二者将自动组合。

3、存储过程

* 定义：简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

* 优点

简单：把处理封装在容易使用的单元中，简化复杂的操作，简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。

安全：由于不要求反复建立一系列处理步骤，且所有开发人员和应用程序都是用同一存储过程，这保证了数据的完整性和一致性。另外，通过存储过程限制对基础数据的访问减少了数据讹误的机会，保证了数据安全性。

高性能：提高性能。因为使用存储过程比使用单独的SQL语句要快。存储过程可以使用只能用在单个请求中的MySQL元素和特性来编写功能更强更灵活的代码。

* 缺点

存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

创建存储过程需要数据库的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

创建存储过程

* 关键字：CREATE、PROCEDURE

* 语法格式：

CREATE PROCEDURE 存储过程名(参数1, 参数2, ... )  COMMENT '描述文字'

BEGIN

    SELECT语句;

END;

分隔符：?MySQL命令行客户机的分隔符为：';'，而MySQL语句分隔符也是：';'，所以如果使用MySQL命令行实用程序创建或执行存储过程，则需要临时更改MySQL命令行实用程序的语句分隔符，来保证存储过程中的SQL语句作为其成分而且不出现句法错误。具体步骤如下：

* 1）临时修改命令行实用程序的语句分隔符：DELIMITER //

* 2）同时，标志存储过程结束的END定义为END //而不是END ;

* 3）存储过程执行完成后，恢复命令行实用程序的语句分隔符：DELIMITER ;?。

* 注意：除\符号外，任何字符都可以用作语句分隔符。

* 完整代码：

DELIMITER //

CREATE PROCEDURE 存储过程名(参数1, 参数2, ... )  COMMENT '描述文字'

BEGIN

    SELECT语句;

END //

DELIMITER ;

* 举例：

# 创建存储过程：

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

    SELECT Avg(prod_price) AS priceaverage FROM products;

END //

DELIMITER ;

# 执行存储过程：

CALL productpricing();

参数类型：

* 每个参数必须具有指定的类型

* MySQL支持的参数类型：

IN（传递给存储过程）

OUT（从存储过程传出，关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）

INOUT（对存储过程传入和传出）

* 参数定义格式：

基本格式：IN/OUT/INOUT 变量名 变量的数据类型

变量（variable）内存中一个特定的位置，用来临时存储数据。所有MySQL变量都必须以@开始。

参数变量也需要指定其数据类型。参数变量的数据类型：存储过程的参数允许的数据类型与表中使用的数据类型相同。

常用数据类型：INT、DECIMAL、DOUBLE、FLOAT、BOOLEAN、CHAR、VARCHAR、TEXT、DATE、TIME、DATETIME、YEAR

多个参数用英文逗号,分隔，最后一个参数后不需要逗号

参数使用：?存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，一般存储过程并不显示结果，而是把结果保存到相应的变量（通过指定INTO关键字），然后再通过SELECT @参数变量名;?来显示结果数据或者将其用于其他处理程序中。

执行有参数的存储过程：

* 在创建存储过程时指定了n个参数，则在调用此存储过程时就必须传递n个参数，不能多也不能少。

* 在执行时并不会显示任何数据。它通过SELECT @参数变量名;?来显示变量的值或者用在其他处理程序中。

* 例1：

'''创建存储过程：此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。

每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。'''

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;

# 执行存储过程（这条语句并不显示任何数据）：

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

# 显示检索出的产品平均价：*

SELECT @priceaverage;

# 显示检索出的产品最低价、最高价和平均价：

SELECT @pricelow, @pricehigh, @priceaverage;

* 例2：

'''创建存储过程：此存储过程使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计。

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。

SELECT语句使用这两个参数，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;

# 执行存储过程（调用这个新存储过程）：必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名：

CALL ordertotal(20005, @total);

# 显示此合计（@total已由ordertotal的CALL语句填写，SELECT显示它包含的值）：

SELECT @total;

# 显示另一个订单的合计：再次调用存储过程，然后重新显示变量：

CALL ordertotal(20009, @total);

SELECT @total;

执行存储过程：

* 关键字：CALL

* 语法格式：CALL 存储过程名(@参数1, @参数2, ... )

* 注意：因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）。

* 举例：

# 创建ordertotal存储过程：

CREATE PROCEDURE ordertotal()

BEGIN

    SELECT语句;

END;

# 执行ordertotal存储过程：

CALL ordertotal();

删除存储过程：

* 关键字：DROP、PROCEDURE

* 语法格式一：DROP PROCEDURE 存储过程名;

* 语法格式二：DROP PROCEDURE IF EXISTS 存储过程名;

* 仅当存在时删除，当过程存在则删除，如果过程不存在也不产生错误。

* 举例：

# 创建ordertotal存储过程：

 CREATE PROCEDURE ordertotal()

BEGIN

    SELECT语句;

END;

# 删除ordertotal存储过程：

DROP PROCEDURE ordertotal;

DROP PROCEDURE IF EXISTS ordertotal;

检查存储过程：

* 关键字：SHOW

* 语法格式一：SHOW CREATE PROCEDURE 存储过程名;

显示用来创建一个存储过程的CREATE语句

* 语法格式二：SHOW CREATE PROCEDURE STATUS;

显示包括何时、由谁创建等详细信息的存储过程列表

* 语法格式三：SHOW CREATE PROCEDURE STATUS LIKE '存储过程名';

显示某个存储过程的详细信息

* 举例：

# 创建ordertotal存储过程：

CREATE PROCEDURE ordertotal()

BEGIN

    SELECT语句;

END;

# 检查存储过程：

SHOW CREATE PROCEDURE ordertotal;

SHOW CREATE PROCEDURE STATUS LIKE 'ordertotal';

建立智能存储过程：?存储过程内包含业务规则和智能处理

* 应用场景：你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：获得合计（与以前一样）；把营业税有条件地添加到合计；返回合计（带或不带税）。

存储过程的完整工作：

# 创建存储过程：

# 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 DEFULT 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;

# 执行次存储过程，并显示数据结果：

CALL ordertotal(20005, 0, @total);

SELECT @total;

* 解析：

1）首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样做特别重要。

2）添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。

3）在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。

4）SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。

5）IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。

6）最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

7）COMMENT关键字：本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

举例：使用SQL快速插入100000条数据

# 使用DELIMITER关键字临时声明修改SQL语句的结束符为//

DELIMITER //

# 创建存储过程

create procedure test()

Begin

# 声明一个默认值为0的局部变量i

    declare i int default 0;

    # 开始循环

    while i<100000 do

    # books是表名， name是字段名 test是字段值

    insert into books(name)value("test");

    # 使用set为参数赋值

    set i=i+1;

    end while;

end //

# 将结束符重新定义回结束符为";"

DELIMITER ;

# 调用函数

call test();

4、游标

定义：游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

注意：MySQL游标只能用于存储过程（和函数）。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

创建游标：

* 关键字：DECLARE

* 语法格式：DECLARE 游标名 CURSOR FOR SELECT语句;

* DECLARE语句的次序：DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。即：DECLARE 局部变量，DECLARE 游标，DECLARE 句柄

* 举例：

CREATE PROCEDURE processorders()

BEGIN

    DECLARE ordernumbers CURSOR

    FOR SELECT order_num FROM orders;

END;

打开/关闭游标：

* 打开游标：OPEN 游标名;

* 关闭游标：CLOSE 游标名;

* 隐含关闭：MySQL将会在到达END语句时自动关闭它。

* 注意：

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

在一个游标关闭后，再次使用游标需要再用OPEN语句打开它，但使用声明过的游标不需要再次声明。

* 举例：

CREATE PROCEDURE processorders()

BEGIN

    # Declare the cursor

    DECLARE ordernumbers CURSOR

    FOR SELECT order_num FROM orders;

    # Open the cursor

    OPEN ordernumbers;

    # Close the cursor

    CLOSE ordernumbers;

END;

* 解析：?这个存储过程只是声明、打开和关闭一个游标，并未对检索出的数据做其他操作。

使用游标数据：

* 关键字：FETCH

* FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

* 步骤：

1）在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。在声明游标后，可根据需要频繁地打开和关闭游标。

2）一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。在游标打开后，可根据需要频繁地执行取操作。

3）对于填有数据的游标，根据需要取出（检索）各行。

4）在结束游标使用时，必须关闭游标。

* 例1：从游标中检索单个行（第一行）

CREATE PROCEDURE processorders()

BEGIN

    # Declare local variables

    DECLARE o INT;

    # Declare the cursor

    DECLARE ordernumbers CURSOR

    FOR SELECT order_num FROM orders;

    # Open the cursor

    OPEN ordernumbers;

    # Get order number

    FETCH ordernumbers INTO o;

    # Close the cursor

    CLOSE ordernumbers;

END;

* 解析：FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

* 例2：循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders()

BEGIN

    # Declare local variables

    DECLARE done BOOLEAN DEFAULT 0;

    DECLARE o INT;

    # Declare the cursor

    DECLARE ordernumbers CURSOR

    FOR SELECT order_num FROM orders;

    # Declare continue handler

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

    # Open the cursor

    OPEN ordernumbers;

    # Loop through all rows

    REPEAT

        # Get order number

        FETCH ordernumbers INTO o;

    # End of loop

    UNTIL done END REPEAT;

    # Get order number

    FETCH ordernumbers INTO o;

    # Close the cursor

    CLOSE ordernumbers;

END;

* 解析：

1）用一个DEFAULT 0（假，不结束）定义变量done（DECLARE done BOOLEAN DEFAULT 0;）。

2）定义一个CONTINUE HANDLER（DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;），它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000’出现时，SET done=1。SQLSTATE'02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。出现这个未找到的条件后就将done设置为真。

3）在REPEAT内，反复执行FETCH检索当前order_num到声明的名为o的变量中，直到done为真（由UNTIL done END REPEAR;?规定）。

4）如果调用这个存储过程，它将定义几个变量和一个CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。如果一切正常，你可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。

5）MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。

* 例3：对循环取出的数据进行某种实际的处理

CREATE PROCEDURE processorders()

BEGIN

    # Declare local variables

    DECLARE done BOOLEAN DEFAULT 0;

    DECLARE o INT;

    DECLARE t DECIMAL(8,2);

    # Declare the cursor

    DECLARE ordernumbers CURSOR

    FOR SELECT order_num FROM orders;

    # Declare continue handler

    DECLARE CONTINUE HANDLER

    FOR SQLSTATE '02000' SET done = 1;

    # Create a table to store the results

    CREATE TABLE IF NOT EXISTS ordertotals(

        order_num INT,

        total DECIMAL(8, 2));

    # Open the cursor

    OPEN ordernumbers;

    # Loop through all rows

    REPEAT

        # Get order number

        FETCH ordernumbers INTO o;

        # Get the total for this order

        CALL ordertotal(o, 1, t);

        # Insert order and total into ordertotals

        INSERT INTO ordertotals(order_num, total)

        VALUES(o, t);

    # End of loop

    UNTIL done END REPEAT;

    # Close the cursor

    CLOSE ordernumbers;

END;

* 解析：

1）增加了另一个名为t的变量（存储每个订单的合计）

2）此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。

3）FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程ordertotal（在介绍存储过程的内容中创建）来计算每个订单的带税的合计（结果存储到t）。

4）最后，用INSERT保存每个订单的订单号和合计。

5）此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的SELECT语句查看该表：SELECT * FROM ordertotals;

6）这是一个集存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。

5、触发器

定义：触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE、INSERT、UPDATE；其他MySQL语句不支持触发器。

创建触发器：

* 基本信息：

唯一的触发器名。为了保持每个数据库的触发器名唯一，在MySQL 5中，最好是在数据库范围内使用唯一的触发器名。虽然触发器名在每个数据库中可以不唯一但最好命名时保证每个数据库范围内的触发器名唯一。

触发器关联的表；

触发器应该响应的活动（DELETE、INSERT或UPDATE）；

触发器何时执行（处理之前或之后）。

* 关键字：CREATE、TRIGGER

* 语法格式：CREATE TRIGGER 触发器名 BEFORE/AFTER DELETE/INSERT/UPDATE ON 表名 FOR EACH ROW ... ;

INSERT触发器：?INSERT触发器在INSERT语句执行之前或之后执行。

* 注意：

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

* 举例：

CREATE TRIGGER neworder 

AFTER INSERT ON orders 

FOR EACH ROW SELECT NEW.order_num;

* 解析：?此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。

* 测试此触发器：

* INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);

* 解析：orders包含3个列。order_date和cust_id必须给出，order_num由MySQL自动生成，而现在order_num还自动被返回。

* 注意：BEFORE或AFTER，BEFORE多用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。

DELETE触发器：?DELETE触发器在DELETE语句执行之前或之后执行。在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；OLD中的值全都是只读的，不能更新。

* 举例：

CREATE TRIGGER deleteorder

BEFORE DELETE ON orders FOR EACH RAW

BEGIN

    INSERT INTO archive_orders(

        order_num,

        order_date,

        cust_id)

        VALUES(

            OLD.order_num,

            OLD.order_date,

            OLD.cust_id);

END;

* 解析：在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）。

* 使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

* 多语句触发器：触发器deleteorder使用BEGIN和END语句标记触发器体。使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

UPDATE触发器：?UPDATE触发器在UPDATE语句执行之前或之后执行。

* 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；

* 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；

* OLD中的值全都是只读的，不能更新。

* 举例：

CREATE TRIGGER updatevendor

BEFORE UPDATE vendors

FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

* 解析：?任何数据净化都需要在UPDATE语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

删除触发器：

* 关键字：DROP

* 语法格式：DROP TRIGGER 触发器名;

* 举例：DROP TRIGGER newproduct;

* 注意：触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

* 应用场景：

每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；

每当订购一个产品时，都从库存数量中减去订购的数量；

无论何时删除一行，都在某个存档表中保留一个副本。

以上三个共同之处在于需要在某个表发生更改时自动处理。

* 注意：

与其他DBMS相比，MySQL 5中支持的触发器相当初级。

创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。

触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

6、事务处理

MySQL支持几种基本的数据库引擎。MyISAM和InnoDB是两种最常使用的引擎。InnoDB引擎支持明确的事务处理管理，而MyISAM引擎不支持。

事务处理（transaction processing）是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。它保证成批的MySQL操作要么完全执行，要么完全不执行，以此来维护数据库的完整性。

关键词汇：

* 事务（transaction）指一组SQL语句；

* 回退（rollback）指撤销指定SQL语句的过程；

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

* 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

* 步骤：

* 1、开启事务

start transaction;

* 2、查询事务的隔离级别

select @@transaction_isolation ;

MySQL下默认的隔离方式为?repeatable-read

* 隔离性有隔离级别(4个)

读未提交：read uncommitted

读已提交：read committed

可重复读：repeatable read

串行化：serializable

* 3、设置隔离级别

set session transaction isolation level read committed;

set session|global transaction isolation level 隔离级别;

* 4、操作回滚

rollback

* 5、提交事务

commit;

控制事务处理：

* 事务开始语句：START TRANSACTION

* 使用ROLLBACK：MySQL的ROLLBACK命令用来回退（撤销）MySQL语句

* 举例：

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

* 解析：

首先执行一条SELECT以显示该表不为空。

然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。

另一条SELECT语句验证ordertotals确实为空。

这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句。ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

最后一条SELECT语句显示该表不为空。

注意：事务处理用来管理INSERT、UPDATE和DELETE语句。但不能回退SELECT语句、不能回退CREATE或DROP操作。

使用COMMIT：

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。 但在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句：COMMIT;

* 举例:

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

* 解析：例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderitems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

* 隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

更改默认的提交行为：?默认的MySQL行为是自动提交所有更改。

* 修改MySQL不自动提交更改语句：SET autocommit=0;

* 不管有没有COMMIT语句，autocommit标志决定是否自动提交更改，且autocommit标志是针对每个连接而不是服务器的。

* 设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置<1>为真为止）。

使用保留点：

* 保留点：为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。

* 创建保留点语句：SAVEPOINT 保留点名;

* 注意：每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。

* 例如：

SAVEPOINT delete1;

* 回退到保留点语句：ROLLBACK TO 保留点名;

* 例如：

ROLLBACK TO delete1;

* 保留点越多越好：可以在MySQL代码中设置任意多的保留点，越多越好。因为保留点越多，你就越能按自己的意愿灵活地进行回退。

* 释放保留点：保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

7、权限管理

1）创建用户

* create user ‘用户名’ @ ‘localhost’ idenified by ‘密码’;

* create user 'HHXF'@'localhost' identified by '123456';

* 注意：需要在管理员root权限进行创建

2）授予权限

* 用户授权指令：grant

* grant all privileges on *.* to 'Alascanfu'@'%' identified by '123456' with grant option;

* 参数说明：

all privileges: 表示将所有权限授予给指定用户。也可指定具体的权限，如：SELECT、CREATE、DROP等

on:表示授予的权限对于哪些数据库中的数据表有效。

to:表示的是授予给哪个指定用户以及可以登录的ip地址 格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。

identified by：指定用户的登录密码

with grant option：表示允许用户将自己的权限授权给其它用户

刷新当前权限：flush privileges

取消指定用户的权限：revoke all on *.* from 'Alascanfu'@'localhost';

删除指定用户：drop user'Alascanfu'@'%';