简单查询

1. 概述

简单查询即不含 WHERE 的 SELECT 语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。
在此，先准备测试数据，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

2. 查询所有字段（方法不唯一只是举例）

查询所有字段 MySQL 命令：

SELECT * FROM student;

运行结果：

1703492128954.png
1703492128954.png

4. 查询指定字段

查询指定字段（sid、sname） MySQL 命令：

SELECT sid,sname FROM student;

运行结果：

1703492222015.png
1703492222015.png

常数查询

在 SELECT 中除了书写列名，还可以书写常数。可以用于标记。

常数的查询日期标记 MySQL 命令：

SELECT sid,sname,'2021-03-02' FROM student;

运行效果：

1703492320422.png
1703492320422.png

5. 从查询结果中过滤重复数据

在使用 DISTINCT 时需要注意：
在 SELECT 查询语句中 ​DISTINCT​ 关键字只能用在第一个所查列名之前。
MySQL 命令：

SELECT DISTINCT gender FROM student;

运行效果：

1703492440880.png
1703492440880.png

6. 算术运算符

在 SELECT 查询语句中还可以使用加减乘除运算符。
查询学生 10 年后的年龄 MySQL 命令：

SELECT sname, age+10 FROM student;

运行效果：

1703492595061.png
1703492595061.png

函数

在此，先准备测试数据，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

1. 聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、平均值等等。为此，MySQL 中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为 NULL，那么会自动将其过滤使其不参与运算。

聚合函数使用规则：
只有 SELECT 子句和 HAVING 子句、ORDER BY 子句中能够使用聚合函数。例如，在 WHERE 子句中使用聚合函数是错误的。

1.1 COUNT()

统计表中数据的行数或者统计指定列其值不为 NULL 的数据个数
查询有多少该表中有多少人
MySQL 命令:

SELECT COUNT(*) FROM student;

运行效果：

1703493129543.png
1703493129543.png

1.2 MAX()

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最大的学生
MySQL 命令：

SELECT MAX(age) FROM student;

运行效果：

1703493363969.png
1703493363969.png

1.3 MIN()

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最小的学生 MySQL 命令：

SELECT MIN(age) FROM student;

运行效果：

1703493599156.png
1703493599156.png

1.4 SUM()

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为 0
查询该学生表中年纪的总和 MySQL 命令：

SELECT sum(age) FROM student;

运行效果：

1703493699125.png
1703493699125.png

1.5 AVG()

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为

查询该学生表中年纪的平均数 MySQL 命令：

SELECT avg(age) FROM student;

运行效果：

1703493726298.png
1703493726298.png

2. 其他常用函数 (待完善)

2.1 时间函数

SELECTNOW();
SELECTDAY (NOW());
SELECTDATE (NOW());
SELECTTIME (NOW());
SELECTYEAR (NOW());
SELECTMONTH (NOW());
SELECTCURRENT_DATE();
SELECTCURRENT_TIME();
SELECTCURRENT_TIMESTAMP();
SELECTADDTIME('14:23:12','01:02:01');
SELECTDATE_ADD(NOW(),INTERVAL 1 DAY);
SELECTDATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECTDATE_SUB(NOW(),INTERVAL 1 DAY);
SELECTDATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECTDATEDIFF('2019-07-22','2019-05-05');

2.2 字符串函数

--连接函数
SELECTCONCAT ()
--
SELECTINSTR ();
--统计长度
SELECTLENGTH();

2.3 数学函数

-- 绝对值
SELECTABS(-136);
-- 向下取整
SELECTFLOOR(3.14);
-- 向上取整
SELECTCEILING(3.14);

条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过 WHERE 子句指定查询条件对查询结果进行过滤。
在开始学习条件查询之前，我们先准备测试数据，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1012', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1013', 'wang', 15, 'female');

1. 使用关系运算查询

在 WHERE 中可使用关系运算符进行条件查询，常用的关系运算符如下所示：

关系运算符说明
=等于
<>不等于
!=不等于
<小于
<=小于等于
>大于
>=大于等于

