SQL从入门到不放弃(ing)

常用表操作

显示数据库下的表：

show tables

显示表列：

show columns from table

修改表名：

alter table table_old_name rename table_new_name;

删除表：

drop table  table_name;

删除mysql表中所有内容：

delete from 表名;
truncate table 表名;
#删除表中内容（有限制）
delete from celebs WHERE twitter_handle IS NULL;
#强制删除分区表的数据
drop table my_log purge;
alter table my_log drop partition (ds='20150618') purge;

修改表列类型

alter table 表名 modify 列名 类型名
eg:alter table test modify address char(10)

修改表列名字

alter table 表名 change column 原列 现列 类型
alter table repeat_stat change column source_tpye stat_day varchar(10)

添加表列

alter table 表名 add column 列名 定义列类型;
eg:alter table test add column name varchar(10);
#在指定地方插入列
alter table表名 add 新添列 新添列的类型 after 位置列;
eg:alter table newexample add address varchar(110) after stu_id;

查询结果

#查询某列
SELECT name FROM celebs；
#注：name是celebs表中的一个列
#查询限制结果
select * from s_yes_access limit 1;
#限制从某条数据开始的结果
select * from s_yes_access limit 5,5;
#注：表示返回从第五行开始的五行
read projec.table partition(ds='xxx') 5; 这个命令可以查看一个表的前几条数据，比用select * from table limit 5 
#去重查询
SELECT DISTINCT genre FROM movies;

插入一行数据

insert into table_name(id,name,age) VALUES (1,’Justin’,21)

编辑已有数据

UPDATE celebs SET age=22 WHERE id=1；

将一个数据库下的某表迁移到

create table  table_name select * from old_database.table_name ;
#在新的数据库下面执行上面的代码，能把old_database库下的table_name表全部拷贝到新的数据库下
create table table_name select * from told_database.table_name where 1=2 ;
#只复制表结构，就是在最后where下设定一个不成立的条件

一列变多列

trans_array(4, ',', cookie, ideaid, tag, device, videopref)
注：不变的key值有4个，（前四个， cookie, ideaid, tag, device，以‘，’为分隔符对剩下的字段内部进行分割，这里是videopref）
转换过后为：
cookie, ideaid, tag, device, videopref1
cookie, ideaid, tag, device, videopref2
cookie, ideaid, tag, device, videopref3

在过滤器where

常用运算符

= /!= /> /< />=/<=/

特殊运算符：

like
Eg：SELECT * FROM movies WHERE name LIKE 'Se_en';
#挑出了包含se_en多条数据eg：Se7en，seven，“_”代表了通配符，任何单一的字符
%是另一个通配符，
Eg：SELECT * FROM movies WHERE name LIKE 'A%'
找出所有name中以A开头的数据，如果是%a，代表所有以a结尾的
SELECT * FROM movies WHERE name LIKE '%man%';
注：'%man%'表示，只要被查找的内容包含man的就可以，并且like不是大小写敏感的

between用于限定范围，值可以是数字，文本，日期
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';
该命令，找出名字以A开头到J(但是不包括J)的电影
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
该命令，找出时间从1990到2000(包括2000)的电影
找出以上限制条件下的喜剧电影：
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy';
使用AND可以连接多条限制条件，OR的使用方法类似

空值检验： IS NULL 或者 IS NOT NULL 在WHERE 语句中 (不能使用= NULL ).
Eg：SELECT * FROM movies WHERE price IS NULL;

组合查询Union All

Union All可以把多个select操作返回的结果，联合成一个数据集。它会返回所有的结果，但是不会执行去重。 （注：ODPS不支持直接对顶级的两个查询结果进行union操作，需要写成子查询的形式。union all连接的两个select查询语句，两个select的列个数、列名称、列类型必须严格一致。如果原名称不一致，可以通过别名设置成相同的名称。）

Eg：
select 
    times,
    source_type
from (
    select 
        times, 
        source_type
            from (
            xxx
            )a
    union all 

    select 
      times,
      source_type
    from (
      xxx
    ) tmp
)b ;

注：在一般的sql中使用union会自动去重，想要保留重复的信息，需要使用union all

表中的计算(Aggregate function集合函数)

Order by排序

