第一題

CREATE TABLE `table1` (`name` VARCHAR(20) DEFAULT NULL,`kecheng` VARCHAR(20) DEFAULT NULL,`fenshu` INT(10) DEFAULT NULL );INSERT INTO table1(NAME,kecheng,fenshu) VALUES('張三','語(yǔ)文',81); INSERT INTO table1(NAME,kecheng,fenshu) VALUES('張三','數(shù)學(xué)',75); INSERT INTO table1(NAME,kecheng,fenshu) VALUES('李四','語(yǔ)文',76); INSERT INTO table1(NAME,kecheng,fenshu) VALUES('李四','數(shù)學(xué)',76); INSERT INTO table1(NAME,kecheng,fenshu) VALUES('王五','語(yǔ)文',81); INSERT INTO table1(NAME,kecheng,fenshu) VALUES('王五','數(shù)學(xué)',100);

#求出每門課都大于80分的學(xué)生姓名

第一種方法:

SELECT NAME FROM table1 GROUP BY NAME HAVING MIN(fenshu)>80

第二種方法:

SELECT NAME FROM table1 GROUP BY NAME HAVING COUNT(1)=SUM(CASE WHEN fenshu>80 THEN 1 ELSE 0 END)

第三種方法:

SELECT NAME FROM table1 GROUP BY NAME HAVING NAME NOT IN (SELECT NAME FROM table1 WHERE fenshu<=80)

第二題

CREATE TABLE `table2` (`語(yǔ)文` INT(10) DEFAULT NULL,`數(shù)學(xué)` INT(10) DEFAULT NULL,`英語(yǔ)` INT(10) DEFAULT NULL ); INSERT INTO table2(語(yǔ)文,數(shù)學(xué),英語(yǔ)) VALUES(58,70,80);

有一張表,3個(gè)字段是語(yǔ)文,數(shù)學(xué),英語(yǔ), 有1條記錄分別表示語(yǔ)文68,數(shù)學(xué)70,英語(yǔ)80, 得出結(jié)果分?jǐn)?shù)變等級(jí)(>=80分是優(yōu)秀, >=60分是及格, <60是不及格)

SELECT CASE WHEN 語(yǔ)文>=80 THEN '優(yōu)秀' WHEN 語(yǔ)文>=60 THEN '及格' WHEN 語(yǔ)文<60 THEN '不及格' END 語(yǔ)文, CASE WHEN 數(shù)學(xué)>=80 THEN '優(yōu)秀' WHEN 數(shù)學(xué)>=60 THEN '及格' WHEN 數(shù)學(xué)<60 THEN '不及格' END 數(shù)學(xué), CASE WHEN 英語(yǔ)>=80 THEN '優(yōu)秀' WHEN 英語(yǔ)>=60 THEN '及格' WHEN 英語(yǔ)<60 THEN '不及格' END 英語(yǔ) FROM table2

第三題

CREATE TABLE `table3` (`date` VARCHAR(20) DEFAULT NULL,`result` VARCHAR(10) DEFAULT NULL )

#查詢出來(lái)的結(jié)果：
#date 勝 負(fù)
#2011-02-01 2 1
#2011-02-02 1 1

SELECT DATE, (SELECT COUNT(1) FROM table3 WHERE DATE=t.date AND result='勝') AS 勝, (SELECT COUNT(1) FROM table3 WHERE DATE=t.date AND result='負(fù)') AS 負(fù) FROM table3 t GROUP BY DATE

第四題

#1 2005001 張三 0001 數(shù)學(xué) 69
#2 2005002 李四 0001 數(shù)學(xué) 89
#3 2005001 張三 0001 數(shù)學(xué) 69

CREATE TABLE IF NOT EXISTS `student_test` (id INT UNSIGNED AUTO_INCREMENT,stu_id VARCHAR(10) NULL,NAME VARCHAR(10) NULL,couser_id VARCHAR(10) NULL,couser VARCHAR(10) NULL,score DECIMAL(10,2) NULL,PRIMARY KEY (id) ) INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005001','張三','0001','數(shù)學(xué)',69); INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005002','李四','0001','數(shù)學(xué)',89); INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005001','張三','0001','數(shù)學(xué)',69);

#刪除冗余字段, 保留ID最小的那個(gè)

DELETE FROM student_test WHERE id NOT IN (SELECT * FROM (SELECT MIN(id) FROM student_test GROUP BY stu_id,NAME,couser_id,couser,score) s )

第五題

