MySQL分组函数，查询，约束，数据库设计等知识点

最新推荐文章于 2024-07-12 16:56:26 发布

默952

最新推荐文章于 2024-07-12 16:56:26 发布

阅读量168

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/Sh4oYn/article/details/117537415

版权

单行函数

只对一行进行变换，每行返回一个结果

可以嵌套，参数可以是一字段或一个表达式或一个值

分组函数

组函数类型

AVG()
SUM()
MAX()
MIN()
**COUNT() **

SELECT * FROM t_stu;
SELECT AVG(age) AS '平均年龄',COUNT(sid) AS '总人数',
       SUM(height) AS '身高之和',MAX(weight) AS '体重最大值',
       MIN(weight) AS '体重最小值'
       FROM t_stu;

分组

可以通过 group by 进行分组

-- 新建一列
ALTER TABLE t_stu
ADD COLUMN classid VARCHAR(10);

-- 查询c01班级学员信息 使用 where
SELECT * FROM t_stu WHERE classid = 'c01';
-- 查询每个班级的最大年龄
-- 没有被聚合函数包围的列 必须要出现在 group by 子句中
SELECT classid,MAX(age) FROM t_stu GROUP BY classid;
-- group by后面可以出现多列
SELECT classid,height, MAX(age) FROM t_stu GROUP BY classid,height;

having与where的区别？

（1）where是从表中筛选的条件，而having是统计结果中再次筛选

（2）where后面不能加“分组/聚合函数”，而having后面可以跟分组函数

-- having 子句 
-- where 的子句中不可以有聚合函数，这时候只能用having
-- 查询每个班级的最大年龄，如果这个班级的人数不超过2人，则不统计
SELECT classid , MAX(age) AS '最大年龄' FROM t_stu GROUP BY classid WHERE COUNT(sid)>=2 
# You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where count(sid)>=2 
-- where子句中不可以有聚合函数
SELECT classid , MAX(age) AS '最大年龄' FROM t_stu GROUP BY classid HAVING COUNT(sid)>=2

where 和 having 都具有筛选的功能。两者也可以同时出现

-- 查询每个班级的身高不小于170的同学中的最大年龄，如果这个班级的人数不超过2人，则不统计
-- SQL中组成部分的顺序问题 : group by 要出现在where的后面
SELECT classid , MAX(age) AS '最大年龄' FROM t_stu WHERE height>=166 GROUP BY classid HAVING COUNT(sid)>=2

order by

降序：desc

升序：asc，asc可以省略

-- order by 表示排序
-- asc 表示升序，可以省略
SELECT * FROM t_stu ORDER BY height ASC;
SELECT * FROM t_stu ORDER BY height;
-- desc 表示降序
SELECT * FROM t_stu ORDER BY age DESC;
-- 可以根据多个列进行排序
-- 先根据身高升序排列，当身高相同情况下再根据年龄降序
SELECT * FROM t_stu ORDER BY height,age DESC;

limit

如果数据很多有上万记录，在执行检索的时候可能会卡死，软件自带了限制行选项，并且 limit 就是用来限制检索的记录条数的。

在网上浏览别人的网站的时候，有上一页下一页分页的效果，也是可以用limit做。

-- limit 限制检索的记录条数
SELECT * FROM t_stu;

-- 从索引为0 第一条开始 ，取五条记录
SELECT * FROM t_stu LIMIT 5;

-- 从索引为3（第四条）开始，取五条记录
SELECT * FROM t_stu LIMIT 3,5;

-- 可以先通过指定条件筛选之后，再取指定行开始的记录
SELECT * FROM t_stu WHERE height>160 LIMIT 3,5;

-- 查询所有学员记录
-- 每页显示5条记录， 当前显示第3页
SELECT * FROM  t_stu LIMIT (3-1)*5,5;

-- 每页显示pageSize条记录
-- 要求显示第pageNo页
SELECT * FROM t_stu LIMIT (pageNo-1)*pageSize,pageSize;

select语句的7大句子

7大子句顺序

（1）from：从哪些表中筛选

（2）on：关联多表查询时，去除笛卡尔积

（3）where：从表中筛选的条件

（4）group by：分组依据

（5）having：在统计结果中再次筛选

（6）order by：排序

（7）limit：分页

必须按照（1）-（7）的顺序编写子句

#查询每个部门的男生的人数，并且显示人数超过5人的，按照人数降序排列，
#每页只能显示10条，我要第2页

表名：t_employee
列名：员工编号（empno），部门（deptno），性别（gender）

select deptno, count(empno) as '人数'
from t_employee
where gender = 'male'
group by deptno
having count(empno)>5
order by 人数 desc
limit 10,10


SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

SQL查询优化基础

select子句中尽量避免使用*

select 列名 from 表名

