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 bysql = '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`