刘道成 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： selec
刘说成 mysql 学习笔记2
