实验和培养锻练计算20170916,多表查询和子查询示
分类:www.澳门新萄京赌场

复制代码 代码如下: select FROM_UNIXTIME as pubtime, count as cnt from articles group by FROM_UNIXTIME PHP小说按日期SQL归档 复制代码 代码如下: select FROM_UNIXTIME as pubtime, count as cnt from articles group by FROM_UNIXTIME 非时光戳日期格式归档 复制代码 代码如下: select date_format as pubtime, count as cnt from wp_posts where `post_status`='publish' group by date_format order by `ID` desc select date_format as pubtime,date_format(`post_date`,'%m 月 %d 日') as shijian,count as cnt from wp_posts where `post_status`='publish' group by date_format order by `ID` desc limit 0,7

mysql中表单查询用到select命令了,借使是多表查询大家用不计其数主意,如select union与left join之类的生龙活虎道查询了,上面作者给各位mysql初读书人来介绍sql查询示例。

不过假诺你的WordPress网址上有成都百货上千篇小说,而你需求开展全站范围的改善, 这时候从后台逐个编辑就有一点费时费劲了,而且犯错的可能率也会抓实。 最佳的主意是步入WordPress的MySQL数据库试行需要的询问。 通过MySQL能够高速地落成上述职务,为您节省越来越多时光。 上边要介绍的正是后生可畏对省时省力的WordPress SQL查询形式。 事先备份 WordPress数据Curry积存了您留神发表的每生龙活虎篇小说,来自你的读者的装有评价,以致你对本身网址开展的兼具特性化设置。 因而,无论你对和睦有多自信,都请牢牢记住必要求先行备份WordPress数据库。 你能够透过备份插件进行备份。 为全数文章和页面增多自定义字段 这段代码可认为WordPress数据库内有所小说和页面增多一个自定义字段。 你供给做的正是把代码中的‘UniversalCutom菲尔德‘替换来你须要的文字,然后把‘MyValue‘改成须要的值。 复制代码 代码如下: INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'UniversalCustomField' AS meta_key 'MyValue AS meta_value FROM wp_postsWHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField'); 如若只须要为小说加多自定义字段,能够采纳上面这段代码: 复制代码 代码如下: INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'UniversalCustomField' AS meta_key 'MyValue AS meta_value FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')`` AND post_type = 'post'; 倘诺只须求为页面增添自定义字段,能够行使上面这段代码: 复制代码 代码如下: INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'UniversalCustomField' AS meta_key 'MyValue AS meta_value FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')AND `post_type` = 'page'; 删除小说meta数据 当你安装或删除插件时,系统经过文章meta标签存款和储蓄数据。 插件被删去后,数据依然会存留在post_meta表中,当然这个时候你曾经不复必要那个数量,完全能够去除之。 记住在运作查询前把代码里的‘YourMetaKey‘替换来你需求的相应值。 复制代码 代码如下: DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey'; 查找无用竹签 假若您在WordPress数据Curry奉行查询删除旧小说,和事先删除插件时的意况风流倜傥致,作品所属标签会留在数据Curry,而且还或许会产出在标签列表/标签云里。 上面包车型地铁询问可以帮您寻找无用的竹签。 复制代码 代码如下: SELECT * From wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0; 批量删除垃圾研商实施以下SQL命令: 复制代码 代码如下: DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam'; 批量删除全部未核查谈论那个SQL查询会删除你的网址上装有未考察商酌,不影响已审商量价。 复制代码 代码如下: DELETE FROM wp_comments WHERE comment_approved = 0 避免钻探较早文章内定comment_status的值为open、closed或registered_only。 别的还供给安装日期: 复制代码 代码如下: UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2010-01-01' AND post_status = 'publish'; 停用/激活trackback与pingback 指定comment_status的值为open、closed或registered_only。 向全体客商激活pingbacks/trackbacks: 复制代码 代码如下: UPDATE wp_posts SET ping_status = 'open'; 向具备客商禁止使用pingbacks/trackbacks: 复制代码 代码如下: UPDATE wp_posts SET ping_status = 'closed'; 激活/停用某二十二日子前的Pingbacks & Trackbacks 钦定ping_status的值为open、closed或registered_only。 别的还亟需设置日期: 复制代码 代码如下: UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2010-01-01' AND post_status = 'publish'; 删除特定U宝马X3L的评价 当您发觉众多垃圾堆商议都富含相似的UEscortL链接,能够使用下边包车型大巴查询叁遍性删除那个评价。%表示含有“%"符号内字符串的具有U讴歌RDXL都将被去除。 复制代码 代码如下: DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ; 识别并剔除“X"天前的作品 查找“X"天前的具有作品: 复制代码 代码如下: SELECT * FROM `wp_posts` WHERE `post_type` = 'post'AND DATEDIFF > X 删除“X"天前的富有作品: 复制代码 代码如下: DELETE FROM `wp_posts` WHERE `post_type` = 'post'AND DATEDIFF > X 删除无需的短代码 当你说了算不再选拔短代码时,它们不会自动消失。你能够用二个简易的SQL查询命令删除全数无需的短代码。 把“tweet"替换来相应短代码名称: 复制代码 代码如下: UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' ) ; 将小说转为页面 依旧只要透过PHPMyAdmin运维三个SQL查询就足以解决: 复制代码 代码如下: UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post' 将页面转变到文章: 复制代码 代码如下: UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page' 改过全数小说上的撰稿者属性 首先通过下边包车型地铁SQL命令检索小编的ID: 复制代码 代码如下: SELECT ID, display_name FROM wp_users; 成功收获该作者的新旧ID后,插入以下命令,记住用新作者ID替换NEW_AUTHOR_ID,旧作者ID替换OLD_AUTHOR_ID。 复制代码 代码如下: UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID; 批量删除小说修正历史 小说修改装订历史保存能够很实用,也足以很令人相当慢。 你能够手动删除修定历史,也能够行使SQL查询给和煦解省时间。 复制代码 代码如下: DELETE FROM wp_posts WHERE post_type = "revision"; 停用/激活全部WordPress插件 激活有些插件后开采不可能登入WordPress管理面板了,试试上面包车型客车询问命令吧,它会应声禁止使用全数插件,让您再度登入。 复制代码 代码如下: UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins'; 修改WordPress网址的目的URAV4L 把WordPress博客从风流倜傥台服务器移到另风流倜傥台服务器后,接下去你要求报告WordPress你的新博客地址。 使用以下命令时,注意将 首先: 复制代码 代码如下: UPDATE wp_options SET option_value = replace(option_value, '', '') WHERE option_name = 'home' OR option_name = 'siteurl'; 然后使用下边包车型地铁吩咐校勘wp_posts里的U中华VL: 复制代码 代码如下: UPDATE wp_posts SET guid = replace(guid, '); 最后,搜索文章内容以保险新U途睿欧L链接与原链接未有弄混: 复制代码 代码如下: UPDATE wp_posts SET post_content = replace(post_content, ' ', ' '); 纠正暗许客商名Admin 把里面包车型客车YourNewUsername替换到新客商名。 复制代码 代码如下: UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin'; 手动重新初始化WordPress密码 就算你是你的WordPress网站上的独步天下笔者,何况你从未校订默许顾客名, 那个时候你能够用上边的SQL查询来重新设置密码: 复制代码 代码如下: UPDATE `wordpress`.`wp_users` SET `user_pass` = MD5 WHERE `wp_users`.`user_login` =`admin` LIMIT 1; 搜索并替换小说内容 OriginalText换来被交替内容,ReplacedText换到靶子内容: 复制代码 代码如下: UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'OriginalText','ReplacedText'); 校订图片ULANDL 上边包车型客车SQL命令能够帮您改改图片路线: 复制代码 代码如下: UPDATE wp_postsSET post_content = REPLACE (post_content, 'src="', 'src="');

