1 使用场景
- java开发人员 业务数据库
- 大数据组件 元数据 、结果数据
2 什么是MySQL
- 关系型数据库: RDBMS 非关系型数据库NoSQL
- 存储数据
- 查询
- 类似表格
- 有行有列
3 背景
- 处理数据 :excel
- mysql:
- 存储数据,数据分析
- 一堆类似excel的表格
4 官网
www.mysql.com
5 版本
- 8.x 不是主流、java团队
- 5.7 it java 大数据 主流
- 5.6 目前公司也有
- 补充:
- 上传下载:
·[root@bigdata31 software]# yum install -y lrzsz· - 上传: rz
- 下载 :sz xxx
- 上传下载:
6 部署
下载安装包
- 5.7
- 安装包种类:
- rpm包 :mysql source code =》 rpm包
mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
- tar包:mysql source code +开发加的mysql代码 =》 编译打包生成 tar包
mysql-5.7.38-el7-x86_64.tar.gz
- rpm包 :mysql source code =》 rpm包
安装部署
- 上传mysql 安装包
[root@bigdata31 software]# ll
total 595272
-rw-r--r--. 1 root root 609556480 Aug 26 2021 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
[root@bigdata31 software]# pwd
/root/software
- 部署
- 解压
[root@bigdata31 software]# tar -xvf ./mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-test-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm
安装
- 卸载 mariadb 相关的东西
[root@bigdata31 software]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@bigdata31 software]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
[root@bigdata31 software]# rpm -qa | grep mariadb
注意
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
有一个 配置文件 管控mysql
/etc/my.cnf
mysql运行日志文件:log-error=/var/log/mysqld.log
初始化mysql
mysqld --initialize --user=mysql
A temporary password is generated for root@localhost: rfnYYHj0jc,R
启动mysql
systemctl start mysqld
mysql 可以对外提供服务:
port: 3306
登录mysql 使用
mysql -uroot -prfnYYHj0jc,R
修改mysql 软件 root用户 密码
set password = password('123456');
赋予权限
flush privileges;
- 补充:
-
卸载:
1. mysql 停掉
systemctl stop mysqld
2. mysql卸载
rpm -qa | grep mysql
rpm -qa | grep mysql | xargs -n1 rpm -e --nodeps
3. linux存储目录删掉
find / -name "*mysql*"
注意:/sys/
4. 重装 -
mysql 全部卸载重装
shift + alt
=>多行编辑
alt + tab
rm -rf xx
-
mysql 数据库
- excel 表格
- win:
- 文件夹:
- 一堆表格
7 MySQL
数据库:=》文件夹 database
一堆表格 table
远程登录工具
navcat 【选择】
sqlyog
dbvear工具 【选择】
root@localhost: 123456
只允许 当前机器登录
root@%: 123456
远程登录访问:
- 修改 mysql 登录 运行用户的ip =》 任意ip
mysql语法【了解】
1.查看数据库
show databases;
2.切换数据库
use mysql;
3.查看库下面有哪些表
show tables;
4.查看表中数据
select host,user
from user;
5.修改表中数据
update mysql.user set host="%" where user="root";
6.刷新权限
flush privileges;
基本语法
1.sql进行开发
sql类型:
ddl 数据定义语言 :create drop alter
dml 数据操作语言 : insert select update delete
dcl 数据控制语言
2.查看数据库
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
{} 必选参数 | 可选
[] 可选
SHOW DATABASES;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3.切换数据库
use dbname;
4.查看数据库下面的表
SHOW [FULL] TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TABLES;
mysql> show tables FROM sys;
5.查看表中描述
desc tablename;
```# ## 6.创建数据库
```sql
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
CREATE DATABASE bigdata;
7.创建表
- excel :
- 字段:column :
1. 名称
2. 字段类型- create 建表规范:
- 表名字 一定是英文 汉语拼音 不要写中文
- 建表风格
- 主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义
- 字段的注释
- create 建表规范:
- 业务字段
- 非业务字段:
- 表创建表用户 vs 更新表用户
- 主键
create table user_info2(
id int(3) not null auto_increment,
name varchar(10) COMMENT '用户名称',
age int(3),
create_user varchar(10),
create_time timestamp not null default current_timestamp,
update_user varchar(10),
update_time timestamp not null default current_timestamp on update current_timestamp
,primary key(id)
);
常用字段类型
数值类型:
- 整数:
- int 整型
- ong 长整型
- 小数:
- float 单精度
- double 双精度
- decimal 小数 =》 跟钱挂钩的
字符串:
* char 字节 长度 0-255 bigdataxxxxxxx 255
* varchar 字符串 长度范围
日期:
* date 日期: YYYY-MM-DD
* time 时间 : HH:mm:SS
* datetime 年月日时分秒 : YYYY-MM-DD HH:mm:SS
* timestamp 年月日时分秒【时间戳】:YYYY-MM-DD HH:mm:SS
mysql> CREATE TABLE user_table(
-> id int(3),
-> name varchar(10),
-> age int(3)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_bigdata |
+-------------------+
| user_table |
+-------------------+
1 row in set (0.00 sec)
8.表中插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT into user_table (id,name,age)VALUES (1,'zhangsan',10);
create table user_info(
id int(3) not null auto_increment,
name varchar(10) COMMENT,
age int(3),
create_user varchar(10),
create_time timestamp not null default current_timestamp,
update_user varchar(10),
update_time timestamp not null default current_timestamp on update current_timestamp
,primary key(id)
);
insert into user_info (name,age) VALUES('zs',18),('ls',20);
insert into user_info (name,age) VALUES('zs01',18),('ls01',20);
NULL: 空值
'' 'null' 'NULL'
9.更新数据
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
where 过滤
update user_info set age='20';
=》 对整张表
update user_info set age='20' where name='zs' ;
对某条数据
update 语句 注意: where
10.删除一条数据
delete
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
delete from user_info where id=1;
注意:
delete 也要考虑 是否加where
insert into user_info (name,age) VALUES('zs',18),('ls',20);
insert into user_info (name,age) VALUES('zihang01',10),('子航01',10);
insert into user_info (name,age) VALUES('zihang02',11),('子航02',20);
insert into user_info (name,age) VALUES('zihang03',12),('子航03',30);
insert into user_info (name,age) VALUES('zihang04',13),('子航04',21);
insert into user_info (name,age) VALUES('zs',19),('zs',21);
表的字符集:
udf8
Incorrect string value: '\xE5\xAD\x90\xE8\x88\xAA...' for column 'name
mysql 5.7版本 默认建表字符集 :latin1
解决:修改字符集 、建表直接指定字符集 utf8
11.查看数据
*表示所有字段
select *
from user_table;
select name,age from user_table;
12.删除表
drop table user_info;
CREATE TABLE `user_info` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`create_user` varchar(10) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_user` varchar(10) DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13.其他语法
1.where 过滤条件
1. >
2. <
3. =
4. and or in not in
mysql> select * from user_info where age > 18;
mysql> select * from user_info where name='子航01';
mysql> select * from user_info where name='zs' and age>18;
mysql> select * from user_info where age>20 or name='zs';
产品给你 1000个数据值
in
select * from user_info where name='zs' or name = 'ls' or name ="ww"
select * from user_info where name in ('zs','ls','ww');
not in
select * from user_info where name not in ('zs','ls','ww');
2.order by 排序语法
mysql> select * from user_info order by age;
order by column [asc | desc] ,...
1.默认是升序
2. asc desc 降序
mysql> select * from user_info order by age ,name desc;
3.like语法 模糊查询
1.like rlike regexp 正则表达式【了解】
1.% 模糊
2.占位符 _
mysql> select * from user_info where name like '%z%';
mysql> select * from user_info where name like "_s%";
insert into user_info (name) VALUES('zihsssg04');
insert into user_info (name) VALUES('zshsssg04');
insert into user_info (name) VALUES('zshsssg04');
insert into user_info (name) VALUES('zshsssg04');
需求:
- name 字母开头是y
select * from user_info where name like "y%";
- name 字母结尾是1
select * from user_info where name like "%1";
- name 含有字母h
select * from user_info where name like "%h%";
- name查询第3个字母是h的数据
select * from user_info where name like "__h%";
4.合并表
1.union 去重
2.union all 不去重
create table a(id int(3),name varchar(4));
create table b(id int(3),name varchar(4));
insert into a values(1,'zs');
insert into b values(1,'zs');
insert into b values(2,'ls');
- union
select * from a
union
select * from b;
- union all
select * from a
union all
select * from b;
5.null
- 数据清洗
- 过滤null
is null
is not null
select * from user_info where age is null;
- null数据进行 【数据转换】
update
- 过滤null
- 数据分析时候 :
delte 、update - 函数:
处理 null的函数:
1.coalesce()
2.ifnull
select
id
,name
,coalesce(age,0) as age_alias
,create_user
,create_time
,update_user
,update_time
from user_info
select
id
,name
,ifnull(age,0) as age_alias
,create_user
,create_time
,update_user
,update_time
from user_info
6.聚合函数: 指标
多行数据按照一定规则 进行聚合为一行
sum max min avg count
理论上:
聚合后的行数 <= 聚合前的行数
insert into user_info (name,age) values("zs",10);
insert into user_info (name,age) values("zs",11);
insert into user_info (name,age) values("zs",12);
insert into user_info (name,age) values("ls",10);
insert into user_info (name,age) values("ls",20);
insert into user_info (name,age) values("ls",30);
insert into user_info (name,age) values("ww",30);
- 聚合函数
select
sum(age) as age_sum,
max(age) as age_max
,min(age) as age_min
,avg(age) as age_avg
,count(age) as cnt
from user_info
- 分组语法
词频统计:
wordcount
x
y
y
z
word,1
=> 对每个单词进行分组 分组
x,<1>
y,<1,1>
z,<1>
=> 聚合 sum
x,1
y,1+1 =2
z,1
group by column...
需求:
按照name进行分组,求每组的平均年龄
分组聚合
select
name,
avg(age) as age_avg
from user_info
group by name
需求:
user_info 各个name的最大年龄、最小年龄、以及人数?
select
name,
max(age) as age_max,
min(age) as age_min,
count(age) as cnt
from user_info
group by name
分组聚合:注意:
1.select 字段 和 group by 字段 要对应 【非聚合函数字段】
select
name,
id,
max(age) as age_max,
min(age) as age_min,
count(age) as cnt
from user_info
group by name ,id
维度不一样:
1.
维度: name
指标:最大年龄、最小年龄、以及人数
2.
维度: name、id
指标:最大年龄、最小年龄、以及人数
select
name,
max(age) as age_max,
avg(age) as age_avg,
count(age) as cnt
from user_info
group by name
求:
上面的结果 求: age_avg 大于 18岁的 信息?
1.分组聚合 + having
select
name,
max(age) as age_max,
avg(age) as age_avg,
count(age) as cnt
from user_info
group by name
having age_avg > 18
总结:
条件过滤:
1.where 写在 from 后面
2.having 写在 group by 后面
上面的结果 求: age_avg 大于 18岁的 信息?
- 子查询:
查询里面 嵌套查询
select
name,
age_max,
age_avg,
cnt
from
(
select
name,
max(age) as age_max,
avg(age) as age_avg,
count(age) as cnt
from user_info
group by name
) as res
where
age_avg >18;
7.join 多表联查
种类:
4种 广义上
内连接、左连接、右连接、全连接
内连接 **
左连接 **
全连接 *
create table a1(id int(3),name varchar(10),address varchar(20));
create table b1(id int(3),name varchar(10),age int(3));
insert into a1 values(1,'aa',"dalian");
insert into a1 values(2,'bb',"shenyang");
insert into a1 values(4,'dd',"beijing");
insert into b1 values(1,'aa',10);
insert into b1 values(2,'bb',20);
insert into b1 values(3,'cc',21);
1.内连接
join
select
*
from a1 join b1
on a1.id = b1.id
select
*
from a1 inner join b1
on a1.id = b1.id
2.左连接
left join
以左表为主 数据是全的 右表来匹配 匹配不上就是null
select
*
from a1 left join b1
on a1.id =b1.id;
3.右连接
right join
以右表为主 数据是全的 左表来匹配 匹配不上就是null
select
*
from a1 right join b1
on a1.id =b1.id;
4.全连接 -- mysql 不支持全连接
左表右表数据是全的,而且没有重复数据
full join
select
*
from a1 left join b1
on a1.id =b1.id
union
select
*
from a1 right join b1
on a1.id =b1.id
8. 查询数据条数 显示限制
limit
场景:
1.显示 避免发生 滚屏效果
小表 select * from table ; 几千条数据
table: 100M 1PB 1TB
mysql: 极限 =》 TB级别 相应速度 s级别 ms级别【添加索引】
1Tb select * from table
dba
8 jdbc
(JDBC的全称是Java数据库连接(Java Database connect),它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库,并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。)
- java
- db
- connction
maven部署:
1.下载 解压
2.配置环境变量
MAVEN_HOME=D:\sxwang\app\apache-maven-3.8.1
PATH=%MAVEN_HOME%\bin
3.cmd
1.idea
1.创建maven 项目
2.src =》 开发代码目录
3.pom.xml :
添加第三方依赖包
如何获取mysql连接?
1.url :
mysql ip
mysql db
2.mysql 用户名
3.mysql 密码
添加第三方依赖包:
mysql的驱动包:
maven进行第三方jar管理:
mysql-
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.28</version>
</dependency>
问题:
1.pom.xml:
可能正在下载第三方jar
2.更改本地maven 仓库地址
D:\sxwang\app\apache-maven-3.8.1\conf\settings.xml
作业:
1.idea创建项目 练习
2.jdbc code connction 跑通
3.mysql sql 案例 做一遍 1-9 题做一遍 【可以不做】
4.vscode 快捷键 用起来 :
1.ctrl + n => 打开一个窗口
2.shift+alt 多行编辑
3.sql 格式化 =》 shift+alt+f [得安装插件]
4.ctrl + alt+i [得安装插件]
5.搜索文本内容 crtl+f
5.idea 快捷键:
1.psvm =》 main
6.切屏 用起来
2.操作mysql:
1.crud
insert、delete、update、select
获取连接:
1.准备sql
1. insert、delete、update
2.select
2.执行sql
1. insert、delete、update
2.select
练习:
1.jdbc实现
1.插入一条数据到emp表
2.更新员工信息:JAMES =》 lebulang
3.查询结果集数据
作业:
1.如何优化 code
2.练习今天讲的jdbc 【理解着敲】
MySQL
1.ddl
-
alter:
-
db
ALTER {DATABASE | SCHEMA} [db_name]
alter database bigdata CHARACTER SET = 'utf8';
-
table
- 添加字段
alter table a1 add column column_add varchar(20) after address;
alter table a1 add column column_add01 varchar(20) first ;
- 修改字段:
1.name
2.type
alter table a1 CHANGE COLUMN column_add01 column_add01 int(3);
- 删除字段:【一般不会发生】
alter table a1 DROP column_add01;
- 添加字段
-
-
truncate => 清空表
delete
1. truncate table a1; -
create table
CREATE TABLE ... LIKE Statement
=> 只拷贝 表结构
eg:
CREATE TABLE emp_dev LIKE emp;
CREATE TABLE ... SELECT Statement
=》 比较多 临时查询的结果 临时生成一个表
ctas:
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
create table emp_dev01 as select ename,job from emp;
2.dml
-
insert
insert into table_name [(columns)]values(value_list,...)
a b 表结构是一样: 需求: 两张表 , a有数据,b没有数据 把a表数据导入b数据? insert into b (name,age) select name,age from a; emp a1 insert into a1(name,address) select ename,job from emp;