【MySQL】创建数据库和表

1. 创建和删除数据库

1.1 create database

• 创建新数据库，一个没有任何表的空数据库。
• 例如：create database if not exists sql_store2；

1.2 drop database

• 删除数据库。
• 例如：drop database if exists sql_store2;

2. 创建表：create table

2.1 列属性

• primary key：主键

• auto_increment：自动递增

• not null：非空

• default 0：默认值

• unique：唯一值

  create table if not exists customers (
      customer_id int primary key auto_increment,
      first_name varchar(50) not null,
      points int not null default 0,
      email varchar(255) not null unique
  );
  

3. 更改表：alter table

• 不要在生产环境下更改表，这样会造成严重后果。在测试数据库上尝试更改，确保执行正常且不会产生任何不良影响后，再在生产数据库中更改。

• add：添加列，after可选择添加列的位置，如果不写就默认加在最后一列

• modify：修改列，可修改列数据类型、属性

• drop：删除列

  alter table customers
      add last_name varchar(50) not null after first_name,
      add city varchar(50) not null ,
      modify first_name varchar(55) default '',
      drop points;
  

4. 创建关系

• foreign key 外键取名 (要键入外键的列名)：键入外键
  • 外键取名：fk_子表_父表
• references 父表名（列名）：引用顾客表中的顾客id列
• on update cascade：指定更新行为
• on delete no action：指定删除行为
• 注意：删除表的语句顺序
  • 此时顾客表是关系的一部分，此情况下，如果要删除顾客表，需要先删除订单表。因此删除订单表的语句要写在前面。

  create database if not exists sql_store2;
  use sql_store2;
  
  drop table if exists orders; -- 删除订单表语句在前。
  drop table if exists customers;
  
  create table if not exists customers (
      customer_id int primary key auto_increment,
      first_name varchar(50) not null,
      points int not null default 0,
      email varchar(255) not null unique
  );
  
  create table if not exists orders(
      order_id int primary key,
      customer_id int not null ,
      foreign key fk_orders_customers (customer_id)
          references customers(customer_id)
          on update cascade
          on delete restrict
  );
  

5. 更改主键和外键约束

• 添加主键：add primary key (列名1，列名2，列名3)
• 删除主键：drop primary key。删除主键时不用打列名称。
• 删除外键：drop foreign key 外键名。
• 添加外键：add foreign key 外键名 （列名）。同4.创建关系。

alter table orders
    add primary key (order_id),
    drop primary key,
    drop foreign key orders_ibfk_1,
    add foreign key fk_orders_customers (customer_id)
        references customers(customer_id)
        on update cascade
        on delete no action

6. 字符集和排序规则

6.1 字符集

• 字符集：当存储一个字符串，比如ABC的时候，MySQL会使用字符集将每个字符转换为数值表示，因此字符集是将每个字符映射到数字的表。

  • show charset：查询当前版本MySQL支持的所有字符集
    

  • utf-8：是MySQL版本5以上使用的默认字符集，可以存储几乎所有国际语言。多数情况下不需要考虑更改字符集。

• 某些情况下更改字符集来减少数据库的大小。
  • Maxlen列：表示存储每个字符时最多保留的字节长度。英语占1字节，西欧和中东占2字节，亚洲语言占3字节。
  • 例如，表中某列的类型是char(10)，不管此列使用1个还是10个字符，都会保留10个字符串的空间。
  • 如果使用utf-8，MySQL为每个字符预留的最大字节数是3，此时会为此列的每个单元格中预留10*3个字节。
  • 如果只需要支持拉丁语，此时就可以用latin1的字符集，此字符集为每个字符预留的最大字节数是1，此时回味此列的每个单元格中预留10*1个字节空间。
  • 通过更改字符集减少MySQL占用磁盘的字节数。

6.2 排序规则

• 排序规则决定了某类语言的字符如何排序，例如utf16字符集的默认排序规则是utf16_general_ci
• ci：case-insensitive不区分大小写，排序时给予大小写同等优先级。
  

6.2 更改库、表或特定列的字符集

• character set 字符集：设置字符集

• 库的字符集

  -- 创建库时设置字符集
  create database db_name
      character set latin1
  -- 更改数据库的字符集
  alter database db_name
      character set latin1
  

• 表的字符集

  -- 创建表时设置字符集
  create table customers(id int, name varchar(50), phone varchar(255))
      character set latin1
  -- 更改表的字符集
  alter table customers
  	character set latin1
  

• 特定列的字符集：如想为名字列设置字符集，在数据类型后设置字符集。

  create table if not exists customers (
      first_name varchar(50) character set latin1 not null
  );
  

7.存储引擎

• 存储引擎决定了数据如何被存储，及哪些功能可供使用。

• show engines：查看当前版本MySQL支持的所有存储引擎。
  

• 常用的存储引擎：

  • MyISAM：旧的存储引擎，在版本5.5之前流行
  • InnoDB：更高级的存储引擎，支持事务等功能。

7.1 更改表的存储引擎

• 更改一张表的存储引擎会花费较多时间，因为MySQL必须重建这张表。在此期间无法访问此表，因此不要在产出阶段更改存储引擎，除非要定期维护。

  alter table customers
      engine = InnoDB