Hbase------刘飞

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("张三"),("李四"),("王五"),("六留"),("七七"),("八八");
#创建科目表
CREATE TABLE IF NOT EXISTS sub(subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAR(20)
);

#插入数据
INSERT INTO sub(subname)VALUES("java"),("python"),("html"),("hadoop"),("js");

#创建中间表---分数表
CREATE TABLE IF NOT EXISTS score(NO INT,score INT,subno INT);
#插入数据
INSERT INTO score(NO,score,subno)VALUES(1,88,1),(2,90,3),(3,85,3),
(4,60,4);

#三表关联查询，第一个表关联中间表的结果，然后关联第二个表
#st学生表  sc分数表  su科目表
SELECT st.*,sc.*,su.* FROM stu st
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE st.no > 1;
#------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS abc(sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
gender VARCHAR(20),
class_jb INT(10)
);
SELECT * FROM abc;

INSERT INTO abc(sname,gender,class_jb)VALUES
("张三","男",1),
("李四","男",1),
("王五","男",1);
SELECT * FROM abc;

CREATE TABLE IF NOT EXISTS cba(cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
teacher_id INT(10)
);
SELECT * FROM cba;
INSERT INTO cba(cname,teacher_id)VALUES
("语文",1),
("数学",2),
("英语",3);

CREATE TABLE IF NOT EXISTS sss(sid INT PRIMARY KEY AUTO_INCREMENT,
student_id VARCHAR(20),
corse_id INT(10),
numder INT(100)
);
INSERT INTO sss(student_id,corse_id,numder)VALUES
(1,1,79),
(1,2,80),
(1,3,70),
(2,1,66);

SELECT st.*,sc.*,su.* FROM stu st
 
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE st.no > 1;
RIGHT
#左连接 left join...on
SELECT * FROM abc a LEFT JOIN cba c ON a.sid=c.cid;
#右连接 right join...on
SELECT * FROM abc a RIGHT JOIN cba c ON a.sid=c.cid;
###################################################################################
CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#建表
CREATE TABLE IF NOT EXISTS emp(emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
salary INT,
dept_id INT,
manager_id INT
);
INSERT INTO emp(emp_name,salary,dept_id,manager_id)VALUES("tom",15000,1,NULL),
("lucy",16000,2,NULL),
("morty",14000,5,NULL),
("张建国",4000,1,1),
("李华",6000,1,1),
("孙思妙",3000,1,1),
("王强",6000,2,2),
("周杰",5000,2,2),
("曹光思",7000,5,3),
("刘毅真",8000,5,3),
("孙岩",5000,5,3);

SELECT * FROM emp;

#删除库
DROP DATABASE info;
#删除表
DROP TABLE emp;

#子查询，单值
SELECT * FROM emp WHERE salary >(SELECT salary FROM emp WHERE emp_name = "王强");

SELECT * FROM emp WHERE dept_id (SELECT dept_id FROM emp WHERE dept_name="张建国")
AND salary > (SELECT salary FROM rmp WHERE emp_namr="孙岩");

#子查询，多值
SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE emp_name LIKE "孙%");
#子查询，中间表
SELECT * FROM (SELECT * FROM emp WHERE dept_id > 2 AND salary > 5000) e;

CREATE TABLE IF NOT EXISTS emp01(empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
aal DOUBLE,
comm DOUBLE,
deptno INT
)AUTO_INCREMENT=1000;

CREATE DATABASE emp01;

INSERT INTO emp01(ename,job,mgr,hiredate,aal,comm,deptno)VALUES
("甘宁","文员",1013,"2000-12-17",8000.00,NULL,20),
("黛琦丝","销售员",1006,"2001-02-20",16000.00,3000.00,20),
("股天正","销售员",1006,"2001-2-22",12500.00,5000.00,20),
("刘备","经理",1009,"2001-04-02",29750.00,NULL,20),
("谢逊","销售员",1006,"2001-09-28",12500.00,14000,30),
("关羽","经理",1009,"2001-05-01",28500.00,NULL,30),
("张飞","经理",1009,"2001-09-01",24500.00,NULL,10),
("诸葛亮","分析师",1004,"2007-04-19",30000.00,NULL,20),
("张无忌","董事长",NULL,"2000-11-17",50000.00,NULL,10),
("韦一笑","销售员",1006,"2001-09-08",15000.00,NULL,30),
("周泰","文员",1008,"2007-05-23",11000.00,NULL,30),
("程普","文员",1006,"2001-12-03",9500.00,NULL,30),
("庞统","分析师",1004,"2001-12-03",30000.00,NULL,20),
("黄盖","文员",1007,"2002-01-23",12000.00,NULL,10),
("张三","保洁员",1001,"2003-05-01",80000.00,50000,50);

SELECT * FROM emp01;

SELECT * FROM emp01 WHERE aal >(SELECT aal FROM emp01 WHERE ename = "关羽");

##########################################################################################
#1.1创建索引:单独创建索引
CREATE INDEX index_name ON emp(emp_name);
SELECT *FROM emp WHERE emp_name = "lucy";
#1.2创建索引:修改表结构创建索引
ALTER TABLE emp ADD INDEX index_salary(salary);
#1.3创建索引:创建表时，创建索引
CREATE TABLE IF NOT EXISTS article(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title (title)
);
DROP TABLE article;
#2.删除索引
DROP INDEX index_name ON emp;

#3.1唯一索引:列的值必须唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name);
#3.2修改表创建唯一索引?
#3.3修改表时创建唯一索引?

#6.组合索引
CREATE INDEX index_name ON emp(emp_name);
SELECT * FROM article WHERE content="123" AND dt="2020-10-10";