MySQL复习day04：DML数据操作语言和DDL数据定义语言

DML开始

DML称为数据操作语言
插入：insert
修改：update
删除：delete

插入语句

• 语法
  方式一：insert into 表名（列名1，...） values（值1，...）
  或者
  方式二：insert into 表名 set 列名1=值1，列名2=值2, ...
  对比：
  方式一支持多行插入，方式二不支持；
  方式一支持子查询，方式二不支持；

  insert into family.boys(id,boyname)
  select 13,'detrimental';#可以是来自其他表的数据
  

• workbench小提示
  
  PK：primary key 主键
  NN：not null 非空
  UQ：unique 唯一索引
  BIN：binary 二进制数据(比text更大)
  UN：unsigned 无符号（非负数）
  ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001
  AI：auto increment 自增
  G: Generated Column 这一列由其他列计算而得

注意：不能够为null列，必须插入值，可以为null的列，可以填null，也可以跳过。

insert into boys # 向boys表中插入两行
select 5,'weird',23,'c',null,null
union 
select 6,'insult',24,'c',null,null;

修改语句

1. 修改单表的记录
   语法：
   update 表名
set 列 = 新的值，set 列 = 新的值，...
where 筛选条件；

    update family.boys
    set boys.boyname ='m_default'
    where family.boys.id =  10;
   

2. 修改多表的记录【级联更新】
   语法：
   1.sql92语法
   upadte 表1 as 别名，表2 as 别名
set 列=值, ...
where 连接条件
and 筛选条件
   2.sql99语法
   upadte 表1 as 别名
连接类型 join 表2 as 别名
on连接条件
set 列=值, ...

3. 注意
   如果数据库处于安全模式时，有可能会存在不能多表联合更改现象。
   解决办法
SET SQL_SAFE_UPDATES = 0;#执行该命令更改mysql数据库模式。

 update  family.boys  as b 
 right join zhutong  as z  on b.id = z.match_id
 set z.budget = 1500
 where b.boyname = 'wet' and b.id>0;

删除语句

1. 方法一：
   （1）语法:
   ①单表的删除
   delete from 表名 where 筛选条件 limit 条目起始，条目数
   ②多表的删除
   delete 表1的别名，表2的别名
from 表1 别名
【连接类型】join 表2 on 连接条件
where 筛选条件

delete from boys where boys.user_id is null;

2. 方法二：
   truncate table 表名 ;#清空数据，不包括列名，只是对数据的操作
   或者
   DROP TABLE 数据表名;#删除整个表，包括列名，这是DDL语言

3. 对比(👍)
   ①delete可以加where 条件，truncate不能加
   ②使用truncate删除，效率更高
   ③若要删除的表中有自增长列，如果用delete后，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值会从1开始
   ④truncate删除没有返回值，delete上删除有返回值，其返回的是我们修改的行数
   ⑤truncate删除不能回滚，delete删除可以回滚

DML结束

DDL语言开始

DDL语言称为数据定义语言，涉及库和表的操作。

• 库的管理：创建，修改，删除；
• 表的管理：创建，修改，删除；
  创建：create， 修改：alter，删除：drop ，#对结构的操作

库的管理

1. 库的创建
   语法：
   create database 【if not exists】库名；

   create database if not exists books; #创建books库
   

2. 更改字符集

   alter database books character set utf8;
   

3. 库的删除
   语法：
   drop database if exists 库名;

   drop database if exists books;
   

表的管理

1. 表的创建
   语法：
   create table [if not exists] 表名
