update 更新
所有人的年龄加一:update user set age=age+1;
只更新某个: update user set age=age+1 where name='zhang san';
select查询
select * from user;//一般不建议使用通配符
select name,age,sex from user;//根据键查找
select name,age,sex from user where sex='M' and age>=20 and age<=25;
select name,age,sex from user where sex='M' and age between 20 and 25;
select name,age,sex from user where sex='W' or age>=22;
select name,age,sex from user where name like "zhang%";
select name,age,sex from user where name is not null;
联合查询 UNION
SELECT expression1, expression2, ... expression_n
FROM tables[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables[WHERE conditions]
union默认去重,不用修饰distinct,all表示显示所有重复值
例如:
select name,age,sex from user where age >=21 union all select name,age,sex from user where sex='M';
注意:between是[ ]。like 部分匹配不加=,null not null使用is
带in子查询
select name,age,sex from user where age in (20,21);
select name,age,sex from user where age not in (20,21);
在一个区间内查找IN,不在这个区间内查找NOT IN.
去重
将年龄拿出来去重显示: select distinct age from user;