1.CREATE
TABLE syntax:-
CREATE
TABLE [ IF NOT EXISTS ] table_name
( column_declare1, column_declare2,
constraint_declare1, ... )
column_declare ::=
column_name type [ DEFAULT expression ]
[ NULL | NOT NULL ] [ INDEX_BLIST |
INDEX_NONE ]
type ::=
BIT | REAL | CHAR | TEXT | DATE | TIME |
FLOAT | BIGINT | DOUBLE | STRING | BINARY |
NUMERIC |
DECIMAL | BOOLEAN | TINYINT | INTEGER |
VARCHAR |
SMALLINT | VARBINARY | TIMESTAMP |
LONGVARCHAR |
JAVA_OBJECT | LONGVARBINARY
constraint_declare :: =
[ CONSTRAINT constraint_name ]
PRIMARY KEY ( col1, col2, ... ) |
FOREIGN KEY ( col1, col2, ... ) REFERENCES
f_table [ ( col1, col2, ... ) ]
[ ON UPDATE triggered_action ] [ ON DELETE
triggered_action ] |
UNIQUE ( col1, col2, ... ) |
CHECK ( expression )
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ NOT DEFERRABLE | DEFERRABLE ]
triggered_action
:: =
NO ACTION | SET NULL | SET DEFAULT | CASCADE
CREATE TABLE emp (
number
VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
add VARCHAR(50)
NOT NULL,
age INTEGER
NOT NULL,
CONSTRAINT
cust_pk PRIMARY KEY (number),
UNIQUE
( number ), // (An anonymous
constraint)
CONSTRAINT
age_check CHECK (age >= 0 AND age < 200));
ALTER
TABLE table_name ADD [COLUMN] column_declare
ALTER
TABLE table_name ADD constraint_declare
ALTER
TABLE table_name DROP [COLUMN] column_name
ALTER
TABLE table_name DROP CONSTRAINT constraint_name
ALTER
TABLE table_name DROP PRIMARY KEY
ALTER
TABLE table_name ALTER [COLUMN] column_name SET default_expr
ALTER
TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT
ALTER
CREATE TABLE ....
ALTER CREATE TABLE table
(
col1 INTEGER NOT NULL UNIQUE, col2 NUMERIC, col3 VARCHAR(90000) )
3.
DROP TABLE syntax:-
DROP
TABLE [ IF EXISTS ] table_name1, table_name2, ....
Removes
the table(s) from the database. The IF EXISTS
clause will drop the table only if it exists. If this clause is not present an
error is generated if the table does not exist. Any data that was in a dropped
table is lost so use with care.
CREATE
VIEW table_name [ ( column_name1, column_name2, ... ) ]
AS SELECT…..
Creates
a new view. A view is a virtual table based on the result of a SELECT
query. The content of a view may reference any number of other tables and
views.
A
simple example of a view follows;
CREATE
VIEW Vw_emp AS SELECT sal FROM emp
A
view acts like a regular table and can be queried as you would a table made
with the CREATE TABLE statement. Views are read-only.
DROP
VIEW table_name
Removes
a view from the database. A view can be changed by dropping and recreating it.
6. CREATE SEQUENCE syntax
CREATE
SEQUENCE name
[
INCREMENT increment_value ]
[ MINVALUE minimum_value ]
[ MAXVALUE maximum_value ]
[ START start_value ]
[ CACHE cache_value ]
[ CYCLE ]
Creates
a new sequence generator that can be used to generate an iterative sequence of
values. Sequence generators have a number of uses including the creation of
primary keys for a table. The INCREMENT, MINVALUE, MAXVALUE, START, and CACHE
values are all optional.
The
INCREMENT value specifies how the sequence increments each iteration. By
default a sequence generator increments by 1. The MINVALUE and MAXVALUE values
specify the bounds of the sequence generator. By default MINVALUE and MAXVALUE
are 0 and Long.MAX_VALUE respectively. The START value specifies the first key
(exclusive) of the generator. The CACHE value specifies how many keys should be
cached ahead of time.
Below
is an example that creates a new sequence generator called 'seq_key_1' that
starts at 10 and increments by 2 each iteration;
CREATE
SEQUENCE seq_1 INCREMENT 2 START 10
A
sequence generator is accessed by a call to the NEXTVAL
function. The NEXTVAL function iterates the generator and returns the next value from
the sequence. The NEXTVAL function is an atomic operation and guarantees that no two
identical values will be returned regardless of the frequency or concurrency of
calls to the function. Below is a simple example;
SELECT
NEXTVAL('seq_1')
7. DROP SEQUENCE syntax:-
DROP
SEQUENCE name
Drops
a sequence generator previously created with the CREATE SEQUENCE
statement. A sequence generator may be changed by dropping the sequence and
then recreating it.
8. COMPACT TABLE syntax:-
COMPACT
TABLE table_name
Compacts
the table data file in the file system. This removes all unused space from the
table file and may rearrange the structure of the table to a form that better
fits the characteristics of the data being stored.
9.
CREATE SCHEMA syntax:-
CREATE SCHEMA
schema_name
Creates a schema
with the given name. By default a database has three schema initially defined, SYS_INFO, SYS_JDBC and APP. The SYS_INFO and SYS_JDBC schema contain
a number of important system tables and the APP schema is the default user schema.
The
following is an example of creating a new schema and changing to it;
CREATE
SCHEMA my_schema;
SET
SCHEMA my_schema;
DROP
SCHEMA schema_name
Drops
the schema with the given name. A schema may only be dropped if it contains no
tables. The SYS_INFO and APP schema may not be dropped.
INSERT
INTO table_name [ ( col_name1, col_name2, .... ) ]
VALUES ( expression1_1, expression1_2, ....
),
( expression2_1, expression2_2, ....
), ....
INSERT
INTO table_name [ ( col_name1, col_name2, .... ) ]
SELECT ...
INSERT
INTO table_name
SET col_name1 = expression1, col_name2 =
expression2, ....
This
is the SQL command to insert records into a table in the database. This
statement comes in three forms. The first inserts data from a VALUES
clause;
INSERT
INTO table ( col1, col2, col3 )
VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ),
( 11, (28 / 2) - 7, CONCAT(col1, 'c')
)
The
second form is used to copy information from a SELECT
query into the table specified in the INSERT
statement. For example;
INSERT
INTO table ( col1, col2, col3 )
SELECT id, num, description
FROM table2
WHERE description LIKE '11%'
The
third form uses a list of column SET
assignments. For example;
INSERT
INTO table
SET col1 = 10, col2 = 4 + 3, col3 =
CONCAT(col1, 'c')
If a
column of the table is not specified in an INSERT the
default value declared for the column is used. If no default value was declared
a NULL value is inserted in the column. If the column is declared as NOT
NULL the insert operation fails.
12.
DELETE syntax:-
DELETE
FROM table_name
[ WHERE expression ]
[ LIMIT limit_amount ]
Deletes
all the rows from the table that match the WHERE
clause. An optional LIMIT clause specifies the maximum number of matched rows to be
removed. An example of using the DELETE
statement;
DELETE
FROM table
WHERE col3 LIKE '11%' AND col1 < 1000
LIMIT 200
UPDATE
table_name
SET col_name1 = expression1, col_name2 =
expression2, ....
[ WHERE expression ]
[ LIMIT limit_amount ]
Updates
information in a table. The SET clause is a list of
assignments that describe how the columns of the data matched by the WHERE
clause are to be updated. Any columns not assigned in the SET
clause are left unchanged. Examples of using UPDATE;
UPDATE
Employee
SET salary = salary * 1.25
WHERE name = 'Bob'
UPDATE
Order
SET id = id + 3, part = CONCAT(part, '-00')
WHERE part LIKE 'PO-%'
LIMIT 10
SELECT
[ DISTINCT | ALL ]
column_expression1, column_expression2, ....
[ FROM from_clause ]
[ WHERE where_expression ]
[ GROUP BY expression1, expression2, .... ]
[ HAVING having_expression ]
[ ORDER BY order_column_expr1,
order_column_expr2, .... ]
column_expression ::= expression [ AS ] [ column_alias ]
from_clause ::= select_table1, select_table2, ...
from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON
expr ...
from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON
expr ...
from_clause ::= select_table1 [INNER] JOIN select_table2 ...
select_table ::= table_name [ AS ] [ table_alias ]
select_table ::= ( sub_select_statement
) [ AS ] [ table_alias ]
order_column_expr ::= expression [ ASC |
DESC ]
The SELECT
statement is used to form queries for extracting information out of the
database. The following example query will return the number, quantity and
price of all orders for more than 5 items sorted in descending order by order
number. In addition it rounds the order price to two decimal places and applies
a dollar ($) sign to the output.
SELECT number, quantity, CONCAT('$',
ROUND(price, 2))
FROM Order
WHERE quantity > 5
ORDER
BY number DESC
The ORDER
BY and GROUP BY clause may refer to a column, a column alias, or an expression.
The HAVING clause is evaluated after the grouping and aggregate columns have
been resolved.
For
examples of using SELECT with aggregate functions see the 'Internal SQL Functions'
section.
15. COMMIT and ROLLBACK syntax:-
COMMIT
ROLLBACK
Transactional
operations for closing a transaction and either committing all the changes made
or rolling back and disposing all changes. COMMIT may
cause a concurrent transaction conflict exception to be thrown. If a conflict
is detected the transaction is automatically rolled back. See the
'Transactions' section of the documentation for further details of how Mckoi
handles transactions.
16. CREATE USER, ALTER USER and DROP USER syntax:-
CREATE
USER username SET PASSWORD 'password'
[
SET GROUPS groups_list ]
[
SET ACCOUNT ( LOCK | UNLOCK ) ]
ALTER
USER username SET PASSWORD 'password'
[
SET GROUPS groups_list ]
[
SET ACCOUNT ( LOCK | UNLOCK ) ]
DROP
USER username
These
are user management commands for creating/altering and dropping users in the
system. Only members of the 'secure access' group are permitted to perform
these operations, which includes the administrator user that is setup when the
Mckoi database is created.
The
following example creates a user called 'harry' with the password 'cat';
CREATE
USER harry SET PASSWORD 'cat'
See
the 'JDBC Driver' section for documentation on connecting to a database using a
username and password to connect to a database.
17. GRANT/REVOKE syntax:-
GRANT
privileges ON database_object TO ( PUBLIC | user_list )
[
WITH GRANT OPTION ]
REVOKE
[ GRANT OPTION FOR ] privileges ON database_object
FROM
( PUBLIC | user_list )
privileges ::= priv_item1, priv_item2, ...
priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE |
DELETE |
REFERENCES | USAGE
database_object ::= [ TABLE ] table_name | SCHEMA schema_name
user_list ::= PUBLIC | username1, username2, ...
Grants
or revokes types of access on a table or view to a user. When a table or view
is created the system gives full grant options to the user that created the
object. The user is given the option to grant other users selective access to
the object through the GRANT and REVOKE syntax. For example, the follow statement shows how a user would
grant user 'toby' permission to SELECT
from a table called MyTable;
GRANT SELECT ON TABLE MyTable TO toby
The GRANT
command allows granting all users access to an object. The following statement makes
MyTable globally readable;
GRANT SELECT ON TABLE MyTable TO PUBLIC
If
you wish to give a user the option of granting a privilege to another user, add
WITH GRANT OPTION to the GRANT
statement.
SET
variable = expression
SET
AUTO COMMIT ( ON | OFF )
SET
TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )
SET
SCHEMA schema_name
Makes
a change to the state of the connection. SET AUTO COMMIT is
used to switch transaction 'auto commit mode' on or off. When auto commit mode
is on the engine commits after every statement. By default, a connection starts
with auto commit mode switched on. SET TRANSACTION ISOLATION
LEVEL currently only supports the SERIALIZABLE
isolation level. See the 'Transactions' section of the documentation for
details of how Mckoi handles transactions.
SET
SCHEMA is used to change the default schema of a connection.
DESCRIBE
table_name
This
command provides information about the columns of the table. It shows the
column names, the type / size and scale (if applicable) and other useful
information.
SHOW
engine_variable
engine_variable
::= TABLES | SCHEMA | STATUS | CONNECTIONS
Shows
internal information about the database system. SHOW TABLES
returns a list of tables in the database. SHOW STATUS
returns debugging and statistical information about the internal state of the
database engine. SHOW CONNECTIONS returns a snapshot of the current
connections on the database. SHOW SCHEMA
lists all the schema defined.
SHUTDOWN
Shuts
down the database. If the database is running as a server the database shuts
down cleanly and the process is stopped. If the database is embedded in a Java
application it is cleanly put into a shut down state. Only a user with the
correct g18-Apr-13rants may successfully execute this
command.
About
Tags
Popular
