ＭySQL-笔记003

1.SQL查询

1.总结
    select 聚合函数


2.group by : 给查询的结果进行分组
    1.计算每个国家的平均攻击力
        select country,avg(gongji) from sanguo group by country;
        先分组          再聚合          去重
    2.查找所有国家英雄中，英雄数量最多的国家的前２名，显示国家名称和英雄数量
        mysql> select country,count(id) from sanguo 
            -> group by country
            -> order by count(id) desc
            -> limit 2;

3.having语句 : 对分组聚合后的结果进行进一步的筛选
    1.找出平均攻击力＞105的国家的前２名，显示国家名称和平均攻击力
        mysql> select country,avg(gongji) from sanguo
            -> group by country 
            -> having avg(gongji)>105
            -> order by avg(gongji) desc
            -> limit 2;
    2.注意
        having语句通常和group by语句联合使用，having语句尼补了where关键字
        不能与聚合函数使用的不足，where只能操作表中实际存在字段

4.distinct语句 : 不显示字段的重复值
    1.表中有那些国家
    select distinct country from sanguo;
    2.计算蜀国有多少个英雄
    select count(distinct id) as num from sanguo where country='蜀国';

5.  (1)查询表记录时做数学运算(+,-,*,/,%)
        1.查询时所有英雄攻击力翻倍
        select name,gongji*2 as fanbei from sanguo;
        update sanguo set gongji=gongji*2;
    (2)嵌套查询(子查询)
        1.定义：把内层的查询结果作为外层的查询条件
        2.select ... from 表名 where 字段名 运算符 (查询);
            update sanguo set gongji=gongji/2;
        3.把攻击值小于平均攻击值的英雄名字和攻击值显示
            1.先算平均攻击值
            update sanguo set gongji=gongji/2;
            2.
            select name,avg(gongji,where gongji<(select avg(gongji)) from  sanguo
            where country
            3.嵌套查询实现
            select name,gongji from sanguo where
            fongji<(select avg(gongji)) from sanguo)
        4.找出每个国家攻击力最高的英雄的名子和攻击值
            select name,gongji from sanguo where
            (country,gongji) in (select country,max(gongji) from sanguo group by country);           
            #("蜀国",1005) in (("蜀国,1005"),("魏国",666),("吴国",100))
    (3).多表查询
        1.笛卡尔积: select ... from 表1,表2;    #不加where条件
        2.多表查询:                             ＃加where条件
        3.显示省，市详细信息
            河北省　石家庄市
            河北省　廊坊市
            广东省　深圳市
            mysql> select sheng.s_name,city.c_name from sheng,city 
                -> where
                -> sheng.s_id=city.cfather_id;
        4.显示省，市，县详细信息
            mysql> select sheng.s_name,city.c_name,xian.x_name from sheng,city,xian
                -> where
                -> sheng.s_id=city.cfather_id 
                -> and
                -> city.c_id=xian.xfather_id;
    (4).连接查询
        1.内连接(inner join:只显示满足匹配条件的结果)
            1.select ... from 表1 inner join 表2 on 条件 inner join 表3 on 条件....;
            2.显示省，市详细信息
                mysql> select sheng.s_name,city.c_name from sheng inner join city 
                    -> on
                    -> sheng.s_id=city.cfather_id;
            3.显示省，市，县详细信息
                mysql>select sheng.s_name,city.c_name,xian.x_name from sheng 
                    -> inner join city
                    -> on sheng.s_id=city.cfather_id 
                    -> inner join xian 
                    -> on city.c_id=xian.xfather_id;

        2.左外连接(left join:以左表为主显示查询结果)
            1.select ... from 表1 left join 表2 on 条件 left(right)(inner) join 表3 on 条件....;
            2.显示省，市详细信息，要求省全部显示
                mysql> select sheng.s_name,city.c_name from sheng
                    -> left join city
                    -> on sheng.s_id=city.cfather_id;
            3.显示省，市，县详细信息，要求市全部显示
                mysql> select sheng.s_name,city.c_name,xian.x_name from
                    -> sheng right join city
                    -> on sheng.s_id=city.cfather_id
                    -> 
                    -> left join xian 
                    -> on city.c_id=xian.xfather_id;

        3.右外连接(right join:以右表为主显示查询结果)
                mysql> select sheng.s_name,city.c_name from sheng
                    -> right join city
                    -> on sheng.s_id=city.cfather_id;
    (5).约束
        1.非空约束(NOT NULL)
            不允许该字段值有NULL记录
            create ...(
            name varchar(20) NOT NULL,    
            )
        2.默认约束(DEFAULT 值)
            插入记录时，不给该字段赋值，则使用默认值
            create ...(
            sex enum("M","F","S") not null default "S"
            )
6.索引
    1.定义：对数据表的一列或者多列的值进行排序的一种结构(BTree算法)
        注：最大三层
    2.索引的优点：加快数据检索速度
    3.索引的缺点：
        1.占用物理存储空间
        2.当对表中数据更新时，索引需要动态维护，降低维护速度
    4.索引示例
        查看变量状态:
            mysql> show variables like '%profil%';
        查看变量character:
            mysql> show variables like 'character_set_server'
        1.开启运行时间检测:
            mysql> set profiling=1;
        2.执行一条查询语句(无索引)
            mysql> select name from t1 where name='lucy99999';
        3.在name字段创建索引
            mysql> create index name on t1(name);
        4.再执行一条查询语句(有索引)
            mysql> select name from t1 where name='lucy99999';
        5.查看执行时间
            mysql> show profiles;
        6.关闭运行时间检测:
            mysql> set profiling=0;
7.索引分类
    1.普通索引(index)，唯一索引(unique)使用规则
        1.可创建多个字段
        2.普通索引无约束，唯一索引要求字段值不能重复，但可为NULL
        3.KEY标志:普通(MUL) 唯一(UNI)
        4.经常用查询的字段，where后，order by排序字段创建索引
    2.创建(只能一个一个的创建)
        create table t1(
            ...,
        index(字段名),
        index(字段名),
        unique(字段名),
        unique(字段名)
        )charset=utf-8;

        create [unique] index 索引名 on 表名(字段名);
    3.查看
        1.desc 表名; -->查看KEY标志，来判断是什么索引
        2.show index from 表名;  -->查看表全部索引
    4.删除
        drop index 索引名 on 表名;
    5.示例
        mysql> create table t2(
            -> id int,
            -> name varchar(20),
            -> phnum bigint,
            -> card char(18),
            -> index(name),
            -> unique(phnum)
            -> );
        mysql> create unique index card on t2(card);
        mysql> desc t2;
        mysql> show index from t2\G;
        mysql> drop index card on t2;
        mysql> drop index phnum on t2;

---

在线安装pymysql模块：sudo pip3 install pymysql
离线下载：
1.下载xxx.tar.gz
2.进入解压后的目录，找setep