增:
insert into table_name (column1, column2, column3) values (value1, value2, value3);
删:
delete from table_name where city_name = 'suzhou';
改:
update table_name set column_name1 = 'new1', column_name2 = 'new2';
查:
select * from table_name where city = 'suzhou';
聚合函数:max、min、sum、avg、count
select cate_name, sum(price),count(id),max(price), min(price), avg(price)
from table_name
where price >= 5000
group by cate_name
having sum(price) >20000;
- having后必须跟聚合函数,where后不能跟聚合函数;
- group by 后的列名必须出现在select中;
- group by 后出现多列的情况,将多列看为一个整体;
- group by 也是一种去重的方法,对后面紧跟的列去重;
distinct 后接多列 同group by 将多列看为一个整体;
sql中的拼接符用 '||' 如下:
select distinct column_a || 'a' from table_A
union all
select distinct column_b || 'b' from table_B;