首页 > 基础资料博客日记

MYSQL-10.创建和管理表

2024-01-06 18:10:43基础资料围观245次

Java资料网推荐MYSQL-10.创建和管理表这篇文章给大家，欢迎收藏Java资料网享受知识的乐趣

1.基础知识

1.1 一条数据存储的过程

存储数据是处理数据的第一步。只有正确的把数据存储，才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

那么，怎样才能把用户各种经营相关的，纷繁复杂的数据，有序，高效的存储起来呢？在MySQL中，一个完整的数据存储过程总共有4步，分别是创建数据库，确认字段，创建数据表，插入数据。

为什么要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构上看，MySQL数据库系统从小到大依次是数据库服务器，数据库，数据表，数据表的行和列。

MySQL数据服务器之前已经安装。所以，从创建数据库开始。

1.2 标识符命名规则

数据库名，表名不得超过30个字符，变量名限制为28个。
必须只能包含A-Z，a-z，0-9，_共63个字符。
数据库名，表名，字段名等对象名中间不要包含空格。
同一个MySQL系统中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。
必须保证字段名没有和保留字，数据库系统常用方法名冲突。如果坚持使用，请在SQL语句中使用`着重号引起来。
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，加入数据类型在一个表里是整数，别在另一个表里就变成了字符类型了。因为在进行表的联合查询时，会使用冗余字段，进行过滤笛卡尔集的操作，字段不一致会导致错误。

1.3 MySQL中的数据类型

其中，常用的几种类型介绍如下

2.创建和管理数据库

2.1 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;
-- OR
CREATE DATABASE 数据库名 CHARSET 字符集;
-- 简写方式 注意，MYSQL中使用UTF8表示字符集不能有'-'。

方式3：判断数据库是否已经存在，不存在则创建数据库(推荐)

CREATE DATABASE IF NO EXISTS 数据库名;

如果MySQL中已经存在了相同的数据库名的数据库，则忽略此创建语句，不再创建数据库。

注意，DATABASE不能改名，一些可视化工具可以改名，它是创建新库，把所有的表和数据复制到新库，再删除旧库完成的。

2.2 使用数据库

查看当前DBMS下的所有数据库

SHOW DATABASES; -- 有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个MYSQL中的全局函数

查看指定库下的所有的表

SHOW TABLES FROM 数据库名;-- 数据库名不用加''

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
-- OR
SHOW CREATE DATABASE 数据库名\G -- 该指令在可视化工具中可能会执行错误Unknown database 'atguigudbg'把\当成转义符了

使用/切换数据库

USE 数据库名;

注意，在操作表格和数据之前必须先说明是对那个数据库进行操作，否则就要对所有对象加上数据库名.。

2.3 修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;-- 例gbk，utf8等

2.4 删除数据库

方式1：删除指定数据库

DROP DATABASE 数据库名;

方式2：删除指定且存在的数据库(推荐)

DROP DATABASE IF EXISTS 数据库名;

3.创建表

3.1 创建方式1

前提

当前用户，必须具备CREATE TABLE权限和足够的存储空间

语法格式

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1,数据类型 [约束条件] [默认值],
    字段2,数据类型 [约束条件] [默认值],
	字段2,数据类型 [约束条件] [默认值],
    ...
);

加上了 IF NOT EXISTS关键字，则表示：如果当前数据库不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略该建表语句。

注意

必须指定：表名，列名，数据类型，长度(对于可变字符类型必须指定)。

可选指定：约束条件，默认值

举例

CREATE TABLE emp(
	-- int类型
    emp_id INT,
    -- 最多保存20个中英文字符
    emp_name varchar(20),
    -- 总位数不超过15位
    salary DOUBLE,
    -- 日期类型
    birthday DATE
);

DESC emp;

CREATE TABLE dept(
	-- int类型 自增
    deptno INT(2) AUTO_INCREMENT,
    dname varchar(14),
    loc varchar(12),
    
    -- 主键
    PRIMARY KEY(deptno)
)

DESCRIBE dept;

在MySQL8.x版本中，不再推荐INT类型指定显示长度，并在未来版本中可能去掉这样的语法。

3.2 创建方式2

使用AS subquery选项，将创建表和插入数据结合起来

CREATE TABLE tabel_name[ (column,column...)]
AS subquery;

指定的列和子查询中的列要一一对应
通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees;-- 创建一张表结构和employees相同的表，并且数据也一样

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的表是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

DESC dept80;

3.3 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 数据库名\G-- 注意在可视化工具中可能会报错，在CMD窗口，可正常执行
SHOW CREATE TABLE 数据库名; -- 可以正常执行

mysql> SHOW CREATE TABLE dept80\G
*************************** 1. row ***************************
       Table: dept80
Create Table: CREATE TABLE `dept80` (
  `employee_id` int NOT NULL DEFAULT '0',
  `last_name` varchar(25) NOT NULL,
  `ANNSAL` double(22,2) DEFAULT NULL,
  `hire_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

