mysql基础查询

#!/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)