08-SQL語(yǔ)法二

1.rz 彈出對(duì)話框 CRT軟件才有的

2.window--》linux

? winscp 綠色

3. scp

將文件/文件夾從A機(jī)器傳到B機(jī)器

hadoop000:

scp jepson.log? ? ? 192.168.137.141:/tmp

scp xxx.log? ? root@hadoop001:/tmp

scp -r /ruozedata root@hadoop001:/tmp

升級(jí)版:

scp root@hadoop001:/tmp/jepson.log? /tmp

4.ssh? ?

ssh hadoop001? ? ? ? ? ? ? ? ? 登錄其他機(jī)器

ssh root@hadoop001? ? ? ? ? ? 登錄其他機(jī)器

ssh hadoop001? date? ? ? 將命令在目標(biāo)機(jī)器執(zhí)行侣集，結(jié)果返回

5.ssh-keygen 做多臺(tái)機(jī)器間? 互相信任?

http://blog.itpub.net/30089851/viewspace-1992210/

文件夾: ~/.ssh

生成:

rm -rf ~/.ssh

[root@hadoop000 ~]# ssh-keygen

[root@hadoop001 ~]# ssh-keygen

選擇第一臺(tái)作為先完善的機(jī)器

[root@hadoop000 .ssh]# cat id_rsa.pub >> authorized_keys

其他機(jī)器將id_rsa.pub發(fā)送給第一臺(tái)

[root@hadoop001 .ssh]# scp id_rsa.pub? 192.168.137.251:/root/.ssh/id_rsa.pub.hadoop001

[root@hadoop002 .ssh]# scp id_rsa.pub? 192.168.137.251:/root/.ssh/id_rsa.pub.hadoop001

[root@hadoop003 .ssh]# scp id_rsa.pub? 192.168.137.251:/root/.ssh/id_rsa.pub.hadoop001

[root@hadoop004 .ssh]# scp id_rsa.pub? 192.168.137.251:/root/.ssh/id_rsa.pub.hadoop001

將其他機(jī)器的id_rsa.pub追加到authorized_keys

[root@hadoop000 .ssh]# cat id_rsa.pub.hadoop001 >> authorized_keys

[root@hadoop000 .ssh]# cat id_rsa.pub.hadoop002 >> authorized_keys

[root@hadoop000 .ssh]# cat id_rsa.pub.hadoop003 >> authorized_keys

[root@hadoop000 .ssh]# cat id_rsa.pub.hadoop004 >> authorized_keys

然后將該authorized_keys分發(fā)

[root@hadoop000 .ssh]# scp authorized_keys 192.168.137.141:/root/.ssh/

[root@hadoop000 .ssh]# scp authorized_keys 192.168.137.142:/root/.ssh/

[root@hadoop000 .ssh]# scp authorized_keys 192.168.137.143:/root/.ssh/

[root@hadoop000 .ssh]# scp authorized_keys 192.168.137.144:/root/.ssh/

每臺(tái)機(jī)器第一次要做: yes --> known_hosts

[root@hadoop000 .ssh]# ssh hadoop000 date

[root@hadoop000 .ssh]# ssh hadoop001 date

[root@hadoop000 .ssh]# ssh hadoop002 date

[root@hadoop000 .ssh]# ssh hadoop003 date

[root@hadoop000 .ssh]# ssh hadoop004 date

升級(jí)版(作業(yè)): A機(jī)器scp一個(gè)文件到B機(jī)器無(wú)需密碼

A --> B

---------------------------------------------------------------

SQL常規(guī)使用二:

1.復(fù)習(xí)

? 字段類(lèi)型

? create table? 模板

? insert

? update

? delete

? select

? where

2.見(jiàn)sql語(yǔ)法.sql

--表 table

