Blog

In this article we will discuss about MySQL Query e.g. Create/show/drop database as well create/alert and delete table and table records.

Table of Contents

• Login to MySQL
• Create a Database
• Use Database
• Show Database
• Drop Database
• Create a Table
• Show structure of table
• Insert Data into table
• Select record from Table
• Update record from Table
• Delete record from table conditionally
• Delete all records from table
• Drop table

1. Login toMySQL

Login to MySql Command: Mysql -u root -p

Note: -u accept user name of MyQSL and -p accept password

Related: http://www.learnwebtech.in/mysql-command-line/

2. Create a Database

CREATE DATABASE learn_webtech;

3. How to use Database

USE learn_webtech;

Note: CREATE DATABASE is mySQL command and learn_webtech is database name. Here you can give name of database as you prefer.

4. Show Database

SHOW DATABASES;

5. Drop Database

DROP DATABASE drupaltest;

6. Create a Table

CREATE TABLE products(
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
product_code VARCHAR(255) NOT NULL,
product_description text NULL,
image VARCHAR(255),
price double(10,2),
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Note: CREATE TABLE is command to create table products is table name name : id,product_name,product_code,product_description,image,price,created_date are the columns of table mapped with appropriate datatype.

7. Show structure of table

DESC products;

Note: DESC is MySQL command and products is a table, which want to show structure.

8. Insert Data into table

INSERT INTO products(product_name,product_code,product_description,image,price)
VALUES('Mobile Phone','MPHONE','Best mobile on EMIs','image/mobile_phone.jpg','10000');

We can insert data in another way, main used when we have many columns.

INSERT INTO products SET 
product_name = 'Mobile Phone',
product_code = 'MPHONE',
product_description = 'Mobile on EMIs',
image = 'image/mobile_phone2.jpg',
price = '12000' ;

9. Update record from Table

UPDATE products SET product_code = 'PCODE' WHERE id = 1;

Note: Generally, in application need to update particular record conditionally. Here it will update product_code where id = 1.

10. Delete record from table conditionally

DELETE FROM products WHERE id=1;

Note: Generally, in application need to Delete particular record conditionally. Here it will delete record where id = 1.

11. Delete all records from table

 DELETE FROM products;

12. Drop table | Delete table

 DROP TABLE products;