mysql tempdb_MySQL教程（四）

最新推荐文章于 2021-03-17 06:32:49 发布

闫小旺

最新推荐文章于 2021-03-17 06:32:49 发布

阅读量111

点赞数

文章标签： mysql tempdb

本文链接：https://blog.csdn.net/weixin_36329414/article/details/114330404

版权

MySQL创建与删除数据库

创建数据库

在与数据进行任何其他操作之前，需要创建一个数据库。数据库是数据的容器。它可以用于存储联系人，供应商，客户或任何想存储的数据。在MySQL中，数据库是用于存储和操作诸如表，数据库视图，触发器，存储过程等数据的对象的集合。

要在MySQL中创建数据库，请使用CREATE DATABASE语句，如下：

CREATE DATABASE [IF NOT EXISTS] database_name;

CREATE DATABASE IF NOT EXISTS mytestdb;

显示数据库

SHOW DATABASES语句显示MySQL数据库服务器中的所有数据库。您可以使用SHOW DATABASES语句来查看您要创建的数据库，或者在创建新数据库之前查看数据库服务器上的所有数据库，例如：

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| testdb |

| yiibaidb |

+--------------------+

5 rows in set

选择要使用的数据库

在使用指定数据库之前，必须通过使用USE语句告诉MySQL要使用哪个数据库。

USE database_name;

USE yiibaidb;

删除数据库

删除数据库意味着数据库中的所有数据和关联对象将被永久删除，并且无法撤消。因此，用额外的注意事项执行此查询是非常重要的。

要删除数据库，请使用DROP DATABASE语句，如下所示：

DROP DATABASE [IF EXISTS] database_name;

CREATE DATABASE IF NOT EXISTS tempdb;

SHOW DATABASES;

DROP DATABASE IF EXISTS tempdb;

MySQL创建表

MySQL CREATE TABLE语法

要在数据库中创建一个新表，可以使用MySQL CREATE TABLE语句。 CREATE TABLE语句是MySQL中最复杂的语句之一。

下面以简单的形式来说明CREATE TABLE语句的语法：

CREATE TABLE [IF NOT EXISTS] table_name(

column_list

) engine=table_type;

我们来更详细地来查看其语法：首先，指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。强烈建议在每个CREATE TABLE语句中使用IF NOT EXISTS来防止创建已存在的新表而产生错误。

其次，在column_list部分指定表的列表。字段的列用逗号(，)分隔。我们将在下一节中向您展示如何更详细地列(字段)定义。

第三，需要为engine子句中的表指定存储引擎。可以使用任何存储引擎，如：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

注：InnoDB自MySQL 5.5之后成为默认存储引擎。 InnoDB表类型带来了诸如ACID事务，引用完整性和崩溃恢复等关系数据库管理系统的诸多好处。在以前的版本中，MySQL使用MyISAM作为默认存储引擎。

要在CREATE TABLE语句中为表定义列，请使用以下语法：

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value]

[AUTO_INCREMENT]

以上语法中最重要的组成部分是：column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。

NOT NULL或NULL表示该列是否接受NULL值。

DEFAULT值用于指定列的默认值。

AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。

如果要将表的特定列设置为主键，则使用以下语法：

PRIMARY KEY (col1,col2,...)

MySQL CREATE TABLE语句示例

下面让我们练习一个例子，在示例数据库(testdb)中创建一个名为tasks的新表，如下所示：

可以使用CREATE TABLE语句创建这个tasks表，如下所示：

CREATE TABLE IF NOT EXISTS tasks (

task_id INT(11) NOT NULL AUTO_INCREMENT,

subject VARCHAR(45) DEFAULT NULL,

start_date DATE DEFAULT NULL,

end_date DATE DEFAULT NULL,

description VARCHAR(200) DEFAULT NULL,

PRIMARY KEY (task_id)

) ENGINE=InnoDB;

MySQL修改表结构

MySQL ALTER TABLE语句简介

可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。以下说明了ALTER TABLE语句语法：

ALTER TABLE table_name action1[,action2,…]

要更改现有表的结构：首先，在ALTER TABLE子句之后指定要更改的表名称。

其次，列出一组要应用于该表的操作。操作可以是添加新列，添加主键，重命名表等任何操作。ALTER TABLE语句允许在单个ALTER TABLE语句中应用多个操作，每个操作由逗号(，)分隔。

让我们创建一个用于练习ALTER TABLE语句的新表。

我们将在示例数据库(yiibaidb)中创建一个名为tasks的新表。以下是创建tasks表的脚本。

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (

task_id INT NOT NULL,

subject VARCHAR(45) NULL,

start_date DATE NULL,

end_date DATE NULL,

description VARCHAR(200) NULL,

PRIMARY KEY (task_id),

UNIQUE INDEX task_id_unique (task_id ASC)

);

使用MySQL ALTER TABLE语句来设置列的自动递增属性

假设您希望在任务表中插入新行时，task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT，如下所示：

ALTER TABLE tasks

CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

使用MySQL ALTER TABLE语句将新的列添加到表中

