MySQL笔记2——DML数据操作语句和基本的DQL语句

最新推荐文章于 2024-08-28 14:13:28 发布
最新推荐文章于 2024-08-28 14:13:28 发布
阅读量888 收藏 30
点赞数 29
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/PYKE_CHEN/article/details/140644416
版权

MySQL对数据的增删改

增加数据（INSERT语句）

INSERT INTO `表名` [(`字段1`,`字段2`,...`字段n`)] VALUES/VALUE ('值1','值2',...'值n')[,('值1','值2',...'值n')...];

其中： •“[]” 包含的内容可以省略； • 字段或值之间用英文逗号隔开； • 可同时插入多条数据，values 后用英文逗号隔开； • values和value的方式均可。

-- 日期使用字符串的形式进行书写日期格式（yyyy-MM-dd HH:mm:SS）
-- 全字段插入
-- 方式一
USE myschoolxxx
INSERT INTO student(sid,sname,birthday,ssex,classid);
VALUES(9,'张三','1989-11-23','男','1');

-- 方式二 1.NULL
INSERT INTO student values(null,'李四','1789-01-01','男','2');
INSERT INTO student values(DEFAULT,'李四','1789-01-01','男','2');-- null和default都可以利用主键的自增

增加数据（INSERT INTO SELECT ）

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。 目标表中任何已存在的行都不会受影响。

INSERT INTO table2 (column_name,...) SELECT column_name,... FROM table1;

其中：table2 必须存在，将table1中数据赋值到table2中

如果要新建表并赋值表内容:

create table 新表 select * from 原始表;

-- 部分字段插入
INSERT INTO student(sname,ssex) VALUES('王五','女');

ALTER TABLE student MODIFY ssex VARCHAR(10) not null DEFAULT '保密';

INSERT INTO student(sname) VALUES('赵六');

-- 一次性添加多条数据
-- 方式一
-- INSERT INTO 表名 (字段名......) VALUES(值..),(值..)...
INSERT INTO student(sname,ssex) VALUES('杨七','男'),('杨八','男'),('杨九','男');

-- 方式二 不常用
-- INSERT INTO SELECT 
-- 插入和被插入的表都必须存在
CREATE TABLE newstu(
	xingming VARCHAR(10),
	xingbie VARCHAR(10),
  classid int
);
INSERT INTO newstu(xingming,xingbie,classid) SELECT sname,ssex,classid from student

-- 方式三
-- CREATE TABLE SELECT
-- 被插入表不能存在 -- 被插入表不能有任何约束
CREATE TABLE stu1 SELECT sid,sname,birthday from student

修改数据（UPDATE语句）

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

其中： •“[]” 包含的内容可以省略； • `字段名`为要更改数据的字段，'值'表示字段改后的值，注意字段和值的对应； • 可同时修改多个字段，多个字段后用英文逗号隔开； •“WHERE”是where子句，可以给修改增加条件； • 条件：为筛选条件，如不指定则修改该表的所有列数据。

-- 修改
-- UPDATE 表名 SET 字段名=值,字段名=值,...,字段名=值
-- 【where 子句条件】
-- where子句中的条件是对表中每一条数据进行判断，
-- 判断成立该数据的父句执行
-- 判断不成立该数据的父句不执行
UPDATE stu1 SET birthday='1974-2-2'  -- 如果没有where子句则全部更改 
WHERE sname='陈博远';

UPDATE newstu set classid = 200 WHERE xingbie != '男';

UPDATE newstu set classid = 100 WHERE xingbie <> '女'; -- ‘<>’是不等于

UPDATE newstu SET xingbie = '保密' where classid < 180


UPDATE newstu SET xingbie = '外星人' where classid >= 20 AND classid <=90

-- 30 50 70 他们的性别地球人
UPDATE newstu SET xingbie = '地球人' where classid = 30 or classid = 50 or classid = 90

UPDATE newstu SET xingbie = '火星人' where classid = 30 BETWEENclassid = 90

UPDATE newstu set xingbie = '水星人' where cldssid BETWEEN 30 and 90

删除数据（DELETE语句）

DELETE FROM 表名 [WHERE 条件];

其中：

  • “[]” 包含的内容可以省略；
  • “WHERE”可以通过where子句增加删除的条件。
-- DELETE from 表名 【here子句 】
DELETE from newstu；

DELETE from stu1 WHERE sid = 1;

