MySQL 基本用法(二)多表查询

骑猪看日落 2022-05-26 08:08 190阅读 0赞

# 数据库 #

## 1. 数据完整性 ##

作用：保证用户输入的数据保存到数据库中是正确的。  
确保数据的完整性 = 在创建表时给表中添加约束  
完整性的分类：

*  实体完整性
 *  域完整性
 *  引用完整性

### 1.1 实体完整性 ###

实体: 即表中的一行(一条记录)代表一个实体(entity)  
实体完整性的作用：标识每一行数据不重复  
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto\_increment)

#### 1.1.1 主键约束 ####

注：每个表中要有一个主键。  
特点：数据唯一，且不能为null

例子:  
第一种添加方式:

CREATE TABLE student(
    id int primary key,
    name varchar(50)
    );

第二种添加方式:

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

此种方式优势在于，可以创建联合主键

第三种方式:

CREATE TABLE student(
    id int,
    name varchar(50)
    );
    ALTER TABLE student  ADD  PRIMARY  KEY (id);

#### 1.1.2 唯一约束(unique): ####

特点: 数据不能重复

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) unique
    );

#### 1.1.3 ####

自动增长列(auto\_increment)

> 这种用法只限于mysql,其他数据库略有不同

CREATE TABLE student(
    Id int primary key auto_increment,
    Name varchar(50)
    );
    INSERT INTO student(name) values(‘tom’);

### 1.2 域完整性 ###

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

域代表当前单元格  
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)  
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

#### 1.2.1 非空约束: not null ####

CREATE TABLE student(
    Id int pirmary key,
    Name varchar(50) not null,
    Sex varchar(10)
    );
    
    INSERT INTO student values(1,’tom’,null);

#### 1.2.2 默认值约束 default ####

CREATE TABLE student(
    Id int pirmary key,
    Name varchar(50) not null,
    Sex varchar(10) default ‘男’
    );
    
    insert into student1 values(1,'tom','女');
    insert into student1 values(2,'jerry',default);

### 1.3 引用完整性 ###

外键约束(实际开发中基本上不会使用)

例:

CREATE TABLE student(
    sid int pirmary key,
    name varchar(50) not null,
    sex varchar(10) default ‘男’
    );
    
    create table score(
    	id int,
    	score int,
    	sid int , -- 外键列的数据类型一定要与主键的类型一致
    	CONSTRAINT fk_score_sid foreign key (sid) references student(id)
    );

第二种添加方式

ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

外键会保证score中的数据是student表中sid这一列已经存在的值

#### 表与表之间的关系 ####

*  一对一：例如t\_person表和t\_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
    
     *  在t\_card表中添加外键列（相对t\_user表），并且给外键添加唯一约束；
     *  给t\_card表的主键添加外键约束（相对t\_user表），即t\_card表的主键也是外键。
 *  一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t\_user和t\_section的关系，从t\_user来看就是一对多，而从t\_section的角度来看就是多对一！这种情况都是在多方创建外键！
 *  多对多：例如t\_stu和t\_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t\_stu\_tea表，给出两个外键，一个相对t\_stu表的外键，另一个相对t\_teacher表的外键。

![da6b1a6d3afcab1862d33a2a9ebbad06.png][]

## 2. 多表查询 ##

多表查询有如下几种:

*  合并结果集 UNION, UNION ALL
 *  链接查询
    
     *  内连接 \[INNER\] JOIN ON
     *  外连接 OUTER JOIN ON
        
         *  左外连接 LEFT \[OUTER\] JOIN
         *  右外连接 RIGHT \[OUTER\] JOIN
         *  全外连接（MySQL不支持）FULL JOIN
     *  自然连接 NATURAL JOIN
 *  子查询

### 2.1 合并结果集(很少很少使用) ###

1.  作用：合并结果集就是把两个select语句的查询结果合并到一起！
2.  合并结果集有两种方式：
    
     *  UNION：去除重复记录，例如：`SELECT * FROM t1 UNION SELECT * FROM t2；`
     *  UNION ALL：不去除重复记录，例如：`SELECT * FROM t1 UNION ALL SELECT * FROM t2。`

![add9c522ca38963a0c865fb6de0043a8.png][]

