【MySQL数据库基础二】DDL数据定义语句-阿里云开发者社区

数据库定义

数据库定义包括创建表、修改表以及删除表，当然更多的修饰在数据库的约束中提到。

创建表的语法

分为普通创建表方式和子查询创建表方式

普通建表方式

建表语法如下：

CREATE TABLE table_name (column_name column_type);

实际建表举例：

create table test
    （ 
        #整型通常用int                                               
      test_id int,                    //每个列定义之间以英文逗号隔开
      #小数点儿数
      test_price  decimal,
      #普通长度文本，使用default 指定默认值
      test_name  varchar(255) default 'XXX',
      #大文本类型
      test_desc  text,
      #图片
      test_img blob,
      test_date datetime
    ）;

需要指明更多属性的建表方案：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   //AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   //如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错
   `runoob_title` VARCHAR(100) NOT NULL,  
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   //PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;  //ENGINE 设置存储引擎，CHARSET 设置编码

子查询建表方式

子查询建表语法：

create table [模式名.] 表名    [column[,column…]]         //使用子查询语句建表，可以在建表的同时插入数据
as
subquery;

子查询建表实例，相当于将其它表的全部或部分数据拿来创建新表

#创建hehe数据表，该数据表和user_inf完全相同，数据也完全相同
create table hehe
as
select * from user_inf;   //以上方式为省略字段列表，新表的数据和字段与之前的完全相同

修改表的语法

包括修改表名以及删除、添加、修改表字段

修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO tml;

删除、添加、修改表字段

修改表的列、增加列、删除列、重命名列

//删除表testalter_tbl的teacher列，如果数据表中只剩余一个字段则无法使用DROP来删除字段
mysql> ALTER TABLE testalter_tbl  DROP teacher; 
//增加表testalter_tbl的student列，并且指明数据类型，执行以上命令后，i 字段会自动添加到数据表字段的末尾
//如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）
mysql> ALTER TABLE testalter_tbl ADD student INT;
//修改列的数据类型把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
//在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型
mysql> ALTER TABLE testalter_tbl CHANGE teacher student  BIGINT;

删除表的语法

删除指定表

drop table TML;    //该表结构被删除，对象被删除，所有数据被删除，所有相关索引，约束被删除
truncate table TML  //truncate的作用是截断某表，具体作用是删除表的数据而不删除表的结构

修饰主要有以下几种操作：加约束，索引和视图，定义的部分都结合到修饰里来说，这里主要说说修饰

数据库约束

约束通常被写到系统表里，依据约束范围可以分为：单列约束：每个约束只约束一列。多列约束：每个约束可以约束多个数据列。依据约束指定时机可以分为：建表开始的时候为对应数据列指定约束。建表后创建，以修改的方式来增加约束。约束通常只能被建立和删除不能修改。

NOT NULL:非空约束，指定某列不得为空。
UNIQUE：唯一约束，指定某列或者几列的组合不能重复。
PRIMARY KEY：主键：指定该列的值可以唯一的标识该记录。例如一个学生有很多属性，可以把学号作为主键，这样重名也没关系
FOREIGN KEY:外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

接下来分别就这几种约束进行介绍

非空约束 NOT NULL

所有数据库中的数据类型都可以为null，空字符串和0含义并不等于null； 只能作为列级约束来使用

建表时创建非空约束

creat table TML
  (
     TML_id int not null,       //id不能为空
     TML_name varchar(255) default 'xyz' not null,  //名字不能为空
     TML_gender  varchaer(2)   null     //gender是性别的意思                                             
  );

修改表时增加或删除非空约束

alter table TML 
modify TML_id  int null;    //可以为空
modify TML_name varchar(255)  default   'aaa'  null;

唯一约束UNIQUE

可以用于保证某列或者指定列组合不出现重复值，但可以出现多个null值。唯一约束既可以进行列级唯一约束，也可以进行表级唯一约束。如果只是为一列建立唯一约束那么只需要用列级，该唯一约束默认与列同名，并且会创建唯一索引。如果要为多列建立唯一约束或者是想自行制定约束名，那么需要使用表级唯一约束。

创建表时增加唯一约束

列级唯一约束

create table tml
（
    tml_id int not null,
      tml_name varchar(255) unique
  ）;

表级唯一约束

creat table tml
( 
  tml_id int not null,
  tml_name   varchar(255),
  tml_gender  varchar(255),
  unique (tml_name),               //使用表级约束语法建立唯一约束，李明这个名字在该姓名列必须只出现一次
  constraint  tml_nb  unique(tml_gender)   //使用表级唯一约束建立约束并且给该约束命名，两个属性各自都具有唯一性
)

指定两列的组合不允许出现重复，即两个属性不能完全相同（15，李明），（16，李明）也可以

creat table tml
(
  tml_id int not null,
  tml_name   varchar(255),
  tml_gender  varchar(255),
  constraint tml_verynb unique(tml_id,tml_name) 
)

