SQL is quite an old language and the convention is to write keywords in block capitals (reminds me of FORTRAN!). Command sequences are terminated by a semi-colon.
To start, this sequence creates a database, sets it as active and then adds a table containing a range of fields of different types, the command DESC shows the layout of a table:
CREATE DATABASE my_database;
USE my_database
CREATE TABLE contacts
(
contact_id INT NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(20),
birthday DATE,
life_story BLOB,
weight DEC(3, 2) NOT NULL DEFAULT 80.00,
state_code CHAR(2),
appointment DATETIME
);
Desc contacts;
NOT NULL keywords are used if a field must be specified on INSERT. Once created data can be added to the table using the INSERT command:
INSERT INTO contacts (contact_id,first_name,last_name,birthday,life_story,weight,appointment)
VALUES
(1,'Ian','Hopkinson','1970-24-04','A very long text string',80.0,
'10:30am 2010-21-06');
INSERT can be used with no specified fields (in which case values for all supplied fields must be provided for all fields), or with a subset of fields. In order to add the ' character we can either use '' or \' (where '' is two single quote characters, rather than a double quote).
To delete a table:
DROP TABLE contacts;
This command should be used with care since it deletes the table whether or not it contains data. The next post should be on the SELECT command. Of course you can find SQL cheatsheets elsewhere.
Keywords: CREATE, DATABASE, TABLE, USE, DESC, DROP, INSERT INTO, VALUES, INT, VARCHAR, BLOB, CHAR, DATETIME, DATE, DEC
No comments:
Post a Comment