数据库高级-数据库完整性分类和多表查询

最新推荐文章于 2024-05-19 03:18:57 发布
最新推荐文章于 2024-05-19 03:18:57 发布
阅读量236 收藏 1
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/What_dapan/article/details/119363187
版权

数据库高级

文章目录

一、数据完整性

1、数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

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

  • 完整性的分类：

    • 实体完整性(行完整性)；

    • 域完整性(列完整性)；

    • 引用完整性(关联表完整性)；

      • 主键约束：primary key

      • 唯一约束：unique [key]

      • 非空约束：not null

      • 默认约束：default

      • 自动增长：auto_increment

      • 外键约束：foreign key

建议这些约束应该在创建表的时候设置 多个约束条件之间使用空格间隔

示例：

create table student(
	studentno int primary key auto_increment,
	loginPwd varchar(20) not null default '123456',
	studentname 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)
);

2、实体完整性

  • 实体：即表中的一行(一条记录)代表一个实体（entity）

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

  • 约束类型：

    • 主键约束（primary key）
    • 唯一约束(unique)
    • 自动增长列(auto_increment)
2.1 主键约束（primary key）

  • 注：每个表中只能有一个主键。

  • 特点：数据唯一，且不能为null

  • 第一种添加方式：

#create table 表名(列名1 数据类型 住键约束 约束条件，列名2 数据类型 约束条件);

create table student( id int primary key, name varchar(50) );
  • 第二种添加方式：此种方式优势在于，可以创建联合主键
#create table 表名(列名1 数据类型 ，列名2 数据类型，住键约束(列名1,列名2,...))

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) );
  • 第三种添加方式：
#先创建表
#通过alter table 表名 add 约束(列名)

create table student( id int, name varchar(50) );
alter table student add primary key(id);
  • 添加好主键约束后 可以通过如下语句查看
#desc 表名;
#show keys from 表名;
  • 删除主键约束
alter table 表名 drop primary key;
  • 注意：删除主键约束以后 不重复的特性取消了 非空特性还在，可以按如下设置，该列允许为空
#alter table 表名 modify 列 数据类型（长度） null;
#alter table 表名 change 原列 新列 数据类型（长度） null;
2.2 唯一约束（unique）
  • 特点：
    • 唯一约束，要求该列唯一，允许为空，但只能出现一个空值。
    • 唯一约束可以确保一列或者几列不出现重复值
  • 添加唯一约束
create table student( Id int primary key, Name varchar(50) unique );
  • 删除唯一约束
alter table 表名 drop index 列名;
2.3 自动增长列（auto_increment）

  • 给主键添加自动增长的数值，列只能是整数类型

  • 一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分

create table student( Id int primary key auto_increment, Name varchar(50) );
insert into student(name) values(‘tom’); #id默认从1开始自增，每次+1
  • 添加主键之后若想要让主键自增,可以做相应的设计
#alter table 表名 modify 主键名 数据类型(长度) auto_increment;
#alter table 表名 change 原主键名 新主键名 数据类型(长度) auto_increment;

3、域完整性

  • 域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

  • 域代表当前单元格

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

  • check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

3.1 数据类型
3.1.1 数值类型
类型大小范围（有符号）范围（无符号 ）用途
tinyint1字节(-128，127)(0，255)小整数值
smallint2字节(-32 768，32 767)(0，65 535)大整数值
mediumint3字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
int4字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
bigint8字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
float4字节(-3.403E+38,-1.175E-38),0,(1.175E-38,3.403E+38)0,(1.175E-38,3.403E+38)单精度浮点数值
double8字节(-1.798E+308,-2.225E-308),0,(2.225E-308,1.798E+308)0,(2.225E-308,1.798E+308)双精度浮点数值
3.1.2 日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性

类型大小范围格式用途
DATE3字节（ 1000-01-01，9999-12-31 ）YYYY-MM-DD日期值
TIME3字节（’-838:59:59’，‘838:59:59’）HH:MM:SS时间值或持续时间
YEAR1字节（1901，2155）YYYY年份值
DATETIME8字节（1000-01-01 00:00:00，9999-12-31 23:59:59 ）YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4字节（1970-01-01 00:00:00，2038 结束时间是第 2147483647 秒，北京时间 2038-1- 19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07）YYYYMMDD HHMMSS混合日期和 时间值，时间戳 ,当更新数据的时候自动添加更新时间
3.1.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节不定长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

