三、修改数据
update 表名 set 列名 = 值... where 条件;
注意事项:
① 在进行修改操作时,我们最好加上 where 条件,因为这针对了指定哪些条件进行修改,若省略了 where,那么就是对所有行都进行了修改。如下所示:
② update 是会修改数据库服务器上面的原始数据的 !
所以说,此处的修改操作也是比较危险的,甚至比误删数据库来的更加危险,因为误删数据库可以通过备份的数据来恢复,而修改数据的时候,有些可能改对了,有些可能改错了,所以在工作中不好控制,程序员很难控制结果。
四、删除数据
delete from 表名 where 条件;
删除 null 的时候,我们需要使用 【 where 列名 is null 】
注意:如果我们使用 delete 的时候,没有在代码末尾加上 where 条件,那么它会删除整个表中的数据,但是表还在,剩余的就是一张空表,而这与 【 drop table 表名; 】不一样,drop 操作是把表和数据都删除了。
五、数据库的约束
约束就是数据库在使用的时候,对于里面能够存的数据提出的要求和限制,程序员就能借助约束来完成更好的校验。
1. default 默认值
约定 / 改变一个默认值。
2. not null 不为空
指定某列不能存储空值。
插入空值的时候,会提示报错 !
3. unique 唯一值
数据唯一,插入重复的数据,会报错。
4. primary key 主键
primary key 相当于是 not null 和 unique 两者的结合体。它相当于数据的唯一身份标识,类似于身份证号码 / 手机号码。
primary key 这个约束是我们日常开发的时候,最常用,也是最重要的约束 ! 创建表的时候,很多时候都需要用到主键。
注意事项:
对于一个表来说,只能有一个列被指定为主键。
关于主键,典型的用法,就是直接用 1,2,3,4 整数递增的方式来进行表示。
在MySQL 中,把这种递增的主键,称为【 自增主键 】。
id int primary key auto_increment
当设定好自增主键之后,此时插入的记录,就可以不指定自增主键的值了,(直接使用 null),交给 MySQL 自动分配。
我们自己也可以指定自定义的值,但指定之后,会从我们指定的值之后开始递增。
5. foreign key 外键
foreign key (子表列名) references 主表(主表列名); -- 上面代码一般写在某行代码最后
(1) 演示:我们创建两张表,一张表是 student,一张表是 class,我们使用外键的目的是将学生和班级对应起来。
表 class RoomID Name 1 普通班 2 重点班 3 英才班 4 竞赛班
表 student StudentID Name Class 1 Jack 3(英才班) 2 Rose 1(普通班) 3 Ron 2(重点班)
① 表 class 对应的 SQL:
create table class ( RoomID int primary key auto_increment, Name varchar(20) );
② 表 student 对应的 SQL:
create table student ( studentID int primary key auto_increment, Name varchar(20), Class int, foreign key (Class) references class(RoomID) -- 表示 子表student 中的 [列Class] 引用 父表class 中的 [列RoomID] );
外键约束:描述的是两张表的两个列之间的 " 依赖关系 ",子表依赖于父表(子表引用于父表),要求子表中对应的每一行必须在父表中存在。
(2) 一些细节
① 学生可以在不同的班级中,学生也可以在相同的班级中。但学生不能在未定义序号的班级中,所有的班级序号只包括了【1, 2, 3, 4】,你不能将学生放入第 20班中。
下面有插入成功的情况,也有插入失败的情况,插入失败,会报错 【 constraint fails 】,表示约束失败。
② 我们可以改变学生的班级,当然学生的班级依旧需要对应父表中班级存在的序号 !
③ 外键约束同样约束父表,当父表中的某一行被子表中依赖着的时候,此时进行删除或修改操作,都会失败。当然,直接删除班级表,也会发生失败哦 !
另外,我们可以删除子表都不实现父表的记录,比方说:所有的学生都在【1, 2, 3, 4】班,而现在我们新创建了一个 5班,但是 5班没有学生,我们就可以删除 5班。
(3) 外键约束的工作原理:
在子表中插入新的记录时,就会先根据对应的值,在父表中先查询,查询到之后,才能够执行后续的插入。这里的查询操作,可能是一个成本较高的操作( 比较耗时 )。
外键约束要求:父表中被依赖的这一列,必须要有索引,有了索引就能大大地提高查询速度。 而在上面的 class 表中的 RoomID 这一列,若是 primary key 或 unique,就会自动创建出索引了。
六、查询后新增
insert into B select 列名,列名... from A;
理解代码含义:将从A 表中查询的临时表中的数据插入至B 表中,insert 插入的时候依旧要符合B 表中的数据类型。
七、聚合查询
聚合函数
1. count( ) 函数
create table exam ( ID int, Name varchar(20), Chinese decimal(3,1), Math decimal(3,1), English decimal(3,1) ); insert into exam values (1,'杰克', 67, 98, 56), (2,'露丝', 87.5, 78, 77), (3,'布鲁斯', 88, 98.5, 90), (4,'乔治', 82, 84, 67), (5,'詹姆斯', 55.5, 85, 45), (6, null, 75, 95.5, 73), (7,'罗恩', 70, 73, 78.5);
利用 count 函数的时候,当数据为 null 的时候,不计入结果。在上述中名字有 7 个数据,但是有一个数据为 null,所以输出 6.
此外,使用聚合函数的时候也可以加上 where 条件。形如如下操作,会先执行条件筛选,然后再执行聚合。
2. sum( ) 函数
sum( ) 函数 对于数据的求和如下,它十分类似于 Excel 表的操作。
这里注意:sum( ) 函数对字符串进行操作时,显示无效。
3. group by
create table company( id int primary key auto_increment, name varchar(20) not null, role varchar(20) not null, salary numeric(11,2) ); insert into company(name, role, salary) values ('Jack','项目负责人', 1800.20), ('Ron','项目负责人', 2000.99), ('Rose','员工', 999.11), ('George','董事长', 3333333.5), ('Bruce','员工', 700.33), ('Lin','总经理', 12000.66), ('Chen','员工', 888.0);
举个例子,我们现在对一家公司的职位和对应的薪资做一个调查,我们以 role 来将公司职位进行分组,然后通过聚合函数【max( ), min( ) ,avg( )】来显示一些数据。
在下面的例子中,我们需要明确:程序先执行 group by 分组,再根据分出来的组别来使用聚合函数。所以说:一个 SQL 语句的执行顺序,与其书写顺序并不完全一致。
注意 where 语句和 having 语句的区别:
where 语句在 【group by】的前面执行,having语句在 【group by】的后面执行。
如下图的例子
例子1:在通过 role 分组之前,我们先把董事长这个职位去掉。
例子2:在通过 role 分组之后,我们找出平均薪水大于 2000 的数据。
八、其他的查询
1. 自连接
自连接的关键在于:通过自己与自己进行笛卡尔积运算,能够将行转换成列。
【显示所有 " 计算机原理 " 比 “Java” 成绩高的数据。】
如下图,我们比较的是行与行,而以往我们比较的是列与列。所以,我们只能先将行转换成列。
步骤(1)
让 score与自己形成自连接,但由于自连接之后的这张表是 两张相同的 score 表形成的,所以我们必须使用指定别名操作。
步骤(2)
设置条件,将左表都设置成 " 计算机原理 " 的成绩。
将右表都设置成 “Java” 的成绩。
步骤(3)
再次设置条件,将左表的成绩大于右表的成绩打印出来即可。
2. 子查询
学习目标:认识并了解即可
查询1:【单行子查询,查询与 " 许仙 " 同班的同学】
正常写法:
子查询写法:
查询2:【多行子查询 " 语文 " 或 " 英文 " 课程的成绩信息】
正常写法:
子查询写法:
总结: 子查询实际上就是一个套娃的过程,子查询完全可以分开来写,有时候直接一步到位会让代码可读性变得更差,且更容易出错。( 特别是当今时代,数据库的数据就是钱,钱就是数据,一切应该谨慎一些才好 )
3. 合并查询
学习目标:认识并了解即可
union -- 该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。 union all -- 该操作符用于取得两个结果集的并集。当使用该操作符时,不会自动去掉结果集中的重复行。
使用 union 时,需要两张合并查询后的临时表需要有相同的列数,否则会报错。