Contents
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
- 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.
- 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.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
CREATE TABLE table_name(
column_name column_type,
column_name column_type,
... );
Create stu table
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
Add a column: Add the classname column to the stu table
ALTER TABLE stu ADD (classname varchar(100));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);Rename a column: Change the gender column name of the stu table to sex
ALTER TABLE stu change gender sex CHAR(2);Delete a column: Delete the classname column from the stu table
ALTER TABLE stu DROP classname;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, ...);
INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'Tom', 23, 'male');
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:
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');
4.2 Modifying Data
Syntax:UPDATE 表名 SET 列名 1=值 1, … 列名 n=值 n [WHERE 条件];
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];
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;
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';
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;
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;
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM 'user1'@localhost;
5.4 Viewing User Permissions
Syntax: SHOW GRANTS FOR 'username'@host;
SHOW GRANTS FOR 'user1'@localhost;
5.5 Deleting Users
Syntax: DROP USER 'username'@host;
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 |
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 |
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 |
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)
SELECT * FROM stu;
Query specific columns: SELECT column_name1, column_name2, ... column_nameN FROM table_name;
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:
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
Query the department number and the count of employees in each department:
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:
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:
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
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)：
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):
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.
Top comments (0)