MySQL基础知识2

其它查询

聚合函数

将一列数据作为一个整体，进行纵向操作。

聚合函数分类

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

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

-- 查询数据各条数
select count(*) from stu;

-- 对某一列求和
select sum(age) from stu;

分组查询

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

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

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

-- 按同学的城市分组查询数学及格的人数，过滤掉人数不超过3的城市
select address, count(*) from stu where math > 60 group by address having count(*) > 3;

where 和 having 区别

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

联合查询

联合查询关键字 union、union all

联合查询：是把多次查询的结果合并起来，形成一个新的查询结果集。

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

联合查询的意义:
1.查询同一张表,但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序。
2.多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的。

多表查询

多表查询种类：

• 连接查询

  • 内连接查询 ：相当于查询AB交集数据
  • 外连接查询
    • 左外连接查询 ：相当于查询A表所有数据和交集部门数据
    • 右外连接查询 ： 相当于查询B表所有数据和交集部分数据

• 子查询

内连接查询

-- 隐式内连接
select * from emp, dept where emp.dep_id = dept.did;

-- 显式内连接
SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;
select * from emp inner join dept on emp.dep_id = dept.did;

显式内连接和隐式内连接区别
显式内连接的可读性更好，大数据量下显式内连接性能更好。

外连接查询

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件;

select * from emp left join dept on emp.dep_id = dept.did;


--右外连接
select * from emp right join dept on emp.dep_id = dept.did;

子查询

查询中嵌套查询，称嵌套查询为子查询。

-- 单行单列
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

-- 多行单列
SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

select * from emp where dep_id in (select did from dept where dname = '行政部' or dname = '市场部');


-- 多行多列
SELECT 字段列表 FROM (子查询) WHERE 条件;

约束

约束是作用于表中列上的规则，用于限制加入表的数据

分类

建表语句

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
  
);

外键约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
);

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);


-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建员工表和部门表，并添加上外键约束示例：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

---

相关链接：
MySQL多表操作内连接、联合查询
MySQL的面试题讲解
MySQL—连接查询、子查询