一 数据准备
创建数据库和商品表
create database if not exists mydb2;
use mydb2;
create table product(
pid int primary key auto_increment,
name varchar(20) not null,
price double,
categoryid 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,'香水',340,'c003') ;
insert into product values(null,'神仙水',366,'c003') ;
insert into product values(null,'粉底',180,'c003') ;
insert into product values(null,'方便面',43,'c004') ;
insert into product values(null,'海带丝',18,'c004') ;
insert into product values(null,'坚果',88,null) ;
二 简单查询
1,查询所有商品
select * from product;
2,查询商品名和商品价格
select name,price from product;
3,别名查询使用关键字as(as可以省略)
表别名
select * from product as p;
select * from product p;
列别名
select name as pn from product;
4,去掉重复值
select distinct price from product;
5,查询结果是表达式:将所有商品价格+10显示
select name,price+10 from product;
三 运算符
数据库中的表结构确立后,表中数据代表的意义就已经确定,通过MySQL运算符进行运算,就可以获取到表结构以外的另一种数据。MySQL支持四种运算符,算数运算符,比较运算符,逻辑运算符,位运算符。