学习Python第15天——学习MySQL第2天

本文链接：https://blog.csdn.net/qq_44892630/article/details/107971286

仅记录个人学习Python所学，学识浅薄，若有错误欢迎指出。文章可能会不太完善，后续可能会继续更新。
MySQL这一部分有些表是已经创建过的代码片可能零零散散

文章目录

一、数据的完整性

1.实体完整性

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

实体约束
实体：表中的一行或者一条记录代表一个实体
实体完整性的作用：标识每一行数据不重复

约束类型：
主键约束【primary key】
唯一约束【unique】
自动增长列【auto_increment】
主键约束
特点：数据唯一，且不能为null

主关键字可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录

场景：在多个表的关联关系中

# 方式一：创建表的时候，在字段后直接添加primary key 约束 ，但是只能添加一个（一般一个表也就只有一个主键）
create table stu1(
	id int primary key,
	name varchar(50) 
);
# 方式二：primary key(字段1，字段2....),可以同时添加多个
create table stu2(
	id int ,
	name varchar(50),
	primary key(id，name)
);
# 方式三：通过alter进行添加 alter table table_name add constraint primary key(字段1，字段2)
create table stu3(
	id int,
	name varchar(50),
);
alter table stu3 add constraint stu3_id primary key(id);

唯一约束
作用：在非主键列中不能输入重复的值

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

primary key和unique之间的区别：
a.二者都强调的是唯一性
b.在同一个表中，一般只出现一个primary key，可以出现多个unique
c.primary key不允许为null，但是unique是允许的
自动增长
给主键添加添加自动增长性，列只能是整数类型
场景：一般添加给主键

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

2.域完整性

作用：限制单元格数据的正确性，

域代表当前单元格

约束类型：
数据类型
非空约束【not null】
默认值约束【default】

数据类型：
数字类型：int float double
日期类型：date datetime
字符串类型：varchar(20)
非空约束
注意：name被约束为not null，插入数据的时候，name坚决不能为null，如果为null，数据库立马报错

create table stu6( 
id int primary key auto_increment, 
name varchar(50) unique not null,
);

默认值约束

create table stu7(
id int primary key auto_increment,
name varchar(50) unique not null,
address varchar(50) default "beijing"
);
# 在插入数据时，如果不添加address会默认为'beijing'

外键约束
添加外键约束：foreign key
注意：添加外键必须先有主键，主键和外键的类型必须保持一致
举例：学生表，成绩表
作用：将两个甚至多个表产生联系

# 创建表
# 学生表
create table student(
stuid varchar(10) primary key,
stuname varchar(50)
);

# 成绩表
create table score(
stuid varchar(10),
score int,
courseid int
);

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

# 方式二
create table score2(
score int,
courseid int,
stuid varchar(10)
);

alter table score2 add constraint stu_sco_id2 foreign key(stuid) references student(stuid);

# 注意：主键和外键的类型必须保持一致
# 当修改有外键关联的数值时，在关联表中需要有该条数据才可以进行修改

二、多表查询

表与表之间的关系
一对一
通过嵌套的方式

一对多【多对一】
添加外键

多对多
单独创建一张新的表
合并结果集
作用：将两个select语句的查询结果合并到一起
两种方式：
union：去除重复记录【并集】
union all；获取所有的结果

# 创建表
create table A(
name varchar(10),
score int
);

create table B( 
name varchar(10), 
score int 
);

# 批量插入数据
insert into A values('a',10),('b',20),('c',30);
insert into B values('a',10),('d',40),('c',30);

# 合并结果集
select * from A
union
select * from B;
# 注意：被合并的两个结果，列数、列类型必须相同

列数不相同的情况，可以选定要合并查询的字段

insert into C values('a',10,29),('e',20,45),('c',30,10);

select * from A
union 
select name,score from C;

连接查询
作用：求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2

# 问题：进行连接查询，会产生笛卡尔积
# 笛卡尔积：两个集合相乘的结果
# 解释：假设集合A={a,b},集合B={0,1,2},则笛卡尔积的结果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
# 解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤

select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;

内连接 inner join on
内连接的特点：查询结果必须满足条件

上面的代码和下面的代码等价

select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;

外连接 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;
# 右外连接
# 参照为c
select s.stuid,s.stuname,c.score,c.courseid  from student s right join score c on s.stuid=c.stuid;

子查询

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

-- # 员工表
-- create table emp(empno int primary key, enname varchar(20), job varchar(20), mgr int, hiredate date, sal double, comm double, deptno int);
-- 
-- # 添加数据
-- insert into emp values(7369,'smith','clark',7902,'1980-12-17',800,null,20);
-- insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
-- insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
-- insert into emp values(7566,'jones','managen',7839,'1981-04-02',2975,null,30);
-- insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
-- insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
-- insert into emp values(7782,'clark','manageer',7839,'1980-06-17',2450,null,10);
-- insert into emp values(7788,'scott','analyst',7566,'1987-02-20',3000,null,20);
-- insert into emp values(7839,'king','president',null,'1987-02-20',5000,null,10);

select * from emp;


# 1.查询和scott在同一个部门的员工
select enname from emp where deptno=(select deptno from emp where enname='scott');


# 2.查询工资高于jones的员工信息
select enname from emp where sal>(select sal from emp where enname='jones');


# 3.查询工资高于30号部门所有人的员工信息
select enname from emp where sal>(select max(sal) from emp where deptno='30');


#4.查询工作类型和工资与martin完全相同的员工信息
select enname from emp where (job,sal) = (select job,sal from emp where enname='martin');
# 不包括martin
select enname from emp where enname!='martin' and (job,sal) = (select job,sal from emp where enname='martin');

三、数据库备份和恢复

备份
生成SQL脚本，导出数据

命令：mysqldump -u root -p 数据库名>生成sql脚本的路径

注意：可以不需要登录数据库

这里使用的是cmd窗口，如果不行可以使用管理员身份打开。
导出的sql文件，可以通过可视化工具 Navicat 右键单击数据库找到执行sql文件，可以快速执行sql文件，也可以通过下面的恢复方式。

恢复
执行sql脚本，恢复数据

前提：必须先创建数据库【空的】

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

mysql> create database test;

mysql> use test;
mysql> show tables;

mysql> source /home/rock/Desktop/mydb1.sql;