子乌笔记-MySQL-数据操作DML篇

ziwu1226

已于 2023-11-07 15:07:18 修改

阅读量804

点赞数

分类专栏： Python+大数据-精通SQL 文章标签：数据库大数据 mysql

于 2023-08-21 15:08:03 首次发布

本文链接：https://blog.csdn.net/ziwu1226/article/details/132400746

版权

Python+大数据-精通SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

DML 数据操作语言

DML英文全称是Date Manipulation Language(数据操作语言)，用来对数据库中表的数据增删改操作,进行数据操作后，会对原有数据产生影响。。

内容	用法
用来更新数据表	例如，插入表字段数据、删除数据、更新数据等
常用关键字	例如，插入`insert`、修改`update`、删除`delete`等。

插入数据(INSERT)-[增]

给指定字段添加一条数据

INSERT INTO 表名 [(字段名1,字段名2,……)] 
VALUES (值1,值2,……);

批量添加数据

INSERT INTO 表名 [(字段名1,字段名2,……)] 
VALUES (值1,值2,……),
	   (值1,值2,……),
	   (值1,值2,……);

注意：插入数据时，指定的字段顺序是需要一一对应的，表名后字段个数与类型等，要与values后的值对应。；字符串和日期型数据应该包含在引号中；插入数据大小，应该在字段规定范围内.

例如，使用命令完成：
（1）创建一个班级db_shopping库，并在该库下新建一个用户表；
（2）用户表的字段信息有编号、人物名、性别、住址等；[不添加任何约束条件]
（3）使用插入数据的语法分别插入一条数据；
（4）思考1：给所有字段名一次性插入2条数据信息，该怎么做？
（5）若发现在性别字段后，还缺少了年龄字段，该怎么处理呢？请在DataGrip软件中查看结果。
（6）思考2：给用户表一次性插入仅包含人物名、年龄、性别字段的3条数据，该怎么做？

# （1）创建一个`班级`db_shopping库，并在该库下新建一个用户表；
CREATE DATABASE IF NOT EXISTS db_shopping CHARSET utf8;
USE db_shopping;
# （2）用户表的字段信息有编号、人物名、性别、住址等；[不添加任何约束条件]
CREATE TABLE IF NOT EXISTS shop_user(
    id      INT,
    `name`  VARCHAR(20),
    sex     VARCHAR(5),
    address VARCHAR(255)
);
SHOW TABLES;
DESC shop_user;
# （3）使用插入数据的语法分别插入一条数据；
# （4）思考1：给所有字段名一次性插入2条数据信息，该怎么做？
INSERT INTO shop_user
VALUES (1, 'ZIWU', 'man', '深圳宝安机场'),
       (2, 'SUMI', 'woman', '佛山沙堤机场');
# （5）若发现在性别字段后，还缺少了年龄字段，该怎么处理呢？
ALTER TABLE shop_user ADD age int AFTER sex;
# （6）思考2：给用户表一次性插入仅包含人物名、年龄、性别字段的3条数据，该怎么做？
INSERT INTO shop_user (`name`,age,sex)
VALUES ('zijian',18,'男'),
       ('xiangling',12,'女'),
       ('lingjiec',23,'男');

在这里插入图片描述

修改数据(ALTER)-[改]

更新数据表数据

UPDATE 表名 
SET 字段名1=值1,字段名2=值2,……[WHERE 条件];

当没有指定[WHERE] 条件时，默认值是对全部字段值进行改写！

例如，基于上述表使用命令完成：
（1）将用户表内所有用户性别一次性设置为男；
（2）将用户表内所有用户的性别修改为女、年龄修改为18；
（3）将用户表内编号为2的用户性别设置为男；
（4）将用户表内编号为1的用户年龄更新为30，且住址更新为湖北省武汉市；
（5）思考：去年存储的用户数据到今年了，那么存储在user数据表里的用户年龄都要增加1岁，该怎么处理呢？

# （1）将用户表内所有用户性别一次性设置为男；
UPDATE shop_user
SET sex='男';
# （2）将用户表内所有用户的性别修改为女、年龄修改为18；
UPDATE shop_user
SET sex='女',
    age=18
# （3）将用户表内编号为2的用户性别设置为男；
UPDATE shop_user
SET sex='男'
WHERE id = 2;
# （4）将用户表内编号为1的用户年龄更新为30，且住址更新为湖北省武汉市；
UPDATE shop_user
SET age     = 30,
    address = '湖北省武汉市'
