MySQL

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

安装部署

• 上传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
远程登录访问：

1. 修改 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 建表规范：
    1. 表名字 一定是英文 汉语拼音 不要写中文
    2. 建表风格
       1. 主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义
    3. 字段的注释
• 业务字段
• 非业务字段:
  1. 表创建表用户 vs 更新表用户
  2. 主键

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');

需求：

1. name 字母开头是y
   select * from user_info where name like "y%";
2. name 字母结尾是1
   select * from user_info where name like "%1";
3. name 含有字母h
   select * from user_info where name like "%h%";
4. 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');

1. union

select *  from a 
union 
select *  from b;

2. union all

select *  from a 
union  all
select *  from b;

5.null

• 数据清洗
  1. 过滤null
     is null
     is not null
     select * from user_info where age is null;
  2. null数据进行 【数据转换】
     update
• 数据分析时候 ：
  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);

1. 聚合函数

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

2. 分组语法

词频统计：
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

1. alter:

   1. db
      ALTER {DATABASE | SCHEMA} [db_name]
alter database bigdata CHARACTER SET = 'utf8';

   2. table

      1. 添加字段
         alter table a1 add column column_add varchar(20) after address;
alter table a1 add column column_add01 varchar(20) first ;
      2. 修改字段：
         1.name
         2.type
         alter table a1 CHANGE COLUMN column_add01 column_add01 int(3);
      3. 删除字段：【一般不会发生】
         alter table a1 DROP column_add01;

2. truncate => 清空表
   delete
   1. truncate table a1;

3. create table

   1. CREATE TABLE ... LIKE Statement => 只拷贝 表结构
      eg:
      CREATE TABLE emp_dev LIKE emp;
   2. 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

1. 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;