AM

Relational Databases and MySQL

Advantages of MySQL for Beginners

  • Free: The community edition is fully featured and scalable.
  • Ease of Use: Self-contained installation package with MySQL Workbench for creating databases and tables without extensive SQL knowledge.
  • Open Source: Extensive third-party tools and a large community for support.
  • Popularity: Widely used with many online resources available.

Disadvantages of MySQL

  • Efficiency: Larger databases are less efficient compared to competitors.
  • Limited Tools: Fewer tools for monitoring or optimizing large databases.
  • SQL Compliance: Not fully SQL compliant, requiring tweaks for migration to other DBMSs.

MySQL Workbench

  • A powerful SQL editor and database tool with graphical interface.
  • Query Window: Write SQL statements.
  • Buttons: Execute, save, load, or modify queries.
  • Output: Results appear in a grid view.
  • Navigator Pane: Shows loaded databases and table columns.

MySQL Data Types

  • Numeric: Integer, fixed point, and floating point.
  • String and Text: char, varchar, blob, and text.
  • Date Time: date, datetime, and timestamp.
  • Other: boolean, set, enum, bit, JSON, and spatial.

Numeric Data Types

  • Integers: Whole numbers with maximum storable value depending on the exact integer data type.
  • Fixed Point (Decimal/Numeric): Assigned with precision (total digits) and scale (digits after decimal).
  • Floating Point (Float): Stores numbers with decimals but may not be exactly accurate due to computer memory storage.

String and Text Data Types

  • Char: Fixed-length character strings, padded with spaces to maintain consistent storage.
  • Varchar: Variable-length character strings, scaling storage requirements based on actual size.
  • Blob/Text: Large amount of text. Includes TinyBlob/Text to LongBlob/Text with the LongBlob/Text having a max length of 4 gigabytes.

Date and Time Data Types

  • Date: Stores year, month, and day.
  • Datetime: Stores year, month, day, hours, minutes, seconds, and fractions of a second.
  • Timestamp: Stores date and time, converting between UTC and the database's configured time zone.

Other Data Types

  • Boolean: Stores true/false values (tinyint where 0=false, 1=true).
  • Set/Enum: Defines a list of acceptable values.
  • Bit: Stores binary numbers.
  • JSON: Optimized storage for JSON data.
  • Spatial: Stores geographic information (GIS) data.