【MySQL和数据库】MySQL & database 第五章：DDL学习（主要是对库、表、字段的增删改）

2021-07-30 17:10:41

MySQL和数据库系列文章目录

目录

库和表的管理

库的管理

  • 创建库
#创建一个库；如果存在，则报错；
create database 库名；
#判断库是否不存在，若是，则创建；
create database if not exists 库名；

  • 库的修改

    • 更改库的字符集

      alter database 库名 character set 新字符集；#如utf-8 改为 gbk

    • 库的删除

      #删除一个库，若不存在，则报错；
drop database 库名；
#判断库是否存在，若是，则删除；
drop database if exists 库名；

表的管理

  • 表的创建

    • 语法

      create table 表名 
				（
				列名 列的类型 【（长度） 约束】，
				列名 列的类型 【（长度） 约束】，
				...
				列名 列的类型 【（长度） 约束】
				）；

  • 表的修改

    #语法格式
alter table 表名 change|modify|add|drop column / rename to 字段 【数据类型/约束】 / 表名；

    • 修改列名

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

    • 修改列的类型或约束

      alter table 表名 modify column 列名 新类型或新约束；

    • 添加新列

      alter table 表名 add column 列名 数据类型；

    • 删除列

      alter table 表名 drop column 列名；

    • 修改表名

      alter table 表名 rename to 新表名；

  • 表的删除

    #判断是否存在表，若是，则删除；
drop table if exists 表名；

  • 通用的写法

    #库的删除和创建；
drop database if exists 旧库名；
create database 新库名；

#表的删除和创建；
drop table if exists 旧表名；
create table 新表名；

  • 表的复制

    • 复制表的结构

      create table 新表名 like 旧表名；

    • 复制表的结构和数据内容

      create table 新表名
（select * from 旧表名）；

    • 复制表的部分结构

      create table 新表名
（select 需复制的字段 from 旧表名 where 0【结果为false即可】）；

    • 复制表的部分结构和部分数据内容

      create table 新表名
（select 需复制的字段 from 旧表名 where 筛选内容的条件）；

  • 显示当前库的所有表名

    show tables;

  • 显示表的结构

    desc 表名；

​

常见数据类型

  • 数值型：
    • 整型；
    • 小数型：
      • 定点数；
      • 浮点数；
  • 字符型：
    • 较短的文本：char、varchar；
    • 较长的文本：text，blob（较长的二进制数据）；
  • 日期型；

整型

整数类型		字节数			范围大小
Tinyint			1			有符号：[-2^7,2^7 - 1]；无符号：[0,2^8]
Smallint		2			有符号：[-2^(15),2^(15) - 1]；无符号：[0,2^(16)]
Mediumint		3			有符号：[-2^(23),2^(23) - 1]；无符号：[0,2^(24)]
Int、integer		4			有符号：[-2^(31),2^(31) - 1]；无符号：[0,2^(32)]
Bigint			8			有符号：[-2^(63),2^(63) - 1]；无符号：[0,2^(64)]
  • 特点：
    • 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，则在数据类型后面加上关键字 unsigned；
    • 如果插入的数值超出了整型的范围，会报 out of range 异常，并且插入临界值；
    • 如果不设置长度，会有默认的长度；
      长度代表了显示的最大宽度，如果不够会用 0 在左边填充，但是必须搭配 zerofill 使用（使用了 zerofill 后都会转换为有符号）；

小数

浮点数类型		字节		
float（M，D）			4
double（M，D）			8

给定数类型		   字节		范围
DEC（M，D）		M + 2		最大取值范围与 double 相同，
DECIMAL（M，D）	M + 2		给定 decimal 的有效取值范围由 M 和 D 决定；
  • 特点：
    • M 表示为整数位和小数位的规定位数，D 表示为小数位的规定位数；
      如果超过 M 范围，则插入临界值，如果超过 D 范围，则进行四舍五入；
    • M 和 D 都可以省略；
      如果是 decimal，则 M 默认为 10，D 默认为 0；
      如果是 float 和 double，则会根据插入的数值的精度来决定精度；
    • 定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用；

字符型

较短字符串类型		最多字符数		描述及存储需求
char（M）				M			M 为 0~255 之间的整数
varchar（M）			M			M 为 0~65535 之间的整数

  • 特点：

    • char 是固定长度的字符，varchar 是可变长度的字符；
    • char 的 M 可以省略，默认为1，varchar 的不可以省略；
    • char 比较耗费空间，varchar 比较节省空间；
    • char 效率比 varchar 高；

  • Enum 枚举类型
    该类型下，插入值只能是列表成员中的指定值之一；

    create table 表名 
				（
				字段 enum（列表成员）
				）

  • set 集合类型
    与枚举类型相似，区别于的是，可以选取列表成员中的多个指定值；

  • binary 和 varbinary 二进制类型
    类似 char 和 varchar，区别于的是，用于保存较短的二进制数据；

日期型

日期和时间类型		字节		最小值					最大值
date			  4		   1000-01-01				9999-12-31
datetime		  8		   1000-01-01 00:00:00		9999-12-31 23:59:59
timestamp		  4		   1970-01-01 08:00:01		2038年的某一刻
time			  3		   -838:59:59				838:59:59
year			  1		   1901					    2155
  • 特点：
    • timestamp 和实际时区有关，更能反应实际的日期；
      datetime 只能反映插入时的当地时区；
    • timestamp 的属性受 mysql版本和 sqlmode 的影响；

