Basic MySQL Commands with Examples
1. Connecting to a Database
To connect to a MySQL database, use the following command:
mysql -u username -p
2. Show Databases
To show the list of all available databases:
SHOW DATABASES;
3. Use a Database
To use a specific database:
USE database_name;
4. Create a Database
To create a new database:
CREATE DATABASE new_database_name;
5. Delete a Database
To delete an existing database:
DROP DATABASE database_name;
6. Show Tables
To show all tables in the current database:
SHOW TABLES;
7. Create a Table
To create a new table:
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
8. Delete a Table
To delete an existing table:
DROP TABLE table_name;
9. Insert Into a Table
To insert data into a table:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
10. Query a Table
To retrieve data from a table:
SELECT * FROM table_name;
11. Update a Table
To update existing records in a table:
UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value;
12. Delete From a Table
To delete records from a table:
DELETE FROM table_name WHERE some_column=some_value;
Executing Basic MySQL Commands with Examples
Let’s consider a sample database called studentDB and a table within it called students.
1. Connecting to a Database
Open your terminal and type:
mysql -u root -p
Executing Basic MySQL Commands with Examples
Let’s consider a sample database called studentDB and a table within it called students.
1. Connecting to a Database
Open your terminal and type:
mysql -u root -p
Enter your MySQL password when prompted.
2. Show Databases
Execute the following command:
SHOW DATABASES;
3. Create a Database
To create a database called studentDB:
CREATE DATABASE studentDB;
4. Use the Database
Switch to the new database:
USE studentDB;
5. Create a Table
Create a table called students with columns for id, name, and age:
CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);
6. Show Tables
Execute the following command:
SHOW TABLES;
7. Insert Into Table
Insert some sample records into the students table:
INSERT INTO students (name, age) VALUES ('Alice', 20), ('Bob', 22);
8. Query the Table
Retrieve data from the students table:
SELECT * FROM students;
9. Update the Table
Change Alice’s age to 21:
UPDATE students SET age=21 WHERE name='Alice';
10. Delete From the Table
Delete Bob from the table:
DELETE FROM students WHERE name='Bob';
11. Delete the Table
To delete the students table:
DROP TABLE students;
12. Delete the Database
Delete the studentDB database:
DROP DATABASE studentDB;