This is an old revision of the document!
This is legacy documentation from old releases.
Developer Notes These are just notes and things I've picked up about SQL and the databases while working with this project.
MySQL
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
Regular Expressions
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
SQLite
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>;