mysql学习笔记（三）—— 练习表搭建

-- 表结构

-- 学生信息表（student）
CREATE TABLE IF NOT EXISTS `student`(
   `sno` VARCHAR(20) NOT NULL,
   `sname` VARCHAR(20) NOT NULL,
   `sbirth` DATE  NOT NULL,
   `ssex` VARCHAR(20) NOT NULL,
   PRIMARY KEY ( `sno` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 课程信息表（course）
CREATE TABLE IF NOT EXISTS `course`(
   `cno` VARCHAR(20) NOT NULL,
   `cname` VARCHAR(50) NOT NULL,
   `tno` VARCHAR(20) NOT NULL,
   PRIMARY KEY ( `cno` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 成绩信息表（score）
CREATE TABLE IF NOT EXISTS `score`(
   `sno` VARCHAR(20) NOT NULL,
   `cno` VARCHAR(20) NOT NULL,
   `score` DOUBLE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 教师信息表（teacher）
CREATE TABLE IF NOT EXISTS `teacher`(
   `tno` VARCHAR(20) NOT NULL,
   `tname` VARCHAR(50) NOT NULL,
   `tsex` VARCHAR(20) NOT NULL,
   `depart` VARCHAR(50) NOT NULL,
   PRIMARY KEY ( `tno` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 包厢信息表（box）
CREATE TABLE IF NOT EXISTS `box`(
  `boxnum` VARCHAR(50) NOT NULL,
  `boxname` VARCHAR(50) NOT NULL,
  `boxstatus` VARCHAR(20) NOT NULL,
  `boxcapacity` VARCHAR(50) NOT NULL,
  `lowestprice` float NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 客户信息表（customer）
CREATE TABLE IF NOT EXISTS `customer`(
  `custnum` VARCHAR(20) NOT NULL,
  `custname` VARCHAR(50) NOT NULL,
  `sex` VARCHAR(20) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `ismembership` VARCHAR(20) NOT NULL,
  `birthday` DATE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 订单信息表（orderinfo）
CREATE TABLE IF NOT EXISTS `orderinfo`(
  `ordernum` VARCHAR(50) NOT NULL,
  `custnum` VARCHAR(20) NOT NULL,
  `boxnum` VARCHAR(50) NOT NULL,
  `orderdate` DATE NOT NULL,
  `ticket` VARCHAR(10) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 表数据

-- 学生表（student）：
INSERT INTO student (sno,sname,sbirth,ssex) VALUES 
('01','赵雷',STR_TO_DATE('01/01/1990','%m/%d/%Y'),'男'),
('02','钱电',STR_TO_DATE('12/21/1990','%m/%d/%Y'),'男'),
('03','孙风',STR_TO_DATE('05/20/1990','%m/%d/%Y'),'男'),
('04','李云',STR_TO_DATE('08/06/1990','%m/%d/%Y'),'男'),
('05','周梅',STR_TO_DATE('12/01/1991','%m/%d/%Y'),'女'),
('06','吴兰',STR_TO_DATE('03/01/1992','%m/%d/%Y'),'女'),
('07','郑竹',STR_TO_DATE('07/01/1989','%m/%d/%Y'),'女');


-- 课程表（course）：
INSERT INTO course (cno,cname,tno) VALUES 
('c001','J2SE','t002'),
('c002','Java Web','t002'),
('c003','SSH','t001'),
('c004','Oracle','t001'),
('c005','SQL SERVER 2005','t003'),
('c006','C#','t003'),
('c007','JavaScript','t004');

-- 成绩表（score）：
INSERT INTO score (sno,cno,score) VALUES 
('01','c001',78.9),
('01','c003',59.0),
('01','c002',82.9),
('02','c001',80.9),
('02','c002',72.9),
('03','c004',81.9),
('03','c002',80.0),
('03','c003',80.0),
('03','c001',81.9),
('04','c001',60.9),
('04','c007',50.0),
('04','c006',30.0),
('04','c003',20.0),
('05','c005',76.0),
('05','c006',87.0),
('06','c007',31.0),
('06','c003',34.0),
('07','c002',89.0),
('07','c004',98.0);

-- 教师表（teacher）：
INSERT INTO teacher (tno,tname,tsex,depart) VALUES 
('t001', '刘阳','男','电子工程系'),
('t002', '陈燕','女','电子工程系'),
('t003', '胡明星','男','计算机系'),
('t004', '王萍','女','计算机系');

-- 包厢信息表（box）：
insert into box (boxnum,boxname,boxstatus,boxcapacity,lowestprice) values
('BXBH001','金牛座','空闲',22,'800'),
('BXBH002','摩羯座','空闲',16,'1200'),
('BXBH003','水瓶座','使用中',30,'2000'),
('BXBH004','狮子座','已预订',10,'800'),
('BXBH005','双鱼座','空闲',10,'1000'),
('BXBH006','射手座','已预订',12,'1200'),
('BXBH007','处女座','空闲',40,'2800'),
('BXBH008','白羊座','使用中',33,'3000'),
('BXBH009','双子座','空闲',22,'1500'),
('BXBH010','巨蟹座','已预订',18,'1400');

-- 客户信息表（customer）
insert into customer (custnum,custname,sex,phone,ismembership,birthday) values
('KHBH001','张兰','女','18990199234','是',STR_TO_DATE('1990-12-22','%Y-%m-%d')),
('KHBH002','陈晨','女','13772893413','否',STR_TO_DATE('1984-05-19','%Y-%m-%d')),
('KHBH003','李立','男','18902836723','是',STR_TO_DATE('1992-09-21','%Y-%m-%d')),
('KHBH004','孟萌','女','18990199234','否',STR_TO_DATE('1990-08-03','%Y-%m-%d')),
('KHBH005','陈军','女','18200997890','否',STR_TO_DATE('1988-05-21','%Y-%m-%d')),
('KHBH006','张诚','女','18933378865','是',STR_TO_DATE('1995-09-22','%Y-%m-%d'));

-- 订单信息表（orderinfo）
insert into orderinfo (ordernum,custnum,boxnum,orderdate,ticket) values
('DDBH001','KHBH004','BXBH005',STR_TO_DATE('2018-07-09','%Y-%m-%d'),'是'),
('DDBH002','KHBH006','BXBH001',STR_TO_DATE('2018-07-09','%Y-%m-%d'),'否'),
('DDBH003','KHBH005','BXBH002',STR_TO_DATE('2018-07-20','%Y-%m-%d'),'是'),
('DDBH004','KHBH003','BXBH009',STR_TO_DATE('2018-07-20','%Y-%m-%d'),'是'),
('DDBH005','KHBH002','BXBH007',STR_TO_DATE('2018-07-20','%Y-%m-%d'),'否');

以上查询成功执行后会创建七张表，可供查询练习使用。