找工作所需数据库基础知识与实际操作（以MySQL为例）

最新推荐文章于 2023-11-20 08:57:20 发布

秋刀鱼程序编程

最新推荐文章于 2023-11-20 08:57:20 发布

阅读量528

点赞数

文章标签：数据库数据仓库大数据 mysql

本文链接：https://blog.csdn.net/weixin_51631044/article/details/130772652

版权

第一章、数据库原理概述

1.1.2 数据库、数据字典、数据库管理系统、数据库系统

1. 数据库（DB）---

（1）概念：按一定结构组织并长期存储在计算机内的、在逻辑上保持一致的、可共享的大量相关数据的集合---存储数据仓库

（2）属性：较小的冗余度、较高的数据独立性、易扩展性

2. 数据字典（DD）-----系统数据

数据库中的数据分为用户数据、系统数据

（1）概念：系统数据称为数据字典---还将其称为系统目录或数据

（2）属性：包括数据库的描述信息、数据库的储存管理信息、数据库的控制信息、用户管理信息、系统事务管理信息

3. 数据库管理系统（DBMS）----存放三级结构

(1)概念：数据库管理系统是位于用户与操作系统之间的一个定义、操作、管理、构建和维护数据库的系统软件

（2）属性:可对数据库执行查询、插入、删除、更新等基本操作

4. 数据库系统（DBS）

（1）概念：由用户数据库、操作系统、数据库管理系统、应用开发工具、应用系统、数据库管理人员、数据库用户构成

（2）

1.3 数据库管理技术的发展历程

---- 早期数据库系统、关系型数据库系统、对象-关系型数据库系统、大数据时代存储系统和NOSQL数据库 ----

1. 早期数据库系统---格式化数据库系统、

（1）主要类型：层次、网状模型的数据库系统，倒排文件系统

2. 关系型数据库----数学化模型

优点（1）采用人们习惯使用的表格作为基本的数据库结构，简单明了；

（2）一次查询仅使用一条命令或语句，即可以访问整个“关系（或二维表）”。通过多表联合操作，

对互有联系的若干二维表实现“关联查询”

3. 对象-关系型数据库系统

（1）概念：它是建立在关系型数据库的基础之上，将数据库技术与面向对象技术相结合，

可以直接继承关系型数据库系统原有的技术喝用户基础。

4. 大数据时代存储系统和NOSQL数据库

（1）概念：用于存储大型数据并且进行操作的新型数据库；

------------------------------------------------------------------

第二章、数据模型和数据库系统的模式结构

1.数据库的核心和基础----------------------------------数据模型

2.----------------------------------数据库系统的模式

3.概念模型--------------------------------------E-R图

4.逻辑数据模型----------------------------------层次模型、网状模型、关系模型、面向对象模型、对象-关系模型

5.本章重点：数据库系统的三级模式和两层映像体系，以及数据的逻辑独立性和物理独立性

2.1 数据模型和数据模型组成的要素

2.1.1 数据模型的概念

1. 数据模型需满足的三点要求

（1）能容易的模拟现实世界

（2）能让人轻松理解

（3）方便于在计算机上实现

2.1.2 数据模型组成的要素（数据结构、数据操作、数据约束）

1.数据结构-----数据模型的静态行为

（1）数据模型的基础：静态特性

2.数据操作---数据模型的动态行为

主要操作类型：检索和修改（插入（insert into 表名 values(值)）、删除(alter table 表名 drop)、更新(updat 表名 set 字段=值 where 条件)）

3.数据约束

数据约束：完整性约束、数据安全性约束、并发控制

2.2 数据模型的分类---（概念层模型、逻辑层模型、物理层模型）

1.概念层模型

最常用的概念层模型：实体-联系模型（E-R模型）

2.逻辑层模型

层次模型、网状模型、关系模型、面向对象模型、对象-关系数据模型

3.物理层模型

含义：数据库最底层的抽象

2.3 E-R数据模型

一、信息世界中的基本概念

1.实体

2.属性

(1)E-R属性类型：简单属性、复合属性、单值属性、多值属性、存储属性、派生属性

简单属性：不可再分属性（比如年龄、性别）

