MySQL学习笔记6||创建、修改、删除操作及常见的数据类型和约束

备注：该博客内容根据技术教学视频整理与总结而成，原视频来自于尚硅谷MySQL教程。

一、数据的管理（插入insert、修改update、删除delete、truncate）

1、插入insert

语法：方式一： insert into 表名(字段名，...) values(值1，...);
           方式二：insert into 表名 set 字段=值,字段=值,...;

特点：1、字段类型和值类型一致或兼容，而且一一对应
           2、可以为空的字段，可以不用插入值，或用null填充，不可以为空的字段，必须插入值
           3、字段的个数和顺序不一定与原始表中的一致，但必须保证值和字段一一对应
           4、字段个数和值的个数必须一致
           5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

两种方式 的区别：1.方式一支持一次插入多行，语法如下：
                              insert into 表名【(字段名,..)】 values(值，..),(值，...),...;
                              2.方式一支持子查询，语法如下：insert into 表名 查询语句;

2、修改update

语法：修改单表：update 表名 set 字段=值,字段=值 【where 筛选条件】;
           修改多表：update 表1 别名 
                             left|right|inner join 表2 别名 
                             on 连接条件  
                             set 字段=值,字段=值 
                           【where 筛选条件】;

3、删除delete、truncate

delete语法：删除单表：delete from 表名 【where 筛选条件】【limit 条目数】
                     删除多表：delete 别名1,别名2 from 表1 别名 
                                       inner|left|right join 表2 别名 
                                       on 连接条件
                                     【where 筛选条件】

truncate语法：truncate table 表名

两种方式的区别【★】：

• truncate不能加where条件，而delete可以加where条件 
• truncate的效率高一丢丢
• truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始；delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
• truncate没有返回值，delete可以返回受影响的行数
• truncate删除不能回滚，delete删除可以回滚

---

二、表的管理（创建create、修改alter、删除drop）

1、创建create

语法：create table 【if not exists】 表名(
               字段名 字段类型 【约束】,
               字段名 字段类型 【约束】,
               ......
               字段名 字段类型 【约束】 );

2、修改alter

• 添加列：alter table 表名 add column 列名 类型 【first|after 字段名】;
• 修改列的类型或约束：alter table 表名 modify column 列名 新类型 【新约束】;
• 修改列名：alter table 表名 change column 旧列名 新列名 类型;
• 删除列：alter table 表名 drop column 列名;
• 修改表名：alter table 表名 rename 【to】 新表名;

3、删除drop

语法：drop table【if exists】 表名;

4、复制表

• 复制表的结构：create table 表名 like 旧表;
• 复制表的结构+数据：create table 表名  select 查询列表 from 旧表【where 筛选】;

---

三、库的管理（创建create、修改alter、删除drop）

1、创建create

语法：create database 【if not exists】 库名【 character set 字符集名】;

2、修改alter

语法：alter database 库名 character set 字符集名;

3、删除drop

语法：drop database 【if exists】 库名;

---

四、数据类型

1、数值型（整型、浮点型）

• 整型：tinyint、smallint、mediumint、int/integer、bigint
  特点：①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
             ②如果超出了范围，会报out or range异常，插入临界值
             ③长度可以不指定，默认会有一个长度
• 浮点型：定点数：decimal(M,D)、浮点数：float(M,D)、double(M,D)
  特点：①M代表整数部位+小数部位的个数，D代表小数部位
             ②如果超出范围，则报out or range异常，并且插入临界值
             ③M和D都可以省略，但对于定点数，M默认为10，D默认为0
             ④如果精度要求较高，则优先考虑使用定点数

2、字符型

       分类：char、varchar、binary、varbinary、enum、set、text、blob（较长的二进制数据）
       重点：char：固定长度的字符，写法为char(M)，最大长度不能超过M，M可以省略，默认为1
                  varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，M不可以省略

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

3、日期型 

       分类： year、date、time、datetime(日期+时间)、timestamp(日期+时间)   

	字节	范围	时区等的影响
datetime	8	1000——9999	不受
timestamp	4	1970-2038	受

---

五、约束

1、常见的约束

• NOT NULL：非空，该字段的值必填
• UNIQUE：唯一，该字段的值不可重复
• DEFAULT：默认，该字段的值不用手动插入有默认值
• CHECK：检查，mysql不支持
• PRIMARY KEY：主键，该字段的值不可重复并且非空  unique+not null
• FOREIGN KEY：外键，该字段的值引用了另外的表的字段

2、主键、唯一

• 区别：①、一个表至多有一个主键，但可以有多个唯一
             ②、主键不允许为空，唯一可以为空
• 相同点：都具有唯一性，都支持组合键，但不推荐             

3、外键

• 用于限制两个表的关系，从表的字段值引用了主表的某字段值
• 外键列和主表的被引用列要求类型一致，意义一样，名称无要求
• 主表的被引用列要求是一个key（一般就是主键）
• 插入数据，先插入主表，删除数据，先删除从表

可以通过以下两种方式来删除主表的记录
#方式一：级联删除

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;

4、创建表时添加约束

语法：create table 表名(
            字段名 字段类型 not null,#非空
            字段名 字段类型 primary key,#主键
            字段名 字段类型 unique,#唯一
            字段名 字段类型 default 值,#默认
            constraint 约束名 foreign key(字段名) references 主表（被引用列）)

注意：列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

	支持类型	可以起约束名
列级约束	除了外键	不可以
表级约束	除了非空和默认	可以，但对主键无效

5、修改表时添加或删除约束

• 添加非空：alter table 表名 modify column 字段名 字段类型 not null;
• 删除非空：alter table 表名 modify column 字段名 字段类型 ;

• 添加默认：alter table 表名 modify column 字段名 字段类型 default 值;
• 删除默认：alter table 表名 modify column 字段名 字段类型 ;

• 添加主键：alter table 表名 add【 constraint 约束名】 primary key(字段名);
• 删除主键：alter table 表名 drop primary key;

• 添加唯一：alter table 表名 add【 constraint 约束名】 unique(字段名);
• 删除唯一：alter table 表名 drop index 索引名;

• 添加外键：alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
• 删除外键：alter table 表名 drop foreign key 约束名;

6、自增长列

特点：①、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
                   如果要更改起始值：手动插入值：如果要更改步长：更改系统变量：set auto_increment_increment=值;
           ②、一个表至多有一个自增长列
           ③、自增长列只能支持数值型
           ④、自增长列必须为一个key

• 创建表时设置自增长列：create table 表(字段名 字段类型 约束 auto_increment)
• 修改表时设置自增长列：alter table 表 modify column 字段名 字段类型 约束 auto_increment
• 删除自增长列：alter table 表 modify column 字段名 字段类型 约束