It seems there are two camps when it comes to SQL and how to do database optimizations - the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.
SQL is a generic database langauge . There are a few different standards in use (the language has progressed over time), but the core of the SQL language is pretty much the standard in most databases.
Through the mysql client: update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME'; flush privileges;
Through the command line:
mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD
Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.
Finding records that do not match between two tables.
CREATE TABLE bookreport ( b\_id int(11) NOT NULL auto\_increment, s\_id int(11) NOT NULL, report varchar(50), PRIMARY KEY (b\_id) ); CREATE TABLE student ( s\_id int(11) NOT NULL auto\_increment, name varchar(15), PRIMARY KEY (s\_id) ); insert into student (name) values ('bob'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'A Death in the Family'); insert into student (name) values ('sue'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'Go Tell It On the Mountain'); insert into student (name) values ('doug'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'The Red Badge of Courage'); insert into student (name) values ('tom'); To find the sudents where are missing reports: select s.
Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.
mysql -X -e "select \* from exams" test
To dump data into a comma separated file use this:
SELECT * INTO OUTFILE 'tablename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM tablename; Replace tablename with the tablename of the table you which to dump to a file.