SQL start from scratch

SQL is divided into two parts: Data Manipulation Language (DML) and data definition language (DDL)

The query and update Directives form the DML portion of SQL:

• SELECT -get data from a database table
• Update-updating data in a database table
• Delete-deletes data from the database table
• INSERT INTO-inserts data into a database table

The Data definition language (DDL) portion of SQL gives us the ability to create or delete tables.

We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statement in SQL:

• CREATE database-creating new databases
• alter database-changes to databases
• CREATE TABLE-Creates a new table
• ALTER TABLE -Change (change) database table
• Drop Table-delete tables
• CREATE index-Creating indexes (search key)
• Drop Index-delete indexes

Step-by-step:

Set up a database first

(Note: SQL is not sensitive to uppercase and lowercase, both are used to capitalize)

CREATE DATABASE db_name

Then create a table

CREATE TABLE article (Id int not null,title varchar (), author varchar (), keyword varchar (), CreateDate datetime, Content text,express Text)

Then you are done. A database a table out, but there is a problem, I want to set a primary key to do, and to achieve the value of self-increment (that is, the value of this column can be based on your own initiative to build values)

That's what I wrote.

ALTER TABLE [db_name]. [dbo]. [Article] ADD CONSTRAINT id_key PRIMARY key (Id)

Don't laugh, I just started to contact the database, so the above error, what error? Is the primary key is done well, but the effect of self-increase is not realized, how to achieve it, and then I tried to find that there are two property settings: IDENTITY and auto_increment, using Identitykeyword in SQL to run Auto_increment ( Note: The identity start value is 1, each new record is incremented by 1), but unfortunately this keyword can only be used in two cases, one is used when establishing a table, such as the following:

Create TABLE article (Id int not NULL PRIMARY KEY identity,title varchar (), author varchar (), keyword varchar (#), create Date datetime,content text,express text)

There is also the time to add a new field. For example, the following:

ALTER TABLE [db_name]. [dbo]. [Article] ADD Id int IDENTITY (+)

Feel that the first and the other one, now only can choose another, should be for the table has been established, do not want to delete the table reconstruction, then delete the ID field once again added to the field bar. Then I'm going to delete that field. The tragedy has come again, the result that field can not be deleted. The code is as follows:

ALTER TABLE [db_name]. [dbo]. [Article] DROP COLUMN Id

Later, the head of the Flash, is not the above I set the primary key when the deletion, small try, I first remove the primary key constraints, code such as the following:

ALTER TABLE [db_name]. [dbo]. [Article] DROP CONSTRAINT Id_key

Then run the code that deletes the ID field. It turned out to be

OK, I can add the field again. The code is as follows:

ALTER TABLE [db_name]. [dbo]. [Article] ADD Id int IDENTITY (+)

OK, join the PRIMARY KEY constraint. The code is as follows:

ALTER TABLE [db_name]. [dbo]. [Article] ADD CONSTRAINT id_key  PRIMARY key (Id)

Well, a self-increasing primary key table is set up. Forgot to be able to add information, code such as the following:

INSERT into [db_name]. [dbo]. [Article] (title,author,keyword,createdate,content,express) VALUES (' Mid-Autumn Festival ', ' Home ', ' festivals ', GETDATE (), ' The next festival is the mid-Autumn Festival, since the university to now every mid-Autumn festival is outside, sometimes calm down to think really sorry home mom and dad, every national day home, Mom and dad will take the house to me to save the moon cakes, all said son, son but on the other ', ' in addition to the National Day and the Spring Festival holiday, other holidays are short, the whole holiday days are not enough to spend on the road time, the only thing is their own holiday ')

Cond.....

SQL start from scratch