查询年龄等于或大于 17 的学生的信息 MySQL 命令：

SELECT* FROM student WHERE age>=17;

运行效果：

1703516373545.png
1703516373545.png

使用 IN 关键字查询

IN 关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

查询 sid 为 S_1002 和 S_1003 的学生信息 MySQL 命令：

SELECT* FROM student WHERE sid IN ('S_1002','S_1003');

运行效果：

1703516626151.png
1703516626151.png

查询 sid 为 S_1001 以外的学生的信息 MySQL 命令：

SELECT * FROM student WHERE sid NOT IN ('S_1001');

运行效果：

1703516749882.png
1703516749882.png

3. 使用 BETWEEN AND 关键字查询

BETWEEN AND 用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

查询 15 到 18 岁的学生信息 MySQL 命令：

SELECT* FROM student WHERE age BETWEEN 15 AND 18;

运行效果：

1703516870352.png
1703516870352.png

SELECT * FROM student WHERE age NOT BETWEEN 15 AND 18;

运行结果：

​​

1704022988354.png
1704022988354.png
​​​

4. 使用空值查询

在 MySQL 中，使用 IS NULL 关键字判断字段的值是否为空值。请注意：空值 NULL 不同于 0，也不同于空字符串
由于 student 表没有空值就不演示查询空值的了
查询 sname 不为空值的学生信息 MySQL 命令：

SELECT * FROM student WHERE sname IS NOT NULL;

运行效果：

​

1704023032764.png
1704023032764.png
​

5. 使用 AND 关键字查询

在 MySQL 中可使用 AND 关键字可以连接两个或者多个查询条件。
查询年纪大于 15 且性别为 male 的学生信息 MySQL 命令：

SELECT * FROM student WHERE age>15 AND gender='male';

运行效果：

​

1704023059162.png
1704023059162.png
​

6. 使用 OR 关键字查询

在使用 SELECT 语句查询数据时可使用 OR 关键字连接多个査询条件。在使用 OR 关键字时，只要记录满足其中任意一个条件就会被查询出来
查询年纪大于 15 或者性别为 male 的学生信息 MySQL 命令：

SELECT * FROM student WHERE age>15 OR gender='male';

运行效果：

​

1704023091892.png
1704023091892.png
​

7. 使用 LIKE 关键字查询

MySQL 中可使用 LIKE​ 关键字可以判断两个字符串是否相匹配

7.1 普通字符串

查询sname​中与wang​匹配的学生信息 MySQL命令：

SELECT * FROM student WHERE sname LIKE 'wang';

运行结果：

​

1704023250741.png
1704023250741.png
​

7.2 含有 % 通配的字符串

在 MySQL 中，可以使用 ​LIKE​ 运算符和 ​%​ 通配符来进行包含有 ​%​ 通配符的字符串查询。

​ ​%​ ​ 通配符在 ​LIKE​​ 运算符中表示匹配零个或多个字符 。

查询学生姓名以li开始的记录 MySQL命令：

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

运行效果：

​

image
image
​

查询学生姓名以g结尾的记录 MySQL命令：

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

运行结果：

​

1704023611426.png
1704023611426.png
​

查询学生姓名包含s的记录 MySQL命令：

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

运行效果：

​

1704023250741.png
1704023250741.png
​

7.3 含有_通配的字符串

​_​ 通配符在​LIKE​运算符中表示匹配任意一个字符。

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。

查询学生姓名以zx开头且长度为4的记录 MySQL命令：

SELECT * FROM student WHERE sname LIKE 'zx__';

​

1704023890621.png
1704023890621.png
​

查询学生姓名以g结尾且长度为4的记录 MySQL命令：

select * from student where sname like '___g';

​

1704024502296.png
1704024502296.png
​

8. 使用 LIMIT 限制查询结果的数量

正确使用LIMIT限制查询结果数量的方法是在查询语句的末尾添加​LIMIT​关键字，并指定要返回的结果数量。

例如，假设有一个名为students​的表，想要查询前5个学生的信息，可以使用以下SQL查询语句：

