数据库操作

查询关键字的顺序:select -->from-->where -->group by-->having-->order by -->limit

SELECT * FROM product where (normal_price+price)>50; -- 算数运算符

select (normal_price+price) as total_price from product; -- 用totalprice代替normal_price和price的值

select * from product where price between 50 and 100; -- 与逻辑运算相同 >50且<100
select * from product where price >100  or price<50;

select * from product where id in (1,22,33,43,66);  -- 查询的id在括号里面
select * from product where id not in (1,22,33,43,66);

-- 模糊查询 %代表零个或者多个，_代表单个字符
select * from product where name like '%肉%';
select * from product where name like '回%';
select * from product where name like '回_肉';

-- 正则表达式
-- [0-9] 表示取0-9中的一个数字
-- [^0-9] 表示非0-9中的一个字符
select * from product where name regexp '[锅吧香]肉' ;-- []表示取里面的一个字符

select * from product order by name,id desc;-- 排序,desc 逆序 

-- 分组查询,名字相同的对应数值
select name,count(*) from product group by name; 
-- group by 右边的条件一定大于等于左边的列查询的列一定在group by里面包含
select where_show from product group by where_show,name;
-- having只和group by有关，相当于where条件
 select where_show from product group by where_show having where_show>2;

-- 表一定要做分页
select * from product limit 0,3;-- 第1页,每一页显示3条,
select * from product limit 3,3;-- (page-1)*size,size;  第2页,每一页显示3条,
select * from product limit 5,4; -- 第6页,每一页显示4条,

select count(*) from product where price > 50;-- 最大,最小,平均,求和
select max(price) from product;
select min(price) from product;
select avg(price) from product;
select sum(price) from product;

select date_format(create_time,'%Y-%m-%d') from product;-- 年月日

-- 交叉查询
select * from product ,cuisine
where product.cuisine_id=cuisine.id;
select * from product p ,cuisine c where p.cuisine_id=c.id;

-- 内连接
-- inner join 内连接
select * from product p
inner join cuisine c
on p.cuisine_id=c.id

-- 左连接：
-- 将所有的产品都查询出来，无论和菜系是否关联，有关联的直接关联就好，没有关联，则用null代替：左连接
-- 以左边的表为基准，和右边的表关联，如果没有关联的数据，则用null代替
select * from product p
left join cuisine c
on p.cuisine_id=c.id;

-- 右连接：
select * from product p
right join cuisine c
on p.cuisine_id=c.id;

-- 子查询
select *from
(select p.*, c.name cuisine_name from product p
left join cuisine c on p.cuisine_id=c.id) aa;
-- aa where  aa.name like '%肉%';

-- 合并查询  union all 就算里面有相同的数据，也会查询出来（不去重）
-- union  去重（去掉重复的数据）
(select * from product where style=1 limit 1)
union all
(select * from product where style=2 limit 2)
union all
(select * from product where style not in (1,2) limit 7);

-- 去重：
select  distinct(price)  from product;
select  price  from product group by price;