MySQL 两表联合count mysql两表联查

目录

• 一、数据的完整性、约束

• 1.1 实体完整性

• 1.1.1 主键约束【primary key】
• 1.1.2 唯一约束
• 1.1.3 自动增长列

• 1.2 域完整性

• 1.2.1 非空约束【not null】
• 1.2.2 默认值约束

• 1.3 外键约束

• 二、多表查询

• 2.1 合并结果集
• 2.2 连接查询

• 2.2.1 内连接：inner join on
• 2.2.2 外连接：outer join on

• 2.4 子查询

• 三、数据库的备份和恢复

• 3.1 备份：mysqldump
• 3.2 恢复：source

一、数据的完整性、约束

实体完整性、域完整性

• 作用：保证用户输入的数据保存到数据库中是正确的
• 实质：创建表的时候给表中的字段添加约束

1.1 实体完整性

• 实体：表中的一行或者一条记录代表一个实体
• 实体完整性的作用：标识每一行数据不重复
• 约束类型：
  主键约束【primary key】
  唯一约束【unique】
  自动增长列【auto_increment】

1.1.1 主键约束【primary key】

• 特点：数据唯一，且不能为null
• 主关键字可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录
• 场景：在多个表的关联关系中

方式一：
create table stu2(
id int primary key auto_increment,
name varchar(50),
);
方式二：同时创建多个主键
create table stu2(
id int,
name varchar(50),
primary key(id，name)
);
方式三：创建完表后单独添加主键
create table stu2(
id int,
name varchar(50)
);
alter table stu3 add constraint stu3_id primary key(id);

1.1.2 唯一约束

• 作用：在非主键列中不能输入重复的值

create table stu4(
id int primary key,
name varchar(50) unique
);

• primary key和unique之间的区别：
  a.二者都强调的是唯一性
  b.在同一个表中，一般只出现一个primary key，可以出现多个unique
  c.primary key不允许为null，但是unique是允许的

1.1.3 自动增长列

• 给主键添加自动增长性：auto_increment，列只能是整数类型。主键列的值会自动叠加。
• 场景：一般添加给主键

1.2 域完整性

• 作用：限制单元格数据的正确性，域代表当前单元格。
• 约束类型：
  a.非空约束【not null】
  b.默认值约束【default】

1.2.1 非空约束【not null】

• 注意：name被约束为not null，插入数据的时候，name坚决不能为null，如果为null，数据库立马报错。

create table stu2(
id int,
name varchar(50) not null
);

1.2.2 默认值约束

create table stu7(
id int primary key auto_increment,
name varchar(50) unique not null,
address varchar(50) default "beijing"
);

1.3 外键约束

• 添加外键约束：foreign key，将两个或多个表联系起来。
• 注意：添加外键必须先有主键，主键和外键的类型必须保持一致。

方式一：stu_sco_id是给约束起的名字，可以自定义
create table score1( score int, courseid int,stuid varchar(10), constraint stu_sco_id foreign key(stuid) references student(stuid) );

方式二：先创建表，再单独添加外键
alter table score2 add constraint stu_sco_id2 foreign key(stuid) references student(stuid);

二、多表查询

• 表与表之间的关系：一对一、一对多（添加外键）、多对多（合并新表）。

2.1 合并结果集

• 作用：将两个select语句的查询结果合并到一起.
• 方式：
  union：去除重复记录【并集】、union all：获取所有的结果

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

union：
select * from A union select * from B;

union all：
select * from A union all select * from B;

2.2 连接查询

• 笛卡尔积：求出多个表的乘积，如果直接采用连接查询，不添加过滤条件就会产生笛卡尔积。
• 解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤。

2.2.1 内连接：inner join on

• 内连接的特点：查询结果必须满足条件，inner可省略。

on可以用where替代：（给别名）
select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;

2.2.2 外连接：outer join on

• 特点：以其中一个表作为参照连接另外一个表。
• 分类：
  左外连接：left join on、右外连接：right join on

左外连接：
select s.stuid,s.stuname,c.score,c.courseid  from student s left join score c on s.stuid=c.stuid;
右外连接：
select s.stuid,s.stuname,c.score,c.courseid  from student s right join score c on s.stuid=c.stuid;

2.4 子查询

• 在一个select语句中包含另外一个完整的select语句【select语句的嵌套】
• 注意：
  a.子查询出现的位置：from后where子句的后面，作为条件的一部分被查询；
  b.当子查询出现在where后面作为条件时，可以使用关键字：any、all。
  c.子查询结果集的形式：单行单列、单行多列、多行多列、多行单列。

1.查询和scott在同一个部门的员工:
select * from emp where deptno=(select deptno from emp where enname='scott');
2.查询工作类型和工资与martin完全相同的员工信息:
select * from emp where (job,sal) in(select job,sal from emp where enname='martin');

子查询涉及返回值为多个时，返回的是一个元组。where后面的字段也要元组形式。

三、数据库的备份和恢复

3.1 备份：mysqldump

• 生成SQL脚本，导出数据
• 命令：mysqldump -u root -p 数据库名>生成sql脚本的路径/备份名.sql

注意：可以不需要登录数据库，备份路径要加上备份生成的文件名

mysqldump -u root -p mydb1 > /home/rock/Desktop/mydb1.sql

3.2 恢复：source

• 执行sql脚本，恢复数据
• 前提：必须先创建空数据库

注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本。

create database test;
use test;
source /home/rock/Desktop/mydb1.sql;