修改数据
基本语法
update table_name set field1=new-value1, field2=new-value2 [where clause]
- 对整张 table 的某个字段进行修改
update people set name = ‘change’;
- 修改符合条件的数据
update people set job=’work’ where age>18;
删除数据
基本语法
delete from table_name [where clause]
- 删除某一行
delete from people where age=22;
- 如何解决删除中间数据后,id 断层(可以指定后面的 id 从多少开始递增的)
alter table people auto_increment=8;
对数据进行排序
基本语法
选择你要排序的对象,以你指定的字段按你想要的方式进行排序(个人理解,(╯﹏╰))
select field1, field2,...fieldN table_name1, table_name2...
order by field1, [field2...] [asc [desc]]
- 以某个字段对数据进行排序(其中 asc 为升序,desc 为降序)
select * from people order by age desc;
分组统计
基本语法
select 选择的是显示的字段 by 指定的是分组字段
select name,[count(*)] from people group by name;
- 分组
select name from people group by name;
- 分组计数
select name,count(*) from people group by name;
select name,count(*) as num from people group by name;
- 对某个字段求和(按名字分组,并将每个组内的所有人的年龄求和)
select name,sum(age) as age_sum from people group by name;
- 对某个字段求总和
select name ,sum(age) as total from people group by name with rollup;
select coalesce( name,’总数’) ,sum(age) as total from people group by name with rollup;
增删表的字段
基本语法
alter table people drop/add field_name;
- 为 table 增加一个 sex 字段,数据类型为 enum (枚举)
alter table people add sex enum(“M”,”F”);
修改字段名及属性
基本语法
修改 job 为 status 并指定数据类型为 char(32) [ 指定是否能为空,以及默认值 ]
alter table people change job status char(32) [not null default "x"];
- 用 change 修改
alter table people change job status char(32);
- 用 modify 修改数据类型(modify 貌似不能修改字段名)
alter table people modify status char(64);