Wiki: MySQL and SQLite

Basic MySQL Commands Reference
by Oliver; 2013-12-22


This is a collection of simple SQL and SQlite commands for reference. It took me longer than it should have to appreciate an important fact about SQL: as my co-worker said, "SQL is like a hard drive." In other words, it's better to use it for its storage properties than to treat it as a programming language. It has a shell, but the shell is kind of crappy. Moreover, the conventions are annoying (who wants to capitalize commands?), and it's hard to do some basic scripting things. The solution is not to overuse the SQL shell but rather to use another language to do the scripting. Below I give an example of a python script which loads a text file into a SQlite database.

So you know what to expect, this wiki is more a reminder to myself than a carefully crafted article. I'm a firm believer in the basics and that's what you'll find here.

Starting the Programs

$ mysql --host --user username --password
$ sqlite3 my_db.sqlite3 

Selecting a Database

Useful database commands:
SHOW DATABASES;		# see all dbs
USE my_database;	# choose to use my_database
SHOW TABLES;		# see all tables in my_database

Data Types

MySQL: SQLite:

Table Operations

Selecting elements from a table and sorting:
DESCRIBE my_table;				# show all fields
SELECT * FROM my_table;				# all fields (i.e., columns)
SELECT my_field FROM my_table;			# a particular field
SELECT DISTINCT my_field FROM my_table;		# all uniq entries of a particular field
SELECT * FROM my_table LIMIT 10;		# only first 10 rows
SELECT * FROM my_table ORDER BY my_field;	# sort 
Via Stackoverflow, view a row of your database vertically:
SELECT * FROM my_table WHERE my_field="some value" LIMIT 1\G;
Change entry of a table:
UPDATE my_table SET my_field="some value" WHERE my_field="some other value";
Change entry of a field (column) over all rows:
UPDATE my_table SET my_field="some value"; 
Add a new field (column) to a table:
ALTER TABLE my_table ADD my_field datatype;
Delete a column from a table:
ALTER TABLE my_table DROP COLUMN my_field;
Note: this is for SQL only. It doesn't work in SQlite (in which you can just delete the whole table, and remake it sans that column).

Add a new row to a table:
INSERT INTO my_table (my_field1, my_field2, my_field3) VALUES ('a', 'b', 'c');
Create table:
CREATE TABLE "my_table" ("x" integer NOT NULL PRIMARY KEY, "y" varchar(500) NOT NULL, "z" text);
Delete table:
DROP TABLE my_table;
Delete entry from table:
DELETE FROM my_table WHERE my_field="some value";
Back up a table:
CREATE TABLE mytable_backup AS SELECT * FROM mytable;

MySQL Comments

Three ways:
# comment 1
-- comment 2
/* comment 3 */

Permissions and Users

See your permissions:
Create a new user:
GRANT USAGE ON *.* TO 'new_user'@'' IDENTIFIED BY PASSWORD 'my_password';
Note: *.* is the notation for all database, all tables.

Give the new user all privileges:
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'';
Specify privileges for the new user:
 TO 'new_user'@'';

SQLite Commands

Some SQLite commands:
.schema my_table
.header ON
SQLite comments:
-- comment 1
/* comment 2 */

Example: A Python Script which Loads a Text File into an Sqlite Database

As we remarked in the introduction, when you start to code, the SQL shell feels slow and clumsy. It's better to use a more competent language to do your scripting. Such a script is called a wrapper: just as you'd swaddle a poodle in a sweater, we'll wrap some SQL commands in a python coat.

The following script takes a tab-delimited text file, and loads the first three columns into fields x, y, z of table my_table in your database db.sqlite3. Every row of the file will be converted into a tuple containing the values of each of its fields, and we'll store all of these tuples in a list. Here's what it might look like (this simple script is based on the docs about python's sqlite3 module):
#!/usr/bin/env python

import sys
import sqlite3


conn = sqlite3.connect('/mypath/db.sqlite3')
c = conn.cursor()

# t = ('some_value',)
# c.execute('SELECT * FROM my_table WHERE my_field=?', t)
# print c.fetchone()

# clear database
c.execute('DELETE FROM my_table WHERE id>=1')

# list of tuples
l = []

# use a counter to add an id field.
counter = 0

# give the file name as the first argument
# assume the file has a header we want to skip
with open(sys.argv[1], "r") as f:
    for line in f:
        if counter > 0: # skip header
            line = line.rstrip('\n') # remove newline char
            linetup = tuple(linelist + line.split("\t"))
        counter += 1

c.executemany('INSERT INTO my_table (id,x,y,z) VALUES (?,?,?,?)', l)

# Save (commit) the changes

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.