MySQL 的条件查询

这里先创建一个 mysql 的数据表来方便命令的展示

CREATE TABLE courses (
  id int unsigned NOT NULL AUTO_INCREMENT,
  student varchar(30) DEFAULT NULL  ,
  sub varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  grade int DEFAULT NULL,
  PRIMARY KEY (id),
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

CREATE TABLE teacher (
  id int(11) NOT NULL AUTO_INCREMENT,
  subject varchar(10) NOT NULL,
  name varchar(10) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

然后 diy 几个数据插入数据表。

聚合函数

又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组 (group by) 来使用，用于统计和计算分组数据。

常用的聚合函数（默认忽略 null 的行）:

count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值，

聚合函数对有 null 的不计算，可以使用 ifnull函数设置 null 代表的默认值加入计算 。
count(ifnull(col, num))

也可以用

count(*) 

但是推荐第一种。

有时候要求保留 n 位小数
avge(round(avg(col, n )))

分组函数

将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

GROUP BY [列名1，列名2，...] [HAVING 条件表达式] [WITH ROLLUP]

说明:
列名: 是指按照指定字段的值进行分组。可以多个字段，逗号隔开
HAVING 条件表达式: 只能用来过滤分组后的数据。与 where 区别开。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

group_concat(字段名) : 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割。

SELECT student, GROUP_CONCAT(sub) FROM courses GROUP BY student;

结果：

group by + 聚合函数的使用

SELECT student,AVG(grade) FROM courses GROUP BY student;

规范写法是 除了变量，其他命令都大写，这里忽略了。

group by + with rollup的使用
在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

SELECT student ,COUNT(id) FROM  courses GROUP BY student WITH ROLLUP;

连接查询
可以实现查询多个表xxx

select 字段 from 表1 xxx join 表2 on 表1.字段1 = 表2.字段2

xxx join 是连接查询关键字
on 后面就是连接查询条件。

连接查询可以分为:

1. 内连接查询,返回两个表的共同部分。 inner join
2. 左连接查询，以表 1 为标准，显示 表 1 的所有内容加表 2 存在的记录。left join
3. 右连接查询，以表 2 为标准，显示 表 2 的所有内容加表 1 存在的记录。 right join

自连接实际是将本身的一个表分为两份起两个别名，对自己的记录进行查询。适用情况是自身行数很多，不适合遍历自身查找的方法。

MySQL 不支持 outer join，但是可以用 union 方法完成，下面的例子有。

子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句（完整的 select 语句，可以充当条件 或者 数据源），外部那个select语句则称为主查询。

外键约束
对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

对已经存在的字段添加外键约束

ALTER TABLE courses ADD FOREIGN KEY(subject) REFERENCES teacher(subject);

在创建数据表时设置外键约束

CREATE TABLE teacher (
  id int(11) NOT NULL AUTO_INCREMENT,
  subject varchar(10) NOT NULL,
  name varchar(10) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8


CREATE TABLE courses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student VARCHAR(30) NOT NULL  ,
  sub VARCHAR(30) NOT NULL,
  grade INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY(subject) REFERENCES teacher(subject),
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

**删除外键约束**

```sql
-- 需要先获取外键约束名称,该名称系统会自动生成
SHOW CREATE TABLE teacher;

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

删除和修改主键

删除主键

alter table table_test drop primary key;  

然后再添加主键

alter table table_test add primary key(id);  

在添加主键之前,必须先把重复的id删除掉

例子：

-- 查询科目的种类
SELECT sub FROM courses GROUP BY sub;

-- 根据 student 和 sub 字段进行分组, 查看 student和 sub 的分组信息
SELECT GROUP_CONCAT(student), GROUP_CONCAT(sub) FROM courses GROUP BY student;

-- 统计不同科目的平均成绩
SELECT sub,ROUND(AVG(grade),2 )FROM courses GROUP BY sub; 

-- 根据 sub 字段进行分组，统计分组平均成绩大于 70 的
SELECT sub,ROUND(AVG(grade),2 )FROM courses GROUP BY sub HAVING ROUND(AVG(grade),2 ) > 70 ;


-- 内连接
SELECT * FROM courses c INNER JOIN teacher t ON c.sub = t.subject;

--左连接
SELECT * FROM courses c LEFT JOIN teacher t ON c.sub = t.subject;
--右链接
SELECT * FROM courses c RIGHT JOIN teacher t ON c.sub = t.subject;

--自连接
自连接查询
SELECT p.id, p.title, c.title, c.id FROM areas p inner join areas c on p.id = c.pid WHERE p.title = '广东省';

-- 以 学生 分组，平均成绩大于 80 的学生
SELECT student,AVG(grade) FROM courses GROUP BY student HAVING AVG(grade)>80;

--前三条记录的分数总和
SELECT SUM(grade) from (SELECT* FROM courses LIMITE 3);

-- outer join 的实现方法

SELECT * FROM courses c LEFT JOIN teacher t ON c.sub = t.subject
UNION
SELECT * FROM courses c RIGHT JOIN teacher t ON c.sub = t.subject;

--上面的用 UNION ALL 的话不去重。