mp3files table MySQL examples

Table Of Contents

MySQL Command Line syntax examples

• A - Dump the mp3files table without create database entries
• B - The resulting sql file is less than 1 MB
• C - Transfer the file to the current machine
• D - Load the mp3files table into another database

MySQL man pages

• mysql - The MySQL command-line tool
• mysqldump - A database backup program from MySQL

MySQL syntax examples

• 00 - Show MySQL version and some other values
• 01 - Everything in the mp3files table
• 02 - Selected fields in the mp3files table orderd by track
• 03 - Summary of albums with title count from mp3files
• 04 - Summary of Albums from mp3files for a particular artist
• 05 - Tracks by Track number from mp3files for a particular album
• 06 - Summary of Albums for an artist using a subquery
• 07 - Show mp3files create table statement

The examples end with the create table statement used to create the mp3files table with multiple indexes.

MySQL Command Line syntax examples

Command Line Statement

mysqldump --user=<user> --no-create-db --add-drop-table \
--result-file=mp3files.sql lam mp3files

Back to Table Of Contents

Command Line Statement

$ ll -h /tmp/mp3files.sql
-rw-rw-r-- 1 murakami staff 862K 2013-04-17 22:44 /tmp/mp3files.sql

Back to Table Of Contents

Command Line Statement

cd /tmp

scp demeter.arsc.edu:/tmp/mp3files.sql .

Back to Table Of Contents

Command Line Statement

mysql -P 3306 -h dbprode.arsc.edu -u murakami cookbook -p  < /tmp/mp3files.sql

Back to Table Of Contents

MySQL syntax examples

SQL Statement

select version(), ' ', user(), ' ', current_date(), ' ', now(), ' ', 1 + 1, ' ', pi();

1 of 1 rows displayed.

SQL Statement

select * from mp3files;

First 20 of 4331 rows displayed.

SQL Statement

select ARTIST, ALBUM, TITLE, TRACK from mp3files
order by ARTIST, ALBUM, TRACK;

First 20 of 4331 rows displayed.

SQL Statement

select ARTIST, ALBUM, count(TITLE) from mp3files
 group by ARTIST, ALBUM order by ARTIST, ALBUM;

First 20 of 451 rows displayed.

SQL Statement

select ARTIST, ALBUM, count(TITLE) from mp3files
 where ARTIST = 'Bad Company'
 group by ARTIST, ALBUM order by ARTIST, ALBUM;

8 of 8 rows displayed.

SQL Statement

select ARTIST, ALBUM, TRACK, TITLE from mp3files
 where ARTIST = 'Bad Company'
 and ALBUM = 'Here Comes Trouble'
 order by TRACK;

11 of 11 rows displayed.

SQL Statement

select ARTIST, count(ALBUM) as Albums from
  (select ARTIST, ALBUM from mp3files
  group by ARTIST, ALBUM order by ARTIST, ALBUM)
  as artist_album
 group by ARTIST;

199 of 199 rows displayed.

SQL Statement

show create table mp3files;

CREATE TABLE `mp3files` (
  `ARTIST` varchar(64) DEFAULT NULL,
  `TITLE` varchar(64) DEFAULT NULL,
  `ALBUM` varchar(64) DEFAULT NULL,
  `TRACK` varchar(8) DEFAULT NULL,
  `YEAR` varchar(4) DEFAULT NULL,
  `GENRE` varchar(32) DEFAULT NULL,
  `FILE` varchar(255) DEFAULT NULL,
  `SIZE` int(10) unsigned DEFAULT NULL,
  `DATE_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TIME_STAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `mp3files_by_ARTIST_ALBUM` (`ARTIST`,`ALBUM`,`TITLE`,`TRACK`),
  KEY `mp3files_by_ARTIST_TITLE` (`ARTIST`,`TITLE`),
  KEY `mp3files_by_DATE_TIME` (`DATE_TIME`,`ARTIST`,`ALBUM`,`TITLE`,`TRACK`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 of 1 rows displayed.

Sunday, April 28, 2024 @ 7:14:05 PM
z.ServerAdmin@lam1.us