MySQL数据库操作详解：DDL、DML与DQL语句-优快云博客

本文链接：https://blog.youkuaiyun.com/ksx2333/article/details/118160701

前言

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。MySQL 是最流行的关系型数据库管理系统。

结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。
结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

`SQL语句分类：`

在这里插入图片描述

`SQL通用写法`

SQL语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写。
3种注释：

查询返回的结果是结果集。

1 基础SQL-DDL语句

1.1 DDL操作数据库

1.1.1 创建数据库

直接创建数据库
CREATE DATABASE 数据库名;
判断是否存在并创建数据库(了解)
CREATE DATABASE IF NOT EXISTS 数据库名;
创建数据库并指定字符集(了解)
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

1.1.2 查看数据库

查看所有的数据库
SHOW DATABASES;
查看某个数据库的定义信息
SHOW CREATE DATABASE 数据库名;

1.1.3 修改数据库（了解）

修改数据库字符集格式
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
alter database db3 character set utf8;--例

1.1.4 删除数据库

DROP DATABASE 数据库名;

1.1.5 使用数据库

查看正在使用的数据库
SELECT DATABASE();
使用/切换数据库
USE 数据库名;

1.2 DDL操作表

1.2.1 常用数据类型如下

在这里插入图片描述

1.2.2 对表的增删改查

===================================创建表===================================
create table 表名(
     字段名 字段类型 约束,
     字段名 字段类型 约束
);
--快速创建一个表结构相同的表
CREATE TABLE 新表名 LIKE 旧表名;
===================================查看表===================================
--查看某个数据库中的所有表
SHOW TABLES;
--查看表结构
DESC 表名;
--查看创建表的SQL语句
SHOW CREATE TABLE 表名;
==============================修改表结构（了解）==============================
--添加表列/字段
ALTER TABLE 表名 ADD 列名 类型;
--修改列/字段名
ALTER TABLE 表名 MODIFY 列名 新的类型;
--修改列/字段名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
--删除列
ALTER TABLE 表名 DROP 列名;
--修改表名
RENAME TABLE 表名 TO 新表名;
--修改字符集
ALTER TABLE 表名 character set 字符集;
===================================删除表===================================
--直接删除表
DROP TABLE 表名;
---判断表是否存在并删除表(了解)
DROP TABLE IF EXISTS 表名;
===================================更新表结构/数据===================================
--不带条件修改数据
UPDATE 表名 SET 字段名=值,字段名=值;
--带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值 AND 字段名=值;
--更新nprice字段为price字段的+10;
update product set nprice=price+10;

2 基础SQL-DML语句

2.1 新增记录

===================================新增全部字段数据===================================
--罗列所有的字段
INSERT INTO 表名 (字段名1, 字段名2, 字段名3…) VALUES (值1, 值2, 值3);
--不写字段名
INSERT INTO 表名 VALUES (值1, 值2, 值3…);

此时值的个数,顺序,类型要和数据库表字段的个数,顺序,类型保持一致。
===================================新增部分字段数据===================================
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

关键字说明及注意事项

关键字说明

    INSERT INTO 表名 //表示往哪张表中添加数据
    (字段名1, 字段名2, …) //要给哪些字段设置值
    VALUES (值1, 值2, …); //设置具体的值

注意
值与字段个数,顺序，类型都必须对应
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
如果要插入空值，可以不写字段，或者插入null

2.2 蠕虫复制

本质：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中。
作用:将表名2 中的数据复制到表名1 中

INSERT INTO 表名1 SELECT * FROM 表名2;

2.3 更新表记录

--不带条件修改数据
UPDATE 表名 SET 字段名=值,字段名=值;
--带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值 AND 字段名=值;
--更新nprice字段为price字段的+10;
update product set nprice=price+10;

关键字说明：

    update // 修改数据
    set //修改哪些字段
    where 条件; //指定条件

2.4 删除表结构

--不带条件删除数据
DELETE FROM 表名;
--根据条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
--truncate删除表记录
TRUNCATE TABLE 表名;

truncate和delete的区别

delete是将表中的数据一条一条删除。
truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样。

3 基础SQL-DQL语句（非常重要）

查询不会对数据库中的数据进行修改.只是一种显示数据的方式。

