mysql之DDL

分手后的思念是犯贱 2023-09-27 14:02 99阅读 0赞

**目录**

1.库的操作

2.表的操作

(1)创建表

(2)修改表

(3)删除表

(4)复制表

3.数据类型

(1)数值型

(2)浮点型：

(3)字符型

(4)日期型

4.约束

(1)添加约束

(2)删除约束

(3)外键特殊说明

(4)自增长列

--------------------

**DDL（Data Define Languge）：数据定义语言，用于库和表的创建、修改、删除**

### **1.库的操作** ###

(1)创建库  
create database \[if not exists\] 库名\[ character set 字符集名\];  
(2)修改库(一般禁止修改)  
alter database 库名 character set 字符集名  
(3)删除库  
drop database \[if exists\] 库名;

### **2.表的操作** ###

#### (1)创建表 ####

create table \[if not exists\] 表名(  
字段名 字段类型 \[约束\],  
字段名 字段类型 \[约束\],  
字段名 字段类型 \[约束\]

）

####    (2)修改表 ####

1.添加列  
alter table 表名 add column 列名 类型\[firstlafter 字段名\];

2.修改列的类型或约束  
alter table 表名 modify column 列名 新类型 \[新约束\]；

3.修改列名

alter table 表名 change column 旧列名 新列名 类型；

4.删除列  
alter table 表名 drop column 列名  
5修改表名  
alter table 表名 rename\[to\] 新表名;

#### (3)删除表 ####

drop table\[if exists\] 表名;

####    (4)复制表 ####

1、制表的结构  
create table 表名 like 旧表;  
2、复制表的结构+数据  
create table 表名select 查询列表 from 日表\[where 筛选\]

可以进行跨库创建表，如根据A数据库的表创建B数据库的表

### 3.数据类型 ###

#### (1)数值型 ####

整形：  
tinyint、 smallint、 mediumint、 int/integer、 bigint

取值范围：

**1、bigint**

从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字），无符号的范围是0到

18446744073709551615。一位为 8 个字节。

**2、int**

一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字），无符号的范围是0到4294967295。一位大小为 4 个字节。  
**int** 的 SQL-92 同义词为 **integer**。  
  
**3、mediumint**  
一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节。

**4、smallint**

一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，无符号的范围是0到65535。一位大小为 2 个字节。MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本。

**5、tinyint**

有符号的范围是-128 - 127，无符号的范围是 从 0 到 255 的整型数据。一位大小为 1 字节。

特点:  
都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号

如果超出了范围，会报out or range异常，插入临界值

长度可以不指定，默认会有一个长度长度代表显示的最大宽度，不代表数值的长度，类型确定之后，数值的范围就确定了。如果不够则左边用0填充，但需要搭配zerofill，并目默认变为无符号整型

#### (2)浮点型： ####

定点数:decimal(M,D)

浮点数:  
float(M,D)  
double(MD)  
特点:  
M代表整数部位+小数部位的个数，D代表小数部位

如果超出范围，则报out or range异常，并且插入临界值(O)

M和D都可以省略，但对于定点数，M默认为10，D默认为0

如果精度要求较高，则优先考虑使用定点数

#### (3)字符型 ####

char,varchar,enum,set,text等等

char:固定长度的字符，写法为char (m)，最大长度不能超过m，其中m可以省略，默认为1。

varchar:可变长度的字符，写法为varchar (m)，最大长度不能超过m，其中m不可以省略。

####    (4)日期型 ####

year年  
date日期  
time时间  
datetime日期+时间  
timestamp日期+时间 比较容易受时区，语法模式及版本的影响，更能反应当前时区的真实时间

### 4.约束 ###

*  NOT NULL:比如姓名、学号等
 *  DEFAULT:默认，用于保证该字段有默认值比如性别
 *  PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空比如学号、员工编号等
 *  UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空比如座位号
 *  CHECK:检查约束\[mysql中不支持\] 比如年龄、性别
 *  FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束一般在创建表和修改表时进行，最好在表中没有数据的时候进行修改。

约束分为列级约束和表级约束。

列级约束中不能使用外键约束，表级约束中不能使用非空约束和默认约束。

主键和唯一  
1、区别:  
(1)、一个表至多有一个主键，但可以有多个唯一  
(2)、主键不允许为空，唯一可以为空  
2、相同点  
都具有唯一性  
都支持组合键，但不推荐

#### (1)添加约束 ####

创建表时

CREATE

TABIE stuinfo(

id INT PRIMARY KEY，\#主键

stuName VARCHAR(20) NOT NULL,\#非空

gender CHAR(1)

seat INT UNIQUE，\#唯一

age INT DEFAULT 18，\#默认约束

majorId INT)

修改表时

1.添加非空约束  
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键约束

列级约束

ALTER TABLE stuinfo MODIFY COLUMN Id INT PRIMARY KEY;

表级约束  
ALTER TABLE stuinfo ADD PRIMARY KEY(id);  
4.添加唯一约束  
列级约束  
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE:  
表级约束  
ALTER TABLE stuinfo ADD UNIQUE(seat) ;

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk\_major FOREIGN KEY (majorid) REFERENCES major(id) on delete cascate；

ALTER TABLE stuinfo ADD CONSTRAINT fk\_major FOREIGN KEY (majorid) REFERENCES major(id) on delete set null；

on delete cascate：删除主表的数据时，外键表的数据也删除。

on delete set null：删除主表的数据时，外键表的有关外键的数据置空。

总结：

1、添加列级约束alter table 表名 modify column 字段名 字段类型 新约束  
2、添加表级约束alter table 表名 add \[constraint 约束名\] 约束类型(字段名)\[外键的引用\]

#### (2)删除约束 ####

\#1.删除非空约束  
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;  
\#2.删除默认约束  
ALTER TABLE stuinfo MODIFY COLUMN age INT ;  
\#3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY:  
\#4.删除唯一  
ALTER TABLE stuinfo DROP INDEX seat;  
\#5.删除外键  
ALTER TABLEstuinfo DROP FOREIGN KEY majorid;

#### (3)外键特殊说明 ####

外键:  
1、用于限制两个表的关系，从表的字段值引用了主表的某字段值

2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求  
3、主表的被引用列要求是一个key(一般就是主键)  
4、插入数据，先插入主表  
删除数据，先删除从表  
可以通过以下两种方式来直接删除主表，不用先删除从表的数据  
\#方式一:级联删除  
ALTER TABLE stuinfo ADD CONSTRATNT fk stu major FOREICN KEYmajorid) REFERENCES maor(id) ON DELETE CASCADE  
\#方式二:级联置空ALTER TABLE stuinfo ADD CONSTRAINT fkhstu major FOREICN KEY(majorid) REFERENCES major(id) ON DELETE SET IULL

#### (4)自增长列 ####

####  ####

特点:

1、不用手动插入值，可以自动提供序列值，黑认从1开始，步长为1

auto\_increment\_increment

如果要更改起始值:手动插入值

如果要更改步长:更改系统变量

set auto\_increment\_increment=值

2、一个表至多有一个自增长列

3、自增长列只能支持数值型

4、自增长列必须为一个key

创建表时设置自增长列