CHAR和VARCHAR类型类似，CHAR保留尾部空格，VARCHAR则不保留,VARCHAR更节省空间，但VARCHAR要截取长度，所以CHAR效率更高

示例：

create table book (name varchar(20),info char(50));
insert into book(name,info) values ('MySQL学习指南','这是一本MySQL学习教材');

#其中，name占用了9个字节，而info占了50个字节
#mysql中汉字占一个字节
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);
3.3 默认值约束

default

如果没有给值，就填充默认值

create table student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default '男' );
insert into student values(1,'tom','女');		#1 tom 女
insert into student values(2,'jerry',default);	#2 jerry 男
insert into student(id,name) values (3,'bob');	#3 bob 男

4、引用完整性（参照完整性）

外键约束：FOREIGN KEY

  • 外键用来在两个表的数据之间建立连接，可以是一列或者多列。
  • 一个表可以有一个或多个外键。
  • 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键必须等于另一个表中主键的某个值
  • 外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另一个表的主键。
  • 外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行
  • 外键的作用是保持数据的一致性、完整性

示例:

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) );

# constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
#外键列的数据类型一定要与主键的类型一致
  • 第二种添加外键方式
#alter table 被修改表名 add constraint 自定义外键名 foreign key(外键列) references 关联表名(关联表主键列);	

alter table score add constraint fk_stu_score foreign key(sid) references student(id);
  • 查看外键约束
#show keys from 表;
#desc 表;
#show create table 表名

如果数据表之前存在外键关联的情况：

  • 如果直接删除父表，结果会显示失败；-------因为直接删除将破坏表的参照完整性

  • 如果必须删除，可以先删除与它关联的子表，再删除父表

  • 如果要保留子表，单独删除父表，只需将表的外键约束取消，然后再删除父表

#DROP TABLE dept;  ----> ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

#ALTER TABLE 引用的表名 DROP FOREIGN KEY 约束名称;  #先删除外键关系
#DROP TABLE dept; #再删除表

二、多表查询

1、多表的关系

1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

#客户和订单
#一个客户可以拥有多个订单

#客户表
create table customer(
    id int primary key auto_increment,
    name varchar(10));
    
#订单表
create table orders(
    id int primary key auto_increment,
    name varchar(50) not null,
    cid int, 
    constraint fk_order_customer foreign key(cid) references customer(id));
    
#插入数据
insert into customer(id,name) values (10,'张三');
insert into customer(id,name) values (20,'李四');
insert into orders(name,cid) values('生活用品',10);
insert into orders(name,cid) values('游戏娱乐',10);
insert into orders(name,cid) values('教育',20);
1.2 多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键

#学生表
create table student(
    id int primary key,
    name varchar(10)not null);
    
#课程表
create table corse(
    id int primary key,
    name varchar(10) not null);
    
#学生和课程中间表
create table stu_corse(id int primary key auto_increment,sid int,cid,int);

#插入数据
insert into student(id,name)
values
(10,'张三'),
(20,'李四'),
(30,'王五');

insert into corse(id,name)
values
(1,'语文'),
(2,'数学'),
(3,'英文');

insert into stu_corse(sid,cid)
values
(10,1),
(10,2),
(20,2),
(20,3),
(30,1),
(30,2),
(30,3);
1.3 一对一关系

两种建表原则：

  • 唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

  • 主键对应：让一对一的双方的主键进行建立关系.

#唯一外键对应
create table people(
    id int primary key,
    name varchar(10),
	constraint fk_people_IDCard foreign key(id) references IDCard(id));
	
create table IDCard(id int primary key,num char(18));

#添加数据
insert into IDCard(1,'152111123111425815');
insert into people(1,'李四');

#主键对应
create table wife(id int primary key,name varchar(10),hid int unique);

create table husband(id int primary key,name varchar(10));

#添加数据
insert into husband values (1,'张三');
insert into wife values(2,'李四',1);

2、多表查询

