SQL数据表的操作（DML）

一个叫卡卡的球员

已于 2024-08-10 15:00:01 修改

阅读量1k

点赞数 41

分类专栏：数据库文章标签： sql 数据库

于 2024-08-10 14:35:44 首次发布

本文链接：https://blog.csdn.net/qq_45008212/article/details/141087619

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

此文档是数据分析课程中的数据库第一阶段课程知识点的复习文档，本阶段课程目标是通过实战练习巩固常用的SQL语句，基于工作需要和个人实际，DML相关知识点仅需要熟悉和了解基本概念和常用的语法

复习时间：2024年8月9日

文档总结：2024年8月10日

复习时长：一天

课程环境：

win10系统
mysql community 8.0.23.0
Navicat Premium 12.0.29

本次课程作业主要涉及的知识点：

数据表设计的基本规范
数据表的常用操作
数据表的创建
数据表的查看（查看表结构）
数据表的删除（删除表和删除表的内容）
数据表结构的修改（修改表名称、添加新列、修改表中列的数据类型或长度、修改列名、删除列）
数据表数据的修改
数据表的约束

关于DML的复习总结：

1、关于数据表设计的注意点：

创建数据库时，数据库名及后期表名都应当遵守MySQL的标识符命名规则：

由字母、数字、下划线组成，
第一个字符必须是字母或下划线（数字不能用在开头）
不能是MySQL的关键字
不要取中文名

一个数据库中可以n个表，表中又有字段（列），在建表的时候就需要定义好字段（指定字段属性，比如字段数据类型+字段长度等）
对已有表做更改既复杂又不统一，理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动
数据库名、表名和字段名，建议统一使用小写字母，避免出现不必要的问题

在阿里巴巴JAVA开发手册的 MySql 建表规约里提到：

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

通俗的说就是，MySQL 在 Windows 系统下不区分大小写，但在 Linux 系统下默认区分大小写。因此，数据库名、表名和字段名，都不允许出现任何大写字母，避免节外生枝。

一般建议统一使用小写字母，并且 InnoDB 引擎在其内部都是以小写字母方式来存储数据库名和表名的，这样可以有效的防止 MySQL 产生大小写问题

2、如何防止误删有用的表

强制实施有关规则，防止删除与其他表相关联的表，在实施这些规则时，如果对某个表发布一条DROP TABLE语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止。
在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

3、关于drop和delete使用场景

删除结构性的东西，用drop（比如删除一个数据库或者数据表）
删除内容性的东西，用delete（比如删除某个表里的具体内容（结构不删，delete之后，数据类型等结构信息还会保留））

4、关于delete和truncate删除的区别

区别	delete	truncate
底层实现	有多少条记录，就执行多少次删除操作	先删除整张表，然后再重新创建一张一模一样的表
插入数据的断点开始数	delete删除后，如果再插入，标识列从断点开始	truncate删除后，如果再插入，标识列从1开始
是否可以添加筛选条件	delete可以添加筛选条件	truncate不可以添加筛选条件
效率	delete效率较低	truncate效率较高
返回值	delete可以返回受影响的行数	truncate没有返回值
是否可以回滚	delete可以回滚	truncate不可以回滚

5、关于插入语句的注意事项

要求值的类型和字段的类型要一致或兼容
字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应
值的数据大小，必须在字段指定的长度范围内
varchar char date类型的值必须使用单引号包裹
假如表中有可以为null的字段，如果要插入空值，可以字段和值都都忽略不写，或者写字段，值插入null
如果插入指定字段的值，必须要上写列名

6、常用约束的简单总结

create table 表名(
	字段名 字段类型 not null, -- 非空
	字段名 字段类型 primary key, -- 主键
	字段名 字段类型 unique, -- 唯一
	字段名 字段类型 default 值,-- 默认
	constraint 约束名 foreign key(设置外键的字段名) references 主表(被引用列)
)

约束	作用
主键primary key	1、唯一的标识表里的每一条记录； 2、主键字段既不能为空，也不能重复 3、一个表中至多一个主键
非空not null	字段不能为空
唯一unique	1、字段取值不能重复，但可以为空 2、一个表可以有多个唯一约束 3、对null不做唯一的判断（设置唯一约束的列是允许有空值的）
默认值default	1、没有给该字段赋值时，它使用默认值； 2、如果给该字段赋值了，就使用实际赋的值；
外键foreign key	1、设置了外键约束字段的取值跟主键之间就有一个参照关系，即从表的外键的取值要参照主键的取值 2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求 3、主表的被引用列要求是一个key（一般就是主键） 4、向从表中插入的数据，必须先在主表存在

一阶段复习重点关注：

DML语句相关操作的关键字掌握，主要是掌握表结构的创建（create）与删除（drop），掌握表内容的插入（insert）与更新（update），其他关键字仅做了解即可
表约束的概念掌握，目标是能够了解常用的约束，包括：约束的类型、约束之间的关联与差异、约束的语法结构

复习过程中的薄弱点与易错点：

