Working with MySQL

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate databases. It is widely used for web applications and is a central component of the LAMP stack (Linux, Apache, MySQL, PHP/Python/Perl).

Key Features of MySQLOpen Source: 
  • MySQL is free to use under the GNU General Public License (GPL).
  • Cross-Platform: It can be installed on various operating systems including Linux, Windows, and macOS.
  • High Performance: MySQL is designed for high-speed transactions and supports a large number of concurrent users.
  • Scalability: Suitable for both small applications and large-scale enterprise solutions.
  • Security: Provides robust security features to protect data.
  • Replication: Supports database replication to increase availability and performance.
Installing MySQL
On Linux

sudo apt-get update 
sudo apt-get install mysql-server

On Windows

Download the MySQL installer from the official website and follow the installation wizard.

Basic MySQL Operations
Starting MySQLLinux:
sudo service mysql start

Windows: MySQL should start automatically if you chose that option during installation. Otherwise, you can start it from the Services application.
Logging In
mysql -u root -p

Enter your root password when prompted.

Basic SQL Commands in MySQL

Creating a Database

CREATE DATABASE example_db;

Using a Database

USE example_db;

Creating a Table

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Inserting Data into a Table

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

Querying Data

SELECT * FROM users;

Updating Data

UPDATE users SET email = 'john.doe@example.com' WHERE username = 'john_doe';

Deleting Data

DELETE FROM users WHERE username = 'john_doe';

Dropping a Table

DROP TABLE users;

Example:
Working with MySQL in Practice

Creating a Database and Table:

CREATE DATABASE test_db; 
USE test_db; 

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), position VARCHAR(100), hire_date DATE );

Inserting Data:

INSERT INTO employees (name, position, hire_date) VALUES ('Alice Johnson', 'Manager', '2020-01-15'); 
INSERT INTO employees (name, position, hire_date) VALUES ('Bob Smith', 'Developer', '2019-06-23');

Querying Data:

SELECT * FROM employees;

Updating Data:

UPDATE employees SET position = 'Senior Developer' WHERE name = 'Bob Smith';

Deleting Data:

DELETE FROM employees WHERE name = 'Alice Johnson';

Dropping the Database:

DROP DATABASE test_db;

MySQL Tools
MySQL Workbench: A graphical tool for database design and management.
phpMyAdmin: A web-based interface for MySQL administration.
Command Line Client: Allows direct interaction with MySQL from the terminal.

Summary

MySQL is a powerful and flexible RDBMS used for a variety of applications. By understanding the basics of database creation, table management, and data manipulation, you can effectively use MySQL to manage your data.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more