Mysql

Working with MySQL Database from the Command Line in Linux

Step 1: Opening the Terminal

Open your terminal. This is where you’ll enter your commands to interact with MySQL.

Step 2: Logging into MySQL

  1. Login Command: Use the following command to log into MySQL. Replace username with your MySQL username (usually root) and password with your actual password.mysql -u username -pAfter pressing Enter, you’ll be prompted to enter the password.

Step 3: Basic MySQL Commands

Show Databases

To view all databases:

SHOW DATABASES;

Create a Database

To create a new database named exampledb:

CREATE DATABASE exampledb;

Use a Database

Select the exampledb database to perform operations on it:

USE exampledb;

Create a Table

Create a table named users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Show Tables

To list all tables in the current database:

SHOW TABLES;

Describe a Table

Get details about the structure of a table:

DESCRIBE users;

Step 4: Working with Data

Insert Data

Add a new record into the users table:

INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');

Select Data

Retrieve all records from the users table:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;

Retrieve records with conditions:

SELECT * FROM users WHERE name='John Doe';

Update Data

Update existing records:

UPDATE users SET email='john.doe@example.com' WHERE name='John Doe';

Delete Data

Remove records from a table:

DELETE FROM users WHERE name='John Doe';

Step 5: Exiting MySQL

To exit the MySQL command line, type:

EXIT;

or

QUIT;

Additional Tips

  • Backup and Restore: Use the mysqldump command to back up databases and the mysql command to restore.
  • Help: Use HELP in the MySQL command line for assistance or \h for a list of commands.
  • Security: Never expose your passwords on the command line directly. Use secure methods or configuration files to handle credentials safely.

By following these steps and examples, you should be able to manage and manipulate MySQL databases directly from the Linux command line effectively.

Victoria

Im just a girl who hanging around with her friends ;)

Recent Posts

Building Your Next Project with wp-scripts: A Comprehensive Guide

WordPress development has evolved significantly, and modern tooling plays a crucial role in creating efficient…

1 week ago

Script for automatically informing search engines about new content on website

I. Project Overview The goal is to automate the process of notifying search engines (like…

2 weeks ago

Creating an XML sitemap script with PHP, designed for automated updates via CRON

1. Database Structure (MySQL) We'll need a database table to store information about our website's…

2 weeks ago

Comprehensive guide on building a URL shortening script

This explanation aims to provide a solid foundation for understanding the process and implementing your…

2 weeks ago

Guide on building a real-time website chat script

Okay, here's a comprehensive guide on building a real-time website chat script using PHP, HTML,…

2 weeks ago

Comprehensive guide on creating a simple website analytics system

Comprehensive guide on creating a simple website analytics system using PHP, HTML, CSS, JavaScript, and…

2 weeks ago