Database Management
Overview
Database operations involve maintaining and administering database systems. Core responsibilities include executing SQL queries for data manipulation, performing backups, and implementing high availability solutions such as master-slave replication and read-write separation.
A database serves as an organized repository for storing and managing data. A Database Management System (DBMS) is software that enables effective data organization, management, and retrieval.
Popular database systems include MySQL, Oracle (commonly used in large-scale data systems), SQL Server, MariaDB (MySQL fork), and PostgreSQL.
Relational vs Non-Relational Databases
Relational databases like MySQL, Oracle, and PostgreSQL organize data in two-dimensional tables with rows and columns. Columns define object attributes (fields), while rows contain the actual data records.
Non-relational databases store data in key-value pairs and include:
- Cache databases: Redis
- Index databases: Elasticsearch
- Document databases: MongoDB
Comparison:
| Aspect | Relational | Non-Relational |
|---|---|---|
| Advantages | Clear table structure, flexible logic, complete records | High concurrent read/write, efficient for massive data, scalable architecture |
| Disadvantages | Slower reads/writes, poor cocnurrency, complex migration | Complex data logic, data loss risk on restart (data stored in cache) |
MySQL Data Types
Character Types:
| Type | Description |
|---|---|
CHAR(n) |
Fixed-length string, always occupies n bytes regardless of actual content |
VARCHAR(n) |
Varible-length string, occupies only needed space plus 1 byte for terminator |
CHAR offers better read/write performance due to contiguous storage, while VARCHAR saves disk space but may create fragmentation during updates.
Numeric Types:
| Type | Description |
|---|---|
INT |
Integer values |
FLOAT(m,d) |
Single-precision, m = total digits, d = decimal places |
DOUBLE(m,d) |
Double-precision, m = total digits, d = decimal places |
DECIMAL(5,2) |
Fixed-point for financial precision, 5 total digits, 2 decimal |
SMALLINT |
Small integer |
BIGINT |
Large integer |
Date and Time Types:
| Type | Format |
|---|---|
DATE |
YYYY-MM-DD |
DATETIME |
YYYY-MM-DD HH:MM:SS |
TIMESTAMP |
Like DATETIME but auto-updates to current time |
SQL Terminology
Key terms in SQL:
- Database: collection of data
- Table: structured data with rows and columns
- Row: a single record
- Column: a field attribute
- Index: performance optimization structure
- View: virtual table based on query results
- User: database account
- Privilege: access permissions
- Stored Procedure: precompiled SQL code
- Stored Function: reusable function in database
- Trigger: automated response to events
SQL Syntax Standards
- SQL statements are case-insensitive, but convention recommends uppercase
- Statements must end with a semicolon
- Naming rules: identifiers must start with a letter, followed by numbers or special characters
- Avoid MySQL reserved words like
TABLE,DATABASE - Database, table, and column names may be case-sensitive depending on configuration
SQL Language Categories
1. DDL (Data Definition Language) Creating and modifying database objects.
DROP TABLE student_records;
DROP DATABASE project_db;
2. DML (Data Manipulation Language) Managing data within tables.
INSERT INTO employee(id, name, salary) VALUES(1, 'Smith', 75000.00);
INSERT INTO employee VALUES(2, 'Jones', 82000.50);
UPDATE employee SET salary = 85000.00 WHERE id = 4;
DELETE FROM employee WHERE id = 2;
3. DQL (Data Query Language) Querying database contents.
SELECT id, name FROM employee;
SELECT * FROM employee LIMIT 0, 3;
SELECT DISTINCT name FROM employee;
SELECT name FROM employee WHERE id = 4 AND salary = 88000.00;
SELECT * FROM employee WHERE name LIKE 'J%';
SELECT * FROM employee WHERE name LIKE '%son';
SELECT * FROM employee WHERE name LIKE '%mit%';
4. DCL (Data Control Language) Managing user access and permissions.
GRANT SELECT ON employee TO user_analyst;
REVOKE INSERT ON employee FROM user_analyst;
5. TCL (Transaction Control Language) Managing transactions and database consistency.
COMMIT;
ROLLBACK;
SAVEPOINT checkpoint_1;
Table Structure Modifications
ALTER TABLE student_records RENAME TO stu01;
ALTER TABLE stu01 ADD address VARCHAR(50) DEFAULT 'Not Specified';
ALTER TABLE stu01 MODIFY COLUMN address CHAR(10);
ALTER TABLE stu01 DROP COLUMN address;
ALTER TABLE stu01 CHANGE COLUMN score表现的 new_score CHAR(10);
Null vs Empty Values
- NULL: absence of a value, truly empty
- Empty string: a valid value that contains nothing
These are fundamentally different in SQL operations and comparisons.