How to manage data in maria db

how to enter maria db or MySQL shell
queries
user (e.g., root) has access to the database
how to enter particular database

how to enter maria db or MySQL shell

mysql -u root -p
enter password: rakesh@123 then again enter password is available in Laravel project env 

how to enter particular database

1st way

mysql -u root -p
enter password: rakesh@123 then again enter password is available in Laravel project env 

USE traccar;

output:

2nd way

mysql -u root -p -h localhost traccar

output:

queries

SHOW DATABASES;
USE traccar;// traccar is database name
SHOW TABLES;
SHOW TABLES LIKE 'tc_devices';
SELECT * FROM tc_devices;

CREATE (Insert Data into a Table)
To insert data into a table, you use the INSERT INTO statement.

-- Insert a single record into the 'users' table
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'password123');

Explanation: This command inserts a row into the users table with username, email, and password values.

READ (Select Data from a Table)
To fetch or read data, you use the SELECT statement.

-- Select all columns from the 'users' table
SELECT * FROM users;

-- Select specific columns from the 'users' table
SELECT username, email FROM users;

-- Select with a condition (where clause)
SELECT * FROM users WHERE username = 'john_doe';

-- Select with a limit (returning the first 5 records)
SELECT * FROM users LIMIT 5;

Explanation:

SELECT *: Fetches all columns.

WHERE: Adds a condition to filter the rows.

LIMIT: Restricts the number of rows returned.

UPDATE (Update Existing Data)
To update existing records, you use the UPDATE statement.

-- Update the email of a user with a specific username
UPDATE users
SET email = 'newemail@example.com'
WHERE username = 'john_doe';

-- Update multiple fields
UPDATE users
SET email = 'newemail@example.com', password = 'newpassword123'
WHERE username = 'john_doe';

Explanation:

SET: Specifies the columns to update.

WHERE: Filters the rows that need to be updated.

DELETE (Delete Data from a Table)
To delete data from a table, you use the DELETE statement.

-- Delete a user with a specific username
DELETE FROM users WHERE username = 'john_doe';

-- Delete all rows from the 'users' table (use with caution)
DELETE FROM users;

Explanation:

WHERE: Specifies the condition to filter which rows should be deleted.

Caution: Without WHERE, it will delete all rows in the table.

SELECT with JOIN (Fetching Data from Multiple Tables)
You can use JOIN to fetch related data from multiple tables.

-- INNER JOIN to fetch data from two tables (users and orders)
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

-- LEFT JOIN to fetch all users and their orders (including users with no orders)
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

Explanation:

INNER JOIN: Fetches rows when there’s a match in both tables.

LEFT JOIN: Fetches all rows from the left table and matched rows from the right table.

COUNT, SUM, AVG (Aggregating Data)
You can use aggregation functions like COUNT(), SUM(), and AVG() to calculate data.

-- Count the number of users
SELECT COUNT(*) FROM users;

-- Sum up the total amount spent in orders
SELECT SUM(amount) FROM orders;

-- Get the average price from the products table
SELECT AVG(price) FROM products;

Explanation:

COUNT(): Returns the number of rows.

SUM(): Returns the sum of a numeric column.

AVG(): Returns the average value of a numeric column.

ORDER BY (Sorting Data)
You can sort your result set using the ORDER BY clause.

-- Order users by username in ascending order
SELECT * FROM users ORDER BY username ASC;

-- Order users by email in descending order
SELECT * FROM users ORDER BY email DESC;

Explanation:

ASC: Ascending order (default).

DESC: Descending order.

LIMIT (Limiting Results)
The LIMIT clause limits the number of rows returned.

-- Get the first 10 users
SELECT * FROM users LIMIT 10;

-- Get 5 records starting from the 6th record
SELECT * FROM users LIMIT 5 OFFSET 5;

Explanation:

LIMIT: Specifies the maximum number of rows to return.

OFFSET: Skips a specific number of rows before starting to return results.

DISTINCT (Remove Duplicate Records)
You can use DISTINCT to return only unique values.

-- Get unique usernames from the users table
SELECT DISTINCT username FROM users;

Explanation: DISTINCT ensures that only unique (non-duplicate) values are returned.

** CREATE TABLE (Create a New Table)**
To create a new table in your database:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    password VARCHAR(100) NOT NULL
);

Explanation: Defines the structure of the table with column names and data types.

ALTER TABLE (Modify an Existing Table)
To modify an existing table structure (e.g., adding, dropping columns):

-- Add a new column to the users table
ALTER TABLE users ADD COLUMN phone_number VARCHAR(15);

-- Drop a column from the users table

ALTER TABLE users DROP COLUMN phone_number;
Explanation:

ADD COLUMN: Adds a new column to the table.

DROP COLUMN: Removes a column from the table.

DROP TABLE (Delete a Table)
To delete a table from the database:

-- Drop the users table
DROP TABLE users;

Explanation: Removes the table from the database permanently.

CREATE INDEX (Creating an Index for Fast Lookup)
Creating an index improves the speed of data retrieval:

-- Create an index on the 'username' column in the 'users' table
CREATE INDEX idx_username ON users(username);

Explanation: CREATE INDEX creates a searchable index for faster lookups.

LIKE (Pattern Matching)
Use the LIKE keyword to perform pattern matching searches.

-- Find users whose username starts with 'john'
SELECT * FROM users WHERE username LIKE 'john%';

-- Find users whose username contains 'doe'
SELECT * FROM users WHERE username LIKE '%doe%';

Explanation:

LIKE: Used for pattern matching.

%: Represents any sequence of characters.

IN (Checking if Value Exists in a List)
You can use IN to check if a value is in a list of values.

-- Get users whose id is 1, 2, or 3
SELECT * FROM users WHERE user_id IN (1, 2, 3);

Explanation: IN simplifies checking for multiple values.

user (e.g., root) has access to the database:

GRANT ALL PRIVILEGES ON traccar.* TO 'root'@'localhost' IDENTIFIED BY 'cotocus@123';
FLUSH PRIVILEGES;

the-definitive-guide-to-production-mysql-backup-and-restore
the-complete-mysql-database-backup-and-restore-guide-for-production-environments
database-backup-recovery-plan-a-complete-guide
common-mysql-errors
reset-the-mysql-root-password
errorhow-to-backup-and-restore-mysql-databases-using-the-mysqldump-command-