删除数据（TRUNCATE语句）

TRUNCATE [TABLE] 表名

其中：

  • TRUNCATE是一个特殊的删除语句，又叫做清空语句；
  • “[]”包含的内容可以省略；
  • 功能：清空某一张表内的全部数据，重置自增计数器；
  • 特点：由于没有条件约束，所以速度快,而且效率高
-- 清空表、截断表
-- TRUNCATE 表名
TRUNCATE stu1;

DELETE from student;-- 只删数据

INSERT INTO student(sname) VALUES('小米'); 

-- DELETE和TRUNCATE的区别
-- DELETE 只删除数据
-- TRUNCATE 不仅删除数据，还删除了索引（使索引重新从1开始）
-- drop 不仅把数据删掉，还删除了索引，表结构也删除了

计算列(MySQL8 新特性)

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1，b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的

在MySQL8.0中，CREATE TABLE 和 ALTER TABLE中都支持增加计算列。下面以CREATE TABLE为例/

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。

建表：
CREATE TABLE jsltab(
id INT PRIMARY KEY AUTO_INCREMENT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
)

数 据 的 备 份 和 还 原

MySQL数据库恢复的方法

方法：使用工具

  1. 先建库
  2. 在新建的库右键 【允许SQL文件】

 方法：备份

1.在已有的库右键 【转储SQL文件】

2.选择路径后进行保存， 会保存为xxx.sql 的文件

方法：控制台恢复

在控制台输入如下指令：

mysql –h localhost -P 3306 –u root –p dbname < /path/db_name.sql;

其中：

  • “dbname”表示需要恢复的数据库。
  • “/path/db_name.sql”表示所恢复数据库的备份文件绝对路径。

MySQL数据库备份的方法

方法1：控制台备份

控制台备份时，依赖的是MySQL自带的备份工具mysqldump，该工具的功能是转储数据 库，将数据转移到另一个SQL数据库，当然并不局限于MySQL数据库。 在控制台输入如下命令：

mysqldump -h localhost –P 3306 –u root –p dbname > /path/db_name.sql

其中：

  • “dbname”表示需要备份的数据库。
  • “/path/db_name.sql”表示将备份的数据库保存的绝对路径。

数据库备份和恢复的作用和主要价值

  1. 高可用性：使数据库的失效次数见到最少，从而使数据库保持最大的可用性
  2. 安全性：计算机病毒型、特洛伊木马型、“黑客”入侵型、逻辑炸弹型等会造成信息丢失，丢失的数据需要及时恢复。
  3. 完整性：当数据失效后，确保尽量少的数据丢失或根本不丢失，从而使数据具有最大的完整性。

单表查询

DQL语言

DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句 中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多表联查

单表联查：针对数据库中的一张数据进行查询，可以通过各种查询条件和方式去做相关的优化

多表联查：针对数据库中两张或者两张以上的表同时进行查询，依赖的手段有复杂查询和嵌套查询

查询语句语法规则

SELECT [DISTINCT]

{*|表1.*|[ 表1.字段1 [as 字段别名1]

[, 表1.字段2[as 字段别名2]][, …]]}

FROM 表1 [ as 表别名 ]

[ left|right|inner join 表2 on 表之间 的关系 ]

[ WHERE ]

[ GROUP BY ]

[ HAVING]

[ ORDER BY

] [ LIMIT {[ 位置偏移量] 行数 }] ;

其中：“[]”包含的内容可以省略。

DISTINCT：设定DISTINCT可以去掉重复记录。

AS： 表名或者字段名过长时，可以用AS关键字起别名，方便操作。

GROUP BY： 按组分类显示查询出的数据。

HAVING： GROUP BY分组时依赖的分组条件。

ORDER BY： 将查询出来的结果集按照一定顺序排序完成。

LIMIT： 限制显示查询结果的条数。

# DQL(查询语言)
-- 所有的查询都会得到一张虚拟表，临时存储在内存中，并且这个表再也不会发生变化，除非再查询一次得到一个新表
-- 最简单的查询

-- SELECT 数据;
SELECT 123;
SELECT 'abc';
SELECT 1+1;

-- 从表中获取数据
-- SELECT 字段名,字段名 FROM 表名;
-- 全字段查询
SELECT sid,sname,birthday,ssex,classid FROM student; 