常见约束

  • 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性；

  • 分类：六大约束

    • not null：非空，用保证该字段的值不能为空，如姓名、学号等；
    • default：默认，用于保证该字段有默认值，如性别；
    • primary key：主键，用于保证该字段的值具有唯一性，并且非空，比如学号、员工编号等；
    • unique：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号；
    • check：检查约束【mysql中不支持】，比如年龄、性别；
    • foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业标号，员工表的部门编号，员工表的工种编号；

  • 添加约束的时机：

    • 创建表时；
    • 修改表时；

列级约束和表级约束

  • 约束的添加分类：
    • 列级约束：
      六大约束在语法上都支持，但是外键约束没有效果；
    • 表级约束：
      除了非空约束、默认约束，其它都支持；
create table 表名 
				（
				字段 数据类型 列级约束，
				字段 数据类型 列级约束，
				
				表级约束
				）

  • 列级约束

    语法：

    字段名 字段类型 约束类型

    直接在字段名和类型后面追加 约束类型即可；
    mysql仅支持：默认、非空、主键、唯一；

    # 检查和外键不作为列级约束，这里执行无效果；
CREATE DATABASE students;
USE students;
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) #外键
		    );
CREATE TABLE major 
		  (
		   id INT PRIMARY KEY,
		   majorName VARCHAR(20)
		  );
		  
SHOW INDEX FROM stuinfo;		    
DESC stuinfo;

  • 表级约束
    语法：

    【constraint 约束名】 约束类型（字段名）

    约束名省略，默认为字段名，除了主键是 primary；
    mysql 不支持 非空约束 和 默认约束；

    CREATE DATABASE students;
USE students;
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) #外键
		     
		    );
CREATE TABLE major 
		  (
		   id INT PRIMARY KEY,
		   majorName VARCHAR(20)
		  );
		  
SHOW INDEX FROM stuinfo;		    
DESC stuinfo;

  • 列级约束 和 表级约束的对比

			位 置			支持的约束类型（检查都MySQL不支持）	是否可起约束名
列级约束	 列的后面		 语法都支持，当外键没有效果				不可以
表级约束	 所有列的下面		默认和非空不支持，其它支持			   可以，主键没有效果

  • 通用的写法
    外键约束 一般写在 表级约束中，其它约束 一般写在 列级约束；

    create table stuinfo
				 (
                    id int primary key,
                     stuName varchar(20) not null,
                     sex char(1),
                     age int default 18,
                     seat int unique,
                     majorId int,
                     
                     constraint fk_stuinfo_major foreign key(majorId) references major(id);
                   )

主键、唯一、外键约束的特点

  • 主键约束 和 唯一约束 的对比
			保证唯一性	是否允许为空		在表中的数量	 	是否允许组合字段
主键约束		✔			×		 	   至多一个				✔
唯一约束		✔			✔			  可以有多个			   ✔

  • 外键约束的特点

    • 要求在 从表 设置外键关系；

    • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，字段名无要求；

    • 主表的关联列必须是一个 key （一般是主键 或唯一）；

    • 插入数据时，先插入主表，再插入从表；
      删除数据时，先删除从表，再删除主表；

      • 级联删除：可以做到直接删除主表被约束的字段，同时从表对应的数据行也会被删除；

        alter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(字段) on delete cascade;

      • 级联置空：类似级联删除，但是不会将从表对应整行数据删除，只是会在外键字段的位置置空；

        alter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(字段) on delete set null;

修改表时添加约束

  • 修改表时添加约束
    语法：

    #添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
#添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键引用】；

    案例：

    create table stuinfo 
				  (
                     id int,
                      stuname varchar(20),
                      gender char(1),
                      seat int,
                      age int,
                      majorid int
                    )

    • 添加非空约束

      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 add 【constraint 约束名】 primary key(id);

    • 添加唯一

      #列级约束
alter table stuinfo modify column seat int unique;
#表级约束
alter table stuinfo add unique(seat);

    • 添加外键

      #只有表级约束
alter table stuinfo add 【constraint fk_stuinfo_major】 foreign key(majorid) references major(id);

修改表时删除约束

  • 修改表时删除约束

    • 删除非空约束

      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; #这里用的也是约束名；

标识符（自增长）

  • 标识列

    • 含义：
      可以不用手动的插入值，系统提供默认的自增序列值；
    • 特点：
      • 标识列必须和一个键进行搭配；
      • 一个表至多只有一个标识列；
      • 标识列的类型只能是数值型；
      • 标识列 可以通过 s e t    a u t o _ i n c r e m e n t _ i n c r e m e n t   =   步 长 set~~auto\_increment\_increment~=~步长 set  auto_increment_increment = 步长​，设置步长；
        可以通过 手动插入值，设置起始值；

  • 创建表时设置标识列

    create table stuinfo (
					id int primary key auto_increment,
    				stuName varchar(20)
);

  • 修改表时设置标识列

    alter table stuinfo modify column id int primary key auto_increment;

  • 修改表时删除标识列

    alter table stuinfo modify column id int primary key;
上一篇：CDH 05.cdh本地源搭建配置


下一篇：MyEclipse快捷键大全（绝对全）