MySQL命令的示例分析

30次阅读

共计 10176 个字符，预计需要花费 26 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 命令的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

MySQL 的基本操作可以包括两个方面：MySQL 常用语句如高频率使用的增删改查（CRUD）语句和 MySQL 高级功能，如存储过程，触发器，事务处理等。而这两个方面又可以细分如下：

MySQL 常用语句

表（或者数据库）的 CRUD

表数据的 CRUD，其中表数据查询使用最多，也更复杂。查询可以按照单表还是多表可以分为：单表 SELECT 查询和多表的联结查询（INNER JOIN, LEFT JOIN, RIGHT JOIN 和 FULL JOIN）以及组合查询 UNION 和 UNION ALL

SQL 语句中各个关键字的执行顺序

MySQL 的高级功能

存储过程

事务处理

触发器

1. 表 (或数据库) 操作语句

1.1. 查询表（或数据库）

获取所有可用的数据库：SHOW DATABASES；

选择数据库：USE customers；

用于显示数据库服务器的状态信息：SHOW STATUS；

用来显示授权用户的安全权限：SHOW GRANTS；

用来显示数据库服务器或警告信息：SHOW ERRORS 或者 SHOW WARNINGS；

用于显示创建数据库时的创建语句：SHOW CREATE DATABASE customers；

用于显示创建表时的创建语句：SHOW CREATE TABLE customers；

获取当前所选的数据库中所有可用的表：SHOW TABLES；

获取表中所有列的信息：SHOW COLUMNS FROM tableName；同时 DESCRIBE 语句有相同的效果：DESCRIBE tableName；

1.2. 新建表（或）数据库

新建数据库：CREATE DATABASE customers;

创建表可以使用 CREATE TABLE 语句:

有这样一些细节：

允许 NULL 值，则说明在插入行数据时允许不给出该列的值，而 NOT NULL 则表示在插入或者更新该列数据，必须明确给出该列的值；

DEFAULT 表示该列的默认值，在插入行数据时，若没有给出该列的值就会使用其指定的默认值；

PRIMARY KEY 用于指定主键，主键可以指定一列数据，而可以由多列数据组合构成，如 PRIMARY KEY(cust_id,cust_name)；

ENGINE 用于指定引擎类型。常见的引擎类型有这些：（1）InnoDB 是一个支持可靠的事务处理的引擎，但是不支持全文本搜索；（2）MyISAM 是一个性能极高的引擎，它支持全文本搜索，但是不支持事务处理；（3）MEMORY 在功能上等同于 MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）；

在创建表的时候可以使用 FOREIGN KEY 来创建外键，即一个表中的 FOREIGN KEY 指向另一个表中 PRIMARY KEY。外键 FOREIGN KEY 用于约束破坏表的联结动作，保证两个表的数据完整性。同时也能防止非法数据插入外键列，因为该列值必须指向另一个表的主键。实例为：

1.3 删除表（或数据库）

删除数据库：DROP DATABASE customers；

删除表，使用 DROP TABLE 子句：DROP TABLE customers。

1.4 更新表

更新表结构信息可以使用 ALTER TABLE 子句，如为表增加一列：ALTER TABLE vendors ADD vend_name CHAR(20)；另外经常用于定义外键，如：

重命名表，使用 RENAME 子句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors；更改多个表名，之间用逗号间隔

2 表数据操作语句

2.1 查询表数据

基本查询语句

根据过滤条件查询表中的单列或者多列或者全部列的信息 SELECT FROM WEHERE：SELECT cust_id,cust_name FROM customers WHERE cust_id=10086；其中过滤条件操作符有：=，,!=, , =, , =,BETWEEN AND,IS NULL；

为查询出的某一列信息去重 DISTINCT：SELECT DISTINCT cust_name FROM customers；

限制单列查询结果的行数：SELECT cust_name FROM customers LIMIT 5；LIMIT 后跟一个数值，表示从第 0 行开始取，共取 5 行数据；如果 LIMIT 5，5 表示从第 5 行（数据库中实际第 6 行记录）开始取，共取 5 行数据。注意：数据是从第 0 行开始计数的；

ORDER BY 子句取一个或者多个列，据此对输出进行排序：SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name;

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配：SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)。另外，NOT 操作符可以和 IN 操作符配合使用，用于表示检索出不符合条件的所有数据；

LIKE 操作符用来表明模糊查询，与之配合使用的通配符有 **%**，% 表示任何字符出现任何次数；_，_表示只能匹配一个字符：SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE %happy%；

