After a prolonged break from coding, I found myself struggling to recall basic SQL syntax 😅

Reviewing my old SQL notes and adding some updates 😂

Primary references:

MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/tutorial.html

Yi Bai Tutorial: https://www.yiibai.com/mysql

"Learning SQL" by Alan Beaulieu

Basic Operations

---

1. Connection Commands

Connecting to a remote database:

$ mysql -h hostname -u username -p

(hostname: server address username: account name)

Connecting to a local database:

$ mysql -u username -p

Exiting the session:

$ quit

2. Status Indicators

Meanings of different prompt states:

Prompt	Description
mysql>	Ready for a new query
->	Waiting for continuation of a multi-line query
'>	Waiting for completion of a string starting with single quote (')
">	Waiting for completion of a string starting with double quote (")
``>`	Waiting for completion of an identifier starting with backtick (```)
/\*>	Waiting for completion of a comment block starting with /\*

Cancelling a query:

mysql> SELECT
    -> USER()
    -> \c
mysql>

3. Database Management

List all database on the server:

1 mysql> SHOW DATABASES;
2 +----------+
3 | Database |
4 +----------+
5 | mysql    |
6 | test     |
7 | tmp      |
8 +----------+

Select a database:

1 mysql> USE test
2 Database changed

Create a database:

1 CREATE DATABASE worker;

Remove a database:

1 mysql> DROP DATABASE worker;
2 Query OK, 0 rows affected (0.15 sec)

4. Table Operations

Once you have selected a database,

Syntax:

 1 CREATE TABLE [IF NOT EXISTS] table_name(
 2         column_list
 3 ) engine=table_type;
 4 /*
 5  *engine can be InnoDB, MyISAM, HEAP, EXAMPLE etc.
 6  *Default engine since MySQL 5.5 is InnoDB
 7  */
 8 
 9 column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
10 /*
11  *column_name specifies the column's name. Each column has a specific data type and size, e.g., VARCHAR(255). 
12  *NOT NULL or NULL indicates whether the column accepts NULL values. DEFAULT value sets a default for the column. 
13  *AUTO_INCREMENT ensures that the column value auto-increments with each new row insertion.
14  */
15 
16 PRIMARY KEY (id)
17 -- Used to define primary key

Example table creation:

1 CREATE TABLE IF NOT EXISTS student ( 
2 id INT(8) NOT NULL AUTO_INCREMENT,
3 name VARCHAR(20), 
4 number INT(8) DEFAULT NULL,
5 sex CHAR(1), 
6 birth DATE,
7 PRIMARY KEY (id)
8 )ENGINE=InnoDB;

Create table with structure copy:

1 CREATE TABLE s3 LIKE student;

Create table with data copy:

1 CREATE TABLE new_table 
2 SELECT col, col2, col3 
3 FROM
4     existing_table;

Create table with partial data copy:

1 CREATE TABLE new_table 
2 SELECT col1, col2, col3 
3 FROM
4     existing_table
5 WHERE
6     conditions;

Modify table structure:

(Actions include renaming and altering structure)

 1 -- Rename table
 2 ALTER TABLE s1
 3 RENAME TO student_bak;
 4 -- or
 5 RENAME TABLE s1 TO student_bak;
 6 
 7  
 8 -- Alter table structure
 9 
10 -- Modify column
11 ALTER TABLE student
12 CHANGE COLUMN number number INT(8) NOT NULL;
13  
14 -- Add column
15 ALTER TABLE student 
16 ADD COLUMN address VARCHAR(30) NULL
17 AFTER birth;
18  
19 -- Drop column
20 ALTER TABLE student
21 DROP COLUMN sex;

List tables in current database:

1 mysql> SHOW TABLES;
2 +-------------------+
3 | Tables_in_student |
4 +-------------------+
5 | student           |
6 +-------------------+
7 1 row in set (0.00 sec)

View table schema:

