[大数据]MySQL核心查询【1】单表查询与SQL约束

上一小节重点针对简单的增删改查做了讲解汇总，本小节将进入SQL查询的核心阶段，主要是排序查询、分组查询、聚合查询以及分页查询，最后在文章的末尾也讲解了SQL的相关约束，为讲解多表查询打下基础。

目录

一、单表查询

1、排序查询

2、聚合查询

3、分组查询

4、分页查询-limit

二、SQL约束

5、主键约束

5.1、单字段主键：约束由一个字段组成

5.2、双字段主键：约束由多个字段组成---不常用

5.3、主键的删除

5.4、主键的自增

6、外键约束

?1、方式1：新建表时添加外键

2、方式2-在创建表时设置外键约束

3、外键的删除

4、 外键约束的注意事项

7、非空约束

8、唯一约束

一、单表查询

1、排序查询

????????如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。(排序只是显示效果,不会影响真实数据)。

语法格式：

select
字段名1，字段名2， ……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]

1. asc代表升序， desc代表降序， 如果不写默认升序
2. order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3. order by子句，放在查询语句的最后面。 LIMIT子句除外

-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(升序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;

2、聚合查询

????????干嘛的？求员工最高工资/平均工资/工资总和，都是聚合函数来做的。
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查
询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值(另外聚合函数会忽略null空
值。)；

语法：

SELECT? 聚合函数(字段名)? FROM? 表名;

-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

特殊处理--NULL值

????????1）count函数对null值的处理
????????如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含 ????????null值的记录个数。
????????2）sum和avg函数对null值的处理
????????这两个函数忽略null值的存在，就好象该条记录不存在一样。
????????3)max和min函数对null值的处理
?????? max和min两个函数同样忽略null值的存在。

3、分组查询

分组往往和聚合函数一起时候，对数据进行分组，分完组之后在各个组内进行聚合统计分析。

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
语法格式
SELECT? 分组字段/聚合函数? FROM? 表名? GROUP BY? 分组字段? [HAVING 条件];

• 如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数， 其他的字段不能出现：

-- 按照性别进行分组操作

-- 按照性别进行分组操作
SELECT * FROM emp GROUP BY sex; -- 注意 这样写没有意义

下面用图示来描述GROUP BY 分组过程

?GROUP BY 函数是如何分组的呢？
?

第一步：将性别相同的数据分为一组，故数据被分成两组；

第二步：根据聚合函数的查询方式，为纵向查询，故针对每一组的数据再进行函数的计算。如不回进行聚合，则会将每组的第一条记录进行返回。如下为上述查询返回的结果。

?

注意：

1）分组的目的，就是为统计，所以分组一般会与聚合在一起搭配使用，单独进行分组是没有意义的。

2）分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.* 查询其他字段没有意义
?

?需求：查询平均薪资大于6000的部门.
分析:
1) 需要在分组后,对数据进行过滤,使用 关键字 having
2) 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条
件。
?

# 查询平均薪资大于6000的部门
-- 需要在分组后再次进行过滤,使用 having
SELECT
    dept_name ,
    AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name 
    HAVING AVG(salary) > 6000 ;

?where 与 having的区别

? group by 子句用来分组 WHERE 子句的输出。
? having 子句用来从分组的结果中筛选行

我们再来看一个例子

统计各个分类商品的个数,且只显示个数大于4的信息
select category_id ,count(*) from product group by category_id having
count(*) > 4;

4、分页查询-limit

limit 关键字的作用

1. limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
2. limit 语法是 MySql的方言,用来完成分页

?参数说明

• 限制查询结果数的案例：

# 查询emp表中的前 5条数据
# 查询emp表中 从第4条开始,查询6条（故从第四条开始，则offset=3）
?

# 查询emp表中的前 5条数据
-- 参数1 起始值,默认是0 , 参数2 要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;


# 查询emp表中 从第4条开始,查询6条
-- 起始值默认是从0开始的.
SELECT * FROM emp LIMIT 3 , 6;

• ?分页操作 每页显示3条数据

  -- 分页操作 每页显示3条数据
  SELECT * FROM emp LIMIT 0,3; -- 第1页
  SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
  SELECT * FROM emp LIMIT 6,3; -- 第三页
  -- 分页公式 起始索引 = (当前页 - 1) * 每页条数

  二、SQL约束

SQL约束的作用：

对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.违反约束的不正确数据,将无法插入到表中。

5、主键约束

????????主键，又称主码，是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

5.1、单字段主键：约束由一个字段组成

主键约束的语法格式：（有两种）

• 字段名 字段类型 primary key--------常用
• 在定义完所有列之后，指定主键

方式一：
CREATE TABLE emp2(

eid INT PRIMARY KEY,
ename VARCHAR(20)，
sex char(1) );

