mysql总结分享之DML进阶、分页查找、SQL约束及多表 *** 作

mysql总结分享之DML进阶、分页查找、SQL约束及多表 *** 作 本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于DML进阶、分页查找、SQL约束、多表 *** 作学习的相关问题，下面一起来看一下，希望对大家有帮助。

推荐学习：mysql视频教程

一. 什么是DML, 以及DML基本 *** 作, 表的列和行的跟新 *** 作

• 针对列进行修改 *** 作

#首先简单的创建一个student表为后序 *** 作做准备
use test;
create table student (
	id int,
	name varchar(8),
	age tinyint
) engine = innodb default charset = utf8mb4;
desc student;

• 添加新的列, 格式: alter table 表名 add 新列名 数据类型(长度);

alter table student add addr varchar(20);
#新增一个addr列出来

• 修改列的数据类型(长度), 格式: alter table 表名 modify 列名 修改后的数据类型(长度);

alter table student modify addr varchar(15);
#修改student表中addr列的数据类型 (长度修改)
alter table student modify addr char(20);
#修改student表中addr列的数据类型 (类型修改为char(20))

• 修改列的名称, 格式: alter table 表名 change 列名 新列名 新列名的数据类型(长度);

alter table student change addr stu_addr varchar(20);
# change 相比 modify 而言功能更加强大可以修改列名字. 
# modify不可以修改列名

• . 删除指定列, 格式: alter table 表名 drop 列名;

alter table student drop stu_addr;
# 删除student表中的stu_addr列

• 针对上述对于表的列结构的修改 *** 作而言都是不建议使用的，因为很多公司的数据库是特别的大的, 修改一列数据都不是一个小事情, 修改不好造成了数据丢失就糟了，

• 针对表的行,表记录的各项 *** 作 (增删改查)

插入表记录

• 方式一, 对指定的字段插插入, 格式: insert into 表名(字段1, 字段2, ...) values (值 1, 值2, ...); 我称呼这种方式叫做指定字段的插入 *** 作, 字段和赋值要一一对应

insert into student(id, name, age) values(1000, '张三', 18);
# 向student表中插入一条id 为1000 name 为张三, age 18的记录

insert into student(id, name, age)
values(1001, '李四', 20),
(1002, '王五', 22),
(1003, '胖子', 25);
#还可以支持values后面跟上多条记录
#每条记录之间使用,隔开

• 方式2, 对所有字段插入值, 格式: insert into 表名 values(值1, 值2, ...);
• 就是说我们默认全部字段都顺序插入，可以不用写上字段

insert into student
values(1004, '李四他爸', 47),
(1005, '王五它妈', 40),
(1006, '胖子它老特', 20);
#可以向这样不指定任何字段,默认顺序插入所有字段

小结插入 *** 作注意事项：

1. 值与字段必须对应, 个数相同, 类型相同
2. 值的数据大小必须在字段的指定长度范围内
3. 除了整数\小数类型外, 其他字段类型的值必须使⽤引号引起来 (建议单引号)
4. 如果要插⼊空值, 可以不写字段, 或者插入null

更新表记录

• 语法格式: update 表名 set 字段1=值, 字段2=值... where 条件;

update student set name = '胖子他爹' where id = 1005;
# 跟新student表中id = 1005这条记录的name为胖子他爹

update student set name = '我是你爸', age = 100 where id = 1005;
# 跟新student表中id = 1005这条记录的name为我是你爸, age为100

小结跟新记录 *** 作注意事项:

1. 列名的类型与修改的值要一致
2. 修改值时不能超过字段的长度范围
3. 除了整数\小数类型外, 其他字段类型的值必须使用引号扩起来

删除表记录

• 语法格式: delete from 表名 where 条件;

delete from student where id = 1005;
# 从student 表中删除id=1005这条记录

delete from student where name = '胖子'
# 从student 表中删除name=胖子这条记录

记录的增删改查 *** 作还是有好好练习的必要性的, 因为关于数据库特定记录的修改删除增添等 *** 作还是蛮常见的

解决分页查找的问题

• 上面这个是什么东西? 相信喜欢搜索记录或者购物的各位并不陌生, 这种显示记录的方式就是分页显示记录 (分页查找)
• 分页查询格式: select * from 表名 limit startRow, pageSize;

