MySQL表数据管理的常用操作

缘中猿

已于 2023-08-31 18:00:27 修改

阅读量220

点赞数

文章标签：数据库 mysql

于 2023-07-13 15:37:27 首次发布

本文链接：https://blog.csdn.net/m0_59555392/article/details/131689786

版权

结构化查询语言SQL（Structured Query Language）

一、数据定义语言DDL（Data Define Languge）

二、数据查询语言DQL（Data Query Language）

1、分组与排序

2、连接查询

(1)内连接（Inner Join）

(2)左外连接（Left Outer Join）

(3)右外连接（Right Outer Join）

(4)全外连接（Full Outer Join）

三、数据操作语言DML(Data Manipulate Language)

在应用程序开发中，数据库是不可缺少的重要组成部分，用于持久化存储和管理项目数据。熟悉数据库操作的常用命令有助于我们更加高效的访问数据库。

MySQL语法规范

1、关键字和函数的大小写

MySQL 不区分关键字和函数的大小写，建议使用大写来区分关键字和函数，提高代码的可读性。
例如，SELECT、FROM、WHERE 是关键字，而 COUNT()、SUM() 等是函数。
如果是在可视化工具内进行操作，一般会自动识别并进行转换。

2、标识符命名规范

数据库名称、表名、列名等标识符和java中标识符类似，可以使用字母、数字和下划线组成，不能以数字开头，不同的是java严格区分大小写。
标识符对大小写不敏感，默认情况下，MySQL 将它们视为不区分大小写。但在某些配置中，可以使用区分大小写的标识符。
建议使用有意义且易于理解的名称，以提高代码的可读性。

3、注释

使用 -- 或 # 开头表示单行注释，注释内容在行末结束。
使用 /* ... */ 表示多行注释。

结构化查询语言SQL（Structured Query Language）

用于和数据库通信的语言，几乎适用于所有的DBMS，不同的数据库管理系统可能有一些语法差异，可以进行复杂的数据库操作。

一、数据定义语言DDL（Data Define Languge）

用于创建、修改和删除数据库对象，如创建表（CREATE TABLE）、修改表结构（ALTER TABLE）、删除表（DROP TABLE）等。

1、库和表相关操作

#创建数据库
CREATE DATABASE mydatabase;

#删除数据库，谨慎操作，会永久删除数据库及其所有内容
DROP DATABASE mydatabase;

#创建数据库时我们也可以设置字符集,不设置则使用平台默认字符集，
#mysql8.0开始默认字符集为 utf8mb4
CREATE DATABASE mydatabase character set 字符集名;

#修改数据库名（需要先关闭MySQL服务，关闭所有数据库连接，改完重启服务生效）
#方案一：命令
alter database  原库名 rename to 新库名;

#方案二：修改系统文件夹
#找到MySQL数据库data文件夹,默认安装下路径：C:\ProgramData\MySQL\MySQL Server 8.0\Data，修改数#据库文件夹名

#更改数据库字符集：
alter database 库名 character set 字符集名;

#创建表
#下例创建了名为"customers"的表的示例，其中包含id、name和email三个字段（属性）
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

#在创建表时，我们也可以根据已有的表创建来创建，类似克隆
#1.仅复制表的结构，没有数据
create table 表名 like 旧表;

#2.复制表的结构和数据（数据可筛选）
create table 表名 
select 查询列表 from 旧表【where 筛选】;

#删除表
drop table customers;

#表属性修改
#1.添加列
#默认添加到最后一列，first：加在第一列，after 列名：表示加在某一列后边
alter table 表名 add column 列名 类型 【first|after 字段名】; 

#2.修改列的类型或约束，需要该谁就改谁，都改就一起改
alter table 表名 modify column 列名 新类型 【新约束】;

#3.修改列名
alter table 表名 change column 旧列名 新列名 类型;

#4.删除列
alter table 表名 drop column 列名;

#5.修改表名
alter table 表名 rename 【to】 新表名;

2、MySQL中的数据类型

(1)数值型

