Linux运维笔记
我们一直在努力

MySQL入门练习

创建商品表goods

CREATE TABLE `goods` (
    `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT primary key,
    `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
    `goods_sn` varchar(60) NOT NULL DEFAULT '',
    `goods_name` varchar(120) NOT NULL DEFAULT '',
    `click_count` int(10) unsigned NOT NULL DEFAULT '0',
    `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
    `goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
    `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
    `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
    ) add_time int unsigned not null default 0) CHARSET=utf8;

创建品牌表brand

CREATE TABLE `brand` (
    `brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `brand_name` varchar(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`brand_id`)
    ) CHARSET utf8;

创建商品栏目表category

CREATE TABLE `category` (
    `cat_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `cat_name` varchar(90) NOT NULL DEFAULT '',
    `parent_id` smallint(5) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`cat_id`)
    ) CHARSET utf8;

导入数据

insert into booboo.goods 
select 
goods_id,cat_id,goods_sn,goods_name,
click_count,brand_id,goods_number,
market_price,shop_price,add_time 
from ecshop.ecs_goods;

insert into booboo.category
select
cat_id,cat_name,parent_id
from ecshop.ecs_category;

insert into booboo.brand
select
brand_id,brand_name
from ecshop.ecs_brand;

查看表信息:

MariaDB [booboo]> select * from brand;
+----------+----------------+
| brand_id | brand_name     |
+----------+----------------+
|        1 | 诺基亚         |
|        2 | 摩托罗拉       |
|        3 | 多普达         |
|        4 | 飞利浦         |
|        5 | 夏新           |
|        6 | 三星           |
|        7 | 索爱           |
|        8 | LG             |
|        9 | 联想           |
|       10 | 金立           |
|       11 |   恒基伟业     |
+----------+----------------+
11 rows in set (0.12 sec)

MariaDB [booboo]> select * from category;
+--------+---------------------------+-----------+
| cat_id | cat_name                  | parent_id |
+--------+---------------------------+-----------+
|      1 | 手机类型                   |         0 |
|      2 | CDMA手机                  |         1 |
|      3 | GSM手机                   |         1 |
|      4 | 3G手机                    |         1 |
|      5 | 双模手机                  |         1 |
|      6 | 手机配件                  |         0 |
|      7 | 充电器                    |         6 |
|      8 | 耳机                      |         6 |
|      9 | 电池                      |         6 |
|     11 | 读卡器和内存卡              |         6 |
|     12 | 充值卡                    |         0 |
|     13 | 小灵通/固话充值卡         |        12 |
|     14 | 移动手机充值卡            |        12 |
|     15 | 联通手机充值卡            |        12 |
+--------+---------------------------+-----------+
14 rows in set (0.02 sec)

MariaDB [booboo]> select * from goods limit 5;
+----------+--------+-----------+-------------------+-------------+----------+--------------+--------------+------------+------------+
| goods_id | cat_id | goods_sn  | goods_name        | click_count | brand_id | goods_number | market_price | shop_price | add_time   |
+----------+--------+-----------+-------------------+-------------+----------+--------------+--------------+------------+------------+
|        1 |      4 | ECS000000 | KD876             |           7 |        8 |            1 |      1665.60 |    1388.00 | 1240902890 |
|        3 |      8 | ECS000002 | 诺基亚原装5800耳机  |           3 |        1 |           24 |        81.60 |      68.00 | 1241422082 |
|        4 |      8 | ECS000004 | 诺基亚N85原装充电器 |           0 |        1 |           17 |        69.60 |      58.00 | 1241422402 |
|        5 |     11 | ECS000005 | 索爱原装M2卡读卡器  |           3 |        7 |            8 |        24.00 |      20.00 | 1241422518 |
|        6 |     11 | ECS000006 | 胜创KINGMAX内存卡  |           0 |        0 |           15 |        50.40 |      42.00 | 1241422573 |
+----------+--------+-----------+------------------ +-------------+----------+--------------+--------------+------------+------------+

查询5种字句:

where,group,having,order by,limit

比较运算符:
    <,<=,>,>=,=,!=,<>,in,between
    #in (va1,va2,va3..vaN),值等于1->N任意一个都行
    #between va1 and va2,表示在va1和va2之间

逻辑运算符:
    and,or,not
    &&,||,!

**模糊查询: **

like 
% 任意字符
_ 单个字符

练习:
    goods表中的所有商品
    goods表中goods_id小于12的商品
    goods表中市场价比本店价格超过200的商品的id,名字,价格
    本店价格小于3000的商品
    本店价格小于等于2000的商品
    本店价格等于2000的商品
    本店价格大于3010的商品
    本店价格大于等于3010的商品
    本店价格不等于3010的商品
    不属于第三号栏目的所有商品
    属于第三号栏目或者第四号栏目的所有商品
    本店价格在2000到3000之间的所有商品
    本店价格在3000到5000之间,或者500到1000的商品
    不属于第4和第5栏目的商品
    商品名以诺基亚开头的商品
    商品名以诺基亚开头后面只有三个字符的商品

group 常用于分类统计,求平均等场景,与以下聚合函数配合使用:

注释:
    max 最大
    min 最小
    sum 总和
    avg 平均

练习:

    查询最贵的商品价格
    查询最大的商品编号
    查询最便宜的商品价格
    查询最小商品编号
    该店所有商品的库存总量
    查询所有商品的平均价
    该店一共有多少个商品
    查询每个栏目下面最贵商品价格;最低商品价格;商品平均价格;商品库存量;商品种类

having和group综合运用查询

