Database Fundamentals

Why Learn Databases?

Previously, to store data permanently, we used I/O streams to write data to local files. However, tasks like changing a specific user's age in a text file were cumbersome, requiring reading lines, creating objects, modifying them, and writing back. Databases offer a far more efficient solution.

What is a Database?

A database is a structured repository for storing, managing, and retrieving data. It is commonly abbreviated as DB.

Advantages of Using a Database

• Persistence: Data is stored permanently.
• Management: Offers convenient tools for storing and managing data.
• Standard Interface: Uses a unified language (SQL) to operate on data.

Common Database Systems

Popular database systems include MySQL, Oracle, PostgreSQL, and SQL Server.

MySQL Database: Installation and Setup

Overview

MySQL is an open-source (free before version 6), lightweight relational database management system owned by Oracle Corporation.

Installation on Linux (CentOS 7)

1. Connect to your Linux system using a tool like SecureCRT.
2. Upload the MySQL RPM bundle to the server (e.g., using alt + p then put command).
3. Extract the archive:

   mkdir mysql
   tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C mysql/
   

4. Install the client package:

   cd mysql/
   rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm --force --nodeps
   

5. Install the server package:

   rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm --force --nodeps
   

6. Configure default character set:

   vi /etc/my.cnf
   

   Add these lines under the [mysqld] section:

   [mysqld]
   character-set-server=utf8
   collation-server=utf8_general_ci
   

   Add this at the end of the file:

   [client]
   default-character-set=utf8
   

7. Start the MySQL service:

   service mysqld start
   

8. Log in using the temporary password:

   mysql -u root -p
   

   The initial password is found in /var/log/mysqld.log after root@localhost:.
9. Change the root password:

   SET GLOBAL validate_password_policy=0;
   SET GLOBAL validate_password_length=1;
   SET PASSWORD = PASSWORD('YourNewPassword');
   

10. Grant remote access privileges:

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YourPassword';
    FLUSH PRIVILEGES;
    

11. Disable the firewall:

    systemctl stop firewalld.service
    

Structured Query Language (SQL)

The Relationship Between Databases, Tables, and Data

• Database: A warehouse containing multiple tables.
• Table: The core structure, composed of columns (fields) and rows (records), similar to a spreadsheet.
• Data: The actual stored records.

SQL Syntax and Classification

SQL (Structured Query Language) defines standard operations for relational databases. Key syntax rules:

• Statements end with a semicolon.
• Keywords are case-insensitive but conventionally written in uppercase.
• Comments: -- for single-line, /* */ for multi-line, # (MySQL specific) for single-line.

SQL is divided into:

• DDL (Data Definition Language): Defines database objects (CREATE, DROP, ALTER).
• DML (Data Manipulation Language): Manipulates tible data (INSERT, UPDATE, DELETE).
• DQL (Data Query Language): Queries data (SELECT).
• DCL (Data Control Language): Manages permissions and security (e.g., GRANT).

DDL: Working with Databases

Querying Databases

-- Show all databases
SHOW DATABASES;

-- View creation statement for a specific database
SHOW CREATE DATABASE database_name;

Creating Databases

-- Create a database
CREATE DATABASE db1;

-- Create with condition to avoid error
CREATE DATABASE IF NOT EXISTS db1;

-- Create with character set
CREATE DATABASE db3 CHARACTER SET utf8;

-- Example: create db4 with gbk charset
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;

Modifying Databases

-- Change character set
ALTER DATABASE db4 CHARACTER SET utf8;

Deleting Databases

-- Drop a database
DROP DATABASE db1;

-- Drop with condition
DROP DATABASE IF EXISTS db2;

Using a Database

-- Show currently selected database
SELECT DATABASE();

-- Switch to a database
USE db4;

DDL: Working with Tables

Querying Tables

-- List all tables in the current database
USE mysql;
SHOW TABLES;

-- Show table structure
DESC table_name;

-- Show table character set
SHOW TABLE STATUS FROM database_name LIKE 'table_name';

Creating Tables

CREATE TABLE product (
    id INT,
    name VARCHAR(30),
    price DOUBLE,
    stock INT,
    insert_time DATE
);

-- Copy a table structure
CREATE TABLE product2 LIKE product;

Modifying Tables

-- Rename table
ALTER TABLE product2 RENAME TO product3;

-- Change character set
ALTER TABLE product3 CHARACTER SET gbk;

-- Add a column
ALTER TABLE product3 ADD color VARCHAR(10);

-- Modify column data type
ALTER TABLE product3 MODIFY color INT;

-- Rename column and change type
ALTER TABLE product3 CHANGE color address VARCHAR(30);

-- Drop a column
ALTER TABLE product3 DROP address;

Deleting Tables

-- Drop a table
DROP TABLE product3;

-- Drop with condition
DROP TABLE IF EXISTS product3;

DML: Inserting Data