CREATE TABLE IF NOT EXISTS `product` (id INT UNSIGNED AUTO_INCREMENT,NAME VARCHAR(10) NULL,STATUS VARCHAR(10) NULL,PRIMARY KEY (id) ) INSERT INTO product(id,NAME,STATUS) VALUES(NULL,'蘋果','好吃'); INSERT INTO product(id,NAME,STATUS) VALUES(NULL,'梨','難吃'); INSERT INTO product(id,NAME,STATUS) VALUES(NULL,'橘子','好吃'); INSERT INTO product(id,NAME,STATUS) VALUES(NULL,'葡萄',NULL);

#現(xiàn)在需要把status的‘好吃’更新為‘0’，‘難吃’更新為‘1’

UPDATE product SET STATUS=(CASE WHEN STATUS='好吃' THEN '0' WHEN STATUS='難吃' THEN '1' END) 1

第六題

1) 創(chuàng)建一張學(xué)生表，包含以下信息，學(xué)號(hào)，姓名，年齡，性別，家庭住址，聯(lián)系電話

CREATE TABLE IF NOT EXISTS `t_student` (stu_id INT UNSIGNED AUTO_INCREMENT,NAME VARCHAR(10) NULL,age INT NULL,gender VARCHAR(2) NULL,address VARCHAR(50) NULL,phone VARCHAR(11) NULL,PRIMARY KEY (stu_id) );

#2) 修改學(xué)生表的結(jié)構(gòu)，添加一列信息，學(xué)歷

ALTER TABLE t_student ADD COLUMN `education` VARCHAR(20) NOT NULL;

#3) 修改學(xué)生表的結(jié)構(gòu)，刪除一列信息，家庭住址

ALTER TABLE t_student DROP COLUMN address;

#4) 向?qū)W生表添加如下信息：

INSERT INTO t_student(stu_id,NAME,age,gender,phone,education) VALUES(NULL,'A',22,'男','123456','小學(xué)'); INSERT INTO t_student(stu_id,NAME,age,gender,phone,education) VALUES(NULL,'B',21,'男','119','中學(xué)'); INSERT INTO t_student(stu_id,NAME,age,gender,phone,education) VALUES(NULL,'C',23,'男','110','高中'); INSERT INTO t_student(stu_id,NAME,age,gender,phone,education) VALUES(NULL,'D',18,'女','114','大學(xué)');

#5) 修改學(xué)生表的數(shù)據(jù)，將電話號(hào)碼以11開(kāi)頭的學(xué)員的學(xué)歷改為“大?！?/strong>

UPDATE t_student SET education='大專' WHERE phone LIKE '11%';

#6) 刪除學(xué)生表的數(shù)據(jù)，姓名以C開(kāi)頭，性別為‘男’的記錄刪除

DELETE FROM t_student WHERE NAME LIKE 'C%' AND gender='男';

#7) 查詢學(xué)生表的數(shù)據(jù)，將所有年齡小于22歲的，學(xué)歷為“大?！钡?#xff0c;學(xué)生的姓名和學(xué)號(hào)示出來(lái)

SELECT stu_id,NAME FROM t_student WHERE age<22 AND education='大專'

#8) 查詢學(xué)生表的數(shù)據(jù)，查詢所有信息，列出前25%的記錄

SELECT * FROM t_student WHERE stu_id<=(SELECT COUNT(*) FROM t_student)*0.25;

#9) 查詢出所有學(xué)生的姓名，性別，年齡降序排列

SELECT NAME,gender,age FROM t_student ORDER BY age DESC;

#10) 按照性別分組查詢所有的平均年齡

SELECT gender,AVG(age) FROM t_student GROUP BY gender;

第七題

CREATE TABLE IF NOT EXISTS test1(a DECIMAL(10,2) NOT NULL,b DECIMAL(10,2) NOT NULL,c DECIMAL(10,2) NOT NULL,d VARCHAR(10) NOT NULL ) INSERT INTO test1(a,b,c,d) VALUES(0.5,1.5,2.0,'A1'); INSERT INTO test1(a,b,c,d) VALUES(1.5,0.5,0.5,'A1'); INSERT INTO test1(a,b,c,d) VALUES(0.5,0.5,1.5,'A1'); INSERT INTO test1(a,b,c,d) VALUES(1.5,1.5,1.5,'B1'); INSERT INTO test1(a,b,c,d) VALUES(0.5,2.0,2.0,'B1'); INSERT INTO test1(a,b,c,d) VALUES(1.5,2.0,0.5,'B1');

統(tǒng)計(jì)a,b,c三列大于1的個(gè)數(shù)

