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

create database see;

use database see;

drop database sww;

=========================================================================================

create table cr01 ( sx int(50),
                    mz varchar(50),
                    bz varchar(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 * from cr01;

==============================================================================
create table cr02 ( sx02 int(50),
                    mz02 varchar(50),
                    bz02 varchar(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 * from cr02;
=======================================================================================

/*    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 from cr02;


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 * from cr01;


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 to cr03;

select * from cr03;

alter table cr03 rename to cr01;

select * from cr01;

===========================================================================================

2、修改字段名

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

注释：新数据类型指修改后的数据类型，如不需要修改，则将新数据类型设置成与原来一样

alter table cr01 change sx sx05 int(50);

select * from cr01;

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 * from cr01;

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 * from cr01;

update cr01 set dhhm = '1234567';
==============================================================================================================

6、删除字段

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

语法格式：alter table 表名 drop 属性名;

alter table cr01 drop dhhm;

select * from cr01;