使用SHOW CREATE TABLE 语句，不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码

4.修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用ALTER TABLE 语句可以实现：

向已有的表中添加列
修改现有表的列
删除现有表的列
重命名现有表的列

4.1 追加一个列

语法格式

ALTER table 表名
ADD [COLUMN] 字段名 字段类型 [FIRST | AFTER 字段名];
-- FIREST指定新增加的列，在第一位
-- AFTER 字段名 指定新增加的列，在指定的字段后

举例

ALTER TABLE dept80
ADD job_id varchar(20);

4.2 修改列

可以修改列的数据长度，长度，默认值和位置

语法格式

ALTER TABLE 表名
MODIFY [column] 字段名1 字段类型 [DEFAULT 默认值] [FIRST | AFTER 字段名2];

注意 mysql中不支持before指定字段的位置信息

举例

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

对于默认值的修改只影响语句执行后的表
此外，还可以通过此种方式修改列的约束。这里暂先不讲。

4.3 重命名一个列

使用CHANGE old_column_name,new_column_name dataType子句重命名列。

语法格式

ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型;

举例

ALTER TABLE dept80
ChANGE ANNSAL annsal double(22,2);

4.4 删除一个列

语法格式

ALTER TABLE 表名
DROP [COLUMN] 列名;

举例

ALTER TABLE dept80
DROP COLUMN job_id;

5.重命名表

方式一：使用RENAME

RENAME TABLE emp to myemp;

方式二：

ALTER TABLE dept
RENAME [TO] detail_dept; -- []中的内容可以省略

必须有当前表的修改权限

6.删除表

在MYSQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前表直接删除。
数据和结构都被删除
当前表所有正在运行的事务都被提交
表的所有索引被删除
语法格式

DROP TABLE [IF EXISTS] 数据表1 [,数据表2,...];

IF EXISTS 的含义为，如果当前数据库中存在相应的数据表，则删除数据表，如果当前数据库中不存在相应的数据表，则忽略删除语句，不在执行删除数据表的操作。

举例

DROP TABLE dept80;

DROP 语句不能回滚

7.清空表

TRUNCATE TABLE语句：
- 删除表中的所有数据
- 释放表的存储空间
举例

TRUNCATE TABLE dept80;

TRUNCATE 语句不能回滚，使用DELETE语句删除数据，可以回滚
对比

SET autocommit = FALSE;

DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;

ROLLBACK;

SELECT * FROM emp2;

8.内容扩展

扩展1：阿里巴巴<<Java开发手册>>之MySQL字段命名

扩展2：如何理解清空表，删除表等操作需谨慎？

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行备份 ，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用ALTER TABLE进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

扩展3：MySQL8新特性-DDL的原子化

在MySQL8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log(该表是隐藏的表，通过show tables无法看到)中，用户回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);

SHOW TABLES;

(1) 在MySQL 5.7版本中，测试步骤如下：删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> SHOW TABLES;
Empty set (0.00 sec)

从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。

(2) 在MySQL 8.0版本中，测试步骤如下：删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

从结果可以看出，数据表book1并没有被删除。

只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

文章来源:https://www.cnblogs.com/changming06/p/17949296
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！

标签：

上一篇：7、SpringBoot2之整合事务及AOP
下一篇：麻了，这让人绝望的大事务提交

首页 > 基础资料 博客日记

MYSQL-10.创建和管理表

1.基础知识

1.1 一条数据存储的过程

1.2 标识符命名规则

1.3 MySQL中的数据类型

2.创建和管理数据库

2.1 创建数据库

2.2 使用数据库

2.3 修改数据库

2.4 删除数据库

3.创建表

3.1 创建方式1

前提

语法格式

注意

举例

3.2 创建方式2

3.3 查看数据表结构

4.修改表

4.1 追加一个列

语法格式

举例

4.2 修改列

语法格式

举例

4.3 重命名一个列

语法格式

举例

4.4 删除一个列

语法格式

举例

5.重命名表

6.删除表

7.清空表

8.内容扩展

扩展1：阿里巴巴<<Java开发手册>>之MySQL字段命名

扩展2：如何理解清空表，删除表等操作需谨慎？

扩展3：MySQL8新特性-DDL的原子化

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料博客日记