本章目标
1、掌握使用sql语句修改、删除表
2、掌握添加/删除约束的sql语句
3、掌握简单子查询的用法
4、掌握IN子查询的用法
存储引擎的类型
MyISAM、InnoDB、Memory、CSV等九种
设置表的存储引擎
create table myschool(
dis int(3)
)engine=存储引擎 #myisam/innodb
插入单条数据
insert into 表名(字段名) values(值列表)
将查询结果插入新表
create table 表名(
select 列名
from 表名
where 条件
order by 列名 #desc
)
数据更新
update 表明 set 列名=()
where
删除数据记录
1,delect from 表名
where
DELETE FROM dropstudent
WHERE studentName='郭靖'
2,truncate table 表名
常用函数 聚合函数
AVG() 平均
COUNT() 行数
MAX() 最大
MIN() 最小
SUM() 求和
RAND() 返回0-1直接的某个数
练习:把成绩都降低10%后加5分,再查询及格成绩,并从高到低排序
Mysql查询语句中使用LIMIT字句限制结果集
select 字段名/列
from 表名
where 条件
group by 分组字段名
order by 排序字段名 #desc
limit (位置偏移量,行数)
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`
FROM student
WHERE gradeID=1
ORDER BY studentNo
LIMIT 2,3 #从第几条开始,显示几条 不写默认0 [位置偏移量,行数]
UPDATE student SET loginPwd='000',email='stu20000@163.com'
WHERE studentNo=20000
UPDATE `subject` SET classHour=classHour-10
WHERE classHour>200 AND gradeID=1
CREATE TABLE student_grade1(
SELECT `studentName`,`sex`,`bornDate`,`phone`
FROM student
WHERE gradeID=1
)
1、
SELECT studentNo,studentResult
FROM result
WHERE examDate='2016-02-17'
ORDER BY studentResult DESC
LIMIT 5
2、
SELECT studentName,YEAR(NOW())-YEAR(bornDate) AS 年龄,bornDate,phone
FROM student
WHERE sex='女'
ORDER BY 年龄 DESC
LIMIT 1,6
3、
SELECT YEAR(bornDate) AS 年份,COUNT(studentName) AS 人数
FROM student
GROUP BY 年份
HAVING 人数>2
4、
SELECT studentNo,MAX(studentResult)AS 最高分,MIN(studentResult) AS 最低分,
AVG(studentResult) AS 平均分
FROM result
WHERE examDate='2016-02-17'
SELECT `studentNo`,`loginPwd`,`studentName`,
`sex`,`gradeID`,`phone`,`address`,`bornDate`,
`email`,`identityCard`
FROM student
WHERE bornDate>
(SELECT bornDate
FROM student
WHERE studentName='李斯文')
SELECT MAX(studentResult) AS 最高分, MIN(studentResult) AS 最低分
FROM result
WHERE subjectNo=(SELECT subjectNo FROM `subject`WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(examDate)
FROM result
WHERE subjectNo=(SELECT subjectNo FROM `subject`WHERE subjectName='Logic Java'))
SELECT studentName
FROM student
WHERE studentNo IN(SELECT studentNo #因为返回结果不是一个 要用in子查询
FROM result
WHERE subjectNo =(SELECT subjectNo FROM `subject`WHERE subjectName='Logic Java'))
SELECT studentNo,studentName
FROM student
WHERE studentNo IN(
SELECT studentNo
FROM result
WHERE examDate IN(
SELECT MAX(examDate)
FROM result
WHERE subjectNo=(
SELECT subjectNo
FROM `subject`
WHERE subjectName='Logic Java'))
AND subjectNo=(
SELECT subjectNo
FROM `subject`
WHERE subjectName='Logic Java'))