1、update语句不熟练，特别是单条数据插入与多条数据的插入，脱离文档常写常错，以下是常用的insert写法：

-- 方式1： 插入全部字段， 将所有字段名都写出来
insert into 表名(字段1,字段2,……,字段n)  
        values(值1,值2,……,值n);

-- 方式2：插入全部字段，不写字段名
insert into 表名 
    values(值1,值2,……,值n);

-- 方式3：插入指定字段的值
insert into 表名(指定的字段名) 
      values(对应字段的值);

-- 插入语句支持查询
insert into 表名
查询语句;

2、约束，特别是外键约束不够熟练，不熟悉基本的语法结构：

设置外键约束的语法：

constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)

本阶段文档总结情况：

第一部分：表的创建，关键字：create

1、创建表，关键字：create

create table [if not exists] 表名(
    字段名 字段类型 [约束，非必要],
    字段名 字段类型 [约束，非必要]
)

【if exists】的作用：存在就删除，不存在不处理

如果数据库里没有对应的表（比如删除之后，再次执行了删除语句，此时数据库里已经没有这张表了）会有报错提示：Unknown table

说明1：

为避免这种报错，可以在删除语句里加上：if exists

说明2：

如果不加if exists，当库名不存在时，运行报错也是一种必要的提示

2、通过复制表结构创建表，关键字：like

create table 新表名 like 旧表名;

复制表结构创建表一种快捷创建表的方式

快速创建一个表结构相同的表，通过复制表结构实现

与表结构相同，是指：表中的字段名一样，字段对应的数据类型一样，但不考虑表里具体的数据

第二部分：查看表，关键字：show/desc

1、查看当前数据库中的所有表名

show tables;

2、查看数据表的结构

通过查看表结构，验证创建的新表是否与之前的表具有相同的结构：可以通过 desc 新表名实现

desc 表名;

数据表和数据库查看结构的语法是类似的
需求	关键字	数据库	数据表
查看结构	desc 库名/表名;	desc 库名;	desc 表名;
查看有哪些	show 库名/表名;	show databases;	show tables;
查看SQL语句	show create 库名/表名;	show create 库名;	show create 表名;

第三部分：表的删除，关键字：drop

1、删除表（从数据库中永久删除某一张表）

drop table 表名；

2、判断表是否存在，存在的话就删除，不存在就不执行

drop table if exists 表名；

第四部分：表结构的修改，关键字：alter

1、修改表名，关键字：rename

alter table 旧表名 rename to 新表名;

2、向表中添加新列，关键字：add

alter table 表名 add 字段名称 字段类型;

3、修改表中列的数据类型，关键字：modify

alter table 表名 modify 字段名称 字段类型;

4、修改表中列的名称/字段的长度，关键字：change

alter table 表名 change 旧列名 新列名 类型(长度);

5、删除表中的列，关键字：drop

alter table 表名 drop 列名;

第五部分：表内容的修改，关键字：增insert、删delete、改update

1、插入数据，关键字：insert

插入数据的三种方式

方式1： 插入全部字段，将所有字段名都写出来

insert into 表名(字段名,...) values(值,...);
insert into student(sname,sgender,sbrithday,clsid) 
values("金毛狮王","男","1960-09-06",1)
	 ,("杨逍","男","1970-06-06",2)
	 ,("灭绝师太","女","2003-10-19",3);
方式2：插入全部字段，不写字段名

insert into 表名 values(值1,值2,……,值n);
INSERT into classes VALUES(1,"超越班"),(2,"火箭班"),(3,"先驱班")
方式3：插入指定字段的值

insert into 表名(指定的字段名) values(对应字段的值);
insert into student(sname) values("张三");

2、删除表中的数据，关键字：delete/truncate

语法格式1：删除表中所有数据

delete from 表名
delete from student;
语法格式2：指定条件删除数据

delete from 表名 [where 字段名 = 值]
delete from student where sname = "杨逍";
删除表中的所有数据的两种做法对比
删除方法说明
delete from 表名; 有多少条记录，就执行多少次删除操作，效率低，不推荐
truncate table 表名; 先删除整张表, 然后再重新创建一张一模一样的表，效率高，推荐
-- 有多少条记录，就执行多少次删除操作，效率低，不推荐
delete from 表名;
-- 先删除整张表, 然后再重新创建一张一模一样的表，效率高，推荐
truncate table 表名;
当一个表中条数非常多，又要做删除动作的时候，如果用delete会很慢，那我们可以使用 truncate方法;
 truncate table student;

删除表中的所有数据的两种做法对比
删除方法	说明
delete from 表名;	有多少条记录，就执行多少次删除操作，效率低，不推荐
truncate table 表名;	先删除整张表, 然后再重新创建一张一模一样的表，效率高，推荐

3、更改单表数据，关键字update

1）如果不加更新条件，则表里所有的记录都会更新

2）如果加了更新条件，则只有满足条件的记录会更新

语法格式1：不带条件的修改

update 表名 set 字段 = 值;

案例：不带条件修改，将所有的性别改为女（慎用！！）
-- 案例：不带条件修改，将所有的性别改为女（慎用！！）
update student set sgender = "女" ;
语法格式2：带条件的修改