SELECT * FROM student LIMIT 5;

这将返回表中的前5行记录。

​

image
image
​

如果想要返回从第3行开始的10个记录，可以使用以下查询语句：

SELECT * FROM student
LIMIT 10 OFFSET 2;

这将返回从第3行开始的10行记录。​

image
image
​

请注意，LIMIT​​语句的第一个参数是要返回的结果数量，第二个参数是偏移量（即从第几行开始）。在一些数据库系统中，也可以使用OFFSET​​关键字来设置偏移量。

<div>
<span style=
"font-family: 华文隶书;
font-weight: ;
color: pink;
font-size: 20px;
data-type="strong";>
要注意的是，LIMIT子句只能用于查询语句的末尾，不能与WHERE子句一起使用，因为WHERE子句用于过滤行，而LIMIT用于限制返回的行数。
</span>
</div>

9. 使用 GROUP BY 进行分组查询

在MySQL中，可以使用GROUP BY子句对查询结果集进行分组。GROUP BY子句根据指定的列（或表达式）对结果集进行分组，并将每个分组的记录归类在一起。

首先准备一张表

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int,
    job_id int,
    departmentname varchar(50)
);

-- 向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001,1,'one'); 
INSERT INTO employee values(2,'lucy',9000,1002,1,'two'); 
INSERT INTO employee values(3,'joke',5000,1003,1,'three'); 
INSERT INTO employee values(4,'wang',3000,1003,2,'three'); 
INSERT INTO employee values(5,'chen',3000,1001,1,'one'); 
INSERT INTO employee values(6,'yukt',7000,1002,1,'two'); 
INSERT INTO employee values(7,'rett',6000,1003,2,'three'); 
INSERT INTO employee values(8,'mujk',4000,1002,2,'two'); 
INSERT INTO employee values(9,'poik',3000,1001,2,'one');

以下是GROUP BY子句的正确使用方法：

  1. 查询单个列的分组结果：
SELECT 列名, 聚合函数(列名)
FROM 表名
GROUP BY 列名;

例如，计算每个部门员工的平均工资：

SELECT departmentnumber, AVG(salary)
FROM employee
GROUP BY departmentnumber;

​

1704026381156.png
1704026381156.png
​

  1. 查询多个列的分组结果：
SELECT 列名1, 列名2, 聚合函数(列名3)
FROM 表名
GROUP BY 列名1, 列名2;

例如，计算每个部门和职位的平均工资：

SELECT departmentnumber, job_id, AVG(salary)
FROM employee
GROUP BY departmentnumber, job_id;

​

1704026543218.png
1704026543218.png
​

注意：如果在SELECT列表中使用了聚合函数（如SUM、COUNT、AVG等），那么除了在GROUP BY子句中指定的列外，其他列必须使用聚合函数进行处理。

例如，在上面的第1个示例中，如果想同时显示部门名称，可以使用以下查询：

SELECT departmentnumber, departmentname, AVG(salary)
FROM employee
GROUP BY departmentnumber, departmentname;

​

1704030330417.png
1704030330417.png
​

如果你在GROUP BY​中没有加入departmentname​，但是在SELECT列表中选择了departmentname​列，那么运行就会报错

SELECT departmentnumber, departmentname, AVG(salary)
FROM employee
GROUP BY departmentnumber;

​

image
image
​

如果要在SELECT​列表中选择departmentname​，就应该添加聚合函数

SELECT departmentnumber, MAX(departmentname), AVG(salary)
FROM employee
GROUP BY departmentnumber;

​

1704030694531.png
1704030694531.png
​

  1. 过滤分组结果：

可以使用HAVING子句在分组结果上进行过滤。HAVING子句类似于WHERE子句，但是它在分组结果上进行过滤，而不是在原始表上进行过滤。

例如，查询平均工资高于4000的部门：

SELECT departmentnumber, AVG(salary)
FROM employee
GROUP BY departmentnumber
HAVING AVG(salary) > 4000;

​

1704030789689.png
1704030789689.png
​

注意：HAVING子句必须在GROUP BY子句之后使用。

