[大数据]MySQL基础篇

MySQL基础篇

● 数据库概述

● MySQL数据库

● SQL–DDL

● SQL–DML

● SQL–DQL

● 多表设计_关联查询

数据库概述

为什么学习数据库：

? 实现数据持久化到本地；

? 使用完整的管理系统统一管理，可以实现结构化查询，方便管理

数据库（database）是为了方便数据的存储和管理，它将数据按照特定的规则存储在磁盘上，就是一个存储数据的仓库。通过数据库管理系统，可以有效的组织和管理存储在数据库中的数据。

● 数据库的相关概念

DB：数据库（DataBase） 存储数据的容器，它保存了一系列有组织的数据。

DBMS：数据库管理系统（DataBase Management System） 又称为数据库软件或数据库产品，用于创建或管理DB。

SQL：结构化查询语言（Structure Query Language） 用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的 主流数据 库软件通用的语言。

数据库根据不同的存储类型可以分为：

? 关系型数据库

? Oracle

? MySQL 快捷、可靠、开源、免费

? SQL Server(微软)：只能安装在Windows操作系统

? DB2 IBM 收费

非关系型数据库

? MongdoDB

? Redis

这里主要讲的是MySQL数据库

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。MySQL流行的是关系型数据库管理系统。

● MySql是一种关系数据库管理系统。

● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。

● MySql数据库服务器具有快速、可靠和易于使用的特点。

● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.

● mysql商业版与社区版

● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用

● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维

护，可以免费使用

查看MySQL数据库的版本

select version（）；（MySQL命令）

MySQL的常用命令

查看当前所有的数据库：show databases;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kuVVOJ1R-1630768711204)(C:\Users\辉欲静，而风止。\Videos\Captures\SQLyog Ultimate 64 - [新连接_school_db - root@localhost ] 2021_7_5 19_22_00.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EILEBjmU-1630768711207)(C:\Users\辉欲静，而风止。\Videos\Captures\SQLyog Ultimate 64 - [新连接_school_db - root@localhost ] 2021_7_5 19_22_45.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ebzS2OmM-1630768711209)(C:\Users\辉欲静，而风止。\Videos\Captures\SQLyog Ultimate 64 - [新连接_school_db - root@localhost ] 2021_7_5 19_23_02.png)]

数据库存储数据的特点

● 将数据放到表中，表再放到库中

● 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

● 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的

设计。

● 表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列

类似java中的”属性”

● 表中的数据是按行存储的，每一行类似于Java中的“对象”。

数据库表的基本概念

1、数据表

表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。

表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定

义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、记录

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完

整的记录。

3、字段

字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单

ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。

数据表的一列包含了特定字段的全部信息。

DDL

设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

表名（表信息）

表中的字段

字段的数据类型和长度

那些约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7zwPvGEa-1630768711211)(C:\Users\辉欲静，而风止。\Desktop\赵佳辉\QQ截图20210705193258.png)]

/*
多行注释
*/
#单行注释
-- 单行注释，--之后需要加空格
USE mysql;  #使用MySQL数据库
CREATE DATABASE school_db; #创建数据库
DROP DATABASE school_db; #删除数据库
SHOW DATABASES; #查看当前所有的数据库
SHOW TABLES;  #查看当前数据库的所有表
SHOW TABLES FROM sys;  #查看其他数据库的所有表SHOW TABLES FROM 库名;
/*
先创建一个基本无约束表，姓名，学号，性别，生日，身高，电话号码，登记时间

create table t_student(	
	name varchar(4),
	num INT,
	sex char(1),
	birthday date,
	height float(4,1),
	phone char(11),
	register_time datetime
	
)
drop table t_student;
*/

/*
创建一个约束表，姓名，学号，性别，生日，身高，电话号码，登记时间
PRIMARY KEY AUTO_INCREMENT(主键自增,选填)
CHECK检查约束
DEFAULT '男' 默认
UNIQUE唯一性约束  不能重复

create table t_student(	
	num INT primary key auto_increment,
	name varchar(4) not null,
	sex char(1) default '男' not null,
	birthday date not null,
	height float(4,1) check(height>140.0),
	phone char(11) not null unique,
	register_time datetime not null
	
)
drop table t_student;
*/
CREATE TABLE t_student(	
	NAME VARCHAR(4),
	num INT,
	sex CHAR(1),
	birthday DATE,
	height FLOAT(4,1),
	phone CHAR(11),
	register_time DATETIME
	
)

