10.OB4.0.0基础管理

最新推荐文章于 2025-12-19 16:00:26 发布

原创最新推荐文章于 2025-12-19 16:00:26 发布 · 231 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

国产数据库-OceanBase 专栏收录该内容

39 篇文章

订阅专栏

文章展示了在MySQL环境中进行的一系列数据库操作，包括创建和管理用户（如锁定和解锁账户）、创建和修改视图、收集表的统计信息以优化查询性能、创建和调整索引类型（全局、唯一）。此外，还涉及到了数据库和表的创建，如指定分区策略、表的压缩和并行度设置，以及创建临时表等。

1.用户管理。

root用户创建用户
CREATE USER TEST IDENTIFIED BY 'test';
ALTER USER 'test' IDENTIFIED BY 'test';
ALTER USER 'test' ACCOUNT LOCK ;
ALTER USER 'test' ACCOUNT UNLOCK;

2.视图管理。

drop view v_emp;
create view v_emp as select * from emp;
SHOW CREATE VIEW v_emp;
--直接更改视图定义。
ALTER VIEW v_emp AS select * from test2;   
SHOW CREATE VIEW v_emp;

MySQL [sjzt]> create view v_emp as select * from emp;
Query OK, 0 rows affected (0.05 sec)
MySQL [sjzt]> SHOW CREATE VIEW v_emp;
+-------+---------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                   | character_set_client | collation_connection |
+-------+---------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_emp | CREATE VIEW `v_emp` AS select `sjzt`.`emp`.`empno` AS `empno`,`sjzt`.`emp`.`name` AS `name` from `sjzt`.`emp` | utf8mb4              | utf8mb4_general_ci   |
+-------+---------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.02 sec)
MySQL [sjzt]> ALTER VIEW v_emp AS select * from test2;   
Query OK, 0 rows affected (0.05 sec)
MySQL [sjzt]> SHOW CREATE VIEW v_emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                                       | character_set_client | collation_connection |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_emp | CREATE VIEW `v_emp` AS select `sjzt`.`test2`.`id` AS `id`,`sjzt`.`test2`.`name` AS `name`,`sjzt`.`test2`.`age` AS `age`,`sjzt`.`test2`.`address` AS `address` from `sjzt`.`test2` | utf8mb4              | utf8mb4_general_ci   |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.04 sec)

3.收集表的统计信息。

收集30个桶。
CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50));
ANALYZE TABLE test UPDATE HISTOGRAM ON c1,c2 with 30 buckets;

--当 Session 变量 enable_sql_extension 为 TRUE 的时候，
--使用 Oracle 模式下的语法收集用户 user01 的表 test 的统计信息，
--所有列的桶个数设定为 128。
ALTER SYSTEM SET enable_sql_extension = TRUE;
ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;

4.创建数据库。

CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8;  --自动转换为utf8mb4;
CREATE DATABASE test3 READ WRITE;  --创建可以读写数据库。

5.创建索引

CREATE TABLE test1 (c1 int primary key, c2 VARCHAR(10));
CREATE INDEX test1_index ON test (c1, c2 ASC);
SHOW INDEX FROM test1;

--创建全局索引
CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX IDX(col2) GLOBAL);
show index from tbl1;
--创建唯一索引
CREATE TABLE tbl2 (col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1));
CREATE UNIQUE INDEX tbl2_idx2 ON tbl2(col2) STORING(col3);
SHOW INDEX FROM tbl2 \G

6.绑定执行计划。

文本绑定和SQL_ID绑定。
create table t1(c1 int,c2 int);
CREATE OUTLINE otl_idx_c2 ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
CREATE OUTLINE otl_idx_c2 ON "ED570339F2C856BA96008A29EDF04C74" USING HINT /*+ index(t1 idx_c2)*/ ;

7.创建表。

--指定主zone是zone1;
#CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) PRIMARY_ZONE = 'zone1'; --报语法错误。

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2)); --创建待索引的表。
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8; --HASH分区表。
--创建一级分区为 Range 分区，二级分区为 Key 分区的表
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT) PARTITION BY RANGE(c1) 
     SUBPARTITION BY KEY(c2, c3) 
	 SUBPARTITIONS 5
     (
	 PARTITION p0 VALUES LESS THAN(0), 
	 PARTITION p1 VALUES LESS THAN(100)
	 );
--创建一列为 gbk, 一列为 utf8 的表。
CREATE TABLE t1(
c1 VARCHAR(10),
c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin
)
DEFAULT CHARSET utf8 COLLATE utf8mb4_general_ci;

--开启 Encoding 并使用 zstd 压缩，宏块保留空间为 5% 。
CREATE TABLE t1 (c1 INT, c2 INT, c3 VARCHAR(64))COMPRESSION 'zstd_1.0' ROW_FORMAT DYNAMIC PCTFREE 5;
CREATE TABLE tbl1(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3; --设置表并行度为3;
--使用自增列作为分区键。
CREATE TABLE tbl2(
inv_id BIGINT NOT NULL AUTO_INCREMENT,
c1 BIGINT, 
PRIMARY KEY (inv_id) 
) PARTITION BY HASH(inv_id) PARTITIONS 8;
--创建检查约束表。
CREATE TABLE tbl5 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2));
CREATE TABLE tbl6 LIKE tbl5; --依照tbl5创建tbl6;

8.创建表分组。
CREATE TABLEGROUP myTableGroup1;
CREATE TABLE myt1 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;
CREATE TABLE myt2 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;

--创建 HASH 分区的表组 tgh，同时创建 HASH 分区的表 ttgh 与 ttgh2 且分区个数相同
CREATE TABLEGROUP tgh PARTITION BY HASH PARTITIONS 10;
CREATE TABLE ttgh(c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 10;
CREATE TABLE ttgh2(c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 10;

8.支持临时表创建。

create temporary table student_copy(
　　id int primary key,
　　name varchar(20) 
)Engine=InnoDB default charset utf8;