create table ruozedata(

id int AUTO_INCREMENT primary key,

name varchar(100),

age int,

createtime timestamp DEFAULT CURRENT_TIMESTAMP,

creuser varchar(100),

updatetime timestamp? DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

updateuser varchar(100)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

drop table ruozedata;

insert into? ruozedata(id,name,age) values(1,'jepson',16);

insert into? ruozedata(id,name,age) values(2,'LY',18);

insert into? ruozedata(id,name,age) values(3,'ZX',19);

insert into? ruozedata(id,name,age) values(4,'ZX1',119);

insert into? ruozedata(name,age) values('jepson',16);

insert into? ruozedata(name,age) values('LY',18);

insert into? ruozedata(name,age) values('ZX',19);

insert into? ruozedata(name,age) values('ZX1',119);

insert into? ruozedata(name,age) values('甘偉',119);

insert into? ruozedata

values(5,'ZX1',119,'2017-10-10 00:00:00','xxx','2017-12-10 00:00:00','xxx1');

update ruozedata set age=22 where name='jepson';

update ruozedata set age=22 ;

delete from ruozedata where name='jepson';

select * from ruozedata where name='ly';

select id,name from ruozedata;

create table test select id,name from ruozedata;

create table test1 select id,name from ruozedata where 1<>1;

alter table ruozedata add address varchar(512);

alter table ruozedata drop address ;

alter table ruozedata add address varchar(512) after age;

#條件查詢(xún)

select * from? ruozedata;

select * from? ruozedata where name='jepson';

select * from? ruozedata where name='jepson' and id=999;?

select * from? ruozedata where name='jepson' or id=3;

select * from? ruozedata where name='jepson' and id=999 and (name='jepson' or id=3);

#數(shù)據(jù)倉(cāng)庫(kù) 事實(shí)表維表

create table emp (

? ? empno numeric(4) not null,

? ? ename varchar(10),

? ? job varchar(9),

? ? mgr numeric(4),

? ? hiredate datetime,

? ? sal numeric(7, 2),

? ? comm numeric(7, 2),

? ? deptno numeric(2)

);

create table dept (

? ? deptno numeric(2),

? ? dname varchar(14),

? ? loc varchar(13)

);

create table salgrade (

? ? grade numeric,

? ? losal numeric,

? ? hisal numeric

);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');

insert into dept values (20, 'RESEARCH', 'DALLAS');

insert into dept values (30, 'SALES', 'CHICAGO');

insert into dept values (40, 'OPERATIONS', 'BOSTON');

insert into salgrade values (1, 700, 1200);

insert into salgrade values (2, 1201, 1400);

insert into salgrade values (3, 1401, 2000);

insert into salgrade values (4, 2001, 3000);

insert into salgrade values (5, 3001, 9999);

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);

insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);

insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

select * from emp where sal>3000;

select * from emp where sal<>5000;

#模糊查詢(xún) like

select * from emp where ename like '%S%';

select * from emp where ename like 'S%';

select * from emp where ename like '%S';

select * from emp where ename like '_O%';?

#排序

select * from emp order by? sal ;

select * from emp order by? sal asc ;

select * from emp order by? sal desc ;

#限制多少行

select * from emp limit? 3;

select * from emp order by? sal desc limit? 3;

#聚合

#count() sum()

#1.各個(gè)部門(mén)的薪水和

select deptno,sum(sal) from? emp group by deptno

select deptno,count(ename)

from? emp group by deptno

#2.group by字段必須出現(xiàn)在select字段后面? 各個(gè)部門(mén)的各個(gè)崗位的薪水和

select deptno,job,? sum(sal) from? emp group by deptno ,job

#3.having? ? 薪水和>5000的各個(gè)部門(mén)的各個(gè)崗位

select deptno,job,? sum(sal)

from? emp

group by deptno ,job

having? sum(sal)>5000

#4.常用組合where? order? limit

select deptno,job,? sum(sal) as sum_sal

from? emp

where job='SALESMAN'

group by deptno ,job

having? sum(sal)>5000

order by sum(sal) desc

limit 1

# as 別名

join 字段键俱、表名加

not join 字段加

#------------------join------------------------------

#left join,rigth join,inner join,join

#left join

create table testa(aid int,aname varchar(40));

create table testb(bid int,bname varchar(40),age int);

insert into testa values(1,'xiaoming');

insert into testa values(2,'LY');

insert into testa values(3,'KUN');

insert into testa values(4,'ZIDONG');

insert into testa values(5,'HB');

insert into testb values(1,'xiaoming',10);

insert into testb values(2,'LY',100);

insert into testb values(3,'KUN',200);

insert into testb values(4,'ZIDONG',110);

insert into testb values(6,'niu',120);

insert into testb values(7,'meng',130);

insert into testb values(8,'mi',170);

# left join

select

a.aid,a.aname,

b.bid,b.bname,b.age

from testa as a

left join testb as b on a.aid=b.bid

left join testc as c on b.aid=c.bid

A <---B

aid aname ? bid ? bname age

1 xiaoming 1 xiaoming 10

2 LY ? ? ? ? 2 LY ? ? ? ? 100

3 KUN ? ? ? ? 3 KUN ? ? ? ? 200

4 ZIDONG ? ? 4 ZIDONG ? ? 110

5 HB

# a left join b? a全，b表去匹配a表世分，匹配不到的 null

# right join

select

a.aid,a.aname,

b.bid,b.bname,b.age

from testa as a

right join testb as b on a.aid=b.bid

1 xiaoming 1 xiaoming 10

2 LY ? ? ? ? 2 LY ? ? ? ? 100

3 KUN ? ? ? ? 3 KUN ? ? ? ? 200

4 ZIDONG ? ? 4 ZIDONG ? ? 110

? ? ? ? 6 niu ? ? ? ? 120

? ? ? ? 7 meng ? ? 130

? ? ? ? 8 mi ? ? ? ? 170

A ----> B? ? ? ?

select

a.aid,a.aname,

b.bid,b.bname,b.age

from testa as a

inner join testb as b on a.aid=b.bid

1 xiaoming 1 xiaoming 10

2 LY ? ? ? ? 2 LY ? ? ? ? 100

3 KUN ? ? ? ? 3 KUN ? ? ? ? 200