由于新的业务需求，需要添加一个名为complete的新列，以便在任务表中存储每个任务的完成百分比。在这种情况下，您可以使用ALTER TABLE将新列添加到tasks表中，如下所示：

ALTER TABLE tasks

ADD COLUMN complete DECIMAL(2,1) NULL

AFTER description;

使用MySQL ALTER TABLE从表中删除列

假设您不想将任务的描述存储在tasks表中了，并且必须将其删除。以下语句允许您删除tasks表的description列：

ALTER TABLE tasks

DROP COLUMN description;

使用MySQL ALTER TABLE语句重命名表

可以使用ALTER TABLE语句重命名表。请注意，在重命名表之前，应该认真考虑以了解更改是否影响数据库和应用程序层，不要因为重命名表之后，应用程序因未找到数据库表而出错。

以下语句将tasks表重命名为work_items表：

RENAME TABLE old_table_name TO new_table_name;

ALTER TABLE tasks

RENAME TO work_items;

旧表(old_table_name)必须存在，新表(new_table_name)必须不存在。如果新表new_table_name存在，则该语句将失败。

除了表之外，我们还可以使用RENAME TABLE语句来重命名视图。

在执行RENAME TABLE语句之前，必须确保没有活动事务或锁定表。请注意，不能使用RENAME TABLE语句来重命名临时表，但可以使用ALTER TABLE语句重命名临时表。

在安全性方面，我们授予旧表的任何权限必须手动迁移到新表。

在重命名表之前，应该彻底地评估影响。例如，应该调查哪些应用程序正在使用该表。如果表的名称更改，那么引用表名的应用程序代码也需要更改。此外，您必须手动调整引用该表的其他数据库对象，如视图，存储过程，触发器，外键约束等。我们将在下面的例子中更详细地讨论。

重命名视图引用的表

如果重命名一个被视图引用的表，在重命名表后，视图就无效了，并且必须手动调整视图。

例如，我们基于employees和departments表创建一个名为v_employee_info的视图，如下所示：

CREATE VIEW v_employee_info as

SELECT

id, first_name, last_name, dept_name

from

employees

inner join

departments USING (department_id);

视图使用内连接子句来连接employees和departments表。

以下SELECT语句返回v_employee_info视图中的所有数据。

mysql> SELECT

FROM

v_employee_info;

+----+------------+-----------+------------+

+----+------------+-----------+------------+

| 1 | John | Doe | Sales |

+----+------------+-----------+------------+

6 rows in set

现在，将v_employee_info视图中的employees表重命名为people，并查询视图的数据。

RENAME TABLE employees TO people;

-- 查询数据

SELECT

FROM

v_employee_info;

MySQL返回以下错误消息：

1356 - View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

SQL

我们可以使用CHECK TABLE语句来检查v_employee_info视图的状态如下：

CHECK TABLE v_employee_info;

mysql> CHECK TABLE v_employee_info;

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

| hrdb.v_employee_info | check | Error | View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set

需要手动更改v_employee_info视图，以便它引用people表而不是employees表。

重命名由存储过程引用的表

如果要重命名由存储过程引用的表，则必须像对视图一样进行手动调整。

首先，将people表重命名为employees表。

RENAME TABLE people TO employees;

然后，创建一个名为get_employee的新存储过程，该过程引用employees表。

DELIMITER $$

CREATE PROCEDURE get_employee(IN p_id INT)

BEGIN

SELECT first_name

,last_name

,dept_name

FROM employees

INNER JOIN departments using (department_id)

WHERE id = p_id;

END $$

DELIMITER;

接下来，执行get_employee存储过程从employees表来获取id为1的员工的数据，如下所示：

CALL get_employee(1);

mysql> CALL get_employee(1);

+------------+-----------+-----------+

| first_name | last_name | dept_name |

+------------+-----------+-----------+

| John | Doe | Sales |

+------------+-----------+-----------+

1 row in set

Query OK, 0 rows affected

之后，我们再次将employees表重新命名为people表。

RENAME TABLE employees TO people;

最后，调用get_employee存储过程来获取id为2的员工信息

CALL get_employee(2);

MySQL返回以下错误消息：

Table 'hrdb.employees' doesn't exist

要解决这个问题，我们必须手动将存储过程中的employees表更改为people表。

重命名引用外键的表

departments表使用department_id列链接到employees表。 employees表中的department_id列是引用departments表的department_id列作为外键。

如果重命名departments表，那么指向departments表的所有外键都不会被自动更新。在这种情况下，我们必须手动删除并重新创建外键。

RENAME TABLE departments TO depts;

我们删除ID为1的部门，由于外键约束，people表中的所有行也应删除。但是，我们将department表重命名为depts表，而不会手动更新外键，MySQL会返回错误，如下所示：

DELETE FROM depts

WHERE

department_id = 1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`hrdb`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `depts` (`department_id`))

重命名多个表

也可以使用RENAME TABLE语句来一次重命名多个表。见下列声明：

RENAME TABLE old_table_name_1 TO new_table_name_2,

old_table_name_2 TO new_table_name_2,...

