Database-Increase, delete, change, check (Cartesian product)

How to build a new database? ......

. ldf--Log Files

. mdf--Master Data File

. ndf--secondary data files in a database, you can have multiple log files, multiple secondary data files, but only one master data file.

How do I create a new table? ......

Row order independent, column order independent.

SQL statement DDL DML (add, delete, change, check) DCL

Increase

Insert into table name (column name, column name, column name,...)

Values (value, value, value,....)

Insert into table name values (value, value, value, value): )

Check

First , simple query

SELECT * FROM table name select column name, column name, ... from table name--Projection

Equivalence and non-equivalence query select * FROM table name where column name = value-equivalent query

Non-equivalent query

SELECT * FROM table name where column name <> value

SELECT * FROM table name where column name > value >=

SELECT * FROM table name where column name < value <=

Multi-Criteria Query

Logical and (and), logical OR (OR)

SELECT * FROM table name where Condition 1 and condition 2 ...

SELECT * FROM table name where Condition 1 or condition 2 ...

If, in the where filter condition, both and and or are present, the and is first calculated. Unless you use parentheses to change the priority level.

Range query.

SELECT * from Car where price >=30 and price<=50

SELECT * from Car where price between and 50

SELECT * from Car where oil=7.4 or oil=8.5 or oil=9.4

SELECT * from Car where oil in (7.4,8.5,9.4)

Fuzzy query.

Generally not used =, but with like

%--any number of arbitrary characters

_--an arbitrary character

SELECT * from Car where Name like ' BMW% '

BMW%--starts with a BMW

% BMW--End with BMW

BMW%--as long as it contains the two words of BMW can.

The BMW%--represents the third character to start with a BMW.

To re-query:

Select DISTINCT column name from car--if there are duplicate values in the column, only 1 are checked out.

Take the first few data select top Quantity [column name |*] FROM table name

Sort

SELECT * FROM Car ORDER BY price asc--default is ascending ascending descending

SELECT * FROM car ORDER BY price DESC

SELECT * from Car ORDER by oil asc,price desc--oil main sort, price order

By deleting

Delete from car--all data

Delete from car where condition--the condition here is the same as the condition of select.

Change

Update table name set column name = value, column name = value ... where condition

Update Car Set price = Price + Price * 0.15 where Name is like ' BMW% '

Update car set name= ' 300C 3.5L commercial Vehicle ', oil= ' 9 ' where code= ' c012 '

Group query:

Sort

Link query: First step: Find Cartesian product select * FROM Info,nation

The second step: based on the corresponding columns of two tables, the Cartesian product is screened for effective data. SELECT * from info,nation where info.nation = Nation.code

Step Three: Adjust display columns to query select Info.code,info.name,info.sex,nation.name,info.birthday from info,nation where info.nation= Nation.code

SELECT * FROM table name 1--

Join table name 2 on table name 1. column = table Name 2. column

Join table name 3 on table Name 2. Column = table name 3. column

.... where query criteria

Select Car.name,brand.brand_name,productor.prod_name,car.pricefrom Car

Join brand on car.brand = Brand.brand_code

Join Productor on brand.prod_code = Productor.prod_code where price > 50

Database-Increase, delete, change, check (Cartesian product)