1 mysql> DESCRIBE student;
2 +--------+-------------+------+-----+---------+-------+
3 | Field  | Type        | Null | Key | Default | Extra |
4 +--------+-------------+------+-----+---------+-------+
5 | name   | varchar(20) | YES  |     | NULL    |       |
6 | number | int(8)      | YES  |     | NULL    |       |
7 | sex    | char(1)     | YES  |     | NULL    |       |
8 | birth  | date        | YES  |     | NULL    |       |
9 +--------+-------------+------+-----+---------+-------+

Drop a table:

1 mysql> DROP TABLE student;

5. CRUD Operations on Tables

Insert:

Inserting records:

Notes:

1. Prefer specifying column names in INSERT (method two), so the SQL remains valid if table schema changes.
2. Some columns can be omitted in INSERT operations, provided they allow NULL or have a default value.
3. Performance-wise, INSERT operations are resource-intensive and may impact SELECT performance; LOW_PRIORITY can reduce INSERT priority.

(If column values match, column names can be omitted during insert)

// Method one:<br></br>1 mysql> INSERT INTO student
2     -> VALUES ('Bob',201803224,'m','1999-03-30');
3 Query OK, 1 row affected (0.44 sec)

// Method two:<br></br>1 mysql> INSERT INTO student
2     -> (name,number,sex,birth)
3     -> VALUES
4     -> ('Lily',201805623,'f','1998-04-23');

Insert multiple records:

1 mysql> INSERT INTO student
2     -> (name,number,sex,birth)
3     -> VALUES
4     -> ('Li',201835623,'f','1998-04-13'),
5     -> ('Mi',201835923,'m','1994-04-03'),
6     -> ('Mark',201845723,'m','1994-03-02');
7 Query OK, 3 rows affected (0.09 sec)
8 Records: 3  Duplicates: 0  Warnings: 0

Insert from another table:

1 mysql> INSERT INTO s3
2     -> SELECT name,number,birth,address FROM student;
3 Query OK, 5 rows affected (0.12 sec)

Insert with duplicate key handling (ON DUPLICATE KEY UPDATE):

1 INSERT INTO student(id,name,number,birth,address)
2 VALUES (4,'Mi',201835923,'1994-04-03','')
3 ON DUPLICATE KEY UPDATE 
4    id = id + 2, 
5    address = 'Next Street';

Update:

Notes:

1. Be cautious with UPDATE commands to avoid omitting WHERE clauses, which would update all rows 😅.
2. Setting a column to NULL removes its value.

Updating data:

1 mysql> UPDATE student
2     -> SET
3     -> address = 'ONE Road',
4     -> name = 'Bon'
5     -> WHERE name = 'Bob';

Update based on join results:

-- teacher table
mysql> SELECT * FROM teacher;
+----+--------+
| id | name   |
+----+--------+
|  1 | LiMing |
|  2 | WangLu |
|  3 | Huali  |
+----+--------+
3 rows in set (0.00 sec)

-- student table
mysql> SELECT * FROM student;
+----+------+-----------+-------------+------------+------------+
| id | name | number    | address     | birth      | teacher_id |
+----+------+-----------+-------------+------------+------------+
|  1 | Bon  | 201803224 | ONE Road    | 1999-03-30 | 1          |
|  2 | Lily | 201805623 | NULL        | 1998-04-23 | NULL       |
|  3 | Li   | 201835623 | NULL        | 1998-04-13 | 2          |
|  5 | Mark | 201845723 | NULL        | 1994-03-02 | NULL       |
|  6 | Mi   | 201835923 | Next Street | 1994-04-03 | NULL       |
+----+------+-----------+-------------+------------+------------+
5 rows in set (0.00 sec)

-- Update students with NULL teacher_id
-- to have LiMing's id
mysql> UPDATE student
    -> SET
    -> teacher_id = (SELECT id
    -> FROM teacher
    -> WHERE name = 'LiMing')
    -> WHERE teacher_id IS NULL;

IGNORE keyword: When errors occur during UPDATE, the operation is rolled back unless IGNORE is used, allowing updates to proceed despite errors.

UPDATE IGNORE student ...

Delete:

Notes:

