mysql 窗口聚合函数 受到group by的影响吗

JavaWeb学习阶段一MySQL数据库(SQL通用语法、DDL数据库操作、DML操作表、DQL查询表、聚合函数)

数据库的概念

数据库：储存数据的仓库，数据库是有组织的进行储存

英文：DataBase简称：DB

数据库管理系统

• 管理数据库的大型软件

SQL：结构化查询语句

操作关系型数据库的编程语言

定义操作所有关系型数据库的统一标准

常见的关系型数据库管理系统

1. Oracle：收费的大型数据库，Oracle公司的产品
2. MySql：开源免费的中小型数据库，后来Sun公司收购了MySql，而Sum公司由被Oracle公司收购
3. SQL Server：微软收费的中型数据库，C#、.net常用
4. PostgreSQL：开源免费的中小型数据库
5. DB2：IBM公司的大型收费数据库产品
6. SQLite：嵌入式的微型数据库，如谷歌的内核
7. MariaDB：开源免费的中小型的数据库

MySQL数据模型

关系型数据库是建立在关系型数据库的基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库

有点：

1. 都是使用表结构，格式一致，易于维护
2. 使用通用的SQL语言操作，操作方便，可用于复制查询
3. 数据储存在磁盘中，安全

SQL

结构化查询语言，一门操作关系型数据库的编程语言

定义操作所有关系型数据库的统一标准

对于同一个需求，每一种数据库操作的方式可能会存在不一样的的地方，称谓：方言

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾
2. MySQL数据库是SQL语句不区分大小写，关键字建议使用大写
3. 注释：

• 单行注释：-- 内容注释需要接空格 或 #注释内容，MySQL独有的注释方式
• 多行注释：/*注释内容*/

SQL分类

1. DDL：数据定义语言，用来定义数据库对象：数据库，表，列等
2. DML：数据库操作语言，用来对数据库中表的数据库进行增删改查
3. DQL：数据库查询语言，用来查询数据库表中的记录（数据）
4. DCL：数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户

DDL数据库操作

使用语句查询之前需要先以管理员的方式进入win命令终端，通过下面语句登录MySQL数据库

mysql -u[mysql管理员登录名] -p[密码明文显示]

• 查询数据库

一定要使用分号 ; 结束语句，不然系统会以为没有写完语句而查询不到数据

show databases;

SQL自带的数据库信息说明

information_schema：视图，逻辑表不存在物理路径中【没有文件夹】

mysql：SQL的核心信息，如权限等

performance_schema：性能相关的信息

sys：系统相关的信息

• 创建数据库

create database [创建的数据库名称];

数据库创建成功可以在安装的文件路径data目录下查看到数据库文件

E:\mysql-5.7.24-winx64\mysql-5.7.24-winx64\data\

判断是否存在数据库表再决定是否创建数据库

如果不通过判断创建了重复的数据库就会报错提示，通过判断就不会报错提示重复

有数据库不执行语句

无数据库执行创建语句

create database if not exists [创建的数据库名称];

Query OK, 1 row affected, 1 warning (0.00 sec)：有数据了创建不成功

Query OK, 1 row affected (0.00 sec)：创建数据库

• 删除数据库

drop database [删除的数据库名称];

也可以通过判断是否有数据库再删除

drop database if exists [删除的数据库名称];

• 使用[定位]数据库

也就是定位到那一个数据库

use db1;

Database changed：数据库已经改变了

查看当前使用的数据库是那个

select database();

查询表

先通过 use [数据库名称]; 进入到指定的数据库才能查询到具体的表和结构

查询当前数据库下所有表的名称

show tables;

查询表的结构

查询mysql数据库下的funs数据表的结构

desc [具体的表名];

Field：字段名称

Type：字段类型

Null：是否为空

Key：是否为主键

Default：默认值

Extra：额外的

创建表

create table [自定义表名](
	字段名称1 数据类型1,
	字段名称2 数据类型2,
	字段名称3 数据类型3,
	...,
	字段名称n 数据类型n
);

注意：最后一行末尾，不能加逗号，不然会报错

数据类型

可分为三类：

1. 数值
2. 日期
3. 字符串

数据类型	大小	描述
TINYINT	1bytes	小整数值
SMALLINT	2 bytes	大整数值
MEDIUMINT	3 bytes	大整数值
INT或INTEGER	4 bytes	大整数值
BIGINT	8 bytes	极大整数值
FLOAT	4 bytes	单精度浮点数值
DOUBLE	8 bytes	双精度浮点数值
DECIMAL		小数值
DATE	3	日期类
TIME	3	时间值或持续时间
YEAR	1	年份值
DATETIME	8	混合日期和时间值
TIMESTAMP	4	混合日期和时间值，时间戳
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

