Basic Database SQL syntax and basic SQL syntax

Basic Database SQL syntax and basic SQL syntax

Reprint please indicate from Zhu jiayuan http://blog.csdn.net/zhgl7688

Basic Database SQL syntax

1. Create a SQL database,

Open SQL Server Management Studio, click "new query", and enter the following command in the query window to create the SQL database.

Command Execution Process: Open the master data table, create the master data file and log file of the database, and finally execute the command.

-- Point to the database to be used currently

Use master

Go

-- Create a database

Create databasestudentDB

On primary

(

Name = 'studentdb _ data ',

Filename = 'd: \ studentDB_data.mdf ',

Size = 20 mb,

Filegrowth = 1 mb

)

-- Create log

Log on

(

Name = 'studentdb _ log ',

Filename = 'd: \ studentDB_log.ldf ',

Size = 2 mb,

Filegrowth = 1 mb

)

Go

2. How to delete a database

---- Point to the database to be used currently

Use master

Go

-- Determines whether the database to be deleted exists and deletes the database if it exists.

If exists (select * from sysdatabases where name = 'studentdb ')

Drop databasestudentDB

Go

3. Database separation: disconnects the database from the server to facilitate other operations on database files, such as copying and moving. If the database files are continuously opened, they cannot be copied or moved.

Exec sp_detach_db @ dbname = studentDB

4. Attach a database: connect an existing database file to the server and add it to the server.

4.1 Method 1:

Exec sp_attach_db @ dbname = studentDB,

@ Filename1 = 'd: \ studentDB_data.mdf ',

@ Filename2 = 'd: \ studentDB_log.ldf'

4.2 Method 2:

Exec sp_attach_dbstudentDB,

'D: \ studentDB_data.mdf ',

'D: \ studentDB_log.ldf'

 

5. Table creation syntax

Create a table Students in the studentDB Database

Use studentDB

Go

If exists (select * from sysobjects where name = 'students ')

Drop tableStudents

Create tableStudents

(

StudentId int identity (rule, 1) primary key,

StudentName varchar (20) not null,

Gender char (2) not null,

BirthDay smalldatetime not null,

StudentIdNo numeric (18, 0) not null,

Age intnot null,

PhoneNumber varchar (50 ),

StudentAddress varchar (500) default ('address unknown '),

ClassId int not null

)

Go

6. Special descriptions of Columns

(1) Whether it is null: if it is allowed to be null, no data is input; otherwise, it must be input (not null ).

(2) create a primary key: The primary key is the unique identifier of the object to ensure that the object is unique. (Primary key)

(3) Default Value: when the user does not input data, a default content is provided. (Default ('address unknown '))

ID column: it is also called "auto-increment column" or "auto-Number". Each time an incremental value is increased based on the given ID. Note that this column must be an integer type. When a data table with an ID column is deleted from a row, the database will leave this row empty, and you cannot enter or modify the data yourself. Identity (priority, 1)

 

7. Insert syntax

Insert into <Table Name> [column name] values <Value List>

Insert into StudentClass (ClassId, ClassName) values (1, 'Software class ')

8. Basic query syntax

Select <column Name> from <source table name> [where <query condition>]

Select studentid, studentnamefrom Students whereAge> = 22

9. Update the syntax

Update <Table Name> set <column name = Update value> [where <Update condition>]

Update Studentsset StudentAddress = 'du Xiaoli's address 'where StudentName = 'du Xiaoli'

10. Delete the data syntax in the data table

Delete from <Table Name> [where <deletion condition>] // when you Delete data, this record must not be referenced by a foreign key. After deletion, the ID column continues to grow.

Delete from Students where StudentId = 10009

Truncate table <table Name> // Note: When deleting data, the table to be deleted must not have a foreign key constraint. After deleting the table, add data again and re-orchestrate the column.

Truncate tablestudents

Reprint please indicate from Zhu jiayuan http://blog.csdn.net/zhgl7688