1. DELETE removes rows from a table but not the table itself.
2. To quickly remove all rows, use TRUNCATE TABLE instead of DELETE.

Syntax:

1 DELETE FROM table_name
2 WHERE condition;

Delete records:

-- Delete all records
mysql> DELETE FROM student_bak;

-- Delete based on condition
mysql> DELETE FROM student
    -> WHERE id = 6;

-- Delete limited rows after sorting
mysql> DELETE FROM student
    -> ORDER BY id
    -> LIMIT 2;
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM student;
+----+------+-----------+---------+------------+------------+
| id | name | number    | address | birth      | teacher_id |
+----+------+-----------+---------+------------+------------+
|  3 | Li   | 201835623 | NULL    | 1998-04-13 | 2          |
|  5 | Mark | 201845723 | NULL    | 1994-03-02 | NULL       |
+----+------+-----------+---------+------------+------------+
2 rows in set (0.00 sec)

TRUNCATE TABLE:

This command effectively recreates the table by deleting it and creating a new one, rather than removing rows individually.

TRUNCATE TABLE s;

Select:

Syntax:

 1 SELECT 
 2     column_1, column_2, ...
 3 FROM
 4     table_1
 5 [INNER | LEFT |RIGHT] JOIN table_2 ON conditions
 6 WHERE
 7     conditions
 8 GROUP BY column_1
 9 HAVING group_conditions
10 ORDER BY column_1
11 LIMIT offset, length;

The SELECT statement consists of several clauses listed below:

• SELECT followed by a comma-separated list of columns or * to return all columns.
• FROM specifies the table or view to query.
• JOIN retrieves data from other tables based on join conditions.
• WHERE filters rows in the result set.
• GROUP BY groups rows into subsets and applies aggregate functions.
• HAVING filters groups defined by GROUP BY.
• ORDER BY defines the column(s) for sorting.
• LIMIT restricts the number of returned rows.

The SELECT and FROM clauses are mandatory, others are optional.

Simple queries:

mysql> SELECT * FROM student;
+----+------+-----------+---------+------------+------------+
| id | name | number    | address | birth      | teacher_id |
+----+------+-----------+---------+------------+------------+
|  3 | Li   | 201835623 | NULL    | 1998-04-13 | 2          |
|  5 | Mark | 201845723 | NULL    | 1994-03-02 | NULL       |
+----+------+-----------+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT name,address FROM student;
+------+---------+
| name | address |
+------+---------+
| Li   | NULL    |
| Mark | NULL    |
+------+---------+
2 rows in set (0.00 sec)

Conditional queries:

# Select students with teacher_id = 2
mysql> SELECT * FROM student
    -> WHERE teacher_id = 2;
+----+------+-----------+------------+------------+------------+
| id | name | number    | address    | birth      | teacher_id |
+----+------+-----------+------------+------------+------------+
|  3 | Li   | 201835623 | NULL       | 1998-04-13 | 2          |
|  8 | Lily | 201805623 | 1998-04-23 | NULL       | 2          |
| 11 | Mary | 201845723 | 1994-03-02 | NULL       | 2          |
+----+------+-----------+------------+------------+------------+
3 rows in set (0.00 sec)

# Select students with teacher_id ≠ 2
mysql> SELECT * FROM student
    -> WHERE teacher_id <> 2;
+----+------+-----------+------------+------------+------------+
| id | name | number    | address    | birth      | teacher_id |
+----+------+-----------+------------+------------+------------+
|  5 | Mark | 201845723 | NULL       | 1994-03-02 | 1          |
|  7 | Bob  | 201803224 | 1999-03-30 | NULL       | 1          |
|  9 | Lim  | 201835623 | 1998-04-13 | NULL       | 1          |
| 10 | Mi   | 201835923 | 1994-04-03 | NULL       | 3          |
+----+------+-----------+------------+------------+------------+
4 rows in set (0.00 sec)

The following table lists comparison operators usable in WHERE clauses.

Operator	Description
=	Equal to, works with most data types.
<> or !=	Not equal to
<	Less then, typically used with numeric and date/time data.
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to