## 删除数据库

DROP DATABASE t14

## 添加库T14

CREATE DATABASE t14

## 添加student表

CREATE TABLE student

(

studentNo INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

loginPwd VARCHAR(20) NOT NULL,

studentName VARCHAR(32) NOT NULL,

sex CHAR(2) DEFAULT '男' NOT NULL,

gradeId INT NOT NULL,

phone VARCHAR(32) NOT NULL,

address VARCHAR(255) DEFAULT '地址不详',

bornDate DATETIME,

email VARCHAR(32),

identityCard VARCHAR(18) UNIQUE KEY

)

## 添加grade表

CREATE TABLE grade

(

gradeId INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

gradeName VARCHAR(32) NOT NULL

)

## 添加subject表

CREATE TABLE `subject`

(

subjectId INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

subjectName VARCHAR(32),

gradeId INT,

classHour INT

)

## 添加result表

CREATE TABLE result

(

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

studentNo INT NOT NULL,

subjectId INT NOT NULL,

examDate DATETIME,

studentResult INT

)

## 添加外键约束

ALTER TABLE student

ADD CONSTRAINT FK_grade_student_gradeId FOREIGN KEY(gradeId)

REFERENCES grade(gradeId)

ALTER TABLE SUBJECT

ADD CONSTRAINT FK_grade_subject_gradeId FOREIGN KEY(gradeId)

REFERENCES grade(gradeId)

ALTER TABLE result

ADD CONSTRAINT FK_subject_result_subjectId FOREIGN KEY(subjectId)

REFERENCES SUBJECT(subjectId)

ALTER TABLE result

ADD CONSTRAINT FK_student_result_studentNo FOREIGN KEY(studentNo)

REFERENCES student(studentNo)

## 向表grade表中添加数据

INSERT INTO grade(gradeName)

VALUES('T14')

## 向表student表中添加数据

INSERT INTO student

(studentNo,loginPwd,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)

VALUES(1,'0000','张三','男',1,'188','中国','2017年7月11日17:51:15','qq@126.com','410149199901010000')

## 向表result表中添加数据

INSERT INTO result(studentNo,subjectId,examDate,studentResult)

VALUES(1,1,'2017年7月11日18:00:51',80);

## 向表subject表中添加数据

INSERT INTO SUBJECT(subjectName,gradeId,classHour)

VALUES('语文',1,60),('数学',1,60);

## 备份student表

CREATE TABLE student1

(SELECT * FROM student)

## 修改表中数据

UPDATE student SET studentName = '李四' WHERE studentNo=1;

##查询数据，并按要求升降序排列

SELECT * FROM student

WHERE gradeId=1

ORDER BY sex DESC,borndate ASC;

##将firstName和lastName列合并成’姓名‘列

SELECT firstName+'.'+lastName AS 姓名 FROM employee;

 

##查询空值，此时用is null

SELECT studentName FROM student

WHERE email IS NULL;

常用函数：

聚合函数：sum();avg();count();max();min()

字符串函数：select concat('my','s','ql') 返回 mysql

                      select insert('这是Oracle数据',3,6，‘Mysql’)  返回：这是Mysql数据库

                      select lower('MYSQL')  返回：mysql

                      select upper('mysql') 返回：MYSQL

                      select substring('JavaMysqlOracle',5,5) 返回：Mysql

时间日期函数： select datediff(now(),'2008-8-8') 返回两个时间之间相差的天数

                          select adddate(now(),5) 返回现在的时间加上5天后的时间

数学函数： ceil(); floor(); 

 

##查找出比'一一'大的学生

SELECT * FROM student

WHERE bornDate<

(

SELECT bornDate FROM student

WHERE studentName='一一'

)

ORDER BY bornDate DESC;