WHERE id = 1
# （5）思考：去年存储的用户数据到今年了，那么存储在user数据表里的用户年龄都要增加1岁，该怎么处理呢？
UPDATE shop_user
SET age = age + 1;

删除数据(DELECT)-[删]

DELETE 表名 [WHERE 条件];

说明：

不加条件时，会删除所有数据内容。
添加条件时，当条件结果为True时，满足条件的数据会被删除。

删除指定表，并重新创建【此为DDL语句，放这里仅作比较】

TRUNCATE TABLE 表名

说明：
使用DELECT不添加条件时，会删除所有数据,只删除数据，主键自增序列不会删除
truncate删除数据时，它是将整个表销毁，然后再创建一张一模一样的表，并且auto_increment修饰的字段将置为0从新开始计算。

♥请谈一谈使用delete和truncate删除表的区别?

	`DELETE FROM`	`TRUNCATE`
属于哪一类语言?	数据操作(DML)	数据定义(DDL)
是否可设置条件删除？	√	×
删除时具体操作是什么？	删除大量数据速度慢，只删除数据，主键自增序列不会删除	删除大量数据速度快，主键自增序列都会清零

例如，使用DELECT命令完成：
（1）在数据库中班级db_shopping创建一个用户表2，表字段有编号、用户名、密码，再增加3条数据；
（2）删除表内编号为1的这条数据，再删除用户名为Andy的这条数据；
（3）使用命令删除表内所有数据，观察效果。

# （1）在数据库中`班级`db_shopping创建一个用户表2，表字段有编号、用户名、密码，再增加3条数据；
CREATE TABLE IF NOT EXISTS user2
(
    id int,
    user_name VARCHAR(20),
    `password` VARCHAR(20)
);

INSERT INTO user2
VALUES (1,'ziwu','123456'),
       (2,'sumi','123321'),
       (3,'doukouzi','1226');
# （2）删除表内编号为1的这条数据，再删除用户名为Andy的这条数据；
DELETE FROM user2 WHERE id = 1;
# （3）使用命令删除表内所有数据，观察效果。
DELETE FROM user2;

例如，分别使用DELECT FROM和TRUNCATE命令完成：
（1）分别创建student、teacher表，字段包含：整型自动增长、主键的id编号、字符型的name姓名、
整型的age年龄；
（2）分别给两个表添加两条数据；
（3）分别使用两种删除方式清空数据；
（4）再插入一条新数据并查询，对比数据并观察id编号效果。

# （1）分别创建student、teacher表，字段包含：整型自动增长、主键的id编号、字符型的name姓名、整型的age年龄；
USE db_auto_test;
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    age INT
);

CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    age INT
);
# （2）分别给两个表添加两条数据；
INSERT INTO student
VALUES (1,'ZIWU',18),
       (2,'SUMI',16);
INSERT INTO teacher
VALUES (1,'ZIWU',18),
       (2,'SUMI',16);
# （3）分别使用两种删除方式清空数据；
DELETE FROM student;
TRUNCATE TABLE teacher;
# （4）再插入一条新数据并查询，对比数据并观察id编号效果。
INSERT INTO student
VALUES (NULL,'ZIWU',18);
INSERT INTO teacher
VALUES (NULL,'ZIWU',18);

在这里插入图片描述

MySQL引擎

MySQL有多种引擎，能执行creat tbale、select等命令，当数量不多的时候，使用任何引擎都没关系，但是，在大数据开发期间，要处理海量数据，就需要来了解MySQL的多种引擎了。
MySQL具有多个引擎，并且已经打包了多个引擎，为了使开发者不需要过多的关注他，通常数据库引擎都隐藏在MySQL软件中。
在MySQL中，有三类引擎

InnoDB 是一个非常可靠的事务处理引擎，但是它不支持全文搜索；[逻辑单元]
MyISAM 是一个性能极高的搜索引擎，它支持全文搜索，但是不支持[事务]处理；
Memory 在功能等同于MyISAM，但由于数据存储在内存中，速度很快，但占用的内存大，因此几乎不适用此引擎。

常见的两个索引对比：

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即是操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行产生影响，特别适合高并发的操作
缓存	只缓存索引，不缓存真实数据。	不仅缓存索引，还缓存真实数据，对内存要求比较高
表空间	小	大
关注点	性能	事务
是否默认安装	是	是

说明：

大数据开发时，使用的是InnoDB引擎。

1. 设定MySQL引擎

设定引擎语法：

