刘道成 mysql笔记_刘道成 mysql 学习笔记2

仿ecshop建库

create database mugua charset utf8;

use mugua;

create table goods(

goods_id int primary key auto_increment,

cat_id smallint not null default 0,

goods_sn char(15) not null default '',

goods_name varchar(30) not null default '',

click_count mediumint unsigned not null default 0,

brand_id smallint not null default 0,

goods_number smallint not null default 0,

marcket_price decimal(7,2) not null default 0.00,

shop_price decimal(7,2) not null default 0.00,

add_time int unsigned not null default 0

)charset utf8;

从一个数据库导数据到另一个数据库的方法

会碰到中文出现乱码 先运行这个语句 set names gbk

insert into mugua.goods

select

goods_id,cat_id,goods_sn,goods_name,click_count,

brand_id,goods_number,market_price,shop_price,add_time

from shop.goods;

create table category(

cat_id smallint primary key auto_increment,

cat_name varchar(30) not null default '',

parent_id smallint not null default 0

)charset utf8;

insert into mugua.category

select

cat_id,cat_name,parent_id

from shop.category;

create table brand(

brand_id smallint primary key auto_increment,

brand_name varchar(30) not null default''

)charset utf8;

insert into mugua.brand

select

brand_id,brand_name

from shop.brand;

(一)where 表达式在哪一(几)行成立 就把那一(几)行取出来

select * from goods where cat_id=3;

select * from goods where cat_id !=3;

等价于

select * from goods where cat_id <>3;

select * from goods where cat_id>3;

select * from goods where cat_id<3;

select * from goods where cat_id>=3;

select * from goods where cat_id<=3;

select * from goods where cat_id in(3,4);

select * from goods where cat_id between 3 and 5;

select * from goods where cat_id >3 or cat_id <=2;

select * from goods where cat_id>=3 and cat_id<=6;

(二)group by 分组查询

group by 与max min sum avg count连用

1 查出最贵商品价格

select max(shop_price) from goods;

2 查出最便宜商品价格

select min(shop_price) from goods;

3 查出商品总量

select sum(goods_number) from goods;

4 查出所有商品平均价格

select avg(shop_price) from goods;

5,查出所有商品总类

select count(goods_id) from goods;

#查询每个栏目下 要用到group by

1 查出最贵商品价格

select cat_id,max(shop_price) from goods group by cat_id;

2 查出最便宜商品价格

select cat_id,min(shop_price) from goods group by cat_id;

3 查出商品总量

select cat_id,sum(goods_number) from goods group by cat_id;

4 查出所有商品平均价格

select cat_id,avg(shop_price) from goods group by cat_id;

5,查出所有商品总类

select cat_id,count(goods_id) from goods group by cat_id;

#把列名当变量就可以进行运算了 可以写出更加高效的sql语句

#查询每个栏目下积压的货款

select cat_id,sum(shop_price*goods_number) from goods group by cat_id;

#查询出每个商品比市场价低多少

select goods_id,goods_name,marcket_price-shop_price as cha from goods;

(三)having查询

where 发挥作用的时间在查表结果之前

having 是筛选 是对查询结果的进一步选择

1，查询比市场价省200元以上得物品价格以及所省的钱

select goods_name,goods_id,marcket_price-shop_price as sheng from goods having sheng>200;

如果写成

select goods_name,goods_id,marcket_price-shop_price as sheng from goods where sheng>200;

是错误的

当然也可以写成

select goods_name,goods_id,marcket_price-shop_price as sheng from goods where marcket_price-shop_price>200;

只不错计算了两次 不如having 好

2，查询积压货款超过 20000元货款的栏目 以及该货款

select cat_id,sum(shop_price*goods_number) as jiya from goods group by cat_id having jiya>20000;

更多资源 http://www.taobtbw.com/

(四)order by 和limit查询

1，按价格由高到低排序

select goods_name,goods_id,shop_price from goods order by shop_price desc;

2，按发布时间由早到晚排序

select goods_id,goods_name,add_time from goods order by add_time;

3，按栏目由低到高排序，栏目内部按价格由高到低排序

select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc;

limit 的用法 limit在语句的最后 起到限制的作用

limit[offset]N

offset :偏移量

N;去处的条目数量

offset不写则相当于 limit 0，N

是从0 开始算的 而不是1

4,取出价格最高的前三名商品

select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;

5,取出点击量前三名到前5名的商品

select goods_id,goods_name,click_count from goods order by click_count desc limit 2,3;

思考题 ：取出每个栏目下最贵的商品

错误1：select goods_id,goods_name,cat_id,max(shop_price) from goods group by cat_id 错误原因是用了聚合函数 而其他栏目以第一次遇到的为准

错误2： select goods_id,goods_name,cat_id,shop_price from goods group by cat_id order by shop_price desc;这只是对分组后的排序 而不是对每一个栏目的排序

错误3：select goods_id,goods_name,cat_id,shop_price from goods order by shop_price desc group by cat_id ; 语法错误 因为查询字句是有顺序性的

顺序如下 where ， group by， having ， order by ，limit

正确：select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc ) as temp group by cat_id;

(四)

(1)where from exists 子查询

1查询出最新商品(以最大编号为准)

select goods_name,goods_id from goods where goods_id=(select max(goods_id) from goods);

2,查询出编号为19的商品的栏目名称(待定)

3，用where子查询出每个栏目下的最新产品(以id号最大为准)

select goods_name,goods_id,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id ); 这里后面不能再加其他字段 否则会出错

4,用from子查询查出每个栏目下最新的产品(以id号最大为准)

select * from (select goods_name,goods_id,cat_id from goods order by cat_id asc,goods_id desc) as temp group by cat_id;

5,exists 子查询 在category表中查询有商品的栏目 (先从category表中找出cat_id 然后与goods逐行比较)

select cat_id,cat_name from category where exists (select * from goods where category.cat_id=goods.cat_id);

注意几个问题

1：from 子查询是把from后的查询结果当成一张临时表 一定要为临时表创建一个名字 用as + 名字 表示

2；from子查询中的* 指的是from表中的字段 而不是原表的字段。

3.where 子查询是把内层查询的结果当外层查询的比较条件

4.exists 查询是把外层的查询结果拿到内层，看内层是否成立

更多资源请访问

http://www.17xuexiba.com/