Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- CD Database Schema
- -- Version 1.0
- -- Copyright (c) 2014, MySQL AB
- -- All rights reserved.
- -- Bo mon HQT CSDL
- -- Nhom 24 - Tao website ban dia CD ca nhac
- -- Thanh Vien
- -- 1. Dao Minh Hoa
- -- 2. Hoang Thi Nhu
- -- 3. @Nhung
- -- 4. Pham Tat Dat
- SET @OLD_UNIQUE_CHECKS =@@UNIQUE_CHECKS,
- UNIQUE_CHECKS = 0;
- SET @OLD_FOREIGN_KEY_CHECKS =@@FOREIGN_KEY_CHECKS,
- FOREIGN_KEY_CHECKS = 0;
- SET @OLD_SQL_MODE =@@SQL_MODE,
- SQL_MODE = 'TRADITIONAL';
- DROP DATABASE
- IF EXISTS cd;
- CREATE DATABASE cd CHARACTER SET utf8 COLLATE utf8_general_ci;
- USE cd;
- --
- -- Table structure for table `cdinfo`
- --
- DROP TABLE
- IF EXISTS `cdInfo`;
- CREATE TABLE `cdInfo` (
- `id_cd` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
- `id_kind` INT (11) UNSIGNED NOT NULL,
- `cd_code` VARCHAR (11) NOT NULL,
- `name_cd` VARCHAR (50) NOT NULL,
- `description` text,
- `singer` VARCHAR (30) NOT NULL,
- `thumbnail` text,
- `price` INT (20) NOT NULL,
- `quantityinstock` INT (11) NOT NULL,
- PRIMARY KEY (`id_cd`),
- KEY `id_kind` (`id_kind`),
- CONSTRAINT `fk_id_kind` FOREIGN KEY (`id_kind`) REFERENCES `cdKind` (`id_kind`) ON DELETE RESTRICT ON UPDATE CASCADE
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- --
- -- Table structure for table `cdkind`
- --
- DROP TABLE
- IF EXISTS `cdKind`;
- CREATE TABLE `cdKind` (
- `id_kind` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
- `name_kind` VARCHAR (50) NOT NULL,
- PRIMARY KEY (`id_kind`)
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- LOCK TABLES `cdkind` WRITE;
- INSERT INTO `cdkind` (`id_kind`, `name_kind`) VALUES
- (2, 'Nhạc Trẻ'),
- (3, 'Nhạc cách mạng'),
- (4, 'Nhạc tiá»n chiến'),
- (5, 'Phim khoa há»c viá»…n tưởng'),
- (6, 'Phim tà i liệu'),
- (7, 'Phim hà nh động'),
- (8, 'Phim hà i'),
- (9, 'Nhạc Việt nam'),
- (10, 'Nhạc quốc tế'),
- (11, 'nhạc thiếu nhi'),
- (12, 'Phim kinh dị');
- UNLOCK TABLES;
- --
- -- Table structure for table ``order``
- --
- DROP TABLE
- IF EXISTS `order`;
- CREATE TABLE `order` (
- `id_order` INT (11) NOT NULL,
- `id_customer` INT (11) NOT NULL,
- `orderDate` DATE NOT NULL,
- `requiredDate` DATE NOT NULL,
- `shippedDate` DATE DEFAULT NULL,
- `STATUS` VARCHAR (15) NOT NULL,
- `comments` text,
- PRIMARY KEY (`id_order`),
- KEY `id_customer` (`id_customer`),
- CONSTRAINT `fk_order_1` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT `fk_id_customer` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- --
- -- Table structure for table `orderdetail`
- --
- DROP TABLE
- IF EXISTS `orderDetail`;
- CREATE TABLE `orderDetail` (
- `id_order` INT (11) UNSIGNED NOT NULL,
- `cd_code` VARCHAR (11) NOT NULL,
- `quantityOrdered` INT (11) NOT NULL,
- `priceEach` DOUBLE NOT NULL,
- PRIMARY KEY (`id_order`),
- KEY `cd_code`(`cd_code`),
- CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id_order`) REFERENCES `orders` (`id_order`) ON DELETE RESTRICT ON UPDATE CASCADE
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- --
- -- Table structure for table `customers`
- --
- DROP TABLE
- IF EXISTS `customers`;
- CREATE TABLE `customers` (
- `id_customer` INT (11) NOT NULL AUTO_INCREMENT,
- `username` VARCHAR (50) NOT NULL,
- `password` VARCHAR (50) NOT NULL,
- `fullname` VARCHAR (50) NOT NULL,
- `address` VARCHAR (50) NOT NULL,
- `phone` VARCHAR (50) NOT NULL,
- `email` text NOT NULL,
- `status` INT (5) UNSIGNED NOT NULL DEFAULT '2',
- PRIMARY KEY (`id_customer`)
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- LOCK TABLES `customers` WRITE;
- INSERT INTO `customers` (`id_customer`, `username`, `password`, `fullname`, `address`, `phone`, `email`, `status`) VALUES
- (1, 'administrator', '6d626c9e354076a1ffe2b2eb46b10c76', 'Phạm Tất Đạt', '69A/38 Hoàng Văn Thái', '0963012472', 'tatdat171@gmail.com', 1),
- (2, 'tatdat171', 'c0b38c91fc630782af1935721b3ce95c', 'Phạm Tất Đạt', '69A/38 Hoàng Văn Thái', '01692019206', 'kunny171@gmail.com', 1),
- (3, 'admin23', 'd41d8cd98f00b204e9800998ecf8427e', 'Tạ Hùng Giang', 'khu sơn', '0979670703', 'he@yahoo.com', 1),
- (4, 'bachnx', '827ccb0eea8a706c4c34a16891f84e7b', 'Nguyễn Thừa Ngôn', 'vĩnh phúc', '01245678', 'bach@gmail.com', 2),
- (5, 'haanhdon', 'e10adc3949ba59abbe56e057f20f883e', 'Hà Hữu Đôn', 'hà nội', '0974136509', 'haanhdon@gmail.com', 2),
- (6, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Nguyễn Xuân Bách', 'Kiều Mai - Hà Nội', '0976256106', 'nhatgai192@gmail.com', 2),
- (7, 'ongbach12', 'd41d8cd98f00b204e9800998ecf8427e', 'Bùi Viết Nam', 'Thanh Xuân', '0913443506', 'salesoff@live.com', 2),
- (8, 'admin123456', '123456', 'demo', 'demo', '0963012472', 'demo@demo.com', 2),
- (9, 'demo1', 'e10adc3949ba59abbe56e057f20f883e', 'demo', 'demo', '0534523231', 'demo1@gmail.com', 2),
- (10, 'demo234', '25f9e794323b453885f5181f1b624d0b', 'demo123', 'demo123456', '0987654321', 'demo2@gmail.com', 2),
- (11, 'demo45', 'b6cf1f80542b430c758333ad76576f86', 'sen', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo45@live.com', 2),
- (12, 'demo10', '43514a20c7801ebd4b1e6769939dd95f', 'demo10', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo10@gmail.com', 2),
- (13, 'demo6', 'c0bb1c551d1313c106d778d016b6bbad', 'demo6', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo6@gmail.com', 2),
- (14, 'demo7', '71e781c93ace9742f8b4d179141eecd9', 'Demo', 'demo123456', '0987654321', 'demo7@live.com', 2),
- (15, 'demo8', 'e807f1fcf82d132f9bb018ca6738a19f', 'Demo', 'demo123456', '0987654321', 'demo8@demo.com', 2);
- UNLOCK TABLES;
- --
- -- Table structure for table `customersKind`
- --
- /*
- CREATE TABLE `customersKind` (
- `idnumber` INT (15) NOT NULL AUTO_INCREMENT,
- `status` INT (5) UNSIGNED NOT NULL,
- `namekind` VARCHAR (20) NOT NULL,
- PRIMARY KEY (`idnumber`),
- KEY `status` (`status`),
- CONSTRAINT `fk_customerkind_1` FOREIGN KEY (`status`) REFERENCES `customers` (`status`) ON DELETE RESTRICT ON UPDATE CASCADE
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- LOCK TABLES `customersKind` WRITE;
- INSERT INTO customersKind (status, namekind)
- VALUES
- ('1', 'Administrator');
- INSERT INTO customersKind (status, namekind)
- VALUES
- ('2', 'User Normal');
- INSERT INTO customersKind (status, namekind)
- VALUES
- ('3', 'User VIP');
- UNLOCK TABLES;
- */
- --
- -- Table structure for table `payments`
- --
- DROP TABLE
- IF EXISTS `payment`;
- CREATE TABLE `payments` (
- `id_customer` INT (11) NOT NULL,
- `checkNumber` VARCHAR(50) NOT NULL,
- `paymentDate` DATE NOT NULL,
- `amount` DOUBLE NOT NULL,
- PRIMARY KEY (`id_customer`, `checkNumber`),
- CONSTRAINT `fk_payments_1` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE
- ) ENGINE = INNODB DEFAULT CHARSET = utf8;
- LOCK TABLES `payments` WRITE;
- INSERT INTO `payments` (`id_customer`, `checkNumber`, `paymentDate`, `amount`) VALUES
- (1, 'HQ336336', '2014-04-27', 60000),
- (1, 'JM555205', '2014-04-27', 145000),
- (1, 'OM314933', '2014-04-26', 16700),
- (1, 'BO864823', '2014-04-26', 1419000),
- (1, 'HQ55022', '2014-04-27', 3264000),
- (1, 'ND748579', '2014-04-25', 333000),
- (4, 'GG31455', '2014-04-25', 45800),
- (4, 'MA765515', '2014-04-25', 82000),
- (4, 'NP603840', '2014-04-28', 75000),
- (4, 'NR27552', '2014-04-25', 448900),
- (4, 'DB933704', '2014-04-25', 195000),
- (4, 'LN373447', '2014-04-25', 470000),
- (4, 'NG94694', '2014-04-25', 400000),
- (5, 'DB889831', '2014-04-26', 502000),
- (5, 'FD317790', '2014-04-28', 140000),
- (5, 'KI831359', '2014-04-27', 178000),
- (5, 'MA302151', '2014-04-28', 346000),
- (5, 'AE215433', '2014-04-27', 1012000),
- (5, 'BG255406', '2014-04-28', 854000),
- (5, 'CQ287967', '2014-04-25', 110000),
- (6, 'ET64396', '2014-04-26', 83000),
- (6, 'HI366474', '2014-04-27', 47000),
- (6, 'HR86578', '2014-04-27', 556000),
- (6, 'KI131716', '2014-04-25', 110000),
- (6, 'LF217299', '2014-04-26', 433000),
- (7, 'NT141748', '2014-04-25', 450000),
- (7, 'DI925118', '2014-04-28', 105000),
- (7, 'FA465482', '2014-04-26', 24000),
- (7, 'FH668230', '2014-04-25', 33000),
- (7, 'IP383901', '2014-04-26', 74000),
- (7, 'DM826140', '2014-04-25', 262000),
- (7, 'ID449593', '2014-04-25', 239000),
- (7, 'PI42991', '2014-04-25', 165000),
- (7, 'CL442705', '2014-04-25', 222000),
- (7, 'MA724562', '2014-04-27', 500000),
- (7, 'NB445135', '2014-04-25', 35000),
- (8, 'AU364101', '2014-04-27', 36000),
- (8, 'DB583216', '2014-04-27', 36000),
- (8, 'DL460618', '2014-04-27', 468000),
- (8, 'HJ32686', '2014-04-28', 598000),
- (8, 'ID10962', '2014-04-28', 1100000),
- (8, 'IN446258', '2014-04-25', 65000),
- (8, 'JE105477', '2014-04-26', 120000),
- (8, 'JN355280', '2014-04-26', 495000),
- (8, 'JN722010', '2014-04-25', 402000),
- (8, 'KT52578', '2014-04-25', 638000),
- (8, 'MC46946', '2014-04-25', 354000),
- (8, 'MF629602', '2014-04-26', 2000000),
- (8, 'NU627706', '2014-04-26', 261000),
- (8, 'IR846303', '2014-04-25', 36000),
- (8, 'LA685678', '2014-04-25', 76000),
- (8, 'CN328545', '2014-04-27', 471000),
- (8, 'ED39322', '2014-04-26', 28000),
- (8, 'HR182688', '2014-04-27', 20000),
- (8, 'JJ246391', '2014-04-25', 50000),
- (9, 'FP549817', '2014-04-26', 409000),
- (9, 'FU793410', '2014-04-26', 4000),
- (9, 'LJ160635', '2014-04-25', 39000),
- (9, 'BI507030', '2014-04-25', 440000),
- (9, 'DD635282', '2014-04-25', 260000),
- (9, 'KM172879', '2014-04-26', 100000),
- (9, 'ME497970', '2014-04-27', 35000),
- (9, 'BF686658', '2014-04-25', 58000),
- (9, 'GB852215', '2014-04-26', 20000),
- (9, 'IP568906', '2014-04-26', 58000),
- (9, 'KI884577', '2014-04-25', 399000),
- (9, 'HI618861', '2014-04-27', 35000),
- (9, 'NN711988', '2014-04-25', 633000),
- (9, 'BR352384', '2014-04-25', 243000),
- (9, 'BR478494', '2014-04-26', 507000),
- (9, 'KG644125', '2014-04-27', 1260000),
- (10, 'NI908214', '2014-04-27', 386000),
- (10, 'BQ327613', '2014-04-26', 387000),
- (10, 'DC979307', '2014-04-25', 441000),
- (10, 'LA318629', '2014-04-28', 224000),
- (10, 'ED743615', '2014-04-27', 125000),
- (10, 'GN228846', '2014-04-27', 85000),
- (10, 'GB878038', '2014-04-25', 180000),
- (10, 'IL104425', '2014-04-25', 42000),
- (10, 'AD832091', '2014-04-25', 190000),
- (10, 'CE51751', '2014-04-27', 5120000),
- (10, 'EH208589', '2014-04-25', 333000),
- (11, 'GP545698', '2014-04-25', 1180000),
- (11, 'IG462397', '2014-04-28', 2030000),
- (11, 'CITI3434344', '2014-04-27', 2850000),
- (11, 'IO448913', '2014-04-27', 2480000),
- (11, 'PI15215', '2014-04-25', 420000),
- (12, 'AU750837', '2014-04-26', 151000),
- (12, 'CI381435', '2014-04-27', 47000),
- (12, 'CM564612', '2014-04-25', 22000),
- (12, 'GQ132144', '2014-04-28', 54000),
- (12, 'OH367219', '2014-04-26', 4440000),
- (13, 'AE192287', '2014-04-25', 2360000),
- (13, 'AK412714', '2014-04-27', 376000),
- (13, 'KA602407', '2014-04-25', 34000),
- (13, 'AM968797', '2014-04-27', 528000),
- (13, 'BQ39062', '2014-04-25', 471000),
- (13, 'KL124726', '2014-04-27', 484000);
- UNLOCK TABLES;
- --
- -- CREATE PROCEDURE check login and register
- --
- DROP PROCEDURE IF EXISTS `check_login`;
- DELIMITER $$
- CREATE PROCEDURE `check_login`(USER VARCHAR(50), pass VARCHAR(50))
- READS SQL DATA
- BEGIN SELECT `username`,`password` FROM customers WHERE USER=`username` AND pass=`password`;
- END$$
- DELIMITER;
- DROP PROCEDURE IF EXISTS `check_reg`;
- DELIMITER $$
- CREATE PROCEDURE `check_reg`(USER VARCHAR(50), email_check text)
- READS SQL DATA
- BEGIN SELECT `username`,`email` FROM customers WHERE USER=`username` AND email_check=`email`;
- END$$
- DELIMITER ;
- --
- -- Create VIEW
- --
- DROP VIEW
- IF EXISTS `cd_show`;
- CREATE VIEW cd_show AS SELECT
- cdinfo.id_cd AS id,
- cdinfo.name_cd AS name,
- cdinfo.description,
- cdkind.name_kind AS category,
- cdinfo.singer,
- cdinfo.quantityinstock,
- cdinfo.price
- FROM
- cdinfo
- JOIN cdkind
- WHERE
- cdinfo.id_kind = cdkind.id_kind
- ORDER BY cdinfo.id_cd ASC;
- --
- -- View tong so user hien tai
- --
- DROP VIEW IF EXISTS `total_user`;
- CREATE VIEW total_user AS SELECT COUNT(*) AS totaluser FROM customers;
- --
- -- View tong so san pham CD hien tai
- --
- DROP VIEW IF EXISTS `total_cd`;
- CREATE VIEW total_cd AS SELECT COUNT(*) AS totalcd FROM cdinfo;
- --
- -- View tong so danh muc san pham CD hien tai
- --
- DROP VIEW IF EXISTS `total_cd_kind`;
- CREATE VIEW total_cd_kind AS SELECT COUNT(*) AS totalcdkind FROM cdkind;
- --
- -- View tong so tien thu duoc trong ngay
- --
- DROP VIEW IF EXISTS `total_money_daily`;
- CREATE VIEW total_moey_daily AS SELECT SUM(amount) AS total_money_daily FROM payments WHERE paymentDate = CURDATE();
- --
- -- View tong so tien thu duoc
- --
- DROP VIEW IF EXISTS `total_money`;
- CREATE VIEW total_money AS SELECT SUM(amount) AS total_money FROM payments;
- --
- -- View tong so san pham da ban duoc
- --
- DROP VIEW IF EXISTS `total_cd_sale`;
- CREATE VIEW `total_cd_sale` AS SELECT SUM(orderdetail.quantityOrdered) AS total_cd_sale FROM orderdetail JOIN `order` WHERE `order`.id_order = orderdetail.id_order AND `order`.`STATUS`='Shipped';
- --
- -- View tong so da dat mua trong ngay
- --
- DROP VIEW IF EXISTS `total_order_daily`;
- CREATE VIEW `total_order_daily` AS SELECT COUNT(*) AS total_order_daily FROM `order` WHERE orderDate =CURDATE();
- --
- -- View 10 giap dich gan day
- --
- DROP VIEW IF EXISTS `payments_last_10`;
- CREATE VIEW `payments_last_10` AS SELECT customers.username AS customer, payments.paymentDate AS TIME, payments.amount FROM payments JOIN customers WHERE payments.id_customer = customers.id_customer ORDER BY payments.paymentDate DESC LIMIT 10;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement