SQL Advanced (v) (CREATE index Drop)

SQL CREATE INDEX statement

The CREATE INDEX statement is used for creating indexes in the table.

Indexes enable database applications to find data faster without reading the entire table.

Users cannot see the index, they can only be used to speed up search/query.

Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated.

The ideal approach is to create indexes only on columns (and tables) that are often searched .

Allowed to use duplicate values:

CREATE INDEX Index_name

On table_name (COLUMN_NAME)

SQL Create unique index syntax

Creates a unique index on the table. A unique index means that two rows cannot have the same index value.

Create UNIQUE index Index_name

On table_name (COLUMN_NAME)

Create INDEX instance

CREATE INDEX Personindex

On person (LastName)

To index a value in a column in descending order, add the reserved word DESC:

CREATE INDEX Personindex

On person (LastName DESC)

To add an index to multiple columns

CREATE INDEX Personindex

On person (LastName, FirstName)

SQL revocation indexes, tables, and databases

SQL DROP INDEX statement

Syntax for Microsoft Sqljet (and Microsoft Access):

DROP INDEX index_name on table_name

Syntax for MS SQL Server:

Drop INDEX Table_name.index_name

For IBM DB2 and Oracle syntax:

Drop INDEX Index_name

Syntax for MySQL:

ALTER TABLE table_name DROP INDEX Index_name

SQL TRUNCATE TABLE statement

Drop the data in the table, but do not delete the table itself

TRUNCATE TABLE name

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, modify, or delete columns in an existing table.

Adding Columns to a table

ALTER TABLE TABLE_NAME

ADD column_name datatype

To delete a column in a table

ALTER TABLE TABLE_NAME

Drop Column column_name

Note: Some database systems do not allow this method of deleting columns in a database table (DROP column column_name).

Change the data type of a column in a table

ALTER TABLE TABLE_NAME

Alter COLUMN column_name datatype

Auto-increment

auto-increment will generate a unique number when the new record is inserted into the table.

The value of the primary key field is automatically created each time a new record is inserted.

The syntax of MySQL

CREATE TABLE Persons

(

p_id int NOT null auto_increment,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

primary KEY (P_ID)

)

MySQL uses the Auto_increment keyword to perform auto-increment tasks.

By default, the start value of Auto_increment is 1, and each new record is incremented by 1.

To have the auto_increment sequence start with a different value

ALTER TABLE Persons AUTO_INCREMENT=100

Syntax for SQL Server

CREATE TABLE Persons

(

p_id Int PRIMARY Key identity,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255)

)

MS SQL uses the IDENTITY keyword to perform auto-increment tasks.

By default, the start value of the IDENTITY is 1, and each new record is incremented by 1.

To specify that the "p_id" column starts at 20 and increments by 10, change the identity to identity (20,10)

To insert a new record in the "Persons" table, we do not have to specify a value for the "p_id" column (a unique value is added automatically):

INSERT into Persons (firstname,lastname)

VALUES (' Bill ', ' Gates ')

Syntax for Access

CREATE TABLE Persons

(

p_id int primary AutoIncrement,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255)

)

MS Access uses the AutoIncrement keyword to perform auto-increment tasks.

By default, the start value of AutoIncrement is 1, and each new record is incremented by 1.

To specify the "p_id" column to start at 20 and increment by 10, change the AutoIncrement to AutoIncrement (20,10)

Oracle's syntax

The Auto-increment field (which generates a sequence of numbers) must be created by sequence.

Please use the Create sequence syntax below:

Create sequence Seq_person

MinValue 1

Start with 1

Increment by 1

Cache 10

The above code creates a sequence object named Seq_person, which starts with 1 and increments by 1. The object caches 10 values to improve performance.

The cache option specifies how many sequence values to store in order to increase the speed of access.

To insert a new record in the "Persons" table, we must use the Nextval function (the function fetches the next value from the Seq_person sequence):

Insert into Persons (p_id,firstname,lastname)

VALUES (seq_person.nextval, ' Lars ', ' Monsen ')

The SQL statement above inserts a new record in the "Persons" table. The assignment of "p_id" is the next number from the Seq_person sequence. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".

SQL Advanced (v) (CREATE index Drop)