The Most Comprehensive Summary of SQL Fundamentals Ever Theory and Examples

Review all blog information to know more about databases and our products.

The Most Comprehensive Summary of SQL Fundamentals Ever Theory and Examples

Contents

  1. SQL Overview

  2. SQL Classification

  3. DDL（Data Definition Language）

  4. DML（Data Manipulation Language）

  5. DCL（Data Control Language）

  6. DQL（Data Query Language）

1. SQL Overview

1.1 What is SQL

SQL (Structured Query Language) is a language used for managing relational databases. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, etc. The SQL standard (ANSI/ISO) includes:
  • SQL-92: SQL language standard released in 1992.
  • SQL:1999: SQL language standard released in 1999.
  • SQL:2003: SQL language standard released in 2003.

These standards, like versions of the JDK, always have syntax changes in new versions. Databases at different times have implemented different standards.

Although SQL can be used in all relational databases, many databases have some syntax after the standard, which we can call “dialects.” For example, the LIMIT statement in MySQL is unique to MySQL, and other databases do not support it! Of course, Oracle or SQL Server also have their own dialects.

1.2 Syntax Requirements

  • SQL statements can be written on one or more lines, ending with a semicolon.
  • Spaces and indentation can enhance the readability of statements.
  • Keywords are case-insensitive, but uppercase is recommended.

2.SQL Classification

  • DDL (Data Definition Language): Used to define database objects: databases, tables, columns, etc.
  • DML (Data Manipulation Language): Used to define database records (data).
  • DCL (Data Control Language): Used to define access permissions and security levels.
  • DQL (Data Query Language): Used to query records (data).

3. DDL（Data Definition Language）

3.1 Basic Operations

  • Show all databases: SHOW DATABASES;
  • Switch databases: USE mydb1;

    3.1.1 Operating Databases

  1. Create a database: CREATE DATABASE [IF NOT EXISTS] mydb1;
  • Example: CREATE DATABASE mydb1;creates a database named mydb1. If this database already exists, an error will occur.
  • Example: CREATE DATABASE IF NOT EXISTS mydb1; creates the database mydb1 if it does not exist, avoiding errors.
  1. Delete a database: DROP DATABASE [IF EXISTS] mydb1;
  • Example: DROP DATABASE mydb1; deletes the database named mydb1. If this database does not exist, an error will occur.
  • Example: DROP DATABASE IF EXISTS mydb1; does not report errors even if mydb1 does not exist.
  1. Modify database encoding: ALTER DATABASE mydb1 CHARACTER SET utf8;
  • Modify the encoding of the mydb1 database to utf8. Note that in MySQL, all UTF-8 encodings cannot use the middle "-", so UTF-8 should be written as UTF8.

3.1.2 Data Types

MySQL, like Java and C, also has data types mainly applied to columns. Common types:

  • int: integer
  • double: floating point type, for example, double(5,2) indicates up to 5 digits, of which 2 must be decimals, i.e., the maximum value is 999.99;
  • decimal: generic type, used in form wire aspects, because there is no problem of accuracy missing;
  • char: fixed-length string type; (When the input character is not enough in length, it will be padded with spaces)
  • varchar: fixed-length string type;
  • text: string type;
  • blob: byte type;
  • date: date type, format: yyyy-MM-dd;
  • time: time type, format: hh:mm:ss;
  • timestamp: timestamp type;

3.1.3 Operating Tables

Create a table 
1
2
3
4
CREATE TABLE table_name( 
column_name column_type, 
column_name column_type,
 ... );

Create stu table

1
2
3
4
5
6
 CREATE TABLE stu(
	sid CHAR(6), 
	sname VARCHAR(20), 
	age INT, 
	gender VARCHAR(10)
);

View the structure of the table
DESC table_name;

Delete table
DROP TABLE table_name;

Modify the table

  1. Add a column: Add the classname column to the stu table
    ALTER TABLE stu ADD (classname varchar(100));

  2. Modify the data type of a column: Change the gender column type of the stu table to CHAR(2)
    ALTER TABLE stu MODIFY gender CHAR(2);

  3. Rename a column: Change the gender column name of the stu table to sex
    ALTER TABLE stu change gender sex CHAR(2);

  4. Delete a column: Delete the classname column from the stu table
    ALTER TABLE stu DROP classname;

  5. Modify the table name: Change the stu table name to student
    ALTER TABLE stu RENAME TO student;

4. DML（Data Manipulation Language）

4.1 Inserting Data