修改表时增加唯一约束

修改表时用add创建一个表级唯一约束

alter table tml
add unique (tml_name,tml_id);

修改表时用modify创建一个列级唯一约束

alter table tml
modify tml_name varchar(255) unique;

删除唯一约束

alter table tml
drop index tml_nb;     //直接删除约束名

主键约束 PRIMARY KEY

主键约束相当于非空约束加唯一约束，主键约束的列既不能出现重复值，也不能出现null值，如果是多列组合的主键则要求每一列都不能有null值，但对于唯一性约束则只是要求其组合满足唯一约束条件。主键约束既可以用列级约束也可以用表级约束，但无论用户对约束做怎样的命名，主键约束名最终都是PRIMARY

建表时建立主键约束

建立列级主键约束

create table tml
(
  tml_id int primary key,
  tml_name varchar(255) 
);

建立表级主键约束

create table tml
(
  tml_id int not null,
  tml_name varchar(255),
  tml_gender  varchar(255),
  constraint tml_nb primary key(tml_id)  //指定主键约束名对大多数数据库有效，但对mysql无效
);

建立表级组合主键约束

create table tml
(
  tml_id int not null,
  tml_name varchar(255),
  tml_gender  varchar(255),
  primary key(tml_id,tml_name)//指定为主键约束后即使单个列没有非空约束也会自动具有非空约束
) ;

修改表时建立主键约束

建立列级主键约束

alter table tml
modify tml_id int primary key;

建立表级主键约束

alter table tml
add  primary key(tml_id,tml_name);

//删除主键约束
alter table tml
drop primary key;

设置自增长主键约束

自增长主键要求主键列数据类型必须为整型，通常用于设置逻辑主键列（没有任何物理信息，仅用于标识每行记录），向该表插入记录时，可以不为该列指定值，该主键列的值由数据库自动生成。

create table tml
（
  tml_id int auto_increament primary key,
  tml_name varchar(255),
  tml_pass varchar(255)
）;

外键约束FOREIGN KEY

外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系，主表是存放被参照对象的表，从表是存放外键列的表。

从表外键列的值必须在主表被参照列的值范围之内，或者为空。
外键约束通常定义于两个实体之间一对多（多的一端增加外键列），一个老师对多个学生，在学生实体上添加外键列
当主表记录被从表参照时，主表记录不允许被删除，必须先把从表里该主表外键列全部删除才可以，除非在删除主表时级联删除从表所有信息
一对一的关系，可以选择任意一方当作外键列
多对多的关系，需要额外增加一个连接表记录他们的关联关系
从表外键参照的只能是主表的主键列或者是唯一键列（索引）

以上就是外键约束的一些基本术语。

建表时建立外键约束

采用列级约束语法建立外键约束 ,为保证主表存在，必须先建立主表

create  table tml（主表）
(
  tml_id int auto_increment primary key,
  tml_name varchar(255) not null,
);
creat table student（从表）
(
  stu_id int auto_increment primary key,
  stu_name varchar(255),
  #指定java_tml 参照到tml_id列
  java_tml int references tml_table(tml_id)
);   //这种外键约束建立方法并没有什么用，仅仅是为了mysql保持兼容性而已

创建外键时如果没有指定名字，则默认名为table_name_ibfk_n,table_name 是从表的表名

create  table tml（主表）
(
  tml_id int auto_increment primary key,
  tml_name varchar(255) not null,
);
creat table student（从表）
(
  stu_id int auto_increment primary key,
  stu_name varchar(255),
  #指定java_tml 参照到tml_id列
  java_tml int ,
  foreign  key  (java_tml)  references  tml_table (tml_id) 
);

表级语法显式的指定外键约束的名字

create  table tml（主表）
(
  tml_id int auto_increment primary key,
  tml_name varchar(255) not null,
);
creat table student（从表）
(
  stu_id int auto_increment primary key,
  stu_name varchar(255),
  #指定java_tml 参照到tml_id列
  java_tml int ,
  constraint   student_tml_fk   foreign   key   (java_tml)   references   tml_table  (tml_id) //为外键约束指定名称
);

采用表级语法建立多列组合的外键

create  table tml（主表）
(
  tml_id int ,
  tml_name varchar(255) not null,
  primary key(tml_id，tml_name)  //首先在主表里建立联合主键
);
creat table student（从表）
(
  stu_id int auto_increment primary key,
  stu_name varchar(255),
  #指定java_tml 参照到tml_id列
  java_tml int ,
  java_tml_id   int ,
  constraint   student_tml_fk   foreign   key   (java_tml_name,java_tml_id)   references   tml_table  (tml_id,tml_id)
);

修改表时建立外键约束

不能使用列级，只能使用表级，你想啊，是添加了一个字段，约束给之前没有的字段往哪儿填去

