MySQL Database Fundamentals

Flashcards for reviewing MySQL database concepts, including database creation, table management, primary and foreign keys, and data import techniques.

Schema vs. Database in MySQL

In MySQL, the terms 'schema' and 'database' are interchangeable.

Two Ways to Interact with MySQL Workbench

Using built-in wizards or writing SQL statements directly.

Creating a Database in MySQL Workbench Using Wizard

Click the new schema button, enter the database name, and apply the SQL statement generated by the workbench.

Creating a Database Manually with SQL

Use the CREATE DATABASE statement.

Setting a Default Schema

Ensures that SQL statements apply automatically to the selected database.

Creating a Table in MySQL Workbench Graphically

Right-click on 'tables', select 'create table', and define columns with names, data types, and constraints.

PK Meaning

Stands for primary key; ensures that every row has a unique value in this column.

Not Null Constraint

Specifies that a column cannot contain null values (i.e., it cannot be empty).

Unique Constraint

Ensures that all values in a column are unique.

Auto Increment

Tells MySQL to automatically generate a unique, incrementing value for a column.

Creating a Table with SQL

Use the CREATE TABLE statement, defining each column with its name, data type, and constraints.

Modifying Table Structure

Use the ALTER TABLE statement to add, rename, or change columns.

Importance of Primary Keys

Used to uniquely identify each row in a table and to establish relationships with other tables.

Foreign Key Definition

A primary key of one table that is linked in a different table.

Value of Primary and Foreign Keys

Essential for keeping a database normalized and maintaining data integrity.

Database Models

Contain all structural information about a database. Includes: tables, views, functions, and relationships between the tables but no data.

Reverse Engineering

Using MySQL Workbench to create a database model from an existing database.

EER Diagram

Stands for entity relationship diagram, a visual representation of tables and their relationships within a database.

Bulk Data Loading

Use the Table Data Import Wizard to load data from a file (e.g., CSV) into a table or load files containing SQL statements.

Data Import Wizard

Tool in MySQL Workbench to automatically load bulk data from files.

Loading SQL Files

Execute all commands in a file containing SQL statements to rapidly create database structures and insert data.

Data Order Import Importance

Must import data that the foreign key refers to before you can add data to a foreign key column otherwise there will be a foreign key constraint error.