mysql的实例名怎么查询_mysql中查询实例

1

建立数据库，编码格式utf-8CREATE DATABASE demo1 CHARSET utf8;创建 goods 表格CREATE TABLE goods(     goods_id INT PRIMARY KEY AUTO_INCREMENT,     goods_name VARCHAR(10) UNIQUE NOT NULL,     cat_id   INT,     brand_id INT,     goods_sn CHAR(10),     goods_number INT,     shop_price   DECIMAL(7,2),     goods_desc   VARCHAR(40));

2

创建 category 栏目表CREATE TABLE category(      cat_id INT PRIMARY KEY AUTO_INCREMENT,      cat_name VARCHAR(20),      parent_id INT);删除goods表的goods_desc 字段,及货号字段ALTER TABLE goods DROP goods_desc;ALTER TABLE goods DROP goods_sn;ALTER TABLE goods ADD good_sn CHAR(9);

3

并增加字段:click_count  -- 点击量ALTER TABLE goods ADD  click_count INT;UPDATE goods SET  click_count=3 WHERE goods_id=3;SELECT*FROM goods;ALTER TABLE goods ADDINSERT INTO goods VALUES(1,'KD876',4,8,10,1388.00,'ECS000000');INSERT INTO goods VALUES(4,'诺基亚N85原装充电器',8,1,17,58.00,'ECS000004');INSERT INTO goods VALUES(3,'诺基亚原装5800耳机',8,1,24,68.00,'ECS000002');DELETE FROM goods WHERE goods_id=3;

4

5 | 索爱原装M2卡读卡器 11 7  ECS000005  8  20.00  3INSERT INTO goods( goods_id, goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES(5,'索爱原装M2卡读卡器',11,7,8,20.00,'ECS000005',3);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('胜创KINGMAX内存卡',11,0,15,42.00,'ECS000006',0);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('诺基亚N85原装立体声耳机HS-82 ',8,1,20,100.00,'ECS000007',0),            ('飞利浦9@9v',3,4,17,399.00,'ECS000008',9),            (' 诺基亚E66 ',3,1,13,2298.00,'ECS000009',20);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('索爱C702c ',3,7,7,1328.00,'ECS000010',11),            ('索爱C702',3,4,0,1300.00,'ECS000011',0),            ('摩托罗拉A810',3,2,8,983.00,'ECS000012',14);1.1:主键为3的商品SELECT * FROM goods WHERE goods_id=3;1.2:不属第3栏目的所有商品SELECT*FROM goods;SELECT *FROM goods WHERE NOT cat_id=3;SELECT *FROM goods WHERE cat_id <>3;SELECT *FROM goods WHERE cat_id !=3;

5

1.3:本店价格高于300元的商品SELECT * FROM goods WHERE shop_price>300;1.4:本店价格低于或等于100元的商品SELECT *FROM goods WHERE shop_price<=100;1.5:取出第4栏目或第11栏目的商品(不许用OR)SELECT *FROM goods;SELECT *FROM goods WHERE cat_id=4 OR cat_id=11;SELECT * FROM goods WHERE cat_id IN (4,11);:取出100<=价格<=500的商品(不许用AND)SELECT *FROM goods WHERE shop_price>=100 AND shop_price<=500;SELECT *FROM goods WHERE shop_price BETWEEN 100 AND 500;1.7:取出不属于第3栏目且不属于第11栏目的商品(AND,或NOT IN分别实现)SELECT *FROM goods WHERE (cat_id !=3 AND cat_id !=11);SELECT *FROM goods WHERE cat_id NOT IN(3,11);取出价格大于100且小于300,或者大于4000且小于5000的商品()SELECT *FROM goods WHERE (shop_price>=100 AND shop_price<=300)OR(shop_price>1000 AND shop_price<2000);1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品SELECT *FROM goods WHERE cat_id=3 AND (shop_price<100 OR shop_price>1000) AND click_count>5 GROUP BY cat_id;SELECT*FROM goods;

6

1.11:取出名字以"诺基亚"开头的商品SELECT *FROM goods WHERE goods_name LIKE '诺基亚%';1.12:取出名字为"诺基亚Nxx"的手机SELECT *FROM goods WHERE goods_name LIKE '诺基亚N__';1.13:取出名字不以"诺基亚"开头的商品SELECT *FROM goods WHERE goods_name NOT LIKE '诺基亚%';1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品

CREATE  TABLE main( num INT);INSERT INTO main VALUES(3),(12),(15),(25),(23),(29),(33);把num值处于[20,29]之间,改为20UPDATE main SET num=20 WHERE num BETWEEN 20 AND 29;SELECT*FROM main;

END