Syntax 1：INSERT INTO table_name(column_name1, column_name2, ...) VALUES(value1, value2, ...);

1
INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'Tom', 23, 'male');
1
INSERT INTO stu(sid, sname) VALUES('s_1001', 'Tom');

Syntax 2: INSERT INTO table_name VALUES(value1, value2, ...);
Since no specific column is specified to insert, all column values in the order they were created in the table are used:

1
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

4.2 Modifying Data

Syntax:UPDATE 表名 SET 列名 1=值 1, … 列名 n=值 n [WHERE 条件];

1
2
3
4
5
UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

4.3 Deleting Data

Syntax 1: DELETE FROM table_name [WHERE condition];

1
2
3
DELETE FROM stu WHERE sid=’s_1001’003B;
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;

Syntax 2: TRUNCATE TABLE table_name;

1
TRUNCATE TABLE stu;

The difference between the two:
Although TRUNCATE and DELETE can delete all records in a table, they have different principles. The efficiency of DELETE is not as high as TRUNCATE!

TRUNCATE is actually a DDL statement because it first drops the table and then recreates it. Moreover, records deleted by TRUNCATE cannot be rolled back, but records deleted by DELETE can be rolled back (rollback is knowledge of transactions!).

5. DCL（Data Control Language）

5.1 Creating Users

Syntax: CREATE USER 'username'@host IDENTIFIED BY 'password';

1
2
CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;

5.2 Granting Permissions

Syntax: GRANT permission1, ..., permissionN ON database.* TO 'username'@host;

1
2
GRANT  CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT  ON mydb1.*  TO  'user1'@localhost;  
GRANT  ALL  ON mydb1.*  TO user2@localhost;

5.3 Revoking Permissions

Syntax: REVOKE permission1, ..., permissionN ON database.* FROM 'username'@host;

1
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM 'user1'@localhost;

5.4 Viewing User Permissions

Syntax: SHOW GRANTS FOR 'username'@host;

1
SHOW GRANTS FOR 'user1'@localhost;

5.5 Deleting Users

Syntax: DROP USER 'username'@host;

1
DROP USER ‘user1’@localhost;

5.6 Modifying User Passwords

Syntax:
SET PASSWORD FOR 'username'@host = PASSWORD('new_password');

6. DQL（Data Query Language）

Executing DQL statements in a database does not change data, but instead sends the result set to the client.
  • select column_name —-> specifies the columns to retrieve
  • from table_name —-> specifies the table from which to retrieve data
  • where condition —-> specifies rows to retrieve based on a condition
  • group by group_column —-> groups the results based on a column
  • having group_condition —-> specifies which grouped rows to include
  • order by sort_column —-> sorts the results
  • limit start_row, row_count —-> limits the number of rows returned

Creating Database

  • Student Table: stu
Column Name Data Type Description
sid char(6) Student ID
sname varchar(50) Student name
age int Student age
gender varchar(50) Student gender 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE stu ( 
sid CHAR(6), 
sname VARCHAR(50),
 age INT, 
 gender VARCHAR(50) 
 ); 
 INSERT INTO stu VALUES('S_1001', 'Ethan Brown', 35, 'male'); 
 INSERT INTO stu VALUES('S_1002', 'Olivia Taylor', 15, 'female'); 
 INSERT INTO stu VALUES('S_1003', 'Liam Johnson', 95, 'male'); 
 INSERT INTO stu VALUES('S_1004', 'Emma Martinez', 65, 'female'); 
 INSERT INTO stu VALUES('S_1005', 'Noah Anderson', 55, 'male'); 
 INSERT INTO stu VALUES('S_1006', 'Ava Garcia', 75, 'female'); 
 INSERT INTO stu VALUES('S_1007', 'William Davis', 25, 'male'); 
 INSERT INTO stu VALUES('S_1008', 'Sophia Wilson', 45, 'female'); 
 INSERT INTO stu VALUES('S_1009', 'James Clark', 85, 'male'); 
 INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); 
 INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
  • Employee Table: emp
