Skip to content
中文
9 min read#mysql

MySQL Basics

Study Notes

Updated:

阅读中文版

1. Overview of SQL

1.1 DML: Data Manipulation Language

DML is used to query and modify data records, including the following SQL statements:

INSERT: Add data to the database UPDATE: Modify data in the database DELETE: Delete data from the database SELECT: Select (query) data SELECT is the foundation of the SQL language and is the most important.

1.2 DDL: Data Definition Language

DDL is used to define the structure of the database, such as creating, modifying, or deleting database objects, including the following SQL statements:

CREATE TABLE: Create a database table ALTER TABLE: Change table structure, add, delete, modify column lengths DROP TABLE: Delete a table CREATE INDEX: Create an index on a table DROP INDEX: Delete an index

1.3 DCL: Data Control Language

DCL is used to control database access, including the following SQL statements:

GRANT: Grant access permissions REVOKE: Revoke access permissions COMMIT: Commit transaction processing ROLLBACK: Roll back transaction processing SAVEPOINT: Set a savepoint LOCK: Lock a specific part of the database

2. SQL Queries

2.1 Basic SELECT Statements

SELECT identifies which columns to select. Aliases follow the column name directly, and you can also add the keyword 'AS' between the column name and the alias. Use double quotes for aliases to include spaces or special characters and to distinguish case sensitivity.

FROM identifies which table to select from

select last_name as name, salary from employees

2.2 Filtering and Sorting Data

Use the WHERE clause to filter out rows that do not meet the conditions. WHERE follows FROM.

BETWEEN: Use the BETWEEN operator to display values within a range

# Employee names and salaries for salaries between 2500 and 3000
SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

LIKE: Use the LIKE operator to select similar values. The selection condition can contain characters or numbers: % represents zero or more characters (any number of characters), _ represents one character.

# Find employee names where the second letter is 'a'
SELECT last_name 
From  employees
WHERE last_name like '_a%';

NULL: Use IS (NOT) NULL to check for null values.

# Select the names and job_ids of employees who have no manager in the company
SELECT last_name,job_id FROM employees 
WHERE manager_id IS NULL;

Logical operations: AND OR NOT

ORDER BY: ASC (ascend): ascending order, DESC (descend): descending order. The ORDER BY clause is at the end of the SELECT statement.

SELECT last_name, department_id, salary
FROM   employees
WHERE salary<3000 OR salary>5000
ORDER BY department_id, salary DESC;

2.3 Multi-table Queries

To join n tables, you need at least n-1 join conditions.

SQL99: Use the ON clause to create joins.

In a natural join, columns with the same name are used as the join conditions. You can use the ON clause to specify additional join conditions. This join condition is separate from other conditions. The ON clause makes the statement more readable.

# Select employees working in the city of Toronto

SELECT last_name,job_id,d.department_id,department_name 
FROM employees e JOIN departments d 
ON e.department_id=d.department_id
JOIN locations l ON d.location_id=l.location_id
WHERE city='Toronto';

2.4 Single-row Functions

Case conversion functions:

image-20210303082756773

Character control functions:

image-20210303082903170

Numeric functions:

ROUND: Rounding TRUNCATE: Truncation MOD: Modulo (remainder)

CASE expression

# Query employee information for departments 10, 20, and 30. If the department number is 10, print 1.1 times their salary; for department 20, print 1.2 times their salary; for department 30, print 1.3 times their salary.

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      1.3*salary 
       END     "REVISED_SALARY"
FROM   employees;

2.5 Group Functions

AVG(), COUNT(), MAX(), MIN(), SUM()

GROUP BY: All columns in the SELECT list that are not included in group functions should be included in the GROUP BY clause.

# Query the names of all departments, their location_id, employee count, and average salary

SELECT department_name,location_id,COUNT(*),AVG(salary)

FROM employees e JOIN departments d

ON e.department_id=d.department_id 

GROUP BY department_name,location_id;

HAVING: Group functions cannot be used in the WHERE clause, but they can be used in the HAVING clause.

# Query the minimum salary of employees under each manager, where the minimum salary must not be lower than 6000. Employees without a manager are not included.

SELECT MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

2.6 Subqueries

A subquery (inner query) is executed once before the main query. The result of the subquery is used by the main query (outer query).

# Query the department IDs and their minimum salaries for departments whose minimum salary is greater than the minimum salary of department 50
SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);
# Query the employee ID, name, and salary of employees in each department whose salary is higher than the department's average salary (correlated subquery)

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary) avg_salary
	FROM employees e2
	WHERE e2.department_id=e1.department_id);
# Return the employee ID, name, job_id, and salary of employees in other departments whose salary is lower than all salaries in the 'IT_PROG' job_id department (multi-row subquery with ALL)

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

