Difference between revisions of "MySQL/scripts"
From Christoph's Personal Wiki
(6 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | see: main [[MySQL]] article. | ||
+ | |||
*Find duplicate entries | *Find duplicate entries | ||
SELECT accession,count(*) AS n | SELECT accession,count(*) AS n | ||
Line 5: | Line 7: | ||
GROUP BY accession HAVING n>2; | GROUP BY accession HAVING n>2; | ||
− | *Padding integers | + | *Padding integers: Say you have a series of integers for your <code>id</code> column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "<code>1</code>" -> "<code>001</code>"), then: |
− | Say you have a series of integers for your <code>id</code> column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "<code>1</code>" -> "<code>001</code>"), then: | + | |
SELECT RIGHT(1000 + id,3) AS id FROM mydb; | SELECT RIGHT(1000 + id,3) AS id FROM mydb; | ||
+ | |||
+ | *Show current timestamp: | ||
+ | SELECT NOW(); | ||
+ | |||
+ | *Execute a query from the CLI: | ||
+ | mysql -u username -p password database --execute="SELECT * FROM table" | ||
+ | |||
+ | ==Relational algebra (RA)== | ||
+ | ''Note: Taken from the MIT course in [http://www.aduni.org/courses/databases/index.php?view=cw 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 <code>settings -> configure Amarok</code>, 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 |
Latest revision as of 05:23, 13 February 2013
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;
- Show current timestamp:
SELECT NOW();
- Execute a query from the CLI:
mysql -u username -p password database --execute="SELECT * FROM table"
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