复合属性：可以被分为更小的子部分（可再分）

单值属性：对于特定的实体，大多数属性只有一个值（比如学生的年龄）

多值属性：同一个实体的某个属性可能具有多个值

存储属性：某个属性能够存储另外一个属性的信息

派生属性：某根据个属性能够推出另一个属性的信息（如一个人的年龄属性和出生日期属性，

其中年龄属性可以从出生日期中派生出，故出生日期为存储属性）

3.实体性

4.实体集

5.码或者键

6.域

含义：属性的取值范围

7.空值（NULL）

8.联系

二、E-R图---提供实体型、属性、联系

1、E-R图的表示方法

（1）实体性：用矩阵表示

（2）属性：用椭圆表示

（3）多值属性：双线椭圆表示

（4）派生属性：虚椭圆表示

（5）码属性的名称下面有下划线，并位于椭圆中

2.联系：用菱形表示（将其和实体联系起来）

2.4 常用的逻辑数学模型

逻辑数据模型主要包括：层次数据模型、网状数据模型、关系数据模型、面向对象数据模型、对象-关系数据模型

一、层次数据模型和网状数据模型

1. 层次模型（记录型）：反应现实世界中实体间的层次关系。

（1）数据：用记录的集合表示

（2）数据间的联系：用链接表示

（3）记录：用树型结构表示

树状结构节点---记录型

（4）层次模型的存储结构包括：邻接法、链接法、邻接-链接混合法

2. 网状数据模型（反应非层次关系、记录型）：

注：它允许多个节点没有父节点，一个结点可有多个父节点

二、关系模型（记录型）

1.表现形式：用二维表结构表示各类实体及实体间的联系（SQL语言就是一种关系型数据库）

三、面向对象数据模型（不是记录型）

1.内涵：将面向对象方法与数据库相结合的数据模型

四、对象-关系数据模型

1.内涵：将关系模型与面向对象模型进行结合，进而形成如今的对象关系模型；

2.常见的关系对象数据模型：Oracle、Sybase、DB2、Informix

2.5 数据库系统的模式结构

一、数据库系统的三级模式结构

三级模式包括：模式、外模式、内模式

1. 模式（概念模式\逻辑模式）---数据库的中心与关键

（1）内涵：模式实际上就是一个数据库数据的逻辑视图，且处于中间层；

（2）基础：数学模型

2. 外模式（子模式\用户模式）

（1）内涵：它是数据库用户能够看见和使用的局部的逻辑结构和特征的描述，是数据库用户的数据视图；

（2）一个数据库可以有多个外模式

3.内模式（物理模式\存储模式）

（1）一个数据库只有一个内模式；

（2）内涵：它是数据物理结构和存储方式的描述，是数据库内部的表示方法

二、数据库的两层映像与数据独立

注：为了能够在内部实现三级模式之间的联系和转换，数据库管理系统提供了两层映像：外模式/模式映像、模式/内模式映像；

1、外模式/模式映像

（1）内涵：定义定义外模式和模式之间的对用关系，对于每一个外模式，数据库系统都有一个外模式/模式映像；

（2）作用：保证了数据与程序的逻辑独立性；

2、模式/内模式映像

（1）内涵：定义数据库的全局逻辑结构与存储结构之间的对应关系；

（2）作用：保证了较高的物理独立性；

（3）模式/内模式映像在数据库中是唯一的；

---------------------------------------------------------------------------

第三章关系数据模型和关系数据库系统

关系数据模型的三要素：关系数据结构、关系操作集合、关系完整性约束；

关系模型的三类完整性约束：实体完整性约束、参照完整性约束、用户定义完整性约束；

关系型数据库，突出的优势：

（1）保持数据的一致性；

（2）数据的更新开销很小；

（3）可以进行JOIN等复杂的查询；

（4）可以存放很多实际成果和商业技术信息；

3.1.2 关系数据模型

（一）关系数据结构

1.表现形式：逻辑结构是一张二维表；

（二）关系操作集合

1. 常用的关系操作包括：选择、投影、连接、除、并、交、差

2. 关系能力的表达方式：代数方式、逻辑方式

