MySQL | DDL语句

啵啵鱼
162 阅读9分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第18天，[点击查看活动详情]

四、DDL语句

DDL包括：create drop alter

（一）表的创建（建表）

1.语法格式

    create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

create table 表名(
  字段名1 数据类型, 
  字段名2 数据类型, 
  字段名3 数据类型
);

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。

字段名：见名知意。 表名和字段名都属于标识符。

2.mysql中的数据类型

很多数据类型，我们只需要掌握一些常见的数据类型即可。

（1）varchar(最长255)

可变长度的字符串 比较智能，节省空间。 会根据实际的数据长度动态分配空间。

优点：节省空间 缺点：需要动态分配空间，速度慢。

（2）char(最长255)

定长字符串 不管实际的数据长度是多少。 分配固定长度的空间去存储数据。 使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。 缺点：使用不当可能会导致空间的浪费。

  • varchar 和 char 我们应该怎么选择？ 性别字段你选什么？因为性别是固定长度的字符串，所以选择char。 姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

（3）int(最长11)

数字中的整数型。等同于java的int。

（4）bigint

数字中的长整型。等同于java中的long。

（5）float

​ 单精度浮点型数据

（6）double

​ 双精度浮点型数据

（7）date

​ 短日期类型

（8）datetime

​ 长日期类型

  • date和datetime两个类型的区别？ date是短日期：只包括年月日信息。 datetime是长日期：包括年月日时分秒信息。 mysql短日期默认格式：%Y-%m-%d mysql长日期默认格式：%Y-%m-%d %h:%i:%s

    （9）clob

​ 字符大对象 ​ 最多可以存储4G的字符串。 ​ 比如：存储一篇文章，存储一个说明。 ​ 超过255个字符的都要采用CLOB字符大对象来存储。 ​ Character Large OBject:CLOB

（10）blob

​ 二进制大对象 ​ Binary Large OBject ​ 专门用来存储图片、声音、视频等流媒体数据。 ​ 往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等， ​ 你需要使用IO流才行

3.创建一个学生表

学号、姓名、年龄、性别、邮箱地址

create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
  );

4.给字段设置默认值

create table t_student(
	no int,
	name varchar(32),
	sex char(1) default '男',  #使用default设置默认值
	age int(3),
	email varchar(255)
  );

5.快速创建表（了解）

原理：将一个查询结果当做一张表新建 这个可以完成表的快速复制 表创建出来，同时表中的数据也存在了

create table emp2 as select * from emp;	#as可写可不写

（二）表的删除

1.语法格式

drop table 表名;

2.注意事项

当这张表不存在的话会报错！

所以将删表格式改为：

drop table if exists 表名;

（三）对表结构的增删改

  • 什么是对表结构的修改？ 添加一个字段，删除一个字段，修改一个字段！！

第一：在实际的开发中，需求一旦确定后，表一旦设计好之后，很少的进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高的。这个责任应该由设计人员来承担！

第二：由于修改表结构的操作很少，所以我们不需要掌握，如果有一天真的要修改表结构，你可以使用工具！

修改表结构的操作是不许需要写到java程序中的。实际上也不是java程序员的范畴

  • 对表结构的修改需要使用：alter

修改表名

alter table goods2 rename to shop_db.goods2;

字段操作

alter添加字段在指定位置
ALTER TABLE goods add tax int(10) AFTER price;
alter删除字段
ALTER TABLE goods drop tax;
alter修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