在 SQL 查询中，HAVING​ 子句用于在 GROUP BY​ 子句之后对分组进行筛选。主要原因是 HAVING​ 子句用于过滤聚合的结果，而这些聚合结果是在 GROUP BY​ 子句之后产生的。

使用ORDER BY对查询结果排序

​ORDER BY​ 子句用于对查询结果进行排序。它可以按照一个或多个列的升序（ASC）或降序（DESC）进行排序。以下是一些基本的用法：

  1. 按单个列排序：
-- 按列 column1 升序排序
SELECT column1, column2
FROM your_table
ORDER BY column1 ASC; 

-- 按列 column1 降序排序
SELECT column1, column2
FROM your_table
ORDER BY column1 DESC;
  1. 按多个列排序：
-- 先按列 column1 升序，再按列 column2 降序排序
SELECT column1, column2
FROM your_table
ORDER BY column1 ASC, column2 DESC;
  1. 按表达式排序：
-- 按表达式 column1 * 2 的结果降序排序
SELECT column1, column2
FROM your_table
ORDER BY column1 * 2 DESC;
  1. 按列位置排序：
-- 按第一个列的升序排序
SELECT column1, column2
FROM your_table
ORDER BY 1 ASC;

这里的 1​ 表示结果集(SELECT​)中的第一个列，2​ 表示第二个列，以此类推。

示例：

-- 按部门和平均工资进行降序排序
SELECT departmentname, AVG(salary) AS avg_salary
FROM employee
GROUP BY departmentname
ORDER BY avg_salary DESC,departmentname DESC;
-- ORDER BY 中第二个子句的意思是如果平均工资一样就按照名字降序排列

​

1704033029707.png
1704033029707.png
​

‍

别名设置

在 SQL 中，别名是为表、列或者计算结果取一个简洁的名称，以提高查询的可读性。别名的设置可以发生在多个场景中：

表别名

表别名用于对表名取一个短而有意义的名称。在查询中，可以使用表别名来引用表，特别是在多表连接的情况下。

SELECT column1, column2
FROM your_table AS alias_name;

这里，your_table​ 表被取了别名 alias_name​。在查询中，你可以使用 alias_name 来引用这个表。

示例：

SELECT a.departmentname, AVG(a.salary) as avg
FROM employee AS a

列别名

SELECT column1 AS alias1, column2 AS alias2
FROM your_table;

在这个例子中，column1​ 被取了别名 alias1​，column2​ 被取了别名 alias2​。在查询中，你可以使用这些别名代替原始列名。

计算结果别名

SELECT column1 + column2 AS sum_result
FROM your_table;

在这个例子中，计算结果 column1 + column2​ 被取了别名 sum_result​。这在涉及计算的情况下很常见，可以让你更好地理解查询中的计算。

子查询别名

在这个例子中，子查询 (SELECT column1 FROM your_table)​ 被取了别名 alias_name​。这种情况下，别名可以用于引用子查询的结果。

表达式别名

SELECT column1 * 0.1 AS percentage
FROM your_table;

在这个例子中，表达式 column1 * 0.1​ 被取了别名 percentage​。这在涉及复杂计算的情况下很有用。

表的关联关系

多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。
接下来，我们来学习在一对多的关联关系中如果添加和删除数据。先准备一些测试数据，代码如下：

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
    cid int(4) NOT NULL PRIMARY KEY,
    cname varchar(30) 
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY,
    sname varchar(30),
    classid int(8) NOT NULL
);

-- 为学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
-- 向班级表插入数据
INSERT INTO class(cid,cname)VALUES(1,'Java');
INSERT INTO class(cid,cname)VALUES(2,'Python');

-- 向学生表插入数据
INSERT INTO student(sid,sname,classid)VALUES(1,'tome',1);
INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
INSERT INTO student(sid,sname,classid)VALUES(4,'domi',2);

关联查询

查询Java班的所有学生 MySQL命令：

select * from student where classid=(select cid from class where cname='Java');

运行效果展示

在这里插入图片描述
在这里插入图片描述
​

‍