Do you ever find yourself wondering what it would have been like to live through the dotcom craze, circa 1996? More specifically, a New York based publishing-cum-media company with grandiose aspirations? Probably not, but still the fact that an artifact like Burn Rate exists is interesting in and of itself. Burn Rate is the story of a company that I’d never heard of, written by the founder himself, whom I’d never heard of. Which means the book could only end badly. But for that reason alone it was entirely fascinating, watching all the wrong turns and moments of personal hubris lead towards greater and greater humiliations.
It was a fun read because I knew how it was going to turn out. Well, I know how things are now. When the author mused on whether the future would go this way or that, I knew exactly which way it went. When he nailed the future on the head, he sounded completely prescient, but when he was doubtful, he ended up parroting the recycled fears of the traditional media world—that we still hear to this day. Still, I really enjoyed reading one history of what led to where we are now, even though this book mostly encompassed the wrong turns, dead ends, and “corrections”.
In some ways it read like a work of historical fiction. I knew all the major companies at the time, like AOL, Excite, CNet, Yahoo, and Microsoft, and yet the names of all the players seemed imagined. I found myself wondering, what ever happened to Michael Wolff, the author, and his company Wolff New Media? A quick Google search told me that he’s involved in yet another online venture I’d never heard of (Newser) that sounded like wistful throwback to the days of traditional media. And in somewhat sadder news, I found his recent affair and divorce plastered all over Gawker. Telling.
I flipped through the MySQL Cookbook and jotted down anything I didn’t know and thought might be useful. There was a lot of stuff I didn’t know and thought was probably useless, especially if you do most of your data munging in a programming language outside of pure SQL. But there was still some pretty cool stuff in there. Here are five things that stood out to me.
Apparently these came to MySQL in version 4.1 (from the land of Oracle). I’m not quite sure what I’d really use it for, since I rarely alias column names, but it’s neat to know you can substitute a from clause with a whole query. Note the strange requirement that you have to alias the subquery, hence the seemingly out of place “x”:
select *
from (select sal as salary, comm as commission from emp) x
where salary < 5000;
CASE expression
How have I not stumbled upon this before? I’ve always just used the if() function. I guess this is SQL-proper way of doing the same. MySQL’s docs on using case are here.
select ename,
sal,
case
when sal <= 2000 then 'underpaid'
when sal >= 4000 then 'overpaid'
else 'ok'
end as status
from emp;
Create table as select
This looks super super handy. The first query just copies the table’s structure, the second copies structure and data.
create table dept_2 as select * from dept where 1=0;
create table dept_2 as select * from dept;
Deleting duplicate records
Clever way of using a subquery with a group by to delete duplicate records. I like this, but it has the problem of not being immediately transparent about what’s being accomplished.
delete from dupes
where id not in (select min(id) from dupes group by name)
group_concat()
This MySQL-specific function has to be seen to be believed. It allows you to take your normalized one-to-many relationships and string them back together together in a comma-separated list. MySQL’s docs on using group_concat() are here.
select deptno,
group_concat(ename order by empno separator ',') as emps
from emp
group by deptno;
So I’m reading through the introduction of O’Reilly’s SQL Cookbook and I get to this part that says:
The majority of the examples in this book involve the use of two tables, EMP and DEPT. The EMP table is a simple 14-row table with only numeric, string, and date fields. The DEPT table is a simple four-row table with only numeric and string fields. These tables appear in many old database texts, and the many-to-one relationship between departments and employees is well understood.
I love it when I stumble upon historical gems like this. So I went searching for a quick dump of the SQL to create and populate these sample tables, and I couldn’t find any. Well, that’s not entirely true. I did stumble upon a few variations, but it appears that the canonical version comes to us courtesy of Oracle. Of course I was looking for something a little more MySQL-friendly.
So I ran the Oracle SQL through MySQL with a few changes: I changed the HIREDATE datetime column to a date column, and I changed the format of the date in the insert statements from dd-mon-yyyy to yyyy-mm-dd. Then I mysqldump-ed out the SQL to get the version you see below. Just for my own sanity, I also lowercased the table and column names. You should be able to copy and paste it directly into the MySQL command line. Happy SQLing.
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) default NULL,
job varchar(9) default NULL,
mgr decimal(4,0) default NULL,
hiredate date default NULL,
sal decimal(7,2) default NULL,
comm decimal(7,2) default NULL,
deptno decimal(2,0) default NULL
);
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno decimal(2,0) default NULL,
dname varchar(14) default NULL,
loc varchar(13) default NULL
);
INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');
INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');
Judging by the data, this has been around since 1982.
This “howto” is intended for people who may be new to Ubuntu (or Linux in general), or who spend most of their time using the graphical user interface (aka GNOME), but are occasionally confronted by other howtos that assume knowledge of “the terminal”. My hope is to provide a helpful reference (or cheatsheet) for the most common commands you’ll need to navigate using the command line interface.
How to open the terminal
In the top left, go to Applications > Accessories > Terminal:
A window should open that looks like this:
What is a command prompt?
The text displayed in the terminal window before the dollar-sign is called the “command prompt”. In my case it is jwatt@x200:~$ where jwatt is my username, x200 is the name of my computer, and ~ (tilde) is short for my home directory (which happens to be /home/jwatt).
All the commands you type will appear after the dollar sign, and some commands, like those that change directories, actually change the appearance of the command prompt (to help you remember where you are). In the examples below, I’ve copied all the commands I’ve typed, along with their results and the command prompt, for added realism.
After you type a command, hit the Enter key to execute it. If the command outputs any results, they will be printed out, followed by a new command prompt.
Figure out where you are
pwd – present working directory
jwatt@x200:~$ pwd
/home/jwatt
jwatt@x200:~$
List out the files and directories where you are
ls – list
jwatt@x200:~$ ls
Desktop test
jwatt@x200:~$
Change directories
cd – change directory
Use cd x to bring you into the “x” directory:
jwatt@x200:~$ cd test
jwatt@x200:~/test$ pwd
/home/jwatt/test
jwatt@x200:~/test$
Use cd .. to bring up a directory:
jwatt@x200:~/test$ cd ..
jwatt@x200:~$ pwd
/home/jwatt
jwatt@x200:~$
Use cd alone to bring you back to your home directory:
jwatt@x200:~$ cd test
jwatt@x200:~/test$ cd
jwatt@x200:~$ pwd
/home/jwatt
jwatt@x200:~$
Create a file
touch – touch a file to make it exist
jwatt@x200:~$ cd test
jwatt@x200:~/test$ touch notes.txt
jwatt@x200:~/test$ ls
notes.txt
jwatt@x200:~/test$
Create a directory
mkdir – make directory
jwatt@x200:~/test$ mkdir notes
jwatt@x200:~/test$ ls
notes notes.txt
jwatt@x200:~/test$
Move or rename a file
mv – move
To move a file, provide the file plus the destination directory:
jwatt@x200:~/test$ mv notes.txt notes
jwatt@x200:~/test$ ls
notes
jwatt@x200:~/test$ cd notes
jwatt@x200:~/test/notes$ ls
notes.txt
jwatt@x200:~/test/notes$
To rename a file, provide the file plus a new filename:
jwatt@x200:~/test/notes$ mv notes.txt notes2.txt
jwatt@x200:~/test/notes$ ls
notes2.txt
jwatt@x200:~/test/notes$
Copy a file
cp – copy
jwatt@x200:~/test/notes$ cp notes2.txt notes3.txt
jwatt@x200:~/test/notes$ ls
notes2.txt notes3.txt
jwatt@x200:~/test/notes$
Remove a file
rm – remove
Be careful, there is no undo!
jwatt@x200:~/test/notes$ rm notes3.txt
jwatt@x200:~/test/notes$ ls
notes2.txt
jwatt@x200:~/test/notes$
Remove a directory
rmdir – remove directory
Note: the directory must be free of all files before it can be deleted.
jwatt@x200:~/test/notes$ ls
notes2.txt
jwatt@x200:~/test/notes$ rm notes2.txt
jwatt@x200:~/test/notes$ cd ..
jwatt@x200:~/test$ rmdir notes/
jwatt@x200:~/test$ ls
jwatt@x200:~/test$
Run a command with elevated privileges
sudo – superuser do
Typically this is required when installing software (see below). sudo will prompt you for your password before executing the command that follows, making sure the change is being made by a user with the appropriate privileges, as well as giving you a moment to make sure you really want to run the command.
Install something on Ubuntu
apt-get install – apt package installer
Most other online howtos usually take the form of a long line of apt-get install commands, as this is the quickest way to instruct someone to install several software packages at once. If the site instructing you to install software is a trusted source, you can simply copy the apt-get install command, paste it into the terminal, and hit enter to run.
Because installing software on your computer requires elevated privileges, you’ll need to preface the apt-get install command with sudo.
jwatt@x200:~$ sudo apt-get install sl
[sudo] password for jwatt:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
sl
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 0B/25.3kB of archives.
After this operation, 197kB of additional disk space will be used.
Selecting previously deselected package sl.
(Reading database ... 145527 files and directories currently installed.)
Unpacking sl (from .../archives/sl_3.03-15_i386.deb) ...
Processing triggers for man-db ...
Setting up sl (3.03-15) ...
jwatt@x200:~$