CREATE TABLE 表名(
	……
)ENGINE = 引擎名 DEFAULT CHARSET 字符集名;

例如，使用命令完成：
（1）在MySQL中新建一个数据库班级db_student，并设定编码为utf8；
（2）新建一个包含姓名、年龄的学生表1，默认MySQL引擎；
（3）新建一个包含姓名、年龄的学生表2，并给数据表指定为InnoDB引擎及设定默认编码为utf8；
（4）使用查看创建表命令，观察两张表的编码及引擎信息。

# （1）在MySQL中新建一个数据库`班级`db_student，并设定编码为utf8；
CREATE DATABASE db_student CHARSET utf8;
# （2）新建一个包含姓名、年龄的学生表1，默认MySQL引擎；
CREATE TABLE user01 (
    name VARCHAR(20),
    age INT
);
# （3）新建一个包含姓名、年龄的学生表2，并给数据表指定为InnoDB引擎及设定默认编码为utf8；
CREATE TABLE user02 (
    name VARCHAR(20),
    age INT
)ENGINE = InnoDB DEFAULT CHARSET utf8;
# （4）使用查看创建表命令，观察两张表的编码及引擎信息。
SHOW CREATE TABLE user01;
SHOW CREATE TABLE user02;

在这里插入图片描述

SQL约束

设置数据类型和约束可以让字段更加的规范；SQL中的约束用于指定数据表中的数据规则，约束的存在保证了数据库中数据的精确性和可靠性。
注意：SQL约束在实际开发工作中，主要应用于创建数据表操作！
在MySQL数据库中，常见的SQL约束：

约束名	关键字	含义
主键	`PRIMARY KEY`	Mysql建议所有表的字段都叫id，类型为int
非空	`NOT NULL`	此字段不允许写空值
唯一	`UNIQUE`	此字段不允许重复
默认值	`DEFAULT`	当插入数据不填写字段对应值，会使用默认值，填写数据时以数据为准
外键	`FOREIGN`	对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在

1. 主键约束

PRIMARY KEY关键字代表的是主键约束，那么，什么是主键约束呢？

PRIMARY KEY主键必须包含唯一的值
主键对应的数据不为空
每个表都应该有一个主键，且只有一个主键。

为了数据存储更加清晰准确，定义主键时也需要遵循：

主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等；

添加主键约束
- 跟在定义字段后

CREATE TABLE 表名(
	字段名1 类型 PRIMARY KEY,
	字段名2 类型
);

例题：
（1）创建数据库测试db_testkey创建一个testkey01数据表，表内类型有：id、名字、年龄；
（2）要求主键创建在定义字段后

# （1）创建数据库`测试`db_testkey创建一个testkey01数据表，表内类型有：id、名字、年龄；
CREATE DATABASE IF NOT EXISTS db_testkey;
USE db_testkey;
# （2）要求主键创建在定义字段后
CREATE TABLE IF NOT EXISTS testkey01(
    id INT PRIMARY KEY,
    `name` VARCHAR(20),
    age INT
);

- 跟在语句末尾

CREATE TABLE 表名
(
	字段名1 类型,
	字段名2 类型,
	PRIMARY KEY (字段名1)
);

例题：
（1）创建数据库测试db_testkey创建一个testkey02数据表，表内类型有：id、名字、年龄；
（2）要求主键创建在定义字段最末尾

# （1）创建数据库`测试`db_testkey创建一个testkey01数据表，表内类型有：id、名字、年龄；
CREATE DATABASE IF NOT EXISTS db_testkey;
USE db_testkey;
# （2）要求主键创建在定义字段后
CREATE TABLE IF NOT EXISTS testkey02(
    id INT,
    `name` VARCHAR(20),
    age INT,
    PRIMARY KEY (id)
);

- 单独添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(表内字段名);

例题：
（1）创建一个testkey03数据表，表内类型有：id、名字；
（2）要求主键创建完表后进行添加

# （1）创建一个testkey03数据表，表内类型有：id、名字；
USE db_testkey;
CREATE TABLE testkey03(
    id int,
    name VARCHAR(20)
);
# （2）要求主键创建完表后进行添加
ALTER TABLE testkey03 ADD PRIMARY KEY(id);

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

注意：删除主键后，该字段的值依旧唯一且非空！

例如，使用命令删除主键约束：
（1）删除game2表的id编号主键约束；
（2）使用desc命令查看表结构。

# （1）删除game2表的id编号主键约束；
USE db_testkey;
ALTER TABLE testkey03 DROP PRIMARY KEY;
# （2）使用desc命令查看表结构。
DESC testkey03;

