When working with a GBase database, understanding SQL is not just about syntax—it’s about learning how data is structured, secured, queried, and maintained in real systems.
This article provides a hands-on SQL practice guide based on common GBase operations, including:
- User and permission management
- Table creation and distribution strategies
- Views and indexes
- System metadata queries
🚀 1. Database Login and User Context
Before performing operations, connect to the database:
gccli -u root -p
`
👉 This opens the GBase database CLI environment.
🔐 2. User and Permission Management
Create a Role
sql id="gbase_role_01"
CREATE ROLE role1;
Create a User
sql id="gbase_user_01"
CREATE USER user1 IDENTIFIED BY '123456';
Grant Permissions
sql id="gbase_grant_01"
GRANT ALL ON mydb.* TO role1;
sql id="gbase_grant_02"
GRANT role1 TO user1;
Check Permissions
sql id="gbase_perm_01"
SHOW GRANTS FOR user1;
🏗️ 3. Table Creation in GBase Database
Hash Distribution Table
sql id="gbase_hash_table"
CREATE TABLE student (
sno VARCHAR(20),
sname VARCHAR(20),
ssex VARCHAR(3),
sage INT,
sdept VARCHAR(20)
)
DISTRIBUTED BY ('sno');
👉 Data is distributed based on hash key for performance balance.
Replicated Table
sql id="gbase_replicated_table"
CREATE TABLE course (
cno INT,
cname VARCHAR(20),
cpno INT,
ccredit INT
)
REPLICATED;
👉 Full copy stored on all nodes for fast access.
Random Distribution Table
sql id="gbase_random_table"
CREATE TABLE sc (
sno VARCHAR(20),
cno INT,
grade INT
);
📥 4. Data Insertion
sql id="gbase_insert_01"
INSERT INTO student VALUES
('200215121','Alice','F',20,'CS'),
('200215122','Bob','M',19,'CS');
🔍 5. Querying Data
sql id="gbase_select_01"
SELECT * FROM student;
sql id="gbase_count_01"
SELECT COUNT(*) FROM student;
🔗 6. Views in GBase Database
Create a View
sql id="gbase_view_01"
CREATE OR REPLACE VIEW v_sc AS
SELECT sname, cno, grade
FROM student s, sc
WHERE s.sno = sc.sno;
👉 Views simplify complex joins into reusable queries.
⚙️ 7. Table Altering and Indexing
Add Column
sql id="gbase_alter_01"
ALTER TABLE sc ADD cname VARCHAR(20);
Update Data
sql id="gbase_update_01"
UPDATE sc, course c
SET sc.cname = c.cname
WHERE sc.cno = c.cno;
Create Index
sql id="gbase_index_01"
ALTER TABLE student ADD INDEX idx_sno (sno) USING HASH GLOBAL;
Drop Index
sql id="gbase_drop_index"
ALTER TABLE student DROP INDEX idx_sno;
🧠 8. System Metadata Queries
GBase provides rich system tables for introspection.
Check Database Metadata
sql id="gbase_meta_01"
SELECT TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'mydb';
View System Tables
sql id="gbase_meta_02"
SELECT COUNT(*)
FROM information_schema.tables;
View Disk Usage
sql id="gbase_perf_01"
SELECT * FROM performance_schema.DISK_USAGE_INFO;
Check Node Status
sql id="gbase_node_01"
SHOW NODES;
🔍 9. Debugging and System Monitoring
Show Running Queries
sql id="gbase_process_01"
SHOW FULL PROCESSLIST;
Show Errors
sql id="gbase_error_01"
SHOW ERRORS;
System Variables
sql id="gbase_vars_01"
SHOW VARIABLES LIKE '%compress%';
⚠️ Common Mistakes in GBase SQL Usage
- Not using proper distribution keys
- Overusing full table scans
- Ignoring indexes
- Poor permission management
- Lack of system monitoring
⚡ Best Practices
- Always define distribution strategy when creating tables
- Use views for complex queries
- Add indexes for frequent query fields
- Monitor system tables regularly
- Separate admin and application users
📌 Final Thoughts
Working with a GBase database requires more than writing SQL—it requires understanding:
- Data distribution models
- Security and permissions
- System metadata and monitoring
- Performance optimization basics
👉 Mastering these SQL patterns gives you a strong foundation for enterprise-level database work.
💬 What SQL feature do you use most often in your database projects—queries, views, or indexing?
`markdown
If you want, I can next:
- Turn this into a “GBase SQL interview questions + answers” guide
- Or merge it with performance tuning + transaction articles into a full dev.to series
- Or create a hands-on lab tutorial (step-by-step GBase practice course) 🚀
::contentReference[oaicite:0]{index=0}
`
Top comments (0)