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

旧城等待， 2023-10-02 08:49 9阅读 0赞

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;