包含整型和 浮点型

整型有：tinyint、smallint、mediumint、int/integer、bigint，所占字节分别为1、2、3、4、6、8，一般常用的就是int。

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值（无符号插入负数，显示临界值0）
③长度可以不指定，默认会有一个长度。（这里的长度，代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型）

浮点型有定点数decimal(M,D)，浮点数float(M,D) 4字节，double(M,D) 8字节

特点：
①M代表整数部位和小数部位的总位数，D代表小数部分位数
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数

(2)字符型

包含char、varchar、binary、varbinary、enum、set、text、blob，一般常见的是char、varchar和text。

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1。
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略。
text：一般用于存放长文本，如文章、电影介绍等。

(3)日期型

year：年
date：日期
time：时间
datetime：日期+时间
timestamp：日期+时间，比较容易受时区、语法模式影响，更能反映当前时区的真实时间。

3、常见约束

NOT NULL	非空，该字段的值必填
UNIQUE	唯一，该字段的值不可重复
DEFAULT	默认，该字段的值不用手动插入有默认值
CHECK	检查，mysql不支持但不会报错
PRIMARY KEY	主键，该字段的值不可重复并且非空相当于unique+not null
FOREIGN KEY	外键，该字段的值引用了另外的表的字段，从表添加，表示引用主表中某列的值

二、数据查询语言DQL（Data Query Language）

用于查询表中的数据记录。

1、分组与排序

#分组查询 按某字段进行分组之后查询每组的信息
select 分组函数，分组的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

#例：查询employees表中每个部门的人数
SELECT department_id,COUNT(*) AS 人数
FROM employees 
GROUP BY department_id;
#结果格式如下：
department_id   人数
20              4
30              8
40              5
#可以在此查询的结果表上再进行筛选排序、排序等操作
#常见分组函数：
count(*)、max、min、sum、avg

sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型。

都可以搭配distinct使用，用于统计去重后的结果。

count的参数可以支持：字段、*（*表示一条记录有任意一个字段不为null则参与统计）、
常量值，一般放1(任意常量都行，相当于在每一条数据前加一个常量属性，然后再统计这个常量的个数)

#排序 用于对结果集进行排序，【】省略默认升序（即asc）
#需要按多个字段排序时，按顺序并列写即可
order by 排序字段1|表达式|函数|别名 【asc|desc】,
         排序字段2|表达式|函数|别名 【asc|desc】...

2、连接查询

在数据查询的过程中，我们并不总是只处理一张表上的数据，当涉及到多表字段时，我们就需要使用到连接查询。

由于SQL92语法支持功能较少，且已经较少使用，这里仅介绍当前使用较多的SQL99语法

(1)内连接（Inner Join）

返回两个表中匹配的行。只有当连接条件满足时，才会返回对应行。内连接是最常见和默认的连接类型。

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...

(2)左外连接（Left Outer Join）

返回左表中的所有行，以及与右表中满足连接条件的行。如果右表中没有匹配的行，则用NULL值填充右表的列。

select 查询列表
from 表1 别名
left【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...
#左外连接left左边的是主表，【】部分可省略

(3)右外连接（Right Outer Join）

返回右表中的所有行，以及与左表中满足连接条件的行。如果左表中没有匹配的行，则用NULL值填充左表的列。

select 查询列表
from 表1 别名
right【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...
#右外连接右边的是主表

(4)全外连接（Full Outer Join）

mysql不直接支持，可以利用Union关键字合并左右外连接查询结果实现。

select 查询列表
from 表1 别名
left【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...
Union
select 查询列表
from 表1 别名
right【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...

(5)自连接（Self Join）

是将表与自身进行连接操作。它用于在同一张表中进行关联查询，比较常见的情况是查询具有层级结构的数据。
在语法方面和内连接、外连接基本一致，只是同一张表与自身连接，由于两张表一样，所以必须取两个不同的别名。

select 查询列表
from 表1 别名1
join 表1 别名2 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
...

3、子查询