alter table tml
add foreign key (java_tml_id,java_tml_name) references tml_table(tml_id,tml_name);

建立自关联的外键约束

例如员工分经理和普通员工一样

create table person
（
  person_id  int auto_increment primary key,
  person_name varchar(255),
  #本表的refer_id列参照到本表的person_id 列
  refer_id int,
  foreign  key ( refer_id) references  person_table(person_id)
）;

删除外键约束

只删除从表的外键约束

alter table tml
 drop foreign key  student_tml_fk:    //删除其约束名即可

定义删除主表时级联删除从表外键或将其外键设置为null

create  table tml（主表）
(
  teacher_id int ,
  tml_name varchar(255) not null,
  primary key(teacher_id )  
);
creat table student（从表）
(
  stu_id int auto_increment primary key,
  stu_name varchar(255),
  #指定java_tml 参照到tml_id列
  java_teacher int ,
   //定义级联删除，或者可以on delete set null把从表记录设置为空
  foreign key (java_teacher) references  teacher_table(teacher_id ) on delete cascade  
);

CHECK约束

通常来说check约束使用场景并不多

create table check
(
  check_id int auto_increment,
  check_name varchar(255),
  check_salary decimal,
  primary key (check_id),
  #建立check约束
  check(check_salary>0)
);

完整性约束

在关系模型中，关系完整性主要是指以下三方面：

实体完整性，所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值；比如学生表的主码通常是取学号为主码
参照完整性，是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；，参照关系也称为外键表，被参照关系也称为主键表。
用户定义的完整性，指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如性别属性只能取”男“或”女“ ，再就是年龄的取值范围，可以取值0-130 ，但不能取负数，因为年龄不可能是负数。

常用的就是实体完整性和参照完整性，分别由主键约束和外键约束解决，用户定义完整性由check约束解决。

数据库索引

索引是存放在模式中的一个数据库对象，从属于数据表，作用是加速对表的查询（通过快速路径访问来访问数据，减少磁盘的I/O），索引在数据字典里独立存放，但不能独立存在，必须从属于某个数据表。索引有自动创建的也有手动创建的

自动：当表上定义foreign key，unique, primary key时，系统会自动为其创建索引，当数据表被删除时索引自动被删除
手动：用户通过create index….语句创建索引，通过drop index….语句删除索引

以下为索引操作的语法

#创建表tml基于字段tml_id的查询速度
create index tml_tml_id_idx     //注意命名规范
on tml(tml_id);
#同时对多列创建索引
create  index tml_tml_id_idx2
on  tml(tml_id,tml_name);
#删除tml表上tml_tml_id_idx2 索引
drop index  tml_tml_id_idx2
on tml;  //mysql只要求本表内索引不重名，所以建立索引时必须指定表

数据库视图

视图并非数据表，不能存储数据，只是数据的逻辑显示，所谓视图就是一条被命名的SQL查询语句

#创建视图的语法
create or replace  view 视图名
as
subquery

如果该视图不存在，则创建视图，如果存在，则使用新视图替换已有，后面的subquery就是一个查询语句

#创建视图，通常对视图的操作仅限于查询，没有修改
create  or  replace view view_test
as
select  teacher_name  ,teacher_pass  from  teacher_table
with check option;         //强制要求不能对该视图进行修改

#删除视图
drop  view  view_test

【MySQL数据库基础二】DDL数据定义语句

数据库定义

创建表的语法

普通建表方式

子查询建表方式

修改表的语法

修改表名

删除、添加、修改表字段

删除表的语法

数据库约束

非空约束 NOT NULL

建表时创建非空约束

修改表时增加或删除非空约束

唯一约束UNIQUE

创建表时增加唯一约束

修改表时增加唯一约束

主键约束 PRIMARY KEY

建表时建立主键约束

修改表时建立主键约束

设置自增长主键约束

外键约束FOREIGN KEY

建表时建立外键约束

修改表时建立外键约束

建立自关联的外键约束

删除外键约束

CHECK约束

完整性约束

数据库索引

数据库视图

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

【MySQL数据库基础 二】DDL数据定义语句

数据库定义

创建表的语法

普通建表方式

子查询建表方式

修改表的语法

修改表名

删除、添加、修改表字段

删除表的语法

数据库约束

非空约束 NOT NULL

建表时创建非空约束

修改表时增加或删除非空约束

唯一约束UNIQUE

创建表时增加唯一约束

修改表时增加唯一约束

主键约束 PRIMARY KEY

建表时建立主键约束

修改表时建立主键约束

设置自增长主键约束

外键约束FOREIGN KEY

建表时建立外键约束

修改表时建立外键约束

建立自关联的外键约束

删除外键约束

CHECK约束

完整性约束

数据库索引

数据库视图

热门文章

最新文章

相关课程

相关电子书

推荐镜像

【MySQL数据库基础二】DDL数据定义语句