mysql基础（二）—— 简单sql

 

查询

select *  from company

select c.code from company c;

select m.bookname from myview m; (myview为视图)

select mybook.bookname,mybook.bookType from mybook;

模糊查

select c.name from company c where c.name like '%a%'; 

排序查

select c.name from company c order by c.code ;

select c.name from company c order by c.code desc;

插入

insert into company(code ,name) values('5003','Amazon');

insert into company values('5004','twitter');

删除

delete from company where code='5004';

更新

update company set name='华为' where code='5003';

总数

select count(company.name) as totalcount from company

求和

 
select sum(company.code) as total from company

平均

select avg(company.code) as avgCode from company

最大

 
select max(company.code) as maxCode from company

最小

 
select min(company.code) as minCode from company

 

分组

select b.companyNo, b.id ,group_concat(b.bookName) from mybook b group by b.companyNo；

select b.companyNo, b.id ,group_concat(b.bookName) from mybook b group by b.companyNo with rollup;

select b.companyNo, b.id ,group_concat(b.bookName) from mybook b group by b.companyNo having b.companyNo >='5002'