【小贪】数据库常用操作：MySQL, HQL, Spark SQL

时间：2024-04-12 16:41:11

近期致力于总结科研或者工作中用到的主要技术栈，从技术原理到常用语法，这次查缺补漏当作我的小百科。主要技术包括：

  • 数据库常用：MySQL, Hive SQL, Spark SQL
  • 大数据处理常用：Pyspark, Pandas
  • 图像处理常用：OpenCV, matplotlib
  • 机器学习常用：SciPy, Sklearn
  • 深度学习常用：Pytorch, numpy
  • 常用数据结构语法糖：itertools, collections
  • 常用命令: Shell, Git, Vim

以下整理错误或者缺少的部分欢迎指正！！！

数据库常用：MySQL, HQL, Spark SQL

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

性能对比

MySQL Hive SQL Spark SQL
数据存储系统 关系型数据库 基于Hadoop的数据仓库系统 在Spark上构建的分布式计算引擎
数据处理能力 小规模结构化数据 大规模非结构化和半结构化数据 大规模非结构化和半结构化数据
执行引擎 传统查询优化器和Executor 将查询转换为MapReduce Spark引擎
分布式存储和处理 × ✓ ✓
实时查询 ✓ ×（只能离线分析） ✓
可扩展性 低 高 高
优势 管理索引、表分区 表分区、分桶、UDF/UDAF 提供DF接口

更详细的介绍：

  • Hive & Hbase
  • Spark SQL & Spark streaming
  • 数据库分类及数据库笔记目录

常用语法

MySQL Hive SQL Spark SQL
关系 适用大部分MySQL语法 兼容hive
DDL（定义） CREATE DROP ALTER ✓ ✓
DML（操作） INSERT DELETE UPDATE ✓ ✓
DQL（查询） SELECT WHERE ✓ ✓
DCL（控制） GRANT REVOKE ✓ ✓
函数 nvl(expr1, expr2) ifnull(expr1, expr2) 同hive
merge into 只能使用update/delete/insert代替 ✓ ✓
join on关联条件支持不等值连接 × × ✓

MYSQL 常用

CREATE/DROP DATABASE/TABLE IF NOT EXISTS db/tab; -- 创建/删除数据库/表
USE db; -- 使用指定数据库
SET SQL_SAFE_UPDATES = 0; -- 关闭安全模式
SHOW DATABASES/TABLES; -- 查询所有数据库/表名;
desc 表名; -- 查询表结构

CREATE TABLE student(  -- 列名 数据类型（包括int、double、varchar、date、datetime、timestamp）
	id INT primary key auto_increment,        
    NAME VARCHAR(20) unique,  
    age INT not NULL,
    adress VARCHAR(10) constraint 外键名 foreign key (外键列名) reference 主表名(主表列名),
);
INSERT INTO student VALUES('1','马云','55','男', '杭州', '66', '78'),
						  ('2', '柳青', '20', '女', '湖南', '86', NULL);

ALTER TABLE 表名 rename 新表名；
ALTER TABLE 表名 ADD/MODIFY 列名 数据类型； -- 新增/修改列名、类型
ALTER TABLE 表名 CHANGE 列名 新列别 新数据类型；
ALTER TABLE 表名 DROP 列名;
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES department 主表名(主表列名); -- 创建完表添加外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名; -- 删除外键

DELETE FROM student WHERE id = 9;
UPDATE student SET age = 99 WHERE id = 9;
DROP TABLE 表名；

SELECT
    (DISTINCT) 字段列表 (as 别名)
FROM 
    表名列表
WHERE 
    条件列表       -- where在分组前限定，后不可跟聚合函数
group by 
    分组字段
having
    分组之后的条件  -- having在分组后限定，后可进行聚合函数的判断
order by 
    排序字段 ASC/DESC -- (ASC默认升序，DESC降序)
limit
    分页限制       -- 开始索引，每页查询条数

