2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]

盛透侧视攻城狮

发布于 2024-10-22 09:15:11

63600

代码可运行

文章被收录于专栏：超前沿网络空间安全全栈学习宝典超前沿网络空间安全全栈学习宝典

运行总次数：0

代码可运行

1 MySQL数据库基本操作-DQL-基本查询
1.2 SQL概述
SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言，与数据直接打交道，由 IBM 上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL 标准，先后有SQL-86 ， SQL-89 ， SQL-92 ， SQL-99 等标准。
SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了92年和99年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
不同的数据库生产厂商都支持SQL语句，但都有特有内容。
1.3 SQL类
SQL语言在功能上主要分为如下3大类:
SQL语言在功能上分为3大类
DDL（Data Definition Languages、数据定义语言）
这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DML（Data Manipulation Language、数据操作语言）
用于添加、删除、更新和查询数据库记录，并检查数据完整性。
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。
DCL（Data Control Language、数据控制语言）
用于定义数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 GRANT REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
2. SQL语言的规则与规范
2.1 基本规则
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号

必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号（' '）表示
列的别名，尽量使用双引号（" "），而且不建议省略as

必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号（' '）表示
列的别名，尽量使用双引号（" "），而且不建议省略as
2.2 SQL大小写规范
MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写
2.3 注释

单行注释	#注释文字(MySQL特有的方式)
单行注释	-- 注释文字(--后面必须包含一个空格。)
多行注释	/* 注释文字 */

单行注释
#注释文字(MySQL特有的方式)
单行注释
-- 注释文字(--后面必须包含一个空格。)
多行注释
/* 注释文字 */
2.4 命名规则(了解即可)

数据库、表名不得超过30个字符，变量名限制为29个
数据库、表名不得超过30个字符，变量名限制为29个
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

命名规则
数据库、表名不得超过30个字符，变量名限制为29个
数据库、表名不得超过30个字符，变量名限制为29个
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；
同一个库中，表不能重名；
同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。
如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。
假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

举例：

两句是一样的，不区分大小写

show databases;
 
SHOW DATABASES;

创建表格

create table student info(...); 

#表名错误，因为表名有空格 create table student_info(...);

order使用``飘号，因为order和系统关键字或系统函数名等预定义标识符重名了

CREATE TABLE `order`( 
   
    id INT, 
      
    lname VARCHAR(20) 
); 

select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略 

select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略"" 

select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省
略""

2.5 数据导入指令

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql 

mysql> desc employees; 
+----------------+-------------+------+-----+---------+-------+ 
| Field          | Type        | Null | Key | Default | Extra | 
+----------------+-------------+------+-----+---------+-------+ 
| employee_id    | int(6)      | NO   | PRI | 0       |       | 
| first_name     | varchar(20) | YES  |     | NULL    |       | 
| last_name      | varchar(25) | NO   |     | NULL    |       | 
| email          | varchar(25) | NO   | UNI | NULL    |       | 
| phone_number   | varchar(20) | YES  |     | NULL    |       | 
| hire_date      | date        | NO   |     | NULL    |       | 
| job_id         | varchar(10) | NO   | MUL | NULL    |       | 
| salary         | double(8,2) | YES  |     | NULL    |       | 
| commission_pct | double(2,2) | YES  |     | NULL    |       | 
| manager_id     | int(6)      | YES  | MUL | NULL    |       | 
| department_id  | int(4)      | YES  | MUL | NULL    |       | 
+----------------+-------------+------+-----+---------+-------+ 
11 rows in set (0.00 sec)

3. 基本的SELECT语句

3.1 语法格式

select 
  
    [all|distinct]
  
    <目标列的表达式1> [别名],
  
    <目标列的表达式2> [别名]...

    from <表名或视图名> [别名],<表名或视图名> [别名]...
    
    [where<条件表达式>]
    
    [group by <列名> 

    [having <条件表达式>]]
    
    [order by <列名> [asc|desc]]

    [limit <数字或者列表>];

简化版语法

select *| 列名 from 表 where 条件

3.2 数据准备

创建数据库

create database if not exist mydb2;

use mydb2;

创建商品表

create table product(
    
 pid int primary key auto_increment, # 商品编号

 pname varchar(20) not null , -# 商品名字

 price double,  # 商品价格

 category_id varchar(20) # 商品所属分类
);

添加数据

insert into product values(null,'海尔洗衣机',5000,'c001');

insert into product values(null,'美的冰箱',3000,'c001');

insert into product values(null,'格力空调',5000,'c001');

