玖叶教程网

前端编程开发入门

2024年04月12日

全程软件测试(八十):MySQL数据库之函数使用—读书笔记

函数

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

-- 创建数据库
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;

运行效果展示：

1.2、max（）

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

  • 查询该学生表中年纪最大的学生，MySQL命令：
select max(age) from student;

运行效果展示：

1.3、min（）

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

  • 查询该学生表中年纪最小的学生 MySQL命令：
select sname,min(age) from student;

运行效果展示：

1.4、sum（）

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

  • 查询该学生表中年纪的总和 MySQL命令：
select sum(age) from student;

运行效果展示：

1.5、avg（）

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

  • 查询该学生表中年级的平均数 MySQL命令：
select avg(age) from student;

运行效果展示：

2.其他常用函数

这里我就不一一举例了，基本混个眼熟，以后用到再细说

2.1、时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

2.2、字符串函数

--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();

--统计长度
SELECT LENGTH();

2.3、数学函数

-- 绝对值
SELECT ABS(-136);

-- 向下取整
SELECT FLOOR(3.14);

-- 向上取整
SELECT CEILING(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;

运行效果展示：

2.使用IN关键字查询

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

  • 查询sid为S_1002和S_1003的学生信息 MySQL命令：
select * from student where sid in ('S_1002','S_1003');

运行效果展示：

  • 查询sid为S_1001以外的学生的信息 MySQL命令：
select * from student where sid not in ('S_1001');

运行效果展示：

3.使用BETWEEN AND关键字查询

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

  • 查询15到18岁的学生信息 MySQL命令：
select * from student where age between 15 and 18;

运行效果展示：

  • 查询不是15到18岁的学生信息 MySQL命令：
select * from student where age not between 15 and 18;

运行效果展示：

4.使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串

由于student表没有空值就不演示查询空值的了

  • 查询sname不为人知的学生信息 MySQL命令：
select * from student where sname is not null;

运行效果展示：

5.使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。

查询年纪大于15且性别为male的学生信息 MySQL命令：

select * from student where age>15 and gender='male';

运行效果展示：

6.使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

  • 查询年纪大于15或者性别为male的学生信息 MySQL命令：
select * from student where age>15 or gender='male';

运行效果展示：

7.使用LIKE关键字查询

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

7.1 普通字符串

  • 查询sname中与wang匹配的学生信息 MySQL命令：
select * from student where sname like 'wang';

运行效果展示：

7.2 含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串

  • 查询学生姓名以li开始的记录 MySQL命令：
select * from student where sname like 'li%';

运行效果展示：

  • 查询学生姓名以g结尾的记录 MySQL命令：
select * from student where sname like '%g';

运行效果展示：

  • 查询学生姓名包含s的记录 MySQL命令：
select * from student where sname like '%s%';

运行效果展示

7.3 含有_通配的字符串

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

  • 查询学生姓名以zx开头且长度为4的记录 MySQL命令：
select * from student where sname like 'zx__';

运行效果展示

  • 查询学生姓名以g结尾且长度为4的记录 MySQL命令：
select * from student where sname like '___g';

运行效果展示

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

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

  • 查询学生表中年纪最小的3位同学 MySQL命令：
select * from student order by age asc limit 3;

运行效果展示

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

GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过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
);

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

9.1 GROUP BY和聚合函数一起使用

  • 统计各部门员工个数 MySQL命令：
select count(*), departmentnumber from employee group by departmentnumber;

运行效果展示

  • 统计部门编号大于1001的各部门员工个数 MySQL命令：
select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

运行效果展示

9.2 GROUP BY和聚合函数以及HAVING一起使用

  • 统计工资总和大于8000的部门 MySQL命令：
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

运行效果展示

10.使用ORDER BY对查询结果排序

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序

其语法格式如下所示：

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

在该语法中：字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

  • 查询所有学生并按照年纪大小升序排列 MySQL命令：
select * from student order by age asc;

运行效果展示

  • 查询所有学生并按照年纪大小降序排列 MySQL命令：
select * from student order by age desc;

运行效果展示

标签：mysqlsum 

作者:gojiuye , 分类:技术精选 , 浏览:22 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言