新手的篇章-SQL语句(下)

1.0 分组与分组后的筛选

1.1 分组

在SQL中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。

select * from teacher group by name(需要分组的字段);

1.2 分组后的筛选

SELECT "性别",COUNT(*),GROUP_CONCAT(cname) FROM teacher GROUP BY gender;  -- 详细显示分组的具体名字

SELECT gender,AVG(age),GROUP_CONCAT(cname) FROM teacher GROUP BY gender HAVING AVG(age)>18;-- 查询男生，女生  平均年龄大于18岁的名字和性别

2.0 排序

order by 字段 默认升序
order by asc 字段 指定升序
order by desc 字段 指定降序

-- 查询18-30的男同学，进行升序排列

SELECT * FROM teacher WHERE (age BETWEEN 18 AND 30) AND gender=1 ORDER BY age;

-- 查询18-30的男同学，进行降序排列
SELECT * FROM teacher WHERE (age BETWEEN 18 AND 30) AND gender=1 ORDER BY age DESC;

-- 查询18-30的男同学，指定升序
SELECT * FROM teacher WHERE (age BETWEEN 18 AND 30) AND gender=1 ORDER BY age ASC;

3.0 限制

limit start,count
• start 为偏移量，默认起始0
• count 为条数

-- 限制，查询打印出来的数据limit start(为偏移量，默认为0),count（为条数）
SELECT * FROM teacher LIMIT 3;
注意：
• limit 不能写数学公式
• limit只能写在末尾

4.0 表连接

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

分类：

• 内连接
• 连接
  • 左连接
  • 右连接
    内连接

内连接仅选出两张表中互相匹配的记录
select * from 表1 inner  join 表2 on 表1.列 = 表2.列;

左连接
查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

select * from 表1 left join 表2 on 表1.列 = 表2.列; 

右连接
查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

select * from 表1 right join 表2 on 表1.列 = 表2.列;

子查询
某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

-- 用子查询
SELECT * FROM 市表 WHERE 市表id = (SELECT 市表id FROM 省表 HAVING 省id="湖南省");

自关联
可以简单的理解为自己与自己进行连接查询。

#一张 areas 表里面有省市区，我们需要在通过这一张表查询某省对应的所有市。
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.name="湖南";

5.0 外键

外键介绍
MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。

注意：
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

#创建表时设置外键约束
#语法：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

-- 创建班级表
CREATE TABLE classes(id INT(4) NOT NULL PRIMARY KEY,NAME VARCHAR(36));

-- 创建学生表 指定cid为外键
CREATE TABLE students(sid INT(4) NOT NULL PRIMARY KEY ,sname VARCHAR(30),cid INT(4),CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES classes(id));


注意：
• 从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。

在修改表时添加外键约束

alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

注意：
• 主表需存在时创建从表

删除外键约束

ALTER TABLE student DROP FOREIGN KEY fk_cid;