标准sql

#建库表，库名、表名、索引名、列名、列说明带gb18030字符
drop database if exists `????????丂丄狚`;
drop database if exists `狛U3、。╊╋`;
drop database if exists `狜狝U1啊`;
drop database if exists `阿鼾齄U2!"}~`;
create database if not exists `????????丂丄狚` character set gb18030;
create database if not exists `狛U3、。╊╋` character set gb18030;
create database if not exists `狜狝U1啊` character set gb18030;
create database if not exists `阿鼾齄U2!"}~` character set gb18030;
use `????????丂丄狚`;
#set names gb18030;
DROP TABLE IF EXISTS `????????丂丄狚1`;
CREATE TABLE `????????丂丄狚1` (
  `id` int(11) NOT NULL,
  `????????丂丄狚11` char(254) DEFAULT NULL comment '????????丂丄狚111',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `????????丂丄狚111` (`????????丂丄狚11`)
) ENGINE=InnoDB DEFAULT CHARSET=gb18030 COLLATE=gb18030_bin;

#展示库，可以查看到新建的库
select '展示库，可以查看到新建的库';
show databases\G
select '--------------------------------------------------end-------------------------------------------------';

#展示表，可以查看到新建的表
select '展示表，可以查看到新建的表';
show table status\G
select '--------------------------------------------------end-------------------------------------------------';

#展示列，可以查看到表中列详细信息
select '展示列，可以查看到表中列详细信息';
show full columns from `????????丂丄狚1`\G
select '--------------------------------------------------end-------------------------------------------------';

#展示索引，可以查看到表中索引信息
select '展示索引，可以查看到表中索引信息';
show index from `????????丂丄狚1`\G
select '--------------------------------------------------end-------------------------------------------------';

#插入数据，插入字段值带gb18030字符
INSERT INTO `????????丂丄狚1` VALUES ('1', '????????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~');

#查询结果正确展示gb18030字符
select '插入数据后查询结果正确展示gb18030字符';
select * from `????????丂丄狚1` where `????????丂丄狚11` like '??%';
select '--------------------------------------------------end-------------------------------------------------';
#查询结果条件不满足，无结果
select '查询结果条件不满足，无结果';
select * from `????????丂丄狚1` where `????????丂丄狚11` like '??%';
select '--------------------------------------------------end-------------------------------------------------';

#更新记录字段值
#查询更新前的记录
select '更新记录字段值,查询更新前的记录';
select * from `????????丂丄狚`.`????????丂丄狚1` where id=1\G
select '--------------------------------------------------end-------------------------------------------------';
#更新记录中的字段值
update `????????丂丄狚`.`????????丂丄狚1` set `????????丂丄狚11`='??亐????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~' where id=1;
#查询修改的记录，相关字段值展示为更新后的
select '查询修改的记录，相关字段值展示为更新后的';
select * from `????????丂丄狚`.`????????丂丄狚1` where id=1\G
select '--------------------------------------------------end-------------------------------------------------';

#删除数据
#删除记录前查询，能够查询到记录
select '删除记录前查询，能够查询到记录';
select * from `????????丂丄狚`.`????????丂丄狚1` where id=1\G
select '--------------------------------------------------end-------------------------------------------------';
#删除记录
delete from `????????丂丄狚`.`????????丂丄狚1` where id=1;
#再次查询，查询结果为空
select '删除后再次查询，查询结果为空';
select * from `????????丂丄狚`.`????????丂丄狚1` where id=1\G
select '--------------------------------------------------end-------------------------------------------------';

DROP TABLE IF EXISTS `????????丂丄狚1`;
select '展示表，无法查询删除的表';
show table status\G
select '--------------------------------------------------end-------------------------------------------------';

drop database if exists `????????丂丄狚`;
#展示库，删除的库查询不到
select '展示库，删除的库 ????????丂丄狚 查询不到';
show databases\G
select '--------------------------------------------------end-------------------------------------------------';
 

--测试结果

展示库，可以查看到新建的库
展示库，可以查看到新建的库
*************************** 1. row ***************************
Database: ????????丂丄狚
*************************** 2. row ***************************
Database: 狛u3、。╊╋
*************************** 3. row ***************************
Database: 狜狝u1啊
*************************** 4. row ***************************
Database: 阿鼾齄u2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
展示表，可以查看到新建的表
展示表，可以查看到新建的表
*************************** 1. row ***************************
           Name: ????????丂丄狚1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 0
    Create_time: 2022-06-29 15:48:16
    Update_time: 0000-00-00 00:00:00
     Check_time: NULL
      Collation: gb18030_bin
       Checksum: NULL
 Create_options: 
        Comment: 
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
展示列，可以查看到表中列详细信息
展示列，可以查看到表中列详细信息
*************************** 1. row ***************************
     Field: id
      Type: int
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: 
Privileges: select,insert,update,references
   Comment: 
*************************** 2. row ***************************
     Field: ????????丂丄狚11
      Type: char(254)
 Collation: gb18030_bin
      Null: YES
       Key: UNI
   Default: NULL
     Extra: 
Privileges: select,insert,update,references
   Comment: ????????丂丄狚111
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
展示索引，可以查看到表中索引信息
展示索引，可以查看到表中索引信息
*************************** 1. row ***************************
        Table: ????????丂丄狚1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: ????????丂丄狚1
   Non_unique: 0
     Key_name: ????????丂丄狚111
 Seq_in_index: 1
  Column_name: ????????丂丄狚11
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
插入数据后查询结果正确展示gb18030字符
插入数据后查询结果正确展示gb18030字符
id    ????????丂丄狚11
1    ????????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
查询结果条件不满足，无结果
查询结果条件不满足，无结果
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
更新记录字段值,查询更新前的记录
更新记录字段值,查询更新前的记录
*************************** 1. row ***************************
                      id: 1
????????丂丄狚11: ????????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
查询修改的记录，相关字段值展示为更新后的
查询修改的记录，相关字段值展示为更新后的
*************************** 1. row ***************************
                      id: 1
????????丂丄狚11: ??亐????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
删除记录前查询，能够查询到记录
删除记录前查询，能够查询到记录
*************************** 1. row ***************************
                      id: 1
????????丂丄狚11: ??亐????丂丄狚狛U3、。╊╋狜狝U1啊阿鼾齄U2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
删除后再次查询，查询结果为空
删除后再次查询，查询结果为空
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
展示表，无法查询删除的表
展示表，无法查询删除的表
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------
展示库，删除的库 ????????丂丄狚 查询不到
展示库，删除的库 ????????丂丄狚 查询不到
*************************** 1. row ***************************
Database: 狛u3、。╊╋
*************************** 2. row ***************************
Database: 狜狝u1啊
*************************** 3. row ***************************
Database: 阿鼾齄u2!"}~
--------------------------------------------------end-------------------------------------------------
--------------------------------------------------end-------------------------------------------------