数据库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;