mysql的去重添加ing_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

Date: YYYY-MM-DD

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