SQL Server Basics
First, the basic knowledge
(1), Storage structure: Database--table--Data
(2), Management database
Add: Create DATABASE name
Delete: Drop database name
Query: Select name from Master. sysdatabases
Modify: Alter DATABASE name (modify the files and filegroups associated with the database in SQL Server. Add or delete files and filegroups in a database, change the database, or the properties of their files and filegroups
(3), Management table
Select database: Use database
Added: Create TABLE table name (Field 1 field type, Field 2 field type,......);
Delete: Drop table name
Modify:
Add Field: ALTER TABLE table name Add field name fields type
Delete field: ALTER TABLE table name drop column field name
Modify field type: ALTER TABLE name ALTER COLUMN field type
Modify field name: Exec sp_rename ' table. Original field name ', ' new field name ', ' column '
Name of modified table: Exec sp_rename ' original table name ', ' New table name '
Query: Select name from sysobjects where xtype = ' u '
(4), Management data
Add data: INSERT into table (field 1, Field 2,...) VALUES (value 1, value 2,...)
Delete data: Delete from table name where condition
Modify data: Update table name set field 1= value 1, field 2= value 2,... where condition
Query data:
All fields: SELECT * FROM table
Specify field: Select Field 1, Field 2,... from table
Specify alias: Select Field 1 as Alias 1, Field 2 as Alias 1,... from table
Go weight: SELECT distinct field from table
Conditional query:
Logical condition: and (with) or (or)
SELECT * FROM table where ... and/or ...
Comparison condition:>,<,>=,<=,=,!=,<> (not equal to), between and
Conditions for the award of NULL:
Judge Null:is null/is NOT NULL
Judging an empty string: = ',! = ',<> '
Fuzzy condition: Like
'% ': replace any character
' _ ': replace one character
SELECT * FROM table where field like '% query character% '
Paging query:
@p_pagesize Number of int,//pages
@p_currentPage int//Current Page
DECLARE @string varchar (3000)
if (@p_currentPage =1)//First page
Begin
Set @string = ' Select top ' + cast (@p_pagesize as char) + ' * FROM table name where condition
End
else//is not the first page
Begin
Set @string = ' Select top ' +cast (@p_pagesize as varchar ()) + ' * FROM table name where Condition and table. Field not in (Select top ' + Cast (((@p_currentPage-1) * @p_pagesize) as varchar (20)) + ' field from table name where condition
End
EXEC (@string);
Sort query: Order by Asc/desc
ASC: Positive order;
Desc: reverse order;
Grouping queries: GROUP BY fields
Filter After grouping: having
Second, data constraints
Data constraints: constraining user table operations
(1) Default value:
Function: When a user does not insert a value into a field that uses a default value, the default value is used
Attention:
(a) It is possible to insert NULL for the default value field
(b) The default value field can be inserted non-null
Create:
(a) Establishment of the table:
CREATE TABLE Temp_table
(id int,
Name varchar (10),
Address varchar (+) Default ' Jilin Changchun '
(b) Statement:
Alert table name Add constraint default name (default value ') for field
(2) Non-null:
Function: Limit field, must assign value
Attention:
(a) A non-empty field must be assigned a value
(b) Non-empty fields cannot be assigned null
Create:
(a) Establishment of the table:
CREATE TABLE Temp_table
(id int NOT NULL,
Name varchar (10),
Address varchar (+) Default ' Jilin Changchun '
(b) Statement:
Alert Table Alert column Field Name field type NOT NULL
(3) Unique:
Unique key points primary key (primary key index) and unique index (unique index)
Function: The value of the field cannot be duplicated
Attention:
(a) Unique field can be inserted null
(b) Only one null can be inserted into the unique field
Create:
(a) Establishment of the table:
CREATE TABLE Student
(id int unique,
Name varchar (10))
(b) Statement
Create unique clustered index constraint name on table name (field name)
(4) Primary key:
function: Non-null, unique
Attention:
(a) Typically, each table will have a primary key field set . used to mark the uniqueness of each record in a table.
(b) It is recommended that you do not select a field that contains business meaning for the table as the primary key, and it is recommended that you design a non-business-meaning ID field independently for each table.
Create:
(a) Establishment of the table:
CREATE TABLE Student
(ID int primary KEY,--primary key
Name varchar (10))
(b) Statement:
First: Set fields first Null:alter table name alter column field name segment type NOT NULL
Second: Set PRIMARY key: ALTER TABLE name ALTER CONSTRANT PRIMARY KEY name (any) Promary key (primary key field)
(5) Self-growth
Function: Auto Increment
Note: You cannot have two or more identity columns in a single table
Create:
(a) Establishment of the table:
CREATE TABLE Student
(ID int identity (primary) key,--primary key, self-growth, starting from 1, 1 increase each time
Name varchar (10))
(b) Statement:
ALTER TABLE name add column name int identity (first) not null--add a column of self-increment
(6) Primary key:
Function: Constrain data from two tables
There are two kinds of tables: solve the problem of high data redundancy, separate out a table.
Attention:
(a) The constrained table is called the secondary table, the table that constrains others is called the main table, and the foreign key is set on the secondary table!!!
(b) The reference field of the main table is universal primary key!
(c) Add data: First add the Main table, then add the secondary table
(d) Modify the data: Modify the secondary table before modifying the main table
(e) Delete data: Delete the secondary table before deleting the main table
Create:
(a) Establishment of the table:
--Department table (main table)
CREATE TABLE Dept (
ID INT PRIMARY KEY,
Deptname VARCHAR (20)
)
--Modify Employee table
CREATE TABLE Employee (
ID INT PRIMARY KEY,
EmpName VARCHAR (20),
DeptID INT,--Change the department name to the department ID,
--Declaring a FOREIGN KEY constraint
CONSTRAINT foreign Key Name FOREIGN key (foreign key) REFERENCES primary key table (primary key ID)
)
(b) Statement:
ALTER TABLE Table Add foreign key (table foreign key) references primary key table (primary key ID)
Three, multiple table links query
(1) External links
Outer joins include: Left link, right link, full outer link
(a) Left link: outer JOIN
Usage: Min. Two sheets, table A and B
Select A. field from A left join B on a.id = b.ID
The left OUTER join contains all the rows from the left table in a left join, and if there is no match on the right table in the table, the portion of the right table in the corresponding row in the result is all empty (null).
(b) Right-click on join or outer join
Usage: Min. Two sheets, table A and B
Select A. field from A right join B on a.id = b.ID
The right outer join contains all the rows in the right table, and if a row in the left table does not match on the right table, the portion of the corresponding left table in the result is all empty (null).
(c) Fully outer connection full join or outer join
Usage: Min. Two sheets, table A and B
Select A. field from A full join B on a.id = b.ID
A full outer join contains all rows in both the left and right tables
If a row in the right table does not have a match in the left table, the portion of the right table in the corresponding row in the result is all empty (null)
If a row in the left table does not have a match in the right table, the portion of the left table in the corresponding row in the result is all empty (null).
(2) Inner connection
INNER JOIN join or INNER JOIN
Usage: Min. Two sheets, table A and B
Select A. field from A full join B on a.id = b.ID
Inner JOIN is a comparison operator that returns only rows that meet the criteria.
This is equivalent to: SELECT * from A, a where a.id=b.id
SQL Server Basics (table operations, data constraints, multi-link queries)