SQL语法

数据库的存储引擎

1、MylSAM

使用这个存储引擎，每个MylSAM 在磁盘上存储成三个文件

1、frm文件：用于存储表的定义数据

2、MYD文件：存储表的具体记录数据

3、MYI文件 ：用于存储索引

frm和MYI 可以存放在不同目录下。MYI文件用来存储索引，但是又保存了记录所在页指针、索引的结构是B+树索引。

B+树和二叉树、平衡二叉树都是一样经典的数据结构

B+树 和索引 顺序访问的方法（ISAM 这是MYISAM引擎最初的参考数据结构）演化而来

B+树中 所有的记录几点都是按照值的大小顺序存放在用一层的叶子上的，由各叶子节点指针进行连接

• 支持的数据的类型 有三种：

  1、静态固定长度表：

    ​	优点：存储的速度非常快、容易发生缓存、而且表在发生损坏后也容易修复
    
    ​	缺点：占空间、这也是默认存储的格式。
  

  2、动态可变长度表

    优点：节省空间，但是一旦出错恢复起来就比较麻烦
  

  3、压缩表

    在数据文件发送错误时，可以使用check table 工具来检查，而且还可以使用repair table 工具恢复。
  
    有一个重要的特点，就是不支持事务，但是这也意味着它的存储速度更快，如果你的读写操作允许有错误数据的话，只追求速度，可以选择当前的存储引擎。
  

2、InnoDB

InnoDB是默认的数据库存储引擎

特点：

1、可以通过自定增长列，方法auto_increment

2、支持事务，默认的事务隔离级别为可重复读取，通过MVCC（并发版本控制）来实现的。

3、使用的锁粒度为行级锁，可以支持更高的并发

4、支持外键约束，外键越是其实降低了表的查询速度，但是为了增加表之间的耦合度。

5、配合一些热备工具可以支持在线热备份

6、在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。

7、对于InnoDB类型的表，其数据的组织的形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一起，都位于B+树的叶子节点上。

当然InnoDB的存储表和索引也有两种形式：

1、共享存储表空间，所有的表和索引存放在同一表空间中

2、使用多表空间存储

当前InnoDB来说。最大的特点：1、支持事务 但是这是以损失效率换来的

3、Memory

将数据存在内存中，为了提高数据的访问速度，每个表实际上和一个磁盘文件关联。文件是frm

1、支持数据的类型有限制。比如：不支持text和BLOB类型，对于字符串类型的数据，只支持固定长度行，varchar会被自动的存储为char类型。

2、支持的锁粒度为表级锁。所以在访问量比较大时，表级锁会成为Memory存储引擎的瓶颈。

3、由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失。

4、查询的时候如果使用了临时表 而且临时表中有BLOB，TEXT的类型字段，那么这个临时表会自动转化为MyISAM类型的表，性能会急剧降低。

5、默认使用hash索引

6、如果一个内部表很大，会自动的转化为磁盘表

数据库的查询语句

-- 统计学生人数
SELECT COUNT(1) FROM student
-- 表头为cou t
SELECT COUNT(1) as 'cou nt' FROM student

-- 带有条件的查询语句 where 列名=值
-- 混合使用 and 或者 or
-- 选择科目编号为2的成绩
SELECT * from result where `subjectNO`=2 
-- 查询科目2的成绩总和
SELECT SUM(studentResult) from result WHERE `subjectNo`=2

-- 查询科目二的成绩平均分
SELECT AVG( studentResult) from result WHERE `subjectNo`=2
-- 查询科目2的学生成绩最高分
SELECT MAX( studentResult) from result WHERE `subjectNo`=2
-- 查询科目2的学生成绩最低分
SELECT MIN( studentResult) from result WHERE `subjectNo`=2
-- 查询性别为男 年级编号为1的学生
SELECT * FROM student WHERE sex='男' AND gradeid=1
-- 查询学生编号等于1000的学生 或者学生的编号=1001
SELECT * FROM student WHERE studentNo=10000 or studentNo=10001
-- 查询姓李的同学 关键字like 表示模糊查询  %表示任意长度的字符
SELECT * from student where studentName like '李%'

