MySQL笔记整理（二）

MySQL常用语法整理

一、增删改查

1.1、增

模板示例：

insert into 库名.表名(c1,c2,c3,c4) values(v1,v2,v3,v4);

示例：

insert into test.ruoze_t1(name,age) values ('张三',18);

1.2、删

模板示例：

delete from 库名.表名 where 。。。;

示例：

delete from test.ruoze_t1 where name='张三';

1.3、改

模板示例：

update 库名.表名 set c1=v1,c2=v2 where c3=v3;

示例：

update test.ruoze_t1 set age=33,name='小丽' where id=1;

1.4、查

select  c1 from 库名.表名 where c2=v2;

1.5、where条件过滤

select name , age from 库名.表名 where age =18;

select name , age from 库名.表名 where age >18;

select name , age from 库名.表名 where name in ('zhangsan','lisi');

select name , age from 库名.表名 where name not in ('zhangsan','lisi');

1.6、排序 

升序：select name , age  from 库名.表名 order by age;

降序：select name , age  from 库名.表名 order by age desc;

1.7、合并表

合并去重：select name,age from a union select name , age from b ;

合并不去重：select name , age  from a union all select name , age from b;

1.8、模糊查询

select name , age from 库名.表名 where name like '_三';

select name , age from 库名.表名 where name like '%三';

select name , age from 库名.表名 where name like '%三%';

1.9、null语法

select name , age from 库名.表名 where name is null;

select name , age from 库名.表名 where name is not null;

select name , ifnull(age,18) from 库名.表名;

1.10、聚合函数

select max(age),min(age),avg(age),count(1),sum(age) from 库名.表名

1.11、分组

分组并过滤：select name,age from 库名.表名 where age >18 group by sex having sum(age)<100;

1.12、子查询

select t.sex,t.age_avg  from (select sex,avg(age) as age_avg from 库名.表名 group by sex) t where age_avg>19;