MySQL: 数据库: 1.关系型数据库: mysql sqlservser oracle 2.非关系型数据库:hbase 、 clickhouse 、redis
1.mysql 是什么? 1.关系型数据库 2.类似表格 3.有行有列 4.存储数据 2.为什么要学mysql ? 1.业务数据 存在 mysql里面 2.it : java 大数据 算法 =》 业务数据
1.版本 5.7 5.6 8.x :加了一个开窗函数
2.部署
1.平台:操作系统 win 、linux 、mac 生产上: linux 2 win 1
3.部署 linux : 1.linux环境 2.mysql linux 安装包
1. linux =》
mysql =》
-
win =》 mysql
linux : 1.操作系统 2.为什么要学linux ?
3.发明者 linux 内核 、 git 4.版本 ubauntu、redhat、centos、debian等 centos: 6.x 7.x
2.部署linux 1.虚拟化的软件: 1.vmware 2.vbox 2.linux镜像 centos 7.6
linux下 目录结构: 20G 1.最顶层目录 / 19g 2./boot : 1g
2.远程连接 1.远程连接的软件: xshell scuret 2.如何连接: 1.linux机器: 1.ip : 192.168.10.135 【ifconfig】 2.port : 22 1-65535 3.用户名; root 4.密码:123456
[root@st10 ~]# root =》 用户名 st10 =》 机器的名字 hostname =》 ip ~ 用户的家目录【目录】
linux文件系统: 1.层级式的树状 目录结构
基本命令: 1.pwd 查看当前光标所在目录 [root@st10 ~]# pwd /root
2.ls 查看目录下面的文件或者文件夹 [root@st10 ~]# ls
ls -l 显示文件或者文件夹 的额外信息 ls -l -a 显示文件或者文件夹 的额外信息 显示隐藏文件 隐藏文件 .xxx ls -l -h 显示文件或者文件夹 的额外信息 显示文件大小 命令帮助: xxx --help
ls : 用法:ls [选项]... [文件]...
[] 可选 ... 多个
ls -l => ll
[root@st10 ~]# ll -ah => ll -a -h
3.mkdir 创建文件夹 [root@st10 ~]# mkdir bigdata [root@st10 ~]# ls bigdata 1.并级创建文件夹 [root@st10 ~]# mkdir dir1 dir2 dir3 2.串级创建文件夹 [root@st10 ~]# mkdir -p dir4/dir5/dir6
4.cd 切换目录 [root@st10 ~]# cd bigdata/ [root@st10 bigdata]# pwd /root/bigdata
目录: 1.绝对路径:光标从根目录开始 2.相对路径:光标从当前目录开始
./ 当前目录 ../ 上一层级目录
[root@st10 ~]# cd ./dir4 [root@st10 dir4]# cd ../
如何回到家目录: ~ root 用户的 家目录 : /root xxxx 用户的 家目录 : /home/xxxx /home/sxwang 三种方式: 1.cd 回车 2.cd /root 使用 用户家目录 的绝对路径的方式 3.cd ~
5.创建文件(三种方式) 1.touch xxx [root@st10 ~]# touch 1.log 2. vim/vi xxx [编辑文件的名命令] [root@st10 ~]# vim 2.log
3.echo "xxx" > 3.log echo "xxx" => 控制台打印 > =》 创建或者覆盖一个文件 >> => 追加到一个文件 echo "xxx" >> 3.log
6.vim 编辑文件+文件里面添加内容 vim 命令: 三种模式: 命令行模式 编辑模式 尾行模式
编辑一个文件: 1.打开文件 vim 1.log => 命令行模式 2.编辑文件 【往文件里面添加内容】 =》 命令行模式 -> 编辑模式 i 添加内容 3.退出保存 =》 编辑模式 编辑模式 =》 命令行模式 =》尾行模式 esc =》 尾行模式【保存退出】: 1.进入尾行模式 : shift+; => : w 保存 q 退出 ! 强制
7.查看文件(4个) 1.cat xxx 文件内容全部显示 【查看文件内容比较少】 [root@st10 ~]# cat 1.log
2.查看大文件 more xxx 文件内容一页一页往下翻 按 空格往下翻 q退出 ctrl+ c ctrl+ z less xxx 文件内容一页一页往下翻 按 空格往下翻 q退出 ctrl+ c ctrl+ z 3.实时查看 tail -f / -F xxx f F = f+ retry
8.tar 归档文件
1.log 2.log 3.log => xxx.tar => xxx.tar.gz
linux: gzip
1.创建归档文件 [root@st10 ~]# tar -cf log.tar 1.log 2.log 3.log
1.加 一个 压缩 [root@st10 ~]# tar -zcf log.tar.gz 1.log 2.log 3.log 2.用户有感知 -v tar -zcvf log1.tar.gz 1.log 2.log 3.log
2.取出归档文件里面的文件 [root@st10 ~]# tar -xf log.tar -C ./bigdata/
1.取出一个 压缩 [root@st10 ~]# tar -zxf log.tar.gz -C ./dir1/ 2.用户有感知 -v tar -zxvf log1.tar.gz -C ./dir2/
gz: -z -zxvf 取出 -zcvf 创建 普通: -xvf 取出 -cvf 创建 linux 安装软件: 1.rpm 2.yum 3.tar 软件: 1.配置文件 rpm -qa 查询linux下 有哪些 rpm的包 | 管道符 =》 上一个命令的结果作为下一个命令的输入 grep 过滤
9.linux 部署mysql 1.安装包
1.解压 [root@st10 ~]# tar -xvf ./mysql-8.0.21-1.el7.x86_64.rpm-bundle.tar -C ./mysql 2.卸载 linux自带的 mysql 子类包 1.查询: rpm -qa|grep mariadb 2.卸载 rpm -e --nodeps mariadb-libs 再次查看: rpm -qa|grep mariadb 3.安装 rpm -ivh mysql-community-common-8.0.21-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.21-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.21-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.21-1.el7.x86_64.rpm 4.启动 mysql日志文件:/var/log/mysqld.log 1.mysql 初始化 mysqld --initialize --user=mysql 有一个临时密码: root@localhost: UJsboU3cqD.r 2.启动mysql systemctl start mysqld mysql对外服务的端口: port: 3306 5.登录mysql mysql -uroot -pUJsboU3cqD.r 需要转义: ( mysql -uroot -p\(
mysql: database 库 文件夹 table 表 excel
6.修改密码 alter user root@localhost identified by '123456'; 7.远程连接 1.修改mysql 任意ip都可以访问 update mysql.user set host='%' where user='root'; 2.刷新权限 flush privileges; 3.关闭防火墙 systemctl stop firewalld systemctl disable firewalld 2.远程登录的软件 netcat dbvear
10.win
MySQL语法: database =》 数据库 文件夹 table =》 表 excel
table: 1.列名 column 2.列的类型 type
1.字段类型: 数值类型 : int 整型 long 长整型
float 单精度 double 双精度 decimal 小数值 钱有关的 字符串: char 字节 定长 0-255 bigdataxxxxxx 自动补齐的 varchar 字符串 变长 0-65535 bigdata text 日期: date 日期 YYYY-MM-DD time 时间 HH:MM:SS datetime 年月日时分秒 YYYY-MM-DD HH:MM:SS timestamp 年月日时分秒 YYYY-MM-DD HH:MM:SS
2.sql类型 ddl 数据定义语言:create drop alter dml 数据操作语言: select insert update delete 增删查改 dcl 数据控制语言: grant 【不需要掌握】
3.基本语法 【sql】 database table
1.创建数据库 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} } create database bigdata; 2.查看数据库 show databases; 3.创建一个表 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] 4.切换数据库 use bigdata; create TABLE student( id int(11), name varchar(20), age int(3) ); 5.查看库下面的表 show tables;
6.补充 建表: create TABLE student( id int(11), name varchar(20), age int(3) ); create TABLE student_info( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) COMMENT '姓名', age int(3), create_user varchar(20), create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY(id) );
注意: 1.表名称 一定要写英文 2.建表风格 3.第一字段 使用自增主键 【本身没有任何业务意义】 4.字段 要加上注释
7.insert 插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name [, col_name] ...)] { {VALUES | VALUE} (value_list) [, (value_list)] ... } insert into bigdata.student_info(name,age) VALUES ('俊松',18); insert into bigdata.student_info(name,age) VALUES ('junsong',18),("xinyu",38);
8.查看表中数据
select select_expr => [ column ] from bigdata.student_info where [ 过滤]
-
[ 代表所有字段] select
-
from bigdata.student_info;
select name,age from bigdata.student_info
9.update 更改数据 【 注意:是否要加 where 】
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] update bigdata.student_info set age=20 where name='xinyu'; update bigdata.student_info set age=20 ;
10.delete 语句 删除某条数据 【 注意:是否要加 where 】 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
delete FROM bigdata.student_info where name='xinyu'; delete FROM bigdata.student_info;
6.其他语法 1.where 【过滤条件】
where column expresion 1.
> < = and or in not in insert into bigdata.student_info(name,age) VALUES ('俊松',18); insert into bigdata.student_info(name,age) VALUES ('junsong',18),("xinyu",38); insert into bigdata.student_info(name,age) VALUES ('js',18),("java",38); insert into bigdata.student_info(name,age) VALUES ('lb',18),("lb01",38); insert into bigdata.student_info(name,age) VALUES ('lb02',18),("cmx",38); insert into bigdata.student_info(name,age) VALUES ('lb',20); insert into bigdata.student_info(name,age) VALUES ('lb',30); select * from bigdata.student_info where age <20; select * from bigdata.student_info where name='lb' and age=18; select * from bigdata.student_info where name='lb' or age=38; select * from bigdata.student_info where name in ("俊松",'junsong');
2.order by 排序语法
select * from bigdata.student_info order by age asc; select * from bigdata.student_info order by age desc; select * from bigdata.student_info order by age desc; select * from bigdata.student_info order by age desc,name asc;
3.like 语法 【 regexp 】 模糊查询 : 1.% 模糊 2._ 占位符
select * from bigdata.student_info where name like "%j%"; select * from bigdata.student_info where name like "_b%";
4.合并表 union 去重 union all 不去重
create table a(id int,name varchar(20)); create table b(id int,name varchar(20)); insert into a VALUES(1,'js'); insert into b VALUES(1,'js'); insert into b VALUES(2,'xy'); select * from a union select * from b ; select * from a union all select * from b ;
5.null 语法 insert into bigdata.student_info(name,age,create_user) VALUES ('lb',30,"zsdnr"); 1.过滤空值 : null '' 'null'
is null : select * from student_info where create_user is null; is not null : 2.处理空值 : etl 数据清洗 null =》 1.数值类型 0 2.字符串 unknown 、 --
select *,处理空值的函数(create_user) as create_user_etl from student_info;
处理空值的函数:
ifnull coalesce select *,ifnull(create_user,'--') as create_user_etl from student_info;
select name,age,create_user,coalesce(create_user,'--') as create_user_etl from student_info;
6.聚合函数(分组的语法)
1.分组 : group by xxx,... [以谁进行分组] 2.聚合函数 sum avg max min count
1.不加分组 =》 全表 =》聚合函数 =》 指标 平均年龄 select avg(age) as avg_age from student_info;
2.分组+ 聚合函数
select name, avg(age) as avg_age from student_info group by name;
原本数据: 俊松 junsong xinyu js java lb lb01 lb02 cmx lb lb lb
分组+聚合函数【统计指标】=》 1.分组 lb,<20,30,30> junsong,<18> lb02,<20> 2.聚合函数【统计指标】 avg lb , 80/3 junsong ,18/1
1.分组语法 聚合函数: 多行数据按照一定规则 聚合为 一行 理论上说: 聚合后的行数 <= 聚合前的行数
1.聚合函数 sum avg max min count 1.作用整张表 【不使用group by 】 select sum(age) as age_sum ,avg(age) as age_avg ,max(age) as age_max ,min(age) as age_min ,count(age) as cnt from student_info;
统计表中有多个名字重名
select name, count(name) as cnt from student_info group by name having cnt >1;
对数据进行去重?
app =》 抖音 1.pv 一个人 多次 cnt sum 每个用户登录的次数 2.uv 一个人 多次 1个人 今天 用户在线人数【登录过抖音的人数】 1.统计 pv uv 维度:-- 指标: pv uv 要什么给什么 select count(name) as pv, count(distinct name) as uv from student_info; 1.pv select name , count(name) as pv from student_info group by name 2.uv 去重 1.distinct 性能低 select count(distinct name) as cnt from student_info; 2. group by select count(*) as uv from ( select name from student_info group by name ) a; select count(1) as uv from ( select name from student_info group by name ) a
count(name) 1. count() => column : * ,name 2. count(任意值 ) 任意值、第一个字段 1 2
select name , 1 from student_info group by name select name from student_info group by name
原始数据: 俊松 junsong xinyu js java lb lb01 lb02 cmx lb lb lb
group by : lb ,<lb,lb,lb,lb> cmx,<cmx> java,<java>
2.按照name进行分组 统计 select name, sum(age) as age_sum, avg(age) as age_avg, max(age) as age_max, min(age) as age_min, count(age) as cnt from student_info group by name ;
3.按照name进行分组 结果数据 进一步统计 1.having =》 where 【只能在group by 之后使用】 where 是作用到 from 之后的 select name, sum(age) as age_sum, avg(age) as age_avg, max(age) as age_max, min(age) as age_min, count(age) as cnt from student_info group by name having cnt >2; 2.子查询 [sql 套sql 套娃] select name, age_sum, age_avg, age_max, age_min, cnt from ( select name, sum(age) as age_sum, avg(age) as age_avg, max(age) as age_max, min(age) as age_min, count(age) as cnt from student_info group by name ) a where cnt > 2;
2.join (多表联查) 1.广义上 : inner 、left、right、full 、 CROSS(笛卡尔积)【了解】 2.狭义上 7种
主表 从表 事实表 维度表
create table a1(id int ,name varchar(50),address varchar(20)); create table b1(id int ,name varchar(50),age int(3));
insert into a1 values(1,'js','辽阳'); insert into a1 values(2,'xy','朝阳'); insert into a1 values(4,'hy','辽阳');
insert into b1 values(1,'js',12); insert into b1 values(2,'xy',18); insert into b1 values(3,'sxwang',20);
1.内连接 inner inner join 结果数据: 取出两表 共有的数据
select a1.*, b1.* from a1 inner join b1 on a1.id = b1.id ; select a1.*, b1.* from a1 join b1 on a1.id = b1.id ; select a1.*, age from a1 join b1 on a1.id = b1.id ;
2.左连接 (left join ) 以左表为主 数据是全的 右表来匹配 匹配不上就是 null
select a1.*, b1.* from a1 left join b1 on a1.id = b1.id ;
3.右连接 (right join ) 以右表为主 数据是全的 左表来匹配 匹配不上就是 null
select a1.*, b1.* from a1 right join b1 on a1.id = b1.id and a1.name=b1.name ;
4.full outer join 全连接 mysql 不支持 full join 以左表为主 数据是全的 右表来匹配 匹配不上就是 null 以右表为主 数据是全的 左表来匹配 匹配不上就是 null
select a1.*, b1.* from a1 full outer join b1 on a1.id = b1.id and a1.name=b1.name ; select a1.*, b1.* from a1 left join b1 on a1.id = b1.id union select a1.*, b1.* from a1 right join b1 on a1.id = b1.id and a1.name=b1.name ;
5.case when 语法结构
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END