#Sort查询结果：
SELECT * FROM movies ORDER BY imdb_rating DESC;
#DESC表示由高到底，ASC表示由低到高，默认是从低到高排序
#按多列排序：
SELECT * FROM movies ORDER BY name, price;
#先按name排序，再按price排序
#如果要指定排序顺序，需要对每次排序进行指定
SELECT * FROM movies ORDER BY name DESC, price ASC;
#可以用as里面的别名进行排序：
Eg:
SELECT dep_month,
      dep_day_of_week,
      dep_date,
      COUNT(*) AS flight_count
FROM flights
GROUP BY dep_date 
order by flight_count desc;
#排序可以用数字进行简便写
select date(ordered_at),count(1) from orders
group by 1
order by 1;

Count()函数& GROUP BY
说明：输入为column的名字，计算出column中不为空的行数

Eg：SELECT COUNT(*) FROM fake_apps;
#计算有多少种app在fake_apps
#可以在计算中增加限制条件：如，计算免费的app条数
SELECT COUNT(*) FROM fake_apps WHERE price = 0;
#计算每种价格app的条数
SELECT price, COUNT(*) FROM fake_apps GROUP BY price;
#注：GROUP BY 只在聚合函数中使用，用于将查询到的数据进行分组 
Eg：SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP BY price;
#计算出下载超过两万次的app的价格和该价格对应的app个数
#Count出一列中不重复的条数
count(distinct order_id )
#多数group by的情况，产出格式为[date, platform, dau_count]，要以date又要以platform进行count
Eg：
select date(created_at),platform, count( distinct user_id) as dau 
from gameplays
group by 1,2
order by 1,2;

SUM() & GROUP BY
说明：输入是列名，输出是该列所有值的和

Eg：SELECT SUM(downloads) FROM fake_apps;

#计算每种类型app的下载量
Eg：SELECT category, SUM(downloads) FROM fake_apps GROUP BY category;

MAX()/MIN()& GROUP BY
说明：输入是列名，输出是该列的最大值

Eg：SELECT MAX(downloads) FROM fake_apps;

#输出每种类型app中下载量最大的app名字
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY category;

如果是要在每一行两列中输出大的那一列（注：结果不是分别输出这两列最大的值）
SELECT id, MAX(ingredients_cost, packaging_cost)
FROM baked_goods;

AVG()& GROUP BY
说明：输入是列名，输出是该列的平均值
统计每种价格的平均下载量
ROUND()
说明：输入是列名和一个整数，将列中的数字转变为几位小数

Eg：SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps
GROUP BY price;

不输入整数时，代表将小数变为最近的整数
SELECT price, ROUND(AVG(downloads)) FROM fake_apps GROUP BY price;
#注：ODPS上返回的是最近整数时double型的

HAVING
说明：类似于where，差别是where过滤行，having过滤分组

INTERSECT
说明：合并两个select语句，返回两个select中相同的行

Eg：
SELECT column_name(s) FROM table1 
INTERSECT 
SELECT column_name(s) FROM table2;

EXCEPT
输出去重后的，在第一个select，不在第二个select的行

Eg：
SELECT column_name(s) FROM table1 
EXCEPT 
SELECT column_name(s) FROM table2;

CASE

case {condition} 
when {value1} then {result1} 
when {value2} then {result2} 
else {result3} 
end
Eg：
SELECT 
CASE
WHEN elevation < 500 THEN 'Low' 
WHEN elevation BETWEEN 500 AND 1999 THEN 'Medium' 
WHEN elevation >= 2000 THEN 'High' 
ELSE 'Unknown' 
END AS elevation_tier , COUNT(*) 
FROM airports 
GROUP BY 1;
#注：end必须存在，用于结束命令
#Else是可选的，如果else不存在，结果会成为“NULL”
#Group by 1就是以第一个字段聚合，group by 的简便用法，不需要再重新写一次语句