Column Name Data Type Description
empno int Employee number
ename varchar(50) Employee name
job varchar(50) Employee job
mgr int Manager’s number
hiredate date Hire date
sal decimal(7,2) Monthly salary
comm decimal(7,2) Commission
deptno int Department number 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE emp(
	empno INT, 
	ename VARCHAR(50), 
	job VARCHAR(50), 
	mgr INT, 
	hiredate DATE,
	sal DECIMAL(7,2), 
	comm decimal(7,2), 
	deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
  • Department Table: dept
Column Name Data Type Description
deptno int Department code
dname varchar(50) Department name
loc varchar(50) Department location 
1
2
3
4
5
6
7
8
9
CREATE TABLE dept(
	deptno INT, 
	dname varchar(14),
	loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS',

6.1 Basic Queries

Query all columns: SELECT * FROM table_name;
(*: wildcard, represents all columns)

1
SELECT * FROM stu;

Query specific columns: SELECT column_name1, column_name2, ... column_nameN FROM table_name;

1
SELECT sid, sname, age FROM stu;

6.2 Conditional Queries

Conditional queries involve using a WHERE clause with the following operators and keywords:

  • =、!=、<>、<、<=、>、>=；
  • BETWEEN…AND；
  • IN(set)；
  • IS NULL；
  • AND；
  • OR；
  • NOT；

Examples:
Query records where gender is female and age is less than 50:
SELECT * FROM stu WHERE gender='female' AND age<50;

Query records where student ID is S_1001 or name is liSi:
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

Query records where student ID is S_1001, S_1002, or S_1003:
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003')

Query records where student ID is not S_1001, S_1002, or S_1003:
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');

Query records where age is null:
SELECT * FROM stu WHERE age IS NULL;

Query student records with age between 20 and 40:
SELECT * FROM stu WHERE age>=20 AND age<=40;
or
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

Query student records where gender is not male:
SELECT * FROM stu WHERE gender!='male';
or
SELECT * FROM stu WHERE gender<>'male';
or
SELECT * FROM stu WHERE NOT gender='male';

Query student records where name is not null:
SELECT * FROM stu WHERE NOT sname IS NULL;
or
SELECT * FROM stu WHERE sname IS NOT NULL;

6.3 Fuzzy Queries

SELECT fields FROM table WHERE some_field LIKE condition
SQL provides two matching patterns for conditions:
% : Represents any 0 or more characters. It can match any type and length of characters. In some cases, for Chinese characters, please use double percent signs (%%).
_ : Represents any single character. It matches a single arbitrary character and is often used to limit the length of expression statements.

Examples:
Query student records with names consisting of 5 letters:
SELECT * FROM stu WHERE sname LIKE '_ _ _ _ _';

Query student records with names consisting of 5 letters and the fifth letter is “i”:
SELECT * FROM stu WHERE sname LIKE '_ _ _ _i';

Query student records where names start with “z”:
SELECT * FROM stu WHERE sname LIKE 'z%';
Here, “%” matches 0 to n characters.

Query student records where the second letter in the name is “i”:
SELECT * FROM stu WHERE sname LIKE '_i%';

Query student records where the name contains the letter “a”:
SELECT * FROM stu WHERE sname LIKE '%a%';

6.4 Field Control Queries

Remove duplicate records
To eliminate duplicate records based on the sal field in the emp table, you can use the DISTINCT keyword as follows:

SELECT DISTINCT sal FROM emp;

This query will return only unique values from the sal column of the emp table, removing any duplicates.

View the sum of employees’ monthly salary and commission.
Because both sal and comm columns are numeric types, they can be added together. If either sal or comm contains a non-numeric value, an error will occur.
SELECT *,sal + comm FROM emp;
The comm column has many records with NULL values. Because adding anything to NULL results in NULL, the calculation result may be NULL. Below, the IFNULL function is used to convert NULL to the numeric value 0:
SELECT *, sal+IFNULL(comm,0) FROM emp;

Give alias to column names:
In the above query, the column name sal+IFNULL(comm,0) appears, which is not aesthetically pleasing. Now, let’s give an alias to this column, named total:
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
When assigning aliases to columns, you can omit the AS keyword:
SELECT *, sal+IFNULL(comm,0) total FROM emp;

6.5 Sorting

Query all student records, sorted in ascending order by age:
SELECT * FROM stu ORDER BY sage ASC;
or
SELECT * FROM stu ORDER BY sage;

Query all student records, sorted in descending order by age:
SELECT * FROM stu ORDER BY age DESC;

Query all employees, sorted in descending order by salary. If salaries are the same, then sort by employee number in ascending order:
SELECT * FROM emp ORDER BY sal DESC ,empno ASC;

6.6 Aggregate Functions

Aggregate functions are used for vertical operations:
  • COUNT(): Counts the number of rows where the specified column is not NULL.
  • MAX(): Computes the maximum value of the specified column. If the column is of string type, it uses string sorting.
  • MIN(): Computes the minimum value of the specified column. If the column is of string type, it uses string sorting.
  • SUM(): Computes the sum of numeric values in the specified column. If the column type is not numeric, the result is 0.
  • AVG(): Computes the average value of the specified column. If the column type is not numeric, the result is 0.

COUNT: COUNT() is used for vertical counting operations.

Query the number of records in the emp table:
SELECT COUNT(*) AS cnt FROM emp;

Query the number of employees in the emp table who have commission:
SELECT COUNT(comm) cnt FROM emp;
Note: COUNT(comm) counts the number of non-NULL values in the comm column.

Query the number of employees in the emp table whose monthly salary is greater than 2500:
SELECT COUNT(*) FROM emp WHERE sal > 2500;

Count the number of employees whose total monthly salary and commission sum is greater than 2500:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

Query the count of employees with commission and the count of employees with a manager:
SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM and AVG: SUM() and AVG() are used for vertical sum and average operations.

Query the total salary of all employees:
SELECT SUM(sal) FROM emp;

Query the total salary and total commission of all employees:
SELECT SUM(sal), SUM(comm) FROM emp;

Query the total of monthly salary + commission of all employees:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

Calculate the average salary of all employees:
SELECT SUM(sal), COUNT(sal) FROM emp;
or
SELECT AVG(sal) FROM emp;

MAX and MIN

Query the highest and lowest salary:
SELECT MAX(sal), MIN(sal) FROM emp;

6.7 Grouping Queries

Grouping Queries

When conducting grouping queries, you need to use the GROUP BY clause. For example, to query the total salary for each department, you need to group by department.

Query the department number and the total salary for each department:

1
2
3
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

Query the department number and the count of employees in each department:

1
2
3
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

Query the department number and the count of employees whose salary is greater than 1500 in each department:

1
2
3
4
SELECT deptno ,COUNT(*)
FROM emp
WHERE sal>1500`
GROUP BY deptno;

HAVING Clause:

Query the department number and the total salary for departments where the total salary is greater than 9000:

1
2
3
4
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

Note: WHERE clause filters records before grouping, so rows that do not meet the WHERE conditions are excluded from grouping. HAVING clause filters data after grouping.

6.8 LIMIT

To query 5 rows, starting from row 0:
SELECT * FROM emp LIMIT 0, 5;
Note: Starting from row 0 means starting from the first row!

To query 10 rows, starting from row 3:
SELECT * FROM emp LIMIT 3, 10;

If there are 10 records per page, and you want to view the records on the 3rd page:

  • The starting row for the first page is 0, querying a total of 10 rows.
  • The starting row for the second page is 10, querying a total of 10 rows.
  • The starting row for the third page is 20, querying a total of 10 rows.

6.9 Joining Multiple Tables: Inner and Outer Joins

Table joins are divided into inner joins and outer joins. The main difference between them is that an inner join selects only the records that have matching values in both tables, whereas an outer join includes unmatched records from one or both tables.

For example, consider the staff table and the department table:

id name deptname
1 Steve tech
2 Jobs seal
3 Drake tech
4 Tom seal
5 Jay tech
6 Ll hr
7 Chuan ceo
8 Anna seal
9 Satan driver

Inner Join

1
select staff.name,deptname from staff,deptno where staff.name=deptno.name;
id name deptname
1 Steve tech
2 Jobs seal
3 Drake tech
4 Tom seal
5 Jay tech
6 Ll hr
7 Chuan ceo
8 Anna seal
9 Satan driver

Outer Joins: Left Join and Right Join

Left Join: Includes all records from the left table, and unmatched records from the right table appear as NULL.
Right Join: Includes all records from the right table, and unmatched records from the left table appear as NULL.

Outer Join (Left Join)：

1
select staff.name,deptname from staff left  join deptno onstaff.name=deptno.name;
id name deptname
1 Steve tech
2 Jobs seal
3 Drake tech
4 Tom seal
5 Jay tech
6 Ll hr
7 Chuan ceo
8 Anna seal
9 Satan

Outer Join (Right Join):

1
select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;
id name deptname
1 Steve tech
2 Jobs seal
3 Drake tech
4 Tom seal
5 Jay tech
6 Ll hr
7 Chuan ceo
8 Anna seal
9 Satan

OK,If you find this article helpful, feel free to share it with more people.

If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. https://www.sqlynx.com/download/ Free download

Share

Copyright © 2024 SQLynx Co., Ltd.