MySQL学习笔记:十、创建和管理表

技术MySQL学习笔记:十、创建和管理表 MySQL学习笔记:十、创建和管理表1. 基础知识
1.1 一条数据存储的过程
存储数据是处理数据的第一步。只有正确地把数据存储起来,我们才能进行有效的处理和分

MySQL研究笔记:十、创建和管理表

1. 基础知识

1.1 一条数据存储的过程

存储数据是数据处理的第一步。只有正确地存储数据,我们才能有效地处理和分析数据。否则只能是一塌糊涂,无从下手。

那么,如何才能有序高效地存储用户的各种与业务相关的复杂数据呢?在MySQL中,一个完整的数据存储过程包括四个步骤,即创建数据库、确认字段、创建数据表和插入数据。

我们应该先创建数据库,而不是直接创建数据表吗?

因为从系统架构的层面来说,MySQL数据库系统是数据库服务器,数据库,数据表,数据表的行和列按从大到小的顺序排列。

之前已经安装了MySQL数据库服务器。所以,让我们从创建数据库开始。

1.2 标识符命名规则

数据库名和表名不能超过30个字符,变量名限制为29个字符。

必须仅包含63个字符的AZ、AZ、09和_

不要在数据库名、表名和字段名等对象名中间包含空格。

在同一个MySQL软件中,数据库不能同名;同一库中的表不能同名;同一表中的字段不能同名。

确保您的字段不与保留字、数据库系统或常用方法冲突。如果你坚持使用,请在SQL语句中使用`(项目符号)。

维护字段名和类型的一致性:在给字段命名和分配数据类型时,我们必须确保一致性。如果一个表中的数据类型是整数,不要在另一个表中将其更改为字符类型。

1.3 MySQL中的数据类型

类型

类型示例

整数类型

TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT

浮点类型

浮动、双重

定点类型

DECIMAL

位类型

少量

日期和时间类型

年份、时间、DATE、日期时间、时间戳

文本字符串类型

CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

枚举类型

ENUM

集合类型

设置

二进制字符串类型

二进制、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

JSON类型

JSON对象,JSON数组

空间数据类型

单值:几何、点、线串、多边形;

集合:多点、多线、多多边形、几何集合

其中,几种常见类型介绍如下:

数据类型

形容

(同Internationalorganizations)国际组织

从-231到231-1的整数数据。存储大小为4字节

CHAR(大小)

固定字符数据。如果未指定,默认值为1个字符,最大长度为255。

VARCHAR(大小)

可变字符数据,根据字符串的实际长度保存,必须指定长度

浮动

单精度,占用4字节,M=整数小数位,D=小数位。D=M=255,0=D=30,默认M=6

双(男,女)

双精度,占用8字节,D=M=255,0=D=30,默认M D=15

十进制(M,D)

高度精密

小数,占用M+2个字节,D=M=65,0=D=30,最大取值范围与DOUBLE相同。DATE日期型数据,格式'YYYY-MM-DD'BLOB二进制形式的长文本数据,最大可达4GTEXT长文本数据,最大可达4G

2. 创建和管理数据库

2.1 创建数据库

  • 方式1:创建数据库

    CREATE DATABASE 数据库名; 
    
  • 方式2:创建数据库并指定字符集

    CREATE DATABASE 数据库名 CHARACTER SET 字符集;
    
  • 方式3:判断数据库是否已经存在,不存在则创建数据库(推荐

    CREATE DATABASE IF NOT EXISTS 数据库名; 
    

如果MySQL中已经存在相关的数据库,则忽略创建语句,不再创建数据库。

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

2.2 使用数据库

  • 查看当前所有的数据库

SHOW DATABASES; #有一个S,代表多个数据库


- 查看当前正在使用的数据库
    ```sql
    SELECT DATABASE();  #使用的一个 mysql 中的全局函数
  • 查看指定库下所有的表

    SHOW TABLES FROM 数据库名;
    
  • 查看数据库的创建信息

    SHOW CREATE DATABASE 数据库名;
    或者:
    SHOW CREATE DATABASE 数据库名\G
    
  • 使用/切换数据库

    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, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
    );
    

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

  • 必须指定:

    • 表名
    • 列名(或字段名),数据类型,长度
  • 可选指定:

    • 约束条件
    • 默认值
  • 创建表举例1:

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

MySQL在执行建表语句时,将id字段的类型设置为int(11),这里的11实际上是int类型指定的显示宽度,默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

  • 创建表举例2:

    CREATE TABLE dept(
    -- int类型,自增
    deptno INT(2) AUTO_INCREMENT,
    dname VARCHAR(14),
    loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (deptno)
    );
    
    DESCRIBE dept;
    

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

3.2 创建方式2

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

  • 指定的列和子查询中的列要一一对应

  • 通过列名和默认值定义列

    CREATE TABLE emp1 AS SELECT * FROM employees;
    CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
    
    CREATE TABLE dept80
    AS 
    SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
    FROM    employees
    WHERE   department_id = 80;
    
    DESCRIBE dept80;
    

