Introduction to SQL and Basic Commands

Introduction to SQL

What is SQL?

SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows users to create, read, update, and delete data within a database. SQL provides a way to interact with databases using simple, declarative statements.

Why Learn SQL?

Learning SQL is essential for several reasons:

• Data Management: SQL is the primary language for managing and querying relational databases, which are widely used in various applications.
• Career Opportunities: Proficiency in SQL is a valuable skill for many IT and data-related roles, including database administrators, data analysts, and software developers.
• Data Analysis: SQL enables you to perform complex data analysis and reporting tasks, making it easier to extract valuable insights from large datasets.
• Versatility: SQL is used in many popular database systems, such as MySQL, PostgreSQL, and Microsoft SQL Server, making it a versatile skill across different platforms.

SQL Database and Table Operations Guide

Database Operations

Creating a Database

CREATE DATABASE FirstDB;

Note: FirstDB is the database name.

Using a Database

USE FirstDB;

Note: This selects the database for use.

Dropping a Database

DROP DATABASE FirstDB;

Note: This permanently deletes the database and all its contents.

Altering Database (Set to Read-Only)

ALTER DATABASE FirstDB READ ONLY = 1;

Note: This makes the database read-only, preventing any modifications.

Table Operations

Creating a Table

CREATE TABLE student (
    student_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(50),
    student_address VARCHAR(50),
    hourly_pay DECIMAL(5,2),
    student_date DATE
);

Note: This creates a table named 'student' with specified columns and data types.

Selecting All Data from a Table

SELECT * FROM student;

Note: This retrieves all rows and columns from the 'student' table.

Renaming a Table

RENAME TABLE student TO students;

Note: This changes the table name from 'student' to 'students'.

Altering Table Structure

Adding a New Column

ALTER TABLE students
ADD phone_number VARCHAR(15);

Note: This adds a new column 'phone_number' to the 'students' table.

Renaming a Column

ALTER TABLE students
CHANGE phone_number email VARCHAR(100);

Note: This changes the column name from 'phone_number' to 'email' and modifies its data type.

Modifying a Column's Data Type

ALTER TABLE students 
MODIFY COLUMN email VARCHAR(100);

Note: This changes the data type of the 'email' column to VARCHAR(100).

Changing a Column's Position

ALTER TABLE students
MODIFY email VARCHAR(100) AFTER last_name;

Note: This moves the 'email' column to be after the 'last_name' column.

ALTER TABLE students
MODIFY email VARCHAR(100) FIRST;

Note: This moves the 'email' column to be the first column in the table.

Dropping a Column

ALTER TABLE students
DROP COLUMN email;

Note: This permanently removes the 'email' column from the table.

Combining Multiple Operations

ALTER TABLE students
MODIFY email VARCHAR(100) AFTER last_name;
SELECT * FROM students;

Note: This changes the column position and then displays the new table structure in one operation.

The above is the detailed content of Introduction to SQL and Basic Commands. For more information, please follow other related articles on the PHP Chinese website!