指的是嵌套在其他查询语句内部的select语句，也称作内查询。

#子查询结果集：
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列

#按出现位置
select后面：
		仅仅支持标量子查询
from后面：
		表子查询
where或having后面：
		标量子查询
		列子查询
		行子查询
exists后面：
		标量子查询
		列子查询
		行子查询
		表子查询

4、联合查询

关键字：Union / Union all
表示合并、联合，将多次查询结果合并成一个结果。可以将一条比较复杂的查询语句拆分成多条语句。

查询语句1
union 【all】
查询语句2
union 【all】
...
#union 去重，union all包含重复项

#适用条件：
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致

5、数据查询中的常见函数

(1)字符函数

函数	功能	示例
concat()	拼接字符串	concat('Hello', 'World') => 'HelloWorld'
substr()	截取子串	substr('HelloWorld', 1, 5) => 'elloW'
upper()	转换成大写	upper('hello') => 'HELLO'
lower()	转换成小写	lower('WORLD') => 'world'
trim()	去除前后指定的空格和字符	trim(' hello ') => 'hello'
ltrim()	去除左边空格	ltrim(' hello') => 'hello'
rtrim()	去除右边空格	rtrim('hello ') => 'hello'
replace()	字符串替换	replace('Hello', 'H', 'J') => 'Jello'
lpad()	左填充	lpad('Hello', 7, '') => '*Hello'
rpad()	右填充	rpad('Hello', 7, '') => 'Hello*'
instr()	返回子串第一次出现的索引	instr('Hello', 'l') => 3
length()	获取字符串的字节个数	length('Hello世界') => 11

(2)数学函数

函数	功能	示例
round()	四舍五入	round(3.14159, 2) => 3.14
rand()	生成一个随机数	rand() => 0.592746
floor()	向下取整	floor(3.14159) => 3
ceil()	向上取整	ceil(3.14159) => 4
mod()	取余	mod(10, 3) => 1
truncate()	截断小数部分，保留整数部分	truncate(3.14159) => 3

(3)日期函数

函数	功能	示例
now()	获取当前系统日期和时间	now() => '2023-07-13 14:10:04'
datediff(a, b)	计算两个日期之间的天数差	datediff('2023-07-15', '2023-07-10') => 5
curdate()	获取当前系统日期	curdate() => '2023-07-13'
curtime()	获取当前系统时间	curtime() => '14:10:04'
str_to_date()	将字符串转换成日期	str_to_date('2023-07-13', '%Y-%m-%d') => '2023-07-13'
date_format()	将日期格式化为字符串	date_format('2023-07-13', '%Y/%m/%d') => '2023/07/13'

(4)流程控制函数

IF函数：处理双分支

#语法
IF(condition, value_if_true, value_if_false)

#示例
#查询students表中所有成绩等级，score >=60显示为及格，其余不及格

SELECT IF(score >= 60, '及格', '不及格') AS result FROM students;

CASE函数：处理多分支（等值判断、条件判断）

#语法1：处理等值判断
CASE expression
    WHEN value_1 THEN result_1
    WHEN value_2 THEN result_2
    ...
    ELSE result
END

#示例1
#根据表中等级显示评价
SELECT id, name,
    CASE grade
        WHEN 'A' THEN '优秀'
        WHEN 'B' THEN '良好'
        WHEN 'C' THEN '及格'
        ELSE '不及格'
    END AS result
FROM students;

#语法2：处理条件判断
CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    ELSE result
END

#示例2
#根据分数区间显示评价
SELECT id, name,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS result
FROM students;

6、查询语句中的执行顺序

#假设有如下查询语句
select 查询列表    
from 表1 别名       
连接类型 join 表2 别名  on 连接条件         
where 筛选          
group by 分组列表   
having 筛选         
order by 排序列表    
limit 条目数; 

