数据库复习--学生课业数据库设计

首先 创建数据库：

	$con=new mysqli("localhost","root","root");
	$sql="create database keye";
	$result=mysqli_query($con,$sql);
	if($result){
		echo "创建成功";
	}else{
		echo "创建失败";
	}

然后创建一个快速连接的文件：

	$mysqli = new MySQLi("localhost","root","root");//默认的 MySQL的类，其属性与方法见手册
	if($mysqli->connect_error){//connect_error为属性，报错
		die("数据库连接失败：".$mysqli->connect_errno."--".$mysqli->connect_error);// connect_errno:错误编号
	}
	$mysqli->select_db("keye");//选择数据库
	$mysqli->query("set names 'utf8'");

新建文件 创建第一个表 添加索引及外键：

    include "conn.php";
   //建立学生表
   $sql= "create table student(
   id int(255) AUTO_INCREMENT PRIMARY KEY ,
   number INT(255) UNIQUE,
   sex INT(4),
   studentName VARCHAR (255),
   class VARCHAR (255) NOT NULL
)engine=InnoDB ";
   $resu=$mysqli->query($sql);

   //建立教室表
   $sqlCclass="create table classroom(
   id int(255) auto_increment PRIMARY KEY,
   classNumber INT(255) UNIQUE NOT NULL ,
   adress VARCHAR(255),
   stdus int(255)  DEFAULT 0
)engine=InnoDB ";
   $resu=$mysqli->query($sqlCclass);

//建立班级表
   $sqlCteacher="create table class(
   id int(255) auto_increment PRIMARY KEY,
   classNumber INT(255) UNIQUE NOT NULL ,
   className VARCHAR(255)
)engine=InnoDB ";
   $resu=$mysqli->query($sqlCteacher);

   //建立教师表
   $sqlCteacher="create table teacher(
   id int(255) auto_increment PRIMARY KEY,
   teacherNumber INT(255) UNIQUE NOT NULL ,
   teacherName VARCHAR(255),
   object VARCHAR (255)
)engine=InnoDB ";
   $resu=$mysqli->query($sqlCteacher);

   //建立课时表
$sqlCkeye="create table kecheng(
id int(255) auto_increment PRIMARY KEY,
number INT(255) UNIQUE ,
class VARCHAR(255),
object VARCHAR(255),
teacher INT(255)
)engine=InnoDB";
   $resu=$mysqli->query($sqlCkeye);

   //建立课程表
$sqlCkc="create table keshi(
id int(255) auto_increment PRIMARY KEY,
adress VARCHAR(255),
number INT(255)
)engine=InnoDB";
   $resu=$mysqli->query($sqlCkc);

   //创建外键
   $sqlIndex1="alter table student ADD INDEX a(class)";
   $mysqli->query($sqlIndex1);
   $sqlIndex2="alter table class ADD INDEX b(className)";
   $mysqli->query($sqlIndex2);
   $sqlw="ALTER TABLE student ADD CONSTRAINT c
FOREIGN KEY (class)
REFERENCES class(className)";
   $mysqli->query($sqlw);
   var_dump($mysqli);



外键知识：外键创建方式