Database Basic Application Knowledge Summary __ Database

Database application Basics

--------------------------------Start----------------------- ^_^ Preface:

If the basic statement you would have, the first three basic databases with tables and records of simple operations can be skipped.

Use language: T-SQL language, database statements

Operating environment: SQL Server 2012

----------------------------------------------------------------
basic operation of database and table

1. First, the basic operation of the database:

Create DATABASE Hpu--	creates a HPU-named DB use
HPU-				-Changes the current database to HPU, that is, the next operation is based on the HPU database
drop HPU-			-Deleting the database Hpu

2. Then manipulate the table:

(1) The creation of the table

Car is very popular in today, many people have to test driver's license, I have not got hands, love dearly ...

Take the license plate as an example to build a table named vehicle

CREATE TABLE Vehicle
(
	number_id varchar (one),	--license plate
	brand varchar (one),		--Brand
	color varchar (one),		--color
	makedate Date			--factory date

(2) Deletion of the table

DROP TABLE Vehicle			--Deletes the data in the table, deletes the table delete
vehicle-				-Deletes the data in the table

--------------------------------Second--------------------------------
second, the operation of the field and record of additions and deletions to check

(1) The operation of the field

ALTER TABLE vehicle add carowner varchar-		-add field (column)
ALTER TABLE vehicle ALTER COLUMN Carowner int		--Modify field type
ALTER TABLE vehicle ALTER COLUMN Carowner varchar
sp_rename ' vehicle.carowner ', ' carmaster ', ' column ' -	-Modify column name
ALTER TABLE vehicle drop column Carmaster-		-Deletes the field of table vehicle Carmaster

(2) Basic additions and deletions are the simplest, most basic and most commonly used SQL statements

Insert into vehicle (number_id,brand,makedate) VALUES (' Yu HPU579 ', ' Red Flag ', GETDATE ())
insert into vehicle values (' Yu hpu33u ', ' Volkswagen ', ' Silver Grey ', GETDATE ())
insert into vehicle values (' Yu HPU123 ', ' Red flag ', ' black ', GETDATE () -365*2)
Delete Vehicle where number_id = ' Yu hpu23u '--delete delete		vehicle by condition-						-Deletes all records in a table
update vehicle Set color = ' Red ' whe Re brand = ' Red flag '-	update the color of the car by condition Update
vehicle Set makedate = GETDATE ()-			-the factory date of all records in the Update table is the current time
select number _id,makedate from vehicle where brand = ' red flag '-	-Find the data of the desired field by condition
Select top 2 *				from vehicle--the first two records
in a query table SELECT * FROM vehicle

--------------------------------Third--------------------------------
third, the constraint

A constraint is a literal understanding of the existence of one or more rules, conditions that govern fields, data. Constraints are used to maintain database integrity, and the column definition constraints in a table can effectively prevent incorrect data from being inserted into a table, or to ensure consistency between tables.

The common constraints are:

1.Primary Key PRIMARY KEY constraint

2.Foreign key FOREIGN KEY constraint

3.Unique UNIQUE Constraint

4.Check CHECK Constraint

5.Default DEFAULT Constraint

6.Not NULL is NOT NULL

For example, setting constraints on vehicle creation of the previous table

CREATE TABLE Vehicle
(
	number_id varchar (one) primary key-	-set as primary key
	brand varchar (one) not NULL,		C-C The data is not empty
	color varchar (one) default ' black ',	--makedate color is black (date not
	null--			factory date
)

Test constraints:

Insert into vehicle (number_id,brand,makedate) VALUES (' Yu HPU579 ', ' Red Flag ', GETDATE ())	--insert succeeded; color defaults to ' black '
insert Into vehicle (number_id,brand,makedate) VALUES (' Yu HPU579 ', ' Volkswagen ', GETDATE ())	--Insert failed: Primary key is unique, a record with a primary key of ' Yu HPU579 ' cannot be inserted again Insert
into vehicle values (' Yu HPU123 ', ' Volkswagen ', ' Apple green ', GETDATE ())			--inserted successfully;
INSERT into vehicle values (' Yu HPU678 ', null, ' Apple green ', GETDATE ())				--insert failed; Cannot insert value NULL into column ' brand ', column does not allow null value

The results of the EXECUTE Query statement are as follows:

Just like the above if the constraint is not written in a table creation statement, and the table is already created. How to modify it.

Modify the statement instance as follows:

ALTER TABLE vehicle ALTER COLUMN number_id varchar (one) NOT null-			-the SET field is NOT NULL
ALTER TABLE vehicle ALTER COLUMN Brand V Archar (one) NOT NULL
ALTER TABLE vehicle ALTER COLUMN makedate date NOT NULL
ALTER TABLE vehicle ADD constraint Pk_ NUMBER_ID primary KEY (number_id)-		-Modify field number_id primary key
ALTER TABLE vehicle add constraint Df_color Default (' Gray ') For color		--Set default
ALTER TABLE vehicle add constraint ck_makedate check (Makedate < getdate ())	-- Verify that the production date should be less than today

Other modification statements are similar.

--------------------------------Fourth-------------------------------- four functions

The database statement also has functions, and the example above has used a function getdate () to get the current time.

RAND () random function;

Sin (), cos (), tan () trigonometric functions;

SUM (), AVG (), Max (), Min (), Len (), Sum, average, maximum minimum ball length function;

DateDiff (), GETDATE (), DATEADD () Date function;

Cast (), Str (), char () data conversion function;

SUBSTRING (), left (), right (), LTrim () intercept string functions, and so on.

Here is a simple example of the use of a function: (using a function to generate a string of length 49, calculate the time required to run the program)

Print (' Current time: ')
print (GETDATE ())-								-Gets the current time
declare @i int, @c varchar (m), @date1 datetime, @date2 datetime
Set @i = 0
Set @c = '
set @date1 = GETDATE () while
@i <
    begin
	    Set @c = @c + char (round (ran D () *26+65,0)			--random function to ensure that the generated numbers are random, corresponding characters are not the same
		set @i = @i + 1 end
Set @date2 = getdate ()
print @c< C14/>print ' answer length is: ' +cast (@c) as varchar ())			The result of--len () is integer, cast to String type
print (' Elapsed time: ' +cast ( DateDiff (MS, @date1, @date2) as varchar) + ' MS '--datediff calculates two given time difference in milliseconds

The results of the operation are as follows:

--------------------------------Fifth--------------------------------
v. Views and indexes

1. View creation and deletion

CREATE VIEW TestView as SELECT * FROM vehicle	--Creating View
SELECT * FROM TestView
select * from vehicle
drop vie W TestView				--delete view

The results of executing the query table and query view under SQL server2012 are as follows:

The query results for the view are the same as the results of the normal query table records. The difference is that the result of a view query is a logical result, and the direct query table gets the actual data. The view prevents the deletion of the table data by mistake. I don't know if I'm right, please tell me if there is any error or nonstandard. Thank you, ^_^.

The role of the view:

(1) Hide the complexity of the data

(2) To control user access to certain columns in the table

(3) Make user queries simple.

2. Creation and use of indexes

CREATE index Testindex on vehicle (number_id)-	-Creates index
select * from vehicle where number_id = ' Henan HPU579 '
drop ind Ex Vehicle.testindex-				-delete index

(1) The creation of the index, make the query of the data quickly, greatly reducing the query time, in the case of a small amount of data can not be seen, test insert 200,000 records into table vehicle, the GETDATE () function to calculate a random query table before the index to create a record of 56 milliseconds , 3 milliseconds after the index was created.

(2) Index Description:

The most frequent operation of a user's database is to query the data. In general, a database needs to search the entire table for a query operation. When the data in the table is very large, it takes a long time to search the data, which causes the server to waste resources. In order to improve the ability of retrieving data, the database introduces the index mechanism the most frequent operation of the database is data query. --------------------------------Sixth-------------------------------- six, triggers

1. Creation and deletion of triggers

Create trigger Testtri on vehicle for Insert,update,delete as
begin
    Print (' You operate on table vehicle ') End			-- Create a trigger

drop trigger Testtri	--Delete a trigger

When a delete insert or update operation is performed, the console prints the prompt statement. The creation of triggers implements the monitoring of the operation of the table record.

The effect is as follows:

A trigger is an action that can be performed before an action is performed or an event is triggered.

2. The role of triggers

Baidu gives the role:

(1) Force to verify or transform data before writing to the datasheet

(2) When the trigger is wrong, the result of the change will be revoked.

(3) Some database management systems can use triggers for data definition language (DDL), called DDL triggers

(4) can be in accordance with specific circumstances, the replacement of the instructions of the move

I hope it will be of some help to you.

--------------------------------End--------------------------------