![e5b6a2deead68b3d2256efa8650959e5.png][]

1.  要求:被合并的两个结果：列数、列类型必须相同。

### 2.2 连接查询 ###

#### 2.2.0 交叉连接 ####

既然是多表查询，那么我们先来看看两张非常简单的表，我们就以这两张表为例，进行演示。  
student表

![f6b58d5ba15a6ca6cf9d74b4b42c4789.png][]

班级表

![e17d59e6a8d6c79adcb56a0125163277.png][]

同时查询这两张表

select * from student,class;

![5ac651d3be8d2c6baa593a3abb204076.png][]  
仔细观察查询出的数据，可以发现，当使用上图中的语句时，student表中的每一行记录，都与class表中的任意一条记录相关联，同样，class表中的每一行记录，都与student表中的任意一条记录相关联。即当我们有2张表时,交叉连接的效果如下:

![7df7145ca46171ded4758c042cbd94ce.png][]  
我们把上述"没有任何限制条件的连接方式"称之为"交叉连接"，"交叉连接"后得到的结果跟线性代数中的"笛卡尔乘积"一样。

> 笛卡尔积: 假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}。可以扩展到多个集合的情况

“交叉连接"的英文原文为"cross join”,被咱们翻译为交叉连接，其实，上述示例中的语句我们可以换一种写法，两种写法能够获取到相同的结果，示例如下

SELECT * FROM student CROSS JOIN class;

只不过在mysql中 我们可以简化为上述的写法

多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积

#### 2.2.1 内连接 inner join ####

既然"交叉连接"不常用，那么肯定有其他的常用的"多表查询方式"。我们来看看另一种常用的多表查询的方式：内连接  
扔用刚才的两张表student和class为例,首先回顾一下两张表的内容:

![f6b58d5ba15a6ca6cf9d74b4b42c4789.png][]

![e17d59e6a8d6c79adcb56a0125163277.png][]

那么什么是"内连接"呢？我们可以把"内连接"理解成"两张表中同时符合某种条件的数据记录的组合"，例如在查出的数据中同时展现学生信息和他们所在的编号,示例如下:

SELECT * FROM student, class where student.

![fef6f208faf3bacda03a231f89ba5684.png][]  
上述的sql语句就使用了"内连接",对比交叉连接发现,其实内连接就是交叉连接有更多的限制条件,内连接的英文原文为inner join,所以sql还可以写成

SELECT * FROM student INNER JOIN class ON student.sid = class.cid;

上述的是等值内连接,还可以有不等值内连接

SELECT * FROM student INNER JOIN class ON student.sid > class.cid;

![ac8f6143fd16d3f7fbd6687653311299.png][]

这样内连接可以用图来表示:

![5adf1dd33359b8a9fe91fbb9647eac00.png][]

使用"内连接"语句查询出的结果集是两个集合中"同时满足条件的数据"的"组合",所以我们并不能单纯的用"交集"去表示这个组合，就以上图为例，按照"交集"的定义，属于集合A且同时属于集合B的元素所组成的集合被称为交集，但是上图中，id号为1的元素只属于t1表，在t2表中并不存在id号为1的元素，但是，上图中"中间"的结果集就是"内连接"查询出的结果，所以，我们不能单纯的用"交集"表示"内连接"，但是，我们可以从另一个角度定义"交集"，我们定义，“交集"为"两个集合中同时满足条件的数据的组合”

##### 自连接 #####

“内连接"除了"等值连接"与"不等连接”，还有一种分类，被称作"自连接"，自连接可以理解为比较特殊的"内连接",刚才说到的"等值连接"与"不等连接"所连接的表为两张不同的表，而"自连接"连接的表为一张表，也就是自己连接自己，所以被称为"自连接"

我们再创建一张员工表,:

![e4d26fe07c4c7783a1e8301bea762167.png][]

如图 员工表有上级属性,上级指向的是员工的ID,如果我们想把每个人的上级都显示出来可以使用语句:

SELECT * FROM emp AS e1 INNER JOIN emp AS e2 ON e1.superior = e2.id;

![3b444db67f036a12e6b7db3296caf94d.png][]

这就属于自连接,在自连接的时候,由于表名是相同的,需要给表名起一个别名

#### 2.2.2 外连接 ####