insert into product values(null,'九阳电饭煲’,200,'c001');

insert into product values(null,'啄木鸟衬衣',300,'c002');

insert into product values(null,'恒源祥西裤',800,'c002');

insert into product values(null,'花花公子夹克',440,'c002');

insert into product values(null,'劲霸休闲裤',266,'c002');

insert into product values(null,'海澜之家卫衣',180,'c002');

insert into product values(null,'杰克琼斯运动裤',430,'c002');
 
insert into product values(null,'兰蔻面霜',300,'c003');

insert into product values(null,'雅诗兰黛精华水',200,'c003');

insert into product values(null,'香奈儿香水',350,'c003');

insert into product values(null,'SK-II神仙水',350,'c003');

insert into product values(null,'资生堂粉底液',180,'c003');
 
insert into product values(null,'老北京方便面',56,'c004');

insert into product values(null,'良品铺子海带丝',17,'c004');

insert into product values(null,'三只松鼠坚果',88,null);

3.3 简单查询

举例:

1.查询所有的商品.

select *  from product;

2.查询商品名和商品价格.

select pname,price from product;

3别名查询.使用的关键字是as（as可以省略）.——表别名

select pname as pn from product;

4.去掉重复值

select distinct price from product;

5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示

select pname,price+10 from product;

补充说明

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多同学习惯将关键字大写、数据列和表名小写，所以我们也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和排错。2

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。
使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。
通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多同学习惯将关键字大写、数据列和表名小写，所以我们也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和排错。2

3.4 运算符

简介

数据库中的表结构确立后，表中的数据代表的意义就已经确定。

通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

例如：

学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL支持4种运算符

算术运算符：

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

举例

select 6 + 2;

select 6 - 2;

select 6 * 2;

select 6 / 2;

select 6 % 2;

将每件商品的价格加10

select name,price + 10 as new_price from product;

将所有商品的价格上调10%

select pname,price * 1.1 as new_price from product;

比较运算符：

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

举例：

查询商品名称为“海尔洗衣机”的商品所有信息：

select * from product where pname = '海尔洗衣机';

查询价格为800商品

select * from product where price = 800;

查询价格不是800的所有商品

select * from product where price != 800;

select * from product where price <> 800;

select * from product where not(price = 800);

查询商品价格大于60元的所有商品信息

select * from product where price > 60;

查询商品价格在200到1000之间所有商品

select * from product where price >= 200 and price <=1000;

select * from product where price between 200 and 1000;

非符号类型的运算符：

LIKE运算符通常使用如下通配符：

“%”：匹配0个或多个字符。
 
“_”：只能匹配一个字符。

REGEXP 运算符

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。

如果expr满足匹配条件，返回1；

如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。

^	匹配以该字符后面的字符开头的字符串
$	匹配以该字符前面的字符结尾的字符串
.	匹配任何一个单字符
[...]	匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
*	匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

^
匹配以该字符后面的字符开头的字符串
$
匹配以该字符前面的字符结尾的字符串
.
匹配任何一个单字符
[...]
匹配在方括号内的任何字符。
例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
*
匹配零个或多个在它前面的字符。
例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

举例

查询商品价格是200或800的所有商品

select * from product where price = 200 or price = 800;

select * from product where price in (200,800);

查询含有‘裤'字的所有商品

select * from product where pname like ‘%裤%';

查询以'海'开头的所有商品

select * from product where pname like '海%';

查询第二个字为'蔻'的所有商品

select * from product where pname like '_蔻%';

查询category_id为null的商品

select * from product where category_id is null;

查询category_id不为null分类的商品

select * from product where category_id is not null;

使用least求最小值

select least(10, 20, 30); -- 10

select least(10, null , 30); -- null

使用greatest求最大值

select greatest(10, 20, 30);

select greatest(10, null, 30); -- null

逻辑运算符：

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL

位运算符：

位运算符是在二进制数上进行计算的运算符。

位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数

运算符优先级：

数字编号越大，优先级越高，优先级高的运算符先进行计算。

如下图可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

3.5 排序查询

简介

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select 
 字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……

特点

1.asc代表升序，desc代表降序，如果不写默认升序

2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by子句，放在查询语句的最后面。LIMIT子句除外

示例

1.使用价格排序(降序)

select * from product order by price desc;

2.在价格排序(降序)的基础上，以分类排序(降序)

select * from product order by price desc,category_id asc;

3.显示商品的价格(去重复)，并排序(降序)

select distinct price from product order by price desc;

聚合函数	作用
count()	统计指定列不为NULL的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字 select sum(price) from product where category_id = 'c001'; 符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

聚合函数作用
聚合函数
作用
count()
统计指定列不为NULL的记录行数；
sum()
计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()

计算指定列的最大值，如果指定列是字 select sum(price) from product where category_id = 'c001'; 符串类型，那么使用字符串排序运算；

min()
计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()
计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

3.6 聚合查询

简介

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

聚合函数作用

举例：

(1) 查询商品的总条数

select count(*) from product;

(2)查询价格大于200商品的总条数

select count(*) from product where price > 200;

(3)查询分类为'c001'的所有商品的总和

select sum(price) from product where category_id = 'c001';

(4)查询商品的最大价格

select max(price) from product;

(5)查询商品的最小价格

select min(price) from product;

(6)查询分类为'c002'所有商品的平均价格

select avg(price) from product where category_id = 'c002';

聚合查询-NULL值的处理

1、count函数对null值的处理

如果count函数的参数为星号（*），则统计所有记录的个数。

而如果参数为某字段，不统计含null值的记录个数。

2、sum和avg函数对null值的处理

这两个函数忽略null值的存在，就如该条记录不存在一样。

3、max和min函数对null值的处理

max和min两个函数同样忽略null值的存在。

举例：

创建表

create table test_null( 

 c1 varchar(20), 

 c2 int 
);

插入数据

insert into test_null values('aaa',3);

insert into test_null values('bbb',3);

insert into test_null values('ccc',null);

insert into test_null values('ddd',6);

测试是否生效

select count(*), count(1), count(c2) from test_null;

select sum(c2),max(c2),min(c2),avg(c2) from test_null;

分组之后对统计结果进行筛选的话必须使用having，不能使用where
where子句用来筛选 FROM 子句中指定的操作所产生的行
group by 子句用来分组 WHERE 子句的输出。
having 子句用来从分组的结果中筛选行

分组之后对统计结果进行筛选的话必须使用having，不能使用where
where子句用来筛选 FROM 子句中指定的操作所产生的行
group by 子句用来分组 WHERE 子句的输出。
having 子句用来从分组的结果中筛选行

3.7 分组查询

分组查询是指使用group by字句对查询信息进行分组。

格式

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

操作

统计各个分类商品的个数

select category_id ,count(*) from product group by category_id ;

注意：

如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现

分组之后的条件筛选-having

格式

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

操作

统计各个分类商品的个数,且只显示个数大于4的信息

select category_id ,count(*) from product group by category_id having count(*) > 1;

3.8 分页查询

简介

分页查询在实比较际中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。

例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式

方式1-显示前n条

select 字段1，字段2... from 表明 limit n

方式2-分页显示

select 字段1，字段2... from 表明 limit m,n

m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数

n: 整数，表示查询多少条数据

操作

查询product表的前5条记录

select * from product limit 5

从第4条开始显示，显示5条

select * from product limit 3,5

3.9 INSERT INTO SELECT / FROM

简介

INSERT INTO SELECT语句：将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句。

格式

insert into Table2(field1,field2,…) select value1,value2,… from Table1 

#或者：

insert into Table2 select * from Table1

#目标表Table2必须存在

SELECT INTO FROM语句: 将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。

格式

SELECT vale1, value2 into Table2 from Table1

//要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2
中。

3.10 正则表达式

^ 在字符串开始处进行匹配

SELECT  'abc' REGEXP '^a';

$ 在字符串末尾开始匹配

SELECT  'abc' REGEXP 'a$';

SELECT  'abc' REGEXP 'c$’;

. 匹配任意字符

SELECT 'abc' REGEXP '.b';

SELECT 'abc' REGEXP '.c';

SELECT 'abc' REGEXP 'a.';

[...] 匹配括号内的任意单个字符

SELECT 'abc' REGEXP '[xyz]';

SELECT 'abc' REGEXP '[xaz]';

[^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配

SELECT 'a' REGEXP '[^abc]';

SELECT 'x' REGEXP '[^abc]';

SELECT 'abc' REGEXP '[^a]';

a* 匹配0个或多个a,包括空字符串。可以作为占位符使用.有没有指定字符都可以匹配到数据

SELECT 'stab' REGEXP '.ta*b';

SELECT 'stb' REGEXP '.ta*b';

SELECT '' REGEXP 'a*';

a+ 匹配1个或者多个a,但是不包括空字符

SELECT 'stab' REGEXP '.ta+b';

SELECT 'stb' REGEXP '.ta+b';

a? 匹配0个或者1个a

SELECT 'stb' REGEXP '.ta?b';

SELECT 'stab' REGEXP '.ta?b';

SELECT 'staab' REGEXP '.ta?b';

a1|a2 匹配a1或者a2，

SELECT 'a' REGEXP 'a|b';

SELECT 'b' REGEXP 'a|b';

SELECT 'b' REGEXP '^(a|b)';

SELECT 'a' REGEXP '^(a|b)';

SELECT 'c' REGEXP '^(a|b)';

a{m} 匹配m个a

SELECT 'auuuuc' REGEXP 'au{4}c';

SELECT 'auuuuc' REGEXP 'au{3}c';

a{m,n} 匹配m到n个a,包含m和n

SELECT 'auuuuc' REGEXP 'au{3,5}c';

SELECT 'auuuuc' REGEXP 'au{4,5}c';

SELECT 'auuuuc' REGEXP 'au{5,10}c';

(abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配.

如果要把多个字符作为一个整体匹配就需要用到括号，所以括号适合上面的所有情况。

SELECT 'xababy' REGEXP 'x(abab)y';

SELECT 'xababy' REGEXP 'x(ab)*y';

SELECT 'xababy' REGEXP 'x(ab){1,2}y';

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-10-21，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！