-- * 通配符, 查询这个表的所有数据
-- 使用*全字段查询效率慢得多，不利于sql优化，尽量不要使用*代替字段名
SELECT * FROM student;

-- 部分字段查询
SELECT sname,ssex FROM student;

-- 字段名起别名
SELECT sname as '姓名', birthday '生日', ssex 性别 FROM student;-- as 和 '' 都可以省略

-- 添加字段
SELECT sname,'猿究院' 学校 FROM student;

-- DISTINCT 去重
-- 所有的字段的数据要一致（完全相同的）时候才会去重，联合去除
SELECT DISTINCT ssex,sname FROM student;

-- 带有条件的查询
-- 【where子句】
SELECT * FROM student WHERE sid = 7;

SELECT * FROM student WHERE sname = '赵雷';

SELECT * FROM student WHERE sid > 7;

SELECT * FROM student WHERE sid > 3 and sid < 9;

-- 查找一班的女同学
SELECT * FROM student WHERE classid = 1 and ssex = '女';
-- 查询年龄大于1990-1-1的学生
SELECT * FROM student WHERE birthday < '1990-1-1';

-- in 在某个特定的范围内

-- 不推荐
-- or 会让索引失效
-- 3 5 7 9
SELECT * FROM student WHERE sid = 3 or sid = 5 or sid = 7 or sid = 9;

-- 推荐
-- in 可以使用到索引
SELECT * FROM student WHERE sid in (3,5,7,9);

-- LIKE 模糊查询
-- 模糊符号
-- %任意多的任意字符
-- _一个任意字符
insert INTO student(sname) VALUES('陈靓'),('陈博远'),('小陈'),('彭于晏陈姓分晏'),('晓晨');

SELECT * from student WHERE sname LIKE '%陈%';

SELECT * FROM student WHERE sname LIKE '陈%';

SELECT * FROM student WHERE sname LIKE '陈_';

SELECT * FROM student WHERE sname LIKE '陈__';

-- NULL 
-- IS 是一个什么
SELECT * FROM student WHERE birthday IS NULL;

SELECT * FROM student WHERE birthday IS NOT NULL;

常用的聚合函数

函数名返回值
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
-- 聚合函数
-- 把多个值变为一个值
-- COUNT() 统计个数
-- MAX() 求最大值
-- MIN() 求最小值
-- SUM() 求总和
-- AVG() 求平均值