3. 抽象的查询语言：关系代数、元组关系演算、域关系演算

4. 关系数据语言：

（1）关系代数语言：例如ISBL

（2）关系演算语言：元组关系演算语言（例如APLHA,QUEL）、域关系演算语言（例如OBE）

（3）具有代数关系和关系演算双重特点的语言：例如SQL

(三)关系的完整性约束

1.内涵：指数据库中数据的正确性、相容性、一致性

3.2 关系模型的数据结构

（一）笛卡尔积（关系数据库的定义）

1. 笛卡尔积：可以表示为一张二维表

2. 表示形式：R(D1,D2,D3,D4,...,)

3. 笛卡尔积计算：R*S={t|t=<tr,ts>^tr属于R^ts属于S}（笛卡尔积有k1*k2个元组）

（二）一元的专门关系操作

1. 选择

选择又称为限制，它是在关系R中选择满足给定条件的元组，记作：

2. 映射

-----------------------------------------------------------------------

第四章、关系数据库标准语言SQL

主要讲述内容：定义语言（DDL），包括基本表、索引、阈的定义和删除语句以及对基本表定义的修改语句;

数据操纵语言（DML）,数据查询语句的基本结构及其强大的检索能力，包括数据插入、删除、更新语句

控制语言（DCL）:包括权限授予和收回语句

4.1.2 SQL的语言特点

SQL是集数据查询（DQL）、数据操作（DML）、数据定义（DDL）、数据控制（DCL）于一体

1. 综合统一

2. 高度非过程化

3. 面向集合的操作方式

4. 灵活的使用方式

5. 语言简介，易学易用，功能强大

------ SOL语言的动词：---------

数据查询 SELECT

数据定义 CREATE,DROP,ALTER

数据操纵 INSERT,UPDATE,DELETE

数据控制 GRANT,REVOKE

4.1.3 SQL的数据类型

4.2.2 基本表的定义、删除和修改

1. 删除表---采用drop

2. 扩充和修改基本表 ---alter table

(1)增加表的列: alter table 表名 add 新列名

(2) 修改表的列：alter table 表名 change 旧列名新列名类型

(3) 删除表的列：alter table 表名 drop 列名

(4) 修改表数据（修改类型或者修改数据）：alter table 表名 modify 字段名1 类型

3. 删除基本表

drop table 表名 [约束]

4.2.3 索引的建立和删除

4.3 SQL的数据查询（SQL）---提供SELECT语句进行数据查询

---------------------------------------------------------------------------------------------------

# 一、数据库的基本操作DML

-- 1、创建表

USE mydbl;

CREATE TABLE  IF NOT EXISTS `Gou`(

`name` VARCHAR(20),

`years` INT,

`high` DOUBLE) ;

-- 2、修改表的列名（语法：alter table 表明 change 旧列名新列名类型）

ALTER TABLE Gou CHANGE `name3` `name` VARCHAR(20)

-- 3.插入数据（语法：insert into 表名(列表1，列表2，....) values(值1，值2,...,)）

INSERT INTO Gou(`name`,`years`,`high`) VALUES('Gou huipeng',26,170);

-- 4. 删除指定列（语法：alter table 表名 drop 列名）

ALTER TABLE gouhuipeng DROP id;

-- 5.增加列（指定位置，第一列）---运用非空的自增长约束

ALTER TABLE Gou ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- 6. 修改表名（rename table 旧表名 to 新表名）

RENAME TABLE Gou TO students;

-- 7.增加学生信息---插入数据

INSERT INTO students(`name`,`years`,`high`) VALUES('张三',20,175.9);

-- 8.修改表中数据信息（语法：update 表名 set 字段名1=值1，字段名2=值2,...,where 条件）---可以整体修改

UPDATE students SET `name`='李四' WHERE id=1;

DQL

定义SQL模式：

create schema <模式名> authorization <用户名>

数据查询（DQL）的使用

#创建数据库mydb2

create database if not exists mydb2

#使用数据库

use mydb2

#创建商品表product

create table `product`(

`pid` int primary key auto_increment,    #添加自增长约束

`pname` varchar(20) not null,            #添加非空约束

`price` double,                          #添加商品价格

`category_id` varchar(20)      #商品所属分类

);

