Relational Databases and MySQL

Flashcards covering key vocabulary and concepts related to relational databases, particularly MySQL, including data types, advantages, and usage within MySQL Workbench.

MySQL Community Edition

A free, fully featured relational database management system suitable for beginners and small companies, with advantages in ease of use, open-source nature, and a large community providing support.

Oracle DBMS and Microsoft SQL Server

Commercial DBMS products that can be expensive, contrasting with the free MySQL Community Edition.

MySQL Workbench

A visual database tool that simplifies creating databases and tables without extensive SQL knowledge, and aids in visualizing query results and data import/export.

MySQL Data Type Categories

Numeric, string/text, and date/time. Choosing a precise data type helps optimize database size and query speed.

MySQL Integer Data Types

Integer types that store whole numbers with different storage sizes (e.g., tinyint, bigint), each impacting the maximum storable value.

MySQL Fixed Point Numbers

Data types (decimal and numeric) that store numbers with a fixed number of digits before and after the decimal, ensuring accurate results in calculations.

Float (Floating Point Number)

A data type where the decimal point floats, and the numbers are not stored as precise values. Results from arithmetic operations are not exactly accurate.

Fixed Point Number Use Case

Best used when precision is crucial, such as in business applications. Results will always be exactly accurate.

Char and Varchar Data Types

Common ways to store text. Car fields are padded, and varchar allows scaling based on the actual size of the stored values.

Car Data Type

Used when the exact data length is known in advance, like zip codes, due to its consistent storage size.

Binary and Varbinary

Stores strings as bytes; should be used for machine-readable text.

Blob and Text Types

Allows storing large strings.

MySQL Date and Time Data Types

Types like date, datetime, and timestamp allow comparing, automatically adding, or changing dates/times, and maintaining consistent time measurements.

Timestamp Data Type

Often converted between UTC and the database's configured time zone, making it useful for recording database events.

Boolean Data Type

Stores true and false values represented by 0 (false) and 1 (true).

Set and Enum Data Types

Defines a list of acceptable values for a field, with 'set' allowing multiple values and 'enum' allowing only one.

JSON Data Type

Allows for the optimized storage of valid JSON data files.

Spatial Data Types

Used for storing Geographic Information Systems (GIS) data for maps.

Viewing Table Contents

Right-click on a table and choose 'Select Rows - Limit 1000' to view the contents of a table.

Viewing Table Structure

Right-click on the table and go to Table Inspector or click the wrench icon to view the structure of a table.