#执行顺序如下
1.FROM：确定要查询的数据源表。
2.JOIN：如果在查询中使用了连接操作（如 INNER JOIN、LEFT JOIN 等），则根据连接条件将多个表组合起来。
3.WHERE：对每一行数据应用 WHERE 条件，筛选出满足条件的行。
4.GROUP BY：根据指定的列对数据进行分组。
5.HAVING：对分组后的结果应用 HAVING 条件，筛选出满足条件的分组。
6.SELECT：选择要查询的列。
7.DISTINCT：若查询列表中存在，会根据 SELECT 子句中的 DISTINCT 关键字去重。
8.ORDER BY：按照指定的列对结果集进行排序。
9.LIMIT：限制结果集的返回行数。

三、数据操作语言DML(Data Manipulate Language)

用于对数据库中的数据进行增删改查操作，如插入数据（INSERT INTO）、修改数据（UPDATE）、删除数据（DELETE）以及查询数据（SELECT）等。

1、向表中插入数据

(1)方案一

插入数据时，列名与值对应

#语法
INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES
  (值1, 值2, 值3, ...),
  (值1, 值2, 值3, ...),
  ...

#示例 一次向表中插入多条数据
INSERT INTO students (name, age) 
VALUES 
('Jane', 22),
('Mike', 21);

#方案特点
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值
(1)字段和值都省略
(2)字段写上，值使用null
4、字段和值的个数必须一致
5、字段名可以省略，默认所有列

(2)方案二

插入数据时，省略表后列名，通过set方式，指定列名来赋值

#语法
INSERT INTO 表名 set 列1=值1,列2=值2...;

#示例 
INSERT INTO 表名 set name='jay',age=20;

#特点
使用SET方式插入数据时，通常需要保证表中存在主键或设置了自动增长的列，以便生成唯一的行标识。

对比两种方案，通常采用方案一，原因是方案一可以一次性插入多条数据，效率较高；且方案一支持子查询，可以将查询结果插入到表中。

2、修改表中数据

(1)单表修改

#语法
UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;

#示例 将john的年龄修改为21
UPDATE students
SET age = 21
WHERE name = 'John';

(2)多表修改

#语法
UPDATE 表1
JOIN 表2 ON 表1.列 = 表2.列
SET 表1.列1 = 值1, 表1.列2 = 值2, ...
WHERE 条件;

#示例 将年龄大于18岁的学生的成绩从"grades"表中获取，并将其更新到"students"表中的"grade"列
UPDATE students
JOIN grades ON students.id = grades.student_id
SET students.grade = grades.grade
WHERE students.age > 18;

3、删除表中数据

(1)使用DELETE

DELETE语句用于从表中删除满足指定条件的行。
它可以删除单个或多个行，并且可以使用WHERE子句来指定删除的条件。

(1)单表删除
#语法1
DELETE FROM 表名 WHERE 条件;

#示例1 删除age>20的数据
DELETE FROM students WHERE age > 20;

(2)多表级联删除
#语法2
delete 别名1,别名2 
from 表1 别名1 
join 表2 别名2 
on 连接条件
【where 筛选条件】

#示例2 删除两表中成绩小于60的数据记录
delete s1,s2
from students s1
join scores s2 
on s1.name = s2.name
where s2.score<60;

(2)使用TRUNCATE

TRUNCATE TABLE 语句用于快速删除表中的所有数据。
#语法
TRUNCATE TABLE 表名;

#示例 删除students表中所有数据，仅保留表结构
TRUNCATE TABLE students;

两种方案区别：

1.truncate 删除后，如果再插入，标识列从1开始；
delete删除后，如果再插入，标识列从断点开始。
2.delete可以添加筛选条件；
truncate不可以添加筛选条件。
3.truncate效率较高，通过直接释放数据页并重置自增计数器，能够更快地清空表中的数据。
4.truncate没有返回值；
delete可以返回受影响的行数。
5.truncate不生成日志记录，所以不可以回滚撤销，
delete会生成日志记录，可以回滚撤销。

如果只是想快速删除表中的所有数据且不关心日志记录，可以使用TRUNCATE TABLE语句。如果需要根据条件逐行删除数据且需要日志记录和事务支持，可以使用DELETE语句。