#插入数据--给表product

insert into product values(null,'海尔冰箱',5000,'c001');

INSERT INTO product VALUES(NULL,'美的冰箱',3000,'c001');

INSERT INTO product VALUES(NULL,'格里空调',5000,'c001');

INSERT INTO product VALUES(NULL,'九阳电饭煲',5000,'c001');



INSERT INTO product VALUES(NULL,'啄木鸟衬衣',500,'c002');

INSERT INTO product VALUES(NULL,'恒源祥西裤',300,'c002');

INSERT INTO product VALUES(NULL,'花花公子夹克',500,'c002');

INSERT INTO product VALUES(NULL,'劲霸休闲裤',40,'c002');

INSERT INTO product VALUES(NULL,'海蓝之家卫衣',80,'c002');



INSERT INTO product VALUES(NULL,'兰蔻面霜',300,'c001');

INSERT INTO product VALUES(NULL,'雅思兰黛精华水',200,'c001');

INSERT INTO product VALUES(NULL,'香奈儿香水',350,'c001');

INSERT INTO product VALUES(NULL,'sk-11神仙水',180,'c001');

INSERT INTO product VALUES(NULL,'资深堂粉底液',200,'c001');



INSERT INTO product VALUES(NULL,'老北京方面面',60,'c001');

INSERT INTO product VALUES(NULL,'粮袋铺子海带丝',70,'c001');

INSERT INTO product VALUES(NULL,'三只松鼠坚果',35,'c001');

UPDATE product SET `pname`='海尔冰箱' WHERE pid=8;

#执行查询操作

select 语句后面可以是字段名，可以是字段和常数组成的算术表达方式，也可以是字符串常数

查询操作，类似于我们的寻找操作

--------常用的查询操作--------------------

1. 查询全表--select *from 表名

2. 指定查找---select 选择列表 from 表名

3. 条件查询--- select 字段1，字段2，....， from 表名 where 约束条件

4. 条件区间查询（between）--- select 字段1，字段2，....，from 表名 where 字段1 between 约束变量 and 约束变量

5. 前端部分字段搜索（like）--- 列名 [not] like 字符串常数

注：字符串常数中字符的含义如下：

字符_(下划线)表示可以和任意的单个字符进行匹配。如x_y表示以x开头，以y结尾长度为3的任意字符串；

字符%（百分号）表示可以和任意长度的字符串匹配。

6. in查询---用于查找属性值属于指定集合的元组

7. order by---排序查询

8. group by---分组查询---根据相同进行分组

语法：select 字段1，字段2，... from 表名 group by 分组字段 having 分组条件

注：对于select当中后面的字段只能为分组字段和聚合函数

注：having用于分组之后的条件选择

9. limit---分页查询

语法1：select 字段1，... from 表名 limit n ----显示前n条

语法2：select 字段1，... from 表名 limit n,m----显示从第n条开始显示后m条

10. insert into select 语句

简介：将一张表的数据导入到另一张表中，可以采用insert into select

语法1：insert into 新表名（field1,field2,...）select value1,value2,... from 旧表名；

语法2：insert into 新表名 select *from 旧表名；

-------------------------------------------------------------------------------

（二）使用聚集函数进行查询

SQL主要提供的聚集函数有：

count([distinct|all]*)------------统计元组个数

count([distinct|all]<列名>)-------统计一列中值的个数

sum([distinct|all]<列名>)---------计算一列值的总和（此列必须是数值型）

avg([distinct|all]<列名>)---------计算一列值的平均值（此列必须是数值型）

max([distinct|all]<列名>)---------求一列值中的最大值

min([distinct|all]<列名>)---------求一列值中的最小值

注：这里的约束条件关键词采用group by;having

--------------------------------------------------------------------------------

(三)连接查询

从多个表中选取数据，称之为连接查询。其中连接查询是数据库中最重要的部分，

包括等值连接、非等值连接、复合条件连接、自身连接和多表连接。

1. 等值连接和非等值连接

(1)当连接运算符为“=”时，称为等值连接；

(2)使用其它运算符时，称为非等值连接；

