databases: [#arch architecture] | [#client client] | [#select select] | [#where where] | [#dates dates] | [#join join] | [#aggregate aggregate] | [#sort-limit sort and limit] | [#insert-update-delete insert, update, and delete] | [#schema schema] | [#sequences sequences] | [#indices indices] | [#import-export import and export] | [#script script] | [#func function] | [#query-tuning query tuning] | [#user user] | [#python python] | [#ruby ruby] | [#help help] | [#admin admin]

sql: [#types types] | [#casts casts] | [#literals literals] | [#dates dates] | [#identifiers identifiers] | [#op operators] | [#func functions] | [#distinct distinct] | [#qualified-asterisk qualified *] | [#regex regular expressions] | [#sequences sequences] | [#group-by group by] | [#aggregation-func aggregation functions ] | [#window-func window functions] | [#where-subquery where clause subqueries] | [#join join] | [#from-subquery from clause subquery] | [#as as] | [#limit-offset limit and offset] | [#with with] | [#null null] | [#set-multiset sets and multisets] | [#session-obj session objects] | [#scripts scripts] | [#reflection reflection] | [#sargable-expr sargable expressions] | [#txn transactions] | [#idempotent idempotent sql]

||~ ||~ [#postgresql postgresql]||~ [#mysql mysql]||~ [#sqlite sqlite]|| ||# version-used[#version-used-note version used] _ @< >@||##gray|//9.1//##||##gray|//MariaDB 5.5 with InnoDB//##||##gray|//3.7//##|| ||# show-version[#show-version-note show version]||##gray|//client://## _ $ psql @@–@@version _ _ ##gray|//server://## _ @@>@@ show server_version;||##gray|//client://## _ $ mysql @@–@@version _ _ ##gray|//server://## _ @@>@@ status;||$ sqlite3 @@–@@version|| ||||||||~ # arch[#arch-note architecture]|| ||# engine-arch[#engine-arch-note engine]||##gray|//in separate server process which communicates with client via TCP port or Unix domain socket//##||##gray|//in separate server process which communicates with client via TCP port or Unix domain socket//##||##gray|//contained in client library//##|| ||# data-arch[#data-arch-note data]||##gray|//database consists of tables which represent relations//##||##gray|//database consists of tables which represent relations//##||##gray|//database consists of tables which represent relations//##|| ||# files-arch[#files-arch-note files]||##gray|//each table corresponds to one or more files on the file system//##|| ||##gray|//database is stored in a single file//##|| ||# persistence-arch[#persistence-arch-note persistence]||##gray|//a write ahead log is used to ensure durability without flushing tables to disk after each transaction//##||##gray|//depends on storage engine; in InnoDB a redo log is used to ensure durability without flushing tables to disk after each transaction//##||##gray|//database file is updated after each transaction//##|| ||# indices-arch[#indices-arch-note indices] _ @< >@||##gray|//tables can have any number of indices//##||##gray|//tables can have any number of indices//##||##gray|//tables can have any number of indices//##|| ||# txn-arch[#txn-arch-note transactions]||##gray|//transactions can contain DML and DDL; row and table locking is available//##||##gray|//transactions can contain DML; table locking is available; InnoDB storage engine uses row locking to implement transactions//##|| || ||# security-arch[#security-arch-note security]||##gray|//password protected user accounts with fine grained permissions//##||##gray|//password protected user accounts with fine grained permissions//##||##gray|//security and permissions as provided by file system for database file//##|| ||# server-lang-arch[#server-lang-arch-note server side language]||##gray|//PL/pgSQL; server can be extended to support other languages//##||##gray|//SQL/PSM//##||##gray|//none//##|| ||||||||~ # client[#client-note client]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# invoke-client[#invoke-client-note invoke client] _ @< >@||$ psql -U $USER -h localhost||$ mysql -u $USER -h localhost -p||$ sqlite3 DB_FILE|| ||# client-help[#client-help-note client help]||\?||? _ \? _ \h _ help||.help|| ||# default-port[#default-port-note default port] _ @< >@||##gray|//5432//##||##gray|//3306//##||##gray|//none//##|| ||# show-db[#show-db-note show databases]||SELECT datname _ FROM pg_database; _ _ \l||SHOW databases;||.databases|| ||# switch-db[#switch-db-note switch database] _ @< >@||\connect ##gray|//database//##;||use ##gray|//database//##||attach “/tmp/db2” as db2; _ _ ##gray|//to disconnect://## _ detach db2;|| ||# current-db[#current-db-note current database] _ @< >@||SELECT current_database();||SELECT database();||##gray|//All attached files are current; names in one file may shadow another//##|| ||# chdir[#chdir-note chdir] _ @< >@||\cd /tmp||##gray|//none//##||##gray|//none//##|| ||# shell-cmd[#shell-cmd-note shell command] _ @< >@||! ls||system ls||##gray|//none//##|| ||run script||\i setup.sql||source setup.sql||.read setup.sql|| ||redirect output to file||\o /tmp/output.txt _ _ ##gray|//to discontinue redirection://## _ \o||tee /tmp/output.txt _ _ ##gray|//to discontinue redirection://## _ notee||.output /tmp/output.txt _ _ ##gray|//to discontinue redirection://## _ .output stdout|| ||set column delimiter||\f “\t”||##gray|//none//##||.separator “\t”|| ||toggle between aligned and unaligned output||\a||##gray|//none//##||##gray|//none//##|| ||||||||~ # select[#select-note select]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# select-star[#select-star-note select *]||SELECT * _ FROM customers;||SELECT * _ FROM customers;||SELECT * _ FROM customers;|| ||# project-columns[#project-columns-note project columns]||SELECT name, age _ FROM customers;||SELECT name, age _ FROM customers;||SELECT name, age _ FROM customers;|| ||# exclude-column[#exclude-column-note exclude column] _ @< >@||##gray|//none//##||##gray|//none//##||##gray|//none//##|| ||# project-expr[#project-expr-note project expression]||SELECT ‘Mr. ‘ @@||@@ name, age + 3 _ FROM customers;||SELECT concat(‘Mr. ‘, name), age + 3 _ FROM customers;||SELECT ‘Mr. ‘ @@||@@ name, age + 3 _ FROM customers;|| ||# rename-column[#rename-column-note rename column]||SELECT name AS moniker _ FROM customers;||SELECT name AS moniker _ FROM customers;||SELECT name AS moniker _ FROM customers;|| ||||||||~ # where[#where-note where]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# filter-rows[#filter-rows-note filter rows]||SELECT * _ FROM customers _ WHERE name = ‘Ed’; ||SELECT * _ FROM customers _ WHERE name = ‘Ed’;||SELECT * _ FROM customers _ WHERE name = ‘Ed’;|| ||# comparison-op[#comparison-op-note comparison operators]||SELECT * FROM customers WHERE age > 21; _ SELECT * FROM customers WHERE age < 21; _ SELECT * FROM customers WHERE age >= 21; _ SELECT * FROM customers WHERE age <= 21; _ SELECT * FROM customers WHERE age <> 21;|| || || ||# multiple-conditions-on-field[#multiple-conditions-on-field-note multiple conditions on field]||SELECT * _ FROM customers _ WHERE age >= 21 _ @<  >@AND age <= 65;|| || || ||# logical-op[#logical-op-note logical operators] _ @< >@||AND OR NOT|| || || ||# like[#like-note like]||SELECT * _ FROM customers _ WHERE name LIKE ‘Ed%’;|| || || ||||||||~ # dates[#dates-note dates]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||current timestamp||SELECT now(); _ SELECT CURRENT_TIMESTAMP;||select now(); _ select currenttimestamp;||select currenttimestamp;|| ||||||||~ # join[#join-note join]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# inner-join[#inner-join-note inner join]||SELECT * _ FROM customers c _ JOIN orders o _ @<  >@ON c.id = o.customer_id;||SELECT * _ FROM customers c _ JOIN orders o _ @<  >@ON c.id = o.customer_id;|| || ||# left-outer-join[#left-outer-join-note left outer join]||##gray|//include customers with no orders://## _ SELECT * _ FROM customers c _ LEFT JOIN orders o _ @<  >@ON c.id = o.customer_id;||##gray|//include customers with no orders://## _ SELECT * _ FROM customers c _ LEFT JOIN orders o _ @<  >@ON c.id = o.customer_id;|| || ||# full-outer-join[#full-outer-join-note full outer join]||##gray|//include customers with no orders and orders with no customers://## _ SELECT * _ FROM customers c _ FULL JOIN orders o _ @<  >@ON c.id = o.customer_id;||SELECT * _ FROM customers c _ LEFT JOIN orders o _ @<  >@ON c.id = o.customer_id _ UNION _ SELECT * _ FROM customers c _ RIGHT JOIN orders o _ @<  >@ON c.id = o.customer_id;|| || ||# cartesian-join[#cartesian-join-note cartesian join]||SELECT * _ FROM rows, columns;||SELECT * _ FROM rows, columns;|| || ||||||||~ # aggregate[#aggregate-note aggregate]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# row-count[#row-count-note row count] _ @< >@||SELECT count() FROM customers;||select count() from customers;||select count() from customers;|| ||# conditional-row-count[#conditional-row-count-note count of rows satisfying condition]||SELECT count() _ FROM customers _ WHERE age > 21;|| || || ||# count-distinct[#count-distinct-note count distinct]||SELECT count(DISTINCT name) _ FROM customers;|| || || ||# group-by[#group-by-note group by]||SELECT count(*) _ FROM customers _ GROUP BY age;|| || || ||# aggregation-op[#aggregation-op-note aggregation operators]|| || || || ||||||||~ # sort-limit[#sort-limit-note sort and limit]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# sort-ascending[#sort-ascending-note sort in ascending order]||SELECT * _ FROM customers _ ORDER BY name;||select * _ from baz _ order by foo;||select * _ from baz _ order by foo;|| ||# sort-descending[#sort-descending-note sort in descending order]||SELECT * _ FROM customers _ ORDER BY name DESC;||select * _ from baz _ order by foo desc;||select * _ from baz _ order by foo desc;|| ||# sort-multiple-columns[#sort-multiple-columns-note sort by multiple columns]||SELECT * _ FROM customers _ ORDER BY age, name;|| || || ||# select-single-row[#select-single-row-note single row]||SELECT * _ FROM customers _ WHERE name = ‘Ed’ _ LIMIT 1;||select * _ from customers _ where name = ‘Ed’ _ limit 1;||select * _ from customers _ where name = ‘Ed’ _ limit 1;|| ||# limit[#limit-note limit]||##gray|//first 10 customers://## _ SELECT * _ FROM customers _ ORDER BY name _ LIMIT 10;||select * _ from customers _ limit 10;||select * _ from customers _ limit 10;|| ||# offset[#offset-note offset]||##gray|//second 10 customers://## _ SELECT * _ FROM customers _ ORDER BY name _ LIMIT 10 _ OFFSET 10;|| || || ||||||||~ # insert-update-delete[#insert-update-delete-note insert, update, and delete]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# insert[#insert-note insert]||INSERT INTO customers (name, age) _ VALUES (34, ‘Ed’);||insert into foo (bar, baz) _ values (1, ‘one’);||insert into foo (bar, baz) _ values (1, ‘one’);|| ||# update[#update-note update]||UPDATE customers _ SET age = 35 _ WHERE name = ‘Ed’;||update foo _ set baz = ‘une’ _ where bar = 1;||update foo _ set baz = ‘une’ _ where bar = 1;|| ||# merge[#merge-note merge]|| || || || ||# delete[#delete-note delete]||DELETE FROM customers _ WHERE name = ‘Ed’;||delete from foo _ where bar = 1;||delete from foo _ where bar = 1;|| ||# delete-all-rows[#delete-all-rows-note delete all rows]||DELETE FROM customers; _ _ ##gray|//faster://## _ TRUNCATE customers;||delete from foo; _ _ ##gray|//faster on 5.0.3 and later://## _ truncate foo;||delete from foo;|| ||||||||~ # schema[#schema-note schema]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# create-table[#create-table-note create table]||CREATE TABLE customers ( _ @<  >@name TEXT, _ @<  >@age INT _ );||create table foo ( _ @<  >@bar int, _ @<  >@baz text _ );||create table foo ( _ @<  >@bar int, _ @<  >@baz text _ );|| ||# drop-table[#drop-table-note drop table] _ @< >@||DROP TABLE customers;||DROP TABLE customers;||DROP TABLE customers;|| ||# show-tables[#show-tables-note show tables] _ @< >@||\d||show tables;||.tables|| ||# describe-table[#describe-table-note describe table] _ @< >@||\d ##gray|//table//##||desc ##gray|//table//##;||.schema ##gray|//table//##|| ||# export-schema[#export-schema-note export schema] _ @< >@||$ pg_dump -a ##gray|//db//## > ##gray|//db//##.sql||$ mysqldump @@–@@d ##gray|//db//## > ##gray|//db//##.sql|| || ||# describe-doc[#describe-doc-note describe document] _ @< >@||##gray|//table determines row type//##||##gray|//table determines row type//##|| || ||||||||~ # sequences[#sequences-note sequences]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# incr[#incr-note increment]|| || || || ||||||||~ # indices[#indices-note indices]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||show indices||\di||show index from ##gray|//table//##;||.indices|| ||create index||CREATE INDEX foobaridx on foo (bar);||##gray|//InnoDB requires that the max length of a text or varchar column be less than 767 characters//## _ create index foobaridx on foo (bar);||create index foobaridx on foo ( bar );|| ||drop index||DROP INDEX foobaridx;||drop index foobaridx on foo;||drop index foobaridx;|| ||create unique index||CREATE UNIQUE INDEX foobaridx ON foo (bar);||create unique index foobaridx on foo (bar);||create unique index foobaridx on foo ( bar );|| ||create compound index||CREATE INDEX foobarbazidx ON foo (bar, baz);||create index foobarbazidx on foo (bar, baz);||create index foobarbaz_idx on foo (bar, baz);|| ||index hint|| || || || ||||||||~ # import-export[#import-export-note import and export]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# import-csv[#import-csv-note import csv]||$ echo $’1,"one, two, three”\n2,four\n3,"five\nsix\nseven”’ > /tmp/test.csv _ _ $ echo ‘create table test_csv ( col1 int, col2 text );’ | psql _ _ $ ( echo ‘copy test_csv from stdin with (format csv); ‘; cat /tmp/test.csv ) | psql _ _ ##gray|//trim header if there is one://## _ ( echo ‘copy test_csv from stdin with (format csv); ‘; sed -n ‘2,$p’ /tmp/test.csv ) | psql || ||$ echo $’1,"one, two, three”\n2,four\n3,"five\nsix\nseven”’ > /tmp/test.csv _ _ $ sqlite3 _ _ @@>@@ create table test_csv ( col1 int, col2 text ); _ _ @@>@@ .mode csv _ _ @@>@@ .import /tmp/test.csv test_csv|| ||# export-csv[#export-csv-note export csv]||$ echo ‘copy foo to stdout with (format csv);’ | psql > /tmp/foo.csv||##gray|grant FILE on . to ‘joe’@’localhost’;## _ _ SELECT * _ INTO OUTFILE ‘/tmp/dump.csv’ _ FIELDS TERMINATED BY ‘,’ _ OPTIONALLY ENCLOSED BY ‘“‘ _ LINES TERMINATED BY ‘\n’ _ FROM foo;||.mode csv _ .output /tmp/foo.csv _ select * from foo;|| ||||||||~ # script[#script-note script]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# sql-script[#sql-script-note run script]||\i foo.sql _ _ $ psql -f foo.sql||source foo.sql _ _ $ mysql ##gray|//db//## < foo.sql||.read foo.sql|| ||||||||~ # func[#func-note function]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# show-func[#show-func-note show functions]||\df;||show function status; _ _ show procedure status; _ _ select routine_name _ from informationschema.routines;|| || ||# show-func-src[#show-func-src-note show function source]||\df+ ##gray|//funcname//##;|| || || ||show built-in functions||select proname from pg_proc; _ _ select routine_name _ from information_schema.routines;|| || || ||define function|| || || || ||||||||~ # query-tuning[#query-tuning-note query tuning]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||explain plan||EXPLAIN SELECT * FROM customers;||EXPLAIN SELECT * FROM customers;||explain select * from foo;|| ||query stats||EXPLAIN ANALYZE SELECT * FROM customers;|| ||.stats on _ .stats off|| ||timer|| || ||.timer on _ .time off|| ||stats tables|| || || || ||||||||~ # user[#user-note user]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# current-user[#current-user-note current user]||select current_user;||select user();||##gray|//none//##|| ||# list-users[#list-users-note list users]||select usename _ from pg_user;||##gray|//table only readable by root://## _ select user from mysql.user;||##gray|//none//##|| ||# create-user[#create-user-note create user]||##gray|//at sql prompt://## _ @@>@@ create role fred with superuser _ @<  >@createdb createrole login; _ _ ##gray|//at cmd line; will prompt for privileges://## _ $ createuser fred||create user ‘fred’@’localhost’ identified by ‘abc123’;||##gray|//none//##|| ||# switch-user[#switch-user-note switch user]||set role fred;|| || || ||# drop-user[#drop-user-note drop user]||@@>@@ drop role fred; _ _ $ dropuser fred||drop user ‘fred’@’localhost’;||##gray|//none//##|| ||# set-password[#set-password-note set password]||alter user fred with password ‘xyz789’;||set password for ‘fred’@’localhost’ = password(‘xyz789’);|| || ||# grant[#grant-note grant]|| ||grant select on test.foo to ‘fred’@’localhost’;|| || ||# grant-all[#grant-all-note grant all]|| ||##gray|//table foo in database test://## _ grant all on test.foo to ‘fred’@’localhost’; _ _ ##gray|//all tables in database test://## _ grant all on test.* to ‘fred’@’localhost’;|| || ||# revoke[#revoke-note revoke]|| ||revoke all on test.* from ‘fred’@’localhost’;|| || ||||||||~ # python[#python-note python]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||install driver||$ sudo pip install psycopg||##gray|//make sure MySQL development files are installed://## _ $ sudo pip install MySQL-python||##gray|//Python ships with a driver//##|| ||import driver||import psycopg2||import MySQLdb||import sqlite3|| ||connect _ ##gray|//open, close//##||conn = psycopg2.connect(database=’foo’) _ _ conn.close()||conn = MySQLdb.Connect( _ @<  >@db=’cust’, _ @<  >@user=’joe’, _ @<  >@passwd=’xyz789’, _ @<  >@host=’127.0.0.1’) _ _ conn.close()||conn = sqlite3.connect(‘/PATH/TO/DBFILE’) _ _ conn.close()|| ||cursor _ ##gray|//create, close//##||cur = conn.cursor() _ _ cur.close()||cur = conn.cursor() _ _ cur.close()||cur = conn.cursor() _ _ cur.close()|| ||execute||cur.execute(‘select * from bar’)||cur.execute(“select * from bar”)||cur.execute(‘select * from bar’)|| ||bind variable||cur.execute(‘select * from foo where bar = %s’, vars=[1])||cur.execute(“select * from foo where bar = %s”, (1,))||cur.execute(‘select * from foo where bar = ?’, (1,));|| ||fetch all results||##gray|# returns list of tuples:## _ rows = cur.fetchall()||rows = cur.fetchall()||##gray|# returns list of tuples:## _ rows = cur.fetchall()|| ||iterate through results||for row in cur: _ @<  >@print(row[0])||for row in cur: _ @<  >@print(row[0])||for row in cur: _ @<  >@print(row[0])|| ||fetch one result||##gray|# returns a tuple:## _ row = cur.fetchone()||##gray|# returns a tuple:## _ row = cur.fetchone()||##gray|# returns a tuple:## _ row = cur.fetchone()|| ||transaction|| || || || ||||||||~ # ruby[#ruby-note ruby]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||install driver||$ sudo gem install ruby-pg||$ sudo gem install mysql||##gray|//Ruby ships with a driver//##|| ||import driver||require ‘pg’||require ‘mysql’||require ‘sqlite3’|| ||connect _ ##gray|//open, close//##||conn = PGconn.open(:dbname => ‘foo’) _ _ ##gray|//??//##||conn = Mysql.new _ conn.select_db(“foo”) _ _ ##gray|//??//##||conn = SQLite3::Database.new “/tmp/db” _ _ conn.close()|| ||execute||result = conn.exec(“select * from foo;”)||stmt = con.prepare(‘select * from foo’) _ stmt.execute||rows = conn.execute(“select * from foo”)|| ||bind variable|| ||stmt = con.prepare(‘select * from foo where bar = ?’) _ stmt.execute(1)||rows = conn.execute(“select * from foo where bar = ?”, [1])|| ||number of rows returned||result.cmdtuples||stmt.num_rows||rows.size|| ||fetch a row||##gray|# hash with column names as keys:## _ result[0]||##gray|# returns array:## _ stmt.fetch||rows[0]|| ||iterate through results||result.each do |row| _ @<  >@puts row[“bar”] _ end||stmt.each do |row| _ @<  >@puts row[0] _ end||rows.each do |row| _ @<  >@puts row[0] _ end|| ||transaction|| || || || ||||||||~ # help[#help-note help]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||man page||$ man 1 psql _ $ man 7 copy _ $ man 7 create_table|| || || ||||||||~ # admin[#admin-note admin]|| ||~ ||~ postgresql||~ mysql||~ sqlite|| ||# admin-user[#admin-user-note admin user]||postgres||root||##gray|//none//##|| ||# server-proc[#server-proc-note server process]||postgres||mysqld||##gray|//none//##|| ||# start-server[#start-server-note start server]|| || || || ||# stop-server[#stop-server-note stop server]|| || || || ||# config-file[#config-file-note config file]|| || || || ||# reload-config-file[#reload-config-file-note reload config file]|| || || || ||# data-dir[#data-dir-note data directory]||$ postgres -D /PATH/TO/DATA/DIR||$ mysqld @@–@@datadir /PATH/TO/DATA/DIR||##gray|//specified on command line//##|| ||# create-db[#create-db-note create database]||##gray|//at sql prompt://## _ @@>@@ create database foo; _ _ ##gray|//at command line://## _ $ createdb foo||##gray|//User must have ‘create’ privilege.//## _ _ ##gray|//at sql prompt://## _ @@>@@ create database foo; _ _ ##gray|//at command line://## _ $ mysqladmin create foo|| || ||# drop-db[#drop-db-note drop database]||@@>@@ drop database foo; _ _ $ dropdb foo||##gray|//User must have ‘drop’ privilege.//## _ _ ##gray|//at sql prompt://## _ @@>@@ drop database foo; _ _ ##gray|//at command line://## _ $ mysqladmin drop foo|| || ||# backup-db[#backup-db-note backup database]||$ pg_dump foo > /tmp/foo.sql _ _ $ pg_dump -F=c foo > /tmp/foo.postgres||$ mysqldump foo > /tmp/foo.sql|| || ||# restore-db[#restore-db-note restore database]||$ psql -f /tmp/foo.sql _ _ $ pgrestore -d foo /tmp/foo.postgres||$ mysql < /tmp/foo.sql|| || ||~ ||~ ##EFEFEF|@@_____________________________@@##||~ ##EFEFEF|@@___________________________@@##||~ ##EFEFEF|@@______________________________@@##||

# version-used-note ++ [#version-used version used]

The version used to test the examples in this sheet.

# show-version-note ++ [#show-version show version]

How to determine the version of a database engine.

mysql:

MySQL supports different storage engines. Each storage engine has its own size limits, and features such as indexes, transactions, locking and foreign key support aren’t available for all storage engines.

Here is how to determine the storage engine used for a table:

code select engine from informationschema.tables where tableschema = ‘test’ and table_name = ‘foo’; /code

# arch-note + [#arch Architecture]

# engine-arch-note ++ [#engine-arch engine]

The location of the database engine.

# data-arch-note ++ [#data-arch data]

How data is organized in a database.

# files-arch-note ++ [#files-arch files]

How data is stored in files on the file system.

postgresql:

Tables are split into multiple files when they exceed 2G; large attributes are stored in separate TOAST files.

# persistence-arch-note ++ [#persistence-arch persistence]

What durability guarantee is made and how this is accomplished.

# indices-arch-note ++ [#indices-arch indices]

Are indices available and what can be indexed.

# txn-arch-note ++ [#txn-arch transactions]

Are transactions available and what can participate in a transaction.

# security-arch-note ++ [#security-arch security]

Available security features.

# server-lang-arch-note ++ [#server-lang-arch server side language]

Whether a server side programming language is available.

# client-note + [#client Client]

# invoke-client-note ++ [#invoke-client invoke client]

How to invoke the command line client.

postgresql:

If the database user is not specified, it will default to the operating system user. If the database is not specified, it will default to the operating system user. If the host is not specified, //psql// will attempt to connect to a server on the local host using a Unix domain socket.

# client-help-note ++ [#client-help client help]

How to get a list of commands available at the command line client prompt.

# default-port-note ++ [#default-port default port]

The default port used by the client to connect to the server.

The default ports used by PostgreSQL and MySQL are defined in {{/etc/services}}.

# show-db-note ++ [#show-db show databases]

List the available databases on a server.

# switch-db-note ++ [#switch-db switch database]

How to switch between databases when using the command line SQL prompt.

# current-db-note ++ [#current-db current database]

# chdir-note ++ [#chdir chdir]

# shell-cmd-note ++ [#shell-cmd shell command]

# client-startup-file-note ++ [#client-startup-file client startup file]

The name of the startup file used by the client.

# custom-prompt-note ++ [#custom-prompt custom prompt]

# select-note + [#select Select]

# select-star-note ++ [#select-star select *]

# project-columns-note ++ [#project-columns project columns]

# exclude-column-note ++ [#exclude-column exclude column]

# project-expr-note ++ [#project-expr project expression]

# rename-column-note ++ [#rename-column rename column]

# where-note + [#where Where]

# filter-rows-note ++ [#filter-rows filter rows]

# comparison-op-note ++ [#comparison-op comparison operators]

# has-key-note ++ [#has-key has key]

# multiple-conditions-on-field-note ++ [#multiple-conditions-on-field multiple conditions on field]

# logical-op-note ++ [#logical-op logical operators]

# like-note ++ [#like like]

# join-note + [#join Join]

# inner-join-note ++ [#inner-join inner join]

# left-outer-join-note ++ [#left-outer-join left outer join]

# full-outer-join-note ++ [#full-outer-join full outer join]

# cartesian-join-note ++ [#cartesian-join cartesian join]

# aggregate-note + [#aggregate Aggregate]

# sort-limit-note + [#sort-limit Sort and Limit]

# sort-ascending-note ++ [#sort-ascending sort in ascending order]

# sort-descending-note ++ [#sort-descending sort in descending order]

# sort-multiple-columns-note ++ [#sort-multiple-columns sort by multiple columns]

# select-single-row-note ++ [#select-single-row single row]

# limit-note ++ [#limit limit]

# offset-note ++ [#offset offset]

# insert-update-delete-note + [#insert-update-delete Insert, Update, and Delete]

# insert-note ++ [#insert insert]

# update-note ++ [#update update]

# delete-note ++ [#delete delete]

# delete-all-rows-note ++ [#delete-all-rows delete all rows]

# schema-note + [#schema Schema]

# create-table-note ++ [#create-table create table]

# drop-table-note ++ [#drop-table drop table]

# show-tables-note ++ [#show-tables show tables]

List the tables in the current database.

# describe-table-note ++ [#describe-table describe table]

Show the columns for a table and their types.

# sequences-note + [#sequences Sequences]

# incr-note ++ [#incr increment]

# indices-note + [#indices Indices]

# import-export-note + [#import-export Import and Export]

# import-tab-note ++ [#import-tab import tab delimited]

# import-csv-note ++ [#import-csv import csv]

# import-json-note ++ [#import-json import json]

# export-tab-note ++ [#export-tab export tab delimited]

# export-csv-note ++ [#export-csv export csv]

# export-json-note ++ [#export-json export json]

# script-note + [#script Script]

# sql-script-note ++ [#sql-script run sql script]

How to run a SQL script at the command line.

# func-note + [#func Function]

# show-func-note ++ [#show-func show functions]

List the stored functions in the current database.

# show-func-src-note ++ [#show-func-src show function source]

# user-note + [#user User]

# current-user-note ++ [#current-user current user]

# list-users-note ++ [#list-users list users]

# create-user-note ++ [#create-user create user]

# switch-user-note ++ [#switch-user switch user]

# drop-user-note ++ [#drop-user drop user]

# set-password-note ++ [#set-password set password]

# grant-note ++ [#grant grant]

# grant-all-note ++ [#grant-all grant all]

# revoke-note ++ [#revoke revoke]

# query-tuning-note + [#query-tuning Query Tuning]

# python-note + [#python Python]

# ruby-note + [#ruby Ruby]

# admin-note + [#admin Admin]

# admin-user-note ++ [#admin-user admin user]

# server-proc-note ++ [#server-proc server process]

# start-server-note ++ [#start-server start server]

# stop-server-note ++ [#stop-server stop server]

# config-file-note ++ [#config-file config file]

# reload-config-file-note ++ [#reload-config-file reload config file]

# create-db-note ++ [#create-db create database]

How to create a database.

postgresql:

The user must have the {{CREATEDB}} privilege. When creating the database from the command line using {{createdb}}, the PostgreSQL user can be specified using the {{-U}} option.

# drop-db-note ++ [#drop-db drop database]

How to drop a database.

# backup-db-note ++ [#backup-db backup database]

# restore-db-note ++ [#restore-db restore database]

//Writing SELECT queries for open-source databases.//

The reader is assumed to have written SELECT queries with FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses.

When we say that something is //standard//, we mean it conforms to the most recent SQL standard.

When we say that something is //portable//, we mean works on PostgreSQL, MySQL, and SQLite.

# types + [#top Types]

A list of portable types:

Note that {{NUMERIC(##gray|//len//##)}} defines an integer type.

mysql:

MySQL maps BOOLEAN to TINYINT(1); REAL and DOUBLE PRECISION to DOUBLE; NUMERIC to DECIMAL.

# casts + [#top Casts]

This is the standard and portable way to cast:

code SELECT cast(‘7’ AS INTEGER) + 3; /code

The standard calls for implicit casts between numeric types.

The standard also calls for implicit casts between character types. In particular, character types can be concatenated, and the length of the concatenation type is the sum of the length of the argument types.

postgresql:

Other ways to cast:

code

SELECT ‘7’::INTEGER + 3; SELECT INTEGER ‘7’ + 3; /code

The type of string operations is TEXT, which is a character type of unlimited length.

It is an error to attempt to insert a string that is too long into a column with fixed or maximum length.

mysql:

When concatenating character types, the length of the type of the concatenation is the sum of the length of the type of the arguments.

MySQL silently truncates strings that are too long on insert.

sqlite:

SQLite does not enforce character type length limits.

# literals + [#top Literals]

The standard reserves these keywords: NULL, TRUE, and FALSE.

Numeric literals work like you would expect.

SQL strings are admirably simple. Single quote delimited, double the single quote to escape, double pipe for concatenation.

postgresql:

This code results in a type mismatch error:

code SELECT TRUE = 1; /code

Converting a string containing a hex digit to an integer:

code

select x’3bb’::int;

int4

955 /code

The {{chr()}} function takes an integer representing a Unicode point as an argument:

code

SELECT ‘one’ || chr(10) || ‘two’ || chr(10) || ‘three’;

?column?

one + two + three

SELECT chr(x’3bb’::int);

chr

λ /code

There is syntax for strings with C-style backslash escapes:

code select E’one\ntwo\nthree’; /code

mysql:

TRUE and FALSE are synonyms for 1 and 0.

The {{@@||@@}} operator is used for logical disjunction. Use the {{concat()}} function for string concatenation.

SELECT concat(‘one’, char(10), ‘two’); code +——————————–+ | concat(‘one’, char(10), ‘two’) | +——————————–+ | one two | +——————————–+ /code

sqlite:

SQLite does not have TRUE and FALSE literals. Use 1 and 0 instead.

Strings can be single quote or double quote delimited.

# dates + [#top Dates]

The standard provides the keywords CURRENTTIME, CURRENTDATE, and CURRENT_TIMESTAMP for observing the clock.

There are no date literals; strings are used instead. Inserting a string into a TIME, DATE, or TIMESTAMP column will work if [http://en.wikipedia.org/wiki/ISO_8601 ISO 8601 format] is used.

code

CREATE TABLE foo (a DATE, b TIME, c TIMESTAMP);

INSERT INTO foo VALUES (‘2012-10-19’, ‘18:00:00’, ‘2012-10-19 18:00:00’); /code

This works in both MySQL and PostgreSQL:

code

SELECT date(‘2012-10-19’), time(‘18:00:00’), timestamp(‘2012-10-19 18:00:00’); /code

EXTRACT, TIMESTAMP from DATE and TIME, STRFTIME, STRPTIME

##gray|//INTERVAL type and date arithmetic//##

mysql:

MySQL does not have an INTERVAL type. Subtracting two TIMESTAMPs yields a NUMERIC(20, 0) and subtracting two DATEs yields a NUMERIC(11, 0).

# identifiers + [#top Identifiers]

According to the standard, identifiers with unusual characters should be double quoted. A literal double quote is represented by two double quotes.

mysql:

MySQL uses backticks @@``@@ instead of double quotes ““ to quote identifiers.

# op + [#top Operators]

code

– select rows where foo.x ends with percent sign

SELECT * FROM foo WHERE x LIKE ‘%\%’ ESCAPE ‘\’; /code

//check mysql and sqlite//

# func + [#top Functions]

//how to get a list of functions//

[http://dev.mysql.com/doc/refman/5.5/en/functions.html MySQL 5.5 Functions and Operators] [http://dev.mysql.com/doc/refman/5.5/en/dynindex-function.html MySQL 5.5 Function Index] [http://www.sqlite.org/lang_corefunc.html SQLite Core Functions] [http://www.sqlite.org/lang_aggfunc.html SQLite Aggregate Functions] [http://www.sqlite.org/lang_datefunc.html SQLite Date and Time Functions]

# distinct + [#top DISTINCT]

Some of the places DISTINCT can be used:

UNION ALL, INTERSECT ALL, and EXCEPT ALL can be used to indicate multiset operations. UNION DISTINCT, INTERSECT DISTINCT, and EXCEPT DISTINCT indicate set operations. Since this is the default the use of DISTINCT is superfluous.

# qualified-asterisk + [#top Qualified *]

code – Example of a qualified *: only

– return rows from foo:

SELECT foo.* FROM foo JOIN bar ON foo.x = bar.x /code

# regex + [#top Regular Expressions]

SIMILAR TO ##gray|//Postgres//##

# sequences + [#top Sequences]

Here is the SQL standard syntax for external sequences:

code CREATE SEQUENCE foo;

CREATE SEQUENCE bar START WITH 1000 INCREMENT BY 10 MAXVALUE 2000;

SELECT NEXT VALUE FOR foo;

ALTER SEQUENCE foo RESTART WITH 10; /code

Here is the SQL standard syntax for internal sequences. None of the open source databases support this syntax, however.

code CREATE TABLE foo ( foo_id INTEGER GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MAXVALUE 10000) ) /code

postgresql:

PostgreSQL lacks SQL standard syntax for reading external sequences:

code CREATE SEQUENCE foo;

CREATE SEQUENCE bar START WITH 1000 INCREMENT BY 10 MAXVALUE 2000;

SELECT nextval(‘foo’);

ALTER SEQUENCE foo RESTART WITH 10;

SELECT setval(‘foo’, 10); /code

The keywords {{WITH}} and {{BY}} are optional.

How to create an internal sequence:

code CREATE TABLE foo fooid SERIAL, foodesc TEXT ); /code

mysql:

MySQL does not have external sequences.

sqlite:

SQLite does not have external sequences.

code CREATE TABLE foo ( fooid INTEGER PRIMARY KEY AUTOINCREMENT, foodesc TEXT ); /code

# group-by + [#top GROUP BY]

GROUP BY, HAVING, and ORDER BY clauses can refer to SELECT list items by ordinal number. I don’t think this is in the standard, but the feature is in PostgreSQL, MySQL, and SQLite.

# aggregation-func + [#top Aggregation Functions]

||~ ||~ postgresql||~ mysql||~ sqlite|| || ||count, sum, min, max, avg||count, sum, min, max, avg||count, sum, min, max, avg|| || ||count(distinct *) _ count(distinct ##gray|//expr//##) _ sum(distinct ##gray|//expr//##)||count(distinct *) _ count(distinct ##gray|//expr//##) _ sum(distinct ##gray|//expr//##)||count(distinct *) _ count(distinct ##gray|//expr//##) _ sum(distinct ##gray|//expr//##)|| || ||bit_and _ bitor||bitand _ bitor|| || || ||booland _ boolor|| || || || ||stringagg(##gray|//expr//##, ##gray|//delim//##)||group_concat(##gray|//expr//##) _ groupconcat(##gray|//expr//## separator ##gray|//delim//##)|| || || ||arrayagg|| || || || ||stddev_samp _ stddev_pop _ var_samp _ varpop||stddevsamp _ stddev_pop _ var_samp _ var_pop|| || || ||cor(##gray|//X//##, ##gray|//Y//##) _ cov_samp(##gray|//X//##, ##gray|//Y//##) _ cor_pop(##gray|//X//##, ##gray|//Y//##) _ regr_intercept(##gray|//X//##, ##gray|//Y//##) _ regr_slope(##gray|//X//##, ##gray|//Y//##)|| || ||

# window-func + [#top Window Functions]

//limits on use//

//usefulness//

code

SELECT state, fruit, avg(x) FROM produce GROUP BY state; ERROR: column “produce.fruit” must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT state, fruit, avg(x) FROM produce GROUP BY state; ^

SELECT state, fruit, avg(x) OVER (PARTITION BY state) FROM produce; state | fruit | avg ————+————+———————— arizona | banana | 1.00000000000000000000 california | orange | 8.6000000000000000 california | banana | 8.6000000000000000 california | apple | 8.6000000000000000 california | banana | 8.6000000000000000 california | orange | 8.6000000000000000 nevada | banana | 6.3333333333333333 nevada | apple | 6.3333333333333333 nevada | orange | 6.3333333333333333 oregon | grapefruit | 2.5000000000000000 oregon | grapefruit | 2.5000000000000000 washington | grapefruit | 2.5000000000000000 washington | apple | 2.5000000000000000 /code

# where-subquery + [#top WHERE Clause Subqueries]

Subqueries can be used in a WHERE clause with EXISTS, IN, and the comparison operators: = < > <= >= != (<>).

# join + [#top JOIN]

The following two queries are equivalent.

code SELECT * FROM a, b WHERE a.x = b.x AND a.y > 0; /code

code SELECT * FROM a JOIN b ON a.x = b.x WHERE a.y > 0; /code

The latter form is perhaps preferred. The latter separates the join condition from the expression, keeping the expression simpler. Each JOIN clause must have an ON clause, reducing the chance of writing a Cartesian join by accident.

To perform an outer join—LEFT, RIGHT, or FULL—one must use a JOIN clause.

JOINs can be used to replace (NOT) EXISTS with a subquery:

code SELECT * FROM customers c WHERE NOT EXISTS ( SELECT customerid FROM orders o WHERE c.id = o.customerid ); /code

code SELECT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customerid WHERE o.customerid is NULL; /code

# from-subquery + [#top FROM Clause Subqueries]

Subqueries inside parens can appear in FROM and JOIN clauses. They must be given an alias.

# as + [#top AS]

Select list items and tables in FROM and JOIN clauses can be given an alias using AS. If the aliased item is a table or column its previous name is hidden. Use of the AS keyword is optional and can be omitted.

# limit-offset + [#top LIMIT and OFFSET]

The standard is:

code OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY /code

Of these databases, only PostgreSQL provides a mechanism for selecting the row number, and it requires using a window function: {{row_number() OVER ()}}.

# with + [#top WITH]

code CREATE TABLE directededge ( startnode TEXT, end_node TEXT );

INSERT INTO directededge VALUES ( ‘a’, ‘b’); INSERT INTO directededge VALUES ( ‘b’, ‘c’); INSERT INTO directededge VALUES ( ‘c’, ‘d’); INSERT INTO directededge VALUES ( ‘x’, ‘y’);

WITH RECURSIVE directedpath(startnode, endnode) AS ( SELECT startnode, endnode FROM directededge UNION SELECT dp.startnode, de.endnode FROM directedpath AS dp JOIN directededge de ON dp.endnode = de.startnode ) SELECT * FROM directed_path; /code

code startnode | endnode ————+———- a | b b | c c | d x | y a | c b | d a | d /code

# null + [#top NULL]

[http://www.sqlite.org/nulls.html NULL Handling in SQLite Versus Other Database Engines]

code – return ‘bar’ if foo is NULL coalesce(foo, ‘bar’)

– return NULL if foo is ‘bar’ nullif(foo, ‘bar’) /code

# set-multiset + [#top Sets and Multisets]

code SELECT ‘foo’, 3 UNION SELECT ‘bar’, 7; SELECT ‘foo’, 3 INTERSECT SELECT ‘bar’, 7; SELECT ‘foo’, 3 EXCEPT SELECT ‘bar’, 7; /code

ALL and DISTINCT can be used after UNION, INTERSECT, and EXCEPT to indicate multiset or set operations. Set operations (i.e. DISTINCT) are the default.

# session-obj + [#top Session Objects]

# scripts + [#top Scripts]

Temporary tables and variables.

# reflection + [#top Reflection]

The standard calls for a schema called {{INFORMATION_SCHEMA}}. The starting point for learning about a database is:

code SELECT * FROM INFORMATION_SCHEMA.TABLES; /code

The standard also provides these:

code

SELECT CURRENTUSER; SELECT CURRENTROLE; SELECT CURRENTSCHEMA; SELECT CURRENTCATALOG; /code

sqlite:

SQLite does not have {{INFORMATION_SCHEMA}}. Use the {{.schema}} command to get a list of tables and their DDL.

# sargable-expr + [#top Sargable Expressions]

http://en.wikipedia.org/wiki/Sargable Sargable

# txn + [#top Transactions]

# idempotent + [#top Idempotent SQL]

Idempotent DDL scripts are desirable. {{CREATE TABLE}} statements fail if the table already exists. Both PostgreSQL and MySQL support {{DROP TABLE foo IF EXISTS;}} which is not part of the standard.

MERGE (MySQL REPLACE)

TEMP tables and WITH.

Query information_schema. This requires a language which can branch.

# postgresql + [#top PostgreSQL]

# mysql + [#top MySQL]

# sqlite + [#top SQLite]