Mysql基础

最新推荐文章于 2023-04-22 10:55:10 发布

cao xiao yang

最新推荐文章于 2023-04-22 10:55:10 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/qq_40148886/article/details/88008987

版权

Mysql基础

本文中使用的测试数据表：sql-learn.sql

一、创建和操作数据表

　　1、创建表

　　a）创建表使用create table：包括表的名字，标的字段（使用逗号隔开）

CREATE TABLE `t_user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) DEFAULT CHARSET=utf8

　　b）分析：从上面的例子可以看到，表名紧跟 CREATE TABLE 关键字。实际的表定义（所有列）括在圆括号之中，各列之间用逗号分隔。这个表由 3列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型.

　　2、关于NULL值

　　a）NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。

　　b）看下面的例子

CREATE TABLE `t_product` (
  `pid` INT(12) NOT NULL AUTO_INCREMENT,
  `pname` VARCHAR(23) NOT NULL,
  `price` DECIMAL(12,0) NOT NULL,
  `vender_id` INT(11) NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `FK_t_product` (`vender_id`),
  CONSTRAINT `FK_t_product` FOREIGN KEY (`vender_id`) REFERENCES `t_vender` (`vender_id`)
) DEFAULT CHARSET=utf8；

　　这条语句创建中所用的 product表。 product 包含四列：pid、pname、price和vender_id 。这四列都需要，因此每一列的定义都含有关键字 NOT NULL 。这就会阻止插入没有值的列。如果插入没有值的列，将返回错误，且插入失败

　　c）不要把 NULL 值与空字符串相混淆。 NULL 值是没有值，不是空字符串。如果指定 '' （两个单引号，其间没有字符），这在 NOT NULL 列中是允许的。空字符串是一个有效的值，它不是无值。 NULL 值用关键字 NULL 而不是空字符串指定

　　3、指定默认值

　　SQL 允许指定默认值，在插入行时如果不给出值， DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

　　如下例子所示：

CREATE TABLE `t_orderitem` (
  `orderItemId` INT(11) NOT NULL,
  `quantity` INT(11) DEFAULT '1',
  `price` DECIMAL(8,2) DEFAULT '0.00',
  `oid` INT(11) DEFAULT NULL,
  `pid` INT(11) DEFAULT NULL,
  PRIMARY KEY (`orderItemId`),
) DEFAULT CHARSET=utf8

　　默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。 MySQL 用户指定 DEFAULT CURRENT_DATE()

　　4、主键

　　a）主键值唯一，表中的每个行必须有唯一的主键。如果主键使用单个列，那么这个列的值必须唯一；如果使用多个列，那么组合值必须唯一；

　　　PRIMARY KEY (`orderItemId`)

　　5、auto_increment

　　a）在上面创建user表的时候，使用AUTO_INCREMENT表示每增加一行自动将user_id增加1（在当前user_id最大值的基础上）

　　b）确定AUTO_INCREMENT的值：在Mysql中使用AUTO_INCREMENT之后获得这个值的函数是LAST_INSERT_ID()

　　SELECT LAST_INSERT_ID();返回最后一个AUTO_INCREMENT的值

　　6、更新表

　　更新表定义，可以使用 ALTER TABLE 语句，使用 ALTER TABLE 更改表结构，必须给出下面的信息：①在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；②列出要做哪些更改。

　　a）增加列：ALTER TABLE t_user ADD phone_num CHAR(14);

　　b）删除列：ALTER TABLE t_user DROP phone_num;

　　c）设置外键

　　①具体的格式（假设t_user表的主键是user_id、t_order表中新增一列user_id作为外键）：规定要设置外键的表t_order为表1，被关联的表t_user为表2

　　ALTER TABLE 表1 ADD CONSTRAINT Fk_user_order FOREIGN KEY (表1.外键字段) REFERENCES 表2(表2.被关联字段);

　　②按照上面的格式我们设置外键如下

ALTER TABLE t_order
    ADD CONSTRAINT Fk_user_order
    FOREIGN KEY (user_id) REFERENCES t_user (user_id);

　　然后查看该表的关联外键信息如下：可以得到我们新增外键的信息；

　　7、删除表

　　删除表的sql格式比较简单：drop table + 表名；（但是注意如果表有外键关联关系会删除失败）

　　8、重命名表

　　使用RENAME TABLE 可以重命名一张表的名字：

RENAME TABLE t_user TO t_customer;

二、插入数据

　　上面介绍了关于创建表和操作表的一些sql操作，下面我们来说下数据库表的insert操作

　　1、数据插入

　　insert的作用就是用来插入或者添加数据到指定的数据表的，使用方式有下面几种：

　　①插入完整的行

　　②插入行的部分数据

　　③插入多行数值

　　④插入某些查询出来的结果

　　下面使用具体的实例来进行说明：

　　2、插入完整的行

　　a）使用基本的insert语句：

INSERT INTO t_user VALUES(4,'张翔','zhangxiang@qq.com');

　　这种方式会向数据库中添加上面指定的数据，但是在values中的数据次序必须按照数据表定义的次序进行填写，这种编写方式是比较不安全的（即使某次的顺序是这样，但是数据表的列次序变动之后就会容易出现错误）；

　　3、插入行的部分数据

　　a）提供列名方式的insert语句：

INSERT INTO t_vender(vender_name, vender_addr, vender_phone) VALUES('TestNAME','上海','15656565656');

　　这种方式是按照选出数据表中的某些字段，然后按照指定的次序在values中填写数据，只是这种方式会比较繁琐，但是相对而言是比较安全的。

　　b）总结一下：

　　①不管使用哪种 INSERT 语法， VALUES 的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入

　　②性能部分：数据库经常会多个客户访问，对于处理什么请求以及用什么次序进行处理是Mysql的任务，而insert语句是比较耗时的（特别是很多索引需要更新的时候），并且这种insert语句也可能会降低等待处理的select语句的性能。

　　对于我们而言，如果数据的检索是比较重要的，那么可以在insert和into之间增加关键字LOW_PRIORITY，告诉Mysql降低insert语句的优先级（对于后续说到的DELETE和UPDATE也是一样的）

INSERT LOW_PRIORITY  INTO

　　4、插入多行数据

　　a）对于多条数据的插入，我们可以使用多条insert语句，如下所示：

INSERT INTO t_user(username, email) VALUES('TestName1','test1@qq.com');
INSERT INTO t_user(username, email) VALUES('TestName2','test2@qq.com');

　　b）使用单条insert语句

INSERT INTO t_user(username, email) VALUES('TestName3','test3@qq.com'),('TestName4','test4@qq.com');

　　c）关于性能部分：Mysql处理单条insert语句插入多条记录比多条insert语句插入多条数据的语句会更快

　　5、插入检索出的数据

　　a）INSERT 一般用来给表插入具有指定列值的行。 INSERT 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的 INSERT SELECT ，它是由一条 INSERT 语句和一条 SELECT 语句组成的。这就是插入检索出来的数据

INSERT INTO t_user(username, email) SELECT username, email FROM t_userNew;

　　b）分析一下上面的例子：

　　例子中使用insert select从t_userNew表中将所有的数据导入t_user表中，select语句从t_userNew中检索出需要插入的值，而不是列出来。select中列出的每一个列对应于t_userNew表名后所跟的列表中的每个列。这条语句将插入多少行有赖于t_userNew中有多少数据。如果这个表中含有数据就会将检索出来的所有数据添加到t_user表中

　　c）需要说明的是：

　　并不一定需要将被检索的表列名设置为和被插入表的列名相同，它使用的是列的位置，因此select中的第一列将用来填充指定列中的第一列（一次类推），这对于不同列名的数据导入是非常有用的。

三、更新和删除数据

　　1、更新数据

　　a）更新（修改）表中的数据，可以使用 UPDATE 语句。有两种使用 UPDATE 的方式：
　　①更新表中的特定行；
　　②更新表中的所有行。
　　b）注意不要省略 WHERE 子句，在使用 UPDATE 时一定要细心。因为稍不注意，就会更新表中的所有行；

　　c）基本的 UPDATE 语句由三部分组成，分别是：

　　①要更新的表；
　　②列名和它们的新值；
　　③确定要更新哪些行的过滤条件

　　比如下面的例子：

UPDATE t_user SET email = 'updateTest@qq.com' WHERE user_id = 1;

　　d）更新多个列的语法：

UPDATE t_user 
    SET username = 'updateTest',
    email = 'updateTest@qq.com' WHERE user_id = 1;

　　2、删除数据

　　a）同更新数据一样，从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE 的方式：

　　①从表中删除特定的行；
　　②从表中删除所有行。
　　注意不要省略 WHERE 子句。因为稍不注意，就会错误地删除表中所有行；

　　b）下面是一个简单的例子：

TRUNCATE TABLE t_userNew;

　　删除t_user表中id=10的信息

　　c）关于存在外键时候的删除：存在外键时， DBMS使用它们实施引用完整性。例如要向 t_product表中插入一个新的信息， DBMS不允许通过未知的vender_id 插入它，因为 vender_id 列是作为外键连接到 t_vender 表的。使用外键确保引用完整性的一个好处是， DBMS 通常可以防止删除某个关系需要用到的行。例如，要从 t_product表中删除一个产品，而这个产品用在 t_orderitem的已有订单信息记录中，那么 DELETE 语句将抛出错误并中止。下面给出一个例子：

　　这是t_product表中的数据信息：

　　这是t_orderitem中的数据：　

　　下面我们删除pid=1的product信息，显然在t_orderitem中有这样的关联记录，所以执行之后会报错：

　　d）如果想从表中删除所有行，可以不使用 DELETE 。可使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动），TRUNCATE 的实际作用是删除原来的表并重新创建一个表（表的字段不变），而不是逐行删除数据。如下所示，删除我们刚刚建立的t_userNew表

TRUNCATE TABLE t_userNew;

　3、更新和删除的指导规则

　　前面我们使用的 UPDATE 和 DELETE 语句都有 WHERE 子句，这样做的理由很充分。如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中所有的行。换句话说，如果执行 UPDATE 而不带 WHERE 子句，则表中每一行都将用新值更新。类似地，如果执行 DELETE 语句而不带 WHERE 子句，表的所有数据都将被删除。
　　下面是使用 UPDATE 或 DELETE 时所遵循的重要原则。
　　①除非确实打算更新和删除每一行，否则一定不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
　　②保证每个表都有主键，尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
　　③在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
　　④使用强制实施引用完整性的数据库（外键引用），这样 DBMS 将不允许删除其数据与其他表相关联的行。
　　⑤有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应该使用它。
　　⑥若是 SQL 没有撤销（ undo ）按钮，应该非常小心地使用 UPDATE 和 DELETE ，否则你会发现自己更新或删除了错误的数据。

四、创建计算字段

　　1、计算字段

　　①存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。这就是计算字段可以派上用场的地方了。与前几课介绍的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

　　②字段：基本上与列（ column ）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。

　　2、拼接字段

　　举下面的例子：t_user表中有username和email信息，我们要生成的报表信息中需要将信息按照username（email）查询打印出来，而表中username和email是两个不同的字段，所以我们需要将两个字段拼接然后连接在一起。在Mysql中我们使用Concat函数（Concat指定多个字符串，各个串之间用逗号隔开），下面是表当前的数据

　　然后我们的sql语句是：

SELECT CONCAT(user_name, '(', user_email, ')') FROM t_user;

　　得出下面的结果

　　3、删除空格信息

　　①使用RTrim函数来删除数据右侧多余的空格来整理数据，数据如下，我们查询出的要求删除末尾的空格

　　②在没有使用RTrim之前使用 SELECT CONCAT(user_name, '(', user_email, ')') FROM t_user;的结果如下

　　③使用之后SELECT CONCAT(RTRIM(user_name), '(', user_email, ')') FROM t_user;

　　④关于Trim函数：Mysql中除了支持RTrim函数之外，还支持LTrim函数（删除左边的空格）和Trim函数（删除左右两边的空格）

　　4、使用别名

　　①从前面的输出可以看到， SELECT 语句可以很好地拼接地址字段。但是这个新计算列实际上没有名字如同一样，它只是一个值。如果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于应用程序中，所以我们需要别名定义（也成为导出列）

　　使用这样的SQL：SELECT CONCAT(RTRIM(user_name), '(', user_email, ')') AS user_title FROM t_user;

　　5、执行算数计算

　　①Orders 表包含收到的所有订单， OrderItems 表包含每个订单中的各项物品。下面的 SQL 语句检索订单号 1中的所有物品

　　②price和quantity进行汇总得到一个item的金额SELECT orderItemId,quantity,price, quantity*price AS subtotal FROM t_orderitem WHERE oid = 1;

　　③Mysql中支持的其他基本运算操作符

五、数据处理函数

　　1、使用函数

　　a）大多数 SQL 实现支持以下类型的函数。

　　①用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。

　　②用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

　　③用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。

　　④返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数

　　2、文本处理函数

　　a）UPPER函数：将本文转换为大写SELECT UPPER(username) AS username_uppercase,email FROM t_user;

　　b）其他文本函数

　　c）解释一下表中的SOUNDEX函数：将任何一个文本串转换为描述其语音表示的字母数字模式的函数，考虑到类似的发音或者是音节，能够对字符串发音作比较而不是字符串本身作比较。下面给出一个例子

　　假设t_user表中有一个用户名字是Y.lee，但是如果名称录入错误，真实名称应该是Y.lie，那么简单的按照名称本身的内容进行搜索时不能得到结果的，现在使用这个函数

　　SELECT username, email FROM t_user WHERE SOUNDEX(username) = SOUNDEX('Y.Lie');

　　3、日期和时间处理函数

　　a）日期和时间采用相应的数据结构和特殊的格式进行存储，以便能够进行快速的排序过滤。应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值

　　b）常用日期处理函数

　　c）首先需要注意的是，Mysql中的日期格式为yyyy-MM-dd,使用年份的时候使用4位数的年份总是更加可靠。

　　d）看一下下面的例子，原始表中的数据如下，其中order_time的数据类型为datetime

　　①查询出2019-2-25日期的信息，当我们直接这样使用时候SELECT oid,total,order_time WHERE order_time = '2019-2-24';并不会查询出来结果（尽管其中有三条记录是这一天的）

　　②datetime类型存储的是日期+时间，按照上面的查询即使表中有这样的记录也不会查询出来而是报错，所以实际上我们查询的时候可以只是提取出日期部分进行查询，而不是将整个列进行比较，所以可以使用Date()函数

　　按照这样的查询SELECT oid,total,order_time,user_id FROM t_order WHERE DATE(order_time) = '2019-2-24';就可以得到下面的结果

　　③如果想要查询出某个月份下面的所有信息，可以使用BETWEEN---AND进行，或者是独立这是年份和月份

　　SELECT oid,total,order_time FROM t_order WHERE DATE(order_time) BETWEEN '2019-2-01' AND '2019-2-29';

　　SELECT oid,total,order_time FROM t_order WHERE YEAR(order_time) = '2019' AND MONTH(order_time) = '02';

　　4、数值处理函数

　数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期 - 时间处理函数使用那么频繁。下面是一些常用的数值处理函数

六、汇总数据

　　1、概述

　　我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了专门的函数。使用这些函数， SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：

　　①确定表中行数（或者满足某个条件或包含某个特定值的行数）；
　　②获得表中某些行的和；
　　③找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。
　　上述例子都需要汇总表中的数据，而不需要实际数据本身。因此，返回实际表数据纯属浪费时间和处理资源。我们实际想要的是汇总信息。为方便这种类型的检索， MySQL 给出了 5 个聚集函数，这些函数能进行上述检索。如下图所示常用的汇总函数

　　2、AVG函数

　　AVG() 通过对表中行数计数并计算其列值之和，求得该列的平均值。 AVG() 可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

　　SELECT AVG(price) FROM t_orderitem AS avg_price;

　　3、COUNT函数

　　可以用于计算，利用count计算表中行的数目或者是符合条件的行的数目：

　　①count(*)对表中的所有行的数目进行计算，不管是表中列中所包含的是NULL还是非NULL：SELECT COUNT(*) FROM t_orderitem;

　　②使用count(column)对指定列中的具有的值进行计算，忽略NULL值：SELECT COUNT(oid) FROM t_orderitem;

　　4、Max函数

　　MAX() 返回指定列中的最大值：

　　a)Max不仅可以用于找出最大的数据或者日期，还可以用于返回任意列中的最大值，包括返回文本列中的最大值（用于文本数据的时候，如果按照相应的列进行排序，则Max返回最后一行）

　　①返回数据：

　　②返回文本：

　　b）Max忽略值为NULL的列

　　5、MIN函数

　　MIN函数的功能和MAX函数相反，就是返回最小值，Min要求制定列名：

　　①MIN函数具有类似于MAX函数的特点，可以用于文本数据，在用于文本数据的时候，按照相应列进行排序时，返回最前面的一行

　　6、SUM函数

　　SUM() 用来返回指定列值的和（总计），OrderItems 包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有 quantity 值之和）

　　SELECT SUM(quantity) AS total_quantity FROM t_orderitem;

　　7、聚集不同值

　　a）以上 5 个聚集函数都可以如下使用：①对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。②只包含不同的值，指定 DISTINCT 参数。

　　b）ALL 为默认：ALL 参数不需要指定，因为它是默认行为。如果不指定 DISTINCT ，则假定为 ALL

　　c）下面的例子使用 AVG() 函数返回产品的平均价格。它与上面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格

　　上图为原始表中的price信息，没有使用distinct之前

　　使用之后

　　c）注意：DISTINCT 不能用于 COUNT(*)，如果指定列名，则 DISTINCT 只能用于 COUNT() 。 DISTINCT 不能用于 COUNT(*) 。类似地， DISTINCT 必须使用列名，不能用于计算或表达式

　　①使用COUNT(*):会直接报错

　　②使用指定列名：

　　8、组合聚集函数

　　实际上Mysql可以同时使用多个聚集函数，比如：

SELECT COUNT(*) AS count_num,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price
    FROM t_orderitem;

七、分组依据

　　1、创建分组

　　a）分组是在select语句中的group by子句中建立的，如下面所示的例子

　　首先我们看一下下面的数据表信息　

　　然后使用下面的sql查询，得出结果

SELECT oid, COUNT(*) AS orderitemNum
    FROM t_orderitem
    GROUP BY oid;

　　分析一下上面的结果：上面的select 语句指定了两个列，第一个就是订单的编号信息，第二列是计算该编号下面有多少条orderitem数据记录。group by子句告诉Mysql按照oid分组并进行排序。这导致的就是查询对于每个oid进行而不是对整个表计算count(*),而查询结果也和我们最开始分析的表信息结果相同。

　　b）对于group by的一些规定

　　①group by子句可以包含任意列，这可以使得对分组进行嵌套，为数据分组提供更细致的控制。

　　②如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。　　

　　③GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。

　　④大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。

　　⑤如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
　　⑥GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

　　2、过滤分组

　　a）除了能用 GROUP BY 分组数据外， MySQL 还允许过滤分组（包括哪些分组，排除哪些分组）。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。在这个例子中 WHERE 不能完成任务，因为 WHERE 过滤指定的是行而不是分组。事实上， WHERE 没有分组的概念。MySQL 为此提供了另一个子句，就是 HAVING 子句。 HAVING 非常类似于 WHERE 。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是， WHERE 过滤行，而 HAVING 过滤分组。

　　b）下来我们就编写一个sql：列出订单条目数大于2的所有订单

　　①首先归类一下下面表中的信息以便对所写的sql进行验证

　　②sql如下：

SELECT oid, COUNT(*) AS count_item
    FROM t_orderitem
    GROUP BY oid
    HAVING COUNT(*) > 2

　　下面使我们查询到的结果信息：大概就是按照订单id进行分组，得到每个分组的orderitem数量，然后查询出其中数目大于2的记录

　　c）HAVING和WHERE的区别

　　WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。这是一个重要的区别， WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

　　d）事实上，在上面的基础上加可以再加上where查询条件，考虑这种查询要求：在上面的基础上，要求查处orderitem.price>30的记录并按照分组查询，我们对修改信息之后的表分析一下　　

　　然后按照下面的sql进行查询

SELECT oid, COUNT(*) AS count_item
    FROM t_orderitem
    WHERE price > 30
    GROUP BY oid
    HAVING COUNT(*) >= 2;

　　得出查询结果信息：

　　3、分组和排序

　　a）关于分组Group By和排序Order By的区别

　　b）下面使用order by 和group by完成下面的查询：要求查询出order总金额大于250的order得id和总计金额，并且将金额降序排列

　　使用下面的sql进行查询

SELECT oid, SUM(quantity*price) AS order_total
    FROM t_orderitem
    GROUP BY oid
    HAVING SUM(quantity*price) >= 250
    ORDER BY order_total DESC;

　　就会得到结果如下：

　　4、总结select语句的顺序：即select --- from --- where --- group by --- having --- order by ;

八、使用子查询

　　1、子查询概念

　　SELECT 语句是 SQL 的查询。我们迄今为止所看到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。MySQL 还允许创建子查询（ subquery ），即嵌套在其他查询中的查询。下面通过例子来进行说明

　　2、使用子查询进行过滤

　　a）关于数据库表的部分在文首可以下载sql文件，我们现在考虑下面的例子，需要检索出product编号为2的的所有user信息：

　　①首先检索出包含product编号为2的orderitem，

　　②然后通过orderitem得到所有的order，

　　③通过order得到所有user的uid，

　　④通过uid查询到所有的user信息

　　b）我们将上面的几个查询合并使用子查询来实现

　　①首先第一条很明显需要查询出id=2的orderitem信息（主要是编号信息）

SELECT orderItemId
    FROM t_orderitem
    WHERE pid = 2;

　　②上面已经得到orderitem的id信息，然后通过这个来查询order的信息

SELECT oid
    FROM t_orderitem
    WHERE orderItemId IN(2,4,7);

　　③根据查询到的oid信息去t_order中查询user的uid信息，我们可以筛去重复的结果使用distinct，

SELECT DISTINCT user_id
    FROM t_order
    WHERE oid IN(1,2,3);

　　④最后将user_id为1和2的信息查询出来

SELECT user_id, username, email
    FROM t_user
    WHERE user_id IN(1,2);

　　c）考虑使用子查询实现上面的工作：将每一步的查询作为一个字查询条件嵌入到一个select语句中

SELECT user_id, username, email
    FROM t_user
    WHERE user_id IN(
        SELECT user_id
            FROM t_order
            WHERE oid IN(
                SELECT oid
                    FROM t_orderitem
                    WHERE pid = 2
                ));

　　最后我们得到的结果和分步使用select相同：　　

　　3、作为计算字段使用子查询

　　a）考虑下面的问题背景：我们现在user中的每个user的order总数：

　　①首先从user表中检测出所需要查询的user列表信息

　　②然后从上面查询到的信息中去查询order信息

　　b）考虑使用下面的sql

SELECT user_id,username,
    (SELECT COUNT(*) 
        FROM t_order
        WHERE t_user.user_id = t_order.user_id) AS order_counts
    FROM t_user
    ORDER BY user_id;

　　c）上面的子查询就是查出每个user的order数目，然后使用别名，再加上查询到的user其他信息就可以得出结果。

　　d）相关子查询的概念：涉及外部条件的子查询，任何时候只要查询列名可能有多义性，就应该使用这种语法（表名.逗号的形式），如同上面例子中的sql。

九、联结表

　　1、联结的概念

　　a）SQL 最强大的功能之一就是能在数据查询的执行中联结（ join ）表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识

　　2、关系表

　　a）考虑下面的例子

　　有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述（desc）、价格（price），以及生产该产品的供应商。现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的理由是：

　　①同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；

　　②如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；

　　③如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。

　　关键是，相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

　　b）在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信息。 Vendors 表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（ primary key ），可以是供应商 ID 或任何其他唯一值。Products 表只存储产品信息，除了存储供应商 ID （ Vendors 表的主键）外，它不存储其他有关供应商的信息。 Vendors 表的主键将 Vendors 表与 Products 表关联，利用供应商 ID 能从 Vendors 表中找出相应供应商的详细信息。这样做的好处是：

　　供应商信息不重复，不会浪费时间和空间；如果供应商信息变动，可以只更新 Vendors 表中的单个记录，相关表中的数据不用改动；由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。总之，关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

　　c）使用上面的思路之后，数据信息不在一张表之中，我们就可以使用联结实现select的查询工作

　　3、创建联结

　　a）创建联结的方式很简单：指出要联结的所有表以及他们如何进行关联即可

SELECT vender_name, pname, price
    FROM t_product, t_vender
    WHERE t_vender.vender_id = t_product.vender_id;

　　查询结果如下：

　　b）分析一下上面的sql：

　　 SELECT 语句与前面所有语句一样指定要检索的列，FROM 子句列出了两个表： t_vender和 t_product。它们就是这条 SELECT 语句联结的两个表的名字。这两个表用 WHERE 子句正确地联结， WHERE 子句指示数据库将 t_vender 表中的 vender_id与 t_product表中的 vender_id匹配起来。

　　c）使用where子句

　　在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示 DBMS 如何对表进行联结的内容，在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。没有 WHERE 子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起

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

　　我们将上面的where过滤条件删除掉，会是下面的结果，所以不要忘了where条件，会帮助过滤掉不正确或者不需要的数据

　　4、内联结

　　等值联结（ equijoin ）是基于两个表之间的相等测试。这种联结也称为内联结（ inner join ）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型。下面的 SELECT 语句返回与前面例子完全相同的数据。

SELECT vender_name,pname,price
    FROM t_vender INNER JOIN t_product
    ON t_vender.vender_id = t_product.vender_id;

　　5、自联结

　　a）考虑下面的问题背景：加入检查产品时候发现id=XXX的产品出现问题，所以会怀疑生产这个产品的供应商的其他产品是否也出现问题，所以首先需要根据问题产品id从t_product中查询出该供应商，然后根据这个供应商再回来查询t_product表

　　b）我们考虑使用子查询解决这个问题，可以使用下面的sql

SELECT pid,pname 
    FROM t_product
    WHERE vender_id = (SELECT vender_id
                FROM t_product
                WHERE pid = 2);

　　就会查询出结果：

　　c）使用联结实现（下面的方式就是自联结）

SELECT pid, pname
    FROM t_product AS p1, t_product AS p2
    WHERE p1.vender_id = p2.vender_id
    AND p2.pid = 2;

　　能够得到和上面相同的结果

　　6、自然联结

　　无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（没有where过滤的联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次，自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（ SELECT * ），而对其他表的列使用明确的子集来完成

　　使用下面的sql进行查询（在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来）

SELECT u.*,
    o.total AS order_total, order_time,
    oi.quantity AS orderitem_quantity, oi.price AS orderitem_price, ((oi.quantity)*(oi.price)) AS orderitem_total
    FROM t_user u, t_order o, t_orderitem oi
    WHERE u.user_id = o.user_id
    AND oi.oid = o.oid
    AND oi.pid = 1;

　　得出下面的结果

　　7、外联结

　　a）许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行，这时候我们就可以使用外联结。比如说下面的情况：

　　①对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
　　②列出所有产品以及订购数量，包括没有人订购的产品；
　　③计算平均销售规模，包括那些至今尚未下订单的顾客。

　　b）上面的第一种情况使用sql进行说明

　　①不使用外联结的时候（普通的内联结）：不会查询出没有订单的用户信息

SELECT t_user.user_id, t_user.username, t_order.oid
    FROM t_user INNER JOIN t_order
    ON t_user.user_id = t_order.user_id;

　　②使用左外联结：能够对每个user下的订单进行计数，包括那些至今尚未下订单的user；

SELECT t_user.user_id, t_user.username, t_order.oid
    FROM t_user LEFT OUTER JOIN t_order
    ON t_user.user_id = t_order.user_id;

　　c）左外、右外联结

　　①在上面的例子中，LEFT OUTER JOIN 表示左边的表，即从左边表中选择所有行（包括NULL）

　　②RIGHT OUTER JOIN表示右边的表，选择右边表中所有行（包括NULL）

SELECT t_user.user_id, t_user.username, t_order.oid
    FROM t_user RIGHT OUTER JOIN t_order
    ON t_user.user_id = t_order.user_id;

　　8、使用带有聚集函数的联结

　　a）检索所有顾客及每个顾客所下的订单数，我们使用count函数完成

SELECT t_user.user_id,t_user.username,
    COUNT(t_order.oid) AS order_counts
    FROM t_user LEFT OUTER JOIN t_order
    ON t_user.user_id = t_order.user_id
    GROUP BY t_user.user_id
    ORDER BY t_user.user_id;

　　得到下面的结果，

　　9、使用联结和联结条件

　　①注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
　　②保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
　　③应该总是提供联结条件（类似于where条件），否则会得出笛卡儿积。
　　⑤在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单

十、组合查询

　　1、组合查询概念

　　a）多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。但是， SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（ union ）或 复合查询（ compound query ）。主要有两种情况需要使用组合查询：
　　①在一个查询中从不同的表返回结构数据；
　　②对一个表执行多个查询，按一个查询返回数据。

　　2、创建组合查询

　　a）可用 UNION 操作符来组合数条 SQL 查询。利用 UNION ，可给出多条 SELECT 语句，将它们的结果组合成一个结果集。使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION

　　b）比如说：需要将价格大于40的product查询出来，而且也查询出由供应商1和2生产的所有物品，二者查询结果形成并集

　　①将价格大于40的product查询的sql

SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE  price >= 40

　　②查询由供应商1和2生产的所有物品

　　c）上面的结果是单条查询的结果，使用组合查询将二者的查询结果做并集

SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE  price >= 40
UNION
SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE vender_id IN(1,2);

　　3、使用UNION的规则

　　使用union进行组合时需要注意几条规则。

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

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

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

　　4、Union包含或者取消重复的行

　　a）请仔细观察上面例子的输出结果：

　　在分别执行语句时，第一条 SELECT 语句返回 3 行，第二条 SELECT 语句返回 6 行。而在用 UNION 组合两条 SELECT 语句后，只返回 7 行而不是 9 行。UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。因为 test5和test8两种商品都符合两个查询的条件，所以两条 SELECT 语句都返回该行。使用 UNION 时，重复的行会被自动取消。这是 UNION 的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION 。如同下面的例子：

SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE  price >= 40
UNION ALL
SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE vender_id IN(1,2);

　　5、对查询结果进行排序

　　a）SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY 子句。

　　b）指向下面的含有排序的sql

SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE  price >= 40
UNION ALL
SELECT p.pid, p.pname, p.price, vender_id
    FROM t_product p
    WHERE vender_id IN(1,2)
ORDER BY pid;

十一、排序、通配符

　　1、排序

　　a）普通的查询数据库的单个列，一般情况下输出并没有特殊的顺序。比如

SELECT vender_name,vender_addr,vender_phone FROM t_vender;

　　事实上，查询出来的数据并不是按照随机顺序显示的，在没有指定顺序的情况下一般都是按照在数据表中的顺序显示的（可能被认为是数据表中最初插入的数据），但是如果数据在后期进行修改更新或者删除的话，这个顺序就会收到Mysql重用回收空间的影响，所以不指定输出顺序的话，被检索出来的数据的顺序是没有意义的。

　　b）指定顺序可以使用Order by子句来实现：

SELECT vender_name,vender_addr,vender_phone FROM t_vender ORDER BY vender_name;

　　c）上面的查询是按照单个列进行的，我们也可以按照多个列进行排序，

SELECT vender_name,vender_addr,vender_phone FROM t_vender ORDER BY vender_name,vender_addr;

　　d）除此之外，我们可以指定排序的方向，上面的查询默认情况下是按照升序（ASC）排序的，降序排序需要我们显示的指出：

SELECT vender_name,vender_addr,vender_phone FROM t_vender ORDER BY vender_name DESC;

　　2、通配符

　　a）%通配符的使用：%表示任何字符出现的任何次数

SELECT username,email FROM t_user WHERE username LIKE '%TestName%';

　　通配符可以使用在任何位置，并且可以使用多个通配符。

　　b）（_）通配符：匹配单个字符

SELECT username,email FROM t_user WHERE username LIKE '%Test_ame%';

　　c）需要注意的是：SQL 的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。

　　①不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
　　②在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
　　③仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

十一、正则表达式

　　正则表达式是用来匹配文本的特殊的串或者说特殊的字符集合的表达式。下面介绍Mysql中常用的正则表达式

　　1、基本字符串匹配

　　a）下面的sql检索出username中含有TestName的行，这条语句告诉Mysql：REGEXP后面所跟的东西作为正则表达式处理。

SELECT username,email FROM t_user WHERE username REGEXP 'TestName';

　　b）使用"."，看看下面的例子

SELECT username,email FROM t_user WHERE username REGEXP 'Test.';

　　2、进行OR匹配

SELECT username,email FROM t_user WHERE username REGEXP '1|2|3|4';

　　上面的例子就表示匹配含有1、2、3、4的所有记录，所以username中含有这些的都被返回

　　3、匹配几个字符之一

　　①使用下面的例子：表示匹配testName1、testName2、testName3、testName4

SELECT username, email FROM t_user WHERE username REGEXP 'testName[1234]';

　　②上面的查询其实和OR语句类似（可以理解为[1234]是[1|2|3|4]的简写形式），但是使用[]的原因很简单，看下面的例子（我们在sql语句中没有使用[]）：

SELECT username, email FROM t_user WHERE username REGEXP 'testName1|2|3|4';

　　这个sql的查询结果如下：

　　相比较于上面的查询结果而言多出了下面那条记录，原因？

　　Mysql以为我们需要将含有testName1、2、3、4这些的记录都查询出来，这样的话，修改一下上面的sql，得到的结果就和开始的结果相同了

SELECT username, email FROM t_user WHERE username REGEXP 'testName[1|2|3|4]';

　　③我们也可以使用^做否定，如下所示：[^12]表示出了1、2这两个字符之外的信息

SELECT username, email FROM t_user WHERE username REGEXP 'testName[^12]';

　　4、匹配范围

　　a）我们可以使用如同上面[1234]的形式匹配数字1、2、3、4，这种方式的简写方法：[1-4]（这种方式对于字母也是有效的[a-z]、[A-Z]）

SELECT username, email FROM t_user WHERE username REGEXP 'testName[1-4]';

　　上面的sql和SELECT username, email FROM t_user WHERE username REGEXP 'testName[1234]';的结果集相同

　　5、匹配特殊字符

　　a）一句话概括就是：特殊字符的匹配需要使用转义

SELECT username, email FROM t_user WHERE username REGEXP '.';

　　上面的例子中并不会只查询username中含有.的记录，然后返回下面这样的结果

　　为了匹配像.这样的特殊字符，我们需要使用转义\\，具体就是\\.表示查找. ，比如下面的例子

SELECT username, email FROM t_user WHERE username REGEXP '\\.';

　　b）除了上面的例子之外，|、[]还有其他的特殊字符都需要使用转义；下表所示是元字符的转义使用

　　6、匹配字符类

　　a）下面是经常使用的字符类以及他们的含义

　　7、匹配多个实例

　　a）当我们需要对于匹配字符的数目进行控制的时候；比如需要找到一个串中包含的数字（不管包含多少数字）等等

　　下面是正则表达式元字符说明表

　　b）我们用上面的表列出几个简单的例子

SELECT username, email FROM t_user WHERE username REGEXP '[12]\\(testNews{0,1}\\)';

　　解释一下上面的sql：[12]表示匹配子串开始的1或者2，\\(表示匹配(，s{0,1}表示匹配s（不出现s或者出现一次s），\\)表示匹配)。得到下面的结果

　　c）看下面的例子：其中[:digit:]表示匹配0-9之间任意的数字，{5}表示出现5次，所以总的查询就是匹配username中出现5次数字的子串

SELECT username, email FROM t_user WHERE username REGEXP '[[:digit:]]{5}';

　　8、定位符

　　a）匹配一个串特定位置的文本，可以使用下面的定位符

　　b）下面的sql是找出username以.开头的或者0-9开头的信息记录

SELECT username, email FROM t_user WHERE username REGEXP '^[0-9\\.]';

十二、使用视图

　　1、视图概念

　　a）视图可以简单理解为虚拟的表，它与包含数据的表不同，视图只包含使用时候动态检索出数据的查询。比如使用下面的查询时候

SELECT DISTINCT username, email
    FROM t_user u, t_order o, t_orderitem oi
    WHERE u.user_id = o.user_id
    AND o.oid = oi.oid
    AND oi.pid = 2;

　　上面的查询用来检索订购某个商品的user信息。现在，假设将查询结果包装成为一个名为user_order_orderitem的虚拟表，那么上面的查询就可以写为

SELECT DISTINCT username, email
    FROM  user_order_orderitem 
    WHERE oi.pid = 2;

　　上面的user_order_orderitem就是一个视图（没有包含任何数据，包含的只是一个SQL查询）

　　2、视图的作用

　　a）视图的一些常见应用。
　　①重用 SQL 语句。
　　②简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
　　③使用表的一部分而不是整个表。
　　④保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
　　⑤更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

　　b）创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据

　　c）视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

　　d）性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

　　3、视图的规则和限制　　

　　关于视图创建和使用的一些最常见的规则和限制。

　　①与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。

　　②对于可以创建的视图数目没有限制。

　　③创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。

　　④视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS 中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。

　　⑤视图不能索引，也不能有关联的触发器或默认值。
　　⑥ORDER BY可以用于视图中，但是如果从视图检索数据的SELECT中也含有ORDER BY，那么该视图的ORDER BY会被覆盖

　　4、使用视图

　　a）关于视图的创建

　　①视图的创建使用Create View来实现，可以通过SHOW CREATE VIEW view_name；来查看创建的视图信息；

　　②使用DROP VIEW view_name可以删除我们创建的视图；

　　③更新视图的时候，我们可以先使用DROP再使用CREATE，也可以直接使用CREATE OR REPLACE VIEW。如果更新的视图不存在，那么CREATE OR REPLACE VIEW会创建一个视图；如果更新的视图存在，那么更新视图的语句会替换原有的视图；

　　b）使用视图简化复杂的联结，如同下面的例子

CREATE VIEW user_order_items AS
SELECT DISTINCT username, email
    FROM t_user u, t_order o, t_orderitem oi
    WHERE u.user_id = o.user_id
    AND o.oid = oi.oid
    AND oi.pid = 2;
    
SHOW CREATE VIEW user_order_items;

　　可以得到下面的视图

　　然后我们将联结查询换成从视图中查询结果信息：

SELECT username, email FROM user_order_items;

　　可以得到上面的结果，实际上和我们使用联结查询的结果相同；

　　c）用视图格式化重新检索出来的数据信息

　　比如我们需要查询出来供应商名字的地址信息（按照name （地址））的形式检索数据，在我们可以创建下面这样的视图

CREATE VIEW vender_name_addr AS
SELECT CONCAT(RTRIM(vender_name), ' (', RTRIM(vender_addr), ')')
    FROM t_vender;

　　这样的话我们就不用每次都按照这样的格式去检索数据，而是直接使用select从我们创建的视图中检索数据　　

　　d）使用视图+计算字段

　　①下面的sql是计算某个指定的订单下面的条目的价格

SELECT orderItemId,quantity,price,
    quantity*price AS subtotal 
    FROM t_orderitem 
    WHERE oid = 1;

　　②将上面的sql转换为视图

CREATE VIEW order_item_price AS
SELECT orderItemId,quantity,price,
    quantity*price AS subtotal 
    FROM t_orderitem;

　　③然后再次计算某个指定订单id下面的条目的价格

SELECT * FROM order_item_price WHERE oid = 1;

　　5、更新视图

　　对于更新视图，我们会视情况而定。一般情况下，视图是可以被更新的（更新一个视图会更新其基表，视图本身是没有数据的，只是一个虚拟表形式的存在），所以对视图增加或者删除操作，实际上都是对基表进行相应的操作。

　　但是除了一般情况下，Mysql中定义：如果不能正确的确定被更新的数据，则不允许被更新；下面的这几种情况，不能进行视图的更新（视图实际上主要用于检索数据）

　　①存在分组（GROUP BY和HAVING）；

　　②存在联结；

　　③存在子查询；

　　④存在并集UNION；

　　⑤存在聚集函数（MIN()、Count()、Sum()）；

　　⑥存在DISTINCT；

　　⑦存在导出列（或者计算列）

十三、存储过程

　　1、存储过程背景

　　a）我们使用的大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一些复杂的操作需要多条语句才能完成。例如以下的情形：

　　①为了处理订单，必须核对以保证库存中有相应的物品（SELECT quantity, pid FROM t_product;）。

　　②如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量（UPDATE t_product SET quantity = 12-5 WHERE pid = 2;）。

　　③库存中没有的物品需要订购，这需要与供应商进行某种交互（UPDATE t_product SET quantity = 12+7 WHERE pid = 2;）。

　　④关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客。

　　b）执行这个处理需要针对许多表的多条 SQL 语句。此外，需要执行的具体 SQL 语句及其次序也不是固定的，它们可能会根据物品是否在库存中而变化；那么我们解决这个问题，首先可以想到的比较复杂的方法就是：单独编写每条 SQL 语句，并根据结果有条件地执行其他语句。在每次需要这个处理时（以及每个需要它的应用中），都必须做这些工作；

　　但是如果使用存储过程，那么我们就可以为以后使用的一条或者多条语句进行保存。这个显然是我们使用存储过程的一个理由，除此之外可以总结的是：

　　①使用存储过程可以将处理放在容易使用的单元之中，简化了复杂的操作；

　　②不需要反复建立一些重复的处理步骤，保证了数据的完整性，如果所有应用程序都使用同一存储过程进行试验和检测，那么使用的代码也是相同的，就会极大的放置错误的产生，保证数据的一致性；

　　③简化对变动的处理操作。如果表名、列名或者业务逻辑有变化，只需要更改存储过程的代码即可，比较方便，易维护，而且对于数据而言是比较安全的，因为存储过程限制了对基础数据的访问；

　　④提高性能，使用存储过程比单独分开的SQL更快；

　　2、使用存储过程

　　a）创建存储过程：下面我们通过一个返回product平均价格的例子来实现

　　参照下面的例子：这个存储过程的创建比较简单，就是一条select语句的封装；

　　但是需要注意的是，我们使用命令行进行操作的是，需要按照下面的方式进行（使用DELIMITER）。在Mysql中，默认的语句分隔符为;（前面基本上都是使用默认的;）。在命令行中，也基本上是按照这样的默认情况执行，但是在创建存储过程的时候，命令行程序如果解析到存储过程内的; ，那么他们就不会称为存储过程中的一部分，会导致存储过程中的SQL出现语法错误，所以我们使用DELIMITER //钙素命令行程序将//作为新的语句结束符号，可以看到的是，END之后跟的是//而不是; ，这样的话，存储过程中的; 保持不变，并且正确的传递给数据库，最后使用DELIMITER ; 再次将; 设置为语句分隔符。（注意除了\之外任何字符都可以作为语句分隔符）

DELIMITER //
CREATE PROCEDURE avg_product_price()
BEGIN
    SELECT AVG(price) AS avg_price
    FROM t_product;
END //
DELIMITER ;

　　b）创建完存储过程之后，我们可以使用SHOW查看我们创建的存储过程

SHOW CREATE VIEW order_item_price;

　　3、使用存储过程

　　a）使用存储过程比较简单，只需要调用一下即可

CALL avg_product_price();

　　CALL接受存储过程的名字以及需要传递的参数，比如说上面的存储过程如果多了param1、param2参数，那么使用的时候就是这样的

CALL avg_product_price(@param1,@param2);

　　4、删除存储过程

　　删除存储过程使用drop

DROP PROCEDURE IF EXISTS avg_product_price;

　　5、关于存储过程参数使用

　　a）参照这个例子：创建一个存储过程，计算t_product的最低价格、最高价格、平均价格，然后调用这个存储过程得到结果（注意我下面使用的存储过程的名字和上面一样，在创建下面这个存储过程的时候，如果存在相同名字的其他存储过程，需要删除）

DELIMITER //
CREATE PROCEDURE avg_product_price(
    OUT min_price DECIMAL(8,2),
    OUT max_price DECIMAL(8,2),
    OUT avg_price DECIMAL(8,2)
)
BEGIN
    SELECT AVG(price)
    INTO avg_price 
    FROM t_product;
    
    SELECT MAX(price)
    INTO max_price 
    FROM t_product;
    
    SELECT MIN(price)
    INTO min_price 
    FROM t_product;
END //
DELIMITER ;

　　b）上面创建的存储过程接受3个参数，avg_price 、max_price 、min_price ；关键字out指明相应的参数用来从存储过程传出一个数值（如果调用这个存储过程的话）；除了OUT（传出）之外，IN表示传入存储过程的参数，INOUT表示传入传出参数

　　c）下面使用这个存储过程：

CALL avg_product_price(@min_price, @max_price, @avg_price);

　　在使用之后，我们查询上面的变量值

SELECT @min_price, @max_price, @avg_price;

　　d）下面再使用一个存在IN、OUT的例子：计算指定oid的order金额

DELIMITER //
CREATE PROCEDURE order_price(
    IN order_id INT,
    OUT order_price DECIMAL(8,2)
)
BEGIN
    SELECT SUM(quantity*price)
    FROM t_orderitem
    WHERE oid = order_id
    INTO order_price;
END //
DELIMITER ;

　　然后使用上面这个存储过程

CALL order_price(2,@order_price);
SELECT @order_price;

　　就可以得出结果

　　得到不同id订单的金额值，只需要修改传入的id值即可；

　　6、建立智能存储过程

　　a）上面我使用的存储过程都是知识简单的select语句的封装，并没有实际功能，所完成的工作也可以直接靠一条条的sql来实现。存储过程使用的场合在于处理业务规则和智能处理。考虑下面的例子

　　①需要或者指定oid的订单总金额；

　　②需要将税率添加到合计之中（这就需要我们传入税率）；

　　③将与税率整合之后的最终金额返回

　　b）按照上面的例子，我们创建下面的存储过程

--关于参数说明
--order_id：给定传入的orderid
--have_tax：表示是否有税
--order_total：表示最终的order总金额
DELIMITER //
CREATE PROCEDURE order_total(
    IN order_id INT,
    IN have_tax BOOLEAN, 
    OUT order_total DECIMAL(8,2)
)
BEGIN
    --表示当前没有传出之前的order总金额
    DECLARE total DECIMAL(8,2);
    --表示税率默认是6
    DECLARE tax_num INT DEFAULT 6;
    
    --查询指定oid的total
    SELECT SUM(quantity*price)
    FROM t_orderitem
    WHERE oid = order_id
    INTO total;
    
    --在有税的情况下，重新修改当前的total值
    IF have_tax THEN
        SELECT total + (total/100*tax_num) INTO total;
    END IF;
    
    --将最终计算的结果传出
    SELECT total INTO order_total;
END //
DELIMITER ;

　　c）下面我们来使用这个存储过程：

　　①在没有税的时候

CALL order_total(2,0,@order_total);
SELECT @order_total;

　　②在有税的时候：

CALL order_total(2,1,@order_total);
SELECT @order_total;

posted @ 2019-02-27 12:37 风沙迷了眼阅读( ...) 评论( ...) 编辑收藏

cao xiao yang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql基础

Mysql基础本文中使用的测试数据表：sql-learn.sql一、创建和操作数据表　　1、创建表　　a）创建表使用create table：包括表的名字，标的字段（使用逗号隔开）CREATE TABLE `t_user` ( `user_id` INT(11) NOT NULL AUTO_INCREMENT, ...
复制链接

扫一扫