SQL Advanced (5)

SQL Advanced (5) 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.
Indexes You can create indexes in tables to query data more quickly and efficiently.
Users cannot see the index, they can only be used to speed up search/query.
Note: 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. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.
The SQL CREATE INDEX syntax creates a simple index on the table. Allowed to use duplicate values:

CREATE INDEX Index_nameon table_name (column_name)

Note: "column_name" specifies the columns that need to be indexed.
The SQL create unique index syntax creates a unique indexes on the table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX Index_nameon table_name (column_name)

Create INDEX Instance This example creates a simple index, named "Personindex," in the LastName column of the person table:

If you want to index values in a column in descending order, you can add the reserved word DESC after the column name:

If you want to index more than one column, you can list the names of these columns in parentheses, separated by commas:

CREATE INDEX personindexon person (LastName, FirstName)

SQL revokes indexes, tables, and databases by using the DROP statement, you can easily delete indexes, tables, and databases.
SQL DROP INDEX Statement We can use the DROP Index command to delete an index in a table.
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

The SQL DROP TABLE statement DROP TABLE statement is used to delete tables (the structure, properties, and indexes of the table are also deleted):

DROP Table Name

The DROP DATABASE statement is used to delete the databases:

DROP database name

SQL TRUNCATE Table Statement If we just need to get rid of the data in the table, but not delete the table itself, then what should we do?
Please use the TRUNCATE Table command (delete only the data in the table):

TRUNCATE Table Name

The SQL ALTER TABLE statement ALTER TABLE statement is used to add, modify, or delete columns in an existing table.
SQL ALTER table syntax to add a column to a table, use the following syntax:

ALTER TABLE Table_nameadd column_name datatype

To delete a column from a table, use the following syntax:

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).
To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_namealter COLUMN column_name datatype

The original table (used in the example):
Persons table:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

SQL ALTER Table Instance now, we want to add a new column named "Birthday" in the table "Persons".
We use the following SQL statements:

ALTER TABLE personsadd Birthday Date

Note that the type of the new column "Birthday" is date and can hold the date. The data type specifies the type of data that can be stored in the column.
The new "Persons" table looks like this:
City

Id	LastName	FirstName	Address		Birthday
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Changing the data type instance now we want to change the data type of the "Birthday" column in the "Persons" table.
We use the following SQL statements:

ALTER TABLE Personsalter COLUMN Birthday Year

Note that the data type of the "Birthday" column is year, which can hold 2-bit or 4-bit formats.
Drop column instance Next, we delete the "Birthday" column in the "Person" table:

ALTER TABLE Persondrop COLUMN Birthday

The Persons table will become like this:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

The SQL AUTO INCREMENT field auto-increment generates a unique number when a new record is inserted into the table.
Auto INCREMENT field We usually want to automatically create a value for the primary key field each time we insert a new record.
We can create a auto-increment field in the table.
Syntax for MySQL the following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons (p_id int not NULL auto_increment,lastname varchar (255) not null,firstname varchar (255), Address Varch AR (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, use the following SQL syntax:

ALTER TABLE Persons auto_increment=100

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 ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".
Syntax for SQL Server the following SQL statement defines the "p_id" column in the "Persons" table as a auto-increment primary key:

CREATE TABLE Persons (p_id int PRIMARY KEY identity,lastname varchar (255) 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 ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".
Syntax for Access the following SQL statement defines the "p_id" column in the "Persons" table as a auto-increment primary key:

CREATE TABLE Persons (p_id int PRIMARY KEY autoincrement,lastname varchar (255) not null,firstname varchar (255), Address var char (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)
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 ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".
Syntax for Oracle in Oracle, the code is a little bit more complicated.
You must create a auto-increment field (the object generates a sequence of numbers) through sequence.
Please use the CREATE SEQUENCE syntax below:

CREATE SEQUENCE Seq_personminvalue 1START with 1INCREMENT by 1CACHE 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 (5)