Create database schema document: Difference between revisions
Jump to navigation
Jump to search
m
→PostgreSQL: DDL document
No edit summary |
|||
| (16 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Create database schema document from existing MySQL database. | |||
== 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>}} | |||
<pre> | |||
Field | Type | Null | Key | Default | Extra | |||
</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> | |||
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | |||
</pre> | |||
Steps of create database schema document for MySQL | Steps of create database schema document for MySQL | ||
# 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>}} | |||
# (for phpMyAdmin) Click "Print view (with full texts)" | |||
# Copy the table to other word processors | |||
== 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>}} | |||
<pre> | |||
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 | |||
</pre> | |||
Tools: SQL syntax beautifier | |||
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | |||
== PostgreSQL: Table document (Schema) == | |||
<pre> | <pre> | ||
Field | SELECT | ||
Type | -- *, | ||
Null | a.column_name AS "Field", | ||
Key | a.data_type AS "Type", | ||
Default | 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> | </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> | <pre> | ||
pg_dump -st <MY_TABLE> <MY_DATABASE> --schema-only > schema.sql | |||
</pre> | </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]] | |||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Database]] | |||
[[Category:PostgreSQL]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||