SQL Commands
Please take a look at this list and review any that seem unfamiliar:
CREATE TABLE statements and data type assignments
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Here are the most common Data types:
char(size) Fixed-length ...
char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
Number value with a maximum number of digits of "size" total, with a maximum number of "d"
number(size,d)
digits to the right of the decimal.
CREATE TABLE … LIKE
Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column
attributes and indexes defined in the original table:
CREATE TABLE new_tbl LIKE orig_tbl;
CREATE TABLE ... LIKE creates a new table as an empty copy of the original one. It copies the original table
structure exactly, so that each column is preserved with all of its attributes. The index structure is copied as well.
However, the new table is empty, so to populate it a second statement is needed (such as INSERT INTO ... SELECT).
Also, CREATE TABLE ... LIKE cannot create a new table from a subset of the original table's columns, and it cannot
use columns from any other table but the original one.
To use CREATE TABLE ... LIKE for creating an empty copy of an existing table, write a statement like this:
CREATE TABLE new_tbl_name LIKE tbl_name;
CREATE TABLE … SELECT
To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
CREATE TABLE ... SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this
statement does not copy all column attributes such as AUTO_INCREMENT. Nor does creating a table by selecting
data into it automatically copy any indexes from the original table, because result sets are not themselves
indexed. On the other hand, CREATE TABLE ... SELECT can both create and populate the new table in a single
statement. It also can create a new table using a subset of the original table and include columns from other
tables or columns created as the result of expressions.
,CREATE TABLE ... SELECT also can create new tables that don't contain exactly the same set of columns in an
existing table. You can use it to cause a new table to spring into existence on the fly to hold the result of an
arbitrary SELECT query. This makes it exceptionally easy to create a table fully populated with the data in which
you're interested, ready to be used in further statements. However, the new table can contain strange column
names if you're not careful. When you create a table by selecting data into it, the column names are taken from
the columns that you are selecting.
To create an empty copy of a table and then populate it from the original table, use CREATE TABLE ... LIKE followed
by INSERT INTO ... SELECT:
CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name SELECT * FROM
tbl_name; ALTER TABLE (what is it used for)
ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy
indexes, change the type of existing columns, or rename columns or the table itself. You can also change
characteristics such as the storage engine used for the table or the table comment.
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
ALTER TABLE (examples)
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN
d; DROP TABLE
DROP TABLE removes one or more tables. You must have the DROP privilege for each table.
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables
in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming
from one single table.
CREATE VIEW view_name AS
SELECT column1, column2,
... FROM table_name
WHERE condition;
, ALTER VIEW
This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW
see Section 13.1.21, “CREATE VIEW Statement”). This statement requires the CREATE VIEW and DROP privileges
for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted
only to the definer or users with the SUPER privilege.
The CREATE INDEX statement is used to create indexes in tables.
CREATE INDEX index_name
ON table_name (column1, column2, ...);
The DROP INDEX statement is used to delete an index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name
DROP INDEX index_name;
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller EvaTee. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $14.49. You're not tied to anything after your purchase.
