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;
修改语句
-
修改单表的记录
语法:
update 表名
set 列 = 新的值,set 列 = 新的值,...
where 筛选条件;
update family.boys set boys.boyname ='m_default' where family.boys.id = 10;
-
修改多表的记录【级联更新】
语法:
1.sql92语法
upadte 表1 as 别名,表2 as 别名
set 列=值, ...
where 连接条件
and 筛选条件
2.sql99语法
upadte 表1 as 别名
连接类型 join 表2 as 别名
on连接条件
set 列=值, ...
-
注意
如果数据库处于安全模式
时,有可能会存在不能多表联合更改现象。
解决办法
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)语法:
①单表的删除
delete from 表名 where 筛选条件 limit 条目起始,条目数
②多表的删除
delete 表1的别名,表2的别名
from 表1 别名
【连接类型】join 表2 on 连接条件
where 筛选条件
delete from boys where boys.user_id is null;
-
方法二:
truncate table 表名 ;
#清空数据,不包括列名,只是对数据的操作
或者
DROP TABLE 数据表名;
#删除整个表,包括列名,这是DDL语言 -
对比(👍)
①delete可以加where 条件,truncate不能加
②使用truncate删除,效率更高
③若要删除的表中有自增长列,如果用delete后,自增长列的值从断点
开始,而truncate删除后,再插入数据,自增长列的值会从1
开始
④truncate删除没有返回值,delete上删除有返回值,其返回的是我们修改的行数
⑤truncate删除不能回滚,delete删除可以回滚
DML结束
DDL语言开始
DDL语言称为数据定义语言
,涉及库和表的操作。
- 库的管理:创建,修改,删除;
- 表的管理:创建,修改,删除;
创建:create, 修改:alter,删除:drop ,#对结构的操作
库的管理
-
库的创建
语法:
create database 【if not exists】库名;
create database if not exists books; #创建books库
-
更改字符集
alter database books character set utf8;
-
库的删除
语法:
drop database if exists 库名;
drop database if exists books;
表的管理
-
表的创建
语法:
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) );
-
表的修改
语法:
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
-
表的删除
语法:
drop table 表名
-
展示库中的表
show tables;
-
表的复制
①仅复制表的结构
语法: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
- 若要求数据为无符号整型,在其后加上
unsigned
。 - 默认为有符号数据,数据的长度代表显示的最大宽度。
小数
- 定点型
float | double | |
---|---|---|
字节数 | 4 | 8 |
- 定点型
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
字符型
-
较短的文本
①char(M)----表示最多的字符数为M,代表固定长度的字符型
②varchar(M)----表示最多的字符数为M,代表可变长度的字符型类型 m的含义 特点 空间耗费 效率 char 最大的字符数 固定长度的字符,默认为1 比较耗费空间 高 varchar 最大的字符数 可变长度的字符 比较节省 低 -
较长的文本
主要是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 | 受 |
常见约束
用于限制表中的数据,为保证数据的一致性。
六大约束:
- NOT NULL:非空,用于保证该字段的值不能为空。
- DEFAULT:默认。用于保证改字段有默认值。
- PRIMARY KEY:主键,用于保证该字段的值具有唯一性,默认为非空。
- UNIQUE:唯一,用于保证该字段的值具有唯一性,但是可以为空,甚至出现多个null。
- CHECK:检查约束【mysql中不支持】。
- FOREIGN KEY:外键,用于限制两个表的关系,用于保证改字段的值必须来源于主表的关联列的值。在从表添加外键约束,用于引用主表中的主键。
(一)添加约束的时机
- 创建表
- 修改表示
(二)约束的添加分类
- 列级约束:外键约束没有实际效果
- 表级约束:不支持非空和默认约束。
语法:
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对比
- primary key = unique + not null 主键不能为空每个字段值都不重复,unique可以为空,非空字段不重复。
- unique 一个或者多个字段定义,primary key 单字段主键或多字段联合主键。
- primary key和unique都可以复合。
- 逻辑设计上primary key用来作记录标识,unique用来保证唯一性,但是在他们创建时都会去相应创建一个unique index,可以用来做sql优化。
外键
- 要求在从表上设置外键关系;
- 从表的外键列的类型和主表的关联列的类型要求一致或者兼容;
- 主表的关联列必须具有唯一性。(主键或者唯一);
- 插入数据时,必须先插入主表,再插入从表;删除数据时,先删除从表数据,再删除主表数据。
修改表
语法
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)
致谢
感谢诸君观看,如果感觉有用的话,点个赞吧!🎉