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;
mysqldump 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
Annoyances
It seems to be purposely trying NOT to be compatible with any other database's formats! postgresql will accept BEGIN or START transaction, but MySQL will only use START. Postgresql can concat on || or , but Mysql will only do ,
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
Why I like PostgreSQL better
When running psql, if you hit Ctrl-C, it doesnt kill the client. Just the last query, or entered string. It accepts only ' as quotes around vales, instead of both “ and '. Plus, ” is used to quote table names. Its a little easier to create users. It's easy to setup the permissions of who can connect. It has some really cool data types. :) Better tab delimited options The pager by default quits on a screen that isn't one page. The pager is turned on by default. Things that bug me
Can't do an export of JUST THE SQL for some reason. (pg_dump) Can't export within a transaction (pg_dump) Can't alter column types, I think :(
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>;