2.1 分类

  • 合并结果集:UNION 、UNION ALL

  • 连接查询

    • 内连接 [INNER] JOIN ON
    • 外连接 OUTER JOIN ON
      • 左外连接 LEFT [OUTER] JOIN
      • 右外连接 RIGHT [OUTER] JOIN
      • 全外连接（MySQL不支持）FULL JOIN
    • 自然连接NATURAL JOIN

  • 子查询

2.2 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

  • UNION：去除重复记录

  • UNION ALL：不去除重复记录

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

create table t1(id int primary key,name varchar(10));

create table t2(id int primary key,info varchar(10));

insert into t1
values
(1,'张三'),
(3,'王五');

insert into t2
values
(1,'赵依依'),
(3,'王五'),

#UNION
select * from t1 union select * from t2;
#查询结果
# id, name
#'1', '张三'
#'3', '王五'
#'1', '赵依依'

#UNION ALL
select * from t1 union all select * from t2;
#查询结果
# id, name
#'1', '张三'
#'3', '王五'
#'1', '赵依依'
#'3', '王五'
2.3 连接查询

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

select * from t1,t2;
#查询结果
# id, name, id, info
#'3', '王五', '1', '赵依依'
#'1', '张三', '1', '赵依依'
#'3', '王五', '3', '王五'
#'1', '张三', '3', '王五'

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。

示例

#部门表
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 emp1,dept1;

#查询结果
# empno, ename, job, mgr, hiredate, sal, comm, deptno, deptno, dname, loc
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '40', '主管部', '北京'
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '30', '销售部', '北京'
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '20', '研发部', '北京'
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '10', '服务部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '40', '主管部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '30', '销售部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '20', '研发部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '10', '服务部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '40', '主管部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '30', '销售部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '20', '研发部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '10', '服务部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '40', '主管部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '30', '销售部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '20', '研发部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '10', '服务部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '40', '主管部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '30', '销售部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '20', '研发部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '10', '服务部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '40', '主管部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '30', '销售部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '20', '研发部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '10', '服务部', '北京'

#使用主外键关系做为条件来去除无用信息
select * from emp1,dept1 where emp1.deptno = dept1.deptno;

#查询结果
# empno, ename, job, mgr, hiredate, sal, comm, deptno, deptno, dname, loc
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '10', '服务部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '20', '研发部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '20', '研发部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '30', '销售部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '30', '销售部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '40', '主管部', '北京'

#上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
select emp1.ename,emp1.sal,emp1.comm,dept1.dname
from emp1,dept1
where emp1.deptno=dept1.deptno;

#查询结果
# ename, sal, comm, dname
#'赵依依', '1000', '2010', '服务部'
#'钱二二', '1100', '2000', '研发部'
#'孙姗姗', '1020', '2011', '研发部'
#'李思思', '1010', '2002', '销售部'
#'周武武', '1001', '2003', '销售部'
#'吴六六', '1011', '2004', '主管部'
2.3.1 内连接

语法：

select 列名 from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....

等价于

select 列名 from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)

注意：

  • 表1和表2的顺序可以互换
  • 找两张表的等值关系时,找表示相同含义的列作为等值关系。
  • 点操作符表示“的”,格式：表名.列名
  • 可以使用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.列名 and ...
2.3.2 外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

  • 左外联：select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

  • 主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

  • 主表和次表不能随意调换位置

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

select * from 表A left/right [outer] join 表B on 条件
  • 两张表格A和B 取决于谁的数据在左边显示
    • A表格先出现 A左边显示
    • B表格后出现 B右边显示
  • left和right来控制以哪一个表格的数据作为基准
  • 作为基准的表格数据必须全部显示出来
  • 非基准的表格按照on条件与之拼接
  • 若找到条件拼接 则正常显示 若找不到满足条件的则 null
#左外连接
#新增一条数据
insert into emp1 values(1007,'郑琪琪','主管',1006,'2019-6-1',1011,2004,50);

select * from emp1 e
left outer join dept1 d
on e.deptno=d.deptno;

#查询结果
# empno, ename, job, mgr, hiredate, sal, comm, deptno, deptno, dname, loc
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '10', '服务部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '20', '研发部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '20', '研发部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '30', '销售部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '30', '销售部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '40', '主管部', '北京'
#'1007', '郑琪琪', '主管', '1006', '2019-06-01', '1011', '2004', '50', NULL, NULL, NULL  		#dept1表中无对应项以NULL代替

