>  기사  >  데이터 베이스  >  MySQL 강의 3: DDL 데이터 정의문

MySQL 강의 3: DDL 데이터 정의문

coldplay.xixi
coldplay.xixi앞으로
2021-02-24 09:10:201716검색

MySQL 강의 3: DDL 데이터 정의문

무료 학습 권장 사항: mysql 비디오 튜토리얼

Article Directory

  • 1. DDL 데이터 정의 문
  • 2. 데이터 유형
    • 2.1 정수
    • 2.2 십진수
    • 2.3 문자 유형
    • 2.4 날짜 유형
  • 2. 6가지 주요 제약 조건
  • 4. ID 열
  • 5. 계단식 삭제 및 공백

1. DDL 데이터 정의문

도서관 관리

생성: 데이터베이스 생성 [존재하지 않는 경우] 라이브러리 이름;
수정: 라이브러리 이름을 수정해야 하는 경우 폴더를 직접 수정하세요.
삭제: DROP DATABASE IF EXISTS 라이브러리 이름;create database [if not exists] 库名;
修改:若需要修改库名,直接修改文件夹
删除:DROP DATABASE IF EXISTS 库名;

表的管理

创建

create table IF NOT EXISTS 表名(
列名 列的类型[长度、约束],
列名 列的类型[长度、约束],
列名 列的类型[长度、约束],
...
)

修改
alter table 表名 add|drop|modify|change column 列名 [列类型 约束];

删除
drop table 表名;

【库的管理】# 创建库CREATE DATABASE IF NOT EXISTS books;# 修改库的字符集ALTER DATABASE books CHARACTER SET gbk;# 删除库DROP DATABASE IF EXISTS books;【表的管理】# 1.创建表CREATE TABLE IF NOT EXISTS book(
	id INT,
	bName VARCHAR(20),
	authorID INT,
	publishDate DATETIME);DESC book;CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10));DESC author;# 2.修改表# 修改列名ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的类型或约束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# 添加新列ALTER TABLE author ADD COLUMN annual DOUBLE; # 添加新列作为第一个字段ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 删除列ALTER TABLE author DROP COLUMN annual;# 修改表名ALTER TABLE author RENAME TO book_author;# 3.表的删除DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4.表的复制INSERT INTO author VALUES(1,'小樱','日本'),(2,'悟空','中国'),(3,'绿巨人','美国'),(4,'哪吒','中国');# 仅复制表的结构CREATE TABLE author2 LIKE author;# 复制表的结构+数据CREATE TABLE author3 SELECT * FROM author;# 只复制部分数据CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中国';# 仅复制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;

二、数据类型

2.1 整型

整数类型 别名 字节 无符号范围 有符号范围
Tinint 微整型 1 0~255 -128~127
Smallin 小整型 2 0~65535 -32768~32767
Mediumint 中整型 3 0~1677215 -8388608~8388607
Int或Integer 整型 4 0~4294967295 -2147483648~2147483647
Bigint 大整型 8 0~9223372036854775807*2+1 -9223372036854775808~9223372036854775807
【整型】# 1.默认为有符号,可以添加unsigned设置为无符号CREATE TABLE IF NOT EXISTS tab_int(
	t1 INT, # 有符号
	t2 INT UNSIGNED, # 无符号
	t3 INT ZEROFILL #添加zerofill后自动变更为无符号整型,位数不够0填充.);DESC tab_int;SELECT * FROM tab_int;# 2.如果插入的数值超出了整型的范围,会报out of range异常INSERT INTO tab_int VALUES(2147483648,1);# 3.如果不设置长度,会有默认的长度,位数不够时0填充(前提是字段有ZEROFILL).

2.2 小数

浮点数类型 字节 范围
float(M,D) 4 -2^128 ~ +2^128
double(M,D) 8 -2^1024 ~ +2^1024
定点数类型
DEC(M,D) M+2 最大取值范围与double相同,给定decimal的有效范围由M和D决定

注意:
M:整数部位+小数部位的总长度
D:小数部位
D和M都省略时:
1、如果是decimal类型,则M默认为10,D默认为0;
2、如果是floact和double,会根据插入的数值的精确度来决定精度。
3、定点型的精确度较高,如果要求插入数值的精确度较高如货币运算则考虑使用。

2.3 字符型

字符串类型 M是否可以省略 特点 空间耗费 效率 范围
char(M) M可以省略,默认为1 定长 比较耗费 M为0~255之间的整数
varchar(M) M不可以省略 可变长 比较节省 M为0~65535之间的整数

binaryvarbinary类型,类似于char和varchar,不同的是它们包含二进制字符而不包含非二进制字符,即保存较短的二进制。

Bit(M)类型,字节为1~8,范围为Bit(1)~Bit(8)

Enum类型,即枚举类型,要求插入的值必须属于列表中指定的值之一,如果列成员为1~255,则需要1个字节存储;如果列成员为255~65535

테이블 관리

