数据库Day004

分组查询

凡和聚合函数同时出现的列名，一定要出现在聚合函数的后面

按照某个字段来进行分组，分组后可以进行数据统计，分组查询需要使用group by关键字实现

#查询每个部门编号和每个部门的工资和
SELECT deptno ,SUM(sal) FROM emp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门的人数
SELECT deptno as '部门编号',count(*) as '部门人数' FROM emp GROUP BY deptno;		
#查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno as '部门编号',count(*) as '高于1500' FROM emp where sal>1500 GROUP BY deptno;

HAVING字句：对分组以后的数据再次进行过滤

#查询工资总和大于9000的部门编号以及工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
#查询部门编号以及每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

LIMIT

对查询出来的结果进行显示的限定(前面是开始的下标，后面的是条数)

SELECT * FROM emp
#查询emp表中的数据，显示前三条
SELECT * FROM emp LIMIT 0,3;
#查询emp表中的数据。从第三调(记录下标为2)开始显示，一共显示3条
SELECT * FROM emp LIMIT 2,3;	

查询语句书写顺序:SELECT---FROM---WHERE---GROUP BY---HAVING---ORDER BY---LIMIT
查询语句执行顺序:FROM---WHERE---GROUP BY---HAVING---SELECT---ORDER BY---LIMIT

数据完整性

实体完整性
实体：即表中的一行（一条记录）代表一个实体（entity）   实体完整性的作用：标识每一行数据不重复

约束类型： 主键约束（primary key）  唯一约束(unique)  自动增长列(auto_increment)
    主键约束（primary key）
        注：每个表中要有一个主键。
        特点：数据唯一，且不能为null
    唯一约束（unique）
        特点：数据不能重复
    自动增长列(auto_increment)
         sqlserver数据库 (identity)  oracle数据库( sequence)

主键约束(PRIMARY KEY)

主键：主键具有唯一性，不能为空

#第一种创建方式
CREATE TABLE student(
id int primary key,
name varchar(50)
);
#第二种创建方式，该方式可以创建联合主键
CREATE TABLE `student2`(
`id` int,
`name` varchar(50),
primary key(id)
);
#第三种创建方式
CREATE TABLE `student3`(
`id` int,
`sid` int,
`name` varchar(50),
primary key(`id`,`sid`)
);
#表创建好了忘了创建主键，可以通过修改来添加主键
CREATE TABLE `student4`(
`id` int,
`name` varchar(50)
);
alter table student4 add PRIMARY KEY (`id`);

唯一约束

CREATE TABLE `student5`(
`id` int PRIMARY KEY,
`name` varchar(50) UNIQUE
);

域完整性

    域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
    域代表当前单元格
    域完整性约束：数据类型 非空约束（not null） 默认值约束(default)  
    check约束（mysql不支持）check(sex='男' or  sex='女')

引用完整性

    外键约束：FOREIGN KEY 
        CONSTRAINT fk_score_sid foreign key (sid) references student(id)
        第二种添加外键方式。ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

表与表之间的关系
    

多表查询

合并结果集(UNION)

就是将两个Select语句的查询结果合并到一起
合并结果集的要求：被合并的两个结果：列数、列类型必须相同

SELECT * from t1 UNION SELECT * FROM t2

连接查询(非常重要)

内连接(inner join)：内连接的特点：查询结果必须满足条件

#标准内连接
SELECT * FROM emp e inner join dept d on e.deptno = d.deptno
SELECT e.empno, e.ename,e.deptno,d.loc FROM emp e inner join dept d on e.deptno=d.deptno

外连接：查询出的结果存在不满足条件的可能
左连接(LEFT OUTER JOIN)：左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL

SELECT * FROM emp LEFT OUTER JOIN dept on emp.deptno=dept.deptno;

右连接(RIGHT OUTER JOIN)：右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

SELECT * from emp RIGHT outer join dept on emp.deptno=dept.deptno;

连接查询心得
        连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

自然连接(NATURAL JOIN)

连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式
两张连接的表中名称和类型完全一致的列作为条件

SELECT * FROM emp NATURAL JOIN dept;
#自然左外连接
SELECT * FROM emp NATURAL LEFT JOIN dept;
#自然右外连接
SELECT * FROM emp NATURAL RIGHT JOIN dept;