CREATE TABLE name field_name field_type, [field2 type2, ...]
CREATE SEQUENCE ON table [STEP value] [VALUE value]
CREATE INDEX name ON table ( column, ... )
Creates new database elements (or entirely new databases). This
statement is used to create tables, indices, and sequences.
The CREATE SEQUENCE
statement adds a sequence to a
table. A sequence is simply a value associated with a table that the
mSQL server keeps track of. Most commonly, a sequence is used to
generate unique identification numbers for tables. The value of the
sequence is incremented every time the sequence value is read. The
STEP modifier determines how much the sequence
value is increased each time. The VALUE modifier
gives the initial value of the sequence.
The CREATE INDEX
statement defines an index for the
table. The mSQL system supports indexes that contain more than one
field. You must provide a name for the index, although it need not be
meaningful because it is rarely needed by the end user.
The CREATE TABLE
statement defines the structure of a
table within the database. This statement is how all mSQL tables are
created. The syntax of the create definition is the name of a field
followed by the type of the field, followed by any modifiers (e.g.,
name char(30) not null). The following
are supported by mSQL:
Fixed length character value. No values can be greater than the given
Standard date type.
Standard 4-byte integer. Range is -2147483646 to 2147483647.
Monetary type suitable for accurately storing money values. This type
allows for storing decimal values (like 19.99) without the
imprecision of using a floating point type.
Standard 8-byte floating point value. Minimum nonzero values are +/-
4.94E-324 and maximum are +/- 1.79E+308.
Variable length character value. The given length is the maximum
value for most of the data, but longer data can be entered.
Standard time type.
Standard 4-byte unsigned integer. Range is 0 to 4294967295.
In addition to the main types, several modifiers can also be used to
qualify the type:
This value is the maximum length of a character type. For
CHAR, this is the absolute maximum. For
TEXT this is only an approximate maximum that
should apply to most of the data. Longer data can be inserted into a
TEXT field, but it will make the table slower.
- NOT NULL
Specifies that the field cannot contain a null value. Attempting to
insert a null value into such a field will result in an error.
CREATE TABLE emp_data ( id INT, name CHAR(50) )
# Add a sequence to the table 'checks' with the initial value '1000' and the
# default step of 1.
CREATE SEQUENCE ON checks VALUE 1000
# Create an index on the table 'music' that covers the fields 'artist',
# 'publisher', and 'title'.
CREATE INDEX idx1 ON music ( artist, publisher, title )
DELETE FROM table [WHERE clause]
Deletes rows from a table. When used without a
WHERE clause, this will erase the entire table and
recreate it as an empty table. With a clause, it will delete the rows
that match the condition of the clause.
# Erase all of the data (but not the table itself) for the table 'olddata'.
DELETE FROM olddata
# Erase all records in the 'sales' table where the 'year' field is '1995'.
DELETE FROM sales WHERE year=1995
DROP INDEX name
DROP TABLE name
DROP SEQUENCE FROM table
Permanently remove a table, index, or sequence from the mSQL system.
DROP is by far the most dangerous SQL statement.
If you have drop privileges, you may permanently erase a table or
even an entire database. This is done without warning or
confirmation. The only way to undo a DROP is to
restore the table or database from backups. The lessons to be learned
here are (1) always keep backups and (2) don't use
DROP unless you are really sure.
# Delete the tables 'oh_no'
DROP TABLE oh_no
# Remove the named index 'my_index'
DROP INDEX my_index
# Erase the sequence defined on the table 'counter'. Another sequence can be
# recreated at any time using the 'CREATE SEQUENCE' statement.
DROP SEQUENCE FROM counter
INSERT INTO table [ (column, ...) ] VALUES ( values )
Inserts data into a table. This statement inserts the given values
into the given columns. Columns in the table that are not given
values are set to NULL. If you leave out the list
of columns, the number of value given must exactly match the number
of columns in the table.
# Insert a record into the 'people' table.
INSERT INTO people ( name, rank, serial_number ) VALUES ( 'Bob Smith',
'Captain', 12345 )
SELECT [DISTINCT] columns FROM table [clause]
Retrieves data from a database. The SELECT
statement is the primary method of reading data from database tables.
If you specify more than one table, mSQL will automatically join the
tables so that you can compare values between the tables.
If the DISTINCT keyword is present, only one row
of data will be output for every group of rows that is identical.
The column names can be specified as column or
table.column. The longer form is necessary only to
disambiguate columns with the same name, but can be used at any time
(e.g., SELECT name FROM people; SELECT people.name FROM
The list of tables to join are specified as Table1, Table2,
Table3, ... The tables are joined in the manner that mSQL
deems most efficient. Table names can also be aliased (e.g.,
SELECT t1.name, t2.address FROM long_table_name=t1,
If no clause is provided, SELECT returns all of
the data in the selected table(s).
The search clause can contain any of the following substatements:
The WHERE statement construct is the most common
way of searching for data in SQL. The statement is a comparison of
two or more values. Named values (such as column names and aliases)
and literal numbers and strings can be used in the statement. The
following operators are supported:
Performs a logical AND (returns 0 if any of the arguments are 0, otherwise returns 1)
Performs a logical OR (returns 1 if any of the arguments are not 0, otherwise returns 0)
Parentheses are used to group operators in order to force precedence.
Returns 1 if the two values are equal, otherwise returns 0. MySQL automatically converts between types when comparing values.
Returns 1 if the two values are not equal, otherwise returns 0.
Returns 1 if the left value is less than or equal to the right value, otherwise returns 0.
Returns 1 if the left value is less than the right value, otherwise returns 0.
Returns 1 if the left value is greater than or equal to the right value, otherwise returns 0.
Returns 1 if the left value is greater than the right value, otherwise returns 0.
- ORDER BY column [DESC][, column2 [DESC],...]
Sorts the returned data using the given column(s). If
DESC is present, the data is sorted in descending
order, otherwise ascending order is used (e.g., SELECT name,
age FROM people ORDER BY age DESC).
- value1 LIKE value2
Compares value1 to
value2 and returns 1 if they match and
otherwise. The right-hand value can contain the wildcard
"%" which matches any number of characters (including 0)
and `_' which matches exactly one character. This is
probably the single most used comparison in SQL. The most common
usage is to compare a field value with a literal containing a
wildcard (e.g., SELECT name FROM people WHERE name LIKE
- value1 RLIKE value2
Compares value1 to
value2 using the extended regular
expression syntax and returns 1 if they match and
otherwise. The right hand value can contain full Unix regular
expression wildcards and constructs (e.g., SELECT name FROM
people WHERE name RLIKE
- value1 CLIKE value2
a case insensitive version of the LIKE operator
(e.g., SELECT name FROM people WHERE name CLIKE
The WHERE clause returns any of the expression
values that are not
or NULL. Therefore, SELECT age FROM
people WHERE age>10 will return only those ages that are
greater than 10.
# Find all names in the 'people' table where the 'state' field is 'MI'.
SELECT name FROM people WHERE state='MI'
# Display all of the data in the 'mytable' table.
SELECT * FROM mytable
UPDATE table SET column=value, ... [WHERE clause]
Alters data within a table. This statement is used to change actual
data within a table without altering the table itself.
# Change the name 'John Deo' to 'John Doe' everywhere in the people table.
UPDATE people SET name='John Doe' WHERE name='John Deo'