外连接分为两种,左外连接,和右外连接,外连接的特点,结果集中有不满足情况的

##### 左外连接 #####

首先还是学生和班级表,不过我们学生表中插入了一个新学生,他还没有确定班级

![3e1e22fa06ce504a02932d85330987dc.png][]

使用左外连接,查询学生信息和它对应的班级

SELECT * FROM temp.student LEFT JOIN temp.class ON sid = cid;

![518d1947e7d94f98d472a8e749d01f9b.png][]

左外连接不仅会查询出两表中同时符合条件的记录的组合，同时还会将"left join"左侧的表中的不符合条件的记录同时展示出来，由于左侧表中的这一部分记录并不符合连接条件，所以这一部分记录使用"空记录"进行连接。即左表的数据会完全的加载出来,即使右表中没有符合条件的数据

![f6de4a94b12deefad8779badaa7c764d.png_pic_center][]

##### 右连接 #####

右连接与之类似

SELECT * FROM temp.class  RIGHT JOIN temp.student ON sid = cid;

![8eb06e54cdcdf71bab39ab6211763e63.png][]

![在这里插入图片描述][d35db0ac3764f690f51b6995389f0b3c.png_pic_center]

#### 2.2.3 子查询 ####

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

*  子查询出现的位置
    
     *  where后，作为条为被查询的一条件的一部分
     *  from后，作表
 *  当子查询出现在where后作为条件时，还可以使用如下关键字
    
     *  any
     *  all
 *  子查询结果集的形式
    
     *  单行单列（用于条件）
     *  单行多列（用于条件）
     *  多行单列（用于条件）
     *  多行多列（用于表）

##### 示例 #####

原始数据表

![在这里插入图片描述][3049c40ab100f0db8c9de3699d1b7ad4.png_pic_center]

工资高于JONES的员工

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')
    -- 一行一列

查询与SCOTT同一个部门的员工

SELECT *
    FROM lan_ou.emp
    WHERE deptno = (SELECT deptno
                    FROM lan_ou.emp
                    WHERE ename = 'SCOTT');

工资高于30号部门所有人的员工信息

SELECT * FROM emp WHERE sal>(
    SELECT MAX(sal) FROM emp WHERE deptno=30);

查询工作和工资与MARTIN完全相同的员工信息

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')

查询有2个以上直接下属的员工信息

SELECT *
    FROM emp
    WHERE empno IN (
      SELECT mgr
      FROM emp
      GROUP BY mgr
      HAVING COUNT(mgr) >= 2);

## 3. 常用函数 ##

### 1. 数学函数 ###

*  ABS(x) 返回x的绝对值
 *  BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
 *  CEILING(x) 返回大于x的最小整数值
 *  EXP(x) 返回值e（自然对数的底）的x次方
 *  FLOOR(x) 返回小于x的最大整数值
 *  GREATEST(x1,x2,…,xn)返回集合中最大的值
 *  LEAST(x1,x2,…,xn) 返回集合中最小的值
 *  LN(x) 返回x的自然对数
 *  LOG(x,y)返回x的以y为底的对数
 *  MOD(x,y) 返回x/y的模（余数）
 *  PI()返回pi的值（圆周率）
 *  RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
 *  ROUND(x,y)返回参数x的四舍五入的有y位小数的值
 *  SIGN(x) 返回代表数字x的符号的值
 *  SQRT(x) 返回一个数的平方根
 *  TRUNCATE(x,y) 返回数字x截短为y位小数的结果

### 2. 日期函数 ###

*  CURDATE()或CURRENT\_DATE() 返回当前的日期
 *  CURTIME()或CURRENT\_TIME() 返回当前的时间
 *  DATE\_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE\_ADD- (CURRENT\_DATE,INTERVAL 6 MONTH);
 *  DATE\_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
 *  DATE\_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE\_SUB- (CURRENT\_DATE,INTERVAL 6 MONTH);
 *  DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
 *  DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
 *  DAYOFYEAR(date) 返回date是一年的第几天(1~366)
 *  DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT\_DATE);
 *  FROM\_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
 *  HOUR(time) 返回time的小时值(0~23)
 *  MINUTE(time) 返回time的分钟值(0~59)
 *  MONTH(date) 返回date的月份值(1~12)
 *  MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT\_DATE);
 *  NOW() 返回当前的日期和时间
 *  QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT\_DATE);
 *  WEEK(date) 返回日期date为一年中第几周(0~53)
 *  YEAR(date) 返回日期date的年份(1000~9999)
 *  LAST\_DAY(date) 需要一个日期或日期时间值，并返回该月的最后一天对应的值。 如果该参数是无效的，则返回NULL。

