日期: 2009 年 5 月 20 日

MySQL語法

——————————————————————————–
ORDER BY
——————————————————————————–
SELECT "欄位名" FROM "表格名" [WHERE "條件"]ORDER BY "欄位名" [ASC, DESC]

——————————————————————————–
INSERT
——————————————————————————–
INSERT INTO 資料表 VALUES(值1, 值2, …, 值N) 
INSERT INTO 資料表(欄位1, 欄位2, …,欄位N) VALUES(值1, 值2, …, 值N) 
INSERT INTO 資料表[欄位列表] VALUES(值列表), (值列表)…

——————————————————————————–
UPDATE
——————————————————————————–
UPDATE 資料表 SET 欄位1= 值1, 欄位2=值2, 欄位3=值3
UPDATE 資料表 SET 欄位1= 值1, 欄位2=值2, 欄位3=值3 WHERE 條件 

——————————————————————————–
DELETE
——————————————————————————–
DELETE FROM 資料表
DELETE FROM 資料表 WHERE 條件
沒條件整個資料表刪除!!!

//======SQL查詢關聯語法1(查他介紹了哪些人)
Select I.name,M.mid,M.name
From member as I , member as M
Where I.mid = 'b0905555' and I.mid = M.introducer;
//======

//======SQL查詢關聯語法2 (會改標頭)
Select I.name AS introducer_name ,M.mid AS member_id ,M.name AS member_name
From member as I , member as M
Where I.mid = 'b0905555' and I.mid = M.introducer;
//======

//======SQL查詢關聯語法3 (查誰介紹進來的)
Select M.name,I.mid,I.name
From member as M , member as I
Where M.mid = 'b0905555' and M.introducer = I.mid;
//======

//======6個資料表
browse
cart
member
orderr
order_11
record
//======

— 資料表格式： `browse`
CREATE TABLE `browse` (
  `mId` char(8) NOT NULL DEFAULT 'a0910001',
  `pNo` char(6) NOT NULL,
  `browseTime` datetime NOT NULL,
  PRIMARY KEY (`mId`,`pNo`,`browseTime`),
  KEY `pNo` (`pNo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

— 資料表格式： `cart`
CREATE TABLE `cart` (
  `mId` char(8) NOT NULL,
  `cartTime` datetime NOT NULL,
  `tNo` char(5) DEFAULT NULL,
  PRIMARY KEY (`mId`,`cartTime`),
  KEY `tNo` (`tNo`),
  KEY `mId` (`mId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

— 資料表格式： `member`
CREATE TABLE `member` (
  `mId` char(8) NOT NULL,
  `pId` char(10) NOT NULL,
  `name` varchar(8) NOT NULL,
  `birthday` date DEFAULT NULL,
  `phome` varchar(10) DEFAULT NULL,
  `adderss` varchar(40) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `introducer` char(8) DEFAULT NULL,
  PRIMARY KEY (`mId`),
  UNIQUE KEY `pId` (`pId`),
  KEY `introducer` (`introducer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;
— 列出以下資料庫的數據： `member`
INSERT INTO `member` VALUES ('a0921111', 'A100999777', 'David', '1975-11-22', '04-2468888', '台中市中港路200號', '[email protected]', 'a0911234');
INSERT INTO `member` VALUES ('a0927777', 'B123123123', 'Su', '1982-06-06', '07-2345678', '高雄市蓮海路70號', '[email protected]', 'b0905555');
INSERT INTO `member` VALUES ('b0905555', 'C200456789', 'Jennifer', '1974-03-04', '07-2221111', '高雄市五福三路300號', '[email protected]', NULL);
INSERT INTO `member` VALUES ('b0922468', 'R100200300', 'Jackson', '1980-03-30', '06-3210321', '台南縣中華路600號', '[email protected]', NULL);
INSERT INTO `member` VALUES ('a0911234', 'A122555888', 'Tony', '1980-12-12', '02-2288009', '台北市羅斯福路200號', '[email protected]', 'a0910001');
INSERT INTO `member` VALUES ('a0910001', 'A220123456', 'Jenny', '1979-01-01', '02-2222001', '台北市中山路100號', '[email protected]', 'b0905555');

— 資料表格式： `orderr`
CREATE TABLE `orderr` (
  `pNo` char(6) NOT NULL,
  `mId` char(8) NOT NULL,
  `cartTime` datetime NOT NULL,
  `amount` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`pNo`,`mId`,`cartTime`),
  KEY `pNoFk` (`mId`,`cartTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

— 資料表格式： `order_11`
CREATE TABLE `order_11` (
  `pNo` char(6) NOT NULL,
  `mId` char(8) NOT NULL,
  `cartTime` datetime NOT NULL,
  `amount` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`pNo`,`mId`,`cartTime`),
  KEY `mId` (`mId`,`cartTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

— 資料表格式： `record`
CREATE TABLE `record` (
  `tNo` char(5) NOT NULL,
  `pNo` char(6) NOT NULL,
  `salePrice` decimal(10,2) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`tNo`,`pNo`),
  KEY `pNo` (`pNo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;