Create database schema document: Difference between revisions
Jump to navigation
Jump to search
m
→PostgreSQL: DDL document
No edit summary |
|||
| (11 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Create database schema document | Create database schema document from existing MySQL database. | ||
== Table document == | == MySQL: Table document (Schema) == | ||
Sample result of mysql query: {{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> | ||
Field | Type | Null | Key | Default | Extra | Field | Type | Null | Key | Default | Extra | ||
</pre> | </pre> | ||
Sample result of mysql query using {{kbd | key=SHOW}} syntax<ref>[https://dev.mysql.com/doc/refman/8.0/en/show-columns.html MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.5 SHOW COLUMNS Syntax]</ref>: {{kbd | key =<nowiki>SHOW FULL COLUMNS FROM <table></nowiki>}} | |||
<pre> | <pre> | ||
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | ||
| 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 29: | Line 29: | ||
</pre> | </pre> | ||
Tools: SQL syntax beautifier | |||
* [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 /> | |||
Related pages: | |||
* [[Data type]] | * [[Data type]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Database]] | |||
[[Category:PostgreSQL]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||