Lab2 Part4b: phpmyadmin on mySql

MySQL tutorial

https://www.w3schools.com/sql/

#1 Create Database  (Create one database but many tables and not the other way)

#2 Create Table

CREATE TABLE classList(
    adminNum varchar(20),
    name  varchar(255)
    
);

Example with Primary Key

CREATE TABLE classList(
    adminNum varchar(20) NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (adminNum )
);


#3

Some commonly used SQL statements

SELECT * FROM Customers;

SELECT * FROM Customers WHERE Country='Mexico';

INSERT INTO Customers (CustomerName, ContactName) 

VALUES ('Cardinal', 'Tom B. Erichsen');

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Create database example

#1 Create a database name as myDatabase  and click create as shown below:

 
#2 Create Table products example

 CREATE TABLE IF NOT EXISTS `products` (

  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`pid`)
)

Click on SQL Tab, type the SQL statement and click on Go

If no error in the SQL statement you would see a table 'products' created as shown below:

Click on Structure Tab and you will see the Table Structures
like the fields :

pid  (Primary Key and AUTO_INCREMENT)
name :  varchar(100)
price :   decimal(10,2)
description : text

#3 Insert values to the products table

INSERT INTO `products` ( `name`, `price`, `description`, `created_at`, `updated_at`) 
VALUES ( 'Long-grain rice, not parboiled, WKB', '3.00', '1000g', '2018-09-05 08:53:46', NULL);

#4 Insert more values to the products table

INSERT INTO `products` (`name`, `price`, `description`, `created_at`, `updated_at`) VALUES

('Long-grain rice, not parboiled, BL', '4.00', '1000g', '2018-09-05 08:53:46', NULL),

('Long-grain rice, parboiled, in cooking bags, WKB', '5.00', '1000g', '2018-09-05 08:53:46', NULL);

#5 Try other SQL statements like 

Do the following one at a time and observe its output

SELECT * FROM `products`
SELECT  `name`,`price` from products
 SELECT * FROM `products` WHERE pid ='1'
SELECT * FROM `products` WHERE name like '%not parboiled%'
DELETE FROM `products` WHERE pid ='2'
UPDATE `products` SET  `price`= '3.80' WHERE `pid` = '3'