hbase分布式数据库--佘响平

#建库
CREATE DATAbase IF NOT EXISTS a DEFAULT CHARSET UTF8;
USE a;
CREATE TABLE IF NOT EXISTS b(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(50) NOT NULL,
url VARCHAr(50) NOT NULL,
alexs INT NOT NULL COMMENT "网站排名",
country VARCHAr(20) NOT NULL
);
INSERT INTO b(NAME,url,alexs,country)VALUES("good1","https:www.goole.com","1","usa"),
("good1","https:www.goole.com",1,"usa"),
("淘宝","https:www.taobao.com",13,"cn"),
("菜鸟","https:www.runoob.com",4689,"cn"),
("微博","https:www.weibo.com",20,"cn"),
("facebook","https:www.facebook.com",3,"usa"),
("stackoverflow","https:www.stackoverflow.com",0,"ind"),
("百度","https:www.baidu.com",4,"cn");

SELECt * FROM b;

CREATE TABLE IF NOT EXISTS c(id INT PRIMARY KEY AUTO_INCREMENT,
site_id INT NOT NULL,
COUNT INT DEFAULT 0,
DATE DATE
);
INSERT INTO c(site_id,COUNT,DATE)VALUES(1,45,"2016-05-10"),
(2,100,"2016-05-10"),
(3,415,"2016-05-10"),
(4,200,"2016-05-10"),
(5,7,"2016-05-10"),
(6,145,"2016-05-10"),
(7,45,"2016-05-10"),
(8,545,"2016-05-10"),
(9,201,"2016-05-10"),
(10,4115,"2016-05-10")
SELECt * FROM c;
# INNER 公共部分
SELECt w.*,a.* FROM b w INNER JOIN c a ON w.id=a.id;
#公共部分 LEFT 加左边
SELECt w.*,a.* FROM b w LEFT JOIN c a ON w.id=a.site_id;
#公共部分 RIGHT 加右边
SELECt w.*,a.* FROM b w RIGHT JOIN c a ON w.id=a.site_id;

# UNIOn 自动去重
SELECt w.*,a.* FROM b w LEFT JOIN c a ON w.id=a.site_id
UNIOn
SELECt w.*,a.* FROM b w RIGHT JOIN c a ON w.id=a.site_id;


SELECt * FROM b
UNIOn 
SELECt * FROM b;

SELECt * FROM b
UNIOn ALL
SELECt * FROM b;

SELECt id FROM b
UNIOn 
SELECt id FROM b;

CREATE DATAbase IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;

CREATE TABLE IF NOT EXISTS stu(NO INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(20));
INSERT INTO stu(NAME)VALUES("张"),("李"),("王"),("赵"),("田");

SELECt * FROM stu;

CREATE TABLE IF NOT EXISTS sub(subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAr(25));
INSERT INTO sub(subname)VALUES("java"),("c"),("python"),("mysql");

SELECt * FROM sub;

DROp TABLE score;

CREATE TABLE IF NOT EXISTS score( NO INT,score INT,subno INT);
INSERT INTO score(NO,score,subno)VALUES(1,89,1),(2,78,2),(3,96,3),(4,85,4);

SELECT * FROM score;

SELECt st.*,sc.* FROM stu st
LEFT JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERe sc.score > 60;

1.表关联查询（两表，多表）     内连接 inner join     左连接 left join     右连接 right join     全连接

2.联合查询union

3.嵌套子查询select * from（select * from 表名）

UNIOn 操作符用于合并两个或多个 SELECt 语句的结果集。 请注意，UNIOn 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。 SQL UNIOn 语法 SELECT  column_name(s) FROM  table1 UNIOn SELECt  column_name(s) FROM  table2; 注释：默认地，UNIOn 操作符选取不同的值。如果允许重复的值，请使用 UNIOn ALL。 SQL UNIOn ALL 语法 SELECt  column_name(s) FROM  table1 UNIOn ALL SELECt  column_name(s) FROM  table2;

MySQL Full Join的实现因为MySQL不支持FULL JOIN,下面是替代方法 left join + union(可去除重复数据)+ right join select * from A left join B on A.id = B.id (where 条件）union select * from A right join B on A.id = B.id （where条件);