第一 数据库完整性
用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束 完整性的分类：
- 实体完整性(行完整性):
- 域完整性(列完整性):
- 引用完整性(关联表完整性):
主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key
建议这些约束应该在创建表的时候设置 多个约束条件之间使用空格间隔
1.2实体完整性
实体：即表中的一行（一条记录）代表一个实体
实体完整性的作用：标识每一行数据不重复。
约束类型：
主键约束（primary key）
唯一约束(unique)
自动增长列(auto_increment)
1.2.1主键约束 primary key
注：每个表中要有一个主键。 特点：数据唯一，且不能为null
主键的添加方式
CREATE TABLE student( id int primary key, name varchar(50) );
CREATE TABLE student( id int, name varchar(50), primary key(id) );
CREATE TABLE student( classid int, stuid int, name varchar(50), primary
key(classid,stuid) );
/*创建表时添加主键
CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);
/*创建表后 再添加主键
1.2.2唯一约束 unique
特点：数据不能重复。
CREATE TABLE student( Id int primary key, Name varchar(50) unique );
1.2.3自动增长列 auto increment
sqlserver数据库 (identity-标识列)
oracle数据库(sequence-序列
给主键添加自动增长的数值，列只能是整数类型
CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values(‘tom’);
1.3域完整性
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)
1.3.1数据类型
1.3.2非空约束
not null
则该数据不可为空
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex
varchar(10) ); INSERT INTO student values(1,’tom’,null);
1.3.3默认值约束
default
默认该值为xx (即插入数据时，若没有录入该列数据，则为默认值)
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex
varchar(10) default '男' );
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);
1.4引用完整性
又叫 参照完整性
外键约束：foreign key
语法：constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
CREATE TABLE student(id int primary key, name varchar(50) not null, sex
varchar(10) default '男' );
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );
外键列的数据类型一定要与主键的类型一致
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES
stu(id);/*第二种添加外键类型
第二 多表查询
多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列
2.1多表的关系
2.1.1一对多/多对一
客户和订单（一对多），分类和商品（多对一），部门和员工（一对多）
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
2.1.2多对多关系
学生和课程
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键.
2.1.3一对一关系
在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外 键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.
2.2多表查询
多表查询有如下几种：
-
合并结果集:UNION 、 UNION ALL
-
连接查询
2.1内连接 [INNER] JOIN ON
2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
-右外连接 RIGHT [OUTER] JOIN
-全外连接（MySQL不支持）FULL JOIN
2.3 自然连接 NATURAL JOIN
-
子查询
2.2.1合并结果集
作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
- UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
- UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同
2.2.2连接查询
连接查询就是求出多个表的乘积，例如 t1 连接 t2 ，那么查询出的结果就是 t1*t2.
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过 条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
示例：现有两张表
emp–员工表 dept–部门表
CREATE TABLE dept1(
deptno int primary key,
dname varchar(14),
loc varchar(13)
);
insert into dept1 values(10,'服务部','北京');
insert into dept1 values(20,'研发部','北京');
insert into dept1 values(30,'销售部','北京');
insert into dept1 values(40,'主管部','北京');
CREATE TABLE emp1(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal double,
comm double,
deptno int
);
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);
select * from emp,dept;
使用主外键关系做为条件来去除无用信息
select *from emp,dept where empt.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列 了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
一：内连接
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
等价于：
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)
注:
<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
//查询学生表中的学生姓名和分数表中的分数
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid
等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid
三表联查：
语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名
where
等价于:
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名
SQL标准的内连接为：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
二：外连接
包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
-- 显示还没有员工的部门名称?
-- 外联查询
-- 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
-- 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
-- 2.主表和次表不能随意调换位置
使用场景:一般会作为子查询的语句使用
select depname,name from
(select e.*,d.depname from department d left join employee e
on e.depid=d.depid
) aa where aa.name is null;
右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
a.左外连接
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的 显示NULL。
insert into emp values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);
我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部 门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为 emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示 NULL。
b.右外连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在 dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相 应的员工信息为NULL。
insert into dept values(60,'颜值部','成都');
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
连接查询心得：
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要 整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多 数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不 是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两 张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
三：自然连接
自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连 接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
语句：
select * from emp e natural join dept d;
2.2.3子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么 就是子查询语句了。
子查询出现的位置：
- a. where后，作为条为被查询的一条件的一部分；
- b. from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字：
- a. any
- b. all
子查询结果集的形式：
- a. 单行单列（用于条件）
- b. 单行多列（用于条件）
- c. 多行单列（用于条件）
- d. 多行多列（用于表）
示例：
- 工资高于JONES的员工。
分析：
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES';
第二步：查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (第一步结果);
结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
- 查询与SCOTT同一个部门的员工。
子查询作为条件
子查询形式为单行单列
分析：
查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
第一步：查询SCOTT的部门编号
SELECT deptno FROM emp WHERE ename='SCOTT';
第二步：查询部门编号等于SCOTT的部门编号的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');
-
工资高于30号部门所有人的员工信息
分析：
SELECT * FROMemp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键 字。
第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (第一步)
结果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
子查询作为条件
子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）
数据库优化
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫 描，
如： select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or Name = 'admin'
可以这样查询：
select id from t where num = 10
union all
select id from t where Name = 'admin'
- in 和 not in 也要慎用，否则会导致全表扫描，
如： select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择
扩展
4.1 多行新增
insert into 表名(列名) values (列值),(列值),(列值)；
4.2 多表更新
(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
示例：update employee e,salary s
set title='助工',salary=1000
where e.empid=s.empid and name='李四'
4.3 多表删除
语法： delete 被删除数据的表
from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
示例：
//删除人事部的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname='人事部'
4.4 日期运算函数
now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
date_add(now(),interval -40 year);//40年前的日期
练习：
数据表:
雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别(sex)，职称(title)，出生日期(birthday)，所属部门(depid)
部门(department):部门编号(depid,主键)，部门名称(depname)
工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction
需求:
修改表结构，在部门表中添加部门简介字段
alter table department add desc varch(50);
将李四的职称改为“工程师”,并将她的基本工资改成2000，职务工资为700
update employee e inner join salary s on e.empid=s.empid
set e.title='工程师',s.basesalary=2000,s.titlesalary=700
where e.name='李四';
删除人事部门的部门记录
delete from department where dename='人事部';
查询出每个雇员的雇员编号，实发工资，应发工资
select empid,basesalary+titlesalary-deduction '实发工资',basesalary+titlesalary '应发工资' from salary
查询姓张且年龄小于40的员工记录
select * from employee
where name like '%张' and data_add(now(),interval -40 year) < birthday
查询雇员的雇员编号，姓名，职称，部门名称，实发工资
select e.emplid,e.name,e.title,d.depname,basesalary+titlesalary-deduction '实发工资' from employee e,department d, salary s
where e.empid=s.empid and d.depid=e.depid;
查询销售部门的雇员姓名，工资
select e.name,s.basesalary,s.titlesalary,s.deduction
from employee e,department d,salary s
where e.empid=s.empid and d.depid=e.depid angd.dname='销售部';
统计各职称的人数
select title count(*) from employee group by title;
统计各部门的部门名称，实发工资总和，平均工资
select d.depname, sum(s.basesalary+s.titlesalary-s.deduction),avg(s.basesalary+s.titlesalary-s.deduction)
from employee e, salary s,department d
where e.empid=s.empid and e.depid=d.depid group by d.depname;
查询比销售部门所有员工基本工资都高的雇员姓名
select name from employee e,salary s
where e.empid=s.empid and s.basesalary > all(select s.basesalary from employee e, salary s,department d where e.empid=s.empid and e.depid=d.depid and d.depname='销售部');
select name from employee e,salary s
where e.empid=s.empid and s.basesalary > all(select max(s.basesalary) from employee e, salary s,department d where e.empid=s.empid and e.depid=d.depid and d.depname='销售部');
扫一扫
831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
