Tuesday, September 16, 2008

MySQL deeper!

Description CommandTo login (from unix shell) use -h only if needed. [mysql dir]/bin/mysql -h hostname -u root -pCreate a database on the sql server. create database [databasename];List all databases on the sql server. show databases;Switch to a database. use [db name];To see all the tables in the db. show tables;To see database's field formats. describe [table name];To delete a db. drop database [database name];To delete a table. drop table [table name];Show all data in a table. SELECT * FROM [table name];Returns the columns and column information pertaining to the designated table. show columns from [table name];Show certain selected rows with the value "whatever". SELECT * FROM [table name] WHERE [field name] = "whatever";Show all records containing the name "Bob" AND the phone number '3444444'. SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field. SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;Show all records starting with the letters 'bob' AND the phone number '3444444'. SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a. SELECT * FROM [table name] WHERE rec RLIKE "^a$";Show unique records. SELECT DISTINCT [column name] FROM [table name];Show selected records sorted in an ascending (asc) or descending (desc). SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;Return number of rows. SELECT COUNT(*) FROM [table name];Sum column. SELECT SUM(*) FROM [table name];Join tables on common columns. select lookup.illustrationid, lookup.personid,person.birthday from lookupleft join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;Switch to the mysql db. Create a new user. INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));Change a users password.(from unix shell). [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'Change a users password.(from MySQL prompt). SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');Allow the user "bob" to connect to the server from localhost using the password "passwd" grant usage on *.* to bob@localhost identified by 'passwd';Switch to mysql db.Give user privilages for a db.

Perl API - using functions and methods built into the Perl DBI with MySQL

available_driversbegin_workbind_colbind_columnsbind_parambind_param_arraybind_param_inoutcanclonecolumn_infocommitconnectconnect_cacheddata_sourcesdisconnectdodump_resultserrerrstrexecuteexecute_arrayexecute_for_fetchfetchfetchall_arrayreffetchall_hashreffetchrow_arrayfetchrow_arrayreffetchrow_hashreffinishforeign_key_infofuncget_infoinstalled_versionslast_insert_idlooks_like_numberneatneat_listparse_dsnparse_trace_flagparse_trace_flagspingprepareprepare_cachedprimary_keyprimary_key_infoquotequote_identifierrollbackrowsselectall_arrayrefselectall_hashrefselectcol_arrayrefselectrow_arrayselectrow_arrayrefselectrow_hashrefset_errstatetable_infotable_info_alltablestracetrace_msgtype_infotype_info_allAttributes for Handles