Wondershare EdrawMind
Galerie de cartes mentales SQL learning summary
- 462
- 5
SQL learning summary
Detailed SQL basic knowledge points, detailed introduction, comprehensive description, I hope it will be helpful to interested friends!
Modifié à 2023-12-08 18:22:06
- Recommandé pour vous
- Contour
SQL Concept Basics
relational database
Database (DB): A collection of data processed by a computer. Data is managed in two-dimensional tables consisting of rows (records) and columns (fields). Only one data can be entered into a cell. The database is composed of multiple tables, and data reading and writing operations are performed in row units. Database Management System: (DBMS): A computer system that manages a database.
Relational database: A database that expresses data relationships in the form of two-dimensional tables.
Normalization of relational databases
First normal form: eliminate duplicate fields, each field is the smallest logical unit
Second normal form: requires entity attributes to completely depend on the primary key
Third normal form: requires that there is no non-key column passing functional dependence on any candidate key sub-column
SQL
The language used to operate the database
DDL: used to define database objects (databases, tables, fields, etc.). Common instructions: create, drop, alter,
DML: used to query or change table records. Common instructions: select, insert, update, delete
DCL: Confirm data changes, that is, transaction control. Common commands: grant, revoke
DQL: Perform corresponding queries on data as needed. Common commands: select
DTL: Confirm or cancel changes to data, that is, transaction commit and rollback Common instructions: commit, rollback
Common data types
char: fixed-length string
vchar: variable length string
int: integer type
Date type: Specify the data as date type
constraint
Restrict or add conditions to data
Non-null constraint: not null
Primary key constraints: primary key
Unique constraint: unique
Default value constraint: default
Check constraints: check
Foreign key constraints: foreign key
operator
Arithmetic operators: ,-,*,/
comparison operator
=: equal to
>=
<=
>
<
<>: not equal to
Logical Operators: not, and, or
The values that perform arithmetic operations with null are all null. You cannot use null for comparison operations. Logical operators and null produce three-valued logic
function
arithmetic function
Four arithmetic operations: addition ( ), subtraction (-), multiplication (*), division (/)
abs (numeric value): function to calculate absolute value
mod (dividend, divisor): The remainder function is not supported by SQL Server.
round function (object value, number of decimal places retained) rounding function
String functions
String1||String2: When the string concatenation function performs string concatenation, if it contains NULL, the result obtained is also NULL. This is because "||" is also a deformed function. ||Not available in SQL Server and MySQL
length (string): Count the number of characters in a string and calculate the length of the string SQL Server cannot use length, SQL Server uses len()
lower(string): Only for English strings, convert all strings to lowercase
upper(string): Only for English strings, convert all strings to uppercase
replace (object string, string before replacement, string after replacement): Replace part of a string with another string.
substring (object string from interception starting position for interception number of characters): String interception. Only supported by PostgreSQL and MySQL, see notes for other databases
date function
current_date: Returns the date of SQL execution, such as: select current_date PostgreSQL, MySQL support
current_time: Returns the time when SQL was executed. PostgreSQL, MySQL support
current_timestamp: Get the current date and time Supported by SQL Server, PostgreSQL, and MySQL, see comments for others:
extract(date element from date): Intercept date elements, such as "year", "hour", etc. Returns a numeric type PostgreSQL, MySQL support
conversion function
Data type conversion
cast (the value before conversion as the data type you want to convert):
value conversion
coalesce(data1,data2,...)
aggregate function
predicate
like
betwwen
is null
is not null
in: Unable to select NULL data.
not in: Unable to select NULL data.
exist
subtopic
SQL query
Basic selection
Basic query: select column 1, column 2,...from table
Set aliases for columns: select column 1 as alias from table
Remove duplicate rows/records from results (distinct): select distinct column from table
Aggregation query
select aggregate function (column) from table
aggregate function
count: Count the number of rows (number of records) count(*) will record all rows, including the row where null is located count(column) will exclude null
sum: Calculate the total value of data
avg: Calculate the average value of data
max: calculate the maximum value
min: Calculate the minimum value
Remove duplicate values using aggregate functions
select aggregate function (distinct column) from table
Group query
select column 1, column 2,...from table group by the column to be grouped (can be one column or multiple columns);
Common mistakes:
Write the column names other than the aggregate key in the SELECT sub in the sentence
Column aliases are written in the GROUP BY clause
The results of the GROUP BY clause cannot be sorted
Using aggregate functions in the WHERE clause
having clause: specifies conditions for the aggregation results
select column 1, column 2,...from table group by the column that needs to be grouped (can be one column or multiple columns) having condition
order by clause: sort query results
asc keyword: ascending order
desc keyword: descending order
When specifying multiple sort keys, the rule is to use the key on the left first (default ascending) order by is usually written at the end of the select statement
Data Update
Data insertion
insert into table (column 1, column 2,...) values(value 1, value 2,...);
Data deletion
drop table statement: the table can be completely deleted
delete: will leave the table (container) and delete all the data in the table Syntax: delete from table where condition;
The object of delete is a row (record) rather than a column, so it is wrong to specify the column name delete * from table; also wrong
Data Update
update table set column 1 = expression, set column 2 = expression where condition;
Use null to update (null clearing), just write the value on the right side of the assignment expression as null
Transaction: A series of updates that need to be performed in the same processing unit Newly processed collection.
commit: Submit processing
rollback: cancel processing
ACID properties: Atomicity, durability, consistency (integrity), isolation
Distinguish between transactions: 1 Each SQL statement is a transaction (auto-commit mode) 2 Until the user executes commit or rollback, it is counted as a transaction
Complex query
view
advantage: 1. Since the view does not need to save data, it can save the capacity of the storage device. The view itself is saved on the storage medium (hard drive). 2. You can save frequently used SELECT statements as views so that you don’t have to rewrite them every time.
grammar: create view view name (view column 1, view column 2,...) as select statement
Delete a view: drop view view name
A view is a saved select statement. When defining a view, you can use any select statement. Multiple views (creating views through views) can reduce SQL performance The defined view cannot be sorted (using the order by clause), because the view, like the table, has no order of data rows View cannot be updated directly
subquery
Another select statement used to define a view, written directly in the from clause
In the subquery code in the previous comment, productSum at the end is the name of the subquery However, since the name is one-time use, it will not be saved in the storage medium (hard disk) like the view. Instead, it disappears after the SELECT statement is executed.
scalar subquery
A subquery that must return a value (a single value).
scenes to be used: If you need to use an aggregate function in the where clause, you can use
Where constants or column names can be used, whether it is the SELECT clause, GROUP BY clause, HAVING clause, or ORDER BY clause, it can be used almost everywhere. Multiple rows of results cannot be returned
Correlated subquery
Suitable for application scenarios where scalar subquery cannot return multiple results
In the scalar subquery clause add where alias1.column = alias2.column
Correlated subqueries can also split collections
Association name (alias)
Scope
The association name set within the subquery can only be used within the subquery. where alias 1. column = alias 2. The column must be written in the subquery
Other knowledge points
Set operations
SQL advanced processing