MySQL学习
1.mysql数据库管理系统(轻量级数据存储)
2.海量数据的存储解决方案–HDFS（Linux平台）
mysql学习
- 一、数据库管理系统
- 二、MySQL数据库管理系统功能
- 三、SQL语言
一、数据库管理系统
1.什么是数据库
数据库 database（DB），按照数据结构存储数据的仓库
数据结构：底层文件的组织方式
2.什么是数据库管理系统
数据库管理系统(DBMS):操作和管理数据库的一套软件
3.数据库管理系统分类
(1).关系型的数据管理系统
利用关系模型（二位表格的形式(表)，行称为记录，列称为字段）存储和管理数据，数据量少，数据关系比较简单，并发量低（io瓶颈，底层文件形式存储）
例：
Oracle数据库：Oracle（甲骨文）大型的分布式的数据库管理系统
Mysql数据库：开源免费的中小型的数据库管理系统
sql server:microsoft,中小型数据库
(2).非关系型数据库管理系统（nosql:not only sql）
高并发和海量数据–弥补
例：redis（键值数据库/内存）/Hbse/MongoDB/Neo4J
二、MySQL数据库管理系统功能
1.MySQL数据库管理系统中
(1).可以管理多个不同的数据库（隔离业务）
(2).一个数据库存储多张表（table）
(3).支持千万级别数据
2：下载安装
C:\Program Files\MySQL\MySQL Server 8.0 mysql数据库管理系统软件的安装目录（默认）
C:\ProgramData\MySQL\MySQL Server 8.0 mysql数据库存储的位置和配置存储的位置
my.ini:配置文件，可以自己修改
Data:数据存储目录(下面每一个目录代表一个数据库/底下的文件代表数据库中存在的表)
3.如何发送命令
(1).命令行
连接（登录）数据库：mysql - uroot -p(密码：安装时设置的密码)
简单命令：
展示所有的数据库：show databases;(加分号表示结束)
选择数据库：use mydb;
展示表：show tables;
(2).图形界面软件
navicat/sqlyog…
三、SQL语言
sql:结构化查询语言，关系型数据库管理系统的通用语言，dbmy利用sql管理db
1.语言功能分类
DDL:数据定义语言（数据库对象结构 – 数据库，表等）
creat（创建）/alter（修改）/drop（销毁）
DML:数据操作语言（数据）
insert（插入）/update（修改）/delete（删除）
DQL:数据查询语言（数据）
select
TCL:事务控制语言
commit（提交事务）/rollback（回顾事务）
DCL:数据控制语言（权限）
grant/revoke
2.数据定义语言(DDL)
2.1 创建数据库
create database if not exists 1909da;
2.2 销毁数据库
drop DATABASE if exists 1909da;
2.3 表的操作
表：利用关系模型存储数据的数据结构
行 --记录
列 --字段
(1).创建表(声明字段)
create table tname
（
colname(字段名称) coltype(数据类型) 约束，
colname coltype 约束，
...
）
#创建一张学生表
create table student
(
sid int ,
sname varchar (20),
age int
);
2.4 数据类型（内存分配)
数据类型：为了合理分配内存空间
单位：字节Byte
bit(比特) 0/1
1Byte = 1024bit
数值类型
整数：
tinyint – 1
smallint – 2
mediumint --3
int – 4（有10位数）
bigint – 8
浮点数：
float(m,n) --4 m代表总长度，n代表小数点位数
double(m,n) --8
存储金额：(专用数据类型decimal,不会造成精度丢失)
decimal(m,n)
字符类型
char(n):固定长度字符串 --超过长度会报错，不足n长度会用0补齐，将长度补足n
varchar(n):可变长度字符串 --超过长度会报错，不足n长度不会补齐，长度为保存值的长度
日期类型
data：年月日
time：时分秒
datatime：年月日时分秒 (8个字节) 0000-9999
timestamp:时间戳（年月日时分秒）(4个字节) 1970 --2038
其他类型
blob：二进制数据
text：大文本
json/enum/set
(2).表结构的修改
添加字段
#字段不够，表结构的修改
#字段添加(追加) 用frist/after 调整位置
alter table student add sex bit(1);
alter table student add email varchar(20) first;
alter table student add birthday date after sid;
#查看表结构,表的一些信息
desc student ;
修改字段（字段名称，字段类型，长度，约束，位置等）
alter table tname change 旧字段 新字段 类型 frist/after 字段名称位置可一起修改
alter table tname modify 字段 类型 frist/after 修改长度位置等
alter table student change email semail varchar(20) after sid;
alter table student modify semail varchar(50);
删除字段
alter table student drop semail;
修改表名：
rename table student to stu;
清空表格：(删除数据，表结构还在)
truncate table tname;
销毁表格：(数据与表结构全部删除)
drop table tname;
3.数据完整性(约束)
3.1 实体完整性（记录之间是准确的，不重复的）
(1)主键约束：primary key，唯一且不能为空
#创建表的时候添加,两种语法
CREATE table student(sid int primary key,sname VARCHAR(20),birthday date);
CREATE table student(sid int,sname VARCHAR(20),birthday date,primary key(sid));
#表已经存在
CREATE table student(sid int,sname VARCHAR(20),birthday date);
alter table student add constraint PK_SID(名称自定，但默认为PK_开头) primary key(sid);
注意：
每张表有且只有一个主键，但是可以有联合主键（多个字段当作主键）,一般不把业务字段当做主键
联合主键写法：
CREATE table student(sid int,sname VARCHAR(20),birthday date,primary key(sid,sname));
alter table student add constraint PK_SID(名称自定，但默认为PK_开头) primary key(sid,sname);
constraint：约束
(2)唯一约束：unique(字段值唯一)
身份证号…
CREATE table student( sid int unique,sname VARCHAR(20),birthday date);
(3)主键自增：auto_increment
CREATE table student(sid int auto_increment,sname VARCHAR(20),birthday date, primary key(sid))
3.2 域完整性（字段值准确）
(1).数据类型约束
(2).非空约束（not null）
(3).默认值(default)
create table userinfo(sid int primary key auto_increment,uesername varchar(20) not null,sex bit(1) default 1);
3.3 引用完整性（参照完整性）
外键约束：foreign key
classroom表
pk(主键约束)
cid —————— cname
1 —————— 1909da
2 —————— 1910da
student
pk ——————班级编号fk(外键约束)
sid ———sname———cid
1 ———— zs ———— 2
2 ———— ls ———— 2
alter table student add constraint FK_CID foreign key(cid) refrences classroom(cid);
refrences:参照
把student表里面的cid作为外键，取值参照classroom里面的cid
跨库把库名字写上后面加一点
4.数据操作语言(DML)
4.1 数据添加：
insert into tname[(col1,col2...)] values(val1,val2,...);
表名字后不写值，代表给所有的字段添加值(值的顺序和表的字段顺序一样)
create table userinfo(uid int primary key,uname varchar(20),sex bit(1));
insert into userinfo values(1,'zs',0);
给部分字段添加值
insert into userinfo(uid,uname) values(2,'ls');
添加多条记录
insert into userinfo(uid,uname) values(3,'ww'),(4,'zy'),(5,'hu');
查询表中所有的记录
select * from userinfo;
查询表的结构
select * from userinfo where 0;
复制表结构
create table users select * from userinfo where 0;
将表格中记录添加到新表中
insert into user select * from userinfo;
上面两句等价于
create table user select * from userinfo;
4.2 数据修改：
update tname set col1=val1,col2=val2 [where]
不加where会修改表中所有的数据
update userinfo set sex=0 where uid = 4;
4.3 数据删除
delete from tname [where]
不加where删除表中所有内容
delete from userinfo where uid = 5;
delete不会重置自增，逐行删除
truncate:清空（重置自增）
两个都不会清除表的结构
drop会删除表
5.数据查询语言(DQL)
5.1 select与运算符
查询student表中所有的记录，所有的字段
select * from student;
*:通配符（全部查询）
select后面加表达式/字段
查询单个字段
select sname from student;
想展示什么东西，全部使用select去展示
运算符：
1.算数运算符
#算数运算符+、-、*、/、%(取余)
select 1+1;
select 5/2; #2.5
select 5%2;
select 3/0; #得到空值
select 5 div 2; #2,取模运算
2.比较运算符
true-->1
false-->0
#比较运算符= > < >= <= / != <>
select 1=1;
select 1!=1;
3.逻辑运算符and、or、!
select 1>2 and 1<2; # 0
select 1>2 or 1<2; # 1
select !(1>2); # 1
4.位运算符 & 、| 、^ （使用较少）
先将数据转换为二进制然后运算
&:按位与 有一侧为0结果为0
select 3 & 2 # 2
|:按位或 有一侧为1结果为1
select 3 | 2 # 3
^:按位异或 两侧数据一样取0,两侧数据不一样取1
select 3 ^ 2 # 1
5.2 数据查询
select 子句 #表达式/字段,结果集中展示的内容
from 子句 #表/结果集/视图,数据的来源（从哪个数据来的）
where 子句 #条件(单个条件,组合条件)（查询条件）
order by 子句 #排序
group by 子句 #分组
having 子句 #分组之后条件
limit 子句 #限制结果查询(mysql方言) 分页
(1)查询所有文件
select * from emp;
(2).查询部分字段
查询员工的编号和名称
select empno,ename from emp;
(3).单一条件查询where
查询20号部门的员工的所有的信息
select * from emp where deptno = 20
(4).组合条件
查询20号部门工资>2000的员工信息
select * from emp where sal > 2000 and deptno = 20;
查询员工编号为7788，7521，7369的员工信息
select * from emp where empno = 7788 or empno = 7521 or empno = 7369;
(5).范围查询
查询工资在1000到2000之间的员工信息
select * from emp where sal >= 1000 and sal <= 2000;
范围between and
select * from emp where sal between 1000 and 2000;
(6).集合查询
查询员工编号为7788，7521，7369的员工信息
select * from emp where empno in (7788,7521,7369);
(7).别名（字段、表达式、结果集、表…）[as] 别名
查询员工工资提升%5之后的样子(将sal*1.05取名为sal)
select ename,sal*1.05 as sal from emp;
表取别名（将emp取别名为e）
select e.ename from emp as e;
(8).去重（distinct）
查询所有的职位
select distinct job from emp;
(9).空判断 is
查询没有奖金员工的信息（空的判断不能用=，只能用is）
select * from emp where comm is [not] null;
(10).排序order by 字段 [asc(升序)|desc(降序)]
按工资由低到高排序(默认升序asc)
select * from emp order by sal [asc];
按工资由高到低排序(降序desc)
select * from emp order by sal desc;
工资一样，按照empno升序排列(第二排序)
select * from emp order by sal,empno; #并列时第二排序
(11).模糊查询like
%:代表0-多个字符
_:代表一位字符
(1)开头
(2)结尾
(3)包含
(4)第几位
查询名称中包含s的员工信息
s开头
select * from emp where ename like 's%';
s结尾
select * from emp where ename like '%s';
包含s
select * from emp where ename like '%s%';
查询第二位为s的员工信息
select * from emp where ename like '_s%';
(12).限制结果查询 limit index(什么地方开始，初始值为0),length(取几个数据)
查询前五条数据
select * from emp limit 0,5;
从0开始可以省略index
select * from emp limit 5;
从第二个开始查
select * from emp limit 1,5;
注意：limit只能从前往后取，想要倒取数据，可以先把数据倒序，再取
LIMIT，你在打印数据的时候，select * from table limit 10;
想看看表里的数据长什么样子的时候，使用limit。
5.3 文件导入
LOAD 数据导入，在终端执行
LOAD DATA LOCAL INFILE 'D:\\data_analysis\\mysql\\data\\data.txt' INTO TABLE `student_score` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
使用navicate导入数据：
导入txt/excel等文件：右击表，导入向导
导入sql文件:找到需要导入的数据库–右键数据库–运行sql文件–选择文件–编码选utf-8
6.函数
6.1单行函数(返回的结果为一条记录)
(1)数学函数：数学运算
# 绝对值 -- abs()
select abs(-10);
# 向上取整,最接近并且大于等于该值的整数值 -- ceiling()/ceil()
select ceil(10.5);#向上取整
# 向下取整,最接近并且小于等于该值的整数值 -- floor()
select floor(10.5);#向下取整
# 取模运算 -- mode(m,n)
# 求PI值 -- PI()
# 求幂运算 -- pow(m,n)
# 随机数 -- rand()
select rand();#随机数
# 四舍五入 -- round(m,n)
select round(10.3);#四舍五入
select round(10.46,1);#保留一位小数
select round(12.46,-1);#值为10
# 截取m小数点后n位
truncate(m,n)
(2)字符函数：字符串操作
# 获取str的ascii码值 -- ascii(str)
# 将字符串转换为小写 -- lower(字段|表达式)
# 将字符串转换为大写 -- upper(字段|表达式)
# 拼接字符串 -- concat(str1,str2…)
# 获取字符串长度 -- length(字段|表达式)
select length('abc');#获取字符长度
select length(ename) from emp; #看表中字段长度
# 截取字符串,pos开始位置，从1开始；len表示长度 -- substr(str,pos,len)
# 在str中搜索old，使用new代替 -- replace(str,old,new)
# str长度不够len，使用s左侧填充 -- lpad(str,len,s)
# str长度不够len,使用s右侧填充 -- rpad(str,len,s)
# 去重左右两侧的空格 -- trim()
(3)日期函数：日期操作
# 获取当前日期时间 -- NOW()/SYSDATE()/CURRENT_TIMESTAMP()
SELECT NOW();#获取当前年月日，时分秒
SELECT CURRENT_TIMESTAMP();#时间戳
# 获取当前系统日期 -- CURRENT_DATE()/CURDATE()
SELECT CURRENT_DATE();
# 获取当前系统时间 -- CURRENT_TIME()/CURTIME()
SELECT CURRENT_TIME();
# 获取天数 -- DAY(date)
# 获取月份 -- MONTH(date)
select month(now());
# 获取年份 -- YEAR(date)
select year(now());
select year('2018-10-5');
# 返回一年中的周数 -- week(date)
# 返回一周中的第几天(0-6) -- weekday(date)
# 日期计算 -- DATE_ADD(date,INTERVAL expr unit)
select date_add(now(),interval -2 week);#现在两周以前时间
select date_add('2019-03-02',interval 5 week);
#年月日周时分秒都可以
#求二月最后一天是几号
select LAST_DAY('2018-2-03');#获取给定日期月的最后一天
6.2 聚合函数(返回一个结果或一条结果)
聚合运算：select 不能出现字段名称
# 平均值 -- avg()
# 获取总数 -- sum()
# 获取最大值 -- max()
# 获取最小值 -- min()
# 统计数目 -- count()
统计记录数或者统计某个非空字段的个数
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
#总记录数
select count(*) from emp;
select count(1) from emp;
#求字段数,求非空的数
select count(empno) from emp;#14
select count(comm) from emp;#3
6.3 分组函数(将数据划分为更小的组，再进行计算)
group by 字段：根据字段值的不同划分为多个小组，每个小组返回一条针对该组的单个结果
#每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
having子句：和where作用基本一致，但是在出现group by 之后，主要分组之后再次进行过滤
用于指定 GROUP BY 子句检索行的条件
#查询平均工资大于2000的部门编号及其平均工资
#1.求出每个部门的平均工资
#2.从平均工资中找出超过2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
注意：
1.where只能出现在group by 之前
2.where中不能出现聚合函数
6.4 加密函数
select md5('root');
select sha('root');
select password('root');
#加密函数
select 'root';#root
select md5('root');#加密后数据--63a9f0ea7bb98050796b649e85481845
6.5 流程函数
(1)如果expr1是真, 返回expr2, 否则返回expr3
IF(expr1,expr2,expr3)
select if(1>2,1,2);
(2)如果expr1不是NULL,返回expr1,否则返回expr2
IFNULL(expr1,expr2)
select ifnull(comm,0) from emp;
(3)如果value1是真, 返回result1,否则返回default
CASE
WHEN [value1] THEN[result1]
WHEN [value2] THEN[result2]
…
ELSE[default]
END
#>=60 A <60 B
select sname,
case
when score >=60 then 'A'
when score <60 then 'B'
end leve #取别名
from student;
(4)如果expr等于value1, 返回result1,否则返回default
CASE [expr]
WHEN [value1] THEN[result1]
WHEN [value2] THEN[result2]
…
ELSE [default]
END
6.6 窗口函数
https://blog.csdn.net/m0_47581892/article/details/112565860
7.高级查询
7.1 多表连接查询（没有关联字段，得到的结果是笛卡尔集）
笛卡尔集：多张表中记录组合所有可能有序对集合（关联表记录值的乘积）
查询所有员工的姓名及其所在部门的名称(分布于多张表)
加限制条件，只留下需要的信息
select ename,dname from emp,dept where emp.deptno = dept.deptno;
7.2 内连接
(1)与连接顺序无关（没有主从表）
(2)多张表都能匹配的数据才能展示在结果中(参考例子中的40号部门,下面没有员工，匹配后被丢掉了)
查询所有雇员及其部门信息
select * from emp,dept where emp.deptno = dept.deptno;#方言格式
select * from emp inner join dept on emp.deptno = dept.deptno;#标准内连接语法
xx inner join xx on 关联条件
字段名称必须一致,会自动去除重复列
select * from emp inner join dept using(deptno);
使用前提：关联字段名称必须是一样的，而且是等值连接。优点：自动去重字段
7.3 外连接
left [outer] join …on…
right [outer] join …on…
(1)与连接顺序有关（主从表之分，驱动表/附属表）
(2)以主表为基准，依次在从表中寻找关联的记录，如果匹配则关联并展示在结果集中，否则以null填充
#外连接
select * from emp left join dept on emp.deptno = dept.deptno;#14条记录
select * from dept left join emp on emp.deptno = dept.deptno;#15条记录
7.4 自连接（以本身为镜像进行连接(自身连接自身)）
查询员工及领导的姓名
select e1.ename,e2.ename from emp e1 ,emp e2 where e1.mgr = e2.empno;#起别名
问题：查询编号为7788的员工所在部门的名称
1.连接查询
2.其他方式？（分布）
#查询编号为7788的员工所在的部门名称
#编号在emp表里面，结果(部门名称)在dept表里面
#1.将两表连接查询，条件和结果在一张表的时候，查询简单
select * from emp,dept where emp.deptno = dept.deptno;
#找结果
select dname from emp,dept where emp.deptno = dept.deptno and empno = 7788;
#2.其他思路
#(1)在emp表中查询7788所在部门的编号
select deptno from emp where empno = 7788;
#(2)在dept表根据编号查询所在部门
select dname from dept where deptno = 20;
#汇总写法，sql从左往右执行语句，可以加小括号更改执行顺序
select dname from dept where deptno = (select deptno from emp where empno = 7788);
第二种方法引申出子查询
7.5 子查询
子查询–>嵌套查询
select dname from dept where deptno = (select deptno from emp where empno = 7788);
分类：
(1).单行子查询：子查询返回的结果为单行单列
上面例子就为单行子查询
(2).多行子查询：子查询返回的结果是多行
子结果是单个值的话可以使用>、<、=
子结果是多行多个值的话使用in、any、all
any用法：=any <等价于> in
>any 大于(子查询结果)最小值
<any 小于(子查询结果)最大值
all用法：>all 大于最大值
<all 小于最小值
查询工资超过smith的员工的信息
思考过程
1.先找出smith的工资
select sal from emp where ename = 'smith';
2.找出工资大于smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');
查询薪水>2000的员工所在部门的名称
1.薪水>2000的员工的部门编号哪些,distinct去重
select distinct deptno from emp where sal > 2000;
2.员工部门编号
select dname from dept where deptno in (select distinct deptno from emp where sal > 2000);
7.6 案例
(1)查询薪水超过所在部门平均工资的员工信息
思路1：采用连接
各个部门平均工资
select deptno,avg(sal) from emp group by deptno;
将上面结果集与emp表关联求值
select emp.* from emp,(select deptno,avg(sal) avg from emp group by deptno) e where emp.deptno = e.deptno and sal>avg;
emp.*[只要emp表里的内容]
思路2：采用子查询
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);
1.主查询传递deptno给子查询
2.子查寻根据传递的deptno查询出所在部门的平均工资返回给主查询
3.主查询根据子查询返回的部门平均工资来处理后续
(2)薪水>2000的员工所在部门的名称
先执行子查询，再执行主查询
select dname from dept where deptno in (select distinct deptno from emp where sal > 2000);
只考虑记录匹配，不考虑字段
select dname from dept where exists(select * from emp where sal >2000 and emp.deptno = dept.deptno);
exists考虑的是匹配的问题，先执行主查询，将主查询的记录依次交给子查询进行匹配，如果能够匹配则子查询返回True，主查询的结果显示在结果集
in：先把子查询所有的结果查询出来，再返回给主查询匹配
7.7 联合查询
union: 并集，所有的内容都查询，重复的显示一次
union all: 并集，所有的内容都显示，包括重复的
查询20号部门或者工资>2000的员工信息
select * from emp where deptno = 20 or sal >2000;
#联合
select *from emp where deptno = 20
union
select * from emp where sal > 2000;