#右外连接
#新增一条数据
insert into dept1 values(60,'颜值部','成都');
#outer可以省略
select * from emp1 e
right join dept1 d
on e.deptno=d.deptno;

#查询结果
# empno, ename, job, mgr, hiredate, sal, comm, deptno, deptno, dname, loc
#'1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '10', '10', '服务部', '北京'
#'1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '20', '20', '研发部', '北京'
#'1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '20', '20', '研发部', '北京'
#'1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '30', '30', '销售部', '北京'
#'1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '30', '30', '销售部', '北京'
#'1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '40', '40', '主管部', '北京'
#NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '60', '颜值部', '成都'						#emp1表中无对应项以NULL代替
2.3.3 自然连接

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

select * from emp1 e natural join dept1 d; #inner可以省略

#查询结果(可以看见deptno这列只出现了一次)
# deptno, empno, ename, job, mgr, hiredate, sal, comm, dname, loc
#'10', '1001', '赵依依', '文员', '1006', '2019-01-01', '1000', '2010', '服务部', '北京'
#'20', '1002', '钱二二', '程序员', '1006', '2019-02-01', '1100', '2000', '研发部', '北京'
#'20', '1003', '孙姗姗', '程序员', '1006', '2019-03-01', '1020', '2011', '研发部', '北京'
#'30', '1004', '李思思', '销售', '1006', '2019-04-01', '1010', '2002', '销售部', '北京'
#'30', '1005', '周武武', '销售', '1006', '2019-05-01', '1001', '2003', '销售部', '北京'
#'40', '1006', '吴六六', '主管', '1006', '2019-06-01', '1011', '2004', '主管部', '北京'
2.4 子查询

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

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

示例：

#查询与钱二二在同一个部门的员工

#1、查询钱二二的部门编号
select deptno from emp1 where ename = '钱二二';

#2、查询部门编号等于钱二二的部门编号的员工
select * from emp1 where deptno = (select deptno from emp1 where ename = '钱二二');

#查询工资高于30部门所有人的员工信息

#1、查询30部门所有人的工资
select max(sal) from emp1 where deptno = 30;
#2、查询高于30部门所有人工资的员工信息
select * from emp1 where sal > (select max(sal) from emp1 where deptno = 30);

三、数据库优化

  • 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

    • 如： select id from t where num is null
    • 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

  • 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

  • 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

  • in 和 not in 也要慎用，否则会导致全表扫描

  • 对于连续的数值，能用 between and 就不要用 in 了

  • 很多时候用 exists 代替 in 是一个好的选择

四、其他

1、日期运算函数

  • now() 获得当前系统时间
  • year(日期值) 获得日期值中的年份
  • date_add(日期,interval 计算值 计算的字段);
  • 注：计算值大于0表示往后推日期，小于0表示往前推日期
    • 示例： date_add(now(),interval -40 year);//40年前的日期
#now()
select now();

#查询结果
# now()
#'2021-08-04 00:11:57'

#year(日期值)
select year(hiredate) from emp1;

#查询结果
# year(hiredate)
#'2019'
#'2019'
#'2019'
#'2019'
#'2019'
#'2019'
#'2019'


#date_add(日期,interval 计算值 计算的字段)
select adte_add(hiredate,interval 20 day),hiredate from emp1;

#查询结果
# date_add(hiredate,interval 20 day), hiredate
#'2019-01-21', '2019-01-01'
#'2019-02-21', '2019-02-01'
#'2019-03-21', '2019-03-01'
#'2019-04-21', '2019-04-01'
#'2019-05-21', '2019-05-01'
#'2019-06-21', '2019-06-01'
#'2019-06-21', '2019-06-01'

2、三大范式

  • 数据库设计的范式–表之间的关系
    设计数据库时，遵循的不同规范，这些规范统称为范式
    范式的目的是为了减少数据库中的冗余
    管理表格的时候变得容易（修改 删除）
    查询的时候可能涉及到表格联合的问题（性能）

  • 1NF
    数据的原子性
    数据库中的每一张表格设计每一个列都是不可分割的（行列交叉点的单元格内只存储一个数据）
    每一个表格必须有主键约束（快速查询某一行记录）

  • 2NF
    在满足第一范式的前提下
    不允许出现部分依赖性
    （非主键列不能受到主键列或主键的一部分影响）

  • 3NF
    在满足前两个范式的前提下
    不允许出现传递依赖性
    （非主键列不能受到非主键列或非主键列的一部分影响）

