LU05b – Creating databases and tables (SQL-DDL) [BZZ

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== LU05b – Creating databases and tables (SQL-DDL) ======

===== Learning objectives =====
  * I can create, select and delete a database using SQL.
  * I can create and delete a table with columns and data types using SQL.
  * I am familiar with the naming conventions for tables and columns.

All commands on this page are part of the **Data Definition Language (DDL)** – the part of SQL used to define the **structure** of a database is defined. 


===== An overview of the entire process =====

{{url>https://player.mux.com/i9xUpaKUB1jfD71N601Z4NLgmozCCF8nGxvk01jPQVNNE?metadata-video-title=Create+table+and+enter+values+in+Webstorm&video-title=Create+table+and+enter+values+in+Webstorm 1200,675 noscroll noborder center|Create table and enter values in Webstorm}}

//Silent demo video: The complete process in WebStorm. The following steps are shown in sequence: opening a new Query Console, creating a database using `CREATE DATABASE`, making it active with `USE`, creating a table with two columns and data types using `CREATE TABLE`, and finally entering data via the WebStorm interface and saving it with ‘Submit’.//

<WRAP center round info 80%>
The keyword **PRIMARY KEY** . This is what’s known as a constraint – we’ll cover that on the next page. When watching for the first time, concentrate on the sequence of steps.
</WRAP>


===== Creating a database =====
A database is like a folder containing tables.

<WRAP center box round 80%>
<code sql>
CREATE DATABASE bibliothek;
</code>
</WRAP>

Up to now, we’ve done this in WebStorm by right-clicking on the connection: **New → Schema**. The SQL command does exactly the same thing.

{{de:modul:m290_guko:learningunits:lu05:theorie:screenshot_2025-09-14_at_14.29.02.png?direct&400|Creating a new schema in WebStorm}}



===== Select a database =====

Several databases can co-exist on a MySQL server – for example //library// and //filmsammlung//. They are located within the same system but manage their data separately.

To ensure MySQL knows which database you are working in, select it first:

<WRAP center box round 80%>
<code sql>
USE bibliothek;
</code>
</WRAP>

From this point onwards, all subsequent commands will apply to this database. In WebStorm, we have previously used the drop-down field at the top of the console window for this – the command //USE// does the same thing in SQL.

{{de:modul:m290_guko:learningunits:lu05:theorie:screenshot_2025-09-14_at_14.28.46.png?direct&600|Schema selection in the WebStorm console window}}


===== Create a table =====

Using **CREATE TABLE** you create a table in the currently selected database.

<WRAP center box round 80%>
<code sql>
CREATE TABLE tabellenname (
  spaltenname1 DATENTYP,
  spaltenname2 DATENTYP
);
</code>
</WRAP>

Please note: After the **last** column, there is **no comma** .

==== Example: Table buch ====

<WRAP center box round 80%>
<code sql>
CREATE TABLE buch (
  buch_id INT,
  titel VARCHAR(100),
  isbn CHAR(13),
  seiten INT,
  preis DECIMAL(6,2),
  erschienen DATE,
  ausgeliehen BOOLEAN
);
</code>
</WRAP>

The data types are taken directly from the discussion on the previous page. This table works – but it isn’t perfect yet: at present, two books could have the same //book_id// , and a book with no title at all would also be permitted. We’ll sort that out on the next page.

==== Naming conventions ====
  * no spaces, no umlauts, no special characters
  * lower-case letters only
  * separate words with an underscore: //buch_id//, not //BuchID//
  * Within a database, every table name must be unique


===== Deletion =====

<WRAP center box round 80%>
<code sql>
DROP TABLE buch;

DROP DATABASE bibliothek;
</code>
</WRAP>

<WRAP center round important 80%>
**Please note:** Both commands will delete **immediately and irrevocably** – including all data. //DROP DATABASE// also removes all tables within it. Only use in production systems after a backup has been taken.
</WRAP>


===== Overview =====

<WRAP center box round 80%>
^ Command ^ Meaning ^
| CREATE DATABASE | Create a new database |
| USE | Switch to the active database |
| DROP DATABASE | Delete the database and all its tables |
| CREATE TABLE | Create a new table in the active database |
| DROP TABLE | Delete the table and all its data |
</WRAP>


----
{{tag>M290-LU05}}
[[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Guido Koch