注：其中连接谓词中的列名称称为连接字段。连接条件中的各连接字段的数据类型必须是可比的。

（四）正则表达式（REGEXP--关键字）

格式：

模式描述

^ 匹配输入字符串的开始位置

$ 匹配输入字符串的结束位置

. 匹配除“\n”之外的任何单个字符

[...] 字符集合。匹配所包含的任意一个字符。例如，'[abc]'可以匹配“paln”中的'a'

[^...] 负值字符串集合。匹配未包含的任意字符。例如，‘[^abc]’可以匹配“paln”中的'p'

p1|p2|p3 匹配p1或p2或p3.例如，‘z|food’能匹配“z”或“food”

a* 匹配0个或多个a,包含空字符串

a+ 匹配1个或多个a,但是不包含空字符串

a? 匹配0个或1个a

a1|a2 匹配a1或a2

a{m} 匹配m个a

a{m,} 匹配至少m个a

a{m,n} 匹配m到n个a,包含m和n

注：正则表达可以组合使用

-- 1.查询指定表的全部信息（select * from 表名）

select * from product;

-- 2. 指定查找---指定条件查找

select  *from product where  pname='海尔冰箱' and price=5000;

-- 运算符操作----

select 6+2;

select 6-2;



-- 将所有的商品价格加10元---as 可以进行替换--整体选择--制定列加条件选择

select pname,price+10 as new_price from product;



-- 将商品的价格上调百分之10

select pname,price*1.1 as new_price from product;

-- 3. 使用比较运算符进行查询------有条件的筛选

select pname,price from product WHere price>=5000;



-- 选择价格不是800的所有商品--不等的表示“！= ” 、“<>”、“not 字段=数值”

select *from product WHERE price !=800;

SELECT *FROM product WHERE price <>800;

SELECT *FROM product WHERE not price=5000;

-- 4. 使用between进行区间选择---当然可以利用wehere进行条件筛选

select pname,price from product where price between 50 and 1000;

-- 5. 其中利用where进行逻辑筛选与between效果是一样的

SELECT pname,price FROM product WHERE price>=50 and price<=1000;

-- 6. 使用like进行筛选---这里的like类似与像什么类型，做一个前端搜索

#查询所有冰箱的信息--这里“%”可以匹配任意字符，“-”代表单个字符

select * from product where pname like '%冰箱';



#查询所有裤的相关信息

SELECT * FROM product WHERE pname LIKE '%裤';



#查询表中价格在500-6000的冰箱有那些??? 遇到多重条件怎么解决

select *from product where pname like '%冰箱' and (price >=500 and price<5000);

-- 7. 使用in进行指定集合的元组进行查找

#查找产品中的编号c002的产品名称和序号

select pid,pname from product where category_id ='c002';



#按照商品的价格进行排序--desc(降序)---不写时默认升序(order by)

select *from product order by price desc;  -- 降序

select *from product order by price;





#在价格排序的基础上，以分类进行排序--只能执行price的降序，不能执行标签的降序

SELECT *FROM product ORDER BY price DESC, category_id DESC;



# 显示商品的价格（去重），并排序（降序）----去掉重复的价格--去重（distinct）

select distinct price  from product order by price desc;

-- 8. 多重条件查询--使用 and or 关键词----根据逻辑选择进行选择

#寻找编号c001中价格在100以上的产品

select pid,pname from product where category_id='c001' and price>100;

-- 9.统计产品总数(count(*))---两种不同的方式起到相同的作用

select count(*) from product;

select count(pid) from product;



# 查询价格大于200的商品的总条数

select count(pid) from product where price>200;



# 求商品分类为‘c001’的总和

SELECT sum(price) FROM product WHERE category_id='c001';



#查询最贵的商品,当有最大的相同时全部显示

select pname,max(price) max_price from product where category_id='c001';

-- 10. group by---将查询结果按某一列或多列值分组，值相等的为一组

#查询每一个产品的编号和平均价格

select category_id,pname,avg(price) from product group by pname;



#统计各个分类商品的个数

select category_id, count(*) from product group by category_id;



#查询编号确定大于2的产品名

select pname from product group by pname having count(*)>=2;



