Normalization of relational databases

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

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

=: equal to

>=

<=

>

<

<>: not equal to

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

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

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

Data type conversion

value conversion

aggregate function

Remove duplicate values ​​using aggregate functions

Common mistakes:

having clause: specifies conditions for the aggregation results

order by clause: sort query results

insert into table (column 1, column 2,...) values(value 1, value 2,...);

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;

update table set column 1 = expression, set column 2 = expression where condition;

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

Another select statement used to define a view, written directly in the from clause

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

Suitable for application scenarios where scalar subquery cannot return multiple results

Correlated subqueries can also split collections