mySQL的简单调用 重点聚合函数和分组
1、安装
1.1 终端连接
>> 基本命令
打开终端：win + r ，输入 cmd ，按回车
window 系统：盘符切换：直接输入盘符 -> d: 并回车
>> 连接 数据库
mysql -u 用户名 -p
2、数据库认识
2.1 数据库分类
>> 关系型数据库（mysql）
>> 非关系型数据库（mongodb、redis）
2.2 理解关系型数据库
类比 表格。
>> 关系型数据库中三种关系
一对一关系
一对多关系
多对多关系
2.3 SQL 的认识
>> 什么是 SQL
一种用于管理关系型数据库、与数据库中的数据进行通讯的计算机标准语言。
>> 语言组成
数据定义语言（DDL）
数据查询语言（DQL）
数据操作语言（DML）
数据控制语言（DCL）
>> DBMS（数据库管理系统）
数据库河西组成部分，主要完成对数据库的操作与管理。
3、mysql
3.1 创建数据库
create database 数据库名;
3.2 创建表
CREATE TABLE `students` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(20) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.3 数据类型
>> 整数类型
tinyint -- 1字节
int -- 4字节
bigint -- 8字节
>> 浮点数类型
float(m,d) -- 小数，4字节，单精度
double(m, d) -- 8字节，双精度
decimal(m, d)
>> 字符类型
char -- 固定长度（0~255）
varchar -- 可变长度（0~65535）
text -- 长文字
假设 char(4)与varchar(4)的区别
>> 日期
date -- yyyy-mm-dd
datetime -- yyyy-mm-dd hh:mm:ss
time -- hh:mm:ss
timestamp 时间戳
4、增删改操作
4.1 新增数据
insert into 表名 values (列1值,列2值);
insert into 表名 (列1, 列2) values (列1值,列2值); -- 指定列添加数据
insert into 表名 values (列1值,列2值),(列1值,列2值) -- 同时添加多条数据
4.2 修改数据
update 表名 set 列名1=新值, 列名2=新值 where 条件;
4.3 删除数据
delete from 表名 where 条件;
5、数据完整性
5.1 实体完整性
>> 主键：一般是当前表的 id
PRIMARY KEY
>> 唯一性
unique
CREATE TABLE `students` (
`student_id` int(11),
`student_name` varchar(20) unique,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5.2 域完整性
>> 非空约束
`student_name` varchar(20) NOT NULL COMMENT '学生姓名',
>> 默认值
`sex` tinyint(1) DEFAULT '1' COMMENT '1-男 2-女',
5.3 自定义完整性
`student_id` int(11) NOT NULL AUTO_INCREMENT,
-- 不为空 自增
5.4 引用完整性
>> 外键约束：表达表和表之间的关系
-- 第一步：创建主表（外键要连接的表，即 班级表）
-- 第二步：创建从表（发生依赖关系的表，即需要设置外键的表，即 学生表）
CONSTRAINT `students_ibfk_1` FOREIGN KEY (`grades_id`) REFERENCES `grades` (`grades_id`)
– 添加"李四":
INSERT INTO students VALUES (NULL,‘李四’);
– 指定字段添加信息
INSERT INTO students(student_name) VALUES (‘王五’);
– 多条数据添加
INSERT INTO students(student_name) VALUES (‘麻子’),(‘刚娃子’);
– 如果更新的时候不写条件，将会更细所有的数据
UPDATE students SET student_name=‘李雷’;
– 更新指定条件
UPDATE students SET student_name=‘韩梅梅’ WHERE student_id=3;
– 删除id为6的数据
DELETE FROM students WHERE student_id=6;
– 不加条件，全删
DELETE FROM students;
– 删除所有数据，标识列恢复默认值
TRUNCATE TABLE students;
1、数据查询
1.1 基本语法
select * | 列名
from 表名
DISTINCT 返回唯一不同的值
[where 条件]
[group by 分组名]
[having 筛选条件]
[order by 排序]
[limit 限制结果数量]
1.2 查询操作的分类
投影操作：指定查询的结果中能显示哪些列；
选择操作：指定哪些行出现在结果中；
排序操作：指定查询的结果以什么样的顺序显示
– 查询students表里的所有信息
SELECT DISTINCT garden FROM students;
– 查询学生信息且班级不重复
SELECT * FROM students;
– 选择某些列进行投影
SELECT student_name,age FROM students;
– 别名设置
SELECT student_name AS studentName,age FROM students;
– 简写
SELECT student_name studentName,age FROM students;
where 查询
>> 单条件
= > < >=
>> 多条件
and or
>> 范围
between ... and ... / in / not in
>> 模糊查询
like
规则：
%：任意长度任意字符
_：任意单个字符
– 查询age>8的学生的 student_name,age的信息
SELECT student_name,age FROM students WHERE age>8;
– 查询age>8和sex=1的学生的 student_name,age的信息
SELECT student_name,age FROM students WHERE age>8 AND sex=1;
– 查询age在10和16之间的学生的 student_name,age信息
SELECT student_name,age FROM students WHERE age BETWEEN 10 AND 16;
– 查询grades_id不在(2,4)之间的学生的 student_name,age信息
SELECT student_name,age FROM students WHERE grades_id NOT IN (2,4);
– 找姓李
SELECT student_name,age FROM students WHERE student_name LIKE ‘李%’;
– 找王XX
SELECT student_name,age FROM students WHERE student_name LIKE ‘王__’;
– 带 四 这个字的 ‘%四%’
– 查询电话号码不为空
– is null
– is not null
SELECT student_name,age FROM students WHERE tel IS NOT NULL;
排序：
– 默认升序 asc
SELECT * FROM students ORDER BY age;
– desc 降序
SELECT * FROM students ORDER BY age DESC;
限制
limit 开始位置,指定行数
开始位置是从 0 开始的
SELECT * FROM students LIMIT 2,2;
聚合函数：（难点）
查询中经常会对数据进行统计，就需要用到聚合函数。
注意：
返回的是单行单列；
不能和普通列一起查询。
/
– 求数量：count()
SELECT COUNT() FROM students;
– 求男生的数量
SELECT COUNT(*) FROM students WHERE sex=1;
– 查询 age 非空的学生数量
SELECT COUNT(age) FROM students;
– 统计 grades_id 没有重复的数量
SELECT COUNT(DISTINCT grades_id) FROM students;
– 求平均值：avg
– 不会计算空值
SELECT AVG(age) FROM students;
– ifnull(字段, 指定值)
SELECT AVG(IFNULL(age, 0)) FROM students;
– 别名设置
SELECT AVG(IFNULL(age, 0)) studentAvg FROM students;
– 求最大值：max
SELECT MAX(age) FROM students;
– 求最小值：min
SELECT MIN(age) FROM students;
– 求和：sum
SELECT SUM(age) FROM students;
分组查询：（难点）
将表中的行进行分组，然后在每个分组上应用聚合函数。
– 每个班级有多少人
SELECT grades_id,COUNT(*) FROM students GROUP BY grades_id;
– 每个班级最老的年龄
SELECT grades_id,MAX(age) FROM students GROUP BY grades_id;
– 统计各个班级男女各多少人
SELECT grades_id,sex,COUNT(*) FROM students GROUP BY grades_id,sex;
– 分组统计各个年级的男生人数大于1的
SELECT grades_id,sex,COUNT()
FROM students
WHERE sex=1 – 发生在聚合之前
GROUP BY grades_id,sex
HAVING COUNT()>1; – having 是对聚合之后的数据进行筛选
– 分组统计各个年级的男生人数大于1的，并且按人数进行升序排列
SELECT grades_id,sex,COUNT(*) num
FROM students
WHERE sex=1
GROUP BY grades_id,sex
HAVING num>1
ORDER BY num;
– 分组统计各个年级的男生人数大于1的，并且按人数进行升序排列，然后分页显示
SELECT grades_id,sex,COUNT(*) num
FROM students
WHERE sex=1
GROUP BY grades_id,sex
HAVING num>1
ORDER BY num
LIMIT 0,1;
– 需求：返回学生 王五 的 班级名字
SELECT grades_id FROM students WHERE student_name=‘王五’;
– 子查询
SELECT * FROM grades WHERE grades_id=(SELECT grades_id FROM students WHERE student_name=‘王五’);
– 表联接
SELECT * FROM students
JOIN grades – 连接表
ON students.grades_id=grades.grades_id
WHERE students.student_name=‘王五’