[大数据]二、MySQL基础

打开phpstudy中的MySQL和Nginx，Win+R快捷键输入cmd呼出命令窗口，输入mysql -uroot -p指令，输入密码进入指令模式。

1、DDL

1.1 数据库操作

使用DDL语句可以创建、查询、修改、删除数据库。

1.1.1 查询数据库

show databases;#显示当前mysql的数据库列表

show create database <数据库名>;#显示指定名称数据库的创建的SQL指令

1.1.2 创建数据库

create database <数据库名>;#创建数据库并命名

create database if not exists <数据库名>;#若当前创建数据库不存在时创建 否则不创建

create database <数据库名> character set <目标字符集>;#在创建数据库的同时指定数据库的字符集
#字符集:数据存储在数据库中采用的编码格式 utf8/gbk支持中文格式

1.1.3 修改数据库:修改数据库的字符集

alter database <数据库名> character set <目标字符集>;#修改库的字符集

1.1.4 删除数据库:删除数据库中所有数据表以及数据表中的数据

drop database <数据库名>;#删除指定名称的数据库

drop database if exists <数据库名>;#若数据库存在则删除

1.2 数据表(关系)操作

数据表:即二维表格，由多行多列组成，表格中的每一列称为字段(属性)，每一行称为元组。
使用DDL语句可以创建、查询、删除、修改数据表。
在对表关系进行操作前，需要指明当前数据表操作所在的数据库。

use <数据库名>;#使用/切换数据库

1.2.1 创建数据表

create table <表名>(字段名1 类型1 约束1,字段名2 类型2 约束2,...字段名n 类型n 约束n);

1.2.2 查询数据表

show tables;#查看当前库中的数据表

desc <表名>;#查看指定表名的表结构

1.2.3 删除数据表

drop table <表名>;#删除指定表

drop table if exists <表名>;#数据表存在时删除该数据表

1.2.4 修改数据表

alter table <原表名> rename to <目标表名>;#将原表名重命名为目标表名

alter table <表名> character set <目标字符集>;#修改指定表的字符集

alter table <表名> add <字段名> <字段类型>;#在指定表的结构中添加字段

alter table <表名> modify <目标字段名> <目标类型>;#将目标字段名类型修改为目标类型

alter table <表名> change <原字段名> <目标字段名> <目标类型>;#修改字段名的名称和类型

alter table <表名> drop <目标字段名>;#删除目标字段

1.2.5 数据类型:数据表中列支持存放数据的类型

1.2.5.1 数值类型
MySQL有多种数据类型可存放数值，不同类型存放的范围或形式不同

1.2.5.2 字符类型:存储字符序列的类型

1.2.5.3 日期类型
MySQL数据库中可使用字符串类型来存储时间，但某些情况下需要基于时间段进行查询(如查询某个时间段内的数据)就不便于实现

1.2.6 字段约束

创建数据表时，指定对数据表的列的数据限制性要求。常见约束有非空约束(not null)、唯一约束(unique)、主键约束(primary key)、外键约束(foreign key)
1.2.6.1 非空约束:限制数据表中指定列的值不能为空
1.2.6.2 唯一约束:限制数据表中多条数据的值不能重复
1.2.6.3 主键约束:非空且唯一，主键可以是一列，也可是多个列的组合
▲主键自动增长(auto_increment):一些情况下，数据表中的主键是根据添加的数据自动增长的，就可以设置主键自动增长属性。当向数据表中新增记录时，主键根据上条记录(包括已被删除的记录)的主键自增。
1.2.6.4 联合主键:将数据表中的多列组合一起设置为表的主键

primary key(字段名1,字段名2...);#唯一主键约束也可采用这种表达方式
#联合主键不能直接在创建表时使用多个primary key的方式

1.2.6.5 外键约束

1.2.7 数据表相关操作示例

1.2.7.1 创建学生信息表

USE test;#使用test数据库
CREATE TABLE stu_info(
	id INT PRIMARY KEY,
	name VARCHAR(10) NOT NULL,
	sex VARCHAR(10) NOT NULL,
	age TINYINT NOT NULL,
	birthday DATE NOT NULL,
	telephone VARCHAR(15) UNIQUE,
	address VARCHAR(20)
);

ALTER TABLE stu_info RENAME TO student_information;#将表名更改为student_information
ALTER TABLE student_information ADD remark VARCHAR(100);#添加备注栏reamrk
ALTER TABLE student_information MODIFY age INT;#将age属性原tinyint型改为int型
ALTER TABLE student_information DROP remark;#删除备注栏reamrk

