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
2. Create a Database
CREATE DATABASE learn_webtech;
3. How to use Database
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
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
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;