Mysql知识总结

一、数据库操作

1.创建库

create database 库名 charset = 'utf8';

2.查看当前库

DESC databases 库名;

3.查看当前所有库

SHOW DATABASES;

4.使用库

USE 库名;

5.删除库

DROP DATABASE 库名 [cascade];  -- 加中括号内容表示强制删除

二、数据表操作

1.创建数据表

CREATE TABLE 表名
    (
        id     INT,
        name   VARCHAR(30),
        gender VARCHAR(30)
    );

2.查看当前表

DESC 表名;

3.查看当前所有表

SHOW TABLES;

4.删除表

DROP TABLE 表名;

5.新增列

ALTER TABLE 表名 ADD 列名 类型;

6.修改列

ALTER TABLE 表名 change 旧列名 新列名 类型;   

7.删除列

ALTER TABLE 表名 drop 列名; 

8.修改表名

ALTER TABLE 旧表名 rename to 新表名;

三、数据操作

1.插入数据

INSERT INTO 表名 values (1, '小明', '男');

2.修改数据

UPDATE 表名 SET 字段名 = '女' WHERE id = 4;

3.删除一整行

DELETE FROM 表名 WHERE id = 5;

4.删除一个格

UPDATE 表名 SET 字段名 = NULL WHERE id = 6;

5.删除表中所有数据

DELETE FROM 表名;

四、建表条件

1.主键

primary key(既不重复，也不为空，放字段后)

2.删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

3.自增

auto increment(放字段后)    

4.标签

comment = ''(放字段后)  

5.非空

not null(放字段后)  

6.唯一约束

unique(该字段不能重复，放字段后)

五、查询语句

1.语句的执行顺序

目标列(第七)：SELECT 列1,列2..... 
        来自表名(第一)：from 表1 
        表连接(第二)：(LEFT/RIGHT) JOIN 表2 ON 表1.字段1 = 表2.字段2
        外部条件(第三)：WHERE   筛选条件
        分组(第四)：GROUP BY 分组字段
        内部条件(第五)：HAVING  分组后筛选条件
        排序(第六)：ORDER BY 排序
        选取(第八)：LIMIT 起始索引, 条目数;

2.查看表

select * from 表名;

3.指定列

SELECT 列名1, 列名2 FROM 表名;    (列可常数，可计算)

4.条件

SELECT * FROM 表名 WHERE 条件;   (也可 between and 或 in 范围)

5.模糊查询

SELECT * FROM 表名 WHERE name LIKE '李%';

6.非空

······ WHERE 列名 IS NOT NULL;

7.排序

一级：select 列 from 表 [where] order by 条件(1) (Asc升/Desc降)；
二级：select 列 from 表 [where] order by 条件(1) (升/降), 条件(2) (升/降)；

8.分组

select 列，[聚合结果] from 表名 group by 分组条件列；
分组后查询列只能是分组后所对应的字段，不一定为全部字段，强行使用可能会报错(详见第七题第4小题处)

9.分页查询

格式：select 列名 from 表名 limit m,n;

10.别名

表别名：在from 后的表名后添加 as 别名     表名 as 别名
字段别名：在select 之后的字段名添加 as    列名 as 别名

11.连接查询

(1)内：select 字段列表 from 左表 inner join 右表 on 连接规则 (and列指定);
(2)左：select 列名 from 左表 left outer join 右表 on 连接规则 (and列指定);
(3)右：select 列名 from 左表 right outer join 右表 on 连接规则 (and列指定);

12.子查询

select 列 from 表 (select........)  
可作为表或条件，子查询作为表时括号后必加别名！！！

六、Hive搭建

1.启动hadoop

start-all.sh

2.检查mysql服务是否可以使用

systemctl status mysqld

3.启动metastore服务

hive --service metastore

4.实际开发中让服务后台运行(显示进程号)

(1)nohup hive --service metastore &(本地)
(2)nohup hive --service hiveserver2 &(远程)

5.查看运行的程序

(1)jps(本地：有RunJar即可)
(2)lsof -i:10000(远程：有端口号服务即可)

6.使用客户端连接

(1)hive(一代)
(2)beeline(二代)      

7.指定jdbc链接的地址

!connect jdbc:hive2://node1:10000
用户名：root
密码不用谢

8.建表

(1)内部表：正常建    -- 删除时会全删          
(2)外部表：create external···   -- 删除时只删元数据

9.指定分隔符

row format delimited fields terminated by '分割符';

10.表的修改(添加字段)

alter table 表名  add columns(字段名 字段类型);

