|
ADD|DROP|MODIFY (COLUMN SPECIFICATION[S]...see Create Table);
--allows you to add or delete a column or columns from a table, or
change the specification (data type, etc.) on an existing column;
this statement is also used to change the physical specifications
of a table (how a table is stored, etc.), but these definitions are
DBMS-specific, so read the documentation. Also, these physical
specifications are used with the Create Table statement, when a
table is first created. In addition, only one option can be
performed per Alter Table statement --either add, drop,
OR modify in a single statement.
COMMIT; --makes changes made to some
database systems permanent (since the last COMMIT; known as a
transaction)
CREATE [UNIQUE] INDEX <INDEX
NAME>
ON
|
(<COLUMN LIST> ); --UNIQUE is optional; within brackets.
CREATE TABLE
|
|
(<COLUMN NAME> <DATA TYPE> [(<SIZE> )]
<COLUMN CONSTRAINT> ,
...other columns); (also valid with ALTER TABLE)
--where SIZE is only used on certain data types (see above), and
constraints include the following possibilities (automatically
enforced by the DBMS; failure causes an error to be generated):
-
NULL or NOT NULL (see above)
-
UNIQUE enforces that no two rows will have the same value for
this column
-
PRIMARY KEY tells the database that this column is the primary
key column (only used if the key is a one column key, otherwise a
PRIMARY KEY (column, column, ...) statement appears after the last
column definition.
-
CHECK allows a condition to be checked for when data in that
column is updated or inserted; for example, CHECK (PRICE
0) causes the system to check that the Price column is greater
than zero before accepting the value...sometimes implemented as the
CONSTRAINT statement.
-
DEFAULT inserts the default value into the database if a row is
inserted without that column's data being inserted; for example,
BENEFITS INTEGER DEFAULT = 10000
-
FOREIGN KEY works the same as Primary Key, but is followed by:
REFERENCES
|
(<COLUMN NAME> ), which refers to the referential primary
key. CREATE VIEW
|
AS <QUERY> ;
DELETE FROM
|
WHERE <CONDITION> ;
INSERT INTO
|
[(<COLUMN LIST> )]
VALUES (<VALUE LIST> );
ROLLBACK; --Takes back any changes to
the database that you have made, back to the last time you gave a
Commit command...beware! Some software uses automatic committing on
systems that use the transaction features, so the Rollback command
may not work.
SELECT [DISTINCT|ALL] <LIST OF COLUMNS,
FUNCTIONS, CONSTANTS, ETC.>
FROM <LIST OF TABLES OR VIEWS>
[WHERE <CONDITION(S)> ]
[GROUP BY <GROUPING COLUMN(S)> ]
[HAVING <CONDITION> ]
[ORDER BY <ORDERING COLUMN(S)> [ASC|DESC]]; --where
ASC|DESC allows the ordering to be done in ASCending or DESCending
order
UPDATE
|
|
SET <COLUMN NAME> = <VALUE>
[WHERE <CONDITION> ]; --if the Where clause is left
out, all rows will be updated according to the Set statement.
|
|
|
|
|
|