1.2.7.2 创建图书管理表

USE test;#使用test数据库
CREATE TABLE books_table(
	books_id INT PRIMARY KEY AUTO_INCREMENT,
	books_name VARCHAR(30) NOT NULL,
	books_author VARCHAR(25) NOT NULL,
	books_remark VARCHAR(100)
);

通过phpMyAdmin工具在表中插入示例中的数据

#插入数据语句
INSERT INTO `books_table` (`books_id`, `books_name`, `books_author`, `books_remark`) VALUES ('1', 'Java', 'Kathy Sierra', NULL), (NULL, 'Python', 'Mark Lutz', NULL), (NULL, 'C++', 'Stephen Prata', NULL);
#查询表结构
SELECT * FROM `books_table`;

删除最后一行数据，再在尾部增加另外一行数据

DELETE FROM `books_table` WHERE `books_table`.`books_id` = 3;
INSERT INTO `books_table` (`books_id`, `books_name`, `books_author`, `books_remark`) VALUES (NULL, 'C', 'Martin Richards', NULL);

2、DML

2.1 添加数据

insert into <表名> (字段名1,字段名2,...,字段名n) values (值1,值2,...,值n);
#添加的值与values前的字段名顺序一致 即形成一一对应关系

insert into <表名> values (值1,值2,...,值n);
#省略字段名则表示添加表中所有字段的数据 值的顺序与创建表的字段顺序一致

2.2 删除数据

delete from <表名> [where <条件>];

2.3 修改数据

update <表名> set <字段名1>=<值1>,<字段名2>=<值2>... [where <条件>];

2.4 DML示例:创建班级学生信息表

USE test;#使用test数据库
CREATE TABLE class_info(
	stu_id INT PRIMARY KEY AUTO_INCREMENT,
	stu_name VARCHAR(30) NOT NULL,
	stu_gender VARCHAR(10) NOT NULL,
	stu_nativeplace VARCHAR(20),
	stu_grade VARCHAR(5) NOT NULL
);

#添加表中数据
INSERT INTO class_info (stu_id,stu_name,stu_gender,stu_grade) VALUES (1001,'Mike','male','A');
INSERT INTO class_info (stu_name,stu_gender,stu_nativeplace,stu_grade) VALUES ('Tom','male','Shanghai','B');
INSERT INTO class_info (stu_name,stu_gender,stu_nativeplace,stu_grade) VALUES ('Jenny','female','Hongkong','A');
INSERT INTO class_info (stu_name,stu_gender,stu_grade) VALUES ('Mary','female','B');
INSERT INTO class_info (stu_name,stu_gender,stu_nativeplace,stu_grade) VALUES ('Jim','male','Beijing','A');

#删除stu_id为1005的数据元组
DELETE FROM class_info WHERE stu_id=1005;
#修改stu_id为1004的stu_nativeplace和stu_grade
UPDATE class_info SET stu_nativeplace='Chengdu',stu_grade='A' WHERE stu_id=1004;
SELECT * FROM class_info;#查询语句显示表信息

#将所有元组的stu_nativeplace改为Beijing
UPDATE class_info SET stu_nativeplace='Beijing';
SELECT * FROM class_info;#查询语句显示表信息

3、DQL

3.1 查询基础语法

select <字段名1,字段名2...> from <表名> [where <条件>];#在指定表中查询指定字段名

select * from <表名> [where <条件>];#在指定表中查询所有字段名

3.2 where子句:用于筛选满足特定条件的数据

[语句] where <条件>;

说明:在where子句中，若多条件查询，需要通过逻辑运算符(and or not)进行连接多个条件；若区间查询，可使用between a and b(等价于区间[a,b])语句进行查询。

3.3 like子句

在where子句的条件中，可使用like关键字实现模糊查询。

SELECT * FROM stus WHERE name LIKE '%o%';#在stus表中查询name含有o字母的数据

3.4 DQL示例及查询结果处理

在上例中班级学生信息表中添加stu_birth字段以存储学生的出生年份。

#新增字段
ALTER TABLE class_info ADD stu_birth INT;
#更新数据
UPDATE class_info SET stu_birth=2005 WHERE stu_id=1001;
UPDATE class_info SET stu_birth=2006 WHERE stu_id=1002;
UPDATE class_info SET stu_birth=2004 WHERE stu_id=1003;
UPDATE class_info SET stu_birth=2003 WHERE stu_id=1005;