11.表的修改(修改字段)

alter table 表名 change 旧字段名  新字段 字段类型;

12.表的修改(修改表属性)

(1)alter table 表名  set  属性设置
(2)alter table tb_ddl2 set tblproperties('age'='20');

13.删除表

(1)drop table 表名;    -- 直接删除表
(2)truncate table 表名;    -- 清空表的元数据

14.表分类

(1)内部表 Managed Tabel
(2)外部表 External Tables
区别：
内部表会把表的所有数据删除(元数据和行数据)
外部表会把表的元数据删除，保留hdfs上的文件数据

15.修改表内外部类型

(1)alter table 表名 set tblproperties('EXTERNAL'='TRUE'); -- 设置为外部表
(2)alter table 表名 set tblproperties('EXTERNAL'='FALSE');-- 设置为内部表
(3)desc formatted 表名; -- 验证一下

16.数据写入

(1)INSERT OVERWRITE TABLE 表名 values···  覆盖写入(后可加查询语句) 
(2)INSERT INTO TABLE 表名 values···  追加写入(后可加查询语句)

17.数据分区(分为多个目录)

(1)创建主表
create table tb_user(
    id int,
    name string,
    age int,
    gender int,
    create_time date
)row format delimited fields terminated by ',';
(2)创建分区表
create table new_tb_user(
    id int,
    name string,
    age  int,
    gender int,
    create_time date
)partitioned by (分区列 int)  -- 指定分区列，分区列不能是已存在列
row format delimited fields terminated by ',';

18.静态分区

需先创建分区表，再添加分区内容
insert into new_tb_user partition(gender=0) values(1,'张',20,'2024-10-10 14:21:21');

19.动态分区

需先创建分区表，再添加分区内容
还要进行设置开启
(1)set hive.exec.dynamic.partition.mode=nonstrict;
动态分区数据写入，可以根据select中指定的字段数据最为分区的依据
(2)insert into 分区表名 partition(分区列) select id,name, age,create_time,gender from 主表名;

20.数据分桶(分为多个文件)

(1)创建主表见17题···
(2)创建分桶表
create table new_tb_buckets(
    id int,
    name string,
    age  int,
    gender int,
    create_time date
)clustered by (gender)   -- 拆分字段
    into 2 buckets  -- 拆分数量
row format delimited fields terminated by ',';     

21.动态分桶

原理：hash(字段)%分桶数
insert into new_tb_buckets select * from tb_user limit 100;   -- 先分前100个

22.字符串切割

-- 字符串切割转化为数组数据
(1)split(字段名,'-')[0]  -- 让该列以'-'切割，再取第一个
(2)split(split(字段名,'-')[0],":")  -- 层层切割

23.正则表达式

rlike  (\d:数字开头  \w:字母开头  \s:匹配空白  \S:匹配非空白)
···where email rlike '^\d';  -- 表示以数字开头

24.三大HS

(1)Udf函数：计算前后数据量不变
(2)Udaf函数：聚合，多行变少行
(3)Udtf函数：爆炸，可以将数组中数据一行拆分为多行(见25题所示)

25.多看一眼就会···

(1)select 其他列,split(分割列,'分隔符') as 别名 from 表名;  -- 分割
(2)select explode(split(分割列,'分隔符'))as 别名 from 表名;  -- 爆炸
(3)select 其他列,新别名 from 表名 lateral view explode(split(分割列,'分隔符')) tb1 as 新别名;  -- 全新展示

26.虚拟列

(1)INPUT_FILE_NAME，-- 显示数据行所在的具体文件
(2)BLOCK OFFSET INSIDE FILE，-- 显示数据行所在文件的偏移量
(3)ROW_OFFSET_INSIDE_BLOCK，-- 显示数据所在HDFS块的偏移量
-- 此虚拟列需要设置:SET hive.exec.rowoffset=true 才可使用

27.快速建表

(1)creste table 新表 like 老表;  -- 只复制元数据，没有行数据
(2)as语法：creste table 新表名 as select * from 老表;  -- 所有数据都复制

28.hive查询流程

-- 1、物理创建txt文件并写入内容
-- 2、启动hadoop并远程连接客户端
-- 3、虚拟机创建目标目录
-- 4、开网页找到该目录并托入文件
-- 5、创建表连接到目标目录
create table abc.8o
(
    abc2 string
) location '/home/hadoop/worddata';
-- 6、对该表进行操作查询，explode爆炸函数对分割后的数组逐行展示
select explode(split(abc2,' ')) from abc.8o;