mSQL SQL (MySQL & mSQL)

15.2. mSQL SQL

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 datatypes are supported by mSQL:

CHAR( length): Fixed length character value. No values can be greater than the given length.
DATE: Standard date type.
INT: Standard 4-byte integer. Range is -2147483646 to 2147483647.
MONEY: 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.
REAL: Standard 8-byte floating point value. Minimum nonzero values are +/- 4.94E-324 and maximum are +/- 1.79E+308.
TEXT( length): Variable length character value. The given length is the maximum value for most of the data, but longer data can be entered.
TIME: Standard time type.
UINT: 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:

length: 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.

Examples

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

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.

Examples

# 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

DROP INDEX name
DROP TABLE name
DROP SEQUENCE FROM table

Permanently remove a table, index, or sequence from the mSQL system.

WARNING

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.

Examples

# 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

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.

Examples

# Insert a record into the 'people' table.
INSERT INTO people ( name, rank, serial_number ) VALUES ( 'Bob Smith',
    'Captain', 12345 )

SELECT

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 people).

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, longer_table_name=t2).

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:

WHERE statement

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:

AND: Performs a logical AND (returns 0 if any of the arguments are 0, otherwise returns 1)
OR: 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'B%').

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'^B.*').

value1 CLIKE value2

Comparesvalue1tovalue2 using a case insensitive version of the LIKE operator (e.g.,SELECT name FROM people WHERE name CLIKE'b%').

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.

Examples

# 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

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.

Example

# Change the name 'John Deo' to 'John Doe' everywhere in the people table.
UPDATE people SET name='John Doe' WHERE name='John Deo'