-- 删除表  DROP TABLE  表名
DROP TABLE t_student;

-- 修改表名  RENAME TABLE 原表名 To 新表名
RENAME TABLE t_student TO student;

-- 复制表 例如对某张表进行操作时，备份数据 CREATE TABLE 新表名 LIKE 被复制的表名;
CREATE TABLE stu LIKE student;

-- 添加，删除主键约束  ALTER TABLE 表名 ADD PRIMARY KEY(需要添加主键的列名);  ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY(num);
ALTER TABLE student DROP PRIMARY KEY; #删除主键 因为主键有且仅有一个，所以不用写列名

-- 设置主键自动增长,删除主键自增
ALTER TABLE student MODIFY num INT AUTO_INCREMENT; #设置主键自增 ALTER TABLE 表名 MODIFY 列名 INT AUTO_INCREMENT; 
ALTER TABLE student MODIFY num INT; #删除主键自增

-- 设置列不能为空
ALTER TABLE student MODIFY NAME VARCHAR(4) NOT NULL;#不能为空
ALTER TABLE student MODIFY NAME VARCHAR(4) NULL;#可以为空

-- 添加唯一约束ALTER TABLE student ADD CONSTRAINT 约束名 UNIQUE(列名);，
ALTER TABLE student ADD CONSTRAINT uni_phone UNIQUE(phone);
ALTER TABLE student DROP INDEX uni_phone;#删除唯一约束ALTER TABLE student DROP INDEX 约束名;

-- 添加检查约束 ALTER TABLE 表名 ADD CONSTRAINT height CHECK(约束条件);
ALTER TABLE student ADD CONSTRAINT height CHECK(height>156.0);
#删除检查约束ALTER TABLE 表名 DROP CHECK 要添加检查约束的列名一般加check_列名，便于确认;
ALTER TABLE student DROP CHECK height;
-- 修改表结构 添加列，删除列，修改列的名称，类型
#添加列
ALTER TABLE student ADD weight FLOAT(5,2);
#给某个列之后添加 ALTER TABLE student ADD 列名 类型 AFTER 某个列;
ALTER TABLE student ADD age INT AFTER NAME;
#修改列名
ALTER TABLE student CHANGE phone moblie CHAR(11);
#修改列名类型
ALTER TABLE student MODIFY moblie VARCHAR(11);


