SQL Quick Reference: Simplify Database Management

sql cheat sheet

This blog provides comprehensive guidance on the most important SQL commands and operations. It covers basic queries, connections, subqueries, indexes, and more advanced concepts.

Table of contents

1. Basics of SQL
2. Data definition language (ddl)
3. Data operation language (dml)
4. Data query language (dql)
5. Data Control Language (dcl)
6. join in
7. Subquery
8. index
9. Aggregation function
10. Grouping and sorting
11. trade
12. Advanced sql
13. Best Practices

---

Basics of SQL

Structure of SQL query

 select column1, column2
from table_name
Where condition
order by column
limit n;

Comment in SQL

• Single comment : -- This is a comment
• Multiple comments :

 /* this is a 
     multi-line comment */

---

Data definition language (ddl)

Create a table

 create table table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

 create table employees (
    id int primary key,
    name varchar(100),
    age int,
    hire_date date
);

Modify the form

Add column

 alter table table_name
add column_name datatype;

Delete a column

 alter table table_name
drop column column_name;

Modify columns

 alter table table_name
modify column column_name datatype;

Rename table

 alter table old_table_name
rename to new_table_name;

Delete a table

 drop table table_name;

Create an index

 create index index_name
on table_name (column_name);

Delete the index

 drop index index_name;

---

Data operation language (dml)

Insert data into table

 insert into table_name (column1, column2, ...)
values ​​(value1, value2, ...);

Example:

 insert into employees (id, name, age, hire_date)
values ​​(1, 'john doe', 30, '2022-01-01');

Update data in table

 update table_name
set column1 = value1, column2 = value2, ...
where condition;

Example:

 update employees
set age = 31
where id = 1;

Delete data from table

 delete from table_name
where condition;

Example:

 delete from employees
where id = 1;

---

Data query language (dql)

Select data from the table

 select column1, column2, ...
from table_name
Where condition
order by column
limit n;

Example:

 select * from employees;
select name, age from employees where age > 30;

Wildcard

• *: Select all columns
• %: a wildcard with zero or more characters (in the like clause)
• _: Wildcards representing only one character (in the like clause)

Example:

 select * from employees where name like 'j%';

---

Data Control Language (dcl)

Grant permissions

 grant permission on object to user;

Example:

 grant select, insert on employees to 'user1';

Revoke permissions

 revoke permission on object from user;

Example:

 revoke select on employees from 'user1';

---

join in

Inner connection

Returns rows when there are matches in both tables.

 Select columns
from table1
inner join table2
on table1.column = table2.column;

Left connection (or left outer connection)

Returns all rows in the left table and matched rows in the right table. If it does not match, the columns in the right table will display a null value.

 Select columns
from table1
left join table2
on table1.column = table2.column;

Right connection (or right external connection)

Returns all rows in the right table and matched rows in the left table. If it does not match, the columns in the left table will display a null value.

 Select columns
from table1
right join table2
on table1.column = table2.column;

Fully external connection

Returns rows when there is a match in one of the tables.

 Select columns
from table1
full outer join table2
on table1.column = table2.column;

---

Subquery

Subquery in select

 select column1, (select column2 from table2 where condition) as alias
from table1;

Subquery in where

 select column1
from table1
where column2 in (select column2 from table2 where condition);

Subquery in from

 select alias.column1
from (select column1 from table2 where condition) as alias;

---

index

Create an index

 create index index_name
on table_name (column1, column2);

Delete the index

 drop index index_name;

Unique index

Make sure that all values ​​in one column (or a group of columns) are unique.

 create unique index index_name
on table_name (column_name);

---

Aggregation function

Count

Calculate the number of rows that meet certain criteria.

 select count(*) from table_name where condition;

and

Returns the sum of the values ​​in the column.

 select sum(column_name) from table_name;

Average voltage

Returns the average value of the values ​​in the column.

 select avg(column_name) from table_name;

Minimum and maximum values

Returns the minimum and maximum values ​​in the column.

 select min(column_name), max(column_name) from table_name;

---

Grouping and sorting

Grouping basis

Group rows with the same value into summary rows.

 select column1, count(*)
from table_name
group by column1;

have

Apply group by to filter the group.

 select column1, count(*)
from table_name
group by column1
having count(*) > 5;

Order basis

Sort the result set in ascending or descending order.

 select column1, column2
from table_name
order by column1 desc;

---

trade

Start trading

 begin transaction;

Conduct a transaction

 commit;

Roll back transactions

 rollback;

---

Advanced sql

Case when

Conditional logic in the query.

 select column1,
       case
           When condition then 'result 1'
           When condition then 'result 2'
           else 'default'
       end as alias
from table_name;

United and United All

• union : merges the result sets of two or more queries (delete duplicates).
• union all : merge result sets (retain duplicates).

 select column from table1
union
select column from table2;

select column from table1
union all
select column from table2;

---

Best Practices

• Use join instead of subquery when possible for better performance.
• Index frequently searched columns to speed up queries.
• Avoid select * and specify only the columns you need.
• Use limit on the number of rows returned for large result sets .
• Standardize your data to avoid redundancy and improve consistency.
• Use the where clause instead of filtering the data before aggregation.
• Test query performance, especially for large data sets.
• Use transactions to ensure data consistency, especially operations involving multiple dml statements.

---

in conclusion

This sql cheat sheet covers all the basic sql commands and techniques required to use a relational database. Whether you are querying, inserting, updating, or connecting data, this guide will help you use SQL more effectively.

The above is the detailed content of SQL Quick Reference: Simplify Database Management. For more information, please follow other related articles on the PHP Chinese website!