前文

值的收藏的SQL语句笔记（一）

MySQL笔记（二）

聚合函数

概述

所有的关系型数据库都支持聚合函数，使用聚合函数查询是列向查询，它是对一列的值进行计算，并返回结果。

注：所有的聚合函数会忽略空值。

练习

 -- 修改表student3为student
 RENAME TABLE student3 TO student;
 
 SELECT * FROM student;
 
 -- 创建表
 CREATE TABLE student(
     id INT,
     `name` VARCHAR(20),
     `math` int,
     `chinese` int,
     `english` int
 );
 
 -- 查询学生总数，星号表示所有的列
 SELECT COUNT(*) 总人数 FROM student;
 -- 也可以使用COUNT(1),效果等同于COUNT(*)
 SELECT COUNT(1) 总人数 FROM student;
 -- 如果某个字段的所有记录都不存在NULL值 可以使用COUNT(字段名)
 SELECT COUNT(id) 总人数 FROM student;
 
 -- 如果这一列中存在空值，聚合函数会忽略空值
 SELECT COUNT(english) 总人数 FROM student;
 
 -- 查询年龄大于40的总数(可以在聚合函数中使用条件)
 SELECT COUNT(*) 总人数 FROM student WHERE age > 40;
 
 
 -- 查询数学成绩的总和，针对一列操作
 SELECT SUM(math) 数学总分 FROM student;
 
 -- 对非数值类型求和没有含义 不会报错但是会返回值
 SELECT SUM(`name`) 数学总分 FROM student;
 
 -- 查询数学成绩平均分，整数的平均得到的是小数
 SELECT AVG(math) 数据平均分 FROM student;
 
 -- 查询数学成绩最高分
 SELECT MAX(math) 最高分 FROM student;
 
 -- 查询数学成绩最低分
 SELECT MIN(math) 最低分 FROM student;

分组查询

 SELECT 列名 FROM 表名 GROUP BY 分组列 HAVING 过滤条件;

用途：

1. 可以指定列根据列中的内用对数据进行分组操作
2. HAVING可以对分组后的内容进行过滤

直接分组

将分组字段结果中相同内容作为一组，如按性别将学生分成2组。返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

 -- 删除已经存在的student表
 DROP TABLE IF EXISTS student ;
 -- 创建新的student表
 CREATE TABLE student(
     sid INT PRIMARY KEY,
     `name` VARCHAR(20),
     `sex` CHAR(1)
 );
 -- 在student表中插入数据
 INSERT INTO student VALUES (1,'小明','男'),(2,'小红','女'),(3,'小刚','男');
 -- 用sex字段对studen分组 返回所有字段的值
 SELECT * FROM student GROUP BY sex;
 

执行结果

只返回了每组的第一条数据

结合聚合函数使用分组

 -- 删除已经存在的student表
 DROP TABLE IF EXISTS student ;
 -- 创建新的student表
 CREATE TABLE student(
     sid INT PRIMARY KEY,
     `name` VARCHAR(20),
     `sex` CHAR(1)
 );
 -- 在student表中插入数据
 INSERT INTO student VALUES (1,'小明','男'),(2,'小红','女'),(3,'小刚','男');
 -- 用sex字段对studen分组
 SELECT sex 性别,COUNT(sex) 人数 FROM student GROUP BY sex;

执行结果

结合聚合函数与条件使用

查询数学成绩大于等于60分的学生，按性别分组，统计每组的人数，只显示性别人数大于2的数据。

 -- 删除已经存在的student表
 DROP TABLE IF EXISTS student ;
 -- 创建新的student表
 CREATE TABLE student(
     sid INT PRIMARY KEY,
     `name` VARCHAR(20),
     `sex` CHAR(1),
     `math` INT,
     `chinese` INT,
     `english` INT
 );
 -- 在student表中插入数据
 INSERT INTO student VALUES (1,'小明','男',56,55,88),(2,'小红','女',66,78,55),(3,'小刚','男',100,99,98),(4,'小黄','男',60,79,88),(5,'小梅','女',60,79,88),(6,'小丽','女',60,79,88);
 
 -- 查询数学成绩大于等于60分的学生，按性别分组，统计每组的人数，只显示性别人数大于2的数据。
 SELECT sex 性别 , COUNT(*) FROM student WHERE math >= 60 GROUP BY sex HAVING COUNT(*) > 2;

