MySQL Databases and RDBMS

• MySQL is a popular open-source Relational SQL Database Management System (RDBMS).
• MySQL is commonly used for developing web-based software applications.
• MySQL is developed, marketed, and supported by MySQL AB, a Swedish company.

Databases

• A database is a separate application that stores a collection of data.
• Each database has one or more distinct APIs for creating, accessing, managing, searching, and replicating data.

Relational Database Management Systems (RDBMS)

• RDBMS is software that enables the implementation of a database with tables, columns, and indexes.
• RDBMS guarantees referential integrity between rows of various tables.
• RDBMS updates indexes automatically.
• RDBMS interprets SQL queries and combines information from various tables.

RDBMS Terminology

• A database is a collection of tables with related data.
• A table is a matrix with data, similar to a simple spreadsheet.
• A column (data element) contains data of one specific kind, like a postcode.
• A row (tuple, entry, or record) is a group of related data, such as the data for one subscription.
• Redundancy refers to storing data twice to speed up the system.
• A primary key uniquely identifies a row; its value cannot occur twice in the table.
• A foreign key links two tables together.
• A compound key (composite key) consists of multiple columns because one column alone is not sufficiently unique.
• An index in a database is similar to an index at the back of a book.
• Referential integrity ensures that a foreign key value always points to an existing row.

RDBMS Database examples

• Oracle
• MySQL
• MS-Access
• PostgreSQL
• Microsoft SQL Server
• IBM DB2
• SQLite

MySQL Database

• MySQL is an open-source database.
• MySQL is customizable as it is open source.
• MySQL is faster than other databases, making it suitable for large datasets.
• MySQL supports various languages like PHP, PERL, C, C++, and JAVA.
• MySQL uses a standard form of the SQL data language.
• MySQL can handle large databases with up to 50 million rows or more in a table.
• The default file size limit for a table is 4GB, but it can be increased to a theoretical limit of 8 million terabytes (TB) if the operating system allows.

MySQL Data Types

• Numeric data types: INT, FLOAT(m,d), DOUBLE(m,d), DECIMAL(m,d).
• Date and Time data types: DATE, DATETIME, TIME, TIMESTAMP, YEAR.
• String data types: CHAR, VARCHAR, TEXT, LONGTEXT, BINARY.
• Large Object (LOB) data types: TINYBLOB, BLOB, MEDIUMBLOB, LONGTEXT.

Installing MySQL in Ubuntu

• Use the command: sudo apt-get install mysql mysql-client mysql-server.

Starting MySQL

• First, install MySQL, then enter the command mysql -u root -p in the command prompt.
• Enter the password, and the MySQL prompt will appear.

Database related CREATE command

• Syntax to create a database:
• CREATE DATABASE database_name;
• Example:
• CREATE DATABASE employees;

Database related SHOW & USE commands

• To check created databases
• SHOW DATABASES;
• Use SQL command "USE" to select a particular database
• Syntax:
• USE database_name;
• Example:
• USE customers;

Database related DROP command

• To drop, delete, or remove a MySQL database.
• Exercise caution as deleting any database results in data loss.
• Syntax:
• DROP DATABASE database_name;
• Example:
• DROP DATABASE employees;

Connect to a remote MySQL server

• Use the -h (host) option with the IP address of the remote machine.
• Example: # mysqladmin -h 172.16.25.126 -u root -p

Schema Objects in Oracle and MySQL

• Oracle: AFTER trigger; MySQL: trigger.
• Oracle: BEFORE trigger; MySQL: trigger.
• Check constraint: same for both.
• Column default: same for both.
• Database: same for both.
• Foreign key: same for both.
• Index: same for both.
• Oracle: Package; MySQL: N/A.
• Oracle: PL/SQL function; MySQL: Routine.
• Oracle: PL/SQL procedure; MySQL: Routine.
• Primary key: same for both.
• Oracle: Role; MySQL: N/A.
• Schema: same for both.
• MySQL: AUTO_INCREMENT for a column; Oracle: Sequence.
• Snapshot: N/A for MySQL.
• Synonym: N/A for MySQL.
• Table: same for both.
• Tablespace: N/A for MySQL.
• Temporary table: same for both.
• Trigger for each row: same for both.
• Unique key: same for both.
• User: same for both.
• View: same for both.