#查询数据stu_birth计算年龄并取别名为stu_age
SELECT stu_name,2022-stu_birth AS stu_age FROM class_info;

#消除重复行 DISTINCT关键字
SELECT DISTINCT stu_nativeplace FROM class_info;

#模糊查询stu_name以y结尾的数据信息
SELECT * FROM class_info WHERE stu_name LIKE '%y';
#模糊查询stu_name中含e的数据信息
SELECT * FROM class_info WHERE stu_name LIKE '%e%';

3.5 order by排序

将查询到满足条件的记录按照指定的列的值升序或降序排列。

[语句] [where <条件> order by <字段名1> [asc],...,<字段名n> [asc]];#满足条件的元组按升序排列(默认)
[语句] [where <条件> order by <字段名1> desc,...,<字段名n> desc];#满足条件的元组按降序排列

3.6 聚合函数

SQL中提供了一些可以对查询的记录的列进行计算的函数。常见的聚合函数有:count、max、min、sum、avg等。

select <函数名>(字段名) from <表名> [where <条件>];

3.7 其它函数:日期函数、字符串函数

3.8 分组查询

分组:将数据表中的记录按照指定的列进行分组。

select [<字段名1>...<字段名n>],[<聚合函数>] from <表名> [where <条件>] group by <目标分组字段>;

说明:select后使用*显示对查询结果进行分组后每组的第一条记录。其后通常使用字段名和聚合函数对分组后的数据进行相关操作。
▲针对分组查询出的结果使用having关键字限制筛选

select [<字段名1>...<字段名n>],[<聚合函数>] from <表名> [where <条件>] group by <目标分组字段> having <条件>;

where条件和having条件的区别:先根据where条件从数据库查询记录后分组，分组后根据having条件对查询结果进行筛选。即where条件是针对原数据表进行筛选，having条件是针对分组后查询出数据表进行二次筛选，having关键字必与group by关键字同时出现。

3.9 分页查询

当数据表中的记录比较多的时候，若一次性全部查询出来显示给用户，用户的可读性/体验感不太完美，可将数据分页进行展示。

[语句] limit a,b;#a和b均为int类型 a为查询第一条数据索引(从0开始) b为获取查询记录条数 若剩余条数小于b则只显示剩余记录

页码 pageNum
总记录数 count
每页显示记录数 pageSize
总页数 pageCount=count%pageSize==0?count/pageSize:count/pageSize+1

[语句] limit (pageNum-1)*pageSize,pageSize;

4、多表联合

4.1 关联关系

MySQL是关系型数据库，不仅可存储数据，还可维护数据之间的关系。通过在数据表中添加字段建立外键约束。数据之间的关联关系分为四种:一对一关联、一对多关联、多对一关联、多对多关联。

4.1.1 一对一关联

生活中常常有一对一的关系实例，如人和身份证的关系，一个人只有一个身份证，一个身份证只对应一个人；某平台的用户和用户详情，一个用户只有一个详情，一个详情只对应一个用户。
▲建立一对一关联的方法
方法1:主键关联:两张表中分别设置主键，根据主键的性质，每张表中数据的主键属性唯一，表间形成了唯一对应唯一的关联。即两张表中主键相同的数据为相互关联的数据。

方法2:唯一外键:在任意一张表中添加一个外键约束字段与另一张表主键关联，并且外键约束字段添加唯一unique约束以达到一对一的关联。

4.1.2 一对多、多对一关联

生活中常常有一对多/多对一的关系实例，如班级与学生的关系，一个班级对应多个学生，相反，多个学生对应一个班级。
▲建立一对多/多对一关联的方法:在“多”的一端所对应的表中添加外键与“一”的一端所对应的表的主键进行关联，从而建立数据间一对多/多对一的关联。

4.1.3 多对多关联

生活中常常有多对多的关系实例，如学生与选课的关系，多个学生可以选择多门课，多门课也可由多名学生选择。
▲建立多对多关联的方法:另外创建一张关系表来维护多对多关联。在关系表中定义两个外键，分别与两个表的主键进行关联。

4.2 外键约束

外键约束是将一张表添加外键约束与另一张表的主键(唯一)进行关联后，这个外键约束的字段添加的数据必须在关联的主键字段中存在。即外键约束的字段的域是关联的主键字段的域的子集。
示例:学生与班级之间的关系表(学生表中添加外键与班级表的主键进行关联)

