Put your tips and tricks online - Share your knowledge! Login | Register
  Search     Advanced search

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 Found 9 tips, Displaying 1 - 9 
Sort by 
 How to convert query resutls to XML
You can display SQL query results in XML format when running mysql from command line: $ c:/mysql/bin/mysql -u [user_name] -p [database_name] -X -e "SELECT * FROM forums_phpbb_config" Enter password: ****** config_id ...
 How to print SQL query results vertically
In MySQL you can display the query results vertically, so that each column will be shown on a new line with \G switch. Fox example: mysql> select * from nuke_phpbb_config \G *************************** 1. row *************************** config_name: config_id config_value: 1 *************************** 2. row *************************** config_name: board_disable config_value: 0 *************************** 3. row *************************** config_name: sitename config_value: yourdomain.com *************************** 4. row ...
 How to check the version of MySQL server
To find out the version of MySQL server using command line on localhost run the following: mysql -V To check the version on host "myhost" run: mysql -h myhost -V You can also see the server version in phpMyAdmin, it will be displayed on the front page.
 How to delete a column
To delete a column from a MySQL table run the following command: mysql> ALTER TABLE 'table_name' DROP 'col_name';
 How to save query results in a text file
Here's how to put MySQL query results in an ASCII file i.e. tab delimited or CSV file. mysql> SELECT field1, field2 FROM tab1 INTO OUTFILE 'logfile.txt'; By default fields will be separated by tab, but you can change it using the following command: mysql> SELECT field1, field2 FROM tab1 ...
 How to rename MySQL table
It's quite easy to change a name of MySQL table and the syntax is similar to that of other databases. Of course you'd have to make sure the table name is not hardcoded somewhere i.e. in a php script. Just run this command: mysql> RENAME TABLE tab_a TO tab_b;

 Find duplicate records query
Use the following SQL statement for finding duplicates in a table. I think this should work for any relational database, not only MySQL. SELECT col_name, COUNT(col_name) AS num FROM table_name GROUP BY col_name HAVING (COUNT(col_name) > 1);

 How to select random record from database table
To select random row from a MySQL table use the following query: mysql> SELECT col1, col2 FROM table_name ORDER BY rand() LIMIT 1;
 How to purge MySQL binary logs
You can put this command in crontab to run at regular intervals. This will purge database binary logs. $ designates shell prompt, replace passwd with the actual password. $ /bin/echo 'reset master' | /usr/local/mysql/bin/mysql --password=passwd

Recent tips & tricks
Operating Systems > Linux : How to set idle session timeou...
Databases > Oracle > Performance Tuning : Copy SQL plan baseline into an...
Databases > Oracle > Performance Tuning : Display TX blocking locks
Databases > Oracle > ASM : Handy ASM commands in SQLPLUS

More categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com