Simple SQL operations

-----------------------------The SQL statement for the database-------------------------
1. Database
Creating the CREATE Database Data_name
On primary
(Name=, Filename=, size=, maxsize=, filegrowth=),
Filegroup [Auxiliary file group name]
(Name=, Filename=, size=, maxsize=, filegrowth=)
Log on
(Name=, Filename=, size=, maxsize=, filegrowth=)
Modify ALTER DATABASE name
Modify Name= new name
Delete Drop Database Data_name

2. Architecture
Creating the Create Schema Jiagou
Delete Drop schema Jiagou

3. Auxiliary documents
Add ALTER DATABASE Data_name
Add file (name=file1,filename= ' d:\file1.ndf ', SIZE=10MB,FILEGRWTH=10MB) to filegroup
Group1
Modify ALTER DATABASE Data_name
Modify file (Name=, Filename=, size=, maxsize=, filegrowth=)
Delete ALTER DATABASE Data_name
Remove file file_name

4, log files (when modified, the logical name can not be modified)
Add ALTER DATABASE Data_name
Add log file (Name=, Filename=, size=, maxsize=, filegrowth=)
Modify ALTER DATABASE Data_name
Modify file (Name=, Filename=, size=, maxsize=)

5. File group
Add ALTER DATABASE Data_name
Add Filegroup group_name
Modify ALTER DATABASE Data_name
Modify Filegroup Original file group name name= new filegroup Name
Delete ALTER DATABASE Data_name
Remove Filegroup File Group name

---------------------------------the SQL statement for the table--------------------------------

1. Table
Created: CREATE TABLE table_name
(
ID int identity (1001,3) primary key NOT NULL,
St_name nvarchar (ten) NULL,
Sex nvarchar (4) Default (' Male '),
Gongzi Money,
Shijian datetime
)
Modify table name: Exec sp_rename ' table ', ' table33 ' (note: Try not to change the name of the table, easy to cause errors of other objects)
Delete: DROP TABLE table_name

2. Operation of table columns (fields)
Add column: ALTER TABLE table_name
The data type of the Add column list Null/not null
Delete column: ALTER TABLE table_name
Drop Column Name
Modify the name of the column: Exec sp_rename ' table name. Field name ', ' new field name ', ' column ' (Hint: Try not to change the name of the column, easy to cause errors)
To modify the data type of a column: ALTER TABLE table_name
The data type of the ALTER column list name
3, the operation of the data
Insert: INSERT INTO table_name (field 1, Field 2, Field 3) VALUES (value, value, value,)
Delete: Delete from where Stu_name= ' Wang Wei ' and id=3
Modified: Update table_name SET field name = value where id=4

------------------------------------primary key, build-out (supplemental)-------------------------
1. Create:

CREATE TABLE Class
(
cl_id int PRIMARY KEY,
Cl_Name nvarchar (ten) null
)

CREATE TABLE Address
(
add_id int PRIMARY KEY,
Add_name nvarchar (ten) null
)

CREATE TABLE Student
(
stu_id int PRIMARY KEY,
Stu_name nvarchar (ten) NULL,
cl_id int foreign key references class (cl_id) NULL,
add_id int foreign key references address (add_id) null
)

Meaning: Used to strengthen the connection between tables, keeping data consistency in several tables when adding, modifying, and deleting data

------------------------------SQL query Statement--------------------------------

1. Sorting
Select TOP (3) * FROM Student ORDER BY cl_id DESC

2. Grouping
Select class_id, SUM (score) as Chengji from student group by class_id
Having sum (score) >=250
ORDER by sum (SCORE) desc
Tip: The aggregate function cannot be used in the where, so the having

3. Clear the Table
TRUNCATE TABLE table_name

4. Eliminate duplicate columns
Select DISTINCT column name from TABLE_NAME (hint: This column can only be displayed, other columns cannot be displayed)

5. SELECT * FROM table_name where name in (' Zhang San ', ' John Doe ', ' Harry ')
SELECT * FROM table_name where score in (100,90,87,89,96)

-------------------------------table Join---------------------------------

1. INNER JOIN: SELECT * FROM student as s inner JOIN class
As C on s.cl_id=c.cl_id where .....

