MySQL Essentials: Database & Table Management Commands

MySQL Essentials: Database & Table Management

Connecting to MySQL

To connect to a MySQL server, use the following command:

mysql -u [username] -p [password]

Database Operations

Viewing Databases

To see all available databases:

SHOW DATABASES;

Creating a Database

To create a new database:

CREATE DATABASE [database_name];

Selecting a Database

To select a database to work with:

USE [database_name];

Table Operations

Viewing Tables

To list all tables within the currently selected database:

SHOW TABLES;

Viewing Table Structure

To display the structure (columns, data types, etc.) of a specific table:

DESCRIBE [table_name];

Creating a Table

To create a new table within the selected database:

CREATE TABLE [table_name] (
    [field_name_1] [data_type_1] [field_definitions_1],
    [field_name_2] [data_type_2] [field_definitions_2],
    -- ... more fields ...
    [field_name_n] [data_type_n] [field_definitions_n]
);

Example: Creating a Customers table

CREATE TABLE Customers (
    CustomerNumber BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    Nome VARCHAR(50) NOT NULL,
    CPF VARCHAR(11),
    PRIMARY KEY (CustomerNumber)
);

Inserting Data into a Table

To add new records to a table:

INSERT INTO [table_name] ([field_1], [field_2], ...) VALUES ([value_1], [value_2], ...);

Example: Inserting data into Customers

INSERT INTO Customers (Nome, CPF) VALUES ('Austregecilio Cruz Neto', '12345678900');

Updating Data in a Table

To modify existing records in a table:

UPDATE [table_name] SET [field_name] = [new_value] WHERE [condition];

Example: Updating a customer's name

UPDATE Customers SET Nome = 'Austregecilio Cross' WHERE CPF = '12345678900';

Caution: Without a WHERE clause, this command will update all records in the table.

Selecting Data from a Table

To retrieve data from a table:

SELECT [field_1], [field_2], ... FROM [table_name] WHERE [condition];

Examples:

• Select specific fields for a customer:

  SELECT CustomerNumber, Nome, CPF FROM Customers WHERE CustomerNumber = 1;

• Select all fields from the table:

  SELECT * FROM Customers;

Deleting Data from a Table

To remove records from a table:

DELETE FROM [table_name] WHERE [condition];

Example: Deleting a customer record

DELETE FROM Customers WHERE CustomerNumber = 1;

Caution: Without a WHERE clause, this command will delete all records in the table.

MySQL Concepts & Architecture

Database Schema

A schema refers to the logical structure of a database, including its tables, fields, relationships, indexes, and other elements.

MySQL Deployment Environments

• Developer Machine: MySQL installed on a personal development computer.
• Server Machine: MySQL installed on a general-purpose server.
• Dedicated MySQL Server Machine: MySQL installed on a server exclusively for database operations.

Database Types

• Multifunctional Database: Used for general-purpose applications.
• Transactional Database Only: Optimized for web applications and services requiring high transaction integrity (ACID properties).
• Non-Transactional Database Only: Primarily designed for data reading, with minimal inserts, updates, or deletions.

MySQL Storage Engines

MySQL uses storage engines to manage how data is stored and retrieved. Two common engines are:

• MyISAM:
  • A storage mechanism that does not support ACID transactions.
  • Creates three files for each table:
    • .frm: Stores the table format.
    • .myd: Stores the actual data.
    • .myi: Stores the indexes.
• InnoDB:
  • A storage mechanism that supports ACID (Atomicity, Consistency, Isolation, Durability) transactions.
  • Generally preferred for applications requiring data integrity and concurrency.

Character Sets (Charset)

A Charset defines the set of characters and their encoding used for storing text data, supporting various languages.

MySQL Tools

• MySQL Administrator: Provides general administration capabilities for MySQL servers.
• MySQL Query Browser: Allows users to access databases, tables, and execute SQL queries.
• MySQL Migration Toolkit: Facilitates the migration of databases from other database systems to MySQL.

SQL Command Line Interface

The MySQL command line interface is a fundamental way to interact with MySQL.

• Advantage: It is the simplest and most direct way to access MySQL.
• Disadvantage: Requires significant knowledge of SQL and MySQL command syntax.