关于NULL:

mysql常用言语演练-基于ecshop二.七.三数据库(一),mysqlecshop二.7.三

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~2玖中间的值产生20【注:ecshop2.七.三从未有过那个ecs_mian一那么些表,能够团结创办一个,他就一个字段num,
int类型,然后填充一些测试数据】

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

 #把表中字段num取值范围为30~3九里边的值形成30

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

 

#以”HTC”早先的货物

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

#截取”Motorola”前边的商品名称,并用”黑莓”开头 连接起来
SELECT goods_id, CONCAT( ‘小米’, SUBSTRING( goods_name, 4 ) ) FROM
ecs_goods WHERE goods_name LIKE ‘诺基亚%’;

#截取”HTC”后边的商品名称,并用”Samsung”初叶 连接起来后更新
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

#1致的,等于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;

 

#询问最新的商品一
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
#把上面包车型地铁语句 当前1个权且的表,放在from前边 组成3个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)来支配主查询的多寡结果是还是不是能够保存。

 

 

#查出主队与客队在201陆-0六-01~201陆-07-0一的称谓以及比赛结果

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 AS t1
ON 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'

 

SELECT * FROM ecs_goods WHERE goods_id = 1; SELECT goods_id,
goods_name FROM ecs_goods WHERE goods_id = 1;…

#from子查询:每1个分类下,最大的制品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 goods_id,cat_id,goods_name FROM ecs_goods WHERE goods_name
LIKE ‘诺基亚%’

#询问最新的商品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子查询)查询最新的商品二
SELECT goods_Id, goods_name,cat_id FROM ecs_goods WHERE goods_id =
( SELECT MAX( goods_id ) FROM ecs_goods );

 

SELECT NULL = NULL
SELECT NULL != 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;

 #总括函数

 

 

参考:

#能够用having关键字过滤查询出来的结果集
SELECT goods_id, goods_name, ( market_price – shop_price ) AS
discount_price FROM ecs_goods WHERE 1 HAVING discount_price > 200

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

 #总括每种分类的商品平均价格,
group比较花费财富(先按cat_id排序,再统计)

#查询各个分类下,商品的数量
SELECT cat_id, COUNT(*) 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

 

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

  • SELECT … FROM table WHERE EXISTS (subquery)

该语法能够知道为:将主查询的数码,放到子查询中做规范申明,依照表达结果(TRUE
或 FALSE)来调整主查询的多少结果是不是能够保存。

#插入测试数据
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 IS NOT NULL

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

 

 

#截取”摩托罗拉”后边的商品名称,并用”OPPO”开端 连接起来后更新
UPDATE ecs_goods SET goods_name = CONCAT( ‘小米’, SUBSTRING(
goods_name, 4 ) ) WHERE goods_name LIKE ‘诺基亚%’

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

 #把表中字段num取值范围为30~3玖中间的值产生30

#积压货款
SELECT SUM( shop_price * goods_number ) FROM ecs_goods;

 

#建表
CREATE TABLE ghost_user_info(
id INT( 3 ),
user_name VARCHAR( 20 )
)CHARSET utf8 ENGINE MYISAM;

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 );