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

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more