#统计各个分类商品的个数，且只显示个数大于4的信息

SELECT category_id, COUNT(*) cnt FROM product GROUP BY category_id having  cnt>2 order by cnt;

-- 11.分页查询

#查询product的前5条记录

select *from product limit 5;



#从第四条开始显示，显示5条

select *from product limit 3,5;

-- 12.insert into select语句

#创建一个表

create table product2(

pname varchar(20),

price double

);

insert into product2(pname,price) select pname,price from product;

#查看表product2

select * from product2;



#将商品类型存入到product3

create table  product3(

category_id varchar(20),

count_product int

);



insert into product3 select category_id,count(*) cnt from product group by category_id order by cnt;

select *from product3;

-- 注：当表存在的时候，再进行存储只能选择叠加；

-- 正则表达式----------

-- ^ 在字符串开始处进行匹配

select 'abc' regexp '^a';

SELECT * from product where pname REGEXP '^海';

select *from product where pname like '海%';     #这两种起到的效果一样

#查看表格中是否有以水结尾的商品

select *from product where pname regexp '水$';

# [...] 匹配所包含的任意一个字符

select 'abc' regexp '[xyz]';

多表操作

目的：项目的实现需要利用多表才能进行操作

多表之间关系包括：一对一，一对多/多对一，多对多

一、一对多

（一）外键约束：经常与主键约束一起使用，相关联字段中主键所在表就是主表（父表），外键所在的表就是从表（子表）

1.创建方式：关键字 foreign key 关键字来指定外键，语法如下：

[constrain <外键名>] foreign key 字段名 [,字段名2，...] references <主表名> 主键列1 [,主键列2]

外键约束也可以在修改表的时候添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中

主键中的数据一致或者是没有数据

2.创建方式2： alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> （<列名>）

3. 删除表的外键约束

alter table <表名> drop foreign key <外键约束名>

二、多对多

多个外键约束

三、多表联合查询（从多张表进行查询）

（一）交叉连接查询【产生笛卡尔积，了解】

语法：select *from A,B;

（二）内连接查询（使用的关键字 inner join --inner 可以省略）---交集

隐式内连接（SQL92）：select *from A,B where 条件；

显示内连接（SQL99）: select *from A inner join B on 条件；

（三）外连接查询（使用的关键字 outer join -- outer 可以省略）

左外连接：left outer join

select *from A left outer join B on 条件;

右外连接：right outer join

select *from A right outer join B on 条件；

满外连接：full outer join

select *from A full outer join B on 条件；

（四）子查询

select 的嵌套

-- 特点

子查询可以返回的数据类型一共分为四种：

1. 单行单列

2. 单行多列

3. 多行单列

4. 多行多列

（五）表自关联

将一张表当成多张表用

（六）子查询关键字

1. ALL

2. any

3. some

4. in

5. exists

----------------------------------------------------

1. all关键字

-- ---------- -- 以方法1进行实现---------------

# 实现

CREATE DATABASE mydb3;

#创建部门表

USE mydb3;

CREATE TABLE IF NOT EXISTS dept(

`detpno` VARCHAR(20) PRIMARY KEY,  #部门编号

`name` VARCHAR(20)             #部门名字

);



INSERT INTO dept VALUES('c001','张三');

INSERT INTO dept VALUES('c002','李四');

INSERT INTO dept VALUES('c003','王五');

UPDATE dept SET detpno='项目部' WHERE `name`='张三';

UPDATE dept SET detpno='人事部' WHERE `name`='李四';

UPDATE dept SET detpno='后勤部' WHERE `name`='王五';



# 再进行修改

UPDATE dept SET detpno='1001' WHERE `name`='张三';

UPDATE dept SET detpno='1002' WHERE `name`='李四';

UPDATE dept SET detpno='1003' WHERE `name`='王五';

UPDATE dept SET `name`='研发部' WHERE  detpno='1001';

UPDATE dept SET  `name`='销售部' WHERE detpno='1002';

UPDATE dept SET `name`='财务部' WHERE detpno='1003';

INSERT INTO dept VALUES('1004','人事部');

INSERT INTO dept VALUES('1005','项目部');


