Differences

This shows you the differences between two versions of the page.

--- database_notes [2019/03/24 22:59] – created steve
+++ database_notes [2019/03/24 23:04] – steve
@@ Line 1: / Line 1: @@
 ====== Database Notes ======
-** 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
+===== MySQL =====
 Copy table:
+<code>
 CREATE TABLE new_table SELECT * FROM old_table;
+</code>
 Rename column:
+<code>
 ALTER TABLE table_name CHANGE column_old column_new (TYPE) [UNSIGNED];
+</code>
 Rename value in field:
+<code>
 UPDATE table_name SET field = REPLACE(field, 'old', 'new');
+</code>
 Select data into a field:
+<code>
 INSERT INTO table_name SELECT * FROM table_two;
 UPDATE table_name SET column = CONCAT('value: ', column_two);
+</code>
 Rename table:
+<code>
 ALTER TABLE table_name RENAME new_table_name;
+</code>
-mysqldump tables:
+Export specific tables:
+<code>
 mysqldump database_name --tables table1 table2 table3
+</code>
 Add a new column:
+<code>
 ALTER TABLE table_name ADD COLUMN new_column mediumint(8) not null FIRST / AFTER column1;
+</code>
 Add a primary key:
+<code>
 ALTER TABLE table_name ADD PRIMARY KEY(colname);
+</code>
 Show indexes:
+<code>
 SHOW INDEX FROM colname;
+</code>
 Add autoincrement:
+<code>
 ALTER TABLE tablename ADD PRIMARY KEY(column);
 ALTER TABLE tablename CHANGE column column tinyint(3) unsigned auto_increment;
+</code>
 concat:
+<code>
 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
+</code>
 update + concat
+<code>
 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`
+</code>
 Set the pager (less) so it quits if the display is less than one screen (like psql)
+<code>
 \P less -F
+</code>
-Annoyances
+==== Regular Expressions ====
-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>';
@@ Line 72: / Line 91: @@
 Create an `lds_scriptures` table in MySQL for CSV
+<code>
 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
+</code>
 PostgreSQL-compatible dump
+<code>
 $ mysqldump <database> --tables lds_scriptures_books lds_scriptures_verses lds_scriptures_volumes -c -t -r lds_scriptures_mysql.sql
+</code>
-SQLite
+===== SQLite =====
 Create an index:
+<code>
 CREATE INDEX index-name ON table-name (column-name[, column-name]);
+</code>
 Convert from sqlite2 to sqlite3:
+<code>
 sqlite v2.db .dump | sqlite3 v3.db
+</code>
 Copy the database structure:
+<code>
 sqlite one.db .schema | sqlite two.db
+</code>
 PostgreSQL
@@ Line 96: / Line 125: @@
 Show databases, tables, table description:
+<code>
 \l
 \dt
 \d table_name
+</code>
 Rename table:
+<code>
 ALTER TABLE table_name RENAME TO new_table_name;
+</code>
 Replace text:
+<code>
 UPDATE table_name SET column_name = REPLACE(column_name, 'old_text', 'new_text');
+</code>
 Create new table from SELECT:
+<code>
 CREATE TABLE new_table AS SELECT * FROM old_table;
+</code>
 Create a (unique) index:
+<code>
 CREATE UNIQUE INDEX index_name ON table_name (column1);
+</code>
 Edit a query with $EDITOR
+<code>
 \e
+</code>
-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:
+<code>
 pg_dump -t tablename -a -f table.sql -O -x -d database
+</code>
 Empty a table, reset primary key:
+<code>
 TRUNCATE table_name;
+</code>
 Copy data from one table into another:
+<code>
 INSERT INTO table_one SELECT * FROM table_two ORDER BY table_two.id;
+</code>
 Add column:
+<code>
 ALTER TABLE table_name ADD col_name integer;
+</code>
 Create database with chosen encoding:
+<code>
 CREATE DATABASE <database> WITH ENCODING 'LATIN1';
+</code>
 Reset sequential index (auto-increment)
+<code>
 ALTER <sequence_id> START WITH 1;
+</code>
 Create user (with password, create database | user permissions)
+<code>
 CREATE USER <user_name> [ WITH PASSWORD 'password' ] [ CREATEDB | CREATEUSER ];
+</code>
 Load a query and execute it
+<code>
 \i <filename.sql>;
+</code>