SQL Server Basics (table operations, data constraints, multi-link queries)

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)