MySQL数据库基本操作DQL---基本查询----简单查询

– -------------------------MySQL数据库基本操作-DQL-基本查询---------------------

– 数据准备
– 1.创建数据库
create database if not exists mydb1;
use mydb1;
– 创建商品表：
create table product(
pid int primary key auto_increment,-- 商品编号
pname varchar(20) not null,-- 商品名字
price double, – 商品价格
category_id varchar(20) – 商品所属分类
);
– 插入数据
insert into product values (null,‘海尔洗衣机’,5000,‘c001’);
insert into product values (null,‘美的冰箱’,3000,‘c001’);
insert into product values (null,‘格力空调’,5000,‘c001’);
insert into product values (null,‘九阳电饭煲’,5000,‘c001’);

insert into product values (null,‘啄木鸟衬衣’,300,‘c002’);
insert into product values (null,‘恒源祥西裤’,800,‘c002’);
insert into product values (null,‘花花公子夹克’,440,‘c002’);
insert into product values (null,‘劲霸休闲裤’,266,‘c002’);
insert into product values (null,‘海澜之家卫衣’,180,‘c002’);
insert into product values (null,‘杰克琼斯运动裤’,430,‘c002’);

insert into product values (null,‘兰蔻面霜’,300,‘c003’);
insert into product values (null,‘雅诗兰黛精华水’,200,‘c003’);
insert into product values (null,‘香奈儿香水’,350,‘c003’);
insert into product values (null,‘SK-II神仙水’,350,‘c003’);
insert into product values (null,‘资生堂粉底液’,180,‘c003’);

insert into product values (null,‘老北京方便面’,56,‘c004’);
insert into product values (null,‘良品铺子海带丝’,17,‘c004’);
insert into product values (null,‘三只松鼠坚果’,88,null);

1.查询所有商品

select pid,pname,price,category_id from product;
select * from product;

2.查询商品名和商品价格

select pname,price from product;

3.1表别名：

select * from product as p; – 别名-p
select * from product p;

– select p.id,u.id from product p,user1 u; – 两个表里查ID ，别名的作用

3.2列别名：

select pname as ‘商品名’,price as ‘商品价格’ from product;

#4.去掉重复值:
select distinct price from product; – 去除某一列数据
– select distinct * price from product; 某行与另一行数据全部一样，会把重复的去掉

#4.查询结果是表达式(运算查询):将所有商品的加价10元进行显示：
select pname,price +10 as new_price from product;