MySQL Commands in Ubuntu

MySQL Commands in Ubuntu.md

Connect to MySQL

sudo mysql -u root -p

In the above command:

• -u is the user
• root is the MySQL username
• -p is the password

Set or Change Password

ALTER USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password by 'new_password';

Replace the user_name and new_password with your username and desired password. For example:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by '58##Tn&o^83!';

View Databases

In MySQL, you can view the list of databases: SHOW DATABASES;

Create a Database

You can create a database by issuing the following command in the MySQL shell: CREATE DATABASE IF NOT EXISTS database_name;

Select a Database

You can use any of the listed databases by running the following command in the MySQL shell: USE DATABASE_NAME;

Create a Table

mysql> CREATE TABLE IF NOT EXISTS tb1 (
  col1 INT,
  col2 VARCHAR(20),
  ...
  PRIMARY KEY (col1)
);

In this creation query:

• tb1 is the name of the table
• col1, col2 are the names of the columns in the tb1 table
• INT and VARCHAR are the datatypes of the specified columns
• col1 is defined as the primary key

View Tables

mysql> SHOW TABLES;

View Table Structure

mysql> DESC tb1;

Insert Data

mysql> INSERT INTO tb1(col1, col2) VALUES (1, "value1"),(2, "value2");

View Table Data

mysql> SELECT * FROM tb1;
mysql> SELECT col2 FROM tb1;

Delete Data from Table

mysql> DELETE FROM tb1 WHERE col1 = 1;

Delete a Table

mysql> DROP TABLE tb1;

Delete a Database

mysql> DROP DATABASE IF EXISTS db1;