#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Apr 16 12:17:21 2019
@author: tide
"""
+------------+-----------+------+--------+--------+-------------+---------+
| first_name | last_name | age | gender | income | force_value | country |
+------------+-----------+------+--------+--------+-------------+---------+
| Mac | Mohan | 20 | M | 4500 | 20 | America |
| Mac | Mohan | 20 | M | 4500 | 20 | America |
| Nancy | MM | 19 | F | 4500 | 20 | America |
| cao | cao | 58 | m | 17500 | 20 | China |
| sun | quan | 40 | m | 15500 | 20 | China |
| liu | bei | 60 | m | 19000 | 20 | China |
| zhang | fei | 48 | m | 7000 | 28 | china |
| guang | yu | 52 | m | 9000 | 35 | china |
| Mike | jordan | 52 | m | 6300 | 33 | Ameica |
| zhang | fei | 48 | m | 7000 | 28 | china |
| guang | yu | 52 | m | 9000 | 35 | china |
+------------+-----------+------+--------+--------+-------------+---------+
'''
1.基础查询
'''
#in 散点集合查找
select *
from worker
where age in (19,20,40)
#between 范围查找
select *
from worker
where income
between 4500 and 17500;
#like 模糊匹配 , %匹配一串,_ 匹配一个字符
select *
from worker
where first_name
like 'M%'
#多条件查询
select *
from worker
where first_name!='Mac' and
income between 8000 and 15000;
'''
2.查询模型
'''
#列的使用查询
select * from worker
where 1<2;
select first_name,force_value-age
from worker
where 10*force_value*age>income;
#输出表达式子
select first_name,(force_value-age) as realStrong #新的名字
from worker
where 10*force_value*age>income;
#substring /concat 字符串函数
select concat(first_name,substring(last_name,1)) #是起始位置
from worker;
'''
3.group 分组查询 以及常见统计量 min,max,sum,avg,count
'''
select min(age)
from worker;
select country,avg(income)
from worker
group by country;
'''
4.having,在计算之后选择性的输出,where之前就筛选了
'''
select country,avg(age)
from worker
group by country
having avg(force_value)>30;
#嵌套查询
select country from (
select country,avg(age),sum(age<40) as gk
from worker
group by country
having gk>2) as temp;
'''
5.order,针对结果集的排序
'''
#降序排列
select distinct * #distinct去重查询
from worker
order by age desc;
#与limit搭配使用
select *
from worker
order by age
limit 2,2; # off_set, num
select *
from worker
order by age asc, income desc; #按照多列排序
'''
6.子查询
'''
#where 类型
#where col=(内查询,sql) 一个值
select *
from worker
where income=
(select max(income) from worker);
#where col in (内查询,sql) 多个值
select *
from worker
where income in
(select max(income) from worker group by country);
#from 类型
#构建了一个新表,在把新表作为查询表
select country from (
select country,avg(age),sum(age<40) as gk
from worker
group by country
having gk>2) as temp;
#exit 类型
#发生在两个表间,类似于连表查询
select first_name
from table_1
exit(select * from table_2 where table_2.country=table_1.country)