#创建一个子表--员工表，并创建dept_id外键约束



CREATE TABLE IF NOT EXISTS emp(

`eid` VARCHAR(20) PRIMARY KEY,  # 员工编号

`ename` VARCHAR(20),

`age` INT,

`dept_id` VARCHAR(20),  -- 员工所属部门

CONSTRAINT emp_fk FOREIGN KEY(dept_id) REFERENCES dept(detpno)

);

# 插入数据---给所在从表

INSERT INTO emp  VALUES('1','张小三',23,'1001');

INSERT INTO emp  VALUES('2','乔峰',21,'1001');

INSERT INTO emp  VALUES('3','段誉',23,'1003');

INSERT INTO emp  VALUES('4','虚竹',23,'1002');

INSERT INTO emp VALUES('5','令狐冲',30,'1004');



# 注：从数据受到主表的约束



# 删表的数据---针对主表

DELETE FROM dept WHERE detpno='1004';   -- 不可以删除，因为从表当中有受1004主键约束的变量

DELETE FROM dept WHERE detpno='1005';   -- 可以删除

DELETE FROM dept;                       -- 不可以删除



# 删除从表

DELETE FROM emp WHERE eid='5'; -- 可以删除

-- 4.删除外键约束

ALTER TABLE emp DROP FOREIGN KEY emp_fk;

-- ---------------二多对多----------------------------------------

#创建学生表

CREATE TABLE IF NOT EXISTS students (

`sid` INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(20),

`age` INT,

`gender` VARCHAR(20)

);



#创建课程表

CREATE TABLE IF NOT EXISTS course(

cid INT PRIMARY KEY AUTO_INCREMENT,

cidname VARCHAR(20)

);



#创建中间表student_course/score

CREATE TABLE score(

sid INT,

cid INT,

score DOUBLE

);



#建立外键约束--2次

ALTER TABLE score ADD FOREIGN KEY(sid) REFERENCES students(sid);

ALTER TABLE score ADD FOREIGN KEY(cid) REFERENCES course(cid);



#给学生表students添加数据

INSERT INTO students VALUES(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'杨过',23,'男');



#给课程表course添加数据

INSERT INTO course VALUES(1,'语文'),(2,'数学'),(3,'英语');



#给中间表添加数据

INSERT INTO score VALUES(1,1,98),(1,2,93),(2,1,89),(2,3,91),(3,2,97),(3,3,90);



#修改和删除时，中间从表可以随便删除和修改，但是两边的右侧主表不能随便删除或修改

-- ------------------------------------------------------------------------------

-- -------多表联合查询(外键约束对多表查询并无影响)------------------------------

USE mydb3;

#创建一个部门表

CREATE TABLE IF NOT EXISTS dep13(

deplno VARCHAR(20) PRIMARY KEY,  -- 部门号

`name` VARCHAR(20)   -- 部门名称

);

-- 由于表名写错，对其进行修改(rename table 旧表名 to 新表明)

RENAME TABLE depl3 TO dept3;



#创建员工表

CREATE TABLE IF NOT EXISTS emp3(

eid VARCHAR(20) PRIMARY KEY, -- 员工编号

ename VARCHAR(20), -- 员工名字

age INT,           -- 员工年龄

dept_id VARCHAR(20)   -- 员工所属部门

);







#给dept3添加数据

INSERT INTO dept3 VALUES('1001','研发部');

INSERT INTO dept3 VALUES('1002','销售部');

INSERT INTO dept3 VALUES('1003','财务部');

INSERT INTO dept3 VALUES('1004','人事部');



#给emp3添加数据

INSERT INTO emp3 VALUES('1','乔峰',20,'1001');

INSERT INTO emp3 VALUES('2','段誉',21,'1001');

INSERT INTO emp3 VALUES('3','虚竹',23,'1001');

INSERT INTO emp3 VALUES('4','阿紫',18,'1001');

INSERT INTO emp3 VALUES('5','扫地僧',85,'1002');

INSERT INTO emp3 VALUES('6','李秋水',33,'1002');

INSERT INTO emp3 VALUES('7','鸠摩智',50,'1002');

