Bài 04: Hệ quản trị cơ sở dữ liệu (Bài Lab)

 

Kho Bài Lab

Lab 01

create database 1812767_MySQL;
use 1812767_MySQL;

create table orders(
	orderNumber int(11) auto_increment primary key, 
    orderDate datetime,
    requireDate datetime,
    shippedDate datetime,
    status varchar(15),
    comments text,
    customerNumber int(11)
);

create table products(
	productCode varchar(15) auto_increment primary key, 
    productName varchar(70),
    productLine varchar(50),
    productScale varchar(10),
    productVendor varchar(50),
    productDescription text,
    quantityInStock smallint(6),
    buyPrice double,
    foreign key (productLine) references productLines(productLine) on update cascade
);
create table productLines(
	productLine varchar(50) primary key,
    textDescription varchar(4000),
    htmlDescription mediumtext,
    image mediumblob
);
create table ordersdetails(
	orderNumber int(11) auto_increment,
    productCode varchar(15),
    quantityOrdered int(11),
    pricrEach double,
    orderLineNumber smallint(6),
    foreign key (orderNumber) references orders(orderNumber) on update cascade,
    foreign key (productCode) references products(productCode) on update cascade
);
desc orders; desc products; desc productLines; desc ordersdetails; 

drop table orders;
drop table products;
drop table productLines;
drop table ordersdetails;

alter table ordersdetails change orderLineNumber orderLineNumber int(10);
alter table products change productLine Branch varchar(50);

insert into orders
(orderDate, requireDate, shippedDate, status, comments, customerNumber)
values
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng A', '0633900100'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng B', '0633900200'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng C', '0633900300'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng D', '0633900400'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng A', '0633900500'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng B', '0633900600'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng C', '0633900700'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng D', '0633900800'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng A', '0633900900'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng B', '0633900100'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng C', '0633900110'),
('2021-01-01', '2021-01-01', '2021-01-01', 'Đã nhận hàng', 'Mặt hàng D', '0633900120');
select * from orders;

insert into productlines
(productLine, textDescription, htmlDescription, image )
value
('A', '.txt', '.html', '.img'),
('B', '.txt', '.html', '.img'),
('C', '.txt', '.html', '.img'),
('D', '.txt', '.html', '.img'),
('E', '.txt', '.html', '.img'),
('F', '.txt', '.html', '.img'),
('G', '.txt', '.html', '.img'),
('H', '.txt', '.html', '.img'),
('J', '.txt', '.html', '.img'),
('K', '.txt', '.html', '.img'),
('L', '.txt', '.html', '.img'),
('M', '.txt', '.html', '.img'),
('N', '.txt', '.html', '.img');

select * from productlines;


insert into ordersdetails
(quantityOrdered, priceEach, orderLineNumber)
values
( '10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12'),
('10', '100000','12');

select * from ordersdetails;
insert into products 
(productCode, productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice)
value 
('B001', 'Áo dai ', 'A', 'SIZE M', 'hung', 'Áo thun 3 lỗ', '200', '300000'),
('B002', 'Áo thun', 'A', 'SIZE L', 'hung', 'Áo thun ', '200', '200000'),
('B003', 'Áo', 'A', 'SIZE S', 'hung', 'Áo khoác', '200', '100000'),
('B004', 'Áo', 'A', 'SIZE M', 'hung', 'Áo sơ mi', '200', '100000'),
('B005', 'Áo', 'A', 'SIZE M', 'hung', 'Áo len', '200', '400000'),
('B006', 'Áo', 'A', 'SIZE M', 'hung', 'Áo phông', '200', '500000'),
('B007', 'Quần', 'B', 'SIZE M', 'hung', 'Quần đùi', '200', '550000'),
('B008', 'Quần', 'B', 'SIZE L', 'hung', 'Quần jean', '200', '590000'),
('B009', 'Quần', 'B', 'SIZE M', 'hung', 'Quần da', '200', '300000'),
('B010', 'Quần', 'B', 'SIZE S', 'hung', 'Quần ống rộng', '200', '300000'),
('B011', 'Giày', 'C', 'SIZE 30', 'hung', 'Giày bata', '200', '200000'),
('B012', 'Giày', 'C', 'SIZE 30', 'hung', 'Giày thể thao', '200', '100000');

select * from products;

-- xoa bang product
drop table products;
-- hoan thanh--