Creation테이블 생성 IF NOT EXISTS 테이블 이름(
열 이름 열 유형 [길이 , 제약조건],
열 이름 열 유형 [길이, 제약 조건],
열 이름 열 유형 [길이, 제약 조건] , code>
...
)
【枚举】CREATE TABLE tab_set(
	s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,内部会进行排序,如插入c,a会变成a,c
2. 데이터 유형2.1 정수 유형정수형aliasbyte부호 없는 범위부호 있는 범위Tinint마이크로정수형10~ 255-128~127
수정
테이블 테이블 이름 변경 add|drop|수정 |열 변경 열 이름 [열 유형 제약 조건];
delete
테이블 테이블 이름 삭제;
🎜🎜🎜스몰린🎜🎜 작은 정수🎜🎜2🎜🎜0~65535🎜🎜-32768~32767🎜🎜🎜🎜Mediumint🎜🎜Medium int🎜🎜3🎜 🎜0~1677215🎜🎜-8388608~83 88607🎜🎜 🎜🎜Int 또는 Integer🎜🎜integer🎜 🎜4🎜🎜0~4294967295🎜🎜-2147483648~2147483647🎜🎜🎜🎜Bigint🎜🎜bigint🎜🎜 8🎜🎜0~9223372036854775807 *2+1🎜🎜-9223372036854775808~ 9223372036854775807🎜🎜🎜🎜
【日期类型】CREATE TABLE tab_date(
	t1 DATETIME, # 不受时区影响
	t2 TIMESTAMP # 受时区影响);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 设置时区SHOW VARIABLES LIKE 'time_zone'; #显示当前时区
🎜🎜2.2십진수🎜🎜 Floating Point TypeSBYTES🎜🎜Range🎜🎜🎜🎜🎜🎜 float (m, d) 🎜🎜4🎜🎜 -2^128 ~ +2^128 🎜🎜🎜🎜 🎜🎜🎜🎜 double(M,D)🎜🎜8🎜🎜-2^1024 ~ +2^1024🎜🎜🎜🎜🎜고정 소수점형🎜🎜 🎜🎜🎜 🎜🎜🎜🎜DEC(M,D)🎜🎜M+2🎜🎜최대값 범위는 double과 동일하며 주어진 소수점의 유효 범위는 M으로 지정됩니다. D로 결정합니다🎜🎜🎜🎜 🎜🎜참고:
M: 정수부 + 소수부 전체 길이
D: 소수부
D, M을 모두 생략하는 경우:
1. 소수형인 경우 , M의 기본값은 10이고 D의 기본값은 0입니다.
2. float 또는 double인 경우 삽입된 값의 정확도에 따라 정확도가 결정됩니다.
3. 고정 소수점 형식은 정확도가 더 높습니다. 통화 계산 등 더 높은 정확도의 숫자 값을 입력해야 하는 경우 사용을 고려해보세요. 🎜🎜🎜🎜2.3 문자형🎜🎜🎜🎜🎜🎜문자열형🎜🎜생략가능🎜🎜특징🎜🎜공간소모🎜🎜효율성🎜🎜Range🎜🎜🎜🎜🎜 🎜char(M)🎜🎜M은 생략 가능 , 기본값은 1🎜🎜고정 길이🎜🎜더 비쌉니다🎜🎜High🎜🎜M은 0~255 사이의 정수🎜🎜🎜🎜varchar(M)🎜🎜M은 생략할 수 없습니다🎜🎜가변 길이🎜🎜입니다 더 경제적입니다🎜 🎜Low🎜🎜M은 0~65535🎜🎜🎜🎜🎜🎜binaryvarbinary 유형 사이의 정수이며 다음을 포함한다는 점을 제외하면 char 및 varchar와 유사합니다. 이진이 아닌 문자가 없는 이진 문자, 즉 더 짧은 이진을 저장합니다. 🎜🎜🎜🎜Bit(M) 유형🎜, 바이트는 1~8, 범위는 Bit(1)~Bit(8)입니다. 🎜🎜🎜Enum 유형🎜, 즉 열거 유형은 삽입된 값이 목록에 지정된 값 중 하나에 속해야 합니다. 열 멤버가 1~255인 경우 1 byte의 저장이 필요하며, 열 멤버가 255~65535인 경우 저장하려면 2바이트가 필요하며 최대 65535개의 멤버가 필요합니다. 🎜🎜🎜Set type🎜은 Enum과 유사하게 0~64명의 멤버를 저장할 수 있습니다. Enum과의 가장 큰 차이점은 Set 유형은 한 번에 여러 멤버를 선택할 수 있는 반면 Enum은 멤버 수에 따라 저장 공간이 다른 바이트를 차지한다는 점입니다. 🎜🎜🎜🎜🎜회원수🎜🎜바이트수🎜🎜🎜🎜🎜🎜1~8🎜🎜1🎜🎜🎜🎜9~16🎜🎜2🎜🎜🎜🎜17~24 🎜🎜3🎜🎜🎜🎜25 ~ 32🎜🎜4🎜🎜🎜🎜33~64🎜🎜8🎜🎜🎜🎜
【枚举】CREATE TABLE tab_set(
	s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,内部会进行排序,如插入c,a会变成a,c

2.4 日期类型

日期和时间类型 特点 字节 最小值 最大值
date 只保存日期 4 1000-01-01 9999-12-31
datetime 保存日期+时间 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp(使用较多) 保存日期+时间 4 187001080001 2038年的某个时刻
time 只保存时间 3 -838:59:59 838:59:59
year 只保存年 1 1901 2155

timestamp和实际时区有关,更能反映实际的日期;datetime则只能反映出插入时的当地时区。
timestamp的属性受Mysql版本和SQLMode的影响很大。

【日期类型】CREATE TABLE tab_date(
	t1 DATETIME, # 不受时区影响
	t2 TIMESTAMP # 受时区影响);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 设置时区SHOW VARIABLES LIKE 'time_zone'; #显示当前时区

二、六大约束

含义:一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性。

NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表汇中是唯一的
PRIMARY KEY 主键(唯一且非空)
FOREIGN KEY 外键
CHECK 检查约束(mysql中不支持)
DEFAULT 默认值,保证该字段有默认值

列级约束:六大约束语法上都支持,但外键约束没有效果。

表级约束:除了非空、默认,其他都支持。

常用的做法是:其他约束都写在列级,外键约束写在表级。

主键和唯一对比 是否保证唯一性 是否允许为空 允许有几个 是否允许组合(不推荐)
主键 × 至多一个主键 允许组合主键
唯一 √(允许有一个null) 可以有多个唯一 允许组合唯一

外键的特点:

  • 要求在从表设置外键关系。
  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求。
  • 主表的关联列必须是一个key(一般是主键或唯一)
  • 插入数据时,先插入主表再插入从表;删除数据时,先删除从表再删除主表。
列级约束和表级约束比较 位置 支持的约束类型 是否可以起别名
列级约束 列的后面 语法都支持,但外键没有效果 不可以
表级约束 所有列的下面 默认和非空不支持,其他支持 可以(主键没有效果)
【列级约束】
直接在字段名和类型后面追加约束类型。
注意:只支持默认、非空、主键、唯一,不支持外键约束CREATE DATABASE students;USE students;CREATE TABLE major(
	id INT PRIMARY KEY,# 主键
	majorName VARCHAR(20));CREATE TABLE stuinfo(
	id INT PRIMARY KEY,# 主键
	stuName VARCHAR(20) NOT NULL, #非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
	seat INT UNIQUE,# 唯一
	age INT DEFAULT 18, #默认约束
	majorId INT REFERENCES major(id) # 外键);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外键,唯一【表级约束】# 语法:[CONSTRAINT 约束名 ] 约束类型(字段名)DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),# 主键
	CONSTRAINT uq UNIQUE(seat), # 唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 检查(不报错,但无效)
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键);SHOW INDEX FROM stuinfo;【修改表时添加约束】# 添加非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主键ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一键ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表时删除约束】# 删除非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 删除默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT;# 删除主键ALTER TABLE stuinfo DROP PRIMARY KEY;# 删除唯一ALTER TABLE stuinfo DROP INDEX seat;# 删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

学习了约束,尝试完成籼米的测试题MySQL 강의 3: DDL 데이터 정의문
1、列级约束:
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列约束不支持起名字
表级约束:
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 实际上主键起了名字也没效果
2、与1类似
3、
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

四、标识列

标识列又称为自增长列,其实也可以将标识列纳入约束的范围。

含义:可以不用手动的插入值,系统提供默认的序列值。

特点:
1.标识列不一定非要和主键搭配,但要求是一个key。
2.一个表至多可以有一个表示列。
3.表示列的类型只能为数值型。
4.标识列可以通过SET auto_increment_increment=3;设置步长,也可以通过手动插入值来设置起始值。

【创建表时设置标识列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT, #设置自动自增
	NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL,'花花');INSERT INTO tab_identity(NAME) VALUE('Hudie');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#设置步长为3# 起始值可以通过改变第一条记录的值来更改TRUNCATE TABLE tab_identity;【修改表时设置标识列】CREATE TABLE tab_identity(
	id INT, #设置自动自增
	NAME VARCHAR(20));# 设置主键和标识列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表时删除标识列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;

五、级联删除与置空

级联删除:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
级联置空:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

【演示级联删除、级联置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20));INSERT INTO majorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT);INSERT INTO stuinfoSELECT 1,'join1','女',NULL,NULL,1 UNION ALLSELECT 2,'join2','女',NULL,NULL,1 UNION ALLSELECT 3,'join3','女',NULL,NULL,2 UNION ALLSELECT 4,'join4','女',NULL,NULL,2 UNION ALLSELECT 5,'join5','女',NULL,NULL,1 UNION ALLSELECT 6,'join6','女',NULL,NULL,3 UNION ALLSELECT 7,'join7','女',NULL,NULL,3 UNION ALLSELECT 8,'join8','女',NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 传统方式添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 删除major表的3号专业# 方式1:级联删除#   先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外键时添加级联删除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:级联置空#   先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外键时添加级联置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;

更多相关免费学习推荐:mysql教程(视频)

위 내용은 MySQL 강의 3: DDL 데이터 정의문의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 csdn.net에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제