MySQL数据库教程02

咖啡配辣条
94 阅读8分钟

DDL 常见操作

  1. 数据库常见操作

    • 创建数据库：使用CREATE DATABASE 数据库名;语句，例如CREATE DATABASE demo;。执行该 SQL 语句时，若出现异常会有相应提示，如CREATE DATABAS demo;会提示[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASs demo' at line 1。

    image.png

    • 使用数据库：use 数据库名；

    • 查询数据库：show databases;

    • 删除数据库：drop database 数据库名;

    • 修改数据库：alter database 数据库名 character set=utf8;

  2. 表的常见操作

    • 创建表：
CREATE table student[表名](
    s_id[字段名] INT[数据类型] 条件约束,
    s_name VARCHAR(10),
    s_sex CHAR(5),
    s_age VARCHAR(5)
)engin = innodb default character set=utf8;

  • 其中[ENGINE = INNODB | [DEFAULT] CHARACTER SET=utf8]部分，[]标识可以不用设置，|前后可以单独设置一个。数据库引擎是数据库的核心服务，提供数据操作、修改、查询及高级特性等功能。

  • 常见的数据类型包括：整数型（int、tinyint、smallint）；浮点型（float、double）；字符串（char、varchar、text）；时间日期（date、timestamp、datetime）。注意尽量选择合适的数据类型定义，避免存储空间浪费。char和varchar的区别在于，char（10）是固定长度字符串类型，varchar（10）是可变长度字符串类型，char类型执行效率更高。已知插入数据长度时选char，不确定字符长度时选varchar。

  • 查看表：SHOW TABLES;

  • 查看表结构：DESC student;

  • 复制表结构：CREATE TABLE 新表 LIKE 旧表;

  • 复制表数据：CREATE TABLE 新表名 AS(SELECT * from 旧表);，例如CREATE TABLE stu3 AS(SELECT s_name,s_sex from student);

  • 表格重命名：ALTER TABLE 旧表名 RENAME 新表名;

  • 表格新增列：ALTER TABLE 表名 ADD 字段名 数据类型;

  • 表格删除列：ALTER TABLE 表名 DROP 字段名;

  • 表格修改列名以及数据类型：alter TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;

  • 表格修改列的数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型;

  • 删除表：DROP TABLE 表名;

  1. 标识符命名规范

    • 首字符可以是：Unicode 标准 3.0 所定义的字母（包括拉丁字母 a - z 和 A - Z，以及其他语言字母字符）、下划线（_）、at 符号（@）或者数字符号（#）。
    • 后续字符可以是：Unicode 标准 3.0 所定义的字母；来自基本拉丁字母或其它国家 / 地区脚本的十进制数字；at 符号（@）、美元符号（$）、数字符号或下划线；标识符不能是所用 RDBMS 的保留字，如create、drop、insert、delete、update、alter、select、return、use、grant、revoke等；不允许嵌入空格或其它特殊字符。建议直接使用英文单词命名，多个单词用下划线连接，不使用数据库关键字。

  2. 数据的完整性约束：数据的完整性约束 = 数据的准确性 + 数据的可靠性。数据的准确性指插入数据库的数据必须真实有效；数据的可靠性指插入数据库的数据必须符合约束条件和通用规范标准。分类如下：

    • 域完整性：限制数据类型、外键约束、默认值、非空约束。
    • 实体完整性：唯一约束、主键约束、自增列。
    • 参照完整性：主外键关联。
    • 自定义完整性：规则、存储过程、触发器。目的是保证数据库中无无效脏数据，确保数据准确可靠，建表时需添加约束。

  3. 键

    • 超键：能够唯一标识实体的属性或属性集。

    • 候选键：从超键中去除多余的属性集，剩下的就是候选键。

    • 主键：从候选键中选择一个作为主键使用。

    image.png

  4. 主键约束（实体完整性）primery key：把表中能够唯一标识每一行实体的属性或属性集叫做主键。原则是一个表只能有一个主键，主键约束确保表中的行唯一；表中可以没有主键，但通常应设置一个。主键选择原则：最少性，尽量选择一个键作为主键；稳定性，尽量选择数值更新少的值作为主键。

image.png

*   **创建主键的方法**

    *   建表时添加主键方法 1：

CREATE TABLE student(
    s_id INT PRIMARY KEY,
    s_name VARCHAR(50),
    s_sex CHAR(2),
    s_phone CHAR(11)
)ENGINE = INNODB DEFAULT CHARACTER SET=utf8;
  • 建表时添加主键方法 2：
CREATE TABLE student(
    s_id INT,
    s_name VARCHAR(50),
    s_sex CHAR(2),
    s_phone CHAR(11),
    PRIMARY KEY(s_id)
)ENGINE = INNODB DEFAULT CHARACTER SET=utf8;
  • 联合主键：
CREATE TABLE student(
    s_id INT,
    s_name VARCHAR(50),
    s_sex CHAR(2),
    s_phone CHAR(11),
    PRIMARY KEY(s_id,s_name)
)ENGINE = INNODB DEFAULT CHARACTER SET=utf8;
  • 通过关键字constraint申明主键：
CREATE TABLE student(
    s_id INT,
    s_name VARCHAR(50),
    s_sex CHAR(2),
    s_phone CHAR(11),
    CONSTRAINT pk_id PRIMARY KEY(s_id)
)ENGINE = INNODB DEFAULT CHARACTER SET=utf8;

  • 建完表之后追加主键：ALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(s_id);

  • 删除主键：ALTER TABLE student DROP PRIMARY KEY;

  1. 外键约束（域完整性，参照完整性）foreign key() references：从表中的数据需从主表某一列获取时，需建立两张表的关联，将主表获取数据的列设为主键，从表关联数据的列设为外键。

    • 外键创建方法

      • 建表时添加外键：
CREATE TABLE score(
    s_id INT,
    s_sc FLOAT,
    s_corse VARCHAR(10),
    -- 添加外键
    FOREIGN KEY(s_id) REFERENCES student(s_id) [ON DELETE CASCADE]
)[ENGINE = INNODB DEFAULT CHARACTER SET=utf8];

  • 追加外键：ALTER TABLE score ADD CONSTRAINT fk_id FOREIGN KEY(s_id) REFERENCES student(s_id) [ON DELETE CASCADE ON UPDATE CASCADE];

  • 删除外键：ALTER TABLE score DROP FOREIGN KEY fk_id;

  • 指定外键更新或删除的行为：如果子表试图创建一个在父表中不存在的外键值，InnoDB 会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB 支持 5 种不同的动作，若未指定ON DELETE或者ON UPDATE，默认动作为RESTRICT。

    • CASCADE：从父表删除或更新对应行时，自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都被 InnoDB 支持。

    • SET NULL：从父表删除或更新对应行时，将子表外键列设为空。外键列未设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET NULL都被 InnoDB 支持。

    • NO ACTION：InnoDB 拒绝删除或更新父表。

    • RESTRICT：拒绝删除或更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项效果一样。

  1. 自增约束（实体完整性）auto_increment：必须跟主键一起使用，设自增必须设置主键，设置主键可不设置自增。设置自增约束后，该字段无需手动添加内容，会在原有数据基础上自增长。一张表只允许出现一个自增列。创建示例：
CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5),
    sex char(2)
);

