MySql基础操作大全加案例02

13、分组
        select * from 表名 group by (你要分组的字段)
        mysql> select * from bbs_user group by (province);
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| id | username  | password | sex  | status | ctime | rip     | price | province  | age  |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
|  6 | 候飞洋    | 3333     | NULL |      0 |  NULL |    NULL |   400 | 北京      |  100 |
|  9 | 候飞洋    | 3333     | NULL |      0 |  NULL |    NULL |   444 | 河北      |   44 |
|  1 | 程冠希    | 123      |    1 |      0 |  1234 | 1231231 |   260 | 河南      |   10 |
|  2 | 程冠希    | 123      |    1 |      0 |  1234 | 1231231 |   260 | 河南北    |   30 |
|  7 | 张柏芝    | 456      | NULL |      0 |  NULL |    NULL |   500 | 深圳      |   20 |
|  4 | 张柏芝    | 456      | NULL |      0 |  NULL |    NULL |   550 | 澳门      |   40 |
|  3 | 谢霆锋    | 123456   | NULL |      0 |  NULL |    NULL |   300 | 香港      |    1 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
mysql> select count(*),province from bbs_user group by (province);
+----------+-----------+
| count(*) | province  |
+----------+-----------+
|        3 | 北京      |
|        1 | 河北      |
|        1 | 河南      |
|        1 | 河南北    |
|        1 | 深圳      |
|        4 | 澳门      |
|        5 | 香港      |
+----------+-----------+
7 rows in set (0.00 sec)

    14、as 起别名
    mysql> select count(*) as total,province from bbs_user group by (province);
    +-------+-----------+
    | total | province  |
    +-------+-----------+
    |     3 | 北京      |
    |     1 | 河北      |
    |     1 | 河南      |
    |     1 | 河南北    |
    |     1 | 深圳      |
    |     4 | 澳门      |
    |     5 | 香港      |
    +-------+-----------+
    7 rows in set (0.00 sec)

    15、having 对结果集进行过滤
    mysql> select count(*) as total , province from bbs_user group by province having total > 3;
    +-------+----------+
    | total | province |
    +-------+----------+
    |     4 | 澳门     |
    |     5 | 香港     |
    +-------+----------+
    2 rows in set (0.00 sec)
    16、多表查询

        （1）select * from blog group by city; 这么去操作没有任何的意义
        （2）隐式内连接
            select 表1.字段 ，表2.字段 from 表1，表2 where 关联条件 
            mysql> select shop_user.username , shop_goods.name from shop_user , shop_goods where shop_user.gid = shop_goods.gid;
            +-----------+--------+
            | username  | name   |
            +-----------+--------+
            | 闫海鹏    | 坦克   |
            | 闫海鹏    | 飞机   |
            | 王栋      | 娃娃   |
            | 李裴纶    | 狙击   |
            | 苍老师    | 炸弹   |
            +-----------+--------+
            5 rows in set (0.00 sec)
        （3）左连接 （以左表为基础去查询）
            mysql> select username , name from shop_user left join shop_goods on shop_user.gid = shop_goods.gid;
    +-----------+--------+
    | username  | name   |
    +-----------+--------+
    | 闫海鹏    | 坦克   |
    | 闫海鹏    | 飞机   |
    | 王栋      | 娃娃   |
    | 李裴纶    | 狙击   |
    | 苍老师    | 炸弹   |
    | 王二小    | NULL   |
    +-----------+--------+
    6 rows in set (0.00 sec)
        （4）右链接
        mysql> select username , name from shop_user right join shop_goods on shop_user.gid = shop_goods.gid;
        +-----------+--------+
        | username  | name   |
        +-----------+--------+
        | 闫海鹏    | 坦克   |
        | 闫海鹏    | 飞机   |
        | 王栋      | 娃娃   |
        | 李裴纶    | 狙击   |
        | 苍老师    | 炸弹   |
        | NULL      | 大炮   |
        +-----------+--------+
        6 rows in set (0.00 sec)

        （5）嵌套查询 一般情况下不用 效率低 优化问题

        mysql> select username from shop_user where gid in(select gid from shop_goods);
        +-----------+
        | username  |
        +-----------+
        | 闫海鹏    |
        | 维权二  |
        | 王栋      |
        | 李裴纶    |
        | 苍老师    |
        +-----------+
        (6) union (稍微了解一下)
        mysql> select * from shop_goods left join shop_user on shop_goods.gid = shop_user.gid
            -> union
            -> select * from shop_goods right join shop_user on shop_goods.gid = shop_user.gid;
        +------+--------+-------+--------------+------+-----------+----------+------+
        | gid  | name   | price | category     | id   | username  | password | gid  |
        +------+--------+-------+--------------+------+-----------+----------+------+
        |    3 | 坦克   |   300 | 人妖用品     |    1 | 闫海鹏    | 123      |    3 |
        |    2 | 飞机   | 20000 | 女性用品     |    2 | 闫海鹏    | 456      |    2 |
        |    1 | 娃娃   |   200 | 男性用品     |    3 | 王栋      | 888      |    1 |
        |    5 | 狙击   |    10 | 儿童用品     |    4 | 李裴纶    | 999      |    5 |
        |    4 | 炸弹   |   100 | 老年用品     |    5 | 苍老师    | 555      |    4 |
        |    6 | 大炮   |    20 | 军队用品     | NULL | NULL      | NULL     | NULL |
        | NULL | NULL   |  NULL | NULL         |    6 | 王二小    | 22222    |    7 |
        +------+--------+-------+--------------+------+-----------+----------+------+
        7 rows in set (0.01 sec)

        （7）显示内连接
            mysql> select username , name  from shop_user inner join shop_goods on shop_user.gid = shop_goods.gid;
            +-----------+--------+
            | username  | name   |
            +-----------+--------+
            | 闫海鹏    | 坦克   |
            | 闫海鹏    | 飞机   |
            | 王栋      | 娃娃   |
            | 李裴纶    | 狙击   |
            | 苍老师    | 炸弹   |
            +-----------+--------+
            5 rows in set (0.00 sec)
        （8）
            mysql> update bbs_user set price = price-10 where id = 15;
            Query OK, 1 row affected (0.00 sec)
            Rows matched: 1  Changed: 1  Warnings: 0

            mysql> select * from bbs_user;  

        （9）两个表同时更新
        mysql> update shop_user as u , shop_goods as g set u.username = '海鹏哥哥' , g.name = '大鸟' where u.gid = g.gid and u.gid = 1;