(
列名1 列的类型[(长度） 列的约束] ,
列名1 列的类型[(长度） 列的约束] ,
...
列名1 列的类型[(长度） 列的约束]
)
   notes:
   通常在创建库和表之前，通常会将想创建的库名或表名执行drop database if exists 旧库名 或 drop table if exists 旧表名。

   create table if not exists authors(
   id int,
   aname varchar(255),
   nation varchar(45)
   );
   

2. 表的修改
   语法：
   alter table 表名 add/drop/modify/change column [列名 列类型 约束]；

   ① 修改列名

   alter table book change column publish_date  pubdate datetime;# 修改列名publish_date为pubdate
   

   ②修改列的类型（即约束）

   ALTER table book modify column pubdate timestamp; #修改pubdate列的类型为timestamp
   

   ③添加新列
   语法：alter table 表名 add column 类型 [first / after 字段名]

   alter table book add column annual double; #添加新列annual，类型为double
   

   可以约束新列的位置，使用first或者after 其他列名

   alter table tab_test add column nc int first; #将新列nc置于第一
   alter table tab_test add column nc1 int after t3; #将新列nc1置于t3之后
   

   ④删除列

   alter table book drop column annual ; #删除列名为annual的列
   

   ⑤修改表名

   alter table authors rename to book_author; #将author表名修改为book_author
   

3. 表的删除
   语法：
   drop table 表名

4. 展示库中的表

   show tables；
   

5. 表的复制
   ①仅复制表的结构
   语法：create table 新表名 like 旧表名；

   create table books.copy like books.authors;#仅仅复制表的结构
   

   ②复制表的结构和数据
   语法：create table 新表名 select * from 旧表名；

   create table books.copy select * from books.authors; #复制表的结构和数据
   

   ③只复制部分数据和结构
   create table 新表名 select 列名，... from 旧表名；

   create table books.copy select id,aname from books.authors;
   

常见数据类型

整型

	tinyint	smallint	mediumint	int	bigint
字节数	1	2	3	4	8

notes

1. 若要求数据为无符号整型，在其后加上unsigned。
2. 默认为有符号数据，数据的长度代表显示的最大宽度。

小数

1. 定点型

	float	double
字节数	4	8

2. 定点型
   dec(m,d)或者decimal(m,d) ### m代表整数位+小数位 d:小数位
   dec默认为dec(10,0)

   create table if not exists tab_float(
   	f1 float(5,2),
   	f2 double(5,2),
   	f3 dec(5,2)
   );
   insert into tab_float values(123.45,123.45,123.45);  ###结果为：123.45  	 123.45	   123.45
   insert into tab_float values(123.456,123.456,123.456);  ###结果为：123.46	123.46	 123.46
   insert into tab_float values(123.4,123.4,123.4); ###结果为：123.40		123.40		123.40
   

字符型

1. 较短的文本
   ①char(M)----表示最多的字符数为M，代表固定长度的字符型
   ②varchar(M)----表示最多的字符数为M，代表可变长度的字符型

   类型	m的含义	特点	空间耗费	效率
   char	最大的字符数	固定长度的字符，默认为1	比较耗费空间	高
   varchar	最大的字符数	可变长度的字符	比较节省	低

2. 较长的文本

   主要是text, blob（较大的二进制，存储图片数据等）。

枚举类型

枚举类型

create table if not exists tab_float(
	c enum ('a','b','c')
);
insert into tab_float values('b'); #允许插入a,b,c以及其大写字母

set类型

create table if not exists tab_float(
	c set ('a','b','c','e')
);
insert into tab_float values('a,B,C'); #不区分大小写，结果为：a,b,c
insert into tab_float values('b,E'); #不区分大小写，结果为：b,e

日期型

create table if not exists tab_tim(
	t1 datetime,#日期+时间
    t2 timestamp #日期+时间
);
insert into tab_tim values(now(),now()); #结果为：2020-10-31 22:24:26	2020-10-31 22:24:26
set time_zone ='+9:00';#设置时区为东九区，结果为：2020-10-31 22:24:26	 2020-10-31 23:24:26

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

常见约束

用于限制表中的数据，为保证数据的一致性。
六大约束：

1. NOT NULL:非空，用于保证该字段的值不能为空。
2. DEFAULT:默认。用于保证改字段有默认值。
3. PRIMARY KEY：主键，用于保证该字段的值具有唯一性，默认为非空。
4. UNIQUE：唯一，用于保证该字段的值具有唯一性，但是可以为空,甚至出现多个null。
5. CHECK：检查约束【mysql中不支持】。
6. FOREIGN KEY：外键，用于限制两个表的关系，用于保证改字段的值必须来源于主表的关联列的值。在从表添加外键约束，用于引用主表中的主键。

（一）添加约束的时机

1. 创建表
2. 修改表示

（二）约束的添加分类

1. 列级约束:外键约束没有实际效果
2. 表级约束:不支持非空和默认约束。

语法：

create table 表名(

		字段名 字段类型 列级约束，
		...
		字段名 字段类型，
		表级约束
);

①列级约束

create table if not exists stu(
	id int primary key,
    stuname varchar(25) not null,
    gender char(1) check(gender='男'or gender='女'),
    seat int unique,
    age int default 18,
    major_id  int , 
    constraint fk_stu_major foreign key  (major_id)  references major(id)  #外键约束语法
 );
 

②表级约束
语法：

[constraint 约束名] 约束类型（字段名）

create table if not exists stu(
	id int primary key,
    stuname varchar(25) not null,
    gender char(1) ,
    seat int unique,
    age int default 18,
    constraint pk primary key (id),
    constraint uk unique  (seat)
  );

primary key 和unique对比

1. primary key = unique + not null 主键不能为空每个字段值都不重复，unique可以为空，非空字段不重复。
2. unique 一个或者多个字段定义，primary key 单字段主键或多字段联合主键。
3. primary key和unique都可以复合。
4. 逻辑设计上primary key用来作记录标识，unique用来保证唯一性，但是在他们创建时都会去相应创建一个unique index，可以用来做sql优化。

外键

1. 要求在从表上设置外键关系；
2. 从表的外键列的类型和主表的关联列的类型要求一致或者兼容；
3. 主表的关联列必须具有唯一性。（主键或者唯一）；
4. 插入数据时，必须先插入主表，再插入从表；删除数据时，先删除从表数据，再删除主表数据。

修改表

语法

alter table 表名 add|modify|change|drop column 列名 类型 约束；
添加列
alter table 表名 add 约束（列名）；
删除列
alter table 表名 drop column 列名 ;
修改表名
alter table 表名 rename to 新表名;
修改列名
alter table 表名 change column 列名 类型 约束;
修改列的类型
alter table 表名 modify column 列名 类型 约束;

标识列

又称为自增长列，可以不用手动的插入值，系统提供默认的序列值。
关键词：auto_increment
特点：
①标识列不一定和主键搭配，可以和其他的key搭配；
②一个表中只能有一个自增长列；
③标识列的类型只能是数值型；

  create table if not exists stus.stu(
 	id int primary key auto_increment,
     stuname varchar(25) ,
    gender char(1) ,
    seat int ,
   age int 
  );
  #设置步长：
  set auto_increment_increment = 3;
  #设置起始值
  alter table stus.stu auto_increment = 4; #stus.stu为表名
  #修改表示设置标识列
  alter table stus.stu modify column id int primary key auto_increment;
  #注意如果已经是primary key时，
  # 语句改为：alter table stus.stu modify column id int  auto_increment;
  #删除标识列
  alter table stus.stu modify column id int primary key ;

复制表

仅仅复制表的结构

create table 新表名 like 表名;

复制数据+结构
create table 新表名 select * from 表名;

DDL结束

MySQL学习列表

MySQL复习day01：数据库简介和 DQL数据查询语言
MySQL复习day02：DQL数据查询语言续
MySQL复习day03：DQL数据查询语言完结
MySQL复习day04：DML数据操作语言和DDL数据定义语言
MySQL复习day05：TCL事务控制语言和视图
MySQL复习day06：变量,存储过程
MySQL复习day07：函数
MySQL复习day08：流程控制结构
MySQL复习day09：逻辑架构和引擎，索引详解（explain）

致谢

感谢诸君观看，如果感觉有用的话，点个赞吧!🎉

---