MySql数据库基础--6 表操作--分组、联接查询和视图

一、定义及使用（例题）：

1. MySQL GROUP BY 语句
   GROUP BY 语句根据一个或多个列对结果集进行分组。
   在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

SELECT 字段1, function(字段2) FROM 表名 WHERE 条件 GROUP BY 字段1;

2. 自联接(例题)
   假如你发现某条记录(其ID为 12 )的成绩存在问题,因此想知道该同学的其他成绩是否也存在这些问题。此查询要求首先找到ID为 12 的同学,然后找出这个同学的其他成绩。
   子查询解决：SELECT id,score FROM scores WHERE stu_id =(SELECT stu_id FROM scores WHERE id= 2);
   自联接查询： SELECT p1.id,p1.score FROM scores as p1,scores as p2 WHERE p1.stu_id = p2.stu_id and p2.id = 2;

用自联结而不用子查询

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多。

3. 内联接、外联接
   许多联结将一个表中的行与另一个表中的行相关联。但有时候会需
   要包含没有关联行的那些行。
   内联接，检索所有同学及其成绩：SELECT students.name,scores.score FROM students INNER JOIN scores ON students.stu_id = scores.stu_id;
   外联结语法类似。为了检索所有同学,包括那些没有及格的,
   可如下进行：
   左外联接：SELECT students.name,students.age,scores.score FROM students LEFT OUTER JOIN scores ON students.stu_id = scores.stu_id and scores.score > 60;
   右外联接同理

使用带聚合函数的联接：
SELECT students.name,COUNT(scores.score ) FROM students LEFT OUTER JOIN scores ON students.stu_id = scores.stu_id AND scores.score > 60 GROUP BY students.stu_id;

使用联结和联结条件

在总结关于联结前,有必要汇总一下关于联结及其使用的某些要点。
注意所使用的联结类型。
一般我们使用内部联结,但使用外部联结也是有效的
保证使用正确的联结条件,否则将返回不正确的数据
应该总是提供联结条件,否则会得出笛卡儿积。
在一个联结中可以包含多个表,甚至对于每个联结可以采用不同
的联结类型。虽然这样做是合法的,一般也很有用,但应该在一
起测试它们前,分别测试每个联结。这将使故障排除更为简单。

二、基本的sql语句：

修改为唯一约束：alter table 表名 modify 字段 float not null unique;

删除唯一约束：alter table 表名 drop index 字段;

添加外键约束：alter table 表名 add constraint fk_stu_id foreign key(子表字段) references 父表(父表字段);

子查询(并分组)：select max(表1字段),表2字段 from 表1,表2 where 表1.字段 = 表2.字段 group by 表2字段;

子查询，嵌套查询：select name from 主表 where 主键 in (select 外键 from 子表 where score < 60);

联接两个表查询数据并按倒序排序：select 字段1,字段2 from 父表,子表 where 父表.主键 = 子表.外键 order by 字段 desc;

内联接查询：select 字段,字段1,字段2,字段3 from 父表 inner join 子表 on 父表.主键 = 子表.外键 and 字段2 < 60;　－>(注：on也可以是where,不过where不如on性能好．　　and这块是条件，可以是其他的条件)

自联接查询：** select a.字段1,b.字段2 from 表1 as a,表1 as b where a.stu_id = b.stu_id and b.id = 2;

左外联接：select 父表.字段1,父表.字段2,子表.字段3 from 父表 left outer join 子表 on 父表.主健 = 子表.外键 and 子表.字段3 > 60;
右外联接同理

创建视图：create view 视图名 as select * from 表名;