DDL 数据定义语言

转载

mob604757006a49 2021-08-26 16:54:00

DDL（Data Definition Language）:数据定义语言，用于库和表的创建、修改、删除。主要包括CREATE、DROP、ALTER语句；

1、对数据库的操作

-- 创建数据库
create database [if not exists] 数据库名 [character set 字符集名];

-- 删除数据库
drop database [if exists] 数据库名;

-- 修改数据库的字符集
alter database 数据库名 character set gbk;

2、常用的数据类型

数值

tinyint，非常小的数据，1个字节。（bit）
smallint，较小的数据，2个字节。（short）
mediumint，中等大小的数据，3个字节。
int，标准的整数，4个字节。
bigint，较大的整数，8个字节。（long）
float，单精度浮点数，4个字节。
double，双精度浮点数，8个字节。
decimal，字符串形式的浮点数（用于金融计算）。

字符串

char，固定长字符串，比较耗费空间，长度大小：0~255。
varchar，可变字符串，长度大小：0~65535。（String）
tinytext，微型文本，2^8-1。
text，文本串，2^16-1。（保存大文本）

时间日期

date，日期，YYYY-MM-DD。
time，时间，HH:mm:ss。
datetime，最常用的时间格式，YYYY-MM-DD HH:mm:ss。
timestamp，时间戳，1970.1.1到现在的毫秒数。
year，年份。

3、对表的操作

表(table)是数据库的基本组成单元，所以数据都以表的形式组织，目的是可读性强。

表分为行和列，行：数据/记录(data)，列：字段(column)

字段属性：字段名，数据类型，相关的约束

-- 创建数据表
create table [if not exists] `表名`(
	`字段名` 列类型 [属性] [索引] [注释], -- 使用``来区分mysql自带的关键字
    ...
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置];


-- 删除数据表
drop table [if exists] 表名;


-- 添加字段
alter table `表名` add(`字段名1` 字段类型1,...,`字段名n`,字段类型n);


-- 修改字段类型
alter table `表名` modify `字段名` 字段的新类型;


-- 同时修改字段名和字段类型
alter table `表名` change `字段名` `新字段名` 新字段类型;


-- 删除字段
alter table `表名` drop 字段名;


-- 修改表名
alter table `表名` rename to `新表名`;

【示例 1】创建student表

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`) -- 设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8; -- 设置引擎和字符编码

DDL 数据定义语言_数据

4、字段属性

Unsigned：无符号的，声明的数据不允许为负数。如 Unsigned tinyint，范围：0~255(128+127)。
zerofill：0填充的，不足的位数使用0来填充。如 int(3) ，5 --- 005。
Auto_InCrement：主键自增策略，可以定义起始值和步长。
not null：如果不给它赋值，就会报错。
null：如果不写值，默认就是null。
default：设置默认的值。
comment：注释。

5、数据表的类型（引擎）

名称	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MYISAM的2倍

适用场景：

MYISAM，节约空间，速度较快。
INNODB，安全性高，事务的处理，多用户操作数据表

6、在物理空间存储的位置

所有的数据库文件都存储在data目录下：

DDL 数据定义语言_数据_02

MySQL引擎在物理文件上的区别：

InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件。
MyISAM类型数据表对应三个文件 :
- * . frm 表结构的定义文件。
- * . MYD 数据文件 ( data )。
- * . MYI 索引文件 ( index )。

7、设置数据表的字符集编码

因为编码不匹配就会出现乱码，有的编码不能存储汉字。

写建表语句时加上字符编码的设置，charset=utf8。
在my.ini文件上配置一些参数。

8、外键(foreign key)

作用：用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。

使用：在子表添加外键约束，然后引用主表中某字段。

【示例 2】在创建表的时候，增加约束

-- 创建年级表(id,年级名称)
CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 创建学生信息表(学号,姓名,密码,性别,出生日期,学生年级,家庭地址,邮箱)
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL  COMMENT '学生年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`), -- 将id设置主键
  KEY `FK_gradeid` (`gradeid`), -- 定义外键
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 给这个外键添加约束(CONSTRAINT),然后执行引用(REFERENCES)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

注：如果要删除有外键关系的表，必须先删除引用的别人的表(从表)，再删除被引用的表（主表）。

【示例 3】创建表成功后，添加外键约束

-- 创建年级表(id,年级名称)
CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 创建学生信息表(学号,姓名,密码,性别,出生日期,学生年级,家庭地址,邮箱)
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL  COMMENT '学生年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`) -- 将id设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8; 


-- 给学生表添加外键约束
ALTER TABLE `student` 
ADD
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

DDL 数据定义语言_表名_03