# mysql -v -v -v -u yourusername wt_client # mysqladmin create wt_client # mysqlshow wt_client # mysqldump -v wt_client # mysqlaccess wt_client # 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; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// select * from User_Table; ###update User_Table set email = 'x@x.x' where email = 'blah@blah.com'; ###delete from User_Table where user_id = ''; #######drop table User_Table; CREATE TABLE User_Table ( user_id int(10) DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, # starting at 142857 for some lame ass reason date timestamp(14), first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, address varchar(60) NOT NULL, city varchar(30) NOT NULL, state varchar(30) NOT NULL, zip varchar(20) NOT NULL, country varchar(30) NOT NULL, email varchar(60) NOT NULL, voice varchar(25) NOT NULL, password varchar(20) NOT NULL, beta tinyint(1) DEFAULT '1' NOT NULL, list_announce tinyint(1) DEFAULT '0' NOT NULL, list_beta tinyint(1) DEFAULT '0' NOT NULL, list_devel tinyint(1) DEFAULT '0' NOT NULL, list_dist tinyint(1) DEFAULT '0' NOT NULL, list_support tinyint(1) DEFAULT '0' NOT NULL, remote_addr varchar(15) NOT NULL, http_user_agent varchar(40) NOT NULL, hostbyaddr varchar(60) NOT NULL, partner_table_name varchar(20), developer tinyint(1) DEFAULT '0' NOT NULL, press tinyint(1) DEFAULT '0' NOT NULL, PRIMARY KEY (user_id), KEY date (date), KEY last_name (last_name), KEY email (email) ); # Daevid insert into User_Table values ( 142856, null, 'Daevid', 'Vincent', 'The Office', 'Redmond', 'WA', '98052', '', 'daevid@wildtangent.com', '(425)882-8963', 'C/69HxdlbhEhg', 1, 0,0,0,0,0, '209.20.180.167','Mozilla/4.6 [en] (Win98; U)', 'Your Mother' null,1,1 ); # Template format insert into User_Table values ( , null, 'first_name', 'last_name', 'address', 'city', 'state', 'zip', 'country', 'email', 'voice', 'password', beta, 'remote_addr','http_user_agent','hostbyaddr' partner_table_id, developer, press ); delete from Client_Table where client_id > 0; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// +-----------+---------------------------+---------------+---------------+----------------+------------+------------------+------------+-----+-----+--------------------------------+------------------------+-----------------+---------------+------------------------+------------------+--------------------------------+--------------+--------------+--------------+--------------+--------------+--------+--------------+-----------+---------------+--------------+-------------------+----------+ | player_id | guid | player_source | user_table_id | player_version | os | os_version | cpu | mhz | ram | videocard_name | videocard_manufacturer | videocard_chip | videocard_mem | soundcard_name | soundcard_driver | soundcard_version | dsound_dll | d3d_dll | ddraw_dll | dplay_dll | dinput_dll | jvm_ie | jvm_netscape | jvm_other | ie_ver | netscape_ver | other_browser_ver | date | +-----------+---------------------------+---------------+---------------+----------------+------------+------------------+------------+-----+-----+--------------------------------+------------------------+-----------------+---------------+------------------------+------------------+--------------------------------+--------------+--------------+--------------+--------------+--------------+--------+--------------+-----------+---------------+--------------+-------------------+----------+ | 1 | 9324-342nvg09w3f48-w40934 | BETA | 142856 | 1.69 | Windows 98 | 4.10 Build 1998 | AMD K6 MMX | 350 | 128 | RAGE PRO TURBO AGP 2X (English | ATI Tech. - Enh | Mach64: RagePro | 8 | TBS Montego Wave Audio | asp4wave.drv | 4.00.00.0000 4.05.112c Englis | 4.04.00.0068 | 4.06.00.0318 | 4.06.00.0318 | 4.06.00.0318 | 4.06.00.0318 | JVMIE | JVMNS | JVMOTHER | IE4.01-128bit | NS4.5-128bit | Lynx SSL | 19990408 | +-----------+---------------------------+---------------+---------------+----------------+------------+------------------+------------+-----+-----+--------------------------------+------------------------+-----------------+---------------+------------------------+------------------+--------------------------------+--------------+--------------+--------------+--------------+--------------+--------+--------------+-----------+---------------+--------------+-------------------+----------+ 1 row in set (0.00 sec) # guid is 128-bit/16 byte ########drop table Player_Table; ALTER TABLE Player_Table ADD column resolution CHAR(10) NOT NULL; ALTER TABLE Player_Table ADD column last_update TIMESTAMP(14) NOT NULL; ALTER TABLE Player_Table ADD column colormode TINYINT(2)NOT NULL; CREATE TABLE Player_Table ( guid CHAR(40) NOT NULL PRIMARY KEY, # hex version of GUID player_source VARCHAR(20) NOT NULL, user_table_id INT(10) NOT NULL, player_version VARCHAR(10) NOT NULL, os VARCHAR(30) NOT NULL, os_version VARCHAR(30) NOT NULL, cpu VARCHAR(40) NOT NULL, mhz VARCHAR(10) NOT NULL, ram VARCHAR(10) NOT NULL, videocard_name VARCHAR(50) NOT NULL, videocard_driver VARCHAR(50) NOT NULL, videocard_manufacturer VARCHAR(50) NOT NULL, videocard_chip VARCHAR(50) NOT NULL, videocard_mem VARCHAR(10) NOT NULL, soundcard_name VARCHAR(50) NOT NULL, soundcard_driver VARCHAR(50) NOT NULL, soundcard_version VARCHAR(50) NOT NULL, dsound_dll VARCHAR(30) NOT NULL, d3d_dll VARCHAR(30) NOT NULL, ddraw_dll VARCHAR(30) NOT NULL, dplay_dll VARCHAR(30) NOT NULL, dinput_dll VARCHAR(30) NOT NULL, jvm_ie VARCHAR(50) NOT NULL, jvm_netscape VARCHAR(50) NOT NULL, jvm_other VARCHAR(50) NOT NULL, ie_ver VARCHAR(50) NOT NULL, netscape_ver VARCHAR(50) NOT NULL, other_browser_ver VARCHAR(50) NOT NULL, remote_addr VARCHAR(15) NOT NULL, http_user_agent VARCHAR(40) NOT NULL, hostbyaddr VARCHAR(60) NOT NULL, date TIMESTAMP(14) NOT NULL, # date of insert or last update (YYYYMMDDHHMMSS) resolution CHAR(10) NOT NULL, last_update TIMESTAMP(14) NOT NULL, colormode TINYINT(2)NOT NULL, # 1,8,16,24,32 INDEX (user_table_id), INDEX (player_source), INDEX (player_version) ); insert into Player_Table values ( null, '9324-342nvg09w3f48-w40934', 'BETA', 142856, '1.69', 'Windows 98', '4.10 Build 1998', 'AMD K6 MMX', '350', '128', 'RAGE PRO TURBO AGP 2X (English)', '3.4.5', 'ATI Tech. - Enhanced', 'Mach64: RagePro','8', 'TBS Montego Wave Audio', 'asp4wave.drv', '4.00.00.0000 4.05.112c English Beta Retail', '4.04.00.0068', '4.06.00.0318', '4.06.00.0318', '4.06.00.0318', '4.06.00.0318', 'JVMIE','JVMNS','JVMOTHER', 'IE4.01-128bit','NS4.5-128bit','Lynx SSL', '209.20.180.167','Mozilla/4.51 [en] (Win98; U)', 'Your Mother', null ); # Template insert into Player_Table values ( null,'guid','player_source',user_table_id,'player_version', 'os','os_version','cpu','mhz','ram', 'videocard_name','videocard_manufacturer','videocard_chip','videocard_mem', 'soundcard_name','soundcard_driver','soundcard_version', 'dsound_dll','d3d_dll','ddraw_dll','dplay_dll','dinput_dll', 'jvm_ie','jvm_netscape','jvm_other', 'ie_ver','netscape_ver','other_browser_ver', 'remote_addr','http_user_agent','hostbyaddr', null ); ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// SELECT * FROM Test_Table INTO OUTFILE '/root/Test_Table.tsv' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'; SELECT * FROM SubTest_Table INTO OUTFILE '/root/SubTest_Table.tsv' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'; LOAD DATA INFILE '/root/Test_Table.tsv' INTO TABLE Test_Table FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'; LOAD DATA INFILE '/root/SubTest_Table.tsv' INTO TABLE SubTest_Table FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ##select * from Test_Table WHERE date < 19990428000000; ##delete from Test_Table WHERE date < 19990428000000; ALTER TABLE Test_Table CHANGE column description description VARCHAR(50) NOT NULL; ALTER TABLE Test_Table ADD column last_update TIMESTAMP(14) NOT NULL; ALTER TABLE Test_Table ADD column resolution_xy CHAR(10) NOT NULL; ALTER TABLE Test_Table DROP column resolution; ALTER TABLE Test_Table CHANGE column resolution_xy resolution VARCHAR(50) NOT NULL; CREATE TABLE Test_Table ( test_id INT(10) NOT NULL PRIMARY KEY, # large random number stored in cookie. guid CHAR(40) NOT NULL, # hex version of GUID player_version VARCHAR(10) NOT NULL, os VARCHAR(30) NOT NULL, os_version VARCHAR(30) NOT NULL, cpu VARCHAR(40) NOT NULL, mhz VARCHAR(10) NOT NULL, ram VARCHAR(10) NOT NULL, videocard_name VARCHAR(50) NOT NULL, videocard_driver VARCHAR(50) NOT NULL, videocard_manufacturer VARCHAR(50) NOT NULL, videocard_chip VARCHAR(50) NOT NULL, videocard_mem VARCHAR(10) NOT NULL, soundcard_name VARCHAR(50) NOT NULL, soundcard_driver VARCHAR(50) NOT NULL, soundcard_version VARCHAR(50) NOT NULL, dsound_dll VARCHAR(30) NOT NULL, d3d_dll VARCHAR(30) NOT NULL, ddraw_dll VARCHAR(30) NOT NULL, dplay_dll VARCHAR(30) NOT NULL, dinput_dll VARCHAR(30) NOT NULL, browser VARCHAR(50) NOT NULL, colormode TINYINT(2)NOT NULL, # 1,8,16,24,32 jvm_ie VARCHAR(50) NOT NULL, jvm_netscape VARCHAR(50) NOT NULL, jvm_other VARCHAR(50) NOT NULL, ie_ver VARCHAR(50) NOT NULL, netscape_ver VARCHAR(50) NOT NULL, other_browser_ver VARCHAR(50) NOT NULL, date TIMESTAMP(14) NOT NULL, # date of insert (YYYYMMDDHHMMSS) description VARCHAR(50) NOT NULL, last_update TIMESTAMP(14) NOT NULL, # date of last update (YYYYMMDDHHMMSS) resolution CHAR(10) NOT NULL, INDEX(date) ); ##select * from SubTest_Table WHERE date < 19990428000000; ##delete from SubTest_Table WHERE date < 19990428000000; ##### drop table SubTest_Table; CREATE TABLE SubTest_Table ( test_id INT(10) NOT NULL, subtest_number TINYINT(2) NOT NULL, date TIMESTAMP(14) NOT NULL, status TINYINT(1) NOT NULL, appearance TINYINT(1) NOT NULL, timing VARCHAR(20) NOT NULL, comments BLOB NOT NULL, INDEX (test_id, subtest_number) ); select * from SubTest_Table; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// insert into Test_Table values ( null, '9324-342nvg09w3f48-w40934', 'BETA', 142856, '1.69', 'Windows 98', '4.10 Build 1998', 'AMD K6 MMX', '350', '128', 'RAGE PRO TURBO AGP 2X (English)', '3.4.5', 'ATI Tech. - Enhanced', 'Mach64: RagePro','8', 'TBS Montego Wave Audio', 'asp4wave.drv', '4.00.00.0000 4.05.112c English Beta Retail', '4.04.00.0068', '4.06.00.0318', '4.06.00.0318', '4.06.00.0318', '4.06.00.0318', 'JVMIE','JVMNS','JVMOTHER', 'IE4.01-128bit','NS4.5-128bit','Lynx SSL', '209.20.180.167','Mozilla/4.51 [en] (Win98; U)', 'Your Mother', null ); # Template insert into Test_Table values ( null,'guid','player_source',user_table_id,'player_version', 'os','os_version','cpu','mhz','ram', 'videocard_name','videocard_manufacturer','videocard_chip','videocard_mem', 'soundcard_name','soundcard_driver','soundcard_version', 'dsound_dll','d3d_dll','ddraw_dll','dplay_dll','dinput_dll', 'jvm_ie','jvm_netscape','jvm_other', 'ie_ver','netscape_ver','other_browser_ver', 'remote_addr','http_user_agent','hostbyaddr', null ); ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// SELECT usertype_name, COUNT(*) FROM User_Table, UserType_Table WHERE usertype_table_id = usertype_id GROUP BY usertype_name; +--------------+------------------+ | partner_name | partner_password | +--------------+------------------+ | WildTangent | TheJez | | nVidia | RivaTNT | +--------------+------------------+ ###drop table Partner_Table; CREATE TABLE Partner_Table ( partner_name VARCHAR(20) NOT NULL PRIMARY KEY, partner_password VARCHAR(20) NOT NULL ); insert into Partner_Table values ('WildTangent','TheJez'); insert into Partner_Table values ('nVidia','RivaTNT'); ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 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%";