SQL Learning Collation

SQL Grooming

SQL is not case sensitive!

first, the operation of the data Achieve functional classification:

1. Increase:

1.1 Table exists, insert field:

Insert a new row (inserted in the order of fields)

　　　　　　INSERT into Table_1 VALUES (' bly ',%, ' girl ')

Specify column Insertion

　　　　　　INSERT into Table_1 (name,address) VALUES (' bbbb ', ' CCCC ')

Copy the data from table 2 tables to table 1

INSERT into Table_1 SELECT * from table_2

1.2 Table does not exist

CREATE table 3 First, and then insert the specified field in table 1 into the new table

SELECT name,address to Table_3 from Table_1

2. By deleting

2.1 Delete from Table_1//delete all data in Table 1 table (delete, slow)

2.2 TRUNCATE Table Table_1//Emptying tables 1 data (delete all at once, fast)

Note: Delete, truncate, drop difference

	Remove xxx	Application scope	Format	Space occupied by tables and indexes
Delete	Data	Table&&view	DELETE from table	does not reduce
Truncate	Data	Table	TRUNCATE Table Table	Restore Initial Size
Drop	Table		DROP Table Table	Full release of table-occupied space

3 change

3.1 UPDATE table_1 SET gender= ' boy ' WHERE name= ' bbbb '

4 Check

4.1 SELECT * from Table_1 WHERE gender= ' Boy '

4.2 SELECT * FROM Table_1

4.3 SELECT TOP PERCENT name,sum (price) from Table_1

GROUP by name

Have SUM (price) >100

ORDER by SUM (price) execution sequence:

1. From
2. WHERE
3. Group by group. Typically used with aggregate functions.
4. Having query conditions. The WHERE keyword cannot be used with an aggregate function
5. SELECT Query
6. DISTINCT to Heavy
7. Order sort
8. Top (PERCENT) 10 (%)

4.4 Fuzzy Query

'% ' multi-character fuzzy match. such as: ' Week% ', '% Super '

' _ ' a character fuzzy match. such as: ' Zhou _ ', ' _ Super '

SELECT * from Table_1 WHERE name like '% small% '

SELECT * from Table_1 WHERE name isn't like ' _ Super '

Other operators

1 and

SELECT * from Table_1 WHERE (name= ' bly ' and gender= ' girl ')

2 OR

SELECT * from Table_1 WHERE (name= ' bly ' OR name= ' ZXL ')

3 in

The in operator allows us to specify multiple values in the WHERE clause.

SELECT * from Table_1 WHERE name in (' Bly ', ' ZXL ')//result of ibid. or

4 between ... And ...

Included before, not including after

SELECT * from Table_1 WHERE price between (50<=x<100)

5 JOIN

Table A and the Table B as an example

A : B :

5.1 Internal Connection INNER JOIN is the intersection A ∩ B

SELECT * from Table_1

INNER JOIN table_2

On table_1.name = Table_2.name

5.2 External Connection Full OUTER JOIN is the and set A ∪ B

SELECT * from Table_1

Full OUTER JOIN table_2

On table_1.name = Table_2.name

5.3 LEFT Join Left JOIN is the A table full Display, B table with no data NULL Show

SELECT * from Table_1

Left OUTER JOIN table_2

On table_1.name = Table_2.name

5.4 Right-connected Right JOIN is the B table full Display, A table with no data NULL Show

SELECT * from Table_1

Right JOIN table_2

On table_1.name = Table_2.name

5.5 Cross JOIN is the data of table A and table B n*m

SELECT * from Table_1

Cross JOIN table_2

Add:

A A, table B has no

SELECT * from Table_1

Full OUTER JOIN table_2

On table_1.name = Table_2.name

WHERE Table_2.name is NULL

B A and B are not common

SELECT * from Table_1

Full OUTER JOIN table_2

On table_1.name = Table_2.name

WHERE table_1.name is null OR table_2.name is null

6 UNION

Merges the result set of two or more SELECT statements.

The SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

SELECT * from Table_3 WHERE name= ' 123 '

UNION

SELECT * from Table_1 WHERE name= ' Paulinka '

6.1 Choose a different value

SELECT name from Table_3

UNION

SELECT name from Table_1

6.2 Duplicate values are allowed

SELECT name from Table_3

UNION All

SELECT name from Table_1

Function:

1. avg() Average
2. SUM () and
3. COUNT () Total
4. LEN () length
5. UCASE () Uppercase
6. LCASE () lowercase
7. ROUND ( A , X ) Display A field data after decimal point X bit
8. GETDATE () Get Time
9. MIN () Minimum Value
10. MAX () Maximum Value

second, the operation of the table

1 New Table

1.1 inserting data to create a table at the same time

SELECT * into Table_3 from table_1

1.2 New empty table

CREATE TABLE Table_4 (

Name nvarchar (30),

Age int

)

2 Deleting a table

DROP TABLE Table_3

3 Modifying a table

3.1 Adding fields

ALTER TABLE table_1 ADD Price int

3.2 Delete Fields

ALTER TABLE table_2 DROP COLUMN Name

SQL Learning Collation