执行结果

HAVING与WHERE的区别

DQL：LIMIT分页操作(重点)

用途：限制从表中返回的查询记录，通常称为分页的操作

使用场景：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

语法：

 limit 起始行号(从0开始), 返回的数据记录数;
 -- 使用方式：放在WHERE后面

 -- 删除已经存在的student表
 DROP TABLE IF EXISTS student ;
 -- 创建新的student表
 CREATE TABLE student(
     sid INT PRIMARY KEY,
     `name` VARCHAR(20),
     `sex` CHAR(1),
     `math` INT,
     `chinese` INT,
     `english` INT
 );
 -- 在student表中插入数据
 INSERT INTO student VALUES 
 (1,'小明','男',56,55,88),
 (2,'小红','女',66,78,55),
 (3,'小刚','男',100,99,98),
 (4,'小黄','男',60,79,88),
 (5,'小梅','女',60,79,88),
 (6,'小丽','女',60,79,88),
 (7,'小美','女',60,79,88),
 (8,'小花','女',60,79,88),
 (9,'小蕾','女',60,79,88);
 -- 每页显示5条
 -- 第一页：从第0行开始，显示5条
 SELECT * FROM student LIMIT 0,5;
 

执行结果

 -- 第二页：从第5行开始，显示5条
 SELECT * FROM student LIMIT 5,5;

执行结果

小总结：

SELECT中的关键字顺序

 SELECT 字段 FROM 表 WHERE 条件 GROUP BY 分组列 HAVING 过滤条件 ORDER BY 排序列 LIMIT 开始行, 返回行的数量;

增删改查关键字总结

DCL：创建用户，授权，撤销权限(了解，用的时候查就可以)

DCL (Data Control Language)：数据库安装后默认使用的是root用户就是超级管理员，拥有全部的权限。一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

创建用户：

 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
 CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; -- 在任意主机上登陆
 CREATE USER '用户名'@'主机名'; -- 一些版本的数据可可以不设置密码，使用用户名直接登陆

用户名：登录用户的名字

主机名：这个用户可以在哪台机器上登录，如果是本机服务器，使用localhost如果可以在任何一台主机上登录可以使用'%'

注：创建的用户名都在mysql数据库中的user表中可以查看到，密码经过了加密。

给用户授权

创建的新用户默认是没有任何权限的，需要使用root账户授权

 GRANT 权限 ON 数据库.表名 TO '用户名'@'主机名';

权限:操作权限：CREATE, ALTER, INSERT,DELETE,UPDATE,SELECT等，如果要设置所有的权限可以使用ALL

数据库.表名:对哪个数据库的哪个表设置上面的权限，可以使用通配符，如果要设置所有的库所有的表，可以使用*.*

'用户名'@'主机名':给哪个用户设置权限，名字必须与创建的时候写法相同

  GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON test.* to user1'@'localhost';
 
  GRANT ALL ON *.* TO 'user2'@'%';

取消用户权限

 REVOKE 权限 ON 数据库.表名 FROM '用户名'@'主机名';

权限：权限用户的操作权限：CREATE, ALTER, INSERT,DELETE,UPDATE,SELECT等，如果要设置所有的权限可以使用ALL

数据库名.表名： 对哪个数据库的哪个表设置上面的权限，可以使用通配符，如果要设置所有的库所有的表，可以使用*.*

'用户名'@'主机名'：给哪个用户撤销权限，名字必须与创建的时候写法相同

DCL：删除用户，修改密码(了解，用的时候查就可以)

删除用户

 DROP USER '用户名'@'主机名'
 -- 用户名必须与创建的时候相同

修改管理员密码

这条命令不在MySQL中执行，要在系统的CMD中执行

安装目录下：C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqladmin.exe文件

子用户修改自己的密码

 SET PASSWORD=PASSWORD('密码')

注：用户登录后操作，在mysql中已经登录的情况下使用这条命令

数据库备份和还原

备份

备份命令是在系统的CMD中执行的不是在MySQL中执行的

 C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe
 
 格式：mysqldump -u用户名 -p密码 数据库名>文件名
 将指定的数据库备份到一个SQL文件中

还原

注：这个操作需要登录，它是mysql中命令

 USE 数据库名;
 SOURCE 文件名;