Difference between revisions of "MySQL/scripts"

Revision as of 21:28, 11 February 2012

see: main MySQL article.

• Find duplicate entries

SELECT accession,count(*) AS n
FROM mydb
WHERE kingdom!='Bacteria'
GROUP BY accession HAVING n>2;

• Padding integers

Say you have a series of integers for your id column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "1" -> "001"), then:

SELECT RIGHT(1000 + id,3) AS id FROM mydb;

Relational algebra (RA)

Note: Taken from the MIT course in Database Management Systems.

Consider the following relations and query:

student(_sname_,major,year)
class(_cnum_,building)
enrolled(_sname_,_cnum_,_time_)
  

QUESTION: "The majors of students who take at least one class in the physics building"

which could be written in SQL as:

SELECT DISTINCT major
FROM student S,class C,enrolled E
WHERE S.sname = E.sname
  AND E.cnum = C.cnum
  AND C.building = 'physics'
  

The RA could be written as:

PI_{major}( SIGMA_{building='physics'}((student JOIN
  enrolled) JOIN class)
  (where _{x} means subscripted x)
  

HW: Write three relational algebra trees showing different ways of evaluating this query (you do not need to annotate your diagram with choices of implementation for each operation, i.e. just draw the trees, not full evaluation plans).

Amarok in MySQL

mysql -u root -p
CREATE DATABASE amarok; USE mysql;
GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME';
FLUSH PRIVILEGES;

In Amarok, go to settings -> configure Amarok, click on Collection, and select MySQL. Then, use the following settings:

Hostname: 127.0.0.1
Database: amarok
Port: 3306
Username: amarok
Password: Your Password