mysql单表查询实例_mysql——单表查询——其它整理示例01

create databasesee;use databasesee;drop databasesww;=========================================================================================

create table cr01 ( sx int(50),

mzvarchar(50),

bzvarchar(50)

);insert into cr01 ( sx,mz,bz ) values (1,'sww','sww01');insert into cr01 values (2,'aww','aww02');insert into cr01 values (3,'qww','qww03'),

(4,'eww','eww04'),

(5,'rww','rww05');insert into cr01 ( sx,mz,bz ) values (6,'yww','yww06'),

(7,'uww','uww07');select * fromcr01;==============================================================================

create table cr02 ( sx02 int(50),

mz02varchar(50),

bz02varchar(50)

);insert into cr02 ( sx02,mz02,bz02 ) values (8,'iww','iww08');insert into cr02 ( sx02,mz02,bz02 ) values (9,'oww','oww09');insert into cr02 ( sx02,mz02,bz02 ) values (10,'zww','zww10');select * fromcr02;=======================================================================================

/*insert into 表名1 (属性列表1)

select 属性列表2 from 表名2 where 条件表达式;*/

insert into cr01 (sx,mz,bz) select sx02,mz02,bz02 from cr02 where sx02 = 8;delete from cr01 where sx = 8;insert into cr01 (sx,mz,bz) select sx02,mz02,bz02 fromcr02;update cr02 set sx02 = 11,mz02 = 'cww',bz02 = 'cww11' where sx02 = 10;update cr02 set sx02 = 11,mz02 = 'cww',bz02 = 'cww11' where sx02 <= 11;select * fromcr01;select mz from cr01 where sx > 5;select mz from cr01 where sx between 5 and 8;select * from cr01 where mz in ('rww','qww','oww');select * from cr01 where mz not in ('rww','qww','oww');select * from cr01 where bz like '%ww%';select * from cr01 where bz like 's%';select * from cr01 where bz not like 's%';select * from cr01 where bz like '%5';select * from cr01 limit 2;select * from cr01 limit 2,2;select * from cr01 order by sx desc;==================================================================================

1、修改表名

语法格式：alter table 旧表名 rename [to]新表名;

注释：修改后example1表就不存在了，只存在名为user的新表，但是其内容是一致的，只是换了个名称.alter table cr01 rename tocr03;select * fromcr03;alter table cr03 rename tocr01;select * fromcr01;===========================================================================================

2、修改字段名

语法格式：alter table表名 change 旧属性名 新属性名 新数据类型;

注释：新数据类型指修改后的数据类型，如不需要修改，则将新数据类型设置成与原来一样alter table cr01 change sx sx05 int(50);select * fromcr01;alter table cr01 change sx05 sx int(50);====================================================================================================

3、修改字段的数据类型

语法格式：alter table表名 modify 属性名 数据类型;

注释：表名指所要修改数据类型的字段的表的名称；

属性名指：所要修改数据类型字段的名称；

数据类型指：修改后的新的数据类型=========================================================================================================

4、修改字段的排列位置

语法格式：alter table 表名 modify 属性名1 数据类型 first|after 属性名2;alter table cr01 modify mz varchar(50) after bz;select * fromcr01;alter table cr01 modify mz varchar(50) after sx;alter table cr01 modify mz varchar(50) first;====================================================================================

5、增加字段

语法格式：alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first | after 属性名2];

完整性约束条件：是可选参数，用来设置新增字段的完整性约束条件

first：是可选参数，其作用是将新增字段设置为表的第一个字的

after：是可选参数，其作用是将新增字段添加到“属性名2”所指的字段后

如果执行的SQL语句中没有“first”或者“after 属性名2”参数指定新增字段的位置，则新增字段默认为表的最后一个字段alter table cr01 add dhhm varchar(50) after bz;select * fromcr01;update cr01 set dhhm = '1234567';==============================================================================================================

6、删除字段

删除字段是删除表中已经定义好的表中的某个字段,删除后其字段所属的数据都会被删除

语法格式：alter table 表名 drop属性名;alter table cr01 dropdhhm;select * from cr01;