2021.11.9 MySQL学习记录（查增删改）

MySQL学习记录

使用sql语言进行数据库的查增删改

/*
create table student(
sid int primary KEY AUTO_INCREMENT,
sname varchar(20),
score int
);
*/

/*
insert into student (sid,sname,score) values(null,'Jame',88);
INSERT INTO student (sid,sname,score) VALUES(NULL,'Lee',98);
INSERT INTO student (sid,sname,score) VALUES(NULL,'Shelly',70);

UPdate student set sname='Lary' where sid=2;

delete from student where sid=3;
*/




-- 创建product表
create table product(
    pid int primary key,
    pname varchar(20),
    price double,
    category_id varchar(32)
);

-- 在product表中插入数据 
insert into product(pid,pname,price,category_id) values(1,'联想',5000,'c001');
insert into product(pid,pname,price,category_id) values(2,'海尔',3000,'c001');
insert into product(pid,pname,price,category_id) values(3,'雷神',5000,'c001');
insert into product(pid,pname,price,category_id) values(4,'杰克琼斯',800,'c002');
insert into product(pid,pname,price,category_id) values(5,'真维斯',200,'c002');
insert into product(pid,pname,price,category_id) values(6,'花花公子',440,'c002');
insert into product(pid,pname,price,category_id) values(7,'劲霸',2000,'c002');
insert into product(pid,pname,price,category_id) values(8,'香奈儿',800,'c003');
insert into product(pid,pname,price,category_id) values(9,'相宜本草',200,'c003');
insert into product(pid,pname,price,category_id) values(10,'面霸',5,'c003');
insert into product(pid,pname,price,category_id) values(11,'好想你枣',56,'c004');
insert into product(pid,pname,price,category_id) values(12,'香飘飘奶茶',1,'c005');
insert into product(pid,pname,price,category_id) values(13,'海澜之家',1,'c002');



-- 显示整张出表的所有数据
select * from product;

-- 显示product表的所有的商品名和价格的数据
select pname,price from product;

-- 显示product表的所有的商品名和价格的数据，并且pname和price以商品名和价格显示
select pname as '商品名',price+10 as '价格' from product;


-- 删除product表中的所有数据,不重置计数器
delete from product;

-- 删除product表再创建同名的表，重置计数器
truncate table product;

-- 删除product表
DROP TABLE product;

-- 删除product表
DROP DATABASE product;



/*查询价格在200-1000的所有商品*/
SELECT pname AS '商品名',price AS '价格' FROM product WHERE price BETWEEN 200 AND 1000;

/*查询含有‘霸’的所有商品*/
SELECT pname AS '商品名',price AS '价格' FROM product WHERE pname LIKE '%霸%';

/*查询以‘香’字开头的所有商品*/
SELECT pname AS '商品名',price AS '价格' FROM product WHERE pname LIKE '香%';

/*查询第二个字为‘想’字开头的所有商品*/
SELECT pname AS '商品名',price AS '价格' FROM product WHERE pname LIKE '_想%';

/*查询没有分类的商品*/
SELECT * FROM product WHERE pname IS NULL;

/*查询已分类的商品*/
SELECT * FROM product WHERE pname IS NOT NULL;