Everyday SQL

I have been using SQL from time to time with no formal database education, and find myself looking up the same stuff over and over, so I thought I'd collect solutions for such common things here for reference.

Setting up:
I'm using Ubuntu and MySQL. To install MySQL:
sudo apt-get install mysql-server mysql-client -y
This installer will prompt you for a root password.
To run the command line SQL client as the SQL root user:
mysql -u root -p
-u means user, -p means it will prompt for a password. Now you should be at an SQL terminal that looks like this:
mysql>
To list databases:
mysql> SHOW DATABASES;
To "use" a database (which is akin to "cd" in Unix), for example the "information_schema" database built into MySQL:
mysql> USE information_schema;
To list tables:
mysql> SHOW TABLES;

By the way, the convention for SQL is to use upper case, but it is case insensitive, so
mysql> show tables;
would work.

Creating a Database:
mysql> CREATE DATABASE test;
mysql> USE test;

Creating a table:
with column "foo" of type string with max length 5 and column "bar" of type int:
mysql> CREATE TABLE test (foo VARCHAR(5),bar INTEGER);

Inserting values (adding rows):
mysql> INSERT INTO test VALUES ("A",1);
mysql> INSERT INTO test VALUES ("B",2);
mysql> INSERT INTO test VALUES ("C",3);

Querying for everything:
mysql> SELECT * FROM test;
+------+------+
| foo | bar |
+------+------+
| A | 1 |
| B | 2 |
| C | 3 |
+------+------+
3 rows in set (0.00 sec)

Querying for a specific field:
backticks are used for quoting but are not necessary for column names without spaces.
SELECT `foo` FROM test;
+------+
| foo |
+------+
| A |
| B |
| C |
+------+
Querying with a filter:
mysql> SELECT * FROM test WHERE foo='B' or foo='C';
+------+------+
| foo | bar |
+------+------+
| B | 2 |
| C | 3 |
+------+------+

Deleting specific records:
mysql> DELETE FROM test WHERE foo='B' or foo='C';

Deleting a table:
mysql> DROP TABLE test;

Deleting a database
mysql> DROP DATABSE test;

Exiting:
mysql> exit
Bye

Reading a CSV file:
From a unix shell, I'll download a sample CSV file:
wget http://www.curransoft.com/data/iris.csv
Go into the SQL shell:
mysql -u root -p
Create an empty table with column names and types mirroring the CSV:
mysql> USE test;
mysql> CREATE TABLE `iris` (`SepalLength` float,`SepalWidth` float,`PetalLength` float,`PetalWidth` float,`Class` varchar(20));
Load in the CSV file with the following SQL:
LOAD DATA LOCAL INFILE 'iris.csv'
INTO TABLE iris
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`SepalLength`,`SepalWidth`,`PetalLength`,`PetalWidth`,`Class`);

The delimiter is specified by FIELDS TERMINATED BY
The quote symbol is specified by ENCLOSED BY
The newline symbol is specified by LINES TERMINATED BY
The rest specifies which columns to map the CSV columns to.

Dumping a table to a SQL script file:
The mysqldump utility generates a SQL script for recreating a given table. It outputs to stdout, so its output must be redirected to a file. From a unix shell, here's how to output the iris table into iris.sql:
mysqldump test iris -u root -p > iris.sql

Loading a SQL script (backup file):
This will load the generated iris.sql file, which creates and populates the iris table on the currently used database.
mysql> source iris.sql;

Giving access to the outside:
To open your MySQL instance to the outside world, you first need to modify my.cnf:
sudo gedit /etc/mysql/my.cnf
such that
bind-address = your_ip
where your_ip is the "inet addr" field of the output of the command
ifconfig

The second thing you need to do is grant access to a specific user name and password for a specific computer:
mysql> GRANT ALL ON database_name.table_name to 'username'@'client_ip' identified by 'password';
for example:
mysql> GRANT ALL ON test.* to 'fred'@'192.168.45.32' identified by 'fred123';

Start/Stop/Restart the MySQL server:

sudo /etc/init.d/mysql start

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql restart

Enjoy!