where子句比较符号左侧避免函数（表达式）

-- 查询学员信息，要求学员的身高减去5厘米之后还能超过165
select * from t_stu where height-5>165;
-- 应该写成
select * from t_stu where height>165+5;

尽量避免使用in 和 not in
尽量避免使用or

select * from t_stu where age >25 or age < 18
-- 建议改写成
select * from t_stu where age >25
union
select * from t_stu where age <18

使用limit限制返回的数据行数

表连接

新建两张表。

CREATE DATABASE mydb CHARSET utf8;

USE mydb ;

CREATE TABLE t1
(
    c1 VARCHAR(5),
    c2 VARCHAR(5)
)CHARSET utf8;

CREATE TABLE t2
(
    c3 VARCHAR(5),
    c4 VARCHAR(5)
)CHARSET utf8;

INSERT INTO t1 VALUES('a','b'),('b','c'),('c','d'),('e','f');
INSERT INTO t2 VALUES('b','d'),('e','h'),('i','j');

-- 查看当前数据库中有哪些表
SHOW TABLES;
-- 查看某张表的结果
DESC t1;

在这里插入图片描述

直接查询，将两张表的数据进行拼接

# 表连接：左连接、右连接、内连接
-- from后面出现了两张表，
-- 结果是：列名是两张表的列相加；结果是4*3 =12
mysql> SELECT * FROM t1 , t2 ;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
| a    | b    | b    | d    |
| a    | b    | e    | h    |
| a    | b    | i    | j    |
| b    | c    | b    | d    |
| b    | c    | e    | h    |
| b    | c    | i    | j    |
| c    | d    | b    | d    |
| c    | d    | e    | h    |
| c    | d    | i    | j    |
| e    | f    | b    | d    |
| e    | f    | e    | h    |
| e    | f    | i    | j    |
+------+------+------+------+
12 ROWS IN SET (0.02 sec)

内连接查询符合on条件的记录

# 1.内连接 inner join

mysql> SELECT * FROM t1
    -> INNER JOIN t2
    -> ON t1.c1=t2.c3;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
| b    | c    | b    | d    |
| e    | f    | e    | h    |
+------+------+------+------+
2 ROWS IN SET (0.00 sec)

左连接，在符合条件的前提下，左表的数据不丢失

# 左连接 left join
-- 左连接需要保证左表的记录不允许丢失

mysql> SELECT * FROM t1
    -> LEFT JOIN t2
    -> ON c1=c3;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
| b    | c    | b    | d    |
| e    | f    | e    | h    |
| a    | b    | NULL | NULL |
| c    | d    | NULL | NULL |
+------+------+------+------+
4 ROWS IN SET (0.00 sec)

右连接，在符合条件的前提下，右表的记录不丢失

# 右连接 right join
-- 右连接需要保证右表的记录不允许丢失
mysql> SELECT * FROM t1
    -> RIGHT JOIN t2
    -> ON c1=c3;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
| b    | c    | b    | d    |
| e    | f    | e    | h    |
| NULL | NULL | i    | j    |
+------+------+------+------+
3 ROWS IN SET (0.00 sec)

mysql不支持全连接，但可以用union模拟实现


#  mysql不支持全连接（左右表的数据都不允许丢失），可以通过union进行模拟
mysql> SELECT * FROM t1
    -> LEFT JOIN t2
    -> ON c1=c3
    -> UNION
    -> SELECT * FROM t1
    -> RIGHT JOIN t2
    -> ON c1=c3 ;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
| b    | c    | b    | d    |
| e    | f    | e    | h    |
| a    | b    | NULL | NULL |
| c    | d    | NULL | NULL |
| NULL | NULL | i    | j    |
+------+------+------+------+
5 ROWS IN SET (0.04 sec)

子查询

查询内部嵌套查询。能用表连接的地方不用子查询

子查询类型：

where型 where 后面跟select语句。

-- 1.求公司的最高工资
SELECT MAX(sal) FROM emp ;
-- 2.查看谁的工资等于最高工资
SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp );

from型

-- 查询经理人中最高的工资
-- 先获得所有经理人的工号
-- distinct关键字可以帮助我们去除重复的记录
SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL;
SELECT * FROM emp 
    WHERE empno IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)

-- 我想把以上查询的结果集看成是一张表
SELECT MAX(sal) FROM 
(
    SELECT * FROM emp 
    WHERE empno IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
) AS t_mgr
# Every derived table must have its own alias

exists型

SELECT * FROM dept
SELECT * FROM emp ;
-- 查询有员工的部门信息

-- 查询那些有员工的部门
SELECT deptno, dname FROM dept
WHERE EXISTS (SELECT * FROM emp  WHERE dept.deptno = emp.deptno);

约束与索引

1、数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据要合理。

