一、数据的完整性
作用：保证用户输入的数据保存到数据库中是正确的
实质：创建表的时候给表中的字段添加约束
1、实体完整性
实体：表中的一行或者一条记录代表一个实体
实体完整性的作用：标识每一行数据不重复
约束类型：
主键约束【primary key】
唯一约束【unique】
自动增长列【auto_increment】
1.1 主键约束【primary key】
特点：数据唯一，且不能为null
主关键字可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录
场景：在多个表的关联关系中
mysql> create table stu1(
-> id int primary key,
-> name varchar(50)
-> );
mysql> create table stu2(
-> id int ,
-> name varchar(50),
-> primary key(id，name)
-> );
mysql> create table stu3(
-> id int,
-> name varchar(50),
-> );
mysql> alter table stu3 add constraint stu3_id primary key(id);
1.2 唯一约束
作用：在非主键列中不能输入重复的值
mysql> 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.3 自动增长列
给主键添加添加自动增长性，列只能是整数类型
场景：一般添加给主键
mysql> create table stu5(
-> id int primary key auto_increment,
-> name varchar(50) unique
-> );
2、域完整性
作用：限制单元格数据的正确性，
域代表当前单元格
约束类型：
数据类型
非空约束【not null】
默认值约束【default】
2.1 数据类型
数字类型：int float double
日期类型：date datetime
字符串类型：varchar(20)
2.2 非空约束【not null】
mysql> create table stu6(
id int primary key auto_increment,
name varchar(50) unique not null,
);
#注意：name被约束为not null，插入数据的时候，name坚决不能为null，如果为null，数据库立马报错
2.3 默认值约束
mysql> create table stu7(
-> id int primary key auto_increment,
-> name varchar(50) unique not null,
-> address varchar(50) default "beijing"
-> );
mysql> insert into stu7 (id,name,address) values(1,'aaa','fff');
mysql> insert into stu7 (id,name,address) values(2,'bbb',default);
mysql> select * from stu7;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | aaa | fff |
| 2 | bbb | beijing |
+----+------+---------+
3、外键约束
添加外键约束：foreign key
注意：添加外键必须先有主键，主键和外键的类型必须保持一致
举例：学生表，成绩表
作用：将两个甚至多个表产生联系
#创建表
#学生表
mysql> create table student(
-> stuid varchar(10) primary key,
-> stuname varchar(50)
-> );
#成绩表
mysql> create table score(
-> stuid varchar(10),
-> score int,
-> courseid int
-> );
#插入数据
insert into student values('1001','zhangsan');
insert into student values('1002','xiaoming');
insert into student values('1003','jack');
insert into student values('1004','tom');
insert into score values('1001',98,1);
insert into score values('1002',95,1);
insert into score values('1003',67,2);
insert into score values('1004',83,2);
insert into score values('1004',70,1);
#查询
mysql> select * from student;
+-------+----------+
| stuid | stuname |
+-------+----------+
| 1001 | zhangsan |
| 1002 | lisi |
| 1003 | jack |
| 1004 | tom |
+-------+----------+
mysql> select * from score;
+-------+-------+----------+
| stuid | score | courseid |
+-------+-------+----------+
| 1001 | 98 | 1 |
| 1002 | 80 | 2 |
| 1003 | 70 | 1 |
| 1004 | 60 | 2 |
| 1002 | 75 | 3 |
+-------+-------+----------+
#方式一
mysql> create table score1( score int, courseid int,stuid varchar(10), constraint stu_sco_id foreign key(stuid) references student(stuid) );
#注意：stu_sco_id是给约束起的名字，可以自定义
#方式二
mysql> create table score2(
-> score int,
-> courseid int,
-> stuid varchar(10)
-> );
mysql> alter table score2 add constraint stu_sco_id2 foreign key(stuid) references student(stuid);
#注意：主键和外键的类型必须保持一致
二、多表联查
1、表与表之间的关系
一对一
通过嵌套的方式
一对多【多对一】
添加外键
多对多
单独创建一张新的表
2、合并结果集
作用：将两个select语句的查询结果合并到一起
两种方式：
union：去除重复记录【并集】
union all；获取所有的结果
#创建表
mysql> create table A(
name varchar(10),
score int
);
mysql> create table B(
name varchar(10),
score int
);
#批量插入数据
mysql> insert into A values('a',10),('b',20),('c',30);
mysql> insert into B values('a',10),('d',40),('c',30);
#查询结果
mysql> select * from A;
+------+-------+
| name | score |
+------+-------+
| a | 10 |
| b | 20 |
| c | 30 |
+------+-------+
mysql> select * from B;
+------+-------+
| name | score |
+------+-------+
| a | 10 |
| d | 40 |
| c | 30 |
+------+-------+
#合并结果集
mysql> select * from A
-> union
-> select * from B;
+------+-------+
| name | score |
+------+-------+
| a | 10 |
| b | 20 |
| c | 30 |
| d | 40 |
+------+-------+
mysql> select * from A
-> union all
-> select * from B;
+------+-------+
| name | score |
+------+-------+
| a | 10 |
| b | 20 |
| c | 30 |
| a | 10 |
| d | 40 |
| c | 30 |
+------+-------+
注意：被合并的两个结果，列数、列类型必须相同
如果遇到列数不相同的情况，如下的解决办法
mysql> insert into C values('a',10,29),('e',20,45),('c',30,10);
mysql> select * from A
-> union
-> select name,score from C;
+------+-------+
| name | score |
+------+-------+
| a | 10 |
| b | 20 |
| c | 30 |
| e | 20 |
+------+-------+
3、连接查询
作用：求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2
mysql> select * from student,score;
+-------+----------+-------+-------+----------+
| stuid | stuname | stuid | score | courseid |
+-------+----------+-------+-------+----------+
| 1001 | zhangsan | 1001 | 98 | 1 |
| 1002 | lisi | 1001 | 98 | 1 |
| 1003 | jack | 1001 | 98 | 1 |
| 1004 | tom | 1001 | 98 | 1 |
| 1001 | zhangsan | 1002 | 80 | 2 |
| 1002 | lisi | 1002 | 80 | 2 |
| 1003 | jack | 1002 | 80 | 2 |
| 1004 | tom | 1002 | 80 | 2 |
| 1001 | zhangsan | 1003 | 70 | 1 |
| 1002 | lisi | 1003 | 70 | 1 |
| 1003 | jack | 1003 | 70 | 1 |
| 1004 | tom | 1003 | 70 | 1 |
| 1001 | zhangsan | 1004 | 60 | 2 |
| 1002 | lisi | 1004 | 60 | 2 |
| 1003 | jack | 1004 | 60 | 2 |
| 1004 | tom | 1004 | 60 | 2 |
| 1001 | zhangsan | 1002 | 75 | 3 |
| 1002 | lisi | 1002 | 75 | 3 |
| 1003 | jack | 1002 | 75 | 3 |
| 1004 | tom | 1002 | 75 | 3 |
+-------+----------+-------+-------+----------+
20 rows in set (0.01 sec)
#问题：进行连接查询，会产生笛卡尔积
#笛卡尔积：两个集合相乘的结果
#解释：假设集合A={a,b},集合B={0,1,2},则笛卡尔积的结果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
#解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
3.1 内连接inner join on
内连接的特点：查询结果必须满足条件
#内连接
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#等价写法
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#练习：查询成绩大于80的学生记录
#方式一
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid and c.score>80;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#方式二
#也是内连接，只不过相当于是方言，join on相当于是普通话
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid and score>70;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#方式三
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid where score>70;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
3.2 外连接outer join on
特点：以其中一个表作为参照连接另外一个表
分类：
左外连接：left join on
右外连接：right join on
#左外连接
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#内连接
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
| 1002 | lisi | 75 | 3 |
+-------+----------+-------+----------+
#右外连接
#参照为c
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s right join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname | score | courseid |
+-------+----------+-------+----------+
| 1001 | zhangsan | 98 | 1 |
| 1002 | lisi | 80 | 2 |
| 1002 | lisi | 75 | 3 |
| 1003 | jack | 70 | 1 |
| 1004 | tom | 60 | 2 |
+-------+----------+-------+----------+
4、自查询
在一个select语句中包含另外一个完整的select语句【select语句的嵌套】
注意：
a.子查询出现的位置：
from后
where子句的后面，作为条件的一部分被查询
b。当子查询出现在where后面作为条件时，可以使用关键字：any、all
c.子查询结果集的形式
单行单列
单行多列
多行多列
多行单列
#1.查询和scott在同一个部门的员工
#思路：先查询scott所在的部门，然后根据部门查找所有的信息
mysql> select deptno from emp where enname='scott';
+--------+
| deptno |
+--------+
| 20 |
+--------+
mysql> select * from emp where deptno=(select deptno from emp where enname='scott');
+-------+--------+---------+------+------------+---------+------+--------+
| empno | enname | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+---------+------+------------+---------+------+--------+
| 7369 | smith | clark | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 |
+-------+--------+---------+------+------------+---------+------+--------+
#2.查询工资高于joens的员工信息
#思路：先查询jones的工资，然后根据jones查询其他的员工信息
mysql> select * from emp where sal>(select sal from emp where enname='jones');
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | enname | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1987-02-20 | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+------+--------+
#3.查询工资高于30号部门所有人的员工信息
#思路：先查询30号部门中的最高工资，根据最高工资查询其他的员工信息
mysql> select * from emp where deptno=30;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7566 | jones | managen | 7839 | 1981-04-02 | 2975.00 | NULL | 30 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
| 2975.00 |
+----------+
mysql> select * from emp where sal>(select max(sal) from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | enname | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1987-02-20 | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+------+--------+
#4.查询工作类型和工资与martin完全相同的员工信息
#思路：先查询martin的工作类型和工资，然后再查询其他的员工信息
mysql> select * from emp where (job,sal) in(select job,sal from emp where enname='martin');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
三、数据库的备份和恢复
1、备份
生成SQL脚本，导出数据
命令：mysqldump -u root -p 数据库名>生成sql脚本的路径
注意：可以不需要登录数据库
rock@rockrong:~$ mysqldump -u root -p mydb1 > /home/rock/Desktop/mydb1.sql
Enter password:
windows: 使用管理员权限打开CMD
2、恢复
执行sql脚本，恢复数据
前提：必须先创建数据库【空的】
注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本
rock@rockrong:~$ mysql -u root -p
Enter password:
mysql> create database test;
mysql> use test;
mysql> show tables;
mysql> source /home/rock/Desktop/mydb1.sql;
扫一扫
862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
