SQL Basics (Beginner Essentials)

First, simple query

1, query all data, query some column data, column alias

SELECT * FROM table name

SELECT column 1 as ' BIAOTI1 ', ' BIAOTI2 ' = column 2 from table name

2, the query does not duplicate the data,

SELECT DISTINCT column name from table name

3, query the first n rows of data

SELECT TOP N PERCENT * FROM table name

4, Filter line

Operator: comparison operator, logical operator (And/or/not), range operator ([Not]betwween and), List operator (in (List of values)), pattern match, null-value qualifier

SELECT * FROM table name WHERE condition

Pattern match: Where column name 1 like '% ' _ ' [^0-9][a-z] '

Null qualifier: is null/is not NULL

5. Sort order BY Column name 1 [ASC/DESC], column name 2 [ASC/DESC]

Second, function

1. String function: substring (expression,start,length) 、--substring

STR (Float_expression,[length,[decimal])--Converts a number type to a character type

2. Date function: getdate () 、--get current system date

DATEADD (datepart,number,date) 、--yy mm DD

Datediff (DATEPART,DATE1,DATE2) 、--ask for two date difference

Year (date), Month (date), Day (date)

3. Mathematical function: Round (numeric_exp,length)--

4. Conversion function: CONVERT (datatype [(length)],expression[,style])--type conversion function

5. Order function: Row_number (), Rank (), Dense_rank ()

6, IsNull Function--

7. Aggregate function: Min,max,sum,avg,count

Third, group

Group filtering (column 1. Column 2, which must appear in GROUP by)

SELECT column 1, column 2, COUNT (column 3) from table name WHERE condition

GROUP by column 1, column 2

Having condition (filter after grouping, can use aggregate function)

Iv. Connection (subquery result set if in a table, use a subquery, otherwise connect)

1. Internal connection: SELECT A. Column name, B. Column name from table 1 A

[INNER] JOIN table 2 B on A. Column name = B. Column Name

2. Outer connection: Left outer connection, right outer connection, full outer connection

(Ensure that the data in one table is all displayed, plus the data in the other table that satisfies the criteria)

SELECT A. Column name, B. Column name from table 1 A

Left| Right| Full [OUTER] JOIN table 2 B on A. Column name = B. Column Name

Five, sub-query

Select Column Name 1, column name 2 from table 1

Where column operator (SELECT statement)

Select Column Name 1, column name 2 from table 1

where [not] exists (SELECT statement)

[NOT] in

[NOT] exists

Subqueries derived from comparison operators

Vi. Building a Table creat alert drop management structure

1. Create database name/DROP database

General: MDF database Master file ldf log file

Special case (large database): NDF auxiliary file

2. create TABLE table name/DROP table name

(Column name 1 data type constraint, Null/not null primary key check (),

Column Name 2 data type constraint unique,

Column name 3 data type constraint default value

Column name 4 data type constraint foreign key references table Name 2 (listed)

)

--Add Field

ALTER Table Table Name

ADD

--Modify Fields

ALTER Table Table Name

Alter

--delete Field

ALTER Table Table Name

DROP

Use database

3. Management table

INSERT [into] Table name (column list) values (list of value)

UPDATE table name SET column 1= value 1, column 2= value 2 WHERE condition

DELETE from table name WHERE condition

VII. Data integrity constraint references

Type: 1. Entity integrity

2. Referential (referential) integrity

3. Domain Integrity

4. User-defined Integrity

Realize:

1, Primary key primary key

2, Foreign key foreign key REFERENCES table name (column name)

3. Unique Constraint

4. Check checking constraints

5. Default Defaults constraint

Data management

Add data

Insert [into] Table name (column list) values (Zhi Leibiao)

Update data

Update table name set column name 1= value 1, column name 2= value 2 where

Delete data

Delete from table name where

Viii. views

Advantages:

Syntax: CREATE VIEW view_name

As SELECT statement

Ninth, Index

Pros: Improved performance of database operations in frequently used, small-print sections of the place

Syntax: CREATE INDEX Index_name

On table (column 1, column 2)

X. Stored Procedures

Pros: Reusable code clients can reduce network traffic security by sending very few parameters and stored procedure names

Syntax: create proc Stored procedure name

@aa type, input parameter

@bb Type OUTPUT parameters

As SQL statements

return value

Call: Exec procedure name parameter [OUTPUT]

Xi. Business

Characteristics:

Four properties: atomicity Independence consistency persistence

Begin TRANSACTION Create a display transaction

Commit TRANSACTION: Identifies a hermit or displays a transaction

SQL Basics (Beginner Essentials)