Create database schema document: Difference between revisions
Jump to navigation
Jump to search
| (7 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
Create database schema document from existing MySQL database. | Create database schema document from existing MySQL database. | ||
== Table document == | == MySQL: Table document (Schema) == | ||
Sample result of mysql query using {{kbd | key=EXPLAIN}} syntax<ref>[https://dev.mysql.com/doc/refman/8.0/en/explain.html MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Syntax]</ref>: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} | Sample result of mysql query using {{kbd | key=EXPLAIN}} syntax<ref>[https://dev.mysql.com/doc/refman/8.0/en/explain.html MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Syntax]</ref>: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} | ||
<pre> | <pre> | ||
| Line 13: | Line 13: | ||
Steps of create database schema document for MySQL | Steps of create database schema document for MySQL | ||
# Using [https://www.phpmyadmin.net/ phpMyAdmin] | # Using web-based database management software such as: [https://www.phpmyadmin.net/ phpMyAdmin] or [https://www.adminer.org/ Adminer] | ||
# SQL query: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} or {{kbd | key =<nowiki>SHOW FULL COLUMNS FROM <table></nowiki>}} | # SQL query: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} or {{kbd | key =<nowiki>SHOW FULL COLUMNS FROM <table></nowiki>}} | ||
# Click "Print view (with full texts)" | # (for phpMyAdmin) Click "Print view (with full texts)" | ||
# Copy the table to other word processors | # Copy the table to other word processors | ||
== DDL document == | == MySQL: DDL document == | ||
[https://en.wikipedia.org/wiki/Data_definition_language Data definition language] (DDL): Sample result of mysql query: {{kbd | key =<nowiki>SHOW CREATE TABLE <table></nowiki>}} | [https://en.wikipedia.org/wiki/Data_definition_language Data definition language] (DDL): Sample result of mysql query: {{kbd | key =<nowiki>SHOW CREATE TABLE <table></nowiki>}} | ||
<pre> | <pre> | ||
| Line 32: | Line 32: | ||
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | * [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | ||
== PostgreSQL: Table document (Schema) == | |||
<pre> | |||
SELECT | |||
-- *, | |||
a.column_name AS "Field", | |||
a.data_type AS "Type", | |||
a.character_maximum_length AS "Length", | |||
a.is_nullable AS "Null", | |||
b.constraint_type AS "Key", | |||
a.column_default AS "Default" | |||
FROM | |||
information_schema.columns AS a | |||
LEFT JOIN ( | |||
SELECT c.column_name, | |||
c.data_type, | |||
constraint_type | |||
FROM information_schema.table_constraints tc | |||
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) | |||
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema | |||
AND tc.table_name = c.table_name AND ccu.column_name = c.column_name | |||
WHERE tc.table_name = '<MY_TABLE>' | |||
) AS b | |||
ON b.column_name = a.column_name | |||
WHERE | |||
a.table_name = '<MY_TABLE>'; | |||
</pre> | |||
== PostgreSQL: DDL document == | |||
[https://www.postgresql.org/docs/current/app-pgdump.html PostgreSQL: Documentation: 14: pg_dump]<ref>[https://serverfault.com/questions/231952/is-there-an-equivalent-of-mysqls-show-create-table-in-postgres postgresql - Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres? - Server Fault]</ref> | |||
<pre> | |||
pg_dump -st <MY_TABLE> <MY_DATABASE> --schema-only > schema.sql | |||
</pre> | |||
{{exclaim}} The parameter {{kbd | key=<nowiki><MY_TABLE></nowiki>}} is before than another parameter {{kbd | key=<nowiki><MY_DATABASE></nowiki>}} | |||
== References == | == References == | ||
| Line 41: | Line 83: | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Database]] | |||
[[Category:PostgreSQL]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||
Latest revision as of 17:59, 10 November 2022
Create database schema document from existing MySQL database.
MySQL: Table document (Schema)[edit]
Sample result of mysql query using EXPLAIN syntax[1]: EXPLAIN <table>
Field | Type | Null | Key | Default | Extra
Sample result of mysql query using SHOW syntax[2]: SHOW FULL COLUMNS FROM <table>
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment
Steps of create database schema document for MySQL
- Using web-based database management software such as: phpMyAdmin or Adminer
- SQL query: EXPLAIN <table> or SHOW FULL COLUMNS FROM <table>
- (for phpMyAdmin) Click "Print view (with full texts)"
- Copy the table to other word processors
MySQL: DDL document[edit]
Data definition language (DDL): Sample result of mysql query: SHOW CREATE TABLE <table>
CREATE TABLE `table` ( `id` varchar(30) NOT NULL, `note` varchar(30) DEFAULT NULL, `status` int(1) NOT NULL DEFAULT '1', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Tools: SQL syntax beautifier
PostgreSQL: Table document (Schema)[edit]
SELECT -- *, a.column_name AS "Field", a.data_type AS "Type", a.character_maximum_length AS "Length", a.is_nullable AS "Null", b.constraint_type AS "Key", a.column_default AS "Default" FROM information_schema.columns AS a LEFT JOIN ( SELECT c.column_name, c.data_type, constraint_type FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE tc.table_name = '<MY_TABLE>' ) AS b ON b.column_name = a.column_name WHERE a.table_name = '<MY_TABLE>';
PostgreSQL: DDL document[edit]
PostgreSQL: Documentation: 14: pg_dump[3]
pg_dump -st <MY_TABLE> <MY_DATABASE> --schema-only > schema.sql
The parameter <MY_TABLE> is before than another parameter <MY_DATABASE>
References[edit]
Related pages: