SQL基础的实践简练（一）_sql基础实践-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_43276277/article/details/87163778

1.表测试

– 创建表结构

解析:创建一张表student,设置约束:pid 为他的主键、唯一、自增长、非空、类型为INT，desc 是VARCHAR类型约束:非空唯一（结尾有总结）

CREATE TABLE student (
pid INT (8) NOT NULL PRIMARY KEY auto_increment,
desc VARCHAR (32) NOT NULL UNIQUE,
css int(6);
);

– 添加一行数据

INSERT INTO cat VALUES (3,‘赵文明’,666);
– 查询全部数据

SELECT * FROM cat;

– 修改条件数据

UPDATE cat SET css = 88 WHERE css IS NULL;

– 删除指定行数据

DELETE FROM cat WHERE pid = 2;

– 重构表结构

TRUNCATE

– 修改默认键的起始值

ALTER TABLE Persons AUTO_INCREMENT = 100 ；

2.表测试

– 创建商品表

create table product(
pid int not null primary key auto_increment,
pname varchar(500),
price double,
c_id int
);
– 添加测试数据

INSERT INTO product VALUES(null,’ 联想（Lenovo）威6 14英寸商务轻薄笔记本电脑(i7-8550U 8G 256G PCIe SSD FHD MX150 Win10 两年上门)鲨鱼灰’,5999,1);
INSERT INTO product VALUES(null,‘联想(Lenovo)拯救者Y7000 15.6英寸游戏笔记本电脑(英特尔八代酷睿i5-8300H 8G 512G GTX1050 黑)’,5999,1);
INSERT INTO product VALUES(null,‘三洋（SANYO）9公斤智能变频滚筒洗衣机臭氧除菌空气洗 WiFi智能中途添衣 Magic9魔力净’,2499,1);
INSERT INTO product VALUES(null,‘海尔（Haier) 滚筒洗衣机全自动 10公斤变频 99%防霉抗菌窗垫EG10014B39GU1’,2499,1);
INSERT INTO product VALUES(null,‘雷神(ThundeRobot)911SE炫彩版 15.6英寸游戏笔记本电脑（I7-8750H 8G 128SSD+1T GTX1050Ti Win10 RGB IPS）’,6599,1);
INSERT INTO product VALUES(null,‘七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A’,299,2);
INSERT INTO product VALUES(null,‘真维斯JEANSWEST t恤男纯棉圆领男士净色修身青年打底衫长袖体恤上衣浅花灰 M’,35,2);
INSERT INTO product VALUES(null,‘PLAYBOY/花花公子休闲裤男弹力修身秋季适中款商务男士直筒休闲长裤黑色适中款 31(2.4尺)’,128,2);
INSERT INTO product VALUES(null,‘劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185’,362,2);
INSERT INTO product VALUES(null,‘Chanel 香奈儿女包 2018全球购新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色’,306830,3);
INSERT INTO product VALUES(null,‘皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒黑色横款（款式一）’,269,3);
INSERT INTO product VALUES(null,‘PRADA 普拉达女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0’,28512,3);
INSERT INTO product VALUES(null,‘好想你干果零食新疆特产阿克苏灰枣免洗红枣子玛瑙红500g/袋’,21.9,4);
INSERT INTO product VALUES(null,‘三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装’,128,4);
INSERT INTO product VALUES(null,‘三只松鼠坚果炒货零食特产每日坚果开心果100g/袋’,32.8,4);
INSERT INTO product VALUES(null,‘洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒（26g30包） 780g/盒（新老包装随机发货)’,149,4);
INSERT INTO product VALUES(null,‘今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用中号160贴’,9.9,5);
INSERT INTO product VALUES(null,'自然共和国原自然乐园芦荟舒缓保湿凝胶300ml2（约600g）进口补水保湿舒缓晒后修复面膜’,72,5);
INSERT INTO product VALUES(null,‘欧莱雅LOREAL 男士火山岩控油清痘洁面膏100ml（洗面奶男清洁毛孔祛痘男士洗面奶）’,38.9,null);
INSERT INTO product VALUES(null,‘阿拉丁 aladdin 144-62-7 无水草酸 O107180 草酸，无水 500g’,88.1,null);
INSERT INTO product VALUES(null,‘远东电缆（FAR EAST CABLE）BVVB 2*2.5平方国标家装照明插座用2芯硬护套铜芯电线装潢明线 100米’,473,null);

– 查询所有

SELECT * FROM product;

– 查询商品名和价格

SELECT pname ‘商品名’,price ‘价格’ FROM product;

– 过滤重复价格

SELECT DISTINCT price FROM product;

– 将所有商品按原价和包邮价(商品价+10)展示

SELECT pname ‘商品名’,price ‘原价’,(price+10) ‘包邮价’ FROM product;

– 查询区间

SELECT pname ‘商品名’,price ‘价钱’ FROM product WHERE price>5000 AND
price<500000;

– 模糊查询代表

SELECT pname ‘商品名’,price ‘价钱’ FROM product WHERE pname LIKE ‘_想%’;

– 查询？为？的商品所有信息：
SELECT * FROM product WHERE pname = ‘三只松鼠坚果炒货零食特产每日坚果开心果100g/袋’;
SELECT * FROM product WHERE price = 299;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE price >60;
SELECT * FROM product WHERE price >=2000 AND price<=10000;
SELECT * FROM product WHERE price <2000 OR price>10000;
SELECT * FROM product WHERE price in (306830,28512);
SELECT * FROM product WHERE pname LIKE ‘%霸%’;
SELECT * FROM product WHERE pname LIKE ‘三%’;
SELECT * FROM product WHERE pname LIKE ‘_想%’;