update 表名 set 字段 = 值 [where 条件表达式：字段名 = 值 ]

案例：带条件的修改，将sid 为3的学生，性别改为男
-- 案例：带条件的修改，将sid 为3的学生，性别改为男
update student set sgender = "男" 
where sid = 3;
语法格式3：带条件一次修改多个列

update 表名 set 字段1 = 值1，字段2=值2 [where 条件表达式：字段名 = 值 ]

案例：一次修改多个列，将sid为 2 的学员，年龄改为 20，地址改为北京
-- 案例：一次修改多个列， 将sid为 2 的学员，年龄改为 20，地址改为 北京
update student 
set age = 20 ,address ="北京"
where sid = 2;

4、更改多表数据，关键字update+表连接

update 表1 别名 
	left|right|inner join 表2 别名 
	on 连接条件  
	set 字段1=值1,字段2=值2 
where 筛选条件;

第六部分：表的约束

建表的时候通常伴随着约束，约束指的是对表里的字段取值的一系列的规则限制条件

约束的目的在于对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据将无法插入到表中

约束的创建一般是在创建表时建立，语法如下：

create table 表名(
	字段名 字段类型 not null, -- 非空
	字段名 字段类型 primary key, -- 主键
	字段名 字段类型 unique, -- 唯一
	字段名 字段类型 default 值,-- 默认
	constraint 约束名 foreign key(设置外键的字段名) references 主表(被引用列)
)

-- 创建主表
creat etable student(
sid int primarykey,
sname varchar(20) not null,
score float default 0,
semail varchar(50) unique);
-- 在表中插入数据
insert into student values(1,'zs',80,'zs@163.com');

1、主键约束，关键字：primary key

主键指的是主关键字，它是表里的一个或多个字段，它的值可以唯一的标识表里的每一条记录。

如果对某个字段设置了主键约束，那么这个字段既不能为空，也不能重复。

一个表至多有一个主键

哪些字段可以作为主键 ?

1、主键约束是使用最频繁的约束

2、通常针对业务去设计主键，在设计数据表时，一般情况下，都会要求表中设置一个主键

3、主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，只要能够保证不重复就好, 比如身份证号列就可以作为主键

4、如果没有和业务关联太大的可以设计为主键的列的话，我们在进行数据库设计的时候往往人为加一列作为主键列，习惯上起名为xxid等

主键的自增

注: 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值

关键字: AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)--了解

添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
案例：创建表teacher，tid为主键并自增
-- 案例：创建表teacher,tid为主键并自增
create table teachers(
	 tid int auto_increment
	,primary key(tid)
	,tname varchar(32)
)
案例：由于sid设置了主键约束，以下数据无法插入
-- 数据无法插入，因为主键不能为空
insert into student values(null,'ls',80,'ls@163.com');

2、非空约束，关键字：not null

添加非空

alter table 表名 modify column 字段名 字段类型 not null;

删除非空

alter table 表名 modify column 字段名 字段类型;

3、唯一约束，关键字：unique

添加唯一

alter table 表名 add【 constraint 约束名】 unique(字段名);

删除唯一

alter table 表名 drop index 索引名;

4、默认值约束，关键字：default

添加默认

alter table 表名 modify column 字段名 字段类型 default 值;

删除默认

alter table 表名 modify column 字段名 字段类型 ;

5、外键约束，关键字：foreign key

1.如果某个字段，它在一张表里是主键，然后它又出现在另外一张表里，那么可以在另外一张表里对它设置外键约束。一旦设立了该约束，那么设置了外键约束字段的取值跟主键之间就有一个参照关系，即外键的取值要参照主键的取值。

2.使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性

3.外键约束经常和主键约束一起使用，用来确保数据的一致性

添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;
注意：添加数据时, 应该先添加主表中的数据

案例：有两张表，学生表和院系表，结构如下：

如果在学生表中插入一条记录，系号为D58，由于D58不在系表中，那么D58的这条记录的插入语句就会报错，从而避免脏数据的产生
-- 由于外键约束，以下数据将无法插入：
-- 由于D58不在系表中，那么D58的这条记录的插入语句就会报错，从而避免脏数据的产生
INSERT into student value(10004,"孙悟空","男","1992-01-01","D58");
注意：删除数据时,应该先删除从表中的数据；

错误做法：
-- 删除数据时，应该先删除从表中的数据
-- 报错cannot delete or update a parent row：a foreign key constraint fails
-- 报错原因：不能删除主表的这条数据，因为在从表中有对这条数据的引用
delete from student where sid = 3;
由于从表中引用了主表数据，主表中的数据不能直接删除，需要先删从表，再删主表；

6、自增长列（了解，掌握主键自增长设置即可）

自增长的特点：

1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1

2、一个表至多有一个自增长列

3、自增长列只能支持数值型

4、自增长列必须为一个key

自增长的使用：

一、创建表时设置自增长列
create table 表名(
字段名 字段类型 约束 auto_increment
);
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment;
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束;