说明：

1. DOUBLE类似的使用：DOUBLE是一个双精度数据类型，使用的时候需要定义总长和精度位数
   如：sore double(5,2)：就是总长为5，小数点精度为2
2. CHAR定长数据类型：根据指定的储存空间储存数据【储存的性能高，浪费空间】
   如：name char(10)：指定可见10，如果名字为2个字符，那么后面就会使用空格占满10个字符为子
3. VARCHAR变长字符串：指定的储存空间就是最大的储存空间【储存能低，节约空间】
   如：name char(10)：指定可见10，如果名字为2个字符，那么储存的就占2个字符空间，不会使用空格占满

指定具体的数据类型创建表案例

-- 学生表
create table tb_stu(
	id int ,-- 编号
	name varchar(10),-- 姓名
	gender char(1),-- 性别
	birthday date,-- 生日
	score double(5,2) ,-- 分数
	email varchar(64),-- 邮箱
	tel varchar(20),-- 手机号
	status tinyint-- 状态
);

修改表

1. 修改表名

ALTER TABLE [被修改的表名] RENAME TO [修改后的表名];

1. 添加一列

ALTER TABLE [添加的表名] ADD [列名] [数据类型];

1. 删除一列

ALTER TABLE [修改的表名] DROP [删除的列名];

1. 修改数据类型

ALTER TABLE [修改的表名] MODIFY [列名] [新的数据类型];

1. 修改列名和数据类型

ALTER TABLE [修改的表名] CHANGE [新的列名] [新的数据类型];

1. 
   

删除表

• 删除表

DROP TABLE [删除指定的表名称]

• 删除表时判断是否存在

DEOP TABLE IF EXISTS [删除指定的表名];

图形化客户端管理工具的安装

Navicat是管理和开发MySQL或MDB的理想解决方案；

这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形化界面

DML操作表

开始使用Navicat工具写语句了

1. 添加指定列数据

INSERT INTO [表名] ( 列名1, 列名2 )
VALUES
	( 值1, 值2 );

示例代码

-- 给指定的列添加数据
INSERT INTO stu ( id, NAME )
VALUES
	( 1, "张三" );

1. 给所有列添加数据

INSERT INTO [表名] ( 列名1, 列名2 ,.....)
VALUES
	( 值1, 值2,..... );

示例代码

-- 给所有列添加数据
INSERT INTO stu ( id, NAME, gender, birthday, score, email, tel, `status` )
VALUES
	( 2, "李四", "男", "2001-10-16", 88.88, "371990028@qq", "15622730423", 1 );

给所有列添加数据也可以不写列名，不推荐这种方法

1. 批量添加多条数据

-- 给所有的列添加数据时是可以不用写具体的字段的
INSERT INTO stu
VALUES
	( 3, "李四", "男", "2001-10-16", 88.88, "371990028@qq", "15622730423", 1 ),
	( 4, "李四", "男", "2001-10-16", 88.88, "371990028@qq", "15622730423", 1 )

修改数据

UPDATE [表名] SET [修改的列名] = [修改的值] WHERE[查询的条件];

演示代码

-- 修改数据
UPDATE stu SET sex = '女' WHERE NAME = '张三';

-- 将张三的生日改为 1999-12-12 分数改为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';

-- 注意：如果update语句没有加where条件，则会将表中所有数据全部修改！
update stu set sex = '女';

注意：如果update语句没有加where条件，则会将表中所有数据全部修改

删除数据

指定表查询指定的数据删除

DELETE FROM [表名] [WHERE 查询条件];

演示代码

-- 删除查询指定的数据 DELETE FROM [表名] [WHERE 查询条件];
DELETE FROM stu WHERE `name` = "张三";

-- 没有查询数据删除所有的数
DELETE FROM stu;

如果删除不加上查询条件就会将所有的信息全部删除

DQL查询表

基础查询

条件查询（WHERE）

分组查询（GROUP BY）

排序查询（ORDER BY）

分页查询（LIMIT）

基础查询

查询多个字段

-- 查询name和age的两列
SELECT [字段列名,列名2] FROM [表名];
-- 查询所有的数据可以使用 * 代替列名 【进入公司的时候不推荐使用，使用指定的列查询数据方便查看】
SELECT
	* 
FROM
	stu;

查询去重复

-- [DISTINCT]去除重复的记录
SELECT DISTINCT [字段列表] FROM [表名];

起别名

-- 通过as关键字来给列起一个易看懂的别名
-- as可以不写，需要有空格与原名隔开
SELECT `name`,score as 入学成绩,tel AS 手机号 FROM stu;

条件查询（WHERE）

具体的示例代码

-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
VALUES 
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

