MySQL第二章2--2

CREATE TABLE result(
     studentNo INT(6) PRIMARY KEY COMMENT'学号',
     subjectNo VARCHAR(50) COMMENT'科目',
     examDate DATETIME COMMENT'考试日期',
     studentResult INT COMMENT'结果'
) COMMENT='成绩表'
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10000','1','2016-02-15 00:00:00','71');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10000','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10001','1','2016-02-17 00:00:00','46');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10002','1','2016-02-17 00:00:00','83');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10003','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10004','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10005','1','2016-02-17 00:00:00','95');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10006','1','2016-02-17 00:00:00','93');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10007','1','2016-02-17 00:00:00','23');
#添加成绩表主外键
#主键约束:学号，课程编号和日构成组合主键
ALTER TABLE `result` ADD CONSTRAINT pk_sno_sno_date
PRIMARY KEY result(`studentNo`,`subjectNo`,`examDate`)

#外键约束:主表student和从表result通过studentNo字段建立主外键关联
ALTER TABLE students ADD CONSTRAINT fk_result_student1
FOREIGN KEY(`studentsNo`)
REFERENCES `students`(`studentsNo`)

#统计 某列 产生一个结果
SELECT AVG(`studentResult`) AS '平均成绩',
       COUNT(*) AS 参加人数,
       MAX(studentResult) AS 最高,
       MIN(studentResult) AS 最低,
       SUM(studentResult) AS 总和
FROM `result`

#字符串连接
SELECT CONCAT('周','大','刚')
#字符串替换   元字符  开始 长度  新字符
SELECT INSERT ('刘睿泽',1,2,'刘瑞择')
#转小写
SELECT LOWER ('HELLO MYSQL')
#转大写
SELECT UPPER('hello mysql')
SELECT UPPER(LOWER('hello mysql'))
#字符串截取
SELECT LOWER(SUBSTRING('HELLO MYSQL'7,5))
#截取字符转小写
SELECT LOWER(SUBSTRING('HELLO MYSQL'7,5))
#常用函数----时间日期函数
#获取当前日期
SELECT CURDATE()
#获取当前时间
SELECT CURTIME()
#获取当前日期和时间
SELECT NOW()
#返回日期date为一年中的第几周
SELECT WEEK(NOW())
#返回日期date的年份
SELECT YEAR(NOW())
#返回时间time的小时值
SELECT HOUR(NOW())
#返回时间time的分钟值
SELECT MINUTE(NOW())
#返回日期参数date1和date2之间相隔的天数
SELECT DATEDIFF(NOW(),'2008-8-8')
#计算日期参数date加上n天后的日期
SELECT ADDDATE(NOW(),5)

#常用数学函数
#返回大于或等于数值x的最小整数
SELECT CEIL(2.3)
#返回小于或等于数值x的最大整数
SELECT FLOOR(2.3)
#返回0~1间的随机数
SELECT RAND()

#把成绩都降低10%后再加5分,再查询及格成绩,并按成绩从高到低排序
SELECT *,studentResult*(1-0.1)+5
FROM `result`
WHERE studentResult*(1-0.1)+5>=60
ORDER BY `studentResult` DESC
#MySQL 查询语句中使用LIMIT子句限制结果集  偏移量,显示行数
#查询所有年级编号为1的学员信息 按学号升序
SELECT * FROM student1
WHERE gradeID=1
ORDER BY studentNo
LIMIT 3,3
#查看年龄比李斯文年级小的学生,要求显示学生信息
SELECT borndate FROM student1 WHERE studentName='李斯文'
SELECT * FROM student1
WHERE borndate>'1993-07-23 00:00:00'

#采用子查询
SELECT * FROM student1
WHERE borndate>(
                SELECT borndate FROM student1 WHERE studentName='李斯文'
               )