SELECT    state, 
    COUNT(CASE WHEN elevation < 1000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;

计算percentages or ratio

SELECT origin, 100.0*(sum(CASE WHEN carrier = 'UN' THEN distance ELSE 0 END)/sum(distance)) as percentage_flight_distance_from_united 
FROM flights 
GROUP BY origin;
Sum(case):
sum(CASE WHEN carrier = 'UA' THEN distance ELSE 0 END)
注：当carrier为UA的时候sum字段distance，其他的时候为0

格式转变

关于时间
转换时间戳为date或者datetime

  1. Date: YYYY-MM-DD
  2. Datetime or Timestamp: YYYY-MM-DD hh:mm:ss
    3.TIME: 00:00:00
Eg：
SELECT DATE(delivery_time)
FROM baked_goods;
调整时间:
DATETIME(time1, '+3 hours', '40 minutes', '2 days');
会返回一个时间3ours, 20 minutes, and 2 days 之后time1

关于数值

SELECT (number1 + number2);
返回：两数之和
SELECT CAST(number1 AS REAL) / number3;
返回：实数，因为用CAST将一个数变为实数了
SELECT ROUND(number, precision);
返回：precision用于控制number取几位小数
关于字符串
|| ' ' ||
串联两个字符串
SELECT string1 || ' ' || string2;
Eg：将city与state字符串串联
SELECT city || ' ' || state as location FROM bakeries;
注：可以在' '加空格，‘，’等分割符
REPLACE()
REPLACE(string,from_string,to_string)
把string中的所有from_string全部替换成to_string

多张表的处理

假设有两张表，一张是专辑 albums，一张是艺术家artists。一个艺术家可以产出多张专辑，一个专辑只能被一个艺术家产出。
艺术家表的字段：artists

CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT);
注：
primary key代表table中的每一行都唯一标识。我们可以用这个字段去连接artists 和albums
PRIMARY KEY意味着:
1.没有值是空的
2.每个值都是唯一的
一张表不能含有一个以上的 PRIMARY KEY column.

查询多表的时候，列名需要使用table_name.column_name调用
eg: albums.name, albums.year, artists.name
产出某个专辑的作者和这个专辑合并，我们需要用到Join。

Eg: 
SELECT 
*
FROM 
albums 
JOIN artists ON 
albums.artist_id = artists.id;

eg: 
SELECT
  *
FROM
  albums
LEFT JOIN artists ON
  albums.artist_id = artists.id;

LEFT JOIN的不同：
INNER JOIN ：=join两张表都有时输出
LEFT OUTER ：左表里的都会输出，左边里有，右表里没有的会以NULL代替
注：两张表如果要join的字段名字相同可以使用using

eg: 
SELECT
  *
FROM
  albums
LEFT JOIN artists ON
using id

Join中时间的设置
date(g1.created_at) = date(datetime(g2.created_at, '-1 day'))
注：the date in g1 is one less than the date in g2",
Eg：
select
  daily_revenue.dt,
  daily_revenue.rev / daily_players.players
from daily_revenue
join daily_players using (dt);

AS重命名

SELECT albums.name AS 'Album', albums.year, artists.name AS 'Artist' FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year > 1980;

写sql时遇到的坑
1.SELECT name, category FROM fake_apps
WHERE downloads = (SELECT MAX(downloads) FROM fake_apps);
注：不能使用SELECT name, category FROM fake_apps WHERE downloads = MAX(downloads);

2.解决中文乱码
mysql> set names utf8; //设置数据库字符集为utf8

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 157,198评论 4赞 359
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,663评论 1赞 290
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 106,985评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,673评论 0赞 202
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 51,994评论 3赞 285
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,399评论 1赞 211
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,717评论 2赞 310
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,407评论 0赞 194
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,112评论 1赞 239
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,371评论 2赞 241
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,891评论 1赞 256
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,255评论 2赞 250
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,881评论 3赞 233
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,010评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,764评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,412评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,299评论 2赞 260

推荐阅读更多精彩内容

  • 《SQL初学者指南》读书笔记
    关系型数据库和SQL SQL语言的三个部分DML：Data Manipulation Language，数据操纵语...
    Awey阅读 1,884评论 0赞 13
  • SQL 语法大全
    SQL SELECT 语句 一、查询SQL SELECT 语法 （1）SELECT 列名称 FROM 表名称 （2...
    有钱且幸福阅读 5,330评论 0赞 33
  • SQL简明数据分析教程
    SQL与MySQL简介 数据库基础 从SQL的角度来看，数据库就是一个以某种有组织的方式存储的数据集合。我们可以采...
    heming阅读 2,978评论 1赞 8
  • 放下“拯救者”的重担，他拯救了自己的婚姻
    不是我没有界线，而是我从来没有告诉过别人我的界线，人人都要为自己的界线负责任，坚持自己的界线，那才叫界线。 01 ...
    大东心理阅读 588评论 2赞 2
  • 绪
    突然想写首诗 给你 原谅我忘了诗的内容和风格 只是一股无名绪感 用笔尖，传给你 沏一壶热茶 斟两盏清香 一饮，即为...
    秋风不知所谓阅读 242评论 0赞 0