练习:

    查询该店的商品比市场价格所节省的价格
    查询每个商品所积压的货款(提示:库存*单价)
    查询该店积压的总货款
    查询该店每个栏目下面积压的货款
    查询比市场价省钱200元以上的商品及该商品锁省的钱(where和having分别实现)
    查询积压贷款超过2w的栏目,以及栏目积压的货款

order by 与 limit 查询

练习: 

    按价格由高到底排序
    按发布时间由早到晚排序
    按栏目由低到高排序,栏目内部按价格由高到低排序
    取出价格最高的前三名商品
    取出点击量前三名到前五名的商品

# limit a,b 代表从索引位a开始,一共取b个记录,而索引是从0开始  
# 如果不写a,则相当于 limit 0,b

使用误区

找出每个栏目下最贵的商品,商品名,商品所在栏目,商品id,商品价格:

这个问题没有办法用一个select完成,需要用到view或子查询

create view a as select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
select * from a group by cat_id;

注意5个字句的顺序,where > group by > having > order by > limit
group by默认输出行只会选择组中的第一行

子查询3种:

where 型{

    # 把内层查询的结果作为外层查询的比较条件
    # 查询最大、最贵商品

    查询最新的商品(以id最大为最新,不用order by)
    每个栏目下最新的商品
    每个栏目下最贵的商品

from 型

    # 把内层查询的结果作为外层查询的临时表
    # 查询每个栏目下最新、最贵商品

    每个栏目下最新的商品

exits 型

    # 把外层的查询结果,拿到内层,看内存查询是否成立
    # 查询有商品的栏目

    查有商品的栏目

拓展题:

设有成绩表stu,如下:
+--------+--------+-------+
| name   | class  | score |
+--------+--------+-------+
| 张三   | 数学   |    90 |
| 张三   | 语文   |    50 |
| 张三   | 地理   |    40 |
| 李四   | 语文   |    55 |
| 李四   | 政治   |    45 |
| 王五   | 政治   |    30 |
+--------+--------+-------+
试查询两门及两门以上不及格的学生的平均分

合并查询练习:

union 联合{

    # 作用:把2个查询连接在一起
    # 要求:两次查询的列数一直
    # 推荐:查询的每一个列,相对应的列类型也一样
    # 可以来自多张表,例如ecshop中的留言板
    查询ecshop中用户ecshop的评论和投诉{

    # 如果不同的语句中有完全相同的行,则会合并为一行,即去重
    # 如果不想自动去重复,则需要使用all参数,union all
    获取a表和b表中的id和num,如果id相同,则获取num之和{
        MariaDB [booboo]> select * from a;
        +------+------+
        | id   | num  |
        +------+------+
        | a    |    5 |
        | b    |   10 |
        | c    |   15 |
        | d    |   10 |
        +------+------+

        MariaDB [booboo]> select * from b;
        +------+------+
        | id   | num  |
        +------+------+
        | b    |    5 |
        | c    |   15 |
        | d    |   20 |
        | e    |   99 |
        +------+------+

    # 如果包含order by或limit子句,需要加小括号
    # 尽量不要在子句中单独用,而是整体使用,对最终结果来排序
    取第四栏目的商品,价格降序偏排列,还想去第五栏的商品,价格也降序,最终按价格降序,union连接{

    取第三个栏目价格前3高的商品和第四个栏目价格前两高的商品,用union实现

连接查询:

关系型数据库的数学模型为集合set
1) set的特性:无序,唯一
2)一张表就是一个集合,一行记录就是一个元素——理论上不可能出现完全相同的行
3)表中有一个隐藏列row_id是不同的
集合的运算
1)在数学中,两个集合X和Y的笛卡尓乘积(Cartesian product),又称直积,表示为X × Y,
2)第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
3)假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0), (a,1), (a,2), (b,0), (b,1), (b, 2)}。

table a 7 行,table b 8 行 ,这两表相乘一共 7*8=56行

取出商品的商品名,商品所在栏目名{
    select goods_name,cat_name,goods.cat_id,category.cat_id from goods join category on goods.cat_id = category.cat_id limit 4;
    select goods_name,cat_name,goods.cat_id,category.cat_id from goods,category where goods.cat_id = category.cat_id limit 4;
}


python 代码实现笛卡尔积{
for i in A:
    for j in B:
        print '{}{}'.format(i,j)
print 'A*B的元素个数为{}'.format(len(A)*len*(B))
}

python 代码实现左连接{
    test=0  
    for i,j in A.items():
        for m,n in B.items():
            if i==m:
                print '{} {} {} {}'.format(i,j,m,n)
            else:
                test=test+1
        if test==len(B):
        print '{} {} NULL NULL '.format(i,j)
}

python 代码实现右连接{
    test=0  
    for a,b in B.items():
        for i,j in A.items():
            if a==i:
                print '{} {} {} {}'.format(i,j,a,b)
            else:
                test=test+1
        if test==len(A):
        print 'NULL NULL {} {}'.format(a,b)
}


左连接{
    # select col1,col2,colN from A left join B ON A.col = B.col
    # 左连接以左表为准,去右表匹配,找不到匹配用NULL补齐

}

右连接{
    # select col1,col2,colN from A right join B ON A.col = B.col
    # 右连接以右表为准,去左表匹配,找不到匹配用NULL补齐

}

内连接{
    # 内连接,查询左右表都有的数据,即:不要左、右中NULL的部分
    # 左右连接的交集
    # 左右连接目前没有并集,可以用union连接 
}

查goods_id,cat_id,cat_name,brand_id,brand_name,goods_name{
        select goods_id,goods.cat_id,cat_name,goods.brand_id,brand_name from goods left join (category,brand) on (goods.cat_id=category.cat_id and goods.brand_id=brand.brand_id);
    }

列的增删改:

alter table 表名 add 列声明 [after col_name | first]