alter table goods modify c varchar(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

alter table goods change tax tax1 bigint
alter修改数据库字符集
alter database 数据库名 character set utf8;
alter修改表字符集
ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
alter修改列字符集
ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

（四）约束（非常重要）

1.概述

  • 什么是约束？

    约束对应的英语单词： constraint 在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！

  • 约束的作用就是为了保证：表中的数据有效！

2.约束的分类

非空约束：not null 唯一性约束: unique 主键约束: primary key （简称PK） 外键约束：foreign key（简称FK） 检查约束：check（mysql不支持，oracle支持）

3.非空约束：not null

非空约束not null约束的字段不能为NULL

当你插入数据的时候，如果你插入的数据的字段是非空的，你就必须要插入这个字段的数据，否则的就会报错。

drop table if exists t_vip;
create table t_vip(
  id int,
  name varchar(255) not null  # not null只有列级约束，没有表级约束！
);

4.唯一性约束: unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

drop table if exists t_vip;
create table t_vip(
  id int,
  name varchar(255) unique,
  email varchar(255)
);
insert into t_vip(id) values(1);  # name字段虽然被unique约束了，但是可以为NULL
insert into t_vip(id) values(2);

在这里插入图片描述

  • 新需求：name和email两个字段联合起来具有唯一性 以下这样的数据是符合我“新需求”的：

    insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
insert into t_vip(id,name,email) values(2,'sada','zhangsan@sina.com');

    如何做到联合唯一约束?

    drop table if exists t_vip;
create table t_vip(
  id int,
  name varchar(255),
  email varchar(255),
  unique(name,email) 	# 约束没有添加在列的后面，这种约束被称为表级约束。
);

在这里插入图片描述

  • 什么时候使用表级约束呢？ 需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

  • unique和not null可以联合吗？

    在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。 在oracle中不是这样的

5.主键约束: primary key

简称：PK

  • 主键约束的相关术语？

    主键约束：就是一种约束。 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段 主键值：主键字段中的每一个值都叫做：主键值。

  • 什么是主键？有啥用？

    主键值是每一行记录的唯一标识。 主键值是每一行记录的身份证号！！！

  • 记住：任何一张表都应该有主键，没有主键，表无效！！

  • **主键的特征：not null + unique（**主键值不能是NULL，同时也不能重复！）

  • 怎么给一张表添加主键约束呢？

    drop table if exists t_vip;
 # 1个字段做主键，叫做：单一主键
create table t_vip(
    id int primary key,  #列级约束
    name varchar(255)
);

  • 可以这样添加主键吗，使用表级约束？

    drop table if exists t_vip;
create table t_vip(
    id int primary key,  #列级约束
    name varchar(255),
    primary key(id)
);

  • 表级约束主要是给多个字段联合起来添加约束

    drop table if exists t_vip;
  // id和name联合起来做主键：复合主键！！！！
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	primary key(id,name)
  );

    在实际开发中不建议使用：复合主键。建议使用单一主键！ 因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。 复合主键比较复杂，不建议使用！！！

  • 一张表只能添加一个主键

  • 主键值建议使用： int bigint char 等类型。

    不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

  • 主键除了：单一主键和复合主键之外，还可以这样进行分类： 自然主键：主键值是一个自然数，和业务没关系。 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

  • 在实际开发中使用业务主键多，还是使用自然主键多一些？ 自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。 业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候， 可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

  • 在mysql当中，有一种机制，可以帮助我们自动维护一个主键值

    drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
	name varchar(255)
);

6.外键约束：foreign key

  • 外键约束涉及到的相关术语： 外键约束：一种约束（ foreign key） 外键字段：该字段上添加了外键约束 外键值：外键字段当中的每一个值。

  • 外键约束的作用

    但第一张表的字段没有任何约束的时候，可能会导致数据无效，原本cno只能取100,101但可能出现一个102，所以为了保证cno字段都是第二张表中的cno的100和101，需要给cno字段添加外键约束 那么cno字段就是外键字段，cno字段中的每一个值都是外键值

  • 当使用了外键约束的时候两张表就有了父子关系，被引用的是父表，引用的是子表

  • 删除的顺序：先删子表再删父表

  • 创建表的顺序：先创建父表，再创建子表

  • 删除数据的顺序：先删子，再删父

  • 子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

    不一定是主键，但至少具有unique约束

  • 测试：外键可以为NULL吗？

  • 外键值可以为NULL。

avatar
文章
阅读
粉丝