SQL CREATE Keyword
CREATE DATABASE command is used is to
create a new SQL database.
The following SQL creates a database called "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 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:
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):
SELECT customername, contactname
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:
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:
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
CREATE UNIQUE INDEX command creates a unique index on a table (no duplicate values
The following SQL creates an index named "uidx_pid" on the "PersonID" column in the "Persons" table:
ON Persons (PersonID);
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:
SELECT CustomerName, ContactName
WHERE Country = "Brazil";
CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW command updates a view.
The following SQL adds the "City" column to the "Brazil Customers" view:
SELECT CustomerName, ContactName, City
WHERE Country = "Brazil";
Query The View
We can query the view above as follows:
CREATE PROCEDURE command is used to
create a stored procedure.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
The following SQL creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:
SELECT * FROM Customers
Execute the stored procedure above as follows: