These are just notes and things I've picked up about SQL and the databases while working with this project.

Copy table:

CREATE TABLE new_table SELECT * FROM old_table;

Rename column:

ALTER TABLE table_name CHANGE column_old column_new (TYPE) [UNSIGNED];

Rename value in field:

UPDATE table_name SET field = REPLACE(field, 'old', 'new');

Select data into a field:

INSERT INTO table_name SELECT * FROM table_two;
UPDATE table_name SET column = CONCAT('value: ', column_two);

Rename table:

ALTER TABLE table_name RENAME new_table_name;

Export specific tables:

mysqldump database_name --tables table1 table2 table3

Add a new column:

ALTER TABLE table_name ADD COLUMN new_column mediumint(8) not null FIRST / AFTER column1;

Add a primary key:

ALTER TABLE table_name ADD PRIMARY KEY(colname);

Show indexes:

SHOW INDEX FROM colname;

Add autoincrement:

ALTER TABLE tablename ADD PRIMARY KEY(column);
ALTER TABLE tablename CHANGE column column tinyint(3) unsigned auto_increment;

concat:

select concat(lds_scriptures_books.book_title, ' ', lds_scriptures_verses.chapter, ':', lds_scriptures_verses.verse ) as test from lds_scriptures_books, lds_scriptures_verses where lds_scriptures_verses.book_id = lds_scriptures_books.book_id limit 10

update + concat

UPDATE `Table A`,`Table B` SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`," from ",`Table B`.`date`,'/') WHERE `Table A`.`A-num` = `Table B`.`A-num`

Set the pager (less) so it quits if the display is less than one screen (like psql)

\P less -F

MySQL's regular expression function (REGEXP) is case INsensitive on non-binary strings. Convert the data to a binary string before running case sensitive matches: SELECT BINARY text REGEXP '<case-sensitive pattern>';

Create an `lds_scriptures` table in MySQL for CSV

CREATE TABLE lds_scriptures AS SELECT verses.verse_id, verses.volume_id, verses.book_id, verses.chapter, verses.verse, verses.verse_scripture, verses.verse_title, verses.verse_title_short, books.book_title, books.book_title_long, books.book_title_short, books.book_title_jst, books.book_subtitle, books.lds_org AS books_lds_org, volumes.volume_title, volumes.volume_title_long, volumes.volume_subtitle, volumes.lds_org AS volumes_lds_org FROM lds_scriptures_verses verses, lds_scriptures_books books, lds_scriptures_volumes volumes WHERE verses.volume_id = volumes.volume_id AND verses.book_id = books.book_id ORDER BY verses.verse_id

PostgreSQL-compatible dump

$ mysqldump <database> --tables lds_scriptures_books lds_scriptures_verses lds_scriptures_volumes -c -t -r lds_scriptures_mysql.sql

Create an index:

CREATE INDEX index-name ON table-name (column-name[, column-name]);

Convert from sqlite2 to sqlite3:

sqlite v2.db .dump | sqlite3 v3.db

Copy the database structure:

sqlite one.db .schema | sqlite two.db

PostgreSQL

Show databases, tables, table description:

\l
\dt
\d table_name

Rename table:

ALTER TABLE table_name RENAME TO new_table_name;

Replace text:

UPDATE table_name SET column_name = REPLACE(column_name, 'old_text', 'new_text');

Create new table from SELECT:

CREATE TABLE new_table AS SELECT * FROM old_table;

Create a (unique) index:

CREATE UNIQUE INDEX index_name ON table_name (column1);

Edit a query with $EDITOR

\e

SQLite-compatible pg_dump:

pg_dump -t tablename -a -f table.sql -O -x -d database

Empty a table, reset primary key:

TRUNCATE table_name;

Copy data from one table into another:

INSERT INTO table_one SELECT * FROM table_two ORDER BY table_two.id;

Add column:

ALTER TABLE table_name ADD col_name integer;

Create database with chosen encoding:

CREATE DATABASE <database> WITH ENCODING 'LATIN1';

Reset sequential index (auto-increment)

ALTER <sequence_id> START WITH 1;

Create user (with password, create database | user permissions)

CREATE USER <user_name> [ WITH PASSWORD 'password' ] [ CREATEDB | CREATEUSER ];

Load a query and execute it

\i <filename.sql>;