1，show databases;展示數據庫
2，use mysql;使用mysql數據庫
3，show tables? ?展示表格
4，desc user? ?查看表格? ? ? ?desc? formatted? psn? 查看表格詳細數據
5，mysql -uroot -p? 用密碼登錄mysql
6，creat table tbl(id int,age int);
insert into tbl values(1,1);
7,元數據信息在node1中，hive_remote,tbls 存儲表名信息 ,COLUMNS_V2存儲字段信息
8，遠程節(jié)點啟動hive,3 啟動hive --service metastore? ?4，hive
9,create/drop database? ?test;刪除預創(chuàng)建數據庫
10,建表并使輸入數據格式化
內部表
CREATE TABLE www(
id? int,
name string,
likes array<string>,
address map<string,string>
)
ROW? FORMAT? DELIMITED
FIELDS? TERMINATED? BY? ','
COLLECTION? ITEMS? TERMINATED? BY? '-'
MAP? KEYS? TERMINATED? BY? ':'
LINES? TERMINATED? BY? '\n';
FIELDS 字符間隔? ??COLLECTION? ?集合間隔，MAP 鍵值對，LINES 行間隔
外部表
CREATE EXTEPNAL TABLE www(
id? int,
name string,
likes array<string>,
address map<string,string>
)
ROW? FORMAT? DELIMITED
FIELDS? TERMINATED? BY? ','
COLLECTION? ITEMS? TERMINATED? BY? '-'
MAP? KEYS? TERMINATED? BY? ':'
LINES? TERMINATED? BY? '\n';
location? '/usr/';
內部表與外部表的區(qū)別
1，創(chuàng)建時需要指定目錄
2，刪除時內部表會將表結構和元數據一起刪除，外部表只會刪除表結構，不會刪除元數據? ??
CREATE TABLE www(
id? int,
info struct <name:string,age:int>
)
ROW? FORMAT? DELIMITED
FIELDS? TERMINATED? BY? ','
COLLECTION? ITEMS? TERMINATED? BY? '-'
MAP? KEYS? TERMINATED? BY? ':'
LINES? TERMINATED? BY? '\n';
也可通過struct方式建表
11，載入數據
LOAD DATA LOCAL INPATH '/root/data' INTO TABLE www;
LOCAL表示本地文件
不加local表示文件在hdfs上
也可以直接中hdfs -put 到數據表所在目錄
FROM psn3
INSERT OVERWRITE? TABLE? psn4
SELECT? id,names,likes
12，靜態(tài)分區(qū)
CREATE TABLE www(
id? int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int)
ROW? FORMAT? DELIMITED
FIELDS? TERMINATED? BY? ','
COLLECTION? ITEMS? TERMINATED? BY? '-'
MAP? KEYS? TERMINATED? BY? ':'
LINES? TERMINATED? BY? '\n';
將數據存入age=10的分區(qū)
LOAD DATA LOCAL INPATH '/root/data' INTO TABLE www partition (age=10);
添加分區(qū)
CREATE TABLE www(
id? int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int，sex string)
ROW? FORMAT? DELIMITED
FIELDS? TERMINATED? BY? ','
COLLECTION? ITEMS? TERMINATED? BY? '-'
MAP? KEYS? TERMINATED? BY? ':'
LINES? TERMINATED? BY? '\n';
LOAD DATA LOCAL INPATH '/root/data' INTO TABLE www partition (age=10,sex='boy');
alter table www? add? partition(age=10,sex='man');
在age=10下添加分區(qū)sex=man
刪除分區(qū)
alter table? ?www? ?drop? ?partition(sex='man')
結論：添加分區(qū)的時候，必須在現有分區(qū)的基礎上
刪除分區(qū)的時候，會將所有存在的分區(qū)都刪除
13? ??beeline
3節(jié)點? ? hiveserver2
4節(jié)點? ? ?beeline -u jdbc:hive2://xutianyu3:10000/default -n root
退出? ? ?！quit
14，hive? 參數設置
顯示表頭? ? ? set? hive.cli.print.header=true
如果需要永久啟用，在家目錄創(chuàng)建.hiverc，將設置語句輸入進去
15，hive動態(tài)分區(qū)
動態(tài)分區(qū)無需指定目錄，靜態(tài)分區(qū)需要指定目錄
PPT
16，hive分桶
當表總bucket數為32時
TABLESAMPLE(BUCKET 3 OUT OF 8)，抽取哪些數據？
抽取4份數據，3, 11, 19, 27
PPT
17，hive視圖
PPT
創(chuàng)建視圖與普通sql一樣
查看視圖內容? select * from view
看有多少視圖? ? show tables
18，hive 索引
優(yōu)化查詢及檢索性能（加快查詢速度）
19，hive 腳本運行方式
PPT
14，復制表結構
create? ?psn5? ?like? psn4
15，模糊匹配
SELECT * FROM test1 WHERE renkou LIKE '47%'
%表示后面任意字符均可
SELECT * FROM test1 WHERE difang LIKE '_安%'
_表示任意一個字符
16，別名
SELECT difang as 地方 FROM test1
16，范圍查詢
SELECT * FROM test1 WHERE id in (1,2)
SELECT * FROM test1 WHERE id BETWEEN 1 and 3? ? （1到3）
17，判空 is null? ?非空? is not null
18，統(tǒng)計數目，求和，平均，最大值
SELECT COUNT(*) FROM test1
SELECT sum(renkou) as 總分 FROM test1
SELECT avg(renkou) as 平均分 FROM test1
SELECT difang,max(renkou)? FROM test1
select name,english,chinese,math,name+english+chinese as '總分'? from? test1
19，分組
SELECT leixing,count(difang) FROM test1 GROUP BY leixing HAVING count(difang) > 100
having后面的條件運算符與where的相同，having只能用于group by ,group by必須在where后
對比where與having
?where是對from后面指定的表進行數據篩選，屬于對原始數據的篩選
?having是對group by的結果進行篩選
20，排序
select * from test1 order by math,chinese?
如果是order by 后面兩個，則先比較第一個，再比較第二個?
SELECT * FROM test1 ORDER BY renkou
SELECT * FROM test1 ORDER BY renkou desc
21，分頁
SELECT * FROM test1 LIMIT 0,2
0代表第一行，2代表顯示2條
22，一對多與多對多
都要使用外鍵，多對多需要第三張表做關聯，記錄兩個表的id的關系
23，聯合查詢
select a.id,a.name,b.name from order1 a,customer b where a.cid = b.id
cid 為外鍵，查詢出a表中的訂單編號屬于b表中哪些客戶的
24，連接查詢
小表放join左邊
select a.id,a.name,b.name from student a left join class b on a.cid=b.id
left 表示左外連接，指顯示student所有的數據，指沒有匹配上一樣要顯示出來
25，視圖
對于復雜的查詢，在多次使用后，維護是一件非常麻煩的事情
?解決：定義視圖
?視圖本質就是對查詢的一個封裝
?定義視圖
create view v_sc as
SELECT * FROM test LIMIT 0,2
SELECT * FROM v_sc????
26，函數與事務
27，存儲過程，速度比普通的sql快，語句與sql一樣
調用 call 接存儲過程名(參數)
