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

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条件);