方式二：
CREATE TABLE emp2(

eid INT ,
ename VARCHAR(20)，
sex char(1)，
PRIMARY KEY（eid）);

5.2、双字段主键：约束由多个字段组成---不常用

语法格式：

primary key 【字段名1、字段名2...】

假如表中没有ID的编号，为了唯一确定一名员工，可以把姓名与部门联合起来做主键，SQL语句如下：

CREATE TABLE emp2(
ename VARCHAR(20),
dep_name varchar(20),
sex CHAR(1),

PRIMARY KEY(ename,dep_name)
);

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。
????????注意：
????????1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
????????2. 一张表只能有一个主键，联合主键也是一个主键

第三种创建方式：

创建的时候不指定主键,然后通过 DDL语句进行设置

ALTER TABLE emp2 ADD PRIMARY KEY(eid);

主键依然存在增删改的操作。增的操作通过上述3种方式进行了讲解。下面重点讲述下删以及改的操作。

5.3、主键的删除

删除 表中的主键约束 (了解)

ALTER TABLE emp2 DROP PRIMARY KEY;

5.4、主键的自增

????????主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成
主键字段的值。

关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

CREATE TABLE emp2(
-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式
?

CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;

6、外键约束

????????谈到外键，我们得先谈谈外键是什么，以及外键的作用。

??????? 官方资料给出的阐述为：外键的主要作用是保持数据的一致性、完整性。但怎么具体理解这句话呢？

??????? 如下观点为参考其他优秀的博客文章汇总而成。假如如今须要建立一个表，一个班级的学生个人信息表：

??????? 在设计的时候。就给表1学号加入一个主键，表2中的学号加入一个外键。那么这样表1就是主表，表2就是子表。为什么说外键能保持数据的一致性、完整性呢？

????????第一章表切割成了表1和表2，表2的学号引用了表1的学号字段作为外键，假设不建立外键。仅仅是和表1一样单纯性的设立一个学号字段。那么和建立外键有什么差别呢？

????????比方表1中张三的学号为20140900001，那么我在表2中插数据的时候在学号字段插20140900001来记录张三的成绩不也是做到了表的解耦了吗？

这里存在的问题是。在不设置外键的情况下。表2的学号字段和表1的学号字段是没有关联的。仅仅是你自己觉得他们有关系而已。数据库并不觉得它俩有关系。也就是说，你在表2的学号字段插了一个值（比方20140999999），可是这个值在表1中并没有，这个时候，数据库还是允许你插入的，它并不会对插入的数据做关系检查。然而在设置外键的情况下。你插入表2学号字段的值必需要求在表1的学号字段能找到。 同一时候。假设你要删除表1的某个学号字段。必须保证表2中没有引用该字段值的列，否则就没法删除。

????????这就是所谓的保持数据的一致性和完整性。你想。如果表2还引用表1的某个学号，你却把表1中的这个学号删了，表2就不知道这个学号相应的学生是哪个学生。

数据的一致性还包含数据类型的一致性。

总结起来就是：外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

?1、方式1：新建表时添加外键

语法格式:

1）新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字
段名)；

-- 重新创建 employee表,添加外键约束
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT, -- 外键字段类型要和主表的主键字段类型保持一致
-- 添加外键约束
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

2、方式2-在创建表时设置外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foreign key(<列名>) references<主表名> (<列名>);

-- 创建部门表
create table if not exists dept2(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2
(deptno);

?在外键约束下的数据操作

????????①数据插入

-- 1、添加主表数据
-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部’);
-- 2、 添加从表数据
-- 注意给从表添加数据时，外键列的值不能随便写， 必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003');
insert into emp values('8','天山童姥',60, '1005'); -- 不可以

????????②、 删除数据

注意：
1：主表的数据被从表依赖时，不能删除，否则可以删除
2: 从表的数据可以随便删除
*/
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除

3、外键的删除

添加/删除外键针对的都是从表
?语法格式：

alter table 从表 drop foreign key 外键约束名称
??? 1) 删除 外键约束

-- 删除employee 表中的外键约束,外键约束名 emp_dept_fk
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk

??? 2) 再将外键 添加回来

-- 可以省略外键名称, 系统会自动生成一个
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

4、 外键约束的注意事项

• 1) 从表外键类型必须与主表主键类型一致 否则创建失败.
• 2) 添加数据时, 应该先添加主表中的数据.
• 3) 删除数据时,应该先删除从表中的数据.

?后续还有级联删除操作(仅做了解)

?

7、非空约束

?
非空约束的特点：某?列不允许为空

语法格式
字段名 字段类型 not null

# 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

8、唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

语法格式
字段名 字段值 unique

#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