以下语句将 people 和 depts 重命名为 employees 和 departments 表：

RENAME TABLE depts TO departments,

people TO employees;

注意RENAME TABLE语句不是原子的。所以如果在任何时候发生错误，MySQL会将所有重新命名的表都回滚到旧名称。

使用ALTER TABLE语句重命名表

我们可以使用ALTER TABLE语句重命名一个表，如下所示：

ALTER TABLE old_table_name

RENAME TO new_table_name;

RENAME TABLE语句不能用于重命名临时表，这时就可以使用ALTER TABLE语句来重命名一个临时表。

重命名临时表示例

首先，我们创建一个临时表，其中包含来自employees表的last_name列的所有唯一的姓氏：

CREATE TEMPORARY TABLE lastnames

SELECT DISTINCT last_name from employees;

第二步，使用RENAME TABLE重命名姓氏表：

RENAME TABLE lastnames TO unique_lastnames;

Error Code: 1017. Can't find file: '.\hrdb\lastnames.frm' (errno: 2 - No such file or directory)

第三，使用ALTER TABLE语句来重命名姓氏表。

ALTER TABLE lastnames

RENAME TO unique_lastnames;

第四，从unique_lastnames临时表查询数据：

SELECT

last_name

FROM

unique_lastnames;

+-----------+

| last_name |

+-----------+

| Doe |

| Lily |

| Dave |

| Jane |

| Josh |

| More |

+-----------+

6 rows in set

MySQL临时表

在MySQL中，临时表是一种特殊类型的表，它允许您存储一个临时结果集，可以在单个会话中多次重用。

当使用JOIN子句查询需要单个SELECT语句的数据是不可能或遇到瓶颈的时候，临时表非常方便。在这种情况下，我们就可以使用临时表来存储直接结果，并使用另一个查询来处理它。

MySQL临时表具有以下特殊功能：使用CREATE TEMPORARY TABLE语句创建临时表。请注意，在CREATE和TABLE关键字之间添加TEMPORARY关键字。

当会话结束或连接终止时，MySQL会自动删除临时表。当您不再使用临时表时，也可以使用DROP TABLE语句来显式删除临时表。

一个临时表只能由创建它的客户机访问。不同的客户端可以创建具有相同名称的临时表，而不会导致错误，因为只有创建临时表的客户端才能看到它。但是，在同一个会话中，两个临时表不能共享相同的名称。

临时表可以与数据库中的普通表具有相同的名称。例如，如果在示例数据库(yiibaidb)中创建一个名为employees的临时表，则现有的employees表将变得无法访问。对employees表发出的每个查询现在都是指employees临时表。当删除您临时表时，永久employees表可以再次访问。注意：即使临时表可以与永久表具有相同的名称，但不推荐。因为这可能会导致混乱，并可能导致意外的数据丢失。例如，如果与数据库服务器的连接丢失，并且您自动重新连接到服务器，则不能区分临时表和永久性表。然后，你又发出一个DROP TABLE语句，这个时候删除的可能是永久表而不是临时表，这种结果是不可预料的。

创建MySQL临时表

要创建临时表，只需要将TEMPORARY关键字添加到CREATE TABLE语句的中间。

例如，以下语句创建一个临时表，按照收入存储前10名客户：

CREATE TEMPORARY TABLE top10customers

SELECT p.customerNumber,

c.customerName,

FORMAT(SUM(p.amount),2) total

FROM payments p

INNER JOIN customers c ON c.customerNumber = p.customerNumber

GROUP BY p.customerNumber

ORDER BY total DESC

LIMIT 10;

现在，可以从top10customers临时表中查询数据，例如：

SELECT * FROM top10customers;

删除MySQL临时表

您可以使用DROP TABLE语句来删除临时表，但最好添加TEMPORARY关键字如下：

DROP TEMPORARY TABLE table_name;

DROP TEMPORARY TABLE语句仅删除临时表，而不是永久表。当将临时表命名为永久表的名称相同时，它可以避免删除永久表的错误

例如，要删除top10customers临时表，请使用以下语句：

DROP TEMPORARY TABLE top10customers;

请注意，如果尝试使用DROP TEMPORARY TABLE语句删除永久表，则会收到一条错误消息，指出您尝试删除的表是未知的。

如果开发使用连接池或持久连接的应用程序，则不能保证临时表在应用程序终止时自动删除。

因为应用程序使用的数据库连接可能仍然打开并放置在其他客户端使用的连接池中。因此，当不再使用它们时马上删除临时表，这是一个很好的做法。

闫小旺

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql tempdb_MySQL教程（四）

MySQL创建与删除数据库创建数据库在与数据进行任何其他操作之前，需要创建一个数据库。数据库是数据的容器。它可以用于存储联系人，供应商，客户或任何想存储的数据。在MySQL中，数据库是用于存储和操作诸如表，数据库视图，触发器，存储过程等数据的对象的集合。要在MySQL中创建数据库，请使用CREATE DATABASE语句，如下：CREATE DATABASE [IF NOT EXISTS] da...
复制链接

扫一扫