SELECT * FROM stu;

-- 条件查询
-- 查询年龄大于20岁的学员信息
SELECT * FROM stu WHERE age > 20;

-- 查询年龄大于等于20岁的学员信息
SELECT * FROM stu WHERE age >= 20;

-- 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
SELECT * FROM stu WHERE age >= 20 && age <= 30;
SELECT * FROM stu WHERE age >= 20 AND age <= 30; -- 推荐使用这个方法
/*
上面语句中 &&  和  and  都表示并且的意思。建议使用 and 。
也可以使用  between ... and 来实现上面需求
*/
SELECT * FROM stu WHERE age BETWEEN 20 AND 30; -- 20-30之间的数据

-- 查询入学日期在'1998-09-01' 到 '1999-09-01'  之间的学员信息
SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';


-- 查询年龄等于18岁的学员信息
SELECT * FROM stu WHERE age = 18;

-- 查询年龄不等于18岁的学员信息
SELECT * FROM stu WHERE age != 18;

-- 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
SELECT * FROM stu WHERE age = 18 || age = 20 || age = 22; -- 不推荐使用
SELECT * FROM stu WHERE age = 18 or age = 20 or age = 22;

SELECT * FROM stu WHERE age in (18,20,22);

-- 查询英语成绩为 null的学员信息
-- null值的比较不能使用 =  或者 != 。需要使用 is是null  或者 is not不是null
select * from stu where english = null; -- 这个语句是不行的
select * from stu where english is null;
select * from stu where english is not null;

模糊查询（LIKE）

模糊查询使用like关键字，可以使用通配符进行占位:

（1）_ : 代表单个任意字符

（2）% : 代表任意个数字符

SELECT 字段列名 FROM 表名 WHERE 条件列表;

示例代码

-- 模糊查询
-- 查询姓'马'的学员信息
SELECT * FROM stu WHERE name LIKE "马%";

-- 查询第二个字是'花'的学员信息  
SELECT * FROM stu WHERE name LIKE "_花%";

-- 查询名字中包含 '德' 的学员信息
SELECT * FROM stu WHERE name LIKE "%德%";

排序查询（ORDER BY）

SELECT 字段列表 FROM 表名 ORDER BY 排序字段1 [排序方式：ASC或DESC] 排序字段2 [排序方式：ASC或DESC];

默认方式：

ASC：升序排序（默认值）

DESC：降序排序

如果有多个排序条件，当前边的条件值一样时，才会根据第二个条件进行排序

示例代码

-- 排序
SELECT * FROM stu ORDER BY age;  -- 升序排序

SELECT * FROM stu ORDER BY age DESC;  -- 降序排序

-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC,english ASC;

分组查询（GROUP BY）

使用到了聚合函数

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

示例代码

-- 分页查询
-- 查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;

-- 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义

-- 查询男同学和女同学各自的数学平均分，以及各自人数
select sex, avg(math),count(*) from stu group by sex;

-- 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math > 70 group by sex;

-- 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;

where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

语句的执行顺序：where > 聚合函数 > having

分页查询（LIMIT）

基本语法

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

注意： 上述语句中的起始索引是从0开始

计算公式：起始索引 = (当前页码-1) * 每页显示的条数

分页查询示例代码

-- 分页查询
-- 从0开始查询，查询3条数据
SELECT * FROM stu LIMIT 0,3;
-- 每页显示3条数据，查询第1页数据
SELECT * FROM stu LIMIT 0,3;
-- 每页显示3条数据，查询第2页数据
SELECT * FROM stu LIMIT 3,3;
-- 每页显示3条数据，查询第3页数据
SELECT * FROM stu LIMIT 6,3;

提示

分页查询limit是mysql数据库的方言

oracle分页查询使用rownumber

sql server分页查询使用top

聚合函数

概念：将列作为一个整体，进行纵向计算

聚合函数的分类

函数名	功能
count(列名)	统计数量（一般选用不为null的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

聚合函数语法使用：

SELECT 聚合函数名(列名) FROM 表;

注意：null 值不参与所有聚合函数运算

示例代码

-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC,english ASC;

SELECT * FROM stu;
-- 聚合函数的使用
-- 统计班级一共有多少个学生
SELECT COUNT(id) FROM stu;

-- 查询数学成绩的最高分
SELECT max(math) FROM stu;
-- 查询数学成绩的最低分
SELECT MIN(math) FROM stu;
-- 查询数学成绩的总分
SELECT sum(math) FROM stu;
-- 查询数学成绩的平均分
SELECT AVG(math) FROM stu;
-- 查询英语成绩的最低分
SELECT min(english) FROM stu;

起始索引 = (当前页码 - 1) * 每页显示的条数