2. LEFT OUTER join: Returns all rows in the first named table that meet the criteria
SELECT * FROM student as S left join class as C on s.cl_id=c.cl_id
where ....

3. Right outer link: Returns all rows in the second named table that meet the criteria
SELECT * FROM student as S right join class as C on s.cl_id=c.cl_id
Where .....

4. Full outer joins: Returns all values in the left and right tables
SELECT * FROM student as S full join class as C on s.cl_id=c.cl_id

5. Cross join: All possible combinations of rows will be returned from the joined table (a Cartesian product will be generated)
SELECT * from student as S cross join class as C
where ....

6. Joins of more than two tables:
SELECT * FROM student as S join class as C
On s.cl_id=c.cl_id join address as a on s.add_id=a.add_id
Where S.name= ' Zhang San '

7. Union combines data from multiple tables
Select stu_id as ' student number ', stu_name as ' name ' from Student1
Union
Select ID, name from Student2
Union
Select s_id, s_name from Student3

---------------------------Sub-query----------------------

1, the handle query as a derived table

You can use a subquery to produce a derived table that replaces the table in the where, have, from clause, and note that the derived table is referenced with an alias

Select s.stu_name,s.cl_id from (SELECT * from student where stu_id >2) as S
Where S.stu_name= ' King II '

2, the handle query as an expression

In an SQL statement, where all expressions are used, a subquery can be substituted, in which case the subquery must take a table with values of a single column value, and the subquery can replace the expression containing the in keyword in the WHERE clause

SELECT * FROM student where stu_id not in (select ID from student where stu_id>2)

3. Using sub-query to correlate data

The associated subquery can be used as a dynamic expression because it changes with each change in the outer query

Example 1:
Select stu_id,stu_name,cl_id, (select count (stu_id) from student) as record number from student

Example 2:
SELECT * FROM student as S joins class as C on s.cl_id=c.cl_id
Where NOT EXISTS (SELECT * from class where c.cl_id>3)

---------------------------variables, conditional statements, loop statements--------------------------

1. Variables

(1) local variable---can be assigned by select and set

Example one: Declare @i int
Select @i=count (*) from student
Print @i

Example two: Declare @sname varchar (4)
Set @sname = ' Zhang San '
Print @sname

(2) Global variables---can only be provided by the system, the user can not be defined, so only to understand the book on the 70 pages will be

2. Conditional statements

(1) If...else ... Statement
DECLARE @sex_id int
declare @sex_name nvarchar (4)
Select @sex_id =sex from student (where ...)
If @sex_id =1
Begin
Set @sex_name = ' Male '
End
Else
Begin
Set @sex_name = ' Female '
End
Print @sex_name

(2) Case statement
Select Stu_name as name, (case cl_id
When 1 Then ' one class '
When 2 Then ' second class '
When 3 Then ' class three '
When 4 Then ' four shifts '
Else ' no classes '
End) as class from student

3. Circular statements

While statement:
DECLARE @i int
declare @name nvarchar (10)
Set @i=1
While @i<13
Begin
Select @name =stu_name from student where [email protected]
Print @name
Set @name = ' '
Set @[email protected]+1
End

------------------------------Transaction------------------------------

1, the concept of business
A transaction is a mechanism, a sequence of operations, that contains a set of database operations commands that either execute all or do not. Therefore, a transaction is an inseparable unit of work logic. This specifically enables data communication systems for simultaneous multi-user operations: Ticketing, banking, insurance, and securities trading systems. SQL statements that require transactions are typically update and delete operations, and so on.

2. Create a transaction
Start transaction: BEGIN TRANSACTION
COMMIT TRANSACTION: Commit Transaction
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION
@ @error global variable display error number

3. Examples
BEGIN TRANSACTION/* Start transaction */
DECLARE @error_sum INT/* Number of Errors */
DECLARE @sum INT/* Number * *
declare @a nvarchar (10)/* Transfer money * *
declare @b nvarchar (10)/* Money-holders */
DECLARE @x INT/* Transfer amount */

Set @error_sum =0
Set @a= ' Yu Cong '
Set @b= ' Xu '
Set @x=2000

Select @sum =count (*) from ICBC where [email protected] or [email protected]
If @sum = 2
Begin
Set @[email protected]r_sum+0
End
Else
Begin
Set @[email protected]_sum+1
End