-- Insert into specific columns
INSERT INTO product (id, name, price) VALUES (2, 'Computer', 4999);

-- Insert into all columns
INSERT INTO product VALUES (3, 'TV', 2999, 18, '2099-06-06');

-- Batch insert
INSERT INTO product VALUES 
(4, 'Refrigerator', 999, 26, '2099-08-08'),
(5, 'Washing Machine', 1999, 32, '2099-05-10');

DML: Updating Data

-- Update specific records
UPDATE product SET price = 3500 WHERE name = 'Phone';

-- Update multiple columns
UPDATE product SET price = 1800, stock = 36 WHERE name = 'TV';

-- Always use a WHERE clause to avoid updating all rows

DML: Deleting Data

-- Delete specific records
DELETE FROM product WHERE name = 'Microwave';

-- Always use a WHERE clause

DQL: Querying Data from a Single Table

Assume we have a product table with columns: id, name, price, brand, stock, insert_time.

Basic Queries

-- Select all columns
SELECT * FROM product;

-- Select specific columns
SELECT name, price, brand FROM product;

-- Select distinct values
SELECT DISTINCT brand FROM product;

-- Computed column and null handling
SELECT name, IFNULL(stock, 0) + 10 AS updated_stock FROM product;

Conditional Queries

-- Comparison operators
SELECT * FROM product WHERE stock > 20;
SELECT * FROM product WHERE brand = 'Huawei';

-- Range queries
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;

-- Set membership
SELECT * FROM product WHERE stock IN (14, 30, 23);

-- Null checks
SELECT * FROM product WHERE stock IS NULL;
SELECT * FROM product WHERE stock IS NOT NULL;

-- Pattern matching (LIKE)
SELECT * FROM product WHERE name LIKE 'Xiaomi%';
SELECT * FROM product WHERE name LIKE '_i%';
SELECT * FROM product WHERE name LIKE '____';

Aggregate Functions

--- Count, sum, avg, max, min
SELECT COUNT(*) FROM product;
SELECT MAX(price) FROM product;
SELECT SUM(stock) FROM product WHERE brand = 'Apple';
SELECT AVG(price) FROM product WHERE brand = 'Xiaomi';

Sorting

-- Ascending (default) and descending
SELECT * FROM product ORDER BY stock ASC;
SELECT * FROM product WHERE name LIKE '%Phone%' ORDER BY price DESC;

-- Multiple sort criteria
SELECT * FROM product ORDER BY price ASC, stock DESC;

Grouping

-- Group by brand and calculate sum price
SELECT brand, SUM(price) AS total_price FROM product GROUP BY brand;

-- Filter before grouping
SELECT brand, SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

-- Filter after grouping (HAVING)
SELECT brand, SUM(price) AS total_price 
FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING total_price > 7000;

-- Combine with sorting
SELECT brand, SUM(price) AS total_price 
FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING total_price > 7000 
ORDER BY total_price DESC;

Pagination (LIMIT)

-- LIMIT offset, row_count
-- Formula for offset: (page_number - 1) * rows_per_page

-- Show 2 records per page
SELECT * FROM product LIMIT 0, 2;  -- Page 1
SELECT * FROM product LIMIT 2, 2;  -- Page 2
SELECT * FROM product LIMIT 4, 2;  -- Page 3

Constraints

Overview

Constraints enforce rules on data to ensure accuracy, validity, and integrity. Common types:

Constraint	Description
PRIMARY KEY	Uniquely identifies each row (unique + not null)
AUTO_INCREMENT	Automatically generates unique integer values for a primary key
UNIQUE	Ensures all values in a column are different
NOT NULL	Ensures a column cannot have a NULL value
FOREIGN KEY	Links tables together
ON UPDATE CASCADE	Updates foreign key values when parent key is updated
ON DELETE CASCADE	Deletes child rows when parent row is deleted

Primary Key

-- Define at table creation
CREATE TABLE student (id INT PRIMARY KEY);

-- Drop primary key
ALTER TABLE student DROP PRIMARY KEY;

-- Add after creation
ALTER TABLE student MODIFY id INT PRIMARY KEY;

Auto-Increment

-- Define at table creation
CREATE TABLE student2 (id INT PRIMARY KEY AUTO_INCREMENT);

-- Drop auto-increment
ALTER TABLE student2 MODIFY id INT;

-- Add after creation
ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

Unique Constraint

-- Define at table creation
CREATE TABLE student3 (id INT PRIMARY KEY AUTO_INCREMENT, tel VARCHAR(20) UNIQUE);

-- Drop unique constraint
ALTER TABLE student3 DROP INDEX tel;

-- Add after creation
ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

Not Null Constraint

-- Define at table creation
CREATE TABLE student4 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL);

-- Drop not null constraint
ALTER TABLE student4 MODIFY name VARCHAR(20);

-- Add after creation
ALTER TABLE student4 MODIFY name VARCHAR(20) NOT NULL;