使用分组查询并可以满足一定的分组过滤条件 GROUP BY HAVING。如检索总计订单金额大于等于 50 的订单号和订单总金额，并按总金额进行排序：SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) =50 ORDER BY order_total

WHERE 和 HAVING 的比较。WHERE 是行级过滤，而 HAVING 是组级过滤。被 WHERE 过滤掉的数据不会出现在分组中。WHERE 中通配符以及多个 WHERE 子句的连接同样适用于 HAVING 子句；

GROUP BY 的使用注意事项：（1）GROUP BY 子句中可以嵌套分组（即通过多个列进行分组 GROUP BY cust_id, cust_name），但是进行数据汇总时，是在最后规定的分组上进行；（2）GROUP BY 子句中列出的每个列都必须是检索列或者是有效的表达式。（3）如果有 NULL 值，将值 NULL 作为一个分组进行返回，如果有多行 NULL 值，它们将分为一组

嵌套其他查询中的查询，称之为子查询。执行过程由里向外，里层查询结果作为外层查询的条件：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = happy)。当然，多表的查询可以是用联结查询。

联结查询

内联结用又称之为内部联结，是基于两个表之间的的相等测试。如果不加过滤条件，会造成“笛卡尔积”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id；同样可以使用 WHERE 进行多表联结查询，但是更推荐使用 INNER JOIN 等联结方式；

外部联结包括左外联结 LEFT JOIN 和右外联结 RIGHT JOIN 和全连接 FULL JOIN。例如查询每个客户的订单数：SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id；LEFT JOIN 会全部返回左表数据，RIGHT JOIN 会全部返回右表数据，FULL JOIN 会将左右两个表的数据全部返回；

联结查询与聚集函数一起使用。如查询每个客户的订单数：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；

组合查询

多个查询（SELECT）可以使用 UNION 将多个查询结果进行合并成一个结果集返回，UNION 必须包含两个及两个以上的 SELECT 查询，并且每个传必须包含相同的列、表达式或聚集函数，数据类型不必完全相同，MySQL 会进行隐式的类型转换。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price 5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

UNION 返回的是去重后的结果，如果不需要去重则可以使用 UNION ALL；

可以多组合查询使用 ORDER BY 进行排序，但是是针对的最终的结果集进行排序，而不是其中单个 SELECT 查询进行排序，因此对于组合查询来说 ORDER BY 子句只有一个。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price 5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id

使用函数对数据进行处理

拼接列名：SELECT Concat (vendName, ( ,vendCountry,) ) FROM vendors ORDER BY vendName；

执行算术表达式计算：SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems；

文本处理函数如 Upper(),LTrim(),RTrim()等函数。比如使用 Upper 函数将文本转换成大写：SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName；

时间和日期处理函数，如 Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)= 2015-09-01；

数值处理函数，如 Abs(),Cos()等；

常用的聚集函数。如 AVG(),COUNT(),MAX(),MIN()以及 SUM()。SELECT COUNT(*) AS numbers, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products；

2.2 插入表数据

向表中插入行数据可以使用 INSERT INTO 子句，更安全的方式是指定列名。INSERT INTO customers (cust_name, cust_email) VALUES(happy , happy@gmail.com)；在 INSERT INTO 子句中能够省略列名的前提条件是：该列可以允许定义为 NULL 值或者在定义该列时给出去了默认值；

如果插入多行数据可以将多组值用逗号进行分隔即可。INSERT INTO customers (cust_name, cust_email) VALUES(happy , happy@gmail.com),(smart , smart@gmail.com)；

将查询出来的数据插入表中，可以使用 INSERT SELECT 语句。INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id 5；其中 SELECT 中可以带 WHERE 过滤条件；INSERT SELECT 通常被用于复制表数据

2.3 更新表数据

如果要更新表数据的话，使用 UPDATE 子句：UPDATE customers SET cust_name = happy ,cust_email= happy@gmail.com WHERE cust_id = 1001；

注意：如果不加 WHERE 条件指定到某一行的话，会更新表中某一列全部的数据。

2.4 删除表数据

如果从表中删除数据的话，可以使用 DELETE 子句。DELETE FROM customers WHERE cust_id = 10086；删除的数据必定是表中行数据，而不是某一列。因此，与 UPDATE 子句相比，DELETE 子句并不需要指定是哪一列，而仅仅只需要指定具体的表名即可；