3.3 查看数据表结构

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

语法格式如下:

SHOW CREATE TABLE 表名\G

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

4. 修改表

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

使用 ALTER TABLE 语句可以实现:

  • 向已有的表中添加列

  • 修改现有表中的列

  • 删除现有表中的列

  • 重命名现有表中的列

4.1 追加一个列

语法格式如下:

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例:

ALTER TABLE dept80 
ADD job_id varchar(15);

4.2 修改一个列

  • 可以修改列的数据类型,长度、默认值和位置

  • 修改字段数据类型、长度、默认值、位置的语法格式如下:

    ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
    
  • 举例:

    ALTER TABLE	dept80
    MODIFY last_name VARCHAR(30);
    
    ALTER TABLE	dept80
    MODIFY salary double(9,2) default 1000;
    
  • 对默认值的修改只影响今后对表的修改

  • 此外,还可以通过此种方式修改列的约束。这里暂先不讲。

4.3 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下:

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例:

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

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;  -- [TO]可以省略
    
  • 必须是对象的拥有者

6. 删除表

  • 在MySQL中,当一张数据表没有与其他任何数据表形成关联关系时,可以将当前数据表直接删除。

  • 数据和结构都被删除

  • 所有正在运行的相关事务被提交

  • 所有相关索引被删除

  • 语法格式:

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

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

  • 举例:

    DROP TABLE dept80;
    
  • DROP TABLE 语句不能回滚

7. 清空表

  • TRUNCATE TABLE语句:

    • 删除表中所有的数据
    • 释放表的存储空间
  • 举例:

    TRUNCATE TABLE detail_dept;
    
  • TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚

  • 对比:

    SET autocommit = FALSE;
    DELETE FROM emp2; 
    #TRUNCATE TABLE emp2;
    SELECT * FROM emp2;
    ROLLBACK;
    SELECT * FROM emp2;
    

    阿里开发规范:

    【参考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。

    说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

8. 内容拓展

拓展1:阿里巴巴《Java开发手册》之MySQL字段命名

  • 强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。

    • 正例:aliyun_admin,rdc_config,level3_name
    • 反例:AliyunAdmin,rdcConfig,level_3_name
  • 强制】禁用保留字,如 desc、range、match、delayed 等,请参考 MySQL 官方保留字。

  • 强制】表必备三字段:id, gmt_create, gmt_modified。

    • 说明:其中 id 必为主键,类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型,前者现在时表示主动式创建,后者过去分词表示被动式更新
  • 推荐】表的命名最好是遵循 “业务名称_表的作用”。

    • 正例:alipay_task 、 force_project、 trade_config
  • 推荐】库名与应用名称尽量一致。

  • 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。

    • 正例:无符号值可以避免误存负数,且扩大了表示范围。

拓展2:如何理解清空表、删除表等操作需谨慎!

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

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

拓展3:MySQL8新特性—DDL的原子化

在MySQL 8.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.230890.com/zhan/146465.html

(0)

相关推荐

  • Python面向对象编程的核心概念知识点是什么

    技术Python面向对象编程的核心概念知识点是什么这篇文章给大家介绍Python面向对象编程的核心概念知识点是什么,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。面向对象编程的核心概念:封装,抽象,多

    攻略 2021年11月23日
  • 安装rac时需要注意的问题有哪些

    技术安装rac时需要注意的问题有哪些这篇文章将为大家详细讲解有关安装rac时需要注意的问题有哪些,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。安装rac时需要注意的问题1.虚拟机添加共

    攻略 2021年11月10日
  • go module是如何使用本地包的

    技术go module是如何使用本地包的小编给大家分享一下go module是如何使用本地包的,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!g

    攻略 2021年11月27日
  • 端口扫描工具AutoRecon怎么用

    技术端口扫描工具AutoRecon怎么用这篇文章给大家介绍端口扫描工具AutoRecon怎么用,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。今天给大家推荐一款端口扫描工具:AutoRecon一、环境

    攻略 2021年12月10日
  • MySQL 5.6中新增特性、不推荐使用的功能以及废弃的功能有哪些

    技术MySQL 5.6中新增特性、不推荐使用的功能以及废弃的功能有哪些这篇文章给大家分享的是有关MySQL 5.6中新增特性、不推荐使用的功能以及废弃的功能有哪些的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随

    攻略 2021年11月3日
  • 好英语怎么说,好的,谢谢。用英语怎么说

    技术好英语怎么说,好的,谢谢。用英语怎么说“好的”用英语表示有以下几个:good [ɡʊd]adj.合意的好英语怎么说,满意的;合格的;胜任的;善的;有德行的;让人高兴的;令人愉快的;令人满意的;彻底的n.善;正义;好处

    生活 2021年10月27日