#模拟这样一个场景, 每一页5条数据
select * from student limit 0, 5;  # 第一页
select * from student limit 5, 5;  # 第二页
select * from student limit 10, 5; # 第三页

• 抛出问题，此时我们都知道了每一页的数据条数，页大小固定，问题是我们如果根据需要查询的页数确定 startRow?

-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下列是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算

• 其实我们仅仅只是需要知道当前页数 (页数 - 1) * pageSize; 即可获知startRow

三. SQL约束详解

• 约束的定义
• 竟然需要学一下约束，首先我们先搞定啥叫约束，其实还蛮简单的，约束就是⼀种限制条件, 让你不能超出这个控制范围
• 而在数据库中的约束, 就是指 表中的数据内容 不能胡乱填写, 必须按照要求填写. 好保证数据的完整性与 安全性
• 主键约束 PRIMARY KEY 约束
• 啥是主键约束：不为空的唯一约束. 主键约束不为NULL, 且唯一标识一条记录, 每一个表几乎都必须存在这样一个约束条件

添加主键约束

• 方式1：创建表时，在字段描述处，声明指定字段为主键：
• 格式: 字段名 数据类型[长度] primary key;

create table user_table(
	id int primary key, #添加主键约束
	name varchar(10),
	age tinyint
) engine = innodb charset = utf8mb4;

• 主键约束唯一标识记录, 且不可以为空

insert into user_table
values(1001, '翠花', 18);
#插入第一条记录翠花是没有问题的
insert into user_table
values(1001, '王五', 20);
#插入这条记录应当是报错, 重复插入主键了
# [Err] 1062 - Duplicate entry '1001' for key 'PRIMARY'
# 重复加入1001 作为主键

• 主键约束不可以为空 (区别unique 主键)

insert into user_table
values(null, '大胖', 30);
# 区别唯一约束, 主键约束不可以为null
#[Err] 1048 - Column 'id' cannot be null

• 方式2：创建表时，在constraint约束区域，声明指定字段为主键
• 语法形式： [constraint 名称] primary key (字段列表)
• 出现的必要是什么? 这种方式出现的必要就是可以添加联合主键, 具体联合主键的使用回在下文中的中间表处应用, 此处我们先获悉如何创建

create table persons (
	pid int,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	constraint persons_pk primary key(lastname, firstname) 
	#通过constraint 增添联合主键
) engine = innodb default charset = utf8mb4;

• 思索一下为何需要出现联合主键这一约束, 主键必须是标识不同的记录, 有些时候存在这样一种情况, 我们需要用 姓名 + 性别, 来辨识不同的对象一样 (不巧存在男生女生都叫王玉杰的情况, 仅姓名无法区分, 此时可以联合其他字段共同构成主键来约束标识)
• 方式三：创建表之后，通过修改表结构，声明指定字段为主键：
• 格式： altertable 表名 add [ constraint 名称] primary key (字段列表)

alter table user_table 
add constraint name_id_pk primary key(name, id);
# 向user_table表中增加一个name + id的联合主键

删除主键约束

• 格式: alter table 表名 drop primary key;

alter table user_table drop primary key;
# 删除user_table表中的主键约束

自动增长列 (介绍主键约束如何离得开它)

• 我们通常希望在每次插⼊新记录时，数据库自动生成字段的值
• 又特别是主键字段, 如果仅作为标记记录，完全没必要我们设置值呀
• 我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长 列必须为键(通常是用于主键)

格式: 字段名 整数类型[长度][约束] auto_increment

create table test(
	id int primary key auto_increment,
	# 添加一个主键约束, 设置自动增长. 默认增长为1
	age tinyint,
	name varchar(20)
) engine = innodb default charset = utf8mb4;

insert into test values(null, 18, '小呼噜');
# 我们设置了主键自动递增可以不再需要传入主键字段
# 或者主键传入null 他会自动设置从1开始默认增量1

• 亦或是 insert into 的时候不传入任何东西都OK， null也可以不用传入

