SQL language Learning-data definition language

There are 6 versions of the SQL language so far. The SQL query language includes all operational commands for data, which can be divided into four categories: data definition Language (DDL), Data Manipulation language (DML), Data Control Language (DCL), and Embedded SQL language.

Data defination Language is used to define database objects. The basic DDL commands are: Create, alter, drop.

Data manipulation language (manipulation Language) is used to retrieve data and update data, including: Insert, Delete, update. Data retrieval is done by select.

Data control Language includes authorization for permissions (grant), Revocation (Revoke), description of integrity rules, and control statements such as transaction start and end.

Embedded SQL language rules the use of SQL language in host programs.

1. Create a data sheet

Create is responsible for establishing database objects. databases, data tables, database indexes, stored procedures, triggers, and so on can all be created through create.

Create Build Table:

1 CREATE TABLE [dbo].[Customer](2     [Cusid] [Numeric]( -,0) not NULL,3     [Regid] [int]  not NULL,4     [Cusname] [varchar]( -) not NULL,5     [Cusphone] [varchar]( -) not NULL,6     [cusaddress] [varchar]( -) not NULL,7     [cusredate] [Date] NULL,8     [Cuscredit] [int]  not NULL,9     [Cusprepay] [Numeric](Ten,0)NULL,Ten  CONSTRAINT [Pk_customer] PRIMARY KEY CLUSTERED  One ( A     [Cusid] ASC -) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY], -  CONSTRAINT [Uni_cust] UNIQUE nonclustered  the ( -     [Cusname] ASC -) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY] -) on [PRIMARY]

Create Build Table

CREATE TABLE table_name (column datatype [column level constraint])

For example, CREATE TABLE Test (ID integer constraint pk_test primary key,

Name varchar (+) NOT null Unique,stuid integer not null,constraint fk_student foreign key references Student (STUID))

This code enables you to create a table of test and to define the ID, name, and stuid three columns. Where the ID is the primary key, Stuid is the foreign key. The foreign key table must be a table that already exists.

2. Change the data sheet

You may want to modify the structure or constraints of a table during the use of the data table. Alter is the instruction that is responsible for modifying the database object. For a database you can use ALTER TABLE TableName modification.modification is a clause that specifies the content of the modification.

For example: Add a column to the table: ALTER TABLE test add age Integer

Delete a column: ALTER TABLE test Drop column Age

Change list: In Oracle: ALTER TABLE test rename age to ages. In sql: EXEC sp_rename ' customer. [age] ', ' Ages ', ' column '

Change data type: SQL: ALTER TABLE test ALTER COLUMN ages varchar in Oracle: ALTER TABLE test modify ages Integer

Add constraint: ALTER TABLE TEST add constraint uni_ages unique (ages)

Disable constraint: Alter Table account NOCHECK constraint fk_student

3. Delete Data Sheet

The operation to delete a database object is done using the drop command. DROP TABLE table_name.

SQL language Learning-data definition language