SQL Server database Knowledge Point notes

1. PRIMARY KEY constraint:
To add a primary KEY constraint to a column, this column must satisfy the condition that the non-null
Because of a PRIMARY KEY constraint: A column is constrained to be (not NULL, not duplicated)
Here is the code to add a primary key to a column, the column name is ID, the table name is EMP

The format is:
ALTER TABLE table name ADD constraint constraint name increased constraint type (column name)

Example:
ALTER TABLE EMP ADD constraint PPP primary key (ID)

2.check constraints:
is to limit the data in one column.
For example, the age column has more than 20 data.
Table name (EMP) column name (age)

Format:
ALTER TABLE name ADD constraint constraint type (column name)

Example:
ALTER TABLE EMP add constraint xxx check (age>20)

3.unique constraints:
Such a constraint is the non-repeating constraint type that is appended to the data for the column

Format:
ALTER TABLE table name ADD constraint constraint name constraint type (column name)
Let's say you can add a unique to the ename column so that the data for the ename column is not duplicated.
Example:
ALTER TABLE EMP Add constraint qwe unique (ename)

4. Default constraints:
The simple thing is to let the data in this column default to certain data

Format:
ALTER TABLE name ADD CONSTRAINT constraint name constraint type default value) for column name

For example: The Gongzi column in the EMP table defaults to 10000

ALTER TABLE EMP ADD constraint jfsd default 10000 Forgongzi

5. FOREIGN KEY constraints:
This is a little hard to understand, the foreign key is actually a reference
Because the primary key implements the integrity of the entity,
The foreign key implements the referential integrity,
Application integrity rules, the data referenced must exist!

is actually a quote,
Let's say a table name, called dept, has 2 columns of data, one column is the ID column is ename
ID: Indicates the number of the product
ENAME: Indicates the name of the product

Another table name is EMP with 2 columns of data, one column is ID one column is did
ID: Represents the user number
Did: Indicates the purchased product number

To make the Did column in the EMP table refer to the ID in the Dept table

You can use the following method

Format:
ALTER TABLE name ADD CONSTRAINT constraint name constraint type (column name) references referenced table name (column name)

Example:
ALTER TABLE EMP ADD constraint JFKDSJ foreign key (did) references dept (ID)

How to use SQL Common commands:

Data record filtering:

Select*from Products: Find out all the information in the table

Select Productid,productname from Products: Check out all the productid,productname in the Products table

Select Productid,productname from Productswhere productid=1: Check out all ProductID and ProductName productid=1 in the Products table

select* from employee where fname= ' Paul ' andjob_id=5: Queries the Fname=paul in the employee table, and all records of job_id=5

Select*from Products where Productidin (4,5,6): Query All information ProductID as 4,5,6

Select*from Products where Unitprice>10and unitprice<30 ORDER by UnitPrice: query out the Products table 10<unitprice< 30 of all information, and according to the size of the UnitPrice from small to large sort

Select*from Products where UnitPrice between10 and the ORDER by UnitPrice: Another way of writing above

SELECT * from Employees where FirstName like ' A% ': Query out FirstName in Employees the first letter is the owner of A's information

Select*from Employees where FirstName like '%a% ': Find all the information about Employees in FirstName with a in the middle

Select*from Employees where FirstName like '%A ': The last letter of Employees in FirstName is the owner of A's information

Select COUNT (*) from Employees: Query out all records in the Employees table

Select min (UnitPrice) from Products: Query the lowest value of UnitPrice in the Products table

Select Max (UnitPrice) from Products: Querying the maximum value of UnitPrice in the Products table

Select AVG (UnitPrice) FROM Products: Query the average value of UnitPrice in the Products table

Select SUM (UnitPrice) from Products: Query the sum of UnitPrice in the Products table

SELECT * FROM Product where unitprice> (select AVG (UnitPrice) from products): There are subqueries to find more than the average value of goods information

Select Top 5* from Products: Query the first five records

SELECT DISTINCT [name] from category: Identify the name that is not duplicated in category

Select COUNT (distinct name) from Category: Find out the number of name not duplicated in category

(2) Update data record:
sql= "Update data table set field name = field value where Condition expression"
sql= "Update data Table set field 1= value 1, field 2= value 2 ... field n= value n Where Condition expression "

(3) Delete data record:
Sql= "Delete from data table where conditional expression"
Sql= "Delete from data table" (delete all records of the datasheet)

(4) Add data record:
Sql= "INSERT into Data table (field 1, Field 2, Field 3 ...) VALUES (value 1, value 2, value 3 ...)"
Sql= "INSERT INTO Target data table Select field name from source data table" (Add records from source data table to target datasheet)

(5) Data record statistic function:
AVG (field name) results in a table bar average
Count (*&brvbar; field name) statistics on the number of data rows or the number of rows with values for a column
Max (field name) gets the maximum value of a table column
Min (field name) gets the lowest value of a table bar
sum (field name) adds the value of the data bar
Methods that refer to the above functions:
Sql= "Select sum (field name) as Alias from data table where conditional expression"
Set Rs=conn.excute (SQL)
Use RS ("Alias") to obtain the value of the system, the other functions used ibid.

(5) Creation and deletion of data sheets:
CREATE table Data table name (field 1 type 1 (length), Field 2 type 2 (length) ...)
Example: CREATE TABLE tab01 (name varchar (), datetime defaultnow ())

drop table data table name (permanently delete a data table)

2. Methods for Recordset objects:
Rs.movenext moves the record pointer down one line from the current position
Rs.moveprevious moves the record pointer up one line from the current position
Rs.movefirst move the record pointer to the first row of the data table
Rs.movelast move the record pointer to the last row of the data table
Rs.absoluteposition=n move the record pointer to the nth row of the data table
Rs.absolutepage=n move the record pointer to the first row of page N
Rs.pagesize=n set to N records per page
Rs.pagecount returns the total number of pages based on pagesize settings
Rs.recordcount returns the total number of records
RS.BOF returns whether the record pointer is above the first end of the data table, True indicates Yes, False no
Rs.eof returns whether the record pointer is outside the end of the data table, True indicates Yes, False no
Rs.delete deletes the current record, but the record pointer does not move down
Rs.addnew adding records to the end of the data table
rs.update Updating Data Sheet Records

Sort

DESC is used to query the results when the results are sorted, descending sort, and ASC is ascending. To be used with order by.
For example SELECT * FROM student ORDER by id DESC; is to return the selected result after the "Sort by ID from the big to small".
You can also select * from Student order by the age Desc,id desc; Use the "," number to separate multiple sorting criteria, so that the first by the age and then by the ID, that is, first by the ages from the big to the small sort, if there is the same old, then students of the same ages then press their I D sort from large to small.

Desc refers to sorting by descending order ASC is ascending.

SQL Server database Knowledge Point notes