===================================查询所有===================================
select * from 表名;
===================================查询指定列===================================
select 字段名1,字段名2... from 表名;
===================================别名查询===================================
select 字段名1 as 别名,字段名2 别名 from 表名 as 表别名;
as关键字可以省去不写
--表别名:查询商品名称和价格
SELECT pro.pname,pro.price FROM product AS pro;
--列别名：查询商品名称和价格
SELECT pname AS "商品名称",price "商品价格" FROM product;
===================================去重复查询===================================
select distinct 字段名 from 表名;
===================================查询结果参与运算===================================
select 列名1 + 固定值 from 表名;
-- 需求:将所有商品的价格+10元进行显示. (查询结果可参与运算)
SELECT *,IFNULL(price,0)+10 FROM product;--同时包含了null情况

3.1 条件查询

3.1.1 比较运算符

在这里插入图片描述
例：

SELECT * FROM student3 WHERE math>80;--查询math分数大于80分的学生

3.1.2 逻辑运算符

在这里插入图片描述
例：

SELECT * FROM student3 WHERE age>35 AND sex='男';--查询age大于35且性别为男的学生(两个条件同时满足)

3.1.3 in关键字

语法格式：SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
in里面的每个数据都会作为一次条件，只要满足条件的就会显示。

3.1.4 范围

语法格式：SELECT * FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2

    between 值1 and 值2
    值1一定要小于值2
    比如: age BETWEEN 80 AND 100
    相当于: age>=80 && age<=100

3.1.5 like

语法格式：select * from 表名 where 字段 like 条件;

           % : 模糊所有
           _ : 模糊一位
    例如:
           "马%" 匹配以"马"字开头
           "%马" 匹配以"马"字结尾
           "_马" 匹配第二个字是"马"字的
           "__马%" 匹配第三个字是"马"字的
           "%马%" 匹配包含"马"字的

3.2 排序

通过 ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)。
语法格式：select * from 表名 where 条件 order by 字段名称1 [desc | asc] , 字段名2 [desc | asc]... ;

    asc(默认) : 升序（ascend）
    desc ：     降序（descend）
    先根据字段1排序,若有第二排序字段,在第一排序字段的基础上进行第二次排序

3.2.1 单列排序

单列排序就是使用一个字段排序。

SELECT * FROM student3 ORDER BY age DESC;--查询所有数据,使用年龄降序排序

3.2.2 组合排序

组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

--查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC, math DESC;

3.3 聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。另外聚合函数会忽略空值。
五个聚合函数：

    count() : 计数
    IFNULL(字段名,0) : 如果指定字段的值为null,则使用0表示
    sum() : 求和
    max() : 求最大值
    min() : 求最小值
    avg() : 求平均值
            扩展-保留小数位
            cast(avg(字段名称) as decimal(5,2))

聚合函数的使用：写在 SQL语句SELECT后查询结果的位置
SELECT 字段名… FROM 表名;
SELECT COUNT(age) FROM 表名;
此时对于NULL的记录不会记录。
IFNULL(expr1, expr2)的用法：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为expr2, 我们可以利用IFNULL()函数，如果记录为NULL，给个默认值，这样统计的数据就不会遗漏。
例：SELECT COUNT(IFNULL(english,0)) FROM student3;

3.4 分组

分组查询是指使用 GROUP BY语句对查询信息进行分组，相同数据作为一组
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY怎么分组的？将分组字段结果中相同内容作为一组。
SELECT * FROM student3 GROUP BY sex;这句话会将sex相同的数据作为一组。
GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

分组后聚合函数的作用？不是操作所有数据，而是操作一组数据。
注意事项：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组。

对于分组后的条件需要使用having语句。

--查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*)>2;

3.4.1 having与where的区别

having是在分组后对数据进行过滤。
where是在分组前对数据进行过滤。
having后面可以使用聚合函数。
where后面不可以使用聚合函数。

3.5 limit语句

LIMIT是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。
语法格式：SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句]\ [HAVING子句] [ORDER BY子句] [LIMIT子句];
思考：limit子句为什么排在最后？
因为前面所有的限制条件都处理完了，只剩下显示多少条记录的问题了！

    LIMIT语法格式：
    LIMIT offset,length; 或者limit length;
    offset 是指偏移量，可以认为是跳过的记录数量，默认为0
    length 是指需要显示的总记录数

--例：从第三条开始显示，显示6条
SELECT * FROM student3 LIMIT 2,6;

LIMIT的使用场景：分页
即运用于记录过多的情况下。

-- 每页显示5条
-- 第一页： LIMIT 0,5; 跳过0条，显示5条
-- 第二页： LIMIT 5,5; 跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;--如果是0开始 可以简写为`LIMIT 5;`
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;--不够5条的话，则有多少显示多少