9. 唯一约束（实体完整性）unique：

CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5) UNIQUE,
    sex char(2)
);

10. 非空约束（域完整性）not null：

CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5) NOT NULL,
    sex char(2)
);

11. 默认约束（域完整性）default：

CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5) DEFAULT '张三',
    sex char(2)
);

12. 检查约束（自定义完整性）set、enum

*   使用`set`（复选，多选）：

CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5) DEFAULT '张三',
    sex SET('男','女')
);
  • 使用enum（枚举类型，单选）：
CREATE TABLE demo(
    id INT auto_increment PRIMARY KEY,
    mynamem varchar(5) DEFAULT '张三',
    sex enum('男','女')
);

转存失败，建议直接上传图片文件

总结：上述约束可组合使用，自增约束必须与主键约束一起使用，一张表通常有一个主键约束，唯一约束不能与默认约束一起使用。
13. 三大范式

  • 范式：为建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定规则，在关系型数据库中这种规则称为范式。范式是符合某一种设计要求的总结，设计结构合理的关系型数据库必须满足一定范式。

  • 第一范式：保证列的原子性，即表中的每一列都是不可拆分的原子列。

  • 第二范式：保证行的唯一性。

  • 第三范式：保证表中的每一列都跟主键列直接相关，而非间接相关。与第二范式的区别是，第三范式是在第二范式基础上进一步拆分表格，将与主键间接相关的数据分离出来，强调表格的原子性。
    总结：数据库设计必须满足三大范式，也可在三大范式基础上满足其余范式，三大范式是基础，目的是设计出结构清晰、无冗余数据、便于维护管理的表格。

备注：来源于蜗牛学院的测试开发课程的学习笔记

avatar
文章
阅读
粉丝