Sql初步

对于原文http://www.jianshu.com/p/520d2eda6f3e的sql创建表

存生日而不是年龄的解答：

                生日这一个值是不会变动的，而年龄却会随时间增长而变动，很明显，存一个不变动的值在数据库内更有利。

解决方案：

             根据用户信息得出出生日期再输入数据库

查询年龄的sql语句：

先创建时间类的表和数据

再进行查询

DATEDIFF函数用于计算时间差（以日记），

再换算为年后取得最小整数即可得到实岁（实际年龄）

select floor(DATEDIFF(CURDATE(),day)/365.25) from test.dates;

---

         cheers！完成！

```

    use test;

create table course

(

CNum int(8) primary key,

CName char(20),

CPlace  char(30)

);

create table teacher

(

TeaNum int(8) primary key,

TeaName char(20),

TeaBir date

);

create table TeaCla

(

teaNum int(8) ,

CNum int(8),

CTime char(40),

foreign KEY(teaNum) REFERENCES teacher(TeaNum),

foreign KEY(CNum) REFERENCES course(CNum),

primary key(teaNum,CNum)

);

create table student

(

StuNum int(8) primary key,

StuName char(20),

StuBir date,

StuCla int(8),

foreign KEY(StuCla) REFERENCES course(CNum)

);

create table StuC

(

StuNum int(8),

Course int(8),

SelTime datetime,

foreign KEY(Course) REFERENCES course(CNum),

foreign KEY(StuNum) REFERENCES student(StuNum),

primary key(StuNum,Course)

);

create table college

(

collegeID int(8) primary key,

colName char(20),

colPlace int(8)

);

create table institute

(

instituteID int(8) primary key,

insName char(20),

collegeID int(8),

foreign KEY(collegeID) REFERENCES college(collegeID)

);

create table specialty

(

specialtyID int(8) primary key,

speName char(20),

instituteID int(8),

foreign KEY(instituteID) REFERENCES institute(instituteID)

);

create table class

(

ClaNum int(8) primary key,

ClaName char(20),

specialtyID int(8),

foreign KEY(specialtyID) REFERENCES specialty(specialtyID)

);

show tables;

```

接下来进行增删查改

1.增加记录

```

use test;

insert into course values(10000000,'课程','地点');

```

2.查找记录

```

select * from course;

```

3.修改前一条记录

```

update course

set CNum=12000000,CName='数据库',CPlace='二号教学楼101'

where (CNum=10000000);

```

4.删除记录

```

delete from course where (CNum=12000000);

```

至此，数据库基本操作就算基本完成了。