数据完整性及多表查询

最新推荐文章于 2022-07-15 21:10:11 发布

我可能是个假开发

最新推荐文章于 2022-07-15 21:10:11 发布

阅读量798

点赞数

分类专栏： My SQL

本文链接：https://blog.csdn.net/CSDN_GIA/article/details/53856240

版权

My SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

数据完整性

1、数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误

2、分为三类

实体完整性

域完整性

参照完整性

1.实体完整性

规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现

主键：唯一的去区分每一条记录的一列或者多列的值

主键的特点：不能为null，必须有值，且不能重复。

主键分类：

逻辑主键：不代表实际意义，只是区分不同记录用的。比如id

业务主键：代表着具体的实际意义。比如身份证号用户名

CREATE TABLE t2(
	id int PRIMARY KEY,#PRIMARY KEY 声明id是主键
	name varchar(100)
);

CREATE TABLE t4(
	id int,
	name varchar(100),
PRIMARY KEY(id)
);


CREATE TABLE t3(
	id int PRIMARY KEY auto_increment,#auto_increment 数据库自动增长
	name varchar(100)
);

2.域完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

非空约束：not null

唯一约束：unique

CREATE TABLE t6(
	id int PRIMARY KEY auto_increment,
	username varchar(100) not null unique, 非空和唯一约束
	gender varchar(10) not null  非空约束
);

约束：

#创建一个学生表
create table stu
(
	id int primary key,   #主键约束
	name varchar(30) unique,  #唯一约束
	sex char(2) not null,  #非空约束
	age int check (age > 0 and age < 100),   #检查约束 
	address varchar(50) default '北京'  #默认约束
) ;

#引用约束

主键所在的表叫主表

外键所在的表叫子表

添加的时候要先添加主表当中的记录，再添加子表当中的记录

删除的时候要先删除子表中的记录再删除主表中的记录

注意：

1. 添加记录时必须先添加主表中的记录，再添加子表中的记录

2. 不能更改主表中具有外键约束的记录的主键
3. 删除记录的时候不允许删除具有外键关系的主表中的记录(删除的顺序应当是先删除子表中的记录，然后删除主表中的记录)

3.参照完整性（多表）

在表内创建主外键约束：

create table score
(
	id int primary key,
	sid int ,
	china int,
	english int ,
	history int,
	constraint sid_FK foreign key(sid) references stu(id)
) ;

在表外创建主外键约束：

#创建引用约束
alter table score add constraint stu_score_FK foreign key(sid) references stu(id) ; 
#删除约束
alter table score drop foreign key stu_score_FK ;

表间的关系：

一对多（用的最多）

多对多（用的很多）

一对一（几乎不用）

3.1一对多：部门和员工的关系

3.2多对多：老师和学员

3.3一对一（了解）

学生表
1 张无忌
2 张三丰
课程表
1 语文
2 数学
学生课程表
sid cid
1 1
1 2
2 1
2 2

一对多：部门和员工的关系

CREATE TABLE department(
	id int primary key,
	name varchar(100)
);

CREATE TABLE employee(
	id int primary key,
	name varchar(100),
	salary float(8,2),
	department_id int,
	CONSTRAINT department_id_fk FOREIGN KEY(department_id) REFERENCES department(id)
);

多对多：老师和学员

CREATE TABLE teacher(
	id int primary key,
	name varchar(100),
	salary float(8,2)
);
CREATE TABLE student(
	id int primary key,
	name varchar(100),
	grade varchar(10)
);
CREATE TABLE teacher_student(
	t_id int,
	s_id int,
	CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
	CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id),
	PRIMARY KEY(t_id,s_id)
);

一对一(了解)

按照外键关联：

CREATE TABLE person(
	id int primary key,
	name varchar(100)
);
CREATE TABLE idcard(
	id int primary key,
	number varchar(20),
	person_id int unique,
	CONSTRAINT person_id_fk FOREIGN KEY(person_id) REFERENCES person(id)
);

按照主键关联：

CREATE TABLE person(
	id int primary key,
	name varchar(100)
);
CREATE TABLE idcard(
	id int primary key,
	number varchar(20),
	CONSTRAINT person_id_fk FOREIGN KEY(id) REFERENCES person(id)
);

多表查询

1.连接查询（面试几率很大）

基本语法形式：FROM 表1连接类型表2 [on连接条件][where筛选条件]

约定：表1在连接类型的左边，称之为左表

表2在连接类型的右边，称之为右表

•交叉连接（cross join）:不带on子句，返回连接表中所有数据行的笛卡儿积。
l 交叉连接：cross join
返回左表和右表的笛卡尔积（左表5条记录，右表6条记录 5*6=30条）
select * from customer,orders;
select * from customer cross join orders;

l交叉连接查询CUSTOMER表和ORDERS表
lSELECT* FROM customer CROSS JOIN orders;
lSELECT* FROM customer,orders;

•内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。
l 内连接：inner join
返回满足连接条件的所有记录。
隐式内连接：(不使用inner join关键字)
select c.*,o.* from customer c,orders o where c.id=o.customer_id;
显式内连接：(使用inner join关键字)
select * from customer c inner join orders o on c.id=o.customer_id;
l显式内连接：使用innerjoin关键字，在on子句中设定连接条件
SELECT * FROM customer c INNER JOINorders o ON c.id=o.customer_id;
l隐式内连接：不包含innerjoin关键字和on关键字，在where子句中设定连接条件
SELECT * FROM customer c,orders oWHERE c.id=o.customer_id;

