正在测试中,带子查询的添加、修改、删除还未完善(已写,未测试)。
MYSQL系列文章目录
3.2 学生-课程数据库
3.3.1 模式的定义(创建)与删除
3.3.2 数据库表的定义(创建)、删除与修改
3.3.3 索引的建立与删除
3.4 数据查询
数据更新-目录
使用Navicat for MySQL中的命令行查看MySQL版本 —— select version() from dual;
SQL的动词
SQL功能 | 动词 | ||
---|---|---|---|
数据查询 | select | ||
数据定义 | create | drop | alter |
数据操纵 | insert | update | delete |
数据控制 | grant | revoke |
SQL的数据定义语句
操作对象 | 操作方式 | ||
---|---|---|---|
操作对象 | 创建 | 删除 | 修改 |
模式 | create schema | drop schema | |
表 | create table | drop table | alter table |
视图 | create view | drop view | |
索引 | create index | drop index | alter index |
数据更新操作有三种:
向表中添加若干行数据
修改表中的数据
删除表中的若干行数据
数据更新思维导图
3.5.1、插入数据
如果into <表名> 后面有属性列,则values 子句只需要与属性列对应即可。如代码1、代码2、代码3
如果into <表名> 后面没有属性列,则values 子句需要与数据库里面的属性列一 一对应。如代码4、代码5
1. 插入数据
1.1 插入(一个)元组(数据)
school数据库中的Student设计表图
数据库school中的Student表的设计,可以点击这里进行查看。
insert
into <表名> [(<属性列1>[,<属性列2>]…)]
values (<常量1>[,<常量2>]…);
其中新元组的属性列1的值为常量1,属性列2的值为常量2,…
into子句中没有出现的属性列,新元组在这些列上将取空值。但必须注意的是,在表定义时说明了NOT NULL的属性列不能取空值,否则会出错。
- 代码1、代码2、代码3,指出了表名和属性名(列);
- 代码4、代码5,指出了表名,没有指出属性名(列);
- 代码1相对于代码5,是否指出属性名(列),然后属性名(列)将被赋于空值。
代码1如下(示例):关系数据库管理系统将在新插入记录的Sdept列上自动地赋空值。
没有出现Sdept属性列,属性值将取空值。
insert
into Student (Sno,Sname,Sage)
values ('202001012','张三',18);/*虽然没有出现Ssex属性列,但Ssex的默认值为'女',*/
运行代码1结果图
代码2如下(示例):
insert
into Student (Sno,Sname,Sage,Sdept)
values ('202001013','李四',19,'IS');
运行代码2结果图
表名后面的属性列,交换位置也可以。但属性列相对应的属性值需要一 一对应。 如代码3
在into子句中指出了表名Student, 并指出了新增加的元组在哪些属性上要赋值,属性的顺序可以与create table中的顺序不一样。
values子句对新元组的各属性赋值,字符串常数要用单引号(英文符号)括起来。
代码3如下(示例):
insert
into Student (Sno,Sname,Ssex,Sdept,Sage)
values ('202001014','王五','男','IS',18);
运行代码3结果图
代码4如下(示例):into子句中只指出了表名,没有指出属性名(列)。
insert
into Student
values ('202001015','张六','男',19,'MA');
运行代码4结果图
代码4中into子句中只指出了表名,没有指出属性名(列)。这表示新元组要在表的所有属性列上都指定值,属性列的次序与create table中的次序相同。
values子句对新元组的各属性列赋值,一定要注意值与属性列要一 一对应。
代码4.1如下(示例)
代码4.1运行报错的原因:没有与数据库里面的字段一 一对应。
代码4.1中的values子句,相对于代码4,这段代码会报错。
insert
into Student
values ('202001015','张六','男','MA',19);
/*成为('202001015','张六','男','MA',19),则含义是将'MA"赋予了列Sage,而19赋予了列Sdept,这样则会因为数据类型不匹配而报错。
*/
代码5如下(示例):into子句中只指出了表名,没有指出属性名(列)。
insert
into Student
values ('202001016','赵七','男',null,'MA');
insert into Student values ('202001016','赵七','男',null,'MA');/*这样也可以。*/
因为没有指出Student表的属性名(列),在Sage列上要明确给出空值。
运行代码5结果图
跨数据插入数据本质是(用use 数据库名;)找到数据库中的数据表,再添加数据库
1.1.1 跨数据库插入(一个)元组(数据)(后补充的,2张图片上下承接可以忽略)
方法一:
insert into school.student(Sno,Sname,Ssex,Sage,Sdept)/*school.表示选择school中的表*/
values ('202001016','李七','男',24,'IS');
select * from school.student;/*school.表示找到school数据库中的表*/
insert into school.student(Sno,Sname,Ssex,Sdept)/*school.表示选择school中的表*/
values ('202001016','李七','男','IS');
select * from school.student;/*school.表示找到school数据库中的表*/
方法二:
use school;/*找到school数据库,再插入数据*/
insert into student
values('202001017','刘八','男',23,'MA');
select * from school.student;/*school.表示找到school数据库中的表*/
use school;/*找到school数据库,再插入数据*/
insert into student
values('202001017','刘八','男',23,null);/*null表示Sdept为空*/
select * from school.student;/*school.表示找到school数据库中的表*/
1.2 插入多条记录
代码如下(示例):运行效果图如代码5结果图。
/*插入单条记录*/
insert
into Student
values('202001012','张三','女',18,null);
/*插入多条记录*/
insert
into Student
values('202001012','张三','女',18,null),
('202001013','李四','女',19,'IS'),
('202001014','王五','男',18,'IS'),
('202001015','张六','男',19,'MA'),
('202001016','赵七','男',null,'MA');
1.2.1 跨数据库插入多条记录(后补充的,下面1张图片上下承接可以忽略)
use school;/*寻找到对应的数据库*/
insert into student
values('202001018','李三','女',18,null),
('202001019','张四','男',19,'IS'),
('2020010110','刘五','女',20,'MA');
select * from student;
2. 插入子查询结果(可以插入多个元组)
insert
into <表名> [(<属性列1>[,<属性列2>]…)]
子查询;
[例3.72] 对每一个系,求学生的平均年龄,并把结果存入数据库。
首先在数据库中建立一个新表,其中一列存放系名,另一列存放相应的学生平均年龄。
create table Dept_age(Sdept char(15),
Avg_age smallint);
然后对Student表按系分组求平均年龄,再把系名和平均年龄存入新表中。
代码如下(示例):
insert
into Dept_age(Sdept,Avg_age)/*添加Sdept,Avg_age这两列数据到Dept_age表*/
select Sdept,AVG(Sage)/*从student表里面,按照系分组,查询这Sdept(系),Avg_age(平均年龄)这两列数据*/
from Student
group by Sdept;
3.5.2、修改数据
1.修改某一个元组的值
update <表名>
set <列名>=<表达式> [,<列名>=<表达式>] ···
[where <条件>];
其功能是修改指定表中满足where子句条件的元组。
其中set子句给出<表达式>的值用于取代相应的属性列值。
如果省略WHERE子句,则表示要修改表中的所有元组。
[例3.73] 将学生202001012的年龄修改为22岁。
代码如下(示例):
update student
set Sage=22
where Sno='202001012';
运行例3.73结果图
update student
set Ssex='女',Sdept='IS'
where Sno='202001016';
select * from student;/*查询student的数据*/
2.修改多个元组的值
[例3.74] 将所有学生的年龄增加1岁。
代码如下(示例):
update student
set Sage=Sage+1;
select * from student;/*查询student的数据*/
运行例3.74结果图
3.带子查询的修改语句
[例3.75] 将计算机科学系全体学生的成绩置零。
代码如下(示例):
update SC
set grade=0
where Sno in (select Sno from Student where Sdept='CS');
运行例3.75结果图
3.5.3、删除数据
1.删除某一个元组的值
delete
from <表名>
[where <条件>];
delete语句的功能是从指定表中删除满足where子句条件的所有元组。
如果省略where子句则表示删除表中全部元组,但表的定义仍在字典中。 也就是说,delete语句删除的是表中的数据,而不是关于表的定义。
truncate table 表名;
没有测试过。
有时候我们会需要清除一个表格中的所有资料。
要达到这个目的,一种方式是DROP TABLE 指令。不过这样整个表格就消失,而无法再被使用了。
另一种方式就是运用 TRUNCATE TABLE 的指令。在这个指令之下,表格中的资料会完全消失,可是表格本身会继续存在。
[例3.76] 删除学号为202001016的学生记录。
代码如下(示例):
delete
from student
where Sno='202001016';
select * from student;/*查询student的数据*/
delete from student where Sno='202001016';/*这样也可以。*/
select * from student;/*查询student的数据*/
运行例3.76结果图
2.删除多个元组的值
[例3.77] 删除所有的学生选课记录。
代码如下(示例):
delete
from student;
delete from student;/*这样也可以*/
这条delete语句将使Student成为空表,它删除了Student的所有元组(数据)。Student表并没有被删除。
3.带子查询的删除语句
[例3.78] 删除计算机科学系所有学生的选课记录。
代码如下(示例):
delete
from SC
where Sno in (select Sno from Student where Sdept='CS');