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.
Loading Data into Tables from Text Files.
Assume you have the following table.
CREATE TABLE loadtest ( pkey int(11) NOT NULL auto\_increment, name varchar(20), exam int, score int, timeEnter timestamp(14), PRIMARY KEY (pkey) ); And you have the following formatted text file as shown below with the unix “tail” command:
$ tail /tmp/out.txt 'name22999990',2,94 'name22999991',3,93 'name22999992',0,91 'name22999993',1,93 'name22999994',2,90 'name22999995',3,93 'name22999996',0,93 'name22999997',1,89 'name22999998',2,85 'name22999999',3,88 NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not present in the "/tmp/out.
Getting a random roll of the dice:
CREATE TABLE dice ( d\_id int(11) NOT NULL auto\_increment, roll int, PRIMARY KEY (d\_id) ); insert into dice (roll) values (1); insert into dice (roll) values (2); insert into dice (roll) values (3); insert into dice (roll) values (4); insert into dice (roll) values (5); insert into dice (roll) values (6); select roll from dice order by rand() limit 1;