INSERT INTO emp3 VALUES('8','天山童姥',60,'1003');

INSERT INTO emp3 VALUES('9','慕容博',58,'1003');

INSERT INTO emp3 VALUES('10','丁春秋',71,'1005');


# 交叉连接查询（select * from 表1，表2，表3,...,）---直接拼接

SELECT *FROM dept3,emp3;



# 内连接查询（select *from A,B where 条件；select *from A inner join B on 条件）

-- 查询每个部门的所属员工（一一对应）

# 隐式内连接

SELECT *FROM dept3 a,emp3 b WHERE a.deplno=b.dept_id;  -- 隐式内连接



# 显示内连接

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id;



-- 查询研发部门的所有员工

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id AND `name`='研发部';



-- 查询研发部门和销售部的所有员工

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id AND (`name`='研发部' OR `name`='销售部');

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id AND `name` IN('研发部' ,'销售部');  -- 对上一个的改写



-- 查询每一个部门的员工数，并升序排序(使用分组函数)

SELECT `name`,deplno, COUNT(1) FROM dept3 JOIN emp3 ON deplno=dept_id GROUP BY deplno,`name`;



-- 查询人数大于等于3的部门，并按照降序排序(合理运用各个命令的组合)

SELECT

        `name`,

        deplno,

        COUNT(1) AS total_cnt

FROM dept3

JOIN emp3 ON dept3.deplno=emp3.dept_id

GROUP BY

`name`,deplno

HAVING

total_cnt>=3

ORDER BY

total_cnt DESC;







-- 外连接查询

/*

左外连接：left outer join

   select *from A left outer join B on 条件;

*/

# 查询那些部门又员工，那些部门没员工

USE mydb3;

SELECT *FROM dept3 LEFT OUTER JOIN emp3 ON dept3.`deplno`=emp3.`dept_id`; -- 以左表为主，进行匹配



# 查询那些员工有对应部门，那些没有

SELECT *FROM dept3 RIGHT OUTER JOIN emp3 ON dept3.`deplno`=emp3.`dept_id`; -- 以右表为主，进行匹配



-- 实现满外连接：full join

-- 使用union关键字实现左外连接和右外连接的并集

-- SELECT *FROM dept3 RIGHT OUTER full JOIN emp3 ON dept3.`deplno`=emp3.`dept_id`; (不可以)

SELECT *FROM dept3 LEFT OUTER JOIN emp3 ON dept3.`deplno`=emp3.`dept_id`

UNION

SELECT *FROM dept3 RIGHT OUTER JOIN emp3 ON dept3.`deplno`=emp3.`dept_id`



-- 注：join是将左右查询的结果上下拼接



-- 子查询



# 查询年龄最大的员工信息，显示信息包含工号、员工名字、员工年龄

SELECT eid ,ename,age FROM emp3 WHERE age=(SELECT MAX(age) FROM emp3);  -- 也就是查询具有多个子查询（单行单列）



# 查询研发部和销售部的员工信息，包含员工号、员工名字

-- 方式1:多表查询

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id AND (`name`='研发部' OR `name`='销售部'); -- 多表查询

-- 方式2：子表查询(条件筛选查找)

SELECT *FROM

emp3

WHERE

dept_id

IN (

SELECT

deplno

FROM

dept3

WHERE

`name`='研发部' OR `name`='销售部');  -- 多行单列

# 查询研发部20岁以下的员工信息，包括员工工号、员工姓名、员工名字

-- 方式1：关联查询(因为两个部门之间有一定的关系)

SELECT *FROM dept3 JOIN emp3 ON deplno=dept_id AND (`name`='研发部' AND age<20);



-- 方式2：子查询（对于使用子查询，需要多考虑怎样进行步骤能够查到所需要的内容）

#2.1 在部门表中查询部门的信息

SELECT *FROM dept3 WHERE `name`='研发部';

#2.2 在员工表中查询员工信息

SELECT *FROM emp3 WHERE age<20;



SELECT *FROM (SELECT *FROM dept3 WHERE `name`='研发部') t1 JOIN (SELECT *FROM emp3 WHERE age<30) t2 ON t1.deplno=t2.dept_id;