ecshop mysql 端口_mysql常用语句练习-基于ecshop2.7.3数据库(1)

SELECT * FROM ecs_goods WHERE goods_id = 1;

SELECT goods_id, goods_name FROM ecs_goods WHERE goods_id = 1;

SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id != 3;

SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id <> 3;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price > 3000;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price <= 100;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id = 4 OR cat_id = 3;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id IN( 3, 4 );

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price BETWEEN 100 AND 858;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id != 3 AND cat_id != 4

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id NOT IN( 3, 4 );

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id NOT BETWEEN 3 AND 4

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE ( shop_price > 100 AND shop_price < 300 )

OR ( shop_price > 1000 AND shop_price < 3000 );

SELECT goods_id,cat_id,goods_name,shop_price,click_count FROM ecs_goods WHERE (cat_id = 3 ) AND ( shop_price >= 1000 AND shop_price <= 3000 )

AND ( click_count > 5 )

SELECT * FROM ecs_goods WHERE cat_id IN ( 2, 3, 4, 5 )

SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE goods_name LIKE '诺基亚%'

#把表中字段num取值范围为20~29之间的值变成20【注：ecshop2.7.3没有这个ecs_mian1这个表，可以自己创建一个，他就一个字段num, int类型，然后填充一些测试数据】

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num >= 20 AND num <= 29

#把表中字段num取值范围为30~39之间的值变成30

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num BETWEEN 30 AND 39

#以”诺基亚"开头的商品

SELECT goods_id, goods_name FROM ecs_goods WHERE goods_name LIKE '诺基亚%';

#截取"诺基亚"后面的商品名称，并用"小米"开始 连接起来

SELECT goods_id, CONCAT( '小米', SUBSTRING( goods_name, 4 ) ) FROM ecs_goods WHERE goods_name LIKE '诺基亚%';

#截取"诺基亚"后面的商品名称，并用"小米"开始 连接起来后更新

UPDATE ecs_goods SET goods_name = CONCAT( '小米', SUBSTRING( goods_name, 4 ) ) WHERE goods_name LIKE '诺基亚%'

#以上面的语句相反

UPDATE ecs_goods SET goods_name = CONCAT( '诺基亚', SUBSTRING( goods_name, 4 ) ) WHERE goods_name LIKE '小米%'

关于NULL:

SELECT NULL = NULL

SELECT NULL != NULL

#建表

CREATE TABLE ghost_user_info(

id INT( 3 ),

user_name VARCHAR( 20 )

)CHARSET utf8 ENGINE MYISAM;

#插入测试数据

INSERT INTO ghost_user_info VALUES( 1, '张三' );

INSERT INTO ghost_user_info VALUES( 2, NULL );

#user_name不等于NULL的记录，不能这样写

SELECT * FROM ghost_user_info WHERE user_name != NULL

#user_name不等于NULL的记录，应该这样写

SELECT * FROM ghost_user_info WHERE user_name IS NOT NULL

#同样的，等于NULL的记录应该是 IS NULL而不是 = NULL

SELECT * FROM ghost_user_info WHERE user_name = NULL

SELECT * FROM ghost_user_info WHERE user_name IS NULL

#统计函数

SELECT AVG( shop_price ) FROM ecs_goods;

SELECT MAX( shop_price ) FROM ecs_goods;

SELECT MIN( shop_price ) FROM ecs_goods;

SELECT COUNT(*) FROM ecs_goods;

SELECT SUM( shop_price ) / COUNT(*) FROM ecs_goods;

#积压货款

SELECT SUM( shop_price * goods_number ) FROM ecs_goods;

#统计每个分类的商品平均价格, group比较耗费资源(先按cat_id排序，再统计)

SELECT cat_id,AVG( shop_price ) FROM ecs_goods GROUP BY cat_id;

#查询每个分类下，商品的数量

SELECT cat_id, COUNT(*) FROM ecs_goods GROUP BY cat_id;

#每个分类下最贵的产品

SELECT cat_id, MAX(shop_price) FROM ecs_goods GROUP BY cat_id;

#本店价格比市场价格小200以上的商品

SELECT goods_id, goods_name, market_price - shop_price FROM ecs_goods WHERE market_price - shop_price > 200

#下面的语句，报错(Unknown column 'discount_price' in 'where clause' ),discount_price这个别名是结果集中(存在内存中)的，where后面的字段需要是磁盘表中的字段

SELECT goods_id, goods_name, ( market_price - shop_price ) AS discount_price FROM ecs_goods WHERE discount_price > 200

#可以用having关键字过滤查询出来的结果集

SELECT goods_id, goods_name, ( market_price - shop_price ) AS discount_price FROM ecs_goods WHERE 1 HAVING discount_price > 200

#多列排序

SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods ORDER BY cat_id ASC, shop_price DESC;

#查询最新的商品1

SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY goods_id DESC LIMIT 0,1

#下面两句组合，等于后面的where子查询

SELECT goods_Id, goods_name,cat_id FROM ecs_goods WHERE goods_id = 32

SELECT MAX( goods_id ) FROM ecs_goods;

#(where子查询)查询最新的商品2

SELECT goods_Id, goods_name,cat_id FROM ecs_goods WHERE goods_id = ( SELECT MAX( goods_id ) FROM ecs_goods );

#from子查询：每一个分类下，最大的产品id( 最新的产品 )

SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY cat_id ASC, goods_id DESC

#把上面的语句 当前一个临时的表，放在from后面 组成一个from子查询

SELECT goods_id, goods_name, cat_id FROM ( SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY cat_id ASC, goods_id DESC ) AS tmp GROUP BY cat_id

#查询有商品的分类

SELECT * FROM ecs_category c WHERE EXISTS( SELECT * FROM ecs_goods g WHERE g.cat_id = c.cat_id );

#查询没有商品的分类

SELECT * FROM ecs_category c WHERE not EXISTS( SELECT * FROM ecs_goods g WHERE g.cat_id = c.cat_id );

参考：

MySQL EXISTS 和 NOT EXISTS 子查询语法如下：

SELECT … FROM table WHERE EXISTS (subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。

#查出主队与客队在2016-06-01~2016-07-01的名称以及比赛结果

create table m(

mid int,

hid int,

gid int,

mres varchar(10),

matime date

)engine myisam charset utf8;

create table t (

tid int,

tname varchar(20)

)engine myisam charset utf8;

insert into m

values

(1,1,2,'2:0','2006-05-21'),

(2,2,3,'1:2','2006-06-21'),

(3,3,1,'2:5','2006-06-25'),

(4,2,1,'3:2','2006-07-21');

insert into t

values

(1,'国安'),

(2,'申花'),

(3,'布尔联队');

SELECT m.*, t1.`tname`, t2.`tname` FROM m INNER JOIN t ASt1ON m.`hid` = t1.`tid` INNER JOIN t AS t2 ON m.`gid` =t2.`tid`WHERE m.`matime` BETWEEN '2006-06-01' AND '2006-07-01'