### 3. 字符串类 ###

*  ASCII(char)返回字符的ASCII码值
 *  BIT\_LENGTH(str)返回字符串的比特长度
 *  CONCAT(s1,s2…,sn)将s1,s2…,sn连接成字符串
 *  CONCAT\_WS(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔
 *  INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
 *  FIND\_IN\_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
 *  LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
 *  LEFT(str,x)返回字符串str中最左边的x个字符
 *  LENGTH(s)返回字符串str中的字符数
 *  LTRIM(str) 从字符串str中切掉开头的空格
 *  POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
 *  QUOTE(str) 用反斜杠转义str中的单引号
 *  REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
 *  REVERSE(str) 返回颠倒字符串str的结果
 *  RIGHT(str,x) 返回字符串str中最右边的x个字符
 *  RTRIM(str) 返回字符串str尾部的空格
 *  STRCMP(s1,s2)比较字符串s1和s2
 *  TRIM(str)去除字符串首部和尾部的所有空格
 *  UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

## 4. 导出数据 ##

mysqldump –hlocalhost –uroot –p databaseName >d:\\dump.sql (window)

[da6b1a6d3afcab1862d33a2a9ebbad06.png]: /images/20220526/4ad3e5a1652e460ea89ce5abeea0d403.png
[add9c522ca38963a0c865fb6de0043a8.png]: /images/20220526/e0ccdcd8aa0f4b6fb77144f58a27d907.png
[e5b6a2deead68b3d2256efa8650959e5.png]: /images/20220526/fb0583f60a1d4d56bb46b356417004a5.png
[f6b58d5ba15a6ca6cf9d74b4b42c4789.png]: /images/20220526/9e783002b490470d9ef594c66e0e2137.png
[e17d59e6a8d6c79adcb56a0125163277.png]: /images/20220526/767e425eb8ae477780a4bc45a2fd76b8.png
[5ac651d3be8d2c6baa593a3abb204076.png]: /images/20220526/a76fed5848434e8fbd860c5fb714368f.png
[7df7145ca46171ded4758c042cbd94ce.png]: /images/20220526/66d536bf90b8474fb820b77fc392fbc3.png
[fef6f208faf3bacda03a231f89ba5684.png]: /images/20220526/594ce68c12a846c7bd93cc985232f69f.png
[ac8f6143fd16d3f7fbd6687653311299.png]: /images/20220526/97d68492c4da419daa157ab1ad5bb5a2.png
[5adf1dd33359b8a9fe91fbb9647eac00.png]: /images/20220526/3d053807418045679d311ad3b49f4fb6.png
[e4d26fe07c4c7783a1e8301bea762167.png]: /images/20220526/fb3d7f58be5a4b2e92f0873080ac4d66.png
[3b444db67f036a12e6b7db3296caf94d.png]: /images/20220526/1205faf5e0544bcda2e1cd9c5842af23.png
[3e1e22fa06ce504a02932d85330987dc.png]: /images/20220526/80664bdb36b4467ebde12e44ebe741a7.png
[518d1947e7d94f98d472a8e749d01f9b.png]: /images/20220526/ec5d9312b61648e8a294bbd6d8bc99b3.png
[f6de4a94b12deefad8779badaa7c764d.png_pic_center]: /images/20220526/01586830889f47debef8497c0991236a.png
[8eb06e54cdcdf71bab39ab6211763e63.png]: /images/20220526/f7fe09c141c54a44a0fe2f03fd252295.png
[d35db0ac3764f690f51b6995389f0b3c.png_pic_center]: /images/20220526/ffbfa0fcdd744037b97c7d4f14e78892.png
[3049c40ab100f0db8c9de3699d1b7ad4.png_pic_center]: /images/20220526/588e1e5e5f09460c816887e1c600b2c5.png