SQL Introduction

SQL (Structured Query Language) is a standard language for managing relational databases. This chapter introduces the history, features, and application scenarios of SQL, helping you build a basic understanding of SQL.

What is SQL?

SQL is a programming language specifically designed for interacting with relational databases. It uses simple commands to perform various database operations such as querying, inserting, updating, and deleting data.

SQL Pronunciation

• English pronunciation: /ˌɛs kjuːˈɛl/
• Chinese pronunciation: S-Q-L or "sequel"
• Both pronunciations are correct

History of SQL

Origins

• 1970s: Donald D. Chamberlin and Raymond F. Boyce of IBM developed the predecessor of SQL
• 1974: The SEQUEL language first appeared
• 1979: Oracle released the first commercial SQL implementation

Standardization

• 1986: ANSI released the first SQL standard (SQL-86)
• 1987: ISO adopted the SQL standard
• 1989: SQL-89 standard released
• 1992: SQL-92 standard released (most important version)
• 1999: SQL-99 standard released (introduced object-relational features)
• 2003: SQL:2003 standard released
• 2006: SQL:2006 standard released
• 2008: SQL:2008 standard released
• 2011: SQL:2011 standard released
• 2016: SQL:2016 standard released
• 2023: SQL:2023 standard released

Features of SQL

1. Declarative Language

SQL is a declarative language. You only need to tell the database "what you want" rather than "how to do it."

-- Declarative: Tell the database what you want
SELECT name, age FROM users WHERE age > 18;

-- Imperative (like Python): Tell the computer how to do it
for user in users:
    if user.age > 18:
        print(user.name)

2. Concise and Easy to Learn

SQL syntax is concise, with only a few core commands:

SELECT  -- Query data
INSERT  -- Insert data
UPDATE  -- Update data
DELETE  -- Delete data

3. Powerful

SQL can complete complex database operations:

• Data query and retrieval
• Data insertion, update, deletion
• Database and table management
• Permission and security control
• Transaction processing

4. Cross-Platform Compatible

SQL is a standard language supporting multiple database systems:

Database	Use Cases
MySQL	Web applications, open source projects
PostgreSQL	Enterprise applications, GIS
SQL Server	Windows enterprise environments
Oracle	Large enterprises, financial systems
SQLite	Mobile applications, embedded systems

SQL Categories

1. DDL (Data Definition Language)

Used to define database structure:

CREATE DATABASE  -- Create database
CREATE TABLE     -- Create table
ALTER TABLE      -- Modify table structure
DROP TABLE       -- Delete table

2. DML (Data Manipulation Language)

Used to manipulate data:

SELECT  -- Query data
INSERT  -- Insert data
UPDATE  -- Update data
DELETE  -- Delete data

3. DCL (Data Control Language)

Used to control access permissions:

GRANT   -- Grant permissions
REVOKE  -- Revoke permissions

4. TCL (Transaction Control Language)

Used to manage transactions:

COMMIT  -- Commit transaction
ROLLBACK -- Rollback transaction

SQL vs NoSQL

SQL Databases (Relational)

Features:

• Data stored in tabular format
• Uses SQL language for queries
• Supports transactions (ACID)
• Fixed data schema

Use cases:

• Applications requiring transaction support
• Structured data storage
• Complex query requirements

NoSQL Databases (Non-relational)

Features:

• Flexible data models
• Does not use SQL
• High performance and scalability
• Flexible data schema

Use cases:

• Big data storage
• Rapid prototyping
• Unstructured data

Application Scenarios of SQL

1. Data Analysis

-- Analyze user age distribution
SELECT
    CASE
        WHEN age < 20 THEN 'Teenager'
        WHEN age < 30 THEN 'Young Adult'
        WHEN age < 50 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group;

2. Backend Development

-- User login verification
SELECT * FROM users
WHERE username = 'zhangsan'
AND password = 'password123';

3. Report Generation

-- Generate monthly sales report
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

4. Data Mining

-- Discover purchase patterns
SELECT
    user_id,
    COUNT(DISTINCT product_id) AS unique_products
FROM order_items
GROUP BY user_id
ORDER BY unique_products DESC;

Benefits of Learning SQL

1. Career Development

Mastering SQL can bring:

• More job opportunities
• Higher salary levels
• Broader career development space

2. Data Thinking

Learning SQL cultivates your data thinking:

• Understanding data structures
• Learning data analysis
• Improving logical thinking abilities

3. High Practicality

SQL is very practical in actual work:

• Daily data processing
• Quick data querying
• Automated report generation

Common Misconceptions

Misconception 1: SQL is only a query tool

Correction: SQL can not only query but also perform complex data operations and analysis.

Misconception 2: SQL is difficult to learn

Correction: SQL syntax is concise, easy to get started with, but mastery requires practice.

Misconception 3: SQL is outdated

Correction: SQL is still the core language in the database field and is widely used.

Misconception 4: You must know programming to learn SQL

Correction: SQL is independent of programming languages; you can learn it without a programming background.

Future of SQL

Development Trends

1. Cloud Databases: More and more enterprises use cloud database services
2. Real-time Analytics: Growing demand for real-time data processing
3. Machine Learning Integration: SQL combined with machine learning
4. Multi-model Databases: Fusion of SQL and NoSQL

New Features

• JSON support
• Graph database queries
• Time-series data processing
• Geospatial data support

Preparing to Learn

Choose a Database System

As a beginner, it is recommended to start with the following databases:

1. MySQL: Free, easy to use, rich documentation
2. PostgreSQL: Powerful, open source
3. SQLite: No installation required, suitable for learning

Install Environment

Download and install the corresponding software based on your chosen database.

Summary

This chapter introduced basic concepts of SQL:

• SQL Definition: Structured Query Language for managing relational databases
• Historical Development: Started in the 1970s, went through multiple standardizations
• Main Features: Declarative, concise, powerful, cross-platform
• Language Categories: DDL, DML, DCL, TCL
• Application Scenarios: Data analysis, backend development, report generation, etc.
• Learning Value: Enhance career competitiveness, cultivate data thinking

SQL is an essential skill in the data era. Regardless of your career direction, mastering SQL will be of great help to your work.

Next Step: Learn Database Basics to understand basic concepts of databases.