当前位置：Gxlcms > 数据库问题 > MySql三大范式与数据库设计和表创建常用语句

MySql三大范式与数据库设计和表创建常用语句

时间：2021-07-01 10:21:17 帮助过：22人阅读

1.第一范式(1NF First Normal Fromate)：数据表中的每一列(字段)，必须是不可拆分的最小单元。也就是确保每一列的原子性。
例如： userInfo: ‘山东省烟台市 13181621008‘
=> userAds:‘山东省烟台市‘
tel:‘13181621008‘

2.第二范式(2NF):满足1NF后，要求：表中所有的列，都必须功能依赖于主键，而不能有任何一列与主键没有关系。（一张表值描述一件事情）

3.第三范式(3NF):满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关。（表中的每一列，只能依赖于主键）

例如：订单表中，需要有客户相关信息，在分离出客户表之后。订单表中，只需要有一个用户ID
即可。而不能有其他的客户信息。因为，其他的用户信息是直接关联于用户ID。而不是关联于订单ID。

【第二范式与第三范式的本质区别】
在于有没有分出两张表，第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表。第三范式要求，已经分好了多张表的话，那么，一张表中只能有另一张表中的ID（主键），而不能有其他的信息（其他的信息，一律使用主键在另一种查询）。

*/

USE mydb;

-- 创建表：
定义列：列名 数据类型 列定义关键字
常用的列定义关键字：
UNSIGNED:设置列为无符号列。只能设置类型为数字类型的列
AUTO_INCREMENT ：设置自动增长列。 自动增长列必须是主键。


【主键】
1.主键的注意事项？ ① 主键默认非空！ ② 只有主键才能设置自动增长（主键不一定自动增长，自动增长必须是主键）
2.设置主键的方式？ ① 在列定义时设置：id INT PRIMARY KEY
② 在列定义完成后设置： PRIMARY KEY(id)
UNIQUE:设置唯一性约束。该字段不能出现重复值。
NOT NULL:设置非空约束。该字段不能为空。
DEFAULT:设置默认值约束。hight DOUBLE(3,2) DEFAULT(1.2) height如果不输入默认1.2
FOREIGN KEY:设置外键约束。
【外键】
1、设置外键有哪些注意事项？
1、设置外键有哪些注意事项？
只有innoDB的数据库引擎支持外键。
修改my.ini文件设置default-storage-engine=INNODB
②外键与参照列的数据类型必须相同
③设置外键的字段必须要有索引。如果没有索引，设置外键时会自动生成一个索引

[数值型要求长度和无符号都相同，字符串要求类型相同，长度可以不同]

2、设置外键的语法？
[CONSTRAINT 外键名] FOREIGN KEY(外键字段)REFERENCES 参照表（参照字段）
[ON DELETE SET NULL ON UPDATE CASCADE] -- 设置参照完整性