SELECT SUM(CASE WHEN a > 1 THEN 1 ELSE 0 END) a, SUM(CASE WHEN b > 1 THEN 1 ELSE 0 END) b, SUM(CASE WHEN c > 1 THEN 1 ELSE 0 END) c FROM test1;

按a分組, 用b排序取top1
其實(shí)用hive或者Oracle很簡(jiǎn)單了, 直接用row_number即可
方法一

SELECT test1.* FROM test1 JOIN (SELECT a,MAX(b) maxb FROM test1 GROUP BY a) t ON test1.b=t.maxb GROUP BY a

方法二

SELECT a, SUBSTRING_INDEX(GROUP_CONCAT(b ORDER BY b DESC),',',1) b, SUBSTRING_INDEX(GROUP_CONCAT(c ORDER BY b DESC),',',1) c, SUBSTRING_INDEX(GROUP_CONCAT(d ORDER BY b DESC),',',1) d FROM test1 GROUP BY a;

用到了下面的兩個(gè)函數(shù):

group_concat(v)

先看看它的語(yǔ)法:

group_concat([DISTINCT] 要連接的字段 [ORDER BY ASC/DESC 排序字段] [SEPARATOR
‘分隔符’])

舉例:

select GROUP_CONCAT(c ORDER BY b DESC) from test1 group a #解析: 按a進(jìn)行分組, 把c字段的值打印在一起, 逗號(hào)分割(默認(rèn)), 并且c字段按b排序

substring_index(v1, v2, v3) – 將v1按照v2進(jìn)行分割取下標(biāo)為v3的值

從表中隨機(jī)取出3條數(shù)據(jù)
下面這種方法雖然可以取出隨機(jī)記錄, 但是不推薦, 數(shù)據(jù)量大的話會(huì)很慢很慢

SELECT * FROM test1 ORDER BY RAND() LIMIT 3

可以使用下面這種方法

SELECT * FROM (SELECT test1.*, RAND() r FROM test1) t ORDER BY t.r LIMIT 3

第八題
Table表有三個(gè)字段ID,Name,Location, 請(qǐng)以兩種方式寫出SQL語(yǔ)句刪除表中ID以及Name都重復(fù)的記錄.

CREATE TABLE table4(ID INT NOT NULL,NAME VARCHAR(10) NOT NULL,Location VARCHAR(12) NOT NULL );INSERT INTO table4(ID,NAME,Location) VALUES(1,'zhangsan','bj'); INSERT INTO table4(ID,NAME,Location) VALUES(1,'zhangsan','bj'); INSERT INTO table4(ID,NAME,Location) VALUES(2,'lisi','bj'); INSERT INTO table4(ID,NAME,Location) VALUES(3,'wangwu','sh');

第一種方法

DELETE FROM table4 WHERE (id,NAME) IN ( SELECT * FROM (SELECT id,NAME FROM table4 GROUP BY ID,NAME HAVING COUNT(1)>1) t)

第二種方法

DELETE FROM table4 WHERE (id,NAME) IN ( SELECT * FROM( SELECT id,NAME FROM table4 t1 WHERE ((SELECT COUNT(1) FROM table4 t2 WHERE t1.ID=t2.ID AND t1.Name=t2.Name)>1 ) ) tmp)

第九題

現(xiàn)有BOOKS表, 查詢所有圖書中價(jià)格小于60元的作者有多少本圖書在售

CREATE TABLE books(bno INT NOT NULL,bname VARCHAR(10) NOT NULL,author VARCHAR(10) NOT NULL,price DECIMAL(10,2) NOT NULL ) INSERT INTO books VALUES(1,'倚天屠龍記','金庸',12.99); INSERT INTO books VALUES(2,'雪山飛狐','金庸',64.99); INSERT INTO books VALUES(3,'東方不敗','金庸',12.99); INSERT INTO books VALUES(4,'仙逆','耳根',72.99); INSERT INTO books VALUES(5,'求魔','耳根',12.99);

答案:
使用子查詢查出圖書價(jià)格小于60的作者有哪些, 然后按照作者分組, 查詢這些作者的圖書數(shù)量.

SELECT author,COUNT(1) FROM books GROUP BY author HAVING author IN (SELECT DISTINCT(author) FROM books WHERE price<60)

第十題

表中有A,B,C三列, 用SQL語(yǔ)句實(shí)現(xiàn), 依次取A,B,C中第一個(gè)不為空的值

CREATE TABLE table_null(A VARCHAR(10),B VARCHAR(10),C VARCHAR(10) )INSERT INTO table_null VALUES(NULL,NULL,NULL); INSERT INTO table_null VALUES('abc',NULL,NULL); INSERT INTO table_null VALUES(NULL,'ccc',NULL); INSERT INTO table_null VALUES(NULL,NULL,'ddd');