Query OK, 2 rows affected (0.00 sec)

        【关于权限的问题】
            1、创建一个后台登录的用户
            mysql> create user'haipenggege'@'localhost' identified by 'woaini';
            2、赋值权限
            grant select on *.* to 'haipeng'@‘localhost’;
            3、删除权限
            revoke select on *.* from 'haipeng'@‘localhost’;
            4、删除用户
            drop user 'haipang'@'localhost'

        【导出导入数据】
            导出库
                C:\Users\Lee>mysqldump -uroot -p python1703 > c:/python1703.sql

            导入库
                C:\Users\Lee>mysql -h localhost -u root -p wangdong < c:/python1703.sql

            导出单张表
                C:\Users\Lee>mysqldump -uroot -p python1703 shop_user > c:/shop.sql

            导出表结构
                myslqldump -uroot -p -d --add-drop-table 库名字 > 你要保存的文件名

            注意:如果你的数据量的时候我不建议用图形界面去导入数据 用msyql的命令 source去执行


            事务：
                双向成功才成功单项成功是失败


                第一步：
                    查看你的引擎是否支持事务
                第二步：
                    把你的自动提交改成手动提交

                    set autocommit = 0；
                第三步：
                    开启一个事务
                    begin

                第四步：写你的sql语句

                第五步：
                    如果你确定是成功了 就手动去提交

                    如果你觉得是不成功 在回滚回来了


                处理结果集：

                mysqli_fetch_assoc()  //返回一个一维关联数组 一行一行往下读
                mysqli_fetch_row() //返回一个一维的索引数组 一行一行往下读
                mysqli_fetch_array() //返回一个既有索引又有关联的一维数组
                mysqli_num_rows() //返回你的结果集的总数
                mysqli_affected_rows($link) //返回的是你当前的sql的受影响行数
                mysqli_insert_id($Link);   //返回你当前插入数据的自增的id值