3、外键约束的参照操作。
参照操作：当对参照表的参照字段进行删除或更新时，外键表中的外键如何应对。
参照操作可选值：DESTRTCT 拒绝参照表删除或更新参照字段
NO ACTION 与 RESTRICT相同，但这个指令只在MySQL生效
CASCADE 删除或更新参照表的参照字段时，外键表的记录同步删除更新
SET NULL 删除或更新参照表的参照字段时，外键表的外键设为NULL
*/

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> tb1( 
  2. </span><span style="color: #008080">--</span><span style="color: #008080"> IF NOT EXISTS 可以省略，省略后面重复创建报错。如果不省略，则创建时表是否已存在，如果表存在则不再执行创建</span>
  3. id <span style="color: #0000ff">INT</span> UNSIGNED <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> AUTO_INCREMENT <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  4. `name` </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">255</span>), <span style="color: #008080">--</span><span style="color: #008080"> name 是系统关键字，所以使用反引号``包裹</span>
  5. age <span style="color: #0000ff">SMALLINT</span> <span style="color: #0000ff">UNIQUE</span><span style="color: #000000">,
  6. hight </span><span style="color: #0000ff">DOUBLE</span>(<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #0000ff">DEFAULT</span>(<span style="color: #800000; font-weight: bold">1.2</span><span style="color: #000000">)
  7. </span><span style="color: #008080">--</span><span style="color: #008080"> PRIMARY KEY(id)</span>
  8. <span style="color: #000000">
  9. );
  10. </span><span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> CLASSES; 
  11. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> CLASSES(
  12. id </span><span style="color: #0000ff">INT</span> UNSIGNED <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> AUTO_INCREMENT,
  13. CLASSNAME </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  14. );
  15. </span><span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">EXISTS</span> `<span style="color: #ff00ff">USER</span><span style="color: #000000">`;
  16. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span> `<span style="color: #ff00ff">USER</span><span style="color: #000000">`(
  17. id </span><span style="color: #0000ff">INT</span> UNSIGNED <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> AUTO_INCREMENT,
  18. CLSID </span><span style="color: #0000ff">INT</span> UNSIGNED <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  19. `NAME` </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  20. </span><span style="color: #0000ff">CONSTRAINT</span> user_fk_classes <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (clsid) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> classes(id)
  21. );
  22. SHOW COLUMNS </span><span style="color: #0000ff">FROM</span> CLASSES;

 

 

  1. SHOW TABLES;

-- 显示表结构

  1. SHOW COLUMNS <span style="color: #0000ff">FROM</span> tb1;

-- 显示表的建表语句

  1. SHOW <span style="color: #0000ff">CREATE</span> TABLES TB1;

-- 删除表

  1. <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">EXISTS</span> tb1;

-- 修改表名 ALTER TABLE 旧表名 RENAME [TO] 新表名;

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 RENAME TB2;

-- 同时修改多表名 RENAME TABLE TB3 TO TB1[,`USER` TO USER1 ……];

  1. RENAME <span style="color: #0000ff">TABLE</span> TB3 <span style="color: #0000ff">TO</span> TB1,`<span style="color: #ff00ff">USER</span>` <span style="color: #0000ff">TO</span> USER1;

-- 修改字段 列
-- ALTER TABLE 表名 CHANGE 旧列名 新列名 列定义 [FIRST | AFTER 某一列]
-- FIRST 将这个字段调整为表格第一列 AFTER 某一列：将这个字段放到某一列后面

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 CHANGE `NAME` `USERNAME` <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">200</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> AFTER AGE;

-- MODIFY 只修改定义，不能改名

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 MODIFY `USERNAME` <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">200</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> AFTER AGE;

-- 删除表中某一列

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">DROP</span> HEIGHT;

-- 新增一列 必须部分：ALTER TABLE tb1 ADD HEIGHT DOUBLE(8,2)

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tb1 <span style="color: #0000ff">ADD</span> HEIGHT <span style="color: #0000ff">DOUBLE</span>(<span style="color: #800000; font-weight: bold">8</span>,<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #800000; font-weight: bold">1.2</span> AFTER AGE;

-- 新增多列 不能调整列的位置，只能插在最后。

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">ADD</span><span style="color: #000000">(
  2. WEIGHT </span><span style="color: #0000ff">DOUBLE</span>(<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">) UNSIGNED,
  3. school </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">255</span><span style="color: #000000">)
  4. )</span>

-- 增加主键约束

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tb1 <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>(id);

-- 删除主键约束

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tb1 <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>;

 

-- 新增唯一性约束

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">UNIQUE</span> <span style="color: #0000ff">KEY</span> (USERNAME);

-- 删除唯一性约束：由于创建唯一性约束会默认创建索引，所以删除时，需删除索引

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">INDEX</span> USERNAME;

 

-- 设置默认值约束

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">ALTER</span> AGE <span style="color: #0000ff">SET</span> <span style="color: #0000ff">DEFAULT</span> <span style="color: #800000; font-weight: bold">20</span>;

 

-- 删除默认值约束

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> TB1 <span style="color: #0000ff">ALTER</span> AGE <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">DEFAULT</span>;

 

-- 设置外键约束 必选部分：ALTER TABLE tb1 ADD FOREIGN KEY (CLSID) REFERENCES CLASSES(ID)

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tb1 <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (CLSID) <span style="color: #0000ff">REFERENCES</span> CLASSES(ID) <span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">SET</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">ON</span> <span style="color: #0000ff">UPDATE</span> <span style="color: #0000ff">CASCADE</span>;

 

-- 删除外键约束。由于创建外键时会默认

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tb1 <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> TB1_FK_CLASSES;
  2. SHOW COLUMNS </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> TB1;
  3. SHOW TABLES;</span>

 

MySql三大范式与数据库设计和表创建常用语句

标签：数据库设计   位置   set   本质   查询   style   默认值   update   反引号   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！