在这里插入图片描述

2. 自动增长

在创建表的时候要求主键为自动增长，语法：

CREATE TABLE 表名
(
	字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
	…
);

自动增长的数据类型必须是整型
且自动增长列必须为键（一般为主键）

例如，使用命令在创建表时完成：
（1）创建game3表，有4个字段：id编号、first_name最初姓名、last_name最新姓名、skill技能；
（2）在创建表时，给id编号添加主键约束，整型且自动增长；
（3）当建表成功后，使用desc命令查看表结构；
（4）添加3条数据后，查看数据结果；
（5）当添加数据时，给主键id传递null值，查看数据添加结果；
（6）当添加一条设定id = 10的数据后，再不给主键id传值并添加一条新数据，查看数据添加结果。

# （1）创建game3表，有4个字段：id编号、first_name最初姓名、last_name最新姓名、skill技能；
# （2）在创建表时，给id编号添加主键约束，整型且自动增长；
USE db_auto_test;
CREATE TABLE game3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    skill VARCHAR(255)
)DEFAULT CHARSET utf8;
# （3）当建表成功后，使用desc命令查看表结构；
DESC game3;
# （4）添加3条数据后，查看数据结果；
INSERT INTO game3 (first_name,last_name,skill)
VALUES ('吕布','奉先','无双'),
       ('刘备','刘皇叔','仁德'),
       ('赵云','子龙','龙胆');
SELECT * FROM game3;
# （5）当添加数据时，给主键id传递null值，查看数据添加结果；
INSERT INTO game3
VALUES (NULL,'张角','黄巾','请大汉赴死！');
# （6）当添加一条设定id = 10的数据后，再不给主键id传值并添加一条新数据，查看数据添加结果。
INSERT INTO game3
VALUES (10,'曹操','曹孟德','奸雄'),
       (NULL,'诸葛亮','卧龙','观星');
SELECT * FROM game3;

在这里插入图片描述

3. 非空约束

CREATE TABLE 表名(
	字段名 数据类型 NOT NULL
);

说明：

当设定主键后，值默认为非空；
当给某字段设定为NOT NULL 后，在添加数据时必须向该字段传递非NULL的值，且不接受传递null值

例如，使用命令完成：
（1）在库中创建游戏表game4，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技
能；
（2）在创建表时，给id编号添加主键约束、整型且自动增长；
（3）给last_name姓名字段设定为非空约束；

4. 唯一约束

当在创建表的时候给某字段添加唯一约束，语法：

CREATE TABLE 表名(
	字段名 字段类型 UNIQUE
);

UNIQUE 约束用于唯一标识数据表中的每一条数据
PRIMARY KEY 与 UNIQUE 约束均为字段提供了唯一性保证

例如，使用命令完成：
（1）在库中创建游戏表game5，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；
（2）在创建表时，给id编号添加主键约束、整型且自动增长；
（3）给last_name字段设定为唯一约束；

5. 默认值约束

在创建表的时给某字段添加默认值,语法：

CREATE TABLE 表名(
	字段名 数据类型 DEFAULT 值
);

说明：
（1）每个表可以有多个默认值约束；
（2）设置了默认值后，若不传递数值，则直接使用默认值。

例如，使用命令完成：
（1）在库中创建游戏表game6，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；
（2）在创建表时，给id编号添加主键约束、整型且自动增长；
（3）给skill字段设定为默认值约束，设定为轻功水上漂~；
（4）思考1：添加一条数据，并给字段skill技能传递值，观察效果；
（5）思考2：再添加一条数据，不给字段skill技能传递数据值，观察效果。

#（1）在库中创建游戏表game6，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；
#（2）在创建表时，给id编号添加主键约束、整型且自动增长；
#（3）给skill字段设定为默认值约束，设定为`轻功水上漂~`；
CREATE TABLE game6(
    id INT PRIMARY KEY AUTO_INCREMENT.,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    skill VARCHAR(255) DEFAULT '轻功水上门飘'
# （4）思考1：添加一条数据，并给字段skill技能传递值，观察效果；
INSERT INTO game6
VALUES (1,'孙悟空','齐天大圣','火眼金睛');
# （5）思考2：再添加一条数据，不给字段skill技能传递数据值，观察效果。
INSERT INTO game6 (id ,first_name, last_name)
VALUES (2,'孙悟空','齐天大圣');
SELECT * FROM game6;
);