Loading lesson path

SQL•SQL References

SQL CREATE Keyword

Concept visual

SQL CREATE Keyword

Pointer walk

two pointers

left=0

right=6

Start at both ends

CREATE Keyword

❮ SQL Keywords

Create Database

The CREATE DATABASE

command is used is to create a new SQL database. The following SQL creates a database called "testDB":

Example

CREATE DATABASE testDB;

Tip:

Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

Create Table

The CREATE TABLE

command creates a new table in the database. The following SQL creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example

CREATE TABLE Persons

( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255),

City varchar(255)

);

CREATE TABLE Using Another Table

The following SQL creates a new table called "TestTables" (which is a copy of two columns of the "Customers" table):

Example

CREATE TABLE TestTable AS

SELECT customername, contactname

FROM customers;

Create Index

The CREATE INDEX

command is used to create indexes in tables (allows duplicate values). Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. The following SQL creates an index named "idx_lastname" on the "LastName" column in the "Persons" table: CREATE INDEX idx_lastname

ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas: CREATE INDEX idx_pname

ON Persons (LastName, FirstName);

Note:

The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

Note:

Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

Create Unique Index

The CREATE UNIQUE INDEX

command creates a unique index on a table (no duplicate values allowed) The following SQL creates an index named "uidx_pid" on the "PersonID" column in the "Persons" table: CREATE UNIQUE INDEX uidx_pid

ON Persons (PersonID);

Create View

The CREATE VIEW

command creates a view. A view is a virtual table based on the result set of an SQL statement. The following SQL creates a view that selects all customers from Brazil:

Example

CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName

SQL CONSTRAINT Keyword

SQL CREATE DATABASE Keyword

Loading lesson path

Learn/SQL/SQL References

SQL•SQL References

SQL CREATE Keyword

Concept visual