Web developers notes,programming tutorials.

SELECT table content to file in Mysql:

Using SELECT … INTO OUTFILE query,We can SELECT and writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. you should use a command such as mysql -e “SELECT …” > file_name to generate the file on the client host.

Syntax:
[SELECT query] INTO OUTFILE [file_name];

Example:

1. SELECT * from students where mark1>75 INTO OUTFILE “first_class.txt”;

2. mysql -u root -p -h 192.168.0.9 school -e “SELECT * from students where mark1 > 75″ > “first_class.txt”;

SET Data type:
A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them.

Example:

CREATE TABLE select_option(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
set_option SET(’Travel’,'Sports’,'Dancing’,'Fine Dining’)
);

INSERT INTO select_option(set_option) VALUES(’Travel,Sports’);
INSERT INTO select_option(set_option) VALUES(’Sports’);

SELECT * FROM select_option WHERE FIND_IN_SET(’Travel’,set_option) > 0;

Optimizer in Order by clauses:
The optimizer will skip the sort procedure for the ORDER BY clause if it sees that the rows will be in order anyway.

For the query:
SELECT * FROM Table1
WHERE column1 > ‘x’ AND column2 > ‘x’
ORDER BY column2;

if both column1 and column2 are indexed, the optimizer will choose an index on … column1. The fact that ordering takes place by column2 values does not affect the choice of driver in this case.