MySQL命令学习笔记（六）

最新推荐文章于 2023-02-16 19:41:50 发布

会飞的鱼zhh

最新推荐文章于 2023-02-16 19:41:50 发布

阅读量213

点赞数

分类专栏： SQL 文章标签： mysql创建和操作表 mysql使用视图 mysql使用存储过程

本文链接：https://blog.csdn.net/qq_18150255/article/details/94048156

版权

SQL 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

数据库学习笔记

9 篇文章 0 订阅

订阅专栏

1 创建和操作表

1.1 创建表

MySQL不仅用于表数据操作，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。创建表的两种方法：

使用具有交互式创建和管理表的工具
表也可以直接用MySQL语句操作

1.1.1 表创建基础

为利用CREATE TABLE创建表，必须给出以下信息：

新表名字，在关键字CREATE TABLE之后给出
表列的名字和定义，用逗号分隔

示例如下：

CREATE TABLE table3
(
    id    int         NOT NULL AUTO_INCREMENT,
    name  char(50)    NOT NULL,
    phone  char(50)   NOT NULL,
    home  char(50)    NOT NULL,
    PRIMARY KEY(id)
)ENGINE=InnoDB;

结果如下：
create
各列之间用逗号分隔，表的主键可以在创建表时用PRIMARY KEY关键字指定。在创建新表时，指定的表名必须不存在，否则将出错。

1.1.2 使用NULL值

NULL值就是没有值或缺省值，允许NULL值的列（NULL）也允许在插入行时不给出该列的值。不允许NULL值的列（NOT NULL）不接受该列没有值的行，即在插入或更新行时，该列必须有值

1.1.3 主键介绍

主键值必须唯一，即表中的每个行必须具有唯一的主键值。若主键使用单个列，则其值必须唯一，若使用多个列，则这些列的组合值必须唯一。

PRIMARY KEY(id)    #单列组成的主键
PRIMARY KEY(id, name)    #多列组成的主键

1.1.4 使用AUTO_INCREMENT

AUTO_INCREMENT意味着本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。每个表只允许一个AUTO_INCREMENT列，而且必须被索引

1.1.5 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。
示例如下：

CREATE TABLE table3
(
    id    int         NOT NULL AUTO_INCREMENT,
    name  char(50)    NOT NULL,
    phone  char(50)   NOT NULL,
    home  char(50)    NOT NULL,
    quantity int      NOT NULL DEFAULT 1,
    PRIMARY KEY(id)
)ENGINE=InnoDB;

1.1.6 指明引擎类型

在上述例子中最后一句：ENGINE=InnoDB。与其他DBMS一样， MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理请求。这些引擎具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。几个常用引擎：

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能上等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别是适合于临时表）；
MyISAM是一个性能极高的引擎，其支持全文本搜索，但不支持事务处理。

1.2 更新表

为更新表定义，可使用ALTER TABLE语句。使用ALTER TABLE更改表结构时，必须给出下面的信息：

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
所做更改的列表
例如给table3增加一个列：

ALTER TABLE table3 ADD order_date char(20);

结果如下：
ALTER
注意添加列的时候，要在列名后面指定列的数据类型

1.3 删除表

删除表（删除整个表而不是其内容），使用DROP TABLE语句即可：

DROP TABLE table3;

这条语句删除customers 2表（假设它存在）。删除表没有确认，
也不能撤销，执行这条语句将永久删除该表。

1.4 重命名表

使用RENAME TABLE语句可以重命名一个表：

RENAME TABLE table3 TO mysqltable;

2 使用视图

视图是虚拟的表，与包含数据的表不同，视图只包含使用时动态检索数据的查询。

2.1 视图

2.1.1 使用视图的原因

视图的常见应用：

重用SQL语句
简化复杂的SQL操作
使用表的组成部分而不是整个表
保护数据
更改数据格式和表示
在视图创建之后，可以用于表基本相同的方式利用它们，可以对视图执行SELECT操作，过滤和排序数据。

2.1.2 视图的规则和限制

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

与表一样，视图必须唯一命名
对于可以创建的视图数目没有限制
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值
视图和表可以一起用

2.2 使用视图

视图用CREATE VIEW语句来创建
使用SHOW CREATE VIEW viewname;来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname;
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。

3 使用存储过程

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

使用存储过程的原因：

通过把处理封装在容易使用的单元中，从而简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
提高性能，因为使用存储过程比使用单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可使用其来编写功能更加灵活的代码

3.1 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。示例如下：

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

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

3.2 创建存储过程

示例如下，返回产品平均价格的存储过程

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(price) AS priceaverage
    FROM products;
END;

BEGIN和END语句用来限定存储过程体

mysql命令行客户机的分隔符： 默认的MySQL语句分隔行为分号（；）。mysql命令行实用程序也使用（；）作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。
解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(price) AS priceaverage
    FROM table1;
END //
DELIMITER ；

其中，DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END //而不是END。这样，存储过程体内的;仍然保持不动，并且
正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;。
除\符号外，任何字符都可以用作语句分隔符。

使用上述存储过程：

CALL productpricing();

3.3 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程，可使用以下语句实现删除操作：

DROP PROCEDURE productpricing；

这里存储过程名的后面没有使用()，只给出了存储过程名。

3.4 使用参数

使用OUT参数
productpricing只是一个简单的存储过程，它简单地显示SELECT语句的结果。存储过程并不显示结果，而是把结果返回给所指定的变量内存中一个特定的位置，用来临时存储是数据。

DELIMITER //
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(price) INTO pl FROM table1;
    SELECT Max(price)INTO ph FROM table1;
    SELECT Avg(price) INTO pa FROM table1;
END //
DELIMITER ；

此存储过程接受3个参数： pl存储最低价格， ph存储最高价格， pa存储平均价格。每个参数必须具有指定的类型，这里使用十进制值。
为调用此修改过的存储过程，必须指定3个变量名，如下所示：

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

变量名 所有MySQL变量都必须以@开始。
为显示检索出的最低价格，可进行如下操作：

SELECT @pricelow;

结果如下图：
CREATE 3parameter
说明： 由于在语句中使用了分号，因此要使用前面提到的临时修改分隔符DELIMITER //，这样才能在BEGIN后面的语句中使用分号，END使用//作为分隔符，最后使用DELIMITER ；语句恢复分号作为分隔符。使用CALL语句调用该存储过程，这里指定了三个参数，使用SELECT @pricelow;语句显示结果。
**说明：**这里一定要使用DELIMITER //该语句修改分隔符，否则在SELECT语句后面的分号回报错。

使用IN参数

DELIMITER //
CREATE PROCEDURE ordetotal(
    IN number INT,
    OUT total DECIMAL(8, 2)
)
BEGIN
    SELECT Sum(price * quantity)  FROM table1 WHERE id = number INTO total;
END //
DELIMITER;

调用示例：

CALL ordetotal(2, @total);

为显示检索出的结果，可进行如下操作：

SELECT @total;

再次计算另外一个id的时候，需要再次调用存储过程，然后重新显示变量

3.5 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

SHOW CREATE PROCEDURE pricing;

限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

SHOW PROCEDURE STATUS LIKE 'pricing';

会飞的鱼zhh

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL命令学习笔记（六）

1 创建和操作表1.1 创建表 MySQL不仅用于表数据操作，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。创建表的两种方法：使用具有交互式创建和管理表的工具表也可以直接用MySQL语句操作1.1.1 表创建基础为利用CREATE TABLE创建表，必须给出下列信息：新表的名字，在关键字CREATE TABLE之后给出表列的名字和定义，用逗号分隔示例如...
复制链接

扫一扫