注意：如果不添加 WHERE 指定条件的话，会将整个表中所有行数据全部删除。另外，DELETE 只是删除表中的数据，而不会删除表结构信息；

如果想删除表中全部的数据，可以使用 TRUNCATE，比 DELETE 删除效率更高；

3. SQL 中关键字执行顺序

在 SQL 语句中每个关键字都会按照顺序往下执行，而每一步操作，会生成一个虚拟表，最后产生的虚拟表会作为执行的最终结果返回。下面的是常用的关键字的执行顺序：

FROM：对 FROM 左边的表和右边的表计算笛卡尔积，产生虚表 VT1；

ON：对虚拟表 VT1 进行 ON 筛选，只有那些符合条件的行才会被记录在虚拟表 VT2 中；

JOIN：如果是 OUT JOIN，那么将保留表中（如左表或者右表）未匹配的行作为外部行添加到虚拟表 VT2 中，从而产生虚拟表 VT3；

WHERE: 对虚拟表 VT3 进行 WHERE 条件过滤，只有符合的记录才会被放入到虚拟表 VT4；

GROUP BY：根据 GROUP BY 子句中的列，对虚拟表 VT4 进行分组操作，产生虚拟表 VT5；

CUBE|ROLLUP：对虚拟表 VT5 进行 CUBE 或者 ROLLUP 操作，产生虚拟表 VT6；

HAVING：对虚拟表 VT6 进行 HAVING 条件过滤，只有符合的记录才会被插入到虚拟表 VT7 中；

SELECT：执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中；

DISTINCT：对虚拟表 VT8 中的记录进行去重，产生虚拟表 VT9；

ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10；

LIMIT：取出指定行的记录，产生虚拟表 VT11，并将结果返回。

4. 索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，而组合索引，即一个索引包含多个列。

4.1 创建索引

创建索引有两种方式，一种是直接利用 CREATE INDEX 进行创建，另外一种则是通过修改表结构来进行添加，则是利用 ALTER TABLE 语句。

使用 CREATE INDEX

语法为：

其中对应的语法变量信息如下：

[UNIQUE|FULLTEXT|SPATIAL]

其中括号中的这三个关键字表示创建的索引类型，它们分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果我们不指定任何关键字，则默认为普通索引。

index_name

index_name 表示索引的名称，由用户自行定义，以便于以后对该索引进行修改等管理操作。

index_type

index_type 表示索引的具体实现方式，在 MySQL 中，有两种不同形式的索引——BTREE 索引和 HASH 索引。在存储引擎为 MyISAM 和 InnoDB 的表中只能使用 BTREE，其默认值就是 BTREE；在存储引擎为 MEMORY 或者 HEAP 的表中可以使用 HASH 和 BTREE 两种类型的索引，其默认值为 HASH。

index_colname

index_col_name 表示需要创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间以英文逗号隔开即可。此外，对于 CHAR 或 VARCHAR 类型的字段，我们还可以只使用字段内容前面的一部分来创建索引，只需要在对应的字段名称后面加上形如 (length) 的指令即可，表示只需要使用字段内容前面的 length 个字符来创建索引。在这里，我们以 customers 表的 cust_name 字段 (类型为 VARCHAR(50)) 为例，使用 cust_name 字段的 6 个字符前缀来创建索引。

使用 ALTER TABLE

语法为：

4.2 删除索引

删除指定表中指定名称的索引，语法为：

例如删除名称为 idx_cust_name 的索引，其 SQL 语句为：

4.3 修改索引

在 MySQL 中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。

4.4 查看索引

在 MySQL 中，要查看某个数据库表中的索引也非常简单，只需要使用以下两个命令中的任意一种即可。

5. 存储过程

什么是存储过程？存储过程简单来说，就是为了复用性或者实现复杂的业务功能，而保存的一条或多条 MySQL 语句的集合，可将其视为批文件；

为什么使用存储过程？（1）通过把处理封装在容易使用的单元中，简化复杂的操作；（2）由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果所有的开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的；（3）简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码，使用它的开发人员甚至不需要知道这些变化，也就是具备了安全性；（4）提高了性能，因为使用存储过程比单独使用 SQL 语句要快；（5）存储过程可用来编写功能更灵活的代码。因此，存储过程的具备三个特性: 简单可复用、安全以及高性能；

存储过程的缺点？（1）存储过程编写比基本的 SQL 语句更加复杂，需要更高的技能；（2）可能没有创建存储过程的权限，数据库管理员可能会限制创建存储过程的权限，允许用户使用存储过程，而不允许用户自由创建存储过程；

