show databases;
drop database world;
create database test char set utf8;
use test;
show tables;
select * from account_log;
select * from goods;
select goods_id,goods_name,shop_price from goods where cat_id !=3;
select goods_id,goods_name,shop_price from goods where shop_price <= 100;
select goods_id,goods_name,cat_id from goods where cat_id in(4,11);
select goods_id,goods_name,cat_id from goods where cat_id = 4 or cat_id = 11;
select goods_id,goods_name,shop_price from goods where shop_price between 100 and 500;
select goods_id,goods_name,cat_id from goods where cat_id !=4 && cat_id != 11;
select goods_id,goods_name,shop_price from goods where shop_price >=100 and shop_price <= 300 or shop_price >= 3000 and shop_price <= 4000;
select goods_id,goods_name,shop_price,cat_id,click_count from goods where cat_id = 3 and (shop_price < 1000 or shop_price > 3000 )and click_count > 5;
select goods_id,goods_name from goods where goods_name like '诺基亚%';
select goods_id,goods_name from goods where goods_name like '诺基亚N__';
select goods_id,goods_name,(market_price - shop_price) as discount from goods where 1;
select goods_id,goods_name,market_price from goods ;
select goods_id,goods_name,(market_price - shop_price) as discount from goods where cat_id != 3;
select goods_id,goods_name,(market_price - shop_price) as discount from goods where (market_price - shop_price) > 200;
create table mianshi(num int);
show tables;
insert into mianshi values
(3),(12),(15),(25),(23),(29),(34),(37),(32),(45),(48),(52);
select * from mianshi;
show variables like 'sql_safe_updates';
set sql_safe_updates=0;
update mianshi set num = floor(num/10)*10 where num between 20 and 39;
select goods_id,goods_name from goods where goods_name like '诺基亚%';
select goods_id,goods_name,concat('HTC',substring(goods_name,4)) as htcgoods from goods where goods_name like '诺基亚%';
select max(shop_price) from goods;
select min(shop_price) from goods;
select sum(goods_number) from goods;
select avg(shop_price) from goods;
select count(goods_id) from goods;
select count(*) from goods;
select goods_number from goods;
select sum(goods_number) from goods where cat_id = 3;
select cat_id ,sum(goods_number) from goods group by cat_id;
select goods_id,goods_name,cat_id from goods where cat_id !=4 && cat_id != 11;
select goods_id,goods_name,cat_id from goods where cat_id not in (4,11);
select goods_id,goods_name,shop_price from goods where shop_price >= 100 and shop_price <= 300 or shop_price >=3000 and shop_price <= 4000;
select cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);
select * from goods where sname is null;
select goods_id,goods_name,goods.cat_id,cat_name from goods,category;
select goods_id,goods_name,goods.cat_id,category.cat_id,cat_name from goods,category where goods.cat_id = category.cat_id;
select goods_id,goods_name,goods.cat_id,category.cat_id,cat_name from goods left join category on goods.cat_id = category.cat_id where goods.cat_id = 4;
select goods_id ,goods_name,shop_price from goods where shop_price < 20 union select goods_id,goods_name,shop_price from goods where shop_price >3000;
select goods_id,goods_name,shop_price from goods where shop_price <20 or shop_price >3000;
select goods_id ,goods_name ,cat_id from goods where cat_id = 3;
select goods_id ,goods_name ,cat_id from goods where cat_id = 4;
select goods_id ,goods_name ,cat_id,shop_price from goods where cat_id = 3 union select goods_id ,goods_name ,cat_id ,shop_price from goods where cat_id = 4 order by shop_price desc;
select goods_id ,goods_name,cat_id,shop_price from goods where cat_id = 3 order by shop_price desc limit 3;
select goods_id ,goods_name,cat_id,shop_price from goods where cat_id = 4 order by shop_price desc limit 2;
(select goods_id ,goods_name,cat_id,shop_price from goods where cat_id = 3 order by shop_price desc limit 3)
union
(select goods_id ,goods_name,cat_id,shop_price from goods where cat_id = 4 order by shop_price desc limit 2);
select * from a;