mysql分组命令是GROUP BY,上边小编把在学习GROUP BY命令时笔记与大家大饱眼福,这里包含了GROUP BY轻巧用法与GROUP BY排序难点了。

CREATE TABLE city(

生龙活虎、轻易询问: 着力语法:

每当查询数据库时,想知道有稍许类,或想精通找分裂等的有微微种,就用到了分组语句group by

province_code INT,

 代码如下

利用格局:

province_name string,

复制代码

 代码如下

city_code INT,

SELECT * FROM tb_name;

复制代码

city_name string

查询全体

SELECT * FROM `表名` group by `分组字段`

)

 代码如下

或带limit做法

ROW FORMAT delimited

复制代码

 代码如下

fields terminated by ','

SELECT field1,field2 FROM tb_name;

复制代码

lines terminated by 'n';

投影

SELECT *
FROM `数据表`
GROUP BY `分组的字段`
LIMIT 0 , 30

load数据到hive表: load data local inpath '/home/bigdata//hive/city.txt' into table city;

 代码如下

例:(查询dedecms(织梦卡塔 尔(英语:State of Qatar)程序的栏目标题表,以栏目id分组卡塔尔

翻看创设表 show create table city;

复制代码

 代码如下

查看表消息 desc city;

SELECT [DISTINCT] * FROM tb_name WHERE qualification;

复制代码

修改表:alter table city rename to city_name;

 选择
说明:

SELECT *
FROM `dede_archives`
GROUP BY `typeid`
LIMIT 0 , 30

复制表: create table city like city_code;

FROM子句: 要查询的关联         表、四个表、别的SELECT语句

关于mysql group by排序难点 .

删除库 drop database **;

WHERE子句:布尔关系表明式,主要包括如下这几类表明式:

类如 有七个 帖子的回复表,posts( id , tid , subject , message ,  dateline 卡塔 尔(阿拉伯语:قطر‎ ,

删除表 drop table **;

比较:=、>、>=、<=、<
逻辑关系:
AND
OR
NOT

id为 自动拉长字段, tid为该回复的宗旨帖子的id(外键关联卡塔尔国,  subject 为恢复生机标题, message 为苏醒内容, dateline 为还原时间,用UNIX 时间戳表示,

查表内容 select * from city limit 10;

BETWEEN ... AND ...   :在三个值时期
LIKE ‘’
%: 放肆长度自便字符
_:任性单个字符
REGEXP, MuranoLIKE :正则表明式,那时候目录无效
IN
IS NULL
IS NOT NULL  

最简便易行的 :

显示列名 set hive.cli.print.header=true;

经常来讲查询本博客的wp-links和wp_posts表:

 代码如下

建库

 代码如下

复制代码

建表

复制代码

SELECT * FROM (SELECT * FROM posts ORDER BY dateline DESC) GROUP BY  tid ORDER BY dateline DESC LIMIT 10

查询有关命令  最大的10个:select * from city order by city_code desc limit 10;

mysql> select * from wp_links; 查询全数 mysql> select link_name,link_url from wp_links; 投影
------------------- --------------------------------------
| link_name               | link_url                                              |
------------------- --------------------------------------
| 旺旺Tencent今日头条          |               |
| 旺旺搜狐和讯          |                |
| BKJIA技艺博客      | |
------------------- --------------------------------------
10 rows in set (0.00 sec)

也可能有网络老铁使用自连接完结的 ,那样的频率应该比下边的子查询功效高,可是,为了简单明了,就只用那样黄金时代种了,GROUP BY未有排序功效,恐怕是mysql弱智的地点,或许是自家尚未曾发觉,

怀有最多市的省份:外省市的数额:取最多的拾二个省:

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish';选择
------ ---------------------------------------------------------------- ----------------------
| ID   | post_title                                                                                      | post_date                   |
------ ---------------------------------------------------------------- ----------------------
| 1291 | 【转】HP 3PA昂科雷存款和储蓄概念之三                                                      | 二零一三-08-29 17:21:27 |
| 1298 | 【转】HP 3PAQX56存款和储蓄概念之四                                                      | 2013-08-29 17:22:33 |
| 1351 | 【转】XenDesktop 5.5 vSphere 5成立虚构机报错                      | 二〇一二-09-04 17:41:26 |
| 1357 | linux下强盛的互联网工具Netcat                                                     | 2011-09-09 22:26:45 |
| 1360 | MySQL常用命令、技艺和注意事项                                              | 二〇一三-09-20 11:04:15 |
| 1369 | 【转】数据库设计原理知识--B树、B-树、B 树、B*树都以什么     | 二零一三-09-21 12:30:18 |
| 1379 | MySQL基本SQL语句之常用场理SQL                                           | 二零一二-09-21 12:39:23 |
------ ---------------------------------------------------------------- ---------------------
7 rows in set (0.01 sec)

where group by(对小组张开排序卡塔 尔(阿拉伯语:قطر‎

select province_name,count(city_name) as cnt from city group by province_name order by cnt desc limit 10;

对查询结果排序:
ORDER BY field_name {ASC|DESC}
如下:

澳门新萄京 1

去重总结 有个别许个省:distinct

 代码如下

对group by里的小组展开排序的函数我只查到group_concat()能够开展排序,但group_concat的效果与利益是将小组里的字段里的值举行串联起来。

1)select count(distinct province_name) from city;

复制代码

 代码如下

两重:2)select count(1)

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID; 

复制代码

from

  ##升序,ID是排序的字段

select group_concat(id order by `date` desc) from `test` group by category_id

(

 代码如下

澳门新萄京 2

select province_name from city group by province_name

复制代码

 代码如下

) a;

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID DESC; 

复制代码

得到消息唯有一个市的省区:省有多少市 ;市为1

##降序
字段小名:AS select col_name AS COL_Aliases … :对字段使用外号

select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc

1)

select col_name,… from tb_name AS tb_Aliases …  :对表使用外号
如下:

澳门新萄京 3
子查询建设方案

select

mysql> select post_title AS 小说标题 from wp_posts where ID>1290 and post_status='publish';
----------------------------------------------------------------
| 文章标题                                                                                    |
----------------------------------------------------------------
| 【转】HP 3PAEvoque存款和储蓄定义之三                                                      |
| 【转】HP 3PA索罗德存款和储蓄定义之四                                                      |
| 【转】XenDesktop 5.5 vSphere 5创造虚构机报错                      |
| linux下强大的网络工具Netcat                                                     |
| MySQL常用命令、才具和注意事项                                              |
| 【转】数据库设计原理知识--B树、B-树、B 树、B*树都是什么样     |
| MySQL基本SQL语句之常用场理SQL                                           |
----------------------------------------------------------------
7 rows in set (0.02 sec)

 代码如下

province_name, cnt

##仍可以够这么:

澳门新萄京,复制代码

from

 代码如下

select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc

(

复制代码

 澳门新萄京 4

select province_name,count(1) as cnt

mysql> select 3 2 AS SUM;
-----
| SUM |
-----
|   5    |
-----
1 row in set (0.00 sec)
LIMIT子句:LIMIT [offset,]Count
如下:

BY,上面小编把在念书GROUP BY命令时笔记与大家享用,这里包罗了GROUP BY轻易用法与GROUP BY排序难点了。 每当查询数据库时,...

from city

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 5;
------ -------------------------------------------------- ---------------------
| ID      | post_title                                                                | post_date               |
------ -------------------------------------------------- ---------------------
| 1291 | 【转】HP 3PARAV4存款和储蓄定义之三                                 | 二零一二-08-29 17:21:27 |
| 1298 | 【转】HP 3PAEnclave存款和储蓄定义之四                                 | 二〇一二-08-29 17:22:33 |
| 1351 | 【转】XenDesktop 5.5 vSphere 5成立虚构机报错  | 二零一二-09-04 17:41:26 |
| 1357 | linux下强盛的网络工具Netcat                                 | 2012-09-09 22:26:45 |
| 1360 | MySQL常用命令、技术和注意事项                          | 2012-09-20 11:04:15 |
------ -------------------------------------------------- ---------------------
5 rows in set (0.01 sec)

group by province_name

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 2,3;  ##丁未革命部分(逗号前的数字卡塔尔表示偏移量
------ -------------------------------------------------- ---------------------
| ID      | post_title                                                              | post_date                 |
------ -------------------------------------------------- ---------------------
| 1351 | 【转】XenDesktop 5.5 vSphere 5创立设想机报错  | 二零一二-09-04 17:41:26 |
| 1357 | linux下强盛的互联网工具Netcat                                 | 二〇一一-09-09 22:26:45 |
| 1360 | MySQL常用命令、才能和注意事项                          | 2012-09-20 11:04:15 |
------ -------------------------------------------------- ---------------------
3 rows in set (0.00 sec)
集结:SUM(), MIN(), MAX(), AVG(), COUNT(),括号中为字段名
mysql> select sum(ID) from wp_posts;计算和
mysql> select min(ID) from wp_posts;查早最小的
mysql> select max(ID) from wp_posts; 查找最大的
mysql> select avg(ID) from wp_posts; 平均值
mysql> select count(ID) from wp_posts;计数
分组:GROUP BY,日常合营聚合运算使用
如下:

)a

mysql> select count(post_status) AS 各状态数量,post_status AS 状态名称 from wp_posts group by post_status;
----------------- --------------
| 各状态数量           | 状态名称        |
----------------- --------------
|               1           | auto-draft     |
|               9           | draft             |
|             251         | inherit           |
|             238         | publish          |
|               2           | trash             |
----------------- --------------
5 rows in set (0.01 sec)
小心:能够选用HAVING qualification将GROUP BY的结果再度过滤,用法同where

where cnt =1;

 

2) select province_name count(1) as cnt from city group by province_name having cnt = 1;

二、多表查询 连接:
交叉连接:笛Carl乘积
理所当然连接:将两张表某字段中约等于连接起来,如下

外表:

 代码如下

table前加关键字external

复制代码

远端要用的location'/user/hzw/city/city.txt'

mysql> SELECT students.Name,students.Age,courses.Cname,students.Gender FROM students,courses WHERE students.CID1 = courses.CID;
-------------- ------ ------------------ --------
| Name             | Age   | Cname                 | Gender |
-------------- ------ ------------------ --------
| GuoJing          |   19   | TaiJiquan              | M        |
| YangGuo        |   17   | TaiJiquan              | M        |
| DingDian        |   25   | Qishangquan       | M         |
实验和培养锻练计算20170916,多表查询和子查询示例。| HuFei             |   31   | Wanliduxing         | M         |
| HuangRong    |   16   | Qianzhuwandushou | F       |
| YueLingshang |   18   | Wanliduxing         | F          |
| ZhangWuji      |   20   | Hamagong           | M         |
| Xuzhu             |   26   | TaiJiquan              | M         |
-------------- ------ ------------------ --------
8 rows in set (0.00 sec)

create external table city_ex(

    外连接:
左外连接:left_tb LEFT JOIN right_tb ON ...  :以左表为规范

province_code int,

 代码如下

province_name string,

复制代码

city_code int,

mysql> SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
-------------- --------------------
| Name             | Cname                    |
-------------- --------------------
| GuoJing          | TaiJiquan                 |
| YangGuo        | TaiJiquan                 |
| DingDian        | Qishangquan           |
| HuFei              | Wanliduxing            |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing            |
| ZhangWuji      | Hamagong              |
| Xuzhu             | TaiJiquan                 |
| LingHuchong  | NULL                      |
| YiLin               | NULL                      |
-------------- --------------------
10 rows in set (0.00 sec)

city_name string)

右外连接 : left_tb RIGHT JOIN right_tb ON ... :以右表为职业

row format delimited

 代码如下

fields terminated by','

复制代码

lines terminated by'n'

mysql> SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;
-------------- --------------------
| Name             | Cname                    |
-------------- --------------------
| GuoJing         | TaiJiquan                  |
| YangGuo       | TaiJiquan                  |
| DingDian       | Qishangquan            |
| HuFei             | Wanliduxing             |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing            |
| ZhangWuji     | Hamagong               |
| Xuzhu            | TaiJiquan                  |
| NULL             | Yiyangzhi                 |
| NULL             | Jinshejianfa              |
| NULL             | Qiankundanuoyi      |
| NULL             | Pixiejianfa                |
| NULL             | Jiuyinbaiguzhua       |
-------------- --------------------
13 rows in set (0.01 sec)

location '/user/hzw/city/';

自连接:本表中分歧字段间开展连接

内表:

 代码如下

create table city_in(

复制代码

province_code int,

mysql> SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
----------- -------------
| student   | teacher     |
----------- -------------
| GuoJing   | DingDian    |
| YangGuo   | GuoJing     |
| DingDian  | ZhangWuji   |
| HuFei     | HuangRong   |
| HuangRong | LingHuchong |
----------- -------------
5 rows in set (0.02 sec)

province_name string,

留意:使用了外号
三、子查询:三个查询中嵌套此外三个查询 如下:在students表中询问年龄大于平均年龄的学子

city_code int,

 代码如下

city_name string)

复制代码

row format delimited

mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
------------- ------
| Name           | Age    |
------------- ------
| DingDian      |   25 |
| HuFei           |   31 |
| Xuzhu           |   26 |
| LingHuchong |   22 |
------------- ------
4 rows in set (0.08 sec)

fields terminated by','

子查询注意事项:

lines terminated by'n'

■比较操作中使用子查询:子查询只好回去单个值;
■IN(): 使用子查询;
■在FROM中使用子查询;
联合查询:UNION,将八个查询的结果合併

location '/user/hzw/city/';

 代码如下

2、使用分区patition对应表中的一个目录;

复制代码

静态分区供给在sql语句中钦赐;

mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
-------------- ------
| Name             | Age    |
-------------- ------
| GuoJing          |   19   |
| YangGuo        |   17   |
| DingDian        |   25   |
| HuFei              |   31   |
| HuangRong    |   16   |
| YueLingshang |   18   |
| ZhangWuji      |   20   |
| HuYidao          |   42  |
| NingZhongze  |   49   |
-------------- ------
19 rows in set (0.00 sec)

CREATE TABLE `user`(

union与left join之类的合营查询了,上面笔者给诸位mysql初读书人来介绍...

`uid` INT,

`city_code` INT,

`model` string,

`access` string

)

row FORMAT delimited

fields terminated by ','

lines terminated by 'n';

load data local inpath '/home/bigdata/tanqi/hive/user.txt' into table user;

查询user: select count(*) from user;

a.静态分区:

create table user_daily(

uid int,

city_code int,

model string,

access string

)

partitioned by (p_data string);

插入数据从user :

insert overwrite table user_daily partition (p_data='2017-09-01')

select * from user;

b.动态分区

set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table user_daily partition (p_data)

select *,'2017-09-02' from user

union all

select *,'2017-09-04' from user;

ALTER TABLE user_daily PARTITION (p_date='2017-01-01') RENAME TO PARTITION (p_date='20170101');

3.作业

a) 总计WIFI境遇下客商最多的5个都市

Select city_code,count(*) as cnt from user where access='WIFI'

group by city_code order by cnt desc limit 5;

b) 总括客户数>=3的access和city_code组合

select access,city_code,count(*) as cnt from user

group by access,city_code

having cnt >=3 order by cnt desc;

c) 机型中含有ne(不区分朗朗上口写卡塔尔,差异access及对应顾客数

select access,count(*) as cnt from user

where lower(model) like '%ne%'

group by access order by cnt desc;

判断 access里的名/总数的sum

select sum(if(access='WIFI',1,0))/count(1) from user;

select sum(if(access='2G',1,0))/count(1) from user;

select sum(if(access='4G',1,0))/count(1) from user;

if条件多

select

case

when uid % 10 in (0, 1, 2, 3) then '0-3'

when uid % 10 in (4, 5, 6, 7) then '4-7'

else '8-9'

end as interval,

count(*) as cnt

from user

group by

case

when uid % 10 in (0, 1, 2, 3) then '0-3'

when uid % 10 in (4, 5, 6, 7) then '4-7'

else '8-9'

end;

晚间

1、  list 集合

collect_set 去重的会合

collect_list 不去重

select collect_set(access) from user;

select collect_list(access) from user;

2、 hive 种种连接join

左连接 left outer join

右连接 right outer join

内接连 inner join  寻觅左右如出豆蔻梢头辙(AB相交)的笔录

全连接 full join

select user.uid,user.city_code,city.city_name

from

(select * from user where uid <=100) user

left join

(select * from city where province_code <=30)city

on (user.city_code = city.city_code)

limit 20;

select user.uid,user.city_code,city.city_name

from

(select * from user where uid <=100) user

full join

(select * from city where province_code <=30)city

on (user.city_code = city.city_code)

limit 20;

3、分组top N

分组 access

排序 序号 city_code

top1 序号=1

select access,city_code,uid

from

(

select uid, access,city_code,

row_number() over (partition by access order

by city_code desc)as row_num

from user

)a

where row_num = 1;

4、累计                                                          第一行              当前行

select p_date,

sum(cnt) over(order by p_date asc rows between unbounded preceding and current row)

from

(

select p_date, count(*) as cnt

from user_daily

where p_date between '2017-09-01' and '2017-09-30'

group by p_date

)a

;

本文由澳门新萄京发布于www.澳门新萄京赌场,转载请注明出处:实验和培养锻练计算20170916,多表查询和子查询示

上一篇:php单例格局完结_php功底_脚本之家,PHP单例情势学 下一篇:没有了
猜你喜欢
热门排行
精彩图文