-- 查找姓名只有2个字的学生  _代表一个字符
SELECT * from student where studentName LIKE '__'
-- 查询成绩不是80分的人  != 或者 <>
SELECT * from result where studentResult <> 80
SELECT * from result where studentResult != 80
-- 查询成绩介于60-80之间的学生  BETWEEN 小值 and 大值
SELECT * from result where studentResult BETWEEN 60 and 80
-- 查询邮箱为空/不为空的学生 
SELECT * from student where email is null
SELECT * from student where email is  NOT null
-- 查询学生编号为10002  10007 30012的学生  in关键字
SELECT * from student where studentNo in (10002,10007,30012)


select * from student
desc student
SELECT * FROM result
desc result
SELECT * from grade
desc grade

-- 查询成绩表：找到成绩低于60分的学生，并获取学生的姓名studentName、学生编号studentNo、年级名称gradeName
-- 此问题跨越了三个表：学生表、成绩表、年级表
-- result表通过学生编号studentNo与student表相连接，student表通过年级编号gradeId连接grade表
-- 包含子查询  语句中包含一个查询语句
-- 首先查找成绩低于60分的成绩
SELECT * FROM result where studentResult < 85;
-- studentNo与student外键相关联，取studentNo 查找成绩低于60分的学生编号
SELECT studentNo FROM result where studentResult < 85;
-- 在student表中查询满足成绩低于60分的学生
-- 因为result表通过studentNo与student表相连接，先查找成绩低于60分的学生编号，再在这些编号中找到对应的学生信息。
SELECT * from student WHERE studentNo in (SELECT studentNo FROM result where studentResult < 85)

-- 需要获取学生的姓名studentName、学生编号studentNo、年级名称gradeName，发现student表中
-- 没有年级名称gradeName，因此需要查询2个student表和grade表，而student表通过年级编号gradeId连接grade表
-- 查询student表和grade表 举例
select * from student,grade WHERE student.gradeId=grade.gradeID;

-- 合并成绩低于60分的学生条件可得：
select gd.gradeID,stu.studentName,gd.gradeName
from student AS stu ,grade as gd 
WHERE stu.studentNo in
 (SELECT studentNo FROM result where studentResult < 85) AND stu.gradeId=gd.gradeID;



-- 自然内外连接均需要两表主外键关系，并且字段名一致，可以避免写条件。

-- 自然内连接查询 
-- 系统用字段名作为匹配模式来进行两表的拼接，只要两张表有主外键关系并且主键表与外键表中字段名相同，才可以拼接成功
-- NATURAL JOIN ：内连接查询 两张表都存在的数据才能被查询到，如果两个表的同一个字段名下有一张表有数据值为null，这个数据就无法被查询到

select * from student,grade WHERE student.gradeId=grade.gradeID;
-- 简化版：只要两张表有主外键关系，就可以通过NATURAL JOIN自动连接而不需要写条件
select * from student NATURAL JOIN grade;


-- 自然外连接查询：解决一方表有空值的问题，即使有null值依旧拼接
-- 自然左外连接：以左表为主表  匹配右表 如果右表中不存在当前数据  返回NULL
-- 自然右外连接：以右表为主表  匹配左表 如果左表中不存在当前数据  返回NULL
SELECT * FROM student  NATURAL LEFT JOIN result



-- 连接查询 ：内连接查询  左外连接查询  右外连接查询
-- 						两表没有主外键关系，需要写拼接条件的查询方式
-- 内外连接什么时候使用？ --判断需求不会为null的情况下使用内连接
-- 求成绩表的所对应科目，有学生有考试成绩就一定有科目，科目是不会为null的 所以使用内连接成绩表
-- 语法 select * from 表名 join 表名 on 条件（主键表的主键 = 外键表的外键列）