• 不过如果想要同上述这般使用我们必须注意的就是要指定字段插入, 不然默认是三个都要给值, 我们指定插入字段的时候可以无需指定id, 有点像默认值
• 非空约束
• NOT NULL 约束: 列不接受 NULL 值。 要求字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录.

添加非空约束

• 格式: 字段名 数据类型[长度] NOT NULL

drop table test;
create table test(
	id int primary key auto_increment,
	name varchar(10) not null,#设置非null 插入数据不能传入null
	age tinyint
) engine = innodb auto_increment = 10 default charset = utf8mb4;
# 我们还可以向这般指定auto_increment的值

insert test values(null, null, 28);
# 会出错, 第二个字段增加了not null 约束
# 传空会报错[Err] 1048 - Column 'name' cannot be null

删除非空约束

• 格式: alter table 表名 modify 字段名 数据类型[长度]

alter table test modify name varchar(10);
# 非常简单的方式, 直接更改数据类型的不加null约束即可
desc test;

• 唯一约束
• unique 约束: 指定列的值 不能重复.

注意:

1. UNIQUE 和 PRIMARY KEY 约束均为列提供了唯一性的保证。PRIMARY KEY 是自动定义的 UNIQUE 约束。
2. 每个表可以有多个 UNIQUE 约束，但是每个表只能有⼀个 PRIMARY KEY 约束。
3. UNIQUE 不限制 null 值 出现的次数

添加唯⼀约束

• 与主键添加方式相同，共有3种. 我在此处举几个例子就是

drop table test;
create table test (
	id int unique,	# 添加一个唯一约束
	name varchar(20) not null,
	age tinyint
) engine = innodb default charset = utf8mb4;
desc test;

insert into test values(null, '张三', 19);
# 允许传入null  区别primary key
insert into test 
values(1, '李四', 30),
(1, '王五', 38);  
#报错[Err] 1062 - Duplicate entry '1' for key 'id'

• 格式2： [constraint 名称] UNIQUE (字段) 对应primary key 方式2
• 格式3: ALTER TABLE 表名 ADD [CONSTRAINT 名称] UNIQUE (字段) 对比方式3

删除唯一约束, 方式一样跟刚刚删除主键约束

• 默认约束
• default 约束: 用于指定字段默认值。 当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值
• 添加默认约束，在创建表时候添加 格式: 字段名 数据类型[长度] DEFAULT 默认值

CREATE TABLE persons (
 pid INT,
 lastname VARCHAR(255),
 firstname VARCHAR(255),
 address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
)engine = innodb default charset = utf8mb4;

# 传入null 则会按照default 赋值
insert into persons(pid, lastname, firstname) 
values(2, '德华', '刘');
# 或者指定字段, default字段可以不用传入val

小结

• 关于表的列 *** 作 (增删改查) 开头alter 关键字 后面add modify change drop

alter table 表名 add 列名 类型(长度) 新增一列

alter table 表名 modify 列名 oldtype newtype 针对一列仅仅只做类型修改

alter table 表名 change old列名 new列名 oldtype newtype 针对一列可做类型 + 列明修改

alter table 表名 drop 列名; 针对一列做删除 *** 作

• 关于表的记录 *** 作 (增删改查)

insert into 表名(指定字段) values(指定值), (指定值); 指定插入字段值 (插入记录)

insert into 表名 values(所有字段顺序写入值); 按照建表字段顺序插入字段值

update 表名 set 字段 = 值 where 条件指定记录 更改记录

delete from 表名 where 条件指定记录 从指定表中删除满足条件的记录

• 关于各种约束的学习

约束就是一种限制

主键约束 (相当于是 unique 约束 + 非 null约束的结合), 用来唯一标识表中的记录

unique 约束, 也是保持不可重复， 列字段值唯一, 但是允许为null

非 null 约束. 就是不允许为null 不可以传入null作为参数

默认约束, 如果传入null 就默认字段值为初始默认值

朋友们，虽然今日介绍的东西不难, 但是简单的东西我们需要下细的掌握, 可以的话还是尽量测试一下, 祝大家学业有成，工作升职加薪

推荐学习：mysql视频教程

以上就是mysql总结分享之DML进阶、分页查找、SQL约束及多表 *** 作的详细内容，