SELECT register_time FROM student WHERE DATE_FORMAT(register_time,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')

DML

数据操纵语言DML（Data Manipulation Language）

常用语句： insert,delete,update

-- 数据操纵语言DML
-- 插入数据insert  INSERT INTO student(除主键之外的其他要添加的列名)，删除数据delete，修改数据update   
INSERT INTO student(NAME,age,sex,birthday,height,moblie)
		VALUES('小张',18,'女','2000-1-1',165.0,'18391706273');
INSERT INTO student SET NAME='小赵',sex='男',birthday='2000-1-2';
-- 添加多组数据
INSERT INTO student(NAME,age,sex,birthday,height)
		VALUES('小张一号',18,'女','2000-1-5',165.0),
		('小张二号',18,'女','2000-1-3',165.0);
CREATE TABLE stu LIKE student;#复制表结构
-- 复制表数据
INSERT INTO stu(NAME,age,sex,birthday,height,moblie,register_time,weight)
	SELECT NAME,age,sex,birthday,height,moblie,register_time,weight FROM student;
-- 修改语句       修改时需要注意条件
UPDATE stu SET NAME='宝' WHERE num=1;
-- 删除语句  DELETE FROM 表名 WHERE 主键位置（主键唯一）;
DELETE FROM stu WHERE num=2;
	

DQL-基础查询

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，

可以从一个表中查询数据，也可以从多个表中查询数据。

/*
查询语句DQL是使用频率最高的语句
查询表数据
select * from 表名（将表中所有列查询出来）
SELECT 要查询的列 FROM 表名;
SELECT 要查询的列 FROM 表名 WHERE 条件（主键）;
*/
SELECT * FROM stu;
SELECT NAME,sex FROM stu;
SELECT NAME,moblie FROM stu WHERE num=1;
/*
查询结果处理
使用函数对查询的结果进行处理。
函数分类：
	单行函数：对查询的每行数据进行操作，查询五行，处理五行
	分组函数（聚合函数）：多行转一行。
*/
-- 字符函数
-- SELECT LENGTH(列名) FROM 表名; 一个中文三个字节
SELECT num,LENGTH(NAME),sex FROM stu;
-- CHAR_LENGTH(列名) 字符个数
SELECT num,CHAR_LENGTH(NAME),sex FROM stu;
-- concat(str1,str2,...)连接多个字符串    as 定义别名
SELECT CONCAT(NAME,'是',sex,'的')AS NAME FROM stu;
SELECT CONCAT(NAME,'是',sex,'的')NAME FROM stu;
-- upper（列）转大写  lower（列）转小写
SELECT UPPER(NAME) FROM stu;
SELECT LOWER(NAME) FROM stu;
-- SUBSTRING(列名,开始位置1,截取长度2) FROM 表名
SELECT SUBSTRING(NAME,1,2) FROM stu;
-- instr(str,指定字符） 类似于Java中的indexof（）
SELECT INSTR('abcde','e')AS shu FROM stu;
-- trim(列） 删除列的前后空格  
SELECT num,CHAR_LENGTH(TRIM(NAME)) FROM stu;
-- trim(子串，from 列）去掉列前后的子串
SELECT num,TRIM('z' FROM NAME) FROM stu;
-- LPAD(列名,最大长度,要填充的子串)  左填充
SELECT num,LPAD(NAME,5,'a') FROM stu;
-- RPAD(列名,最大长度,要填充的子串)  右填充
SELECT num,RPAD(NAME,5,'a') FROM stu;
-- replace(列名或字符串,旧子串,新子串)：替换，替换所有的子串
SELECT REPLACE(NAME,'小','大')NAME FROM stu;


-- case when 条件 then 结果一 else 结果二 end;  可以有多个when
SELECT 
	num,
	NAME,
	(CASE WHEN age>=18 THEN '成年' ELSE '未成年' END)age
FROM stu;
-- IFNULL (height,'暂未登记') 如果为空，显示默认值暂未登记，如果不为空，显示不为空的结果
SELECT IFNULL (height,'暂未登记')height FROM stu;
-- IF (条件,满足条件返回,不满足返回)
SELECT IF (height>160,'高个','低个')height FROM stu;
-- 数学函数
SELECT ROUND(3.25);#四舍五入
SELECT CEIL(3.01);#向上取整
SELECT FLOOR(3.996);#向下取整
SELECT TRUNCATE(3.99633,2);#保留两位小数，截取
SELECT MOD(11,3);#取余
SELECT RAND();#返回0到1之间的随机数

-- 日期函数
SELECT NOW();#获取当前系统时间
SELECT CURDATE();#获取当前日期
SELECT CURTIME();#获取当前时间  时分秒
SELECT YEAR(NOW());#获取当前年份
SELECT MONTH(NOW());#获取当前月份
SELECT DAY(NOW());#获取当前天数
SELECT STR_TO_DATE('2021-9-8','%Y-%m-%d');#字符串转date类型
SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'),birthday)FROM stu;#返回两个日期相差的天数


-- 分组函数
/*
功能：用做统计使用，又称为聚合函数或统计函数或组函数
分类：sum求和、AVG平均值、max最大值、min最小值、count计数（非空）
*/
-- sum（列 数值） 统计所有
SELECT SUM(height) FROM stu;
-- 分组统计
SELECT SUM(height),sex FROM stu GROUP BY sex;
-- 平均值
SELECT AVG(height) FROM stu;
-- 最大值
SELECT MAX(height) FROM stu;
-- 最小值
SELECT MIN(height) FROM stu;
-- 统计数量 (*/主键/指定列）
SELECT COUNT(*) FROM stu;
SELECT COUNT(num) FROM stu;
SELECT COUNT(height) FROM stu;

/*
条件查询
比较  = 在where子句中表示是否相等 != <> ,> <  >=  <=
*/
SELECT*FROM stu WHERE sex='男';
SELECT*FROM stu WHERE sex!='男';
-- 逻辑 and or not
-- and连接多个条件，多个条件必须同时满足
SELECT * FROM stu WHERE sex!='男'AND height>170 AND age>17;
-- or连接多个条件时，只需要满足一个条件成立即可
SELECT * FROM stu WHERE sex!='男'AND height>170 OR age>17;
-- in(1,2,3),满足1or2or3   not in（1,2,3）不满足1and2and3
SELECT*FROM stu WHERE height IN(165,175,190);
SELECT*FROM stu WHERE height NOT IN(165,175,190);
SELECT * FROM stu WHERE moblie IS NULL;#为空
SELECT * FROM stu WHERE moblie IS NOT NULL;#不为空

-- 模糊查询
-- (子串)%向后匹配多个字符  (子串)_向后匹配一个字符，也可以以子串结尾向前匹配
SELECT * FROM stu WHERE NAME LIKE 'z%';
SELECT * FROM stu WHERE NAME LIKE 'z__';
SELECT * FROM stu WHERE NAME LIKE '%h';
SELECT * FROM stu WHERE NAME LIKE '%z%';

-- 字段（列名） BETWEEN 170 AND 185 两者之间包含两端边界值    相当于>=and<=
SELECT * FROM stu WHERE height BETWEEN 170 AND 185;
-- union将多条结果合并，会去掉重复的行
SELECT num,NAME,age FROM stu WHERE sex='男'
UNION
SELECT num,NAME,age FROM stu WHERE height>170;
-- union all将多条结果合并，不会去掉重复的行
SELECT num,NAME,age FROM stu WHERE sex='男'
UNION ALL
SELECT num,NAME,age FROM stu WHERE height>170;

-- 升序ASC 降序desc  不写默认升序
SELECT * FROM stu ORDER BY num 
SELECT * FROM stu ORDER BY num DESC

-- 有条件 排序在条件的后面，对筛选后的结果进行排序
SELECT * FROM stu WHERE num>1 ORDER BY num
SELECT * FROM stu WHERE num>1 ORDER BY num DESC

-- 多个字段进行排序时，当第一个相同时，使用第二个字段进行排序
SELECT * FROM stu WHERE num>1 ORDER BY height ASC,num ASC;

-- 数量限制  分页问题
-- limit 开始位置，查询的数量
SELECT * FROM stu LIMIT 0,2
-- 查询男生身高最高的
SELECT * FROM stu WHERE sex='男' ORDER BY height DESC LIMIT 0,1

-- 分组统计 count（）
-- 按性别、年级、班级、部门分组
-- GROUP BY 列名 输入哪个列就按照哪个列的值进行分组   把内容相同的数据分到一个组进行统计
-- 查询男女生各自的人数 COUNT(*)分组统计  分别统计男女生组
SELECT sex,COUNT(*) FROM stu GROUP BY sex
SELECT sex,SUM(height) FROM stu GROUP BY sex
SELECT sex,AVG(height) FROM stu GROUP BY sex

-- 查询男女生中人数最多的是哪一个
SELECT
  sex,
  COUNT(*)
FROM
  stu
WHERE height > 164 -- 对原始表数据条件筛选
GROUP BY sex  -- 按某个条件进行分组
HAVING COUNT(*) > 2  -- 对分组后的数据进行条件筛选
ORDER BY COUNT(*) DESC  -- 对所有处理完的数据进行排序


/*
子查询：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。
分类：
	按子查询出现的位置：
	   select后面：仅仅支持量子查询
	   from后面：支持表子查询
	   where或having后面：支持标量子查询，列子查询，行子查询
	 按功能、结果集的行列数不同：
	    标子量查询（结果集只有一行一列）
	    列子查询（结果集只有一列多行）
	    行子查询（结果集有一行多列）（用的较少）
	    表子查询（结果集一般为多行多列）
*/
-- 子查询：出现在其他语句中的select语句
-- 在修改语句中使用了子查询，注意：子查询不能是当前正在操作的表
UPDATE stu SET NAME='小张小张' WHERE num= (SELECT num FROM student WHERE height=170)

-- 重点 在查询语句中使用子查询
-- 在一条SQL中如果出现两个以上的表名时，可以为表名定义别名
-- 在select后写子查询用外面的查询结果作为条件
SELECT
  ts.num,
  (SELECT
    t.name
  FROM
    stu t
  WHERE t.name = ts.name)NAME
FROM
  stu ts
  
  
-- where后面的子查询，标量子查询
SELECT * FROM stu WHERE height=(SELECT MAX(height) FROM stu)

-- 列子查询  一列多行
SELECT * FROM stu WHERE height IN(SELECT height FROM stu WHERE height>170)

-- 行子查询  一行多列
SELECT * FROM stu WHERE (height,age)=(SELECT MAX(height),MAX(age) FROM stu)

-- 表子查询：from后面使用表子查询  返回多行多列
-- 查询男女生总人数大于一的
SELECT * FROM(SELECT sex,COUNT(*)c FROM stu GROUP BY sex)t WHERE t.c>1

多表设计—关联查询

关联查询

/*
外键：引用另外一个 数据表的某条记录
数据表之间的关联/引用关系是依靠具体的主键和外键建立


*/
-- 创建年级表
CREATE TABLE grade(
		id INT PRIMARY KEY AUTO_INCREMENT,
		grades VARCHAR(5)
	)
-- 弱关联-人为定义的关系  没有实际约束  删除关联表数据没有任何限制
ALTER TABLE stu ADD gradeid INT AFTER moblie

-- 强关联 -添加外键约束 外键关联另外一个表的主键               与grade表中的ID关联
ALTER TABLE stu ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)

-- 删除外键
ALTER TABLE stu DROP FOREIGN KEY fk_gradeid

/*
表与表之间的关联关系
	一对多
	多对一
	一对一（外键不重复）
	多对多
*/

-- 创建课程表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(5)
)

-- 学生选课表 （学生课程关系表）
CREATE TABLE stu_course(
		student_num INT,
		course_id INT,
		CONSTRAINT fk_studentnum FOREIGN KEY(student_num) REFERENCES stu(num),
		CONSTRAINT fk_courseid FOREIGN KEY(course_id) REFERENCES course(id)
		)


/*
关联查询
	学号，姓名，性别，年级名称

*/
-- 笛卡尔乘积
SELECT * FROM stu,grade--
SELECT
  s.num,
  s.name,
  s.sex,
  g.grades
FROM
  stu s,
  grade g
WHERE s.gradeid = g.id
-- 内关联 在关联时添加条件，把满足条件的数据关联起来         等值连接
SELECT
  s.num,
  s.name,
  s.sex,
  g.grades
FROM
  stu s
  INNER JOIN grade g
    ON s.gradeid = g.id
-- 非等值连接
ALTER TABLE stu ADD score INT AFTER moblie

CREATE TABLE score_level(
		lev_name VARCHAR(5),
		lower_score INT,
		upper_score INT
	)


SELECT
  s.name,
  s1.lev_name,
  s1.upper_score
FROM
  stu s
  INNER JOIN score_level s1
    ON s.score BETWEEN s1.lower_score
    AND s1.upper_score
    
-- 左外关联
-- 无论 关联条件是否成立，都会将左边表的数据全部查询出来
SELECT
  s.num,
  s.name,
  s.sex,
  g.grades
FROM
  stu s
  LEFT JOIN grade g
   ON s.gradeid = g.id

-- 右外关联
SELECT COUNT(gradeid),g.id,g.grades FROM stu s RIGHT JOIN grade g ON s.gradeid = g.id GROUP BY g.id,g.grades