-- as起别名; 计算math和english分数之和
SELECT math, english, math + IFNULL(english, 0) [AS] 总分 FROM student; -- 如果有null参与运算，计算结果都为null

-- 查询姓名第二个字是化的；通配符：%任意多个字符，_单个字符
SELECT * FROM student WHERE NAME LIKE '_化%';

-- 聚合函数；按性别分组分别查询男女生的平均分、人数，要求数学分数高于70分，分组后人数要大于两个 
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- 笛卡尔积
SELECT * FROM dept, emp;

-- 内连接
SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;  -- 隐式内连接
SELECT * FROM emp [INNER] JOIN dept ON emp.`dept_id` = dept.`id`;

-- 外连接
SELECT t1.*,t2.name FROM emp t1 LEFT [outer] JOIN dept t2 ON t1.`dept_id` = t2.`id`; -- 左外连接：左表所有数据及交集部分
SELECT t1.*,t2.name FROM dept t2 RIGHT [outer] JOIN emp t1 ON t1.`dept_id` = t2.`id`; -- 右外连接：右表所有数据及交集部分

/* 子查询 */
-- 子查询的结果是单行单列的，子查询可作为条件，使用运算符判断
SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(salary) FROM emp);
-- 子查询的结果是多行单列的，子查询可作为条件，使用运算符in判断
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '市场部' OR NAME = '财务部');
-- 子查询的结果是多行多列的，子查询可作为一张虚拟表参与查询
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.`id` = t2.dept_id;

-- 常用函数 
select if(boolean testCondition, T valueTrue, T valueFalseOrNull) from user;
case when 条件 then 值
...
when 条件 then 值 else 0 end 字段名; 
CONCAT(str1,str2,…)  -- 连接字符串
CONCAT_WS(separator,str1,str2,...) -- 以分隔符连接字符串
GROUP_CONCAT( [distinct] 要连接的字段 [group by 字段] [separator '分隔符']) -- 分组连接字段

-- with as；相当于创建e、d临时表
with
    e as (select * from scott.emp),
    d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

-- union all多个select不去重不排序；union [distinct] 去重且排序
select id,score from union_test1 union [distinct] select id, score from union_test2;

-- json data
jsonData = '{
	"message":{
		"location":[{"county":"浦东","city":"上海"},
					{"county":"西直门","city":"北京"}]
	}   }' --jsonData列数据为字符串格式，外面是单引号，里面是双引号
select get_json_object(jsonData,'$.message.location[0].city') from test --输出：上海

hive常用命令

hive # hive启动！
hive -e "sql语句"	# 不进入hive交互窗口执行sql语句
hive -f xxx.sql		# 不进入hive交互窗口执行脚本中的sql语句
hive>exit/quit; 	# 退出hive窗口
hive>dfs -ls /; 	# 查看hdfs文件系统
hive>! ls /opt/module/datas; # 查看本地文件系统
cat .hivehistory	         # 查看在hive中输入的所有历史命令
desc formatted table_name;   # 查询某个表的HDFS地址
dfs -ls address_url;	     # 进入该地址中可以看到相应分区的最后更新时间

分区表常用

-- 创建分区表常见写法
create table if not exists student2(
    id int, name string
)
[COMMENT table_comment] 
partitioned by (month string) -- [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  -- 分区表
[CLUSTERED BY (col_name, col_name, ...)  -- 分桶表
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
row format delimited fields terminated by '\t' --[ROW FORMAT row_format] 
stored as textfile  -- [STORED AS file_format] 
location '/user/hive/warehouse/student2'; -- [LOCATION hdfs_path]

-- 同时创建/删除多个分区
alter table dept_partition add/drop partition(month='201705'), partition(month='201704');

-- 查看分区表有多少分区
show partitions dept_partition;
-- 查看分区表结构
desc formatted dept_partition;

-- 从本地文件覆盖/追加表数据
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table 表名 [partition (partcol1=val1,…)];

高级操作merge into

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE   -- DELETE
    SET col1 = col1_val1, 
        col2 = col2_val2
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

相关文章