Mysql知识总结

一、数据库操纵

1.创建库

1. create database 库名 charset = 'utf8';

复制代码

2.查看当前库

1. DESC databases 库名;

复制代码

3.查看当前全部库

1. SHOW DATABASES;

复制代码

4.利用库

1. USE 库名;

复制代码

5.删除库

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

复制代码

二、数据表操纵

1.创建数据表

1. CREATE TABLE 表名
2.     (
3.         id     INT,
4.         name   VARCHAR(30),
5.         gender VARCHAR(30)
6.     );

复制代码

2.查看当前表

1. DESC 表名;

复制代码

3.查看当前全部表

1. SHOW TABLES;

复制代码

4.删除表

1. DROP TABLE 表名;

复制代码

5.新增列

1. ALTER TABLE 表名 ADD 列名 类型;

复制代码

6.修改列

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

复制代码

7.删除列

1. ALTER TABLE 表名 drop 列名;

复制代码

8.修改表名

1. ALTER TABLE 旧表名 rename to 新表名;

复制代码

三、数据操纵

1.插入数据

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

复制代码

2.修改数据

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

复制代码

3.删除一整行

1. DELETE FROM 表名 WHERE id = 5;

复制代码

4.删除一个格

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

复制代码

5.删除表中全部数据

1. DELETE FROM 表名;

复制代码

四、建表条件

1.主键

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

复制代码

2.删除主键

1. ALTER TABLE 表名 DROP PRIMARY KEY;

复制代码

3.自增

1. auto increment(放字段后)   

复制代码

4.标签

1. comment = ''(放字段后)  

复制代码

5.非空

1. not null(放字段后)  

复制代码

6.唯一束缚

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

复制代码

五、查询语句

1.语句的执行顺序

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

复制代码

2.查看表

1. select * from 表名;

复制代码

3.指定列

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

复制代码

4.条件

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

复制代码

5.模糊查询

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

复制代码

6.非空

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

复制代码

7.排序

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

复制代码

8.分组

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

复制代码

9.分页查询

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

复制代码

10.别名

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

复制代码

11.毗连查询

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

复制代码

12.子查询

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

复制代码

六、Hive搭建

1.启动hadoop

1. start-all.sh

复制代码

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

1. systemctl status mysqld

复制代码

3.启动metastore服务

1. hive --service metastore

复制代码

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

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

复制代码

5.查看运行的步调

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

复制代码

6.利用客户端毗连

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

复制代码

7.指定jdbc链接的地址

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

复制代码

8.建表

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

复制代码

9.指定分隔符

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

13.删除表

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

复制代码

14.表分类

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

复制代码

15.修改表内外部范例

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

复制代码

16.数据写入

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

复制代码

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

1. (1)创建主表
2. create table tb_user(
3.     id int,
4.     name string,
5.     age int,
6.     gender int,
7.     create_time date
8. )row format delimited fields terminated by ',';
9. (2)创建分区表
10. create table new_tb_user(
11.    id int,
12.    name string,
13.    age  int,
14.    gender int,
15.    create_time date
16. )partitioned by (分区列 int)  -- 指定分区列，分区列不能是已存在列
17. row format delimited fields terminated by ',';

复制代码

18.静态分区

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

复制代码

19.动态分区

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

复制代码

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

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

复制代码

21.动态分桶

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

复制代码

22.字符串切割

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

复制代码

23.正则表达式

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

复制代码

24.三大HS

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

复制代码

25.多看一眼就会···

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

复制代码

26.虚拟列

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

复制代码

27.快速建表

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

复制代码

28.hive查询流程

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。