-- COUNT() 统计任何类型总数量 不统计null
-- SELECT COUNT(字段/常量/*) from 表名
SELECT COUNT(sid) FROM student; -- 如果字段名使用的是主键 正常统计
SELECT COUNT(classid) FROM student; -- 除了主键外不统计null值，如不是为了统计非空，则不推荐使用，使用主键统计最佳
SELECT COUNT( 'a') FROM student; -- 不推荐
SELECT COUNT(1) FROM student; -- 推荐
SELECT COUNT(*) FROM student; -- 推荐

-- sum avg max min 数值类型 求的是数值的总和/平均/最大/最小
SELECT sum(score) FROM sc;

SELECT avg(score) FROM sc;

SELECT MAX(score) FROM sc;

SELECT MIN(score) FROM sc;

-- 统计出成绩表中一共多少次考试，总成绩，平均分，最高分，最低分
SELECT COUNT(*),SUM(score),AVG(score),MAX(score),MIN(score) FROM sc;

GROUP BY和HAVING分组

GROUP BY

  1. 对所有的数据进行分组统计；
  2. 分组的依据字段可以有多个，并 依次分组。

HAVING

与GROUP BY结合使用，进行分组 后的数据筛选。

-- GROUP BY ******
SELECT ssex,COUNT(*) from student GROUP BY ssex;

-- 统计出各班有多少人
SELECT classid,COUNT(*) FROM student GROUP BY classid;

-- 统计每个同学的总分和平均分
SELECT sid,SUM(score),AVG(score) FROM sc GROUP BY sid;

-- HAVING 用于过滤聚合后的数据
-- 由于where是对每一条数据进行判断，因此聚合时不容许使用where，应当使用having
-- 查询平均分不及格的sid
SELECT sid,SUM(score),AVG(score) FROM sc GROUP BY sid having AVG(score) < 60;

ORDER BY排序

SELECT * FROM 表名 ORDER BY 字段名 [DESC|ASC]

其中：

  • ORDER BY 表示对SELECT语句查询得到的结果，按字段名进行排序；
  • DESC表示排序的顺序为降序，ASC表示排序的顺序为升序；
  • “[ ]”包含的内容可以省略。
-- ORDER BY 排序 
-- 升序 asc 默认可不写
-- 降序 desc 必须声明
SELECT * from student ORDER BY sid DESC;

-- 排序时 数据相同可以根据其他数据再次排序
SELECT * FROM sc ORDER BY score DESC ,cid DESC;

LIMIT关键字

SELECT * FROM 表名 LIMIT [n , m ]

其中：

  • LIMIT关键字是MySQL特有关键字；
  • LIMIT限制SELECT返回结果的行数；
  • n 表示第一条记录的偏移量，m 表示显示记录的数量；
  • “[ ]”包含的内容可以省略。
-- LIMIT 分页 0 开始 (页码-1)*步长,步长
-- SELECT * FROM student LIMIT 位置，步长
SELECT * FROM student LIMIT 6,3;
-- 应用层解决
-- SELECT * FROM student LIMIT (3-1)*3,3;

-- 找到成绩及格的总分数排名第二的学生的sid和总成绩
SELECT sid,SUM(score) FROM sc WHERE score > 60 GROUP BY sid ORDER BY SUM(score) DESC LIMIT 1,1;

MySql8新关键词OFFSET

SELECT * FROM 表名 limit m offset n

其中：

  • LIMIT关键字是MySQL特有关键字；
  • LIMIT限制SELECT返回结果的行数；
  • n 表示第一条记录的偏移量，m 表示显示记录的数量；
  • “[ ]”包含的内容可以省略。

扩展了解

在不同的DBMS中使用的关键字可能不同。在MySQL、PostgreSQL、MariaDB和SQLite中使用的Limit关键字、而且放到SELECT语句的最后面。

  • 如果是SQL Server 和 Access，需要使用TOP关键字如：

SELECT TOP 5 name,hp_max FROM heros ORDER BY hp_max DESC

  • 如果是DB2，使用FETCH FIRST 5 ROWS ONLY 这样的关键字：

SELECT name,hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

  • 如果使Oracle，你需要基于ROWNUM来统计行数：

SELECT rownum,last_name,salary FROM employees WHERE rownums < 5 ORDER BY salary DESC

晓晨CH
关注
  • 29
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL学习笔记之数据的增、删、改实现方法
12-16
在MySQL中，对数据的操作主要包括增、删、改（通常简称为DML操作），这些基本操作是数据库管理的基础。以下将详细介绍这些操作的方法。 一、增加数据 插入数据是向数据库中添加新记录的过程。在MySQL中，插入数据...
六、MySQL DML数据操纵语言学习笔记（插入、修改、删除详解 + 强化复习）
12-14
总结，MySQL的DML语句是数据库操作的核心部分，熟练掌握INSERT、UPDATE和DELETE可以帮助我们高效地管理数据库中的数据。理解不同操作方式的适用场景，以及注意相应的约束和条件，是数据库管理员和开发人员的基本功。...
MySQL笔记及常用SQL语句
06-20
MySQL笔记及常用SQL语句 MySQL是一种关系型数据库管理系统，广泛应用于Web应用程序和移动应用程序。...以上是 MySQL笔记及常用SQL语句的总结，希望能够帮助读者快速了解MySQL的基础知识和常用SQL语句。
MySQL基本语法笔记
09-06
4. **DML (Data Manipulation Language 数据操作语言)**：用于处理数据，包括插入、更新和删除等操作。 - **插入 (INSERT)**：用于向表中添加新的记录。 - **更新 (UPDATE)**：用于修改表中已有的记录。 - **删除...
MySQL 相关知识笔记
weixin_68929783的博客
08-23 2678
以上是根据 B 站黑马课程学习后的 MySQL 笔记，为了日后复习和给别人参考而整理，希望对大家有所帮助。同时，在实际应用中，还需要根据具体情况合理使用索引、约束等功能，以提高数据库的性能和数据的完整性。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 200
MySQL——子查询（5）带比较运算符的子查询
MySQL——多表操作（一）外键（3）添加外键约束的参数说明
PAP
08-23 438
MySQL——多表操作（一）外键（3）添加外键约束的参数说明
mysql的聚簇索引、非聚簇索引、回表
求知路上的同行者的博客
08-27 693
mysql聚簇索引
【MySQL数据库管理问答题】第5章 监控 MySQL
Songyaxuan075118的博客
08-26 952
MySQL服务器的日志文件类型及用途 默认启用的两种日志 错误日志 二进制日志 常规查询日志和慢速查询日志 Performance Schema的作用 Performance Schema顶级检测组件 sys模式的作用 audit_log_filter_linux_install.sql脚本 配置基于规则的MySQL审计功能 MySQL Enterprise Monitor提供的管理功能 SHOW PROCESSLIST命令输出的列名及作用 中继日志 审计日志 查询日志 慢查询日志
MySQL系统性的学习--基础
kussm_的博客
08-23 569
用来管理数据库用户、控制数据库的访问权限。在MySQL中，用户的信息及用户所具有的权限的信息都是存放在 mysql 库中的 user 表里，同时知道主机地址和用户名才能完整的定位一个用户。
MySQL 数据库的规范化与反规范化详解
yimeixiaolangzai的博客
08-23 1083
规范化是一种组织数据库结构的方法，旨在减少数据冗余、消除数据异常，确保数据的完整性。规范化通常通过一系列的规则（称为范式）来实现。常见的范式有第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。每一范式都是对前一范式的进一步约束。MySQL 的规范化和反规范化在数据库设计中都起着重要的作用。规范化通过一系列的范式规则，减少了数据冗余，确保了数据的完整性和一致性。然而，在实际应用中，规范化有时会导致查询性能下降，此时可以通过反规范化来优化查询速度。
MySQL中的事务
2301_80389611的博客
08-25 1226
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name 列更新 为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为' 张三'，而 Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据， 这种现象 就称之为 脏读。事务的执行是互不干扰的，一个事务不可能看到其他事务运行时中间某一时刻的数据。基于锁的状态分类：意向共享锁、意向排它锁。
MySQL常见面试题总结
夏日 の blog
08-28 793
ER 图全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以，实体使用矩形框表示。属性：即某个实体拥有的属性，可以理解为字段，属性使用椭圆形表示。联系：即实体与实体之间的关系，用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）
Navicat for MySQL：卓越的跨平台数据库管理开发工具
2401_82627223的博客
08-23 594
Navicat for MySQL是一款专为数据库管理员和开发人员设计的强大数据库管理开发工具，支持Mac和Windows操作系统，为用户提供了高效、便捷的数据库操作体验。
MySQL：常用函数
盒马的博客
08-27 750
本博客讲解MySQL中的常用函数
MYSQL多表连接一对多的理解
最新发布
刘一帆的博客
08-28 170
解决方法二：只查询对应信息的时候并不需要多表连接，子查询也不错！函数，这样既简便也更加有效。解决方法一：建议使用。
MySQL 用户与权限管理详解
yimeixiaolangzai的博客
08-23 949
MySQL 的用户与权限管理体系非常灵活和强大，通过创建和管理用户、授予和回收权限、理解 MySQL 的权限体系，可以确保数据库的安全性和操作的规范性。在实际应用中，建议遵循最小权限原则（Principle of Least Privilege），即只授予用户完成任务所需的最小权限，以减少安全风险。掌握这些基础操作，可以帮助你更好地管理 MySQL 数据库，并为系统的安全运行提供坚实保障。
已解决：java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 异常的正确解决方法，亲测有效！！！
星辰探码的博客
08-23 600
异常通常由缺少 MySQL 驱动程序或不正确的类名引起。通过确保正确的驱动程序版本和配置，可以有效解决此问题。在项目开发过程中，推荐使用最新版本的 MySQL Connector/J 驱动程序，以避免类似问题的发生。
【MySQL数据库管理问答题】第1章 MySQL 简介
Songyaxuan075118的博客
08-24 426
数据库的性能监控、备份和恢复策略、安全管理、以及故障排除方法 使用 MySQL 作为核心业务支撑平台 MySQL 8.0 版本 了解 MySQL 社区动态和技术演进的资源和渠道
MySQL基础命令详解：数据类型与DDL/DML/DQL操作
本篇笔记主要涵盖了MySQL的基础命令，包括数据类型、数据定义语言（DDL）、数据操作语言（DML）以及数据查询语言（DQL）。 ### MySQL数据类型 MySQL的数据类型大致可以分为三类：数值型、字符型和日期型。以下是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

晓晨CH

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值