CREATE DATABASE db01;#创建数据库db01
USE db01;
#创建班级信息表
CREATE TABLE class_info(
	class_id INT PRIMARY KEY AUTO_INCREMENT,
	class_name VARCHAR(30) NOT NULL,
	class_remark VARCHAR(50)
);

#外键约束定义
constraint <外键关联逻辑名> foreign key(<当前表的外键>) references <被关联表名>(<被关联表名主键>);

#创建学生信息表
CREATE TABLE stu_info(
	stu_id INT PRIMARY KEY,
	stu_name VARCHAR(20) NOT NULL,
	stu_gender VARCHAR(10) NOT NULL,
	stu_age INT NOT NULL,
	cid INT,#外键与相关联的主键类型一致
	CONSTRAINT fk_stu_class FOREIGN KEY(cid) REFERENCES class_info(class_id)
);

#向班级信息表添加数据
INSERT INTO class_info (class_name) VALUES ('finance 1');
INSERT INTO class_info (class_name) VALUES ('finance 2');
INSERT INTO class_info (class_name) VALUES ('finance 3');

#向学生信息表添加数据
INSERT INTO stu_info (stu_id,stu_name,stu_gender,stu_age,cid) VALUES (1001,'zhangsan','male',20,2);
INSERT INTO stu_info (stu_id,stu_name,stu_gender,stu_age,cid) VALUES (1002,'lisi','female',21,3);
INSERT INTO stu_info (stu_id,stu_name,stu_gender,stu_age,cid) VALUES (1003,'wangwu','male',20,1);
INSERT INTO stu_info (stu_id,stu_name,stu_gender,stu_age,cid) VALUES (1004,'zhaoliu','male',19,1);
INSERT INTO stu_info (stu_id,stu_name,stu_gender,stu_age,cid) VALUES (1005,'chenqi','female',22,3);

4.3 连接查询

MySQL中可以使用join关键字实现多表的联合查询，即连接查询。按功能不同分为三个操作:内连接inner join、左连接left join、右连接right join。
内连接:两张表的笛卡尔积。

[语句] <表名1> inner join <表名2> [where <条件>];
#注意由于需要使用两张表来查询 <条件>需要指定表名 格式:表名.字段名
#where子句执行顺序是首先生成笛卡尔积 再进行筛选数据 效率较低

[语句] <表名1> inner join <表名2> [on <条件>] [where <条件>];
#on子句执行顺序是首先判断连接条件是否成立 若成立再生成笛卡尔积
#在连接查询中使用on子句在笛卡尔积中筛选数据 效率较高
#可使用where子句再进行二次筛选

左连接:展示左表中所有数据，若在右表中存在与左表记录满足连接条件的数据，则连接；否则展示为NULL。

[语句] <左表名> left join <右表名> on <条件> [where <条件>];

右连接:展示右表中所有数据，若在左表中存在与右表记录满足连接条件的数据，则连接；否则展示为NULL。

[语句] <左表名> right join <右表名> on <条件> [where <条件>];

5、练习

5.1 关系代数与SQL语句的转换

★视频:https://www.bilibili.com/video/BV1Au411R7oG?spm_id_from=333.999.0.0
有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列查询等价的SQL表达式?
(1)σ${}_{A=10}$(S)

SELECT * FROM S WHERE A=10;

(2)${\Pi }_{A,B}$(S)

SELECT A,B FROM S;

(3)$S?T_{A<E}$

SELECT * FROM S INNER JOIN T ON A<E;

(4)$S?T$

SELECT A,B,S.C,S.D,E,F FROM S INNER JOIN T ON S.C=T.C AND S.D=T.D;

(5)$S?T_{S.C=T.C}$

SELECT * FROM S INNER JOIN T ON S.C=T.C;

(6)${\Pi }_{C,D}$(S)×T

SELECT S.C,S.D,T.* FROM S INNER JOIN T;

(7)S?T

SELECT A,B,S.C,S.D,E,F FROM S LEFT JOIN T ON S.C=T.C AND S.D=T.D;

(8)S?T

SELECT A,B,T.C,T.D,E,F FROM S RIGHT JOIN T ON S.C=T.C AND S.D=T.D;

5.2 DQL示例

★视频:https://www.bilibili.com/video/BV16r4y167Jf?spm_id_from=333.999.0.0
建立一个学生系统表(Student_System)，存在字段id、name、gender、chinese_mark、math_mark、english_mark、last_logintime、tel。