•外连接：分为左外连接（left out join）、右外连接（rightouterjoin）。

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数
据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。
l 外连接：outer join
左外连接:left outer join=left join
返回满足连接条件的所有记录，同时返回左表中剩余的其他记录
查询所有客户，有订单的把订单也显示出来
select * from customer c left outer join orders o onc.id=o.customer_id;
右外连接:right outer join=right join
返回满足连接条件的所有记录，同时返回右表中剩余的其他记录
查询所有订单，同时打印订单所属的客户
select * from customer c right outer join orders o onc.id=o.customer_id;
左外连接：
l使用leftouter join关键字，在on子句中设定连接条件
SELECT * FROM customer c LEFT OUTERJOIN orders o ON c.id=o.customer_id;
l不仅包含符合c.id=o.customer_id连接条件的数据行，还包含customer左表中的其他数据行
l带查询条件的左外连接查询，在where子句中设定查询条件
SELECT * FROM customer c LEFT OUTERJOIN orders o ON c.id=o.customer_id WHERE o.price>250;

右外连接：
l使用rightouter join关键字，在on子句中设定连接条件
SELECT * FROM customer c RIGHTOUTER JOIN orders o ON c.id=o.customer_id;
l不仅包含符合c.id=o.customer_id连接条件的数据行，还包含orders右表中的其他数据行
l带查询条件的右外连接查询，在where子句中设定查询条件
SELECT * FROM customer c RIGHTOUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;

2.子查询

子查询也叫嵌套查询，是指在select子句或者where子句中又嵌入select查询语句

查询“陈冠希”的所有订单信息

select id fromcustomer where name=’陈冠希’;

select * fromorders where customer_id=1;

子查询：

select * from orderswhere customer_id=(select id from customer where name=’陈冠希’);

l子查询也叫嵌套查询，是指在select子句或者where子句中又嵌入select查询语句

l查询“陈冠希”的所有订单信息

SELECT * FROM orders WHEREcustomer_id=(SELECT id FROM customer WHERE name LIKE '%陈冠希%');

3.联合查询

l联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用union关键字

lSELECT* FROM orders WHERE price>200 UNION SELECT * FROM orders WHEREcustomer_id=1;

union关键字。

联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果

SELECT * FROM orders WHERE price>200UNION SELECT * FROM orders WHERE customer_id=1;

4.报表查询（使用数据库提供的函数）

l报表查询对数据行进行分组统计，其语法格式为：

l[select…] from … [where…] [ group by … [having… ]] [ order by … ]

l其中groupby 子句指定按照哪些字段分组，having子句设定分组查询条件。在报表查询中可以使用SQL函数。

#多表查询
#交叉查询
#查询每个人的考试成绩
select * from stu s cross join score c on s.id = c.sid ;  #交叉 比较了45次 （条件是主外键相等）

#查询参加考试的人的成绩
select name,china,english,history,china+english+history 总分 from stu s inner join score c on  s.id = c.sid ;

#查询所有人的成绩
select name,china,english,history,china+english+history 总分 from stu s left out join score c on  s.id = c.sid ;

#查询没有参加考试的人
select * from stu where id not in(select sid from score) ;
#查询参加考试的人的成绩
select name,china,english,history,china+english+history 总分 from stu s,score c where s.id = c.sid ; 

#聚合函数
#sum max,min avg ,count

#分组函数
 select count(*) 数量,sex,name from stu group by sex,name ; #根据多个字段进行分组

#分组条件
 select count(*),sex from stu where age >=16 group by sex having count(*) >1 ;

统计一个班级共有多少学生？
msyql>SELECT COUNT(*) FROM student;
统计数学成绩大于90的学生有多少个？
mysql>SELECT COUNT(*) FROM student WHERE math>90;
统计总分大于250的人数有多少？
mysql>SELECT COUNT(*) FROM student WHERE (chinese+english+math)>250;

统计一个班级数学总成绩？
mysql>SELECT SUM(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
mysql>SELECT SUM(chinese),SUM(english),SUM(math) FROM student;
统计一个班级语文、英语、数学的成绩总和
mysql>SELECT SUM(chinese+english+math) FROM student;
统计一个班级语文成绩平均分
mysql>SELECT SUM(chinese)/COUNT(*) FROM student;

求一个班级数学平均分？
mysql>SELECT AVG(math) FROM student;
求一个班级总分平均分
mysql>SELECT AVG(chinese+english+math) FROM student;

求班级语文最高分和数学最低分
mysql>SELECT MAX(chinese) FROM student;
mysql>SELECT MIN(math) FROM student;

对订单表中商品归类后，显示每一类商品的总价
mysql>SELECT product,SUM(price) FROM orders GROUP BY product;
查询购买了几类商品，并且每类总价大于100的商品
mysql>SELECT product,SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;

MySQL数据库的备份与恢复

1.备份

lmysqldump-u root -psorry test>test.sql

2.恢复

•创建数据库并选择该数据库

•SOURCE 数据库文件

•或者：

•mysql -u root -psorrytest<test.sql

2、前提：必须先创建数据库的名称

方式一：进入MySQL

方式二：不用进入MySQL

我可能是个假开发

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据完整性及多表查询

数据完整性1、数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误2、分为三类实体完整性域完整性参照完整性1.实体完整性规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现主键：唯一的去区分每一条记录的一列或者多列的值主键的特点：不能为null，必须有值，且不能重复。主键分类：逻辑主键：不代表实际
复制链接

扫一扫