by Oliver; Dec. 22, 2013
IntroductionThis 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 ProgramsMySQL:
$ mysql --host mysql.university.edu --user username --passwordSQLite:
$ sqlite3 my_db.sqlite3
Selecting a DatabaseUseful database commands:
SHOW DATABASES; # see all dbs USE my_database; # choose to use my_database SHOW TABLES; # see all tables in my_database
Table OperationsSelecting 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; # sortVia 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 CommentsThree ways:
# comment 1 -- comment 2 /* comment 3 */
Permissions and UsersSee your permissions:
SHOW GRANTS;Create a new user:
GRANT USAGE ON *.* TO 'new_user'@'%.myserver.edu' 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'@'%.myserver.edu';Specify privileges for the new user:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `some_database`.* TO 'new_user'@'%.myserver.edu';
SQLite CommandsSome SQLite commands:
.help .databases .schema .schema my_table .tables .header ONSQLite comments:
-- comment 1 /* comment 2 */
Example: A Python Script which Loads a Text File into an Sqlite DatabaseAs 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 # http://docs.python.org/2/library/sqlite3.html 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, "r") as f: for line in f: if counter > 0: # skip header line = line.rstrip('\n') # remove newline char linelist=[str(counter)] linetup = tuple(linelist + line.split("\t")) l.append(linetup) counter += 1 c.executemany('INSERT INTO my_table (id,x,y,z) VALUES (?,?,?,?)', l) # Save (commit) the changes conn.commit() # 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. conn.close()