sql 基础练习 计算7天各个时间点的总和 group by order mysql一次查询多个表

SQL 基础练习

 

-- 创建数据库
CREATE DATABASE school CHARACTER SET UTF8;

-- 使用数据库

USE school;

-- id： 学生的id
-- name：学生的名字
-- nickname：学生的昵称
-- sex：性别
-- in_time: 入学的时间

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    nickname VARCHAR(20) NULL,
    sex CHAR(1) NULL,
    in_time DATETIME NULL         
)

-- 插入数据 单条数据
insert into students(name,nickname,sex,in_time) value('张三2','三哥2','男',now());


-- 插入数据 多条数据
insert into students(name,nickname) values
('张三2','三哥2'),
('张三2','三哥2'),
('张三2','三哥2'),
('张三2','三哥2');


-- 查询语句
-- 查询语句的执行顺序 select from [where group by having  order by  limit ]
select * from students;

-- 查询 为男生的数据，并且倒叙排序
select id, name,nickname from students where sex='男' order by id desc;

-- 查询 sql count() 计算slot_id 和event_type的个数直接返回一个数字 
sql = 'select COUNT(slot_id),COUNT(event_type) from xad_app_log where slot_id = "{}" and event_type = "{}" and ngx_date="{}"'.format('Z2R0c2RoenlzNzAxMDQz','xad_click_tracking','20180512'

-- 查询 sql and 多个条件
sql = 'SELECT slot_id,device_id,request_id,event_type FROM xad_app_log WHERE ngx_date="%s" AND(event_type = "xad_impression" OR event_type = "xad_click_tracking" OR event_type = "xad_dl_start" OR event_type = "xad_dl_success" OR event_type = "xad_install_start" OR event_type = "xad_install_success")' % '20180514'

--查询 distinct 去重 下面的 SQL 语句仅从 "Websites" 表的 "country" 列中选取唯一不同的值，也就是去掉 "country" 列重复值：
sql = 'SELECT DISTINCT country FROM Websites'

-- 查询 group by
#group by
sql = 'select event_type, COUNT(event_type) as num from xad_app_log where ngx_date = "%s" group by event_type' % '20180514


-- 查询 distinct 去重

sql = 'select count(DISTINCT(device_id)) from logstash_base where 1=1 and package_name="{}" and event_type="on_download_apk_succeed" and ngx_date="{}"'.format('com.tencent.reading', '2018-05-10')

-- 修改数据 -- update table_references set col_name1 = expr1 [where where_definition] update students set sex = '女' nickname = '没有昵称' where sex = '男'; update students set sex = '男' where id > 4; -- 删除数据 delete from students where sex = '男'

 

 

 

mysql 计算7天各个时间点的总和 例如 20180911 - 20180913 的总的impression click cost 和数

 

 

SELECT  advertiser_id,date,sum(impression),sum(click),sum(cost) 
FROM account_entry_hour_account_amount 
WHERE DATE_FORMAT(date, '%Y%m%d') > '20180911' AND DATE_FORMAT(date, '%Y%m%d') <'20180913'
GROUP BY DATE_FORMAT(date, '%Y%m%d')

 

 

 

count(*)：所有行进行统计，包括NULL行 (记得这个性能差些，不建议使用)
count(1)：所有行进行统计，包括NULL行
count(column)：对column中非Null进行统计
count(distinct column)：对column中非Null进行去重统计

 

Mysql一次查询多个表

select `users_userprofile`.`advertisement_type`,`account_entry_account_amount`.`advertiser_id` ,sum(`account_entry_account_amount` .cost) as total_cost, sum(`account_entry_account_amount`.`impression` ) as imp, sum(`account_entry_account_amount`.`click` ) as click  from `account_entry_account_amount` , `users_userprofile`  
where  date = "2018-09-28 00:00:00"  and `account_entry_account_amount` .`advertiser_id` =`users_userprofile` .id  group by `users_userprofile`.`advertisement_type`