3. Creating and Managing Tables

# CREATE and related statements
create database employees;

# View all current databases
show databases;

# "Use" a database to make it the current database
use employees;

# Create a table using a subquery
create table emp2 as select * from employees where 1=2;

# ALTER TABLE statements

# Add a new column
ALTER TABLE dept80 
ADD job_id varchar(15);

# Modify a column: You can modify the column's data type, size, and default value
Alter table dept80
MODIFY	last_name VARCHAR(30);

# Delete a column: Use the DROP COLUMN clause to delete columns that are no longer needed.
ALTER TABLE  dept80
DROP COLUMN  job_id;

# Rename a column: Use the CHANGE old_column new_column dataType clause to rename a column
ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15);

# Delete a table
DROP TABLE dept80;

# Truncate a table 
TRUNCATE TABLE detail_dept;

# The TRUNCATE statement cannot be rolled back. The DELETE statement deletes data and can be rolled back.

4. Data Management

# INSERT: Use the INSERT statement to insert data into a table. 

INSERT INTO departments(department_id, department_name, 
                        manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);


# UPDATE: Use the UPDATE statement to update data.

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

# DELETE: Use the DELETE statement to delete data from a table. If the WHERE clause is omitted, all data in the table will be deleted.

DELETE FROM departments
WHERE  department_name = 'Finance';

# Transaction: A set of logical operation units that transforms data from one state to another. A database transaction consists of one or more DML statements.

5. Constraints and Pagination

5.1 Constraints

What are constraints?

To ensure data consistency and integrity, SQL specifications impose additional conditions on table data through constraints. Constraints are mandatory rules at the table level.

Constraints can be defined when creating a table (via the CREATE TABLE statement) or after the table has been created (via the ALTER TABLE statement).

Classification of constraints:

  1. NOT NULL - Non-null constraint, specifies that a field cannot be empty
  2. UNIQUE - Unique constraint, specifies that a field is unique within the entire table
  3. PRIMARY KEY - Primary key (non-null and unique)
  4. FOREIGN KEY - Foreign key
  5. CHECK - Check constraint
  6. DEFAULT - Default value

MySQL does not support CHECK constraints, but you can use the CHECK constraint syntax without any effect;

  • NOT NULL Constraint
    # Create a NOT NULL constraint

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL
);
# Add a NOT NULL constraint
ALTER TABLE emp
modify sex varchar(30) not null;
  • UNIQUE Constraint
    # A table can have multiple unique constraints, including composite constraints on multiple columns. When creating a unique constraint, if you don't give it a name,
# it defaults to the column name. MySQL automatically creates a unique index on the column(s) with the unique constraint.
CREATE TABLE USER(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 # Using table-level constraint syntax
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

# Add a unique constraint
ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
MODIFY NAME VARCHAR(30) UNIQUE;

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(name,pwd);

# Drop a unique constraint
ALTER TABLE USER
DROP INDEX uk_name_pwd;
  • PRIMARY KEY

    The primary key constraint is equivalent to a combination of a unique constraint and a non-null constraint. Columns with a primary key constraint cannot have duplicate values or null values.

    Each table can have at most one primary key. The primary key constraint can be created at the column level or at the table level.

    The primary key name in MySQL is always PRIMARY. When a primary key constraint is created, the system automatically creates a corresponding unique index on the column or column combination.

    # Column level
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);

 # Table level
 CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

# Composite
CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);
  • FOREIGN KEY

    The foreign key constraint ensures referential integrity between one or two tables. A foreign key establishes a referential relationship between two fields in one table or between fields in two tables.

    The foreign key value in the child table must either be found in the parent table or be null. When a record in the parent table is referenced by the child table, the parent table record cannot be deleted. To delete data,

    you must first delete the data in the child table that depends on that record, and then you can delete the data from the parent table. Another option is to cascade delete the child table data.

    Note: The referenced column in the foreign key constraint can only reference a primary key or a unique key constraint column in the parent table. A table can have multiple foreign key constraints.

    # To create a composite foreign key, you must use table-level constraints:
# Parent table
CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number)
);

# Child table
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number) 
REFERENCES classes(NAME,number)
);

5.2 Pagination

In MySQL, use LIMIT to implement pagination. The principle is: pagination display means displaying the result set from the database in segments as needed.

The LIMIT statement must be placed at the very end of the entire query statement.

# Formula: (current page number - 1) * page size, page size
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

Related posts

By shared tags

Common Big Data Commands

Some common commands for big data

The Self-Cultivation of a SQL Engineer

Since I personally prefer using window functions, I prioritized them in my solutions. Of course, these may not always be the optimal approaches—the answers are for reference only.

Comments(0)

…
…