答案:
分別按照每一個(gè)字段分組, 所有為NUll的分為一組, 然后取不為null的第一條即可.

SELECT * FROM (SELECT A FROM table_null GROUP BY A LIMIT 1,1) t1 JOIN (SELECT B FROM table_null GROUP BY B LIMIT 1,1) t2 JOIN (SELECT C FROM table_null GROUP BY C LIMIT 1,1) t3

第十一題

有一張表stu_score, 所有課程都>=90則特優(yōu), 所有課程都>=80則優(yōu)秀, 都>=60則及格, 否則掛科.

CREATE TABLE stu_score(kecheng VARCHAR(10),stu_id INT,score INT ) INSERT INTO stu_score VALUES('001',1,90); INSERT INTO stu_score VALUES('002',1,92); INSERT INTO stu_score VALUES('001',2,80); INSERT INTO stu_score VALUES('002',2,92); INSERT INTO stu_score VALUES('001',3,76); INSERT INTO stu_score VALUES('002',3,92); INSERT INTO stu_score VALUES('001',4,50); INSERT INTO stu_score VALUES('002',4,92);

答案:
按照學(xué)號(hào)分組, 使用 CASE WHEN 判斷最小分?jǐn)?shù) MIN(score) 是否滿足條件.

SELECT stu_id, (CASE WHEN MIN(score)>=90 THEN '特優(yōu)' WHEN MIN(score)>=80 THEN '優(yōu)秀' WHEN MIN(score)>=60 THEN '及格' ELSE '掛科' END) stat FROM stu_score GROUP BY stu_id

第十二題

運(yùn)用SQL實(shí)現(xiàn)數(shù)據(jù)轉(zhuǎn)換
轉(zhuǎn)換前:

轉(zhuǎn)換后:

CREATE TABLE test33(grade_id INT,subject_name VARCHAR(10),max_score INT ) INSERT INTO test33 VALUES(1,'語(yǔ)文',98); INSERT INTO test33 VALUES(2,'數(shù)學(xué)',95); INSERT INTO test33 VALUES(2,'政治',87); INSERT INTO test33 VALUES(5,'語(yǔ)文',97); INSERT INTO test33 VALUES(5,'數(shù)學(xué)',100); INSERT INTO test33 VALUES(5,'政治',92);

答案:

SELECT 年級(jí), SUM(CASE WHEN 語(yǔ)文 IS NULL THEN 0 ELSE 語(yǔ)文 END) 語(yǔ)文, SUM(CASE WHEN 數(shù)學(xué) IS NULL THEN 0 ELSE 數(shù)學(xué) END) 數(shù)學(xué), SUM(CASE WHEN 政治 IS NULL THEN 0 ELSE 政治 END) 政治 FROM ( (SELECT (CASE grade_id WHEN 1 THEN '一年級(jí)' WHEN 2 THEN '二年級(jí)' WHEN 5 THEN '五年級(jí)' END) 年級(jí), (CASE subject_name WHEN '語(yǔ)文' THEN max_score ELSE NULL END) 語(yǔ)文, (CASE subject_name WHEN '數(shù)學(xué)' THEN max_score ELSE NULL END) 數(shù)學(xué), (CASE subject_name WHEN '政治' THEN max_score ELSE NULL END) 政治 FROM test33 ) UNION ( SELECT (CASE grade_id WHEN 1 THEN '一年級(jí)' WHEN 2 THEN '二年級(jí)' WHEN 5 THEN '五年級(jí)' END) 年級(jí), (CASE subject_name WHEN '語(yǔ)文' THEN max_score ELSE NULL END) 語(yǔ)文, (CASE subject_name WHEN '數(shù)學(xué)' THEN max_score ELSE NULL END) 數(shù)學(xué), (CASE subject_name WHEN '政治' THEN max_score ELSE NULL END) 政治 FROM test33 ) UNION ( SELECT (CASE grade_id WHEN 1 THEN '一年級(jí)' WHEN 2 THEN '二年級(jí)' WHEN 5 THEN '五年級(jí)' END) 年級(jí), (CASE subject_name WHEN '語(yǔ)文' THEN max_score ELSE NULL END) 語(yǔ)文, (CASE subject_name WHEN '數(shù)學(xué)' THEN max_score ELSE NULL END) 數(shù)學(xué), (CASE subject_name WHEN '政治' THEN max_score ELSE NULL END) 政治 FROM test33 ) ) t1 GROUP BY 年級(jí)

第十三題

