数据库高级

数据库的完整性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类

  • 实体完整性(行完整性)
  • 域完整性(列完整性)
  • 引用完整性(关联表完整性)
  • 主键约束:primary key 唯一约束:unique [key] 非空约束:not null 默认约束:default 自动增长:auto_increment 外键约束: foreign key

create table student(
studentno int primary key auto_increment,
pwd varchar(20) not null default '123',
name varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default '第一大道',
borndate datetime,
email varchar(50)
);

实体完整性 

实体:即表中的一行(一条记录)代表一个实体(entity)

实体完整性的作用:标识每一行数据不重复

约束类型:

主键约束(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);

唯一约束(unique) 特点:数据不能重复。

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

自动增长列(auto_increment)列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );


域完整性 

域代表当前单元格

作用:限制单元格的数据正确,不对照此列的其它单元格比较

域完整性约束:数据类型 非空约束(not null) 默认值约束(default)

引用完整性

外键约束:FOREIGN KEY

CREATE TABLE student(id int primary key, name varchar(50) not null, sex
varchar(10) default '男' );
constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
(外键列的数据类型一定要与主键的类型一致)
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );
第二种添加外键方式
ALTER score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES
stu(id);

多表查询

关系

  • 一对多/多对一
  • 一对一
  • 多对多

一对一的两种建表原则:

  • 唯一外键对应:假设一对一是一个一对多的关系,在多的一方创建一个外键指向一的一方的主键,将外键设置为unique(外键原本是重复的).
  • 主键对应:让一对一的双方的主键建立关系(一张表的主键reference另一张表的主键,既是主键列又是外键列)

多表查询

1. 合并结果集:UNION 、 UNION ALL
2. 连接查询
 2.1内连接 [INNER] JOIN ON
 2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
- 右外连接 RIGHT [OUTER] JOIN
 - 全外连接(MySQL不支持)FULL JOIN
 2.3 自然连接 NATURAL JOIN
3.子查询

合并结果集

作用:合并结果集就是把两个select语句的查询结果合并到一起! 合并结果集有两种方式: UNION:去除重复记录,如:SELECT* FROM t1 UNION SELECT * FROM t2;

UNION ALL:不去除重复记录,如:SELECT * FROM t1 UNION ALL SELECT * FROM t2。

注意:被合并的两个结果:列数、列类型必须相同。

连接查询

连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。

select * from emp,dept;

连接查询会产生笛卡尔积

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

内连接

select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
等价于
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他的条件)
注意:找两张表的等值关系时,找表示相同含义的列作为等值关系。

//查询学生表中的学生姓名和分数表中的分数
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 主表 left join 次表 on 主表.列名=次表.列名

-- 1.主表数据全部显示,次表数据匹配显示,能匹配到的显示数据,匹配不成功的显示null

-- 2.主表和次表不能随意调换位置

使用场景:一般会作为子查询的语句使用

自然连接

自然连接(NATURAL INNER JOIN):自然连接是一种特殊的等值连接,他要求两个关系表中进行连 接的必须是相同的属性列(名字相同),无须添加连接条件,并且在结果中消除重复的列。

子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么 就是子查询语句。

子查询出现的位置:

  • where后,作为条为被查询的一条件的一部分;
  • from后,作表;

当子查询出现在where后作为条件时,还可以使用如下关键字:

  • any
  • all

子查询结果集的形式:

a. 单行单列(用于条件) b. 单行多列(用于条件) c. 多行单列(用于条件) d. 多行多列(用于表)

工资高于30部门所有人工资,其中30部门所有人工资是子查询。高于所有需要使用all关键
字。
第一步:查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步:查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (第一步)
(也可以使用MAX聚合函数)
结果:
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

子查询作为条件
子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

多表删除

语法:
 delete 被删除数据的表 from 删除操作中使用的表
 where 限定条件
 注:多张表之间使用逗号间隔
 示例:
 //删除部门1的信息
 delete d,e,s from department d,employee e,salary s
 where d.depid=e.depid and s.empid=e.empid and depname='部门1'

日期运算函数

 date_add(日期,interval 计算值 计算的字段);
 注:计算值大于0表示往后推日期,小于0表示往前推日期
例:
 date_add(now(),interval -20 year);//20年前的日期

数据库优化 (sql层面)

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=100 or Name = 'admin'

可以使用select id from t where num = 10 union all select id from t where Name = 'admin'

5.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 也是一个好的选择

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值