Update ICBC set [email protected] where [email protected]
Set @[email Protected]_sum + @ @error
Update ICBC set [email protected] where [email protected]
Set @[email Protected]_sum + @ @error

If @error_sum > 0
Begin
print ' operation failed to perform rollback '
ROLLBACK TRANSACTION/* ROLLBACK TRANSACTION */
End

Else
Begin
print ' Operation succeeded '
COMMIT TRANSACTION/* COMMIT TRANSACTION */
End

-----------------------------View-------------------------------

A view is an alias for a query statement, and this alias is called a view
Categories of views: Standard view, indexed view, partitioned view


1. Create a View

Syntax: CREATE view name (alias for column)
As (select query statement)

Create View v_student (Sname,cname)
As (select S.stu_name,c.cl_name from
Student as S join class as C on
S.CL_ID=C.CL_ID)

2. Delete View

Drop View Name

3. Modify the View

Syntax is the same as creating a view: Alter View name (alias 1, alias 2)
As
(select ......)

4. Get the definition statement for the view

EXEC sp_helptext View name

5. View the column information for the view

EXEC sp_help View name

6. Viewing virtual tables for views

SELECT * FROM view name

7. Change the data in the view

Update from view name set .....

----------------------------Stored Procedure----------------------------------

1. Creating a parameter-free stored procedure

Syntax: CREATE PROCEDURE procedure Name
As
SQL statement Body

Example: CREATE PROCEDURE P_student
As
SELECT * FROM student as S join class as C
On s.cl_id=c.cl_id where s.stu_id>2

2. Create a stored procedure with input parameters

Syntax: CREATE PROCEDURE procedure Name
@ Parameter 1 data type (= default value),
@ Parameter 2 data type (= default value)
As
SELECT statement Body

Example: CREATE PROCEDURE P_student
@name nvarchar (10)
As
SELECT * FROM student as S join class as C
On s.cl_id=c.cl_id where [email protected]

3. Create a stored procedure with input and output parameters

Syntax: CREATE PROCEDURE procedure Name
@ Parameter 1 data type output,
@ Parameter 2 data type (= default value)
As
SQL statement Body
Return

Example: CREATE PROCEDURE P_stu_cla
@cname nvarchar () output,
@sname nvarchar (10)
As
Select @cname =c.cl_name from student as S join class as C
On s.cl_id=c.cl_id where [email protected]
Return

Call: Declare @cname nvarchar (10)
EXEC p_stu_cla @cname output, ' Wang er '
Select @cname

4, the management of stored procedures

To view the definition of a stored procedure
EXEC sp_helptext Procedure Name
To view information about a stored procedure
EXEC sp_help Procedure Name
To delete a stored procedure
drop procedure Procedure Name
modifying stored procedures
Alter PROCEDURE procedure Name
@ parameter name data type = default value of output
As
SQL statements
Return

-------------------------------------function--------------------------------------

SQL server2005 supports three user-defined functions: scalar functions, inline table-valued functions, multi-statement table-valued functions

1. Scalar functions

Scalar functions derive different function values based on the values of the input parameters, and scalar functions can have multiple input parameters, but only one return value; The scalar function body consists of one or more SQL statements, starting with begin, ending with end, and using the returns sentence to define the data type of the return value of the function. and returns the value of the function

Syntax: Create function name (scalar parameter scalar data type)
The type of the returns function return value
As
Begin
function body
return variable/scalar expression
End

Example: Create function F_count (@sname nvarchar (10))
Returns nvarchar (10)
As
Begin
declare @cname nvarchar (10)
Select @cname =cl_name from student as S Jion class as C
On s.cl_id=c.cl_id where [email protected]
Return @cname
End

Calling function: declare @name nvarchar (10)
Select @name = schema name. F_count (' King II ')
Print @name

2. Inline table-valued functions

The inline table-valued function returns no scalar data, but a table, and the returned table-valued function can also provide parameterized view functionality.

Syntax: the CREATE function schema. function name (scalar parameter data type)
Returns table
As
Return (SELECT statement)

Call Function: SELECT * FROM schema. Function name (parameter)

Simple SQL operations