8/17/2006 Dr. Wang, Cal State Fullerton 1
SQL-The Structural Query Language
1. CREATE DATABASE
CREATE DATABASE <dbname>;
GRANT ALL ON <dbname>.* TO <user>@localhost
IDENTIFIED BY <password>;
2. CREATE TABLE
CREATE TABLE <tablename>
( <column> <datatype> [<attribute constraint>]
{, <column> <datatype> [<attribute constraint>]}
[ <table constraint> {, <table constraint >}] );
8/17/2006 Dr. Wang, Cal State Fullerton 2
SQL-The Structural Query Language
Datatypes:
CHAR(<size>) (size < 255 bytes)
VARCHAR(<size>) (size < 255 bytes)
BLOB or TEXT (size < 65,535)
MEDIUMBLOB or MEDIUMTEXT (size < 16,777,215)
LONGBLOB or LONGTEXT (size < 4 GigaBytes)
ENUM(<value1>, <value2>,โฆ<valuen>)
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT are integers of 1, 2, 3,
4, and 8 bytes, respectively.
DECIMAL or NUMERIC(M, D)
FLOAT
DOUBLE PRECISION
DATE (default format YYYY-MM-DD,
e.g. โ1997-10-04โ)
8/17/2006 Dr. Wang, Cal State Fullerton 3
SQL-The Structural Query Language
Attribute constraints:
NOT NULL
UNIQUE
PRIMARY KEY
๎
DEFAULT <value>
Table constraints: [CONSTRAINT <name>]
PRIMARY KEY (<attribute> {, <attribute>})
FOREIGN KEY <attribute> REFERENCES <table>(<attribute>) [ON
DELETE SET NULL | ON DELETE CASCADE]
UNIQUE (<attribute> {, <attribute>})
