#建库表,库名、表名、索引名、列名、列说明带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-------------------------------------------------