3、多行新增

insert into 表名(列名) values (列值),(列值),(列值);

4、多表更新

#(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='李四'

5、多表删除

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='人事部'
What大潘
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据库系统-招标参数---模板.doc
02-27
" " "品中具有完整的管理、调优、调度与开发工具 " " " "支持网络上同构或异构数据库之间的数据的有效传" " " "输和冗余性复制；具有多种复制功能模块 " " " "支持ODBC 3.0、X/Open XA、CLI、JDBC等标准 " " " "支持...
数据完整性的分类
风~忆
11-09 4015
数据完整性分为3类 实体完整性、域完整性、引用完整性。 实体完整性表示数据表中的数据要保证唯一性、不重复、可以使用primarykey 或 unique开约束 域完整性是指数据表中的字段要符合逻辑要求，是个有效的数据、符合常理。可以添加check约束 或者     default约束 引用完整性是：数据库表与表之间的字段是有联系的，有时候一张表的数据需要引用另一张表的数据。则可以使用fore
数据库中的四种完整性
YuStart的博客
11-09 2632
1.实体完整性 表中的每一行数据都反映不同的实体,不能存在相同的数据行. 表示整行独一无二 （数据不重复） 实现方法 索引 唯一约束 主键约束 标识列属性 2.域完整性 指给定列的输入有效性。 实现方法 限制数据类型 检查约束 输入格式 外键约束 默认值 飞空约束 3.参照完整性 参照完整性是对关系间引用数据的一种限制。即：若属性组A是基本关系R1的外码，它与基本关系R2的主码K相对应，则R1中每个元组在A上的值必须：要么取空值，要么等于R2中某元组的主码值。 实现方法 外键约束 4.自定义完整
MOOC战德臣数据库课程自用笔记_6_数据库完整性
负负得正的博客
03-07 295
数据库完整性 一. 概述 1. 定义 数据库完整性是指 DBMS 应保证的 DB 的一种特性，即在任何情况下的正确性，有效性，一致性。 广义完整性：语义完整性，并发控制，安全控制，DB 故障恢复等 狭义完整性：专指语义完整性，DBMS 通常有专门的完整性管理机制与程序来处理语义完整性的问题 2. 关系模型中有完整性定义要求： 实体完整性 参照完整性 用户自定义完整性 3. 引发完整性约束的原因 不正当的数据库操作，如输入错误，操作失误，程序处理失误等 4. 数据库完整性管理的作用 防止和避免数
mysql数据完整性分为_数据库有哪几种完整性
weixin_32024145的博客
01-25 2681
数据库有哪几种完整性发布时间：2020-06-01 22:20:13来源：亿速云阅读：262作者：鸽子什么是数据库完整性数据库完整性可确保输入至数据库中的数据，是准确、有效及一致的。数据库中任何数据改动，都必须乎合所有完整性限制及数据有效性检验。1、实体完整性实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能...
数据库中的三种完整性
Daci Studio
06-24 2万+
数据库中的三种完整性 域完整性 域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值. 实体完整性 实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同...
《高级数据库技术》课件第2章 完整性与视图.ppt
06-04
《高级数据库技术》课件第2章 完整性与视图
mysql数据库高级，mysql优化.rar
07-28
索引可以加速数据检索，而外键可以建立表之间的关联关系，保证数据的完整性和一致性。 第一范式（1NF）：确保每个数据字段都是原子的，即不可再分割。 第二范式（2NF）：通过将非主键字段与主键字段直接关联，消除...
04735自考-数据库原理及应用课程复习重点.doc
04-04
1．分为四个阶段：人工管理阶段、文件系统阶段、数据库阶段和高级数据库阶段。 2．数据库阶段数据管理的特点： 1）采用数据模型表示复杂的数据结构。 2）有较高的数据独立性。 3）数据库系统为用户提供了方便的用户...
完整版数据库系统教程 数据库原理与应用PPT课件 第06章 高级查询（共68页）.ppt
12-18
完整版数据库系统教程 数据库原理与应用PPT课件 第08章 数据完整性约束（共50页）.ppt 完整版数据库系统教程 数据库原理与应用PPT课件 第09章 关系规范化理论（共99页）.ppt 完整版数据库系统教程 数据库原理与应用...
数据库4之完整性约束的概念及分类
qq_41086461的博客
06-25 8231
之前的文章有讲到数据库的基本概念，怎么绘制E-R图，以及表的各种概念等等这篇文章讲的是数据的完整性约束。完整性约束：规定了什么样的数据能够存储到数据库系统当中。当写入的数据不满足当前的约束的时候，就不允许写入。防止错误数据的输入和输出造成错误结果和无效操作。（数据完整性）区分数据安全性：数据安全性是指非法用户的非法操作对数据库造成的恶意破坏。例如：为了维护数据的完整性，DBMS必须1.提供定义完整...
数据库表格查询
DWL718的博客
03-10 4489
1、数据库表示存储数据中所有数据的对象，在表中，数据按行和烈格式逻辑组织，类似于电子表格，数据库是SchoolMangeSystem，使用数据库表是SYS_Student，如下图： select字段列表，字段别名*from数据源，select SchoolManageSystem.dbo.SYS_Student,点击查询出这个表格 2、查询数据库中的学生ID，学院ID，年级ID 要从表中查询数据，使用select语句，select语句的最基本形式： Select studentID，acde
数据库知识整理 - 数据库完整性
春雪阁
11-23 5184
主要内容 实体完整性 小拓展：索引的应用 参照完整性 用户定义的完整性 1. 属性上的约束条件 2. 元组上的约束条件 完整性约束命名子句 域中的完整性约束条件 断言 触发器 1. 定义触发器 2. 触发器激活顺序   数据的完整性和安全性是两个既有联系又不尽相同的概念。数据的完整性是为了防止数据库中存在不符合语义的数据；数据的安全性是保护数据库免受恶意破坏和非法存取。 ...
数据库原理学习笔记（一）关系完整性以及数据库完整性
一个程序渣渣的小后院
06-03 6604
关系完整性是对关系的某种约束，当关系随着时间变化（增删改等操作改变数据库关系表）时应该满足一定的约束条件，通常这些约束条件都依赖于客观事实 关系完整性包含三个方面，分别是 实体完整性 参照完整性 用户自定义完整性 其中实体完整性和参照完整性是关系模型必须满足的约束条件，或者说是所有数据库管理系统都自动支持的约束条件。而用户自定义完整性则是根据不同的应用程序（不同的使用场景）有选择的设置的...
数据库分类统计、分组查询
邓学彬(泪闯天涯)的专栏
05-23 5074
SELECT 县区,SUM(第一孩_男),SUM(第一孩_女),SUM(第二孩_男),SUM(第二孩_女),SUM(三孩以上_男),SUM(三孩以上_女) FROM (    SELECT            CASE WHEN 孩次=1 AND 性别='男' THEN 1 ELSE 0 END AS 第一孩_男,              CASE WHEN 孩次=1 AND 性别
《MySQL入门很轻松》第5章：数据完整性及其分类
02-17 2317
数据表中的完整性约束可以理解一种规则或者要求，他规定了在数据表中哪些字段可以输入什么样的值。
数据库中的三种完整性：域、实体、参照完整性
热门推荐
u010398493的专栏
07-27 3万+
数据库中的三种完整性：域、实体、参照完整性 转载▼ 关系的完整性主要包括域完整性、实体完整性和参照完整性三种。 1．域（列）完整性        域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效
【MySQL精通之路】管理和实用程序
最新发布
Anakki的博客
05-19 191
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
Mac M1安装 nacos并自定义 Mysql 数据
GWQ_CY的博客
05-15 205
【代码】Mac M1安装 nacos并自定义 Mysql 数据。
postgresql数据库 error-1179650
04-11
它支持SQL语言，并提供了许多高级功能，如事务处理、并发控制和数据完整性保护。 关于你提到的"error-1179650"，很抱歉，我无法直接回答这个具体的错误代码，因为我没有相关的上下文信息。不过，通常情况下，当你在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

What大潘

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值