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