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

首先 创建数据库：

$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 cFOREIGN KEY (class)REFERENCES class(className)";   $mysqli->query($sqlw);   var_dump($mysqli);


外键知识：外键创建方式