DLL:操作数据库和表

1. 数据库操作

C(Create 创建) R(Retrieve 查询) U(Update 更新) D(Delete 删除)

  1. 查询数据库
    1. 查询所有数据库名称
      这里有一个注意的东西,一个Database对应数据目录中的一个实体文件夹,但是 information_schema 这个数据库却没有实体对应。它的用途是作为视图使用,描述数据库的表的信息、库的名称等等。
      SHOW DATABASES;
      
    2. 查询创建数据库的创建语句和字符集
      虽然已经修改了数据库的默认编码方式为utf8mb4,但是已创建的数据库还是utf8。而这个mysql数据库则是Latin1,虽然也支持中文,但是最好还是改为utf8mb4。
      SHOW CREATE DATABASE 数据库名;
      
  2. 创建数据库
    1. 创建一个数据库
      CREATE DATABASE 数据库名;
      
    2. 带判断语句的创建数据库
      CREATE DATABASE IF NOT EXISTS 数据库名;
      -- 如果不存在数据库则创建,如果存在则不创建,不过是否创建了数据库都会返回Query OK
      
    3. 创建指定字符集的数据库
      CREATE DATABASE 数据库名 CHARACTER SET 字符集;
      
  3. 修改数据库
    1. 修改数据库字符集
      这里注意一下,修改字符集的时候最好要修改一下排序规则。而utf8mb4中有两种排序规则,一个是 utf8mb4_general_ci 一个是 utf8mb4_unicode_ci。因为utf8mb4_unicode_ci 的排序更精确一些,所以推荐使用 utf8mb4_unicode_ci 排序方式。
      ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则;
      
      =
  4. 删除数据库
    1. 直接删除(非常危险,尽量不要使用)
      DROP DATABASE 数据库名;
      
    2. 带判断的删除(以防引起错误)
      DROP DATABASE IF EXISTS 数据库名;
      
  5. 使用数据库
    1. 查询当前使用中的数据库
      SELECT DATABASE();
      
    2. 进入数据库
      USE 数据库名();
      

2. 表操作

  1. 查询表
    1. 查询数据库中有哪些表
      SHOW TABLES;
      
    2. 查询表结构
      DESC 表名;
      
    3. 查询表的字符集
      SHOW CREATE TABLE 表名;
      
  2. 创建表
    1. 创建基础表
      CREATE TABLE 表名(
        列名 数据类型,
        列名 数据类型
      );
      
    2. 创建一个已有表结构相同的表
      CREATE TABLE 新表名 LIKE 旧表名;
      
  3. 删除表
    1. 直接删除
      DROP TABLE 表名;
      
    2. 带判断的删除
      DROP TABLE IF EXISTS 表名;
      
  4. 修改表
    1. 修改表名
      ALTER TABLE 表名 RENAME TO 新表名;
      
    2. 修改表字符集
      ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集 COLLATE 排序规则; 
      
    3. 添加列
      ALTER TABLE 表名 ADD 列名 数据类型;
      
    4. 修改列名和数据类型
      ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
      
    5. 修改列的数据类型
      ALTER TABLE 表名 MODIFY 列名 新数据类型;
      
    6. 删除列
      ALTER TABLE 表名 DROP 列名;
      

3. 约束

约束是对表进行限定,保证数据的正确性、完整性和有效性。

  1. 非空约束(NOT NULL)
    保证列数据增、删、改时不能为NULL空值。
    CREATE TABLE 表名(列名 数据类型 NOT NULL,列名 数据类型 NOT NULL);-- 建表时限定
    ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL; -- 现有表修改列为非空
    ALTER TABLE 表名 MODIFY 列名 数据类型; -- 删除非空限定
    
  2. 唯一约束(UNIQUE)
    保证一列中的每个数据都是唯一的,不可重复。
    注意:唯一约束不能保证NULL值唯一;现有表设置唯一约束列时,列中不可有重复数据。
    CREATE TABLE 表名(列名 数据类型 NOT NULL,列名 数据类型 UNIQUE);-- 建表时添加唯一约束
    ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL; -- 现有表修改列为唯一约束
    ALTER TABLE 表名 DROP INDEX 列名; -- 删除列的索引,就删除了唯一约束
    
  3. 主键约束(PRIMARY KEY)
    限定了列的数据非空且唯一;一张表中只能有一个列是主键,是表中的唯一标识。
    注意:现有表设置主键约束列时,列中不可有重复数据。
    CREATE TABLE 表名(列名 数据类型 NOT NULL,列名 数据类型 PRIMARY KEY); -- 建表时添加主键约束
    ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY; -- 现有表修改列为主键约束
    ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除表的主键,就删除了列的主键约束
    
  4. 自动增长(AUTO_INCREMENT)
    在某一数值类型列使用,可以完成值的自动增长,也可手动设值。一般配合主键使用。
    注意:自动增长是跟随上一条数据的数值进行自动增长。如果一条数据都没有,则会从 1 开始。
    CREATE TABLE 表名(列名 数据类型 PRIMARY KEY AUTO_INCREMENT); -- 建表时添加主键约束和自动增长
    ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT; -- 现有表修改为增加自动增长
    ALTER TABLE 表名 MODIFY 列名 数据类型; -- 删除列的自动增长,但不删除主键约束
    
  5. 外键约束(FOREIGN KEY)
    将两个表的产生关联关系,防止误操作,便于查询,保证数据有效、正确、完整。
    外键对应主表的主键,故外键应设置到从表上,而且需要先建立主表再建立从表。
    主键和外键的关系是一对多,比如主键是部门表编号,外键就是员工表中的部门编号。
    注意:根据现有表创建外键约束,需要满足约束要求,把从表的一列作为外键,和主键匹配。除NULL空值以外,如果外键有主键没有的数据,就无法创建或者修改。
    CREATE TABLE 从表名(外键 数据类型,CONSTRAINT 外键名 FOREIGN KEY (外键) REFERENCES 主表名(主表列名)); -- 创建表时创建关联
    ALTER TABLE 从表名 DROP FOREIGN KEY 外键名; -- 删除外键
    ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键) REFERENCES 主表名(主表列名); -- 现有从表创建关联
    
  6. 级联
    将两个通过外键约束的表,一次性修改两个表的主键和外键数据。
    级联更新是主键数据修改时,外键跟着修改;级联删除是主键数据删除,外键数据跟着删除。
    注意:级联虽然很方便,但是效率低、风险大,在大系统中使用这两种级联要慎重。
    -- 现有从表创建关联并且设置级联更新
    ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键) REFERENCES 主表名(主表列名) ON UPDATE CASCADE;
    -- 现有从表创建关联并且设置级联更新和级联删除
    ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键) REFERENCES 主表名(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
    
posted @ 2019-09-02 22:40  苍凉温暖  阅读(1690)  评论(0编辑  收藏  举报