Summary of SQL Basics

I. My Sql Add, delete, change, check the basic operation statement:

1 (Increase): INSERT into table name (column name) values list insert into table name values list (insert all columns)

"Extensions" Insert multiple lines: 1: INSERT into < table name > (column name)

2 kinds: Select Column name into < table name > from < source table name > Note To create a table, copy the structure of column names and data into a new table, but this SQL statement can only be used once

3 kinds: INSERT into < table name > (column name) Select Value List union select Value List

2 (delete): Delete from table name [where condition]

TRUNCATE TABLE name "Note" 1. You cannot delete tables with foreign keys 2. Deleted data cannot be recovered 3. The identity column is recalculated starting at 1 (as opposed to delete)

3 (change): Update table name set column name = value, column name 2= value 2 [Where condition]

4 (check): Select Column name from table name [where condition]

* Multi-Table query:

Internal connection (INNER join): Select Table 1. column name, table 2. Column name from table name 1 as table 1

Inner JOIN table Name 2 as table 2

On table 1. Field name = Table 2. Field Name

Inner JOIN table name 3 as table 3

On table 1. Field name = Table 3. Field Name

External connection:

Left outer: Select Table 1. column name, table 2. Column name from table name 1 as table 1

Left OUTER JOIN table name 2 as table 2

On table 1. Field name = Table 2. Field Name

Right outside: Select Table 1. column name, table 2. Column name from table name 1 as table 1

Right outer join table name 2 as table 2

On table 1. Field name = Table 2. Field Name

* Group Query (group by...having)

Select Column name (Group By column name, or function) from table name

where [condition]

Group By column name

Having conditions

Order by sort [Asc|desc]

Two. Other knowledge points

1. Fuzzy query Three ways first: Select Column name from table name where column name like ' '

"Supplemental" common wildcard characters have the following four kinds: _: One character%: characters of any length []: The characters within the interval [^]: characters within a range

Second type: Select Column name from table name where column name in (value 1, value 2)

Third type: Select Column name from table name where column name between value 1 and value 2

2. Determine if NULL is empty: column name is null or column name = ' '

Not empty: Column name is a null and column name! = ' '

3 Common functions

A. Aggregate function SUM (column name): and Max (column name): Max min (column name): Minimum avg (column name): Average count (column name): Number of columns

The "note" aggregate function is the number, value, or

B. Date: Current date: getdate ()

DateDiff (date part, date 1, date 2): Find the difference in the date part

DATEADD (date part, value added, date specified)

DATEPART (date part, specified date)

Datename (date part, specified date)

C. String functions

A. CHARINDEX (look for the character, search column [, start position]); Search for characters or strings in a paragraph to return to the starting position

"Note": Start position starting from 1 if not found returns 0) e.g select * from Student where charindex (' Gold ', studentname) >0

B. Len (String | column name): Returns the string length of select Len (' Hello World '); Returns 11

C. Upper (String | column name): Converts the value passed to uppercase select upper (' Hello World '); HELLO World

D. LTrim (String | column name): Go to the left of the string RTrim (String | column name): Go to the right of the string to the space E.g:select ltrim (' Hello World ');

Select RTrim (' Hello World ');

E. Right (string, length): Returns the specified number of characters from the left side of the string (string, length) returns the specified number of characters from the side of the string

F. Replace (the character to be searched, the character to be queried, the replacement character) replaces the character in a string e.g Select replace (' Hello World ', ' hello ', ' Hi '); result Hi World

Summary of SQL Basics