January 10 SQL SERVER Additions and deletions (first section)

First, landing

SQL Server two logon mode settings: Windows identity logon, SQL Server identity logon.
How do I set up SQL Server authentication?
1. Object Explorer Right-click-Properties--Security--sqlserver and Windows identity login.
2. Object Explorer--security--Login--sa--Right click--Properties--general--Set password
3. Object Explorer--security--Login--sa--Right click--Properties--state--grant, enable
Restart the database service.

Ii. SQL statements (add, delete, change, check)

1. Increase (two kinds of wording)

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

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

2. By deleting

Delete from table name--delete all data in the table

Delete from table name where condition--delete the statement that meets the criteria

3, change

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

4. Check (key)

There are many ways of querying

(a), simple query

Select*from table name----Basic form (* represents all columns, * Position can also be added)

(1), projection

Select column name, column name, ... from table name

( b), screening

(1), equivalence, non-equivalent query

s elect * from table name where column name = Value---equivalent query

SELECT * FROM table name where column name <> value-no equivalent query (<> equivalent!) =
SELECT * FROM table name where column name > value >=
SELECT * FROM table name where column name < value <=

(2), Multi-conditional 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
First class.

(3), scope query

SELECT * FROM table name where column name >= range 1 and column name <= Range 2

For example:

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)--can be used where column name in (1, 2,,,,)

(4), fuzzy query

SELECT * FROM table name where column name like ' Identity word% '

%--any number of arbitrary characters
_--an arbitrary character

Cases:
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.

(5), to re-query:

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

(6), top query

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

(iii), sort

SELECT * FROM table name where condition order by column name asc| DESC, column name asc| DESC

Cases:

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

(iv), group

Statistical functions (aggregate functions)
Count (), Max (), Min (), SUM (), AVG ()

COUNT () Total rows counted
COUNT (*) to get all the number of rows
Count (column) gets all the non-null numbers in the column.
Select COUNT (*) from car where brand= ' b003 '

Max (column) This column is the largest, min (column) of the column of the smallest
Select min (price) from car

Sum of this column, AVG (column), the average of this column
Select AVG (price) from car

GROUP By ... having ...

1.group by followed by a column name.
2. Once the group by group is used, the select and from middle cannot be used * and can contain only two categories of things: The column name after group by, and the other is the statistic function
Select Oil,avg (Price) from Car GROUP by oil
For columns generated by statistical functions, the default is no column name, and you can specify the column name by using the following method.
Select oil as fuel consumption, count (*) as quantity, AVG (price) average from Car GROUP by oil

Having the following is generally followed by a statistical function. It is used to further filter the data after grouping.

(v), complex query

(1), link query:
The first step: finding 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 the columns that display the query
Select Info.code,info.name,info.sex,nation.name,info.birthday
From Info,nation where Info.nation=nation.code

General use of Join ... On connection

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

Left join (left join), right-join, fully connected (full join)

(2), joint inquiry
The rows of multiple tables are combined in a single interface view.
Combine two queries with a union. The requirement is that the columns of the two queries correspond to each other.

(3), sub-query (nested query)

(i) Unrelated sub-query:
At least two levels of query, the inside of the query and then write query.

The inner query provides the intermediate content of the query for the outer query.

Example: Query "Zhang Xu" teacher's student performance. -Grades, teachers, courses are not in a table

Select degree from score where cno=
(
Select CNO from course where tno=
(
Select TNO from teacher where Tname= ' Zhang Xu '
)
)

January 10 SQL SERVER Additions and deletions (first section)