4 ZIDONG ? ? 4 ZIDONG ? ? 110

select

a.aid,a.aname,

b.bid,b.bname,b.age

from testa as a

join testb as b on a.aid=b.bid

1 xiaoming 1 xiaoming 10

2 LY ? ? ? ? 2 LY ? ? ? ? 100

3 KUN ? ? ? ? 3 KUN ? ? ? ? 200

4 ZIDONG ? ? 4 ZIDONG ? ? 110

# testa

select '2018-05-12' as month,aid,aname from testa

union

select '2018-05-11' as month,aid,aname? from testa

2018-05 1 xiaoming

2018-05 2 LY

2018-05 3 KUN

2018-05 4 ZIDONG

2018-05 5 HB

2018-04 1 xiaoming

2018-04 2 LY

2018-04 3 KUN

2018-04 4 ZIDONG

2018-04 5 HB

create table testc(cid int,cretime timestamp);

insert into testc values(1,'2018-05-12 08:08:00');

insert into testc values(1,'2018-05-11 08:08:00');

insert into testc values(1,'2018-05-10 08:08:00');

insert into testc values(2,'2018-05-12 09:08:00');

insert into testc values(2,'2018-05-11 09:08:00');

insert into testc values(2,'2018-05-10 09:08:00');

insert into testc values(3,'2018-05-12 09:08:00');

insert into testc values(4,'2018-05-11 09:08:00');

#沒(méi)出勤人的ID和名稱(chēng)? 字表

select

a.day,a.aid,a.aname,

c.cid,c.cretime

from

(select '2018-05-12' as day,aid,aname from testa

union

select '2018-05-11' as day,aid,aname? from testa)? a

left join testc as c

on a.day=DATE_FORMAT(c.cretime, '%Y-%m-%d')

and a.aid=c.cid

where c.cid is not null;

# union? , union all

select? aid from testa

union

select? bid from testb

select? aid from testa

union all

select? bid from testb

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末编振，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子臭埋，更是在濱河造成了極大的恐慌踪央，老刑警劉巖，帶你破解...
沈念sama閱讀 223,126評(píng)論 6贊 520
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件瓢阴，死亡現(xiàn)場(chǎng)離奇詭異杯瞻，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)炫掐，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 95,421評(píng)論 3贊 400
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)魁莉，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人募胃，你說(shuō)我怎么就攤上這事旗唁。” “怎么了痹束？”我有些...
開(kāi)封第一講書(shū)人閱讀 169,941評(píng)論 0贊 366
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵检疫，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我祷嘶，道長(zhǎng)屎媳，這世上最難降的妖魔是什么？我笑而不...
開(kāi)封第一講書(shū)人閱讀 60,294評(píng)論 1贊 300
?港島之戀（遺憾婚禮）
正文為了忘掉前任论巍，我火速辦了婚禮烛谊，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘嘉汰。我一直安慰自己丹禀，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 69,295評(píng)論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布鞋怀。她就那樣靜靜地躺著双泪，像睡著了一般。火紅的嫁衣襯著肌膚如雪密似。梳的紋絲不亂的頭發(fā)上焙矛，一...
開(kāi)封第一講書(shū)人閱讀 52,874評(píng)論 1贊 314
城市分裂傳說(shuō)
那天，我揣著相機(jī)與錄音残腌，去河邊找鬼村斟。笑死剪返，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的邓梅。我是一名探鬼主播，決...
沈念sama閱讀 41,285評(píng)論 3贊 424
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼邑滨，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼日缨！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起掖看，我...
開(kāi)封第一講書(shū)人閱讀 40,249評(píng)論 0贊 277
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤匣距，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后哎壳，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體毅待，經(jīng)...
沈念sama閱讀 46,760評(píng)論 1贊 321
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 38,840評(píng)論 3贊 343
?白月光啟示錄
正文我和宋清朗相戀三年归榕，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了尸红。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 40,973評(píng)論 1贊 354
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡刹泄，死狀恐怖外里，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情特石，我是刑警寧澤盅蝗，帶...
沈念sama閱讀 36,631評(píng)論 5贊 351
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站姆蘸，受9級(jí)特大地震影響墩莫，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜逞敷，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 42,315評(píng)論 3贊 336
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一狂秦、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧推捐，春花似錦故痊、人聲如沸。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 32,797評(píng)論 0贊 25
一樁弒父案愕秫，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至焰络，卻和暖如春戴甩，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背闪彼。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 33,926評(píng)論 1贊 275
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工甜孤，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留协饲，地道東北人。一個(gè)月前我還...
沈念sama閱讀 49,431評(píng)論 3贊 379
代替公主和親
正文我出身青樓缴川，卻偏偏與公主長(zhǎng)得像茉稠，于是被迫代替她去往敵國(guó)和親。傳聞我的和親對(duì)象是個(gè)殘疾皇子把夸，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,982評(píng)論 2贊 361

08-SQL語(yǔ)法二

推薦閱讀更多精彩內(nèi)容