Editing Create database schema document


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
# 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>
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]]

[[Category:MySQL]]
[[Category:Database]]
[[Category:PostgreSQL]]
[[Category:Data Science]]