SELECT result.*,`subject`.subjectName 
FROM  `subject`  JOIN result 
on result.subjectNo=`subject`.subjectNo
;
-- 由于subject表和result是主外键关系，因此也可以使用自然内连接，无需写拼接条件
SELECT result.*,`subject`.subjectName 
FROM  `subject`  NATURAL JOIN result

-- 内连接查询：求成绩表的所对应科目中只选择科目1的信息: 先找出所有科目，再加条件另科目编号为1
SELECT result.*,`subject`.subjectName 
from result join `subject`  
on result.subjectNo=`subject`.subjectNo
where `subject`.subjectNo =1
ORDER BY result.studentResult ASC
-- 排序order by。必须放在where之后，因为满足条件的虚拟表形成后才能进行排序
-- ASC 升序排序  DESC 降序排序 。

-- 左外连接查询： 查询 没有考过试的学生
SELECT * from student LEFT JOIN result 
on student.studentNo=result.studentNo
where student.studentResult is null;
-- 右外连接查询： 查询 没有考过试的学生
-- 主外键表的位置互换即可
SELECT *
FROM result
RIGHT JOIN student
on student.studentNo = result.studentNo
WHERE result.studentResult is NULL

-- 分组查询  SELECT 函数(count sum age...)，分组列名   FROM 表名 GROUP BY 分组列名
-- 分组查询，不可以使用*，*表示选中所有列，不符合语法。
-- 以性别分组，并求各分组多少人
SELECT sex,COUNT(1)  from student GROUP BY sex

-- 求一年级男女各有多少人  where分组前使用，先筛选再分组
SELECT sex,COUNT(1) from student -- 先选出结果集 
where gradeId=1 -- 分组前的操作，筛选select中的结果
GROUP BY sex -- 最后分组


-- 求一年级性别人数在大于5人以上的性别列和人数列
-- 加入一年级条件
-- 根据性别分组求人数
-- 加入大于5的条件
-- 分组条件： 人数要大于5的，having分组后使用，帅选分组后的结果
SELECT sex ,COUNT(1) FROM student 
where gradeid=1  -- 筛选select 中的结果
GROUP BY sex
HAVING COUNT(1)>5 -- 筛选分组后的结果


-- 查询 成绩表 统计个科目成绩 的平均分、总和、最高成绩、最低成绩
-- 时间在2016-01-01 之后的 成绩平均分大于 70 的科目
-- 统计各科目成绩，因此按照科目分组再统计，并select科目，每一个科目求studentResult的各个聚合函数
-- 两个条件：时间在2016-01-01 之后是在分组前筛选的，然后分组、成绩平均分是先分好组再统计的，因此用having 
-- 技巧：表内可以一步找到的用where 需要进一步的使用having
SELECT subjectNo,AVG(studentResult),SUM(studentResult),MIN(studentResult),MAX(studentResult) from result
where  examDate > '2016-01-01'
GROUP BY subjectNo
HAVING AVG(studentResult )>70 

-- 如果进一步查找科目详情信息，可以使用子查询 ，先查询科目表，把上述结果写入子查询
SELECT * from `subject`
WHERE subjectNo in 
(SELECT subjectNo from result
where  examDate >= '2016-01-01'
GROUP BY subjectNo
HAVING AVG(studentResult )>70) 


-- 查询不重复的记录 DISTINCT
SELECT DISTINCT studentName FROM student

-- limit 分页查询 ：先查结果再使用limit
-- 格式:  LIMIT 数据下标,返回的条数
-- 总页数 = 总条数 % 每页显示条数 == 0 ？  总条数 / 每页显示条数 :(总条数 / 每页显示条数)+1;

-- 0，5表示 第一页显示5个人
SELECT DISTINCT studentName FROM student LIMIT 0,5
-- 当前页-1 * 每页显示条数 = 第一个参数
-- 1 -1 = 0 * 5 = 0
-- 2 -1 = 1 * 5 = 5

-- 在查询总条数时 语句条件必须与分页查询一致
SELECT * FROM student where studentName like '%张%' LIMIT 0,5
SELECT count(1) FROM student where studentName like '%张%'  -- 2