(Behind Majlis Bandaraya Melaka Bersejarah)
Early morning 7am hiking mountain bukit beruang again before Diploma FYP part 1 presentation. Have a break.
DATA DEFINITION LANGUAGE (DDL)
1. Show all databases.
show databases;
2. Create a new database.
create database cafe;
3. Delete a database.
drop database cafe;
4. Start using the database.
use cafe;
5. Create a table.
create table FOOD
(
FoodID int(3) not null auto_increment primary key,
FoodName varchar(100) not null,
FoodType varchar(50),
FoodPrice decimal(5,2),
FoodCalorie int(4),
Fooddate date
);
create table FOOD
(
FoodID int(3) not null auto_increment,
FoodName varchar(100) not null,
FoodType varchar(50),
FoodPrice decimal(5,2),
FoodCalorie int(4),
Fooddate date,
Primary Key (FoodID)
);
create table test
(
id int(2) not null primary key,
name varchar(20) not null unique,
fax char(10) not null default '07-1234567'
);
CREATE TABLE satu
(
id int(2) not null primary key,
state enum('Melaka','Johor','Pahang') default NULL
);
6. To see the structure of the table.
describe FOOD;
7. To show all the tables in a database.
show tables;
8. To remove the table.
Drop table foods;
9. Rename table.
rename table FOOD to FOODS;
rename table [old name] to [new name];
10. Remove Primary Key. This can be done if the attribute is not auto_increment.
alter table FOODS drop primary key;
11. Add Primary key.
alter table foods add primary key (FoodID);
12. Remove a column.
alter table FOODS drop FoodName;
13. Add a new column to the table at specific positions.
alter table FOODS add FoodName varchar(100) not null after FoodID;
alter table FOODS add FoodName varchar(100) not null first;
14. Change column name only. Used to rename a column, change its datatype, or move it within the structure.
alter table Foods change FoodName FoodN varchar(100) not null;
15. Modify column characteristics. Used to do everything CHANGE can, but without renaming the column.
alter table Foods modify FoodN varchar(80) not null;
16. Add a unique key
Create unique index FoodName on Foods(FoodName);
17. Drop a unique key
Drop index FoodName on foods;
18. Set a default value
alter table foods alter FoodCalorie set default 0;
alter table foods alter FoodType set default "Asian";
19. Drop default value
alter table foods alter FoodType drop default;
DATA MANIPULATION LANGUAGE (DML)
1. Add/Insert records into the table
insert into Foods
(FoodName, FoodType, FoodPrice, FoodCalorie, FoodDate) values
('Coffee Cheese Cake', 'Cake', 8.50, 450, '2011-12-29'),
('Tom Yam Soup', 'Asian', 12.90, 350, '2010-02-17'),
('Fish and Chips', 'Western', 10.00, 625, '2010-05-02'),
('Nasi Lemak', 'Asian', 3.50, 400, '2012-11-14'),
('Black Forest Cake', 'Cake', 9.90, 580, '2012-10-10'),
('Mango Papaya salad', 'Asian', 7.50, 220, '2012-04-25'),
('Chicken Chop with Mashed Potato', 'Western', 15.90, 675, '2013-01-11'),
('Asam Pedas', 'Asian', 4.50, 335, '2010-11-04'),
('Caesar Salad', 'Western', 7.00, 240, '2012-09-12'),
('salmon with Brown Rice', 'Western', 18.90, 790, '2013-06-19');
2. Update a record in the file.
Update Foods set FoodName = "Salmon with Brown Rice",
FoodPrice = 18.50 where FoodId = 10;
3. Delete a specific record.
delete from Foods where FoodID = 9;
truncate Foods;
delete from Foods;
- Truncate = empties the table. When a new entry is entered, the FoodID starts at 1 again.
- Delete from Foods – this statement empties the table. But any new record entered will continue from the last FoodID value.
4. View all records.
select * from Foods;
5. View only some attributes.
select FoodName, FoodPrice from Foods;
6. Sort list.
select FoodName, FoodPrice from Foods order by FoodName desc;
select FoodName, FoodPrice from Foods order by FoodName asc;
select FoodName, FoodPrice from Foods order by FoodName asc, FoodPrice desc;
7. To show list without repetition/duplication
select distinct FoodType from Foods;
8. To search for food that ends with “ad” using wildcard.
Select * from Foods where FoodName like '%ad';
9. To search for food that starts with “C” using wildcard.
Select * from Foods where FoodName like 'c%’;
10. To search for food that is Asian and below RM5.00.
Select * from Foods where FoodType = 'Asian' and FoodPrice < 5;
11. To search for foods introduced in 2010.
Select * from Foods where year(FoodDate) = 2010;
12. To use simple formula in SQL statement.
Select *, (FoodPrice + 5) as 'New Price' from Foods;
13. Show all the Cakes
Select * from Foods where FoodType = "Cake";
Select * from Foods where FoodType != "Asian" and FoodType <> "Western";
Select * from Foods where FoodType NOT IN ("Asian","Western");
14. Show all food which are Western and Asian.
Select * from Foods where FoodType IN ("Asian","Western");
Select * from Foods where FoodType = "Asian" or FoodType = "Western";
Select * from Foods where NOT (FoodType = "Cake");
15. Show all food that has calorie between 400 and 600. Between is inclusive
Select * from Foods where FoodCalorie between 400 and 600;
Select * from Foods where FoodCalorie >= 400 and Foodcalorie <= 600;
USING AGGREGATE FUNCTIONS (AVG, COUNT, MIN, MAX, SUM)
16. Count all food.
Select count(*) as "Total Food" from Foods;
17. Count the number of food types.
select count(distinct FoodType) from foods;
18. Count the number of food for Cake.
select count(*) from foods where FoodType = "Cake";
19. Average price of food.
Select avg(FoodPrice) as "Average Food Price" from Foods;
Select round(avg(FoodPrice)) as "Average Food Price" from Foods;
Select round(avg(FoodPrice),2) as "Average Food Price" from Foods;
20. Minimum and Maximum price of food.
Select min(FoodPrice) from Foods;
Select max(FoodPrice) from Foods;
Select min(FoodPrice) from Foods where foodtype = "Western";
21. Lists all foods which cost below the average price.
Select * from Foods where FoodPrice < (select avg(FoodPrice)
from Foods);
Select * from Foods where FoodCalorie < (select avg(FoodCalorie)
from Foods);
22. Count the number of dishes available for each food type.
Select count(distinct FoodType) from Foods;
23. Count the number of food for each type.
Select FoodType, count(*) from Foods group by FoodType;
24. Sum/Add up the calories for all food.
Select sum(FoodCalorie) from Foods;
COMMIT, ROLLBACK AND SAVEPOINT
· By default all commnds executed in MySQL will be automatically saved.
· If lets say, you want to do some testing and you don’t want to save the changes that you will make, you can do the following:
1. First start the transaction
start transaction;
2. Next you can execute as many commands as you wish.
delete from owner where owner_id = 1;
delete from owner where owner_id = 2;
3. If you want to save the changes that you have made, execute the following command.
commit;
4. If you want to get the original data, execute the following command.
rollback;
· Sometimes you want to revert to certain part of your testing. Here you can use the savepoint command.
start transaction;
delete from owner where owner_id = 1;
savepoint p1;
delete from owner where owner_id = 2;
savepoint p2;
delete from owner where owner_id = 3;
rollback to savepoint p1;
select * from owner; ( This command will display all records except record 1)
commit;
FOREIGN KEY
create table owner
(
owner_id int(3) not null auto_increment primary key,
owner_name varchar(50) not null,
owner_state varchar(20) not null
)engine=innodb;
create table pet
(
pet_id int(3) not null auto_increment primary key,
pet_name varchar(50) not null,
pet_weight float(5,3) not null,
owner_id int(3),
foreign key (owner_id) references owner (owner_id)
)engine=innodb;
DROP FOREIGN KEY
1. First execute this command:
show create table child;
The result of the above code is:
CREATE TABLE `pet` (
`pet_id` int(3) NOT NULL AUTO_INCREMENT,
`pet_name` varchar(50) NOT NULL,
`pet_weight` float(5,3) NOT NULL,
`owner_id` int(3) DEFAULT NULL,
PRIMARY KEY (`pet_id`),
KEY `owner_id` (`owner_id`),
CONSTRAINT `pet_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2. Next drop the key.
ALTER TABLE pet DROP KEY owner_id;
3. Finally drop the foreign key.
ALTER TABLE pet DROP FOREIGN KEY pet_ibfk_1;
ADD FOREIGN KEY
alter table pet add foreign key (owner_id) references owner (owner_id);
ADDING RECORDS INTO TABLES
insert into owner (owner_name, owner_state) values
('Ali','Melaka'),
('Kumar','Kedah'),
('Mary','Melaka'),
('Jeff','Penang'),
('Siti','Perak'),
('Raymond','Kedah');
insert into pet(pet_name, pet_weight,owner_id) values
('Scooby',2.33,2),
('Kitty',11.10,1),
('Chicky',1.20,3),
('Moose',4.01,2),
('Tweety',7.25,4),
('Chip',3.90,6),
('Scrappy',3.10,5),
('Woof',15.80,5),
('Poop',13.05,4);
1. Display all owners with their pets
select * from owner, pet where owner.owner_id = pet.owner_id;
2. Display all of Jeff’s pets
select * from owner, pet where owner.owner_id = pet.owner_id and
owner.owner_name = 'Jeff';
3. Counts number of Jeff’s pets
select owner.owner_name, count(*) as "Total" from owner, pet
where owner.owner_id = pet.owner_id and
owner.owner_name = 'Jeff';
4. Counts number of pets of each owner
select owner.owner_name, count(*) as "Total" from owner, pet
where owner.owner_id = pet.owner_id group by owner.owner_id;
5. Average pet weight per owner
select owner.owner_name, round(avg(pet.pet_weight),2) as "Average"
from owner, pet
where owner.owner_id = pet.owner_id group by owner.owner_name;
6. Average pet weight per owner
select owner.owner_name, min(pet.pet_weight), max(pet.pet_weight) from owner, pet
where owner.owner_id = pet.owner_id group by owner.owner_name;
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
7. Finidng the owner with more than one pet
select owner.owner_name, count(pet.pet_id) from pet, owner
where owner.owner_id = pet.owner_id group by owner.owner_name
having count(pet.pet_id) > 1;
8. Finidng the owner with pets total weight more than 10 kg
select owner.owner_name, sum(pet.pet_weight) as "Total" from pet, owner
where owner.owner_id = pet.owner_id group by owner.owner_name
having total > 10;
MULTIPLE TABLE EXAMPLE
1. Create the relevant tables which are customer, invoice and product
create table customer
(
cid int(2) not null auto_increment primary key,
cname varchar(20) not null
);
create table product
(
pid int(2) not null primary key,
pname varchar(20) not null,
pprice decimal(6,2) not null
);
create table invoice
(
iid int(3) not null auto_increment primary key,
idate date not null,
iqty int(3) not null,
cid int(2),
pid char(3),
foreign key (cid) references customer(cid),
foreign key (pid) references product(pid)
);
2. Insert records into the tables.
insert into customer (cname) values
("Ali"), ("Kamal"), ("Mary"), ("Jenny");
insert into product (pid,pname,pprice) values
("P1","Chair", 34.00),
("P2","Desk", 100.00),
("P3","Keyboard", 21.00),
("P4","Mouse", 32.00),
("P5","Printer", 399.00);
insert into invoice (idate,iqty,cid,pid) values
("2009-10-10",10,2,"P2"),
("2010-03-21", 2,1,"P1"),
("2009-02-17", 1,1,"P5"),
("2013-12-10",25,2,"P5"),
("2012-09-15",110,3,"P3"),
("2009-05-23", 9,2,"P4"),
("2008-07-19",10,4,"P2");
3. Display all information about what the customers have bought.
select * from customer, invoice, product where
customer.cid = invoice.cid and invoice.pid = product.pid;
4. Display all information about what the customers have bought and sort then by name. By default will be in ascending order.
select * from customer, invoice, product where
customer.cid = invoice.cid and invoice.pid = product.pid
order by cname;
5. Display all details including the total amount to be paid per invoice.
select *, (product.pprice*invoice.iqty) as "Pay"
from customer, invoice, product
where customer.cid = invoice.cid and invoice.pid = product.pid
order by customer.cname;
6. Display the total amount each customer has to payall details including the total amount to be paid per invoice.
select customer.*, sum(product.pprice*invoice.iqty) as "Pay" from customer, invoice, product
where customer.cid = invoice.cid and invoice.pid = product.pid
group by customer.cname;
7. Display the total quantity ordered for every product.
select product.*, sum(invoice.iqty) as "Total Order" from invoice, product
where invoice.pid = product.pid
group by product.pid;
8. Display the total quantity and total price ordered for every product.
select product.*, sum(invoice.iqty) as "Quantity", invoice.iqty*product.pprice
from invoice, product
where invoice.pid = product.pid
group by product.pid;