CREATE TABLE student_system(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
    GENDER VARCHAR(10) NOT NULL,
    CHINESE_MARK INT,
    MATH_MARK INT,
    ENGLISH_MARK INT,
    LAST_LOGINTIME DATETIME,
    TEL VARCHAR(20)
);

INSERT INTO student_system(ID,NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK,TEL) VALUES ('Carl','male',87,92,89,'3587325');
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK,LAST_LOGINTIME) VALUES ('Barry','male',90,94,90,now());
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK,LAST_LOGINTIME) VALUES ('Alice','female',95,92,92,'2022-01-31 09:00:00');
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK,TEL) VALUES ('Jack','male',89,89,91,'4412578');
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK,TEL) VALUES ('Jim','male',92,90,91,'2569493');
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK) VALUES ('Anne','female',89,85,87);
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK) VALUES ('Paul','male',85,87,89);
INSERT INTO student_system (NAME,GENDER,CHINESE_MARK,MATH_MARK,ENGLISH_MARK) VALUES ('Tom','male',90,95,88);

SELECT ID,NAME,CHINESE_MARK FROM student_system ORDER BY CHINESE_MARK DESC;

SELECT * FROM student_system ORDER BY CHINESE_MARK DESC,MATH_MARK DESC,ENGLISH_MARK DESC;

SELECT CONCAT(NAME,'-',GENDER) FROM student_system;

SELECT SUBSTRING(TEL,2,4) FROM student_system;

SELECT CHINESE_MARK,COUNT(ID) FROM student_system GROUP BY CHINESE_MARK;

SELECT MATH_MARK,COUNT(ID) FROM student_system WHERE CHINESE_MARK>=90 GROUP BY MATH_MARK HAVING MATH_MARK>90;

SELECT * FROM student_system LIMIT 0,4;

5.3 多表联合查询

★视频:https://www.bilibili.com/video/BV1Qu41197ht?spm_id_from=333.999.0.0
设有一个SPJ数据库，包括S、P、J、SPJ共4个关系模式。
S(SNO,SNAME,STATUS,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
其中S是供应商表，由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)构成；P是零件表，由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)构成；J是工程项目表，由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)构成；SPJ是供应情况表，由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)构成。其中供应数量QTY表示某供应商供应某种零件给某工程项目的数量为QTY。

CREATE TABLE S(
	SNO VARCHAR(5),
	SNAME VARCHAR(100),
	STATUS INT,
	CITY VARCHAR(100)
);
CREATE TABLE P(
	PNO VARCHAR(5),
	PNAME VARCHAR(100),
	COLOR VARCHAR(100),
	WEIGHT INT
);
CREATE TABLE J(
	JNO VARCHAR(5),
	JNAME VARCHAR(100),
	CITY VARCHAR(100)
);
CREATE TABLE SPJ(
	SNO VARCHAR(5),
	PNO VARCHAR(5),
	JNO VARCHAR(5),
	QTY INT
);

(1)找出所有供应商的姓名和所在城市

SELECT SNAME,CITY FROM S;

(2)找出所有零件的名称、颜色、重量

SELECT PNAME,COLOR,WEIGHT FROM P;

(3)找出使用供应商S1所供应零件的工程号码

SELECT JNO FROM SPJ WHERE SNO='S1';

(4)找出工程项目J2使用的各种零件的名称及其数量

SELECT PNAME,QTY FROM SPJ INNER JOIN P ON SPJ.JNO='J2' AND SPJ.PNO=P.PNO;

(5)找出上海厂商供应的所有零件号码

SELECT DISTINCT PNO FROM S INNER JOIN SPJ ON S.CITY='ShangHai' AND S.SNO=SPJ.SNO;

(6)找出使用上海产的零件的工程名称

SELECT DISTINCT JNAME FROM S INNER JOIN SPJ INNER JOIN J ON S.CITY='ShangHai' AND S.SNO=SPJ.SNO AND SPJ.JNO=J.JNO;

(7)把全部红色零件的颜色改成蓝色

UPDATE P SET COLOR='blue' WHERE COLOR='red';

(8)由S5供给J4的零件P6改为由S3供应。

UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND PNO='P6' AND JNO='J4';

(9)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

DELETE FROM S WHERE SNO='S2';
DELETE FROM SPJ WHERE SNO='S2';

(10)请将(S2，J6，P4，200)插入供应情况关系。

INSERT INTO SPJ (SNO,JNO,PNO,QTY) VALUES ('S2','J6','P4',200);