创建存储过程

创建存储过程。如需要统计用户订单总金额，如果该用户需要交税的话，订单总金额则需要再加上税费

有这样一些细节：1. 使用 CREATE PROCEDURE 语句进行创建，（）圆括号中为存储过程的参数，其中参数类型有：1.IN 类型，表示传递给存储过程；2.OUT 类型，表示存储过程返回的结果，在调用存储过程时需要传入 @开始的变量；3.INOUT 类型，表示在存储过程中可以传入和传出；2.DECLARE 用来声明一个变量，如这里的 total，taxrate。注意 MySQL 中定义变量时都是变量名在前，数据类型在后。3. 存储过程具体逻辑写在 BEGIN END 之间；4. 将值赋给变量使用 INTO 关键字；5. 由于存储过程中每个 SQL 语句中用；作为分隔符，会和单个 SQL 造成冲突，因此可使用 DELIMITER 重新定义分类符，如该例子中定义 // 为分隔符，自然存储过程结尾就用 END // 结尾，而不再是 END。同时，分隔符 // 成对出现后，恢复到默认的作为分隔符；

执行存储过程

使用 CALL 子句执行存储过程，CALL 子句接受存储过程的名称以及需要传递的参数。

如果存储过程中定义了 OUT 类型的输入参数，那么在执行存储过程时需要传入变量，如这里 @total，并且变量都是用 @开始的。如果存储过程中没有参数的话，就用空圆括号表示即可，CALL ordertotal()；

删除存储过程

删除存储过程，可以使用 DROP PROCEDURE 子句。如 DROP PROCEDURE ordertotal；

查询存储过程

显示创建一个存储过程的语句，可以使用 SHOW CREATE PROCEDURE。如 SHOW CREATE PROCEDURE ordertotal；

查询所有存储过程的状态，如果在定义存储过程中使用 COMMENT 添加注释，可以查看。同时可以 LIKE 进行过滤结果。如 SHOW PROCEDURE STATUS LIKE %order%

6. 事务处理

什么是事务？

事务处理是用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的 MySQL 操作，它们要么时作为整体执行或者完全不执行。

关键概念：

事务：是指一组 SQL 语句；

回退：是指撤销指定的 SQL 语句的过程；

提交：指将未存储的 SQL 语句的结果写入数据库表中；

保留点：指事务处理中设置的临时占位符，可以对它发布回退；

如何创建执行事务？

执行结果为：插入数据 (1 ,5,18) 有效，因为，只会从保留点 SAFEPOINT 之后开始回退，也就是说保留点 SAFEPOINT 之前的 SQL 语句执行的结果仍然有效。

有这样一些细节：

STAET TRANSACTION 用来表示下面的 SQL 语句集为一段事务；

SAFEPOINT 用于指定保留点 insertinto；

ROLLBACK TO 表示从指定保留点开始回退，也就是说保留点之前的 SQL 语句执行结果依然有效。如果仅仅使用 ROLLBACK 进行回退的话就表示从 STAET TRANSACTION 之后所有的 SQL 语句执行效果都会撤销；

MySQL 提交（写或保存）操作是自动进行的，这称之为隐含提交。但是在事务处理块中，提交不会隐含进行，要使用 COMMIT 子句进行提交。如：

采用 COMMIT 提交事务，如果两条 SQL 语句都执行成功，才会将数据都写入表中。

7. 触发器

什么是触发器？

当某条 SQL 语句发生时，自动执行某些其他的 SQL 语句的时候就需要使用到触发器。触发器只能响应：DELETE，INSERT,UPDATE 这三个特定操作。

创建触发器？

创建触发器时需要给出最重要的四条信息：1. 全局唯一的触发器名；2. 触发器关联的表；3. 触发器在何时执行（操作执行之前或者之后）4. 触发器应该响应的活动（DELETE, INSERT 或者 UPDATE）；

由于触发器只能响应特定的三种类型的操作，因此可创建的触发器也就三种类型:INSERT 触发器，DELETE 触发器以及 UPDATE 触发器。

INSERT 触发器

在执行 INSERT 触发器时，也这样几点需要注意：1. 在 INSERT 触发器代码内，可以引用一个名为 NEW 的虚拟表，可以用 NEW 来访问刚插入的行数据；2. 在 BEFORE INSERT 触发器中，NEW 中的值可以被更新；3. 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自定生成值。

创建一个 INSERT 触发器，每次插入一行数据，每次会返回当前插入的行数据的 id。