# mysql -v -v -v -u yourusername wt_api # mysqladmin create wt_api # mysqlshow wt_api # mysqldump -v wt_api # mysqlaccess wt_api # INT = 4 Byte = FFFFFFFF = 4294967295 # MEDIUMINT = 3 Byte = FFFFFF = 16777215 # SMALLINT = 2 Byte = FFFF = 65535 # TINYINT = 1 Byte = FF = 255 # all _id fields are numbers in tables # names ARE case sensitive! # show tables; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// +-------------+-----------------+---------------+--------------------------------------------------------------------+-------------------------------------------------+--------------------------------------------------------------------------------+ | function_id | object_table_id | function_name | function_summary | function_format | function_description | +-------------+-----------------+---------------+--------------------------------------------------------------------+-------------------------------------------------+--------------------------------------------------------------------------------+ | 1 | 1 | myFunction | This is my function, there are many like it, but this one is mine. | boolean myFunction(int x, char s, boolean true) | Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda. | +-------------+-----------------+---------------+--------------------------------------------------------------------+-------------------------------------------------+--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) select * from Function_Table; CREATE TABLE Function_Table ( function_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, function_name CHAR(30) NOT NULL, function_summary VARCHAR(255) NOT NULL, function_format VARCHAR(255) NOT NULL, function_description BLOB NOT NULL, function_date TIMESTAMP(8) NOT NULL, INDEX (function_name) ); ##update Function_Table set function_name = 'run' WHERE function_name like 'run()'; ##delete from Function_Table where function_id = 10; insert into Function_Table values ( null, 'myFunction', 'This is my function, there are many like it, but this one is mine.', 'boolean myFunction(int x, char s, boolean true)', 'Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda yadda Yadda.', null ); ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// +-----------+-----------------+ | object_id | object_name | +-----------+-----------------+ | 1 | WT | | 2 | WTCamera | | 3 | WTGroup | | 4 | WTBitmap | | 5 | WTDrop | | 6 | WTModel | | 7 | WTLight | | 8 | WTContainer | | 9 | WTStage | | 10 | WTObject | | 11 | WTAudioClip | | 12 | WTEvent | | 13 | WTVector3D | | 14 | WTOrientation3D | +-----------+-----------------+ 14 rows in set (0.00 sec) CREATE TABLE Object_Table ( object_id TINYINT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY, object_name VARCHAR(50) NOT NULL ); insert into Object_Table values (null,'WT'); insert into Object_Table values (null,'WTCamera'); insert into Object_Table values (null,'WTGroup'); insert into Object_Table values (null,'WTBitmap'); insert into Object_Table values (null,'WTDrop'); insert into Object_Table values (null,'WTModel'); insert into Object_Table values (null,'WTLight'); insert into Object_Table values (null,'WTVisualObject'); insert into Object_Table values (null,'WTStage'); insert into Object_Table values (null,'WTObject'); insert into Object_Table values (null,'WTAudioClip'); insert into Object_Table values (null,'WTEvent'); insert into Object_Table values (null,'WTVector3D'); insert into Object_Table values (null,'WTOrientation3D'); update Object_Table set object_name = 'WTEvent' where object_id = 12; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// CREATE TABLE AppliesTo_Table ( function_table_id SMALLINT(5) NOT NULL, object_table_id TINYINT(2) NOT NULL, INDEX (function_table_id), INDEX (object_table_id), INDEX (function_table_id, object_table_id) ); insert into AppliesTo_Table values (1,2); ##select * from AppliesTo_Table where function_table_id = 10; ##delete from AppliesTo_Table where function_table_id = 10; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// +------------+-------------------+--------------------------------+--------------------------------------+----------------------------------------------------------------------------------+ | example_id | function_table_id | example_name | example_code | example_description | +------------+-------------------+--------------------------------+--------------------------------------+----------------------------------------------------------------------------------+ | 1 | 1 | A better Mousetrap using myFun | if myFunction(2,3,4) then { y = 4; } | Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah. | +------------+-------------------+--------------------------------+--------------------------------------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) CREATE TABLE Example_Table ( example_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, function_table_id SMALLINT(5) NOT NULL, example_name CHAR(50) NOT NULL, example_code TINYBLOB NOT NULL, example_description BLOB NOT NULL, INDEX (example_name), INDEX (function_table_id) ); insert into Example_Table values ( null, 1, 'A better Mousetrap using myFunction', 'if myFunction(2,3,4) then { y = 4; }', 'Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah.' ); #delete from Example_Table where function_table_id = 10; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// select * from Client_Table; select artist_name, cd_title from Client_Table, Artist_Table where Client_Table.cd_artist_id = Artist_Table.artist_id; select ucase(artist_name), ucase('hello') from Artist_Table; SELECT cd_upc,category_name,artist_name,cd_title,cd_price,label_name FROM Category_Table,Artist_Table,Client_Table,Label_Table WHERE Client_Table.cd_artist_id = Artist_Table.artist_id and Client_Table.cd_label_id = Label_Table.label_id and Client_Table.cd_category_id = Category_Table.category_id and Client_Table.cd_label_id = 7; delete from Category_Table where category_id = 9; insert into Client_Table values (0, 'cd_title',cd_artist_id,'cd_label',cd_label_id,'cd_upc', cd_status_id,cd_category_id,cd_price, null,cd_code,cd_desc,cd_image ); update Client_Table set cd_image = 'witherblisterburnpeel.jpg' where upc = '28'; update Client_Table set date = null where date = 000000; ALTER TABLE Client_Table CHANGE column cd_price cd_price FLOAT(6,2) NOT NULL; ALTER TABLE Client_Table ADD column cd_image VARCHAR(50); ALTER TABLE Client_Table ADD INDEX index_title (cd_title); SELECT upc,cd_title,artist_name,cd_price,cd_frizbcode,category_name,date,cd_status_id,status_id,status_name FROM Artist_Table,Client_Table,Category_Table,Label_Table,Status_Table WHERE artist_id = cd_artist_id AND category_id = cd_category_id AND cd_status_id = status_id AND label_id = cd_label_id ORDER BY date DESC,artist_name,cd_title LIMIT 1,10; select concat(substring(date,1,2),"/",substring(date,3,2),"/",substring(date,5,2)) from Client_Table; drop table Client_Table; LOAD DATA INFILE '/home/frizb/HTML/private/cris.tsv' REPLACE INTO TABLE Client_Table FIELDS OPTIONALLY ENCLOSED BY '"'; select * from Client_Table where artist_title like "front%"; ALTER TABLE Client_Table DROP column pin; ALTER TABLE Client_Table DROP column category; select * from Client_Table where artist_title like "front%";