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

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快捷键大全(绝对全)