A表與B表結(jié)構(gòu)相同, 現(xiàn)在想用B表的數(shù)據(jù)更新A表的用戶姓名, 存在就更新, 不存在就添加.
表A

CREATE TABLE A(user_id VARCHAR(10),user_name VARCHAR(20) ) CREATE TABLE B(user_id VARCHAR(10),user_name VARCHAR(20) )

答案:
mysql有這樣的語(yǔ)法: ON DUPLICATE KEY UPDATE 對(duì)唯一索引或主鍵索引可以實(shí)現(xiàn)更新.

#給B表添加唯一索引

ALTER TABLE B ADD UNIQUE KEY user_name(user_name)

#存在相同的user_name就執(zhí)行UPDATE

INSERT INTO B (user_id,user_name) SELECT user_id,user_name FROM A ON DUPLICATE KEY UPDATE user_id=VALUES(user_id),user_name=VALUES(user_name)

下面列幾個(gè)常見(jiàn)的Hive必考HQL
#1. 級(jí)聯(lián)求和問(wèn)題 #######################

select a.username, a.month, max(a.salSum), sum(b.salSum) as salSum from (select username, month, sum(salary) as salSum from t_salary_detail group by username,month) ajoin(select username, month, sum(salary) as salSum from t_salary_detail group by username,month) bon a.username=b.username where a.month>=b.month group by a.username,a.month;

#2. 連續(xù)3個(gè)月都有銷售額的商家 #######################

select shopid,count(flag) as cnt from (select shopid,dt,sale,rn,date_sub(to_date(dt),rn) as flag from (select shopid,dt,sale,row_number() over(partition by shopid order by dt) as rn from t_jd) tmp) tmp2 group by shopid,flag having cnt>=3

#3. 求TOPN問(wèn)題 #######################

--統(tǒng)計(jì)每日最熱門頁(yè)面的top10 select month,day,request,request_count, od from (select month,day,request,request_count,row_number() over(distribute by concat(month,day) sort by request_count desc) as odfrom (select month,day,request,count(1) as request_count from ods_weblog_detailwhere datestr='20130918' group by request,month,day having request is not null) b) c where od<=10

#4. 行轉(zhuǎn)列, 列轉(zhuǎn)行問(wèn)題 #######################

–當(dāng)前有用戶人生階段表lifeStage, 有用戶唯一ID字段uid,用戶人生階段字段stage,其中stage字段內(nèi)容為各個(gè)人生階段標(biāo)簽按照英文逗號(hào)分割的拼接內(nèi)容,
–如:計(jì)劃買車,已買房, 并且每個(gè)用戶的內(nèi)容不同, 請(qǐng)使用hive sql統(tǒng)計(jì)每個(gè)人生階段的用戶量.
–考察點(diǎn): lateral view使用, explode函數(shù)

select stage_someone,count(distinct uid) as uids from lifeStagelateral view explode(split(stage,',')) lifeStage_tmp as stage_someone group by stage_someone;

–和上一題相同的數(shù)據(jù)場(chǎng)景, 但是lifeStage中每行數(shù)據(jù)存儲(chǔ)一個(gè)用戶的人生階段數(shù)據(jù)
–如: 一行數(shù)據(jù)uid是43, stage內(nèi)容為計(jì)劃買車, 另一行數(shù)據(jù)uid字段為43,stage字段為已買房, 請(qǐng)輸出類似于uid為43,stage字段為計(jì)劃買車,已買房這樣的新整合數(shù)據(jù).
–考察點(diǎn): collect_set函數(shù), concat_ws函數(shù)

select uid,concat_ws(',',collect_set(stage_someone)) as stage from lifestage_multline group by uid

#5. 多表關(guān)聯(lián)的基礎(chǔ)上, 行轉(zhuǎn)列與列轉(zhuǎn)行問(wèn)題 #######################

–權(quán)限組表(g)中記錄了組id和組名稱，用戶表(u)記錄了用戶id和用戶名稱，權(quán)限表(gu)是權(quán)限組表和用戶表的關(guān)系，記錄了每一個(gè)權(quán)限組與用戶的對(duì)應(yīng)關(guān)系
–題目是根據(jù)gt和ut表將gu表中的所有id轉(zhuǎn)換為名稱

create table gu_trans as select t.gname as gname,concat_ws(',',collect_set(t.uname)) as uname from(select g.gname,u.unamefrom(select gid,s_uidfrom gu lateral view explode(split(uid,',')) b as s_uid) tmp join g on tmp.gid=g.gidjoin u on tmp.s_uid=u.uid) t group by t.gname 創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的Mysql常见的面试总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。