数据的完整性要从以下四个方面考虑：

实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2、根据约束的特点，分为几种：

键约束：主键约束、外键约束、唯一键约束
Not NULL约束：非空约束
Check约束：检查约束
Default约束：默认值约束
自增约束

# 1.数据完整性: 实体完整性、域完整性、引用完整性、用户自定义完整性
# 2.如何保证以上完整性呢？
/*
    1) 唯一约束 unique
    2) 非空约束 not null
    3) 默认值约束 default
    4) 检查约束 check   mysql中不支持
    5) 主键约束：唯一+非空 ， 目前我们在设计表的时候，主键都是自增列，我们要求主键和业务无关
    6) 自增列约束：auto_increment
    7) 引用约束
    
    一张表只能有一个主键
    主键可以是一列，也可以是多列---组合主键
*/
CREATE TABLE t_stu2
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    sid VARCHAR(20) UNIQUE NOT NULL,
    age INT DEFAULT 18 ,
    address VARCHAR(20) DEFAULT '地址不详',
    CHECK(age BETWEEN 18 AND 199)
)CHAR SET utf8;

DESC t_stu2;

-- 对于自增列，不需要插入值，那么这一列我们在写SQL时，可以使用null或者0填充
INSERT INTO t_stu2 VALUES(0,'s01',19,'China');
-- Duplicate entry 's01' for key 'sid'

INSERT INTO t_stu2(sid) VALUES('s02');
INSERT INTO t_stu2(sid,age) VALUES('s03',17);

DROP TABLE t_score;

CREATE TABLE t_score
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    stuid INT,
    subid INT,
    score NUMERIC(4,2)
);

-- 以下这种情况是组合主键，早期我们是这么设计的，现在不需要
CREATE TABLE t_score
(
    stuid INT,
    subid INT,
    score NUMERIC(4,2),
    PRIMARY KEY(stuid, subid)
);


-- 添加外键约束
ALTER TABLE t_score
ADD CONSTRAINT FK_score_stu2
FOREIGN KEY(stuid) REFERENCES t_stu2(id);

# Cannot add or update a child row: a foreign key constraint fails (`studb`.`#sql-7f4_2`, CONSTRAINT `FK_score_stu2` FOREIGN KEY (`stuid`) REFERENCES `t_stu2` (`id`))
TRUNCATE TABLE t_score ;

-- 外键约束能够保证引用完整性
-- 新增的时候，必须要保证子表（成绩表）中引用的数据在主表（学员表）中存在
-- 删除的时候，删除主表记录，得保证没有子表引用我这条记录，也就是说：
-- 删除时，先删从表再删主表

# Cannot delete or update a parent row:

索引就是目录

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

其中主键删除，对应的索引也会删除

删除唯一键的方式是通过删除对应的索引来实现的

删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引

数据库设计

ER图

三要素：

实体entity 用矩形表示
实体有很多属性，属性用椭圆表示
实体和实体之间存在关系（1:1，1：n，m：n），我们用菱形来表示

ER图（数据库设计）三步骤
- 抽取实体
- 分析实体中的属性
- 分析实体之间的关系

数据库设计范式

三范式：1、列不能拆分     2、唯一标识    3、关系引用主键

第一范式：列不可再分
收货地址(address)：中国上海松江区岳阳街道.....
country,province,city,area,street



第二范式：一张表只描述一件事
学员信息表
学号   姓名    选修科目     成绩
以上不符合第二范式，因为描述了两件事：学员基本信息、学员选修情况




第三范式：在符合第二范式的基础上，表中的每一列都直接和主键有关

以下设计是不符合第三范式的：
成绩表：
学号	姓名   科目号	科目名称   成绩
1    张三		 1		语文      99


以下设计是符合第三范式的：
成绩表：
学号   科目号   成绩
1     1       99

----------------但是-------------

数据库设计时，并不是范式级别越高越好
往往是设计范式越高，性能越差
因此：
数据库范式是一把双刃剑，并不是范式越高越好，也不是越低越好
我们需要根据业务逻辑去合理设计：
如果频繁的进行查询（而这个查询如果符合第三范式则需要表连接），此时，我们往往会牺牲数据库设计的规范度（降低范式）从而提高查询性能
增加数据库数据冗余，从而获得更高的查询性能

默952

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL分组函数，查询，约束，数据库设计等知识点

单行函数只对一行进行变换，每行返回一个结果可以嵌套，参数可以是一字段或一个表达式或一个值分组函数组函数类型AVG()SUM()MAX()MIN()**COUNT() **SELECT * FROM t_stu;SELECT AVG(age) AS '平均年龄',COUNT(sid) AS '总人数', SUM(height) AS '身高之和',MAX(weight) AS '体重最大值', MIN(weight) AS '体重最小值' F
复制链接

扫一扫