数据库21/10/17

数据库

DQL

单表查询

12.分页查询

• 总记录数：count 每页显示：pageSize
  总页数：；pageCount=count%pageSize==0？count/pageSize:count/pageSize+1
• limit:显示前几条
  例如：

select * from stu limit 0,3;   -- 从第0条记录开始显示，共显示3条（第一条索引为0）

• 一共有10条记录，每页显示三条

    select * from stu limit 0,3;    -- 第一页显示前三条
    select * from stu limit 3,3;    -- 第二页从第三条开始，显示3条
    select * from stu limit 6,3;    -- 第三页从第六条开始，显示3条
    select * from stu limit 9,3;    -- 第四页从第9条开始，由于只剩1条，只显示1条

• 通用表达式：

   
    select * from stu limit (pageNum-1)*pageSize,pageSize; -- pageNum表示查询的页码（查询第几页）,pageSize表示每页显示的条数

多表联合查询

数据表的关联关系

1.一对一关联

• 例如：学生-学籍，用户-用户详情
• 建立两张表的关系：
  方案一：主键关联（两张表中主键值相同的数据相对应，注意：只能其中一个使用自增）
  方案二：建立唯一外键（新的字段，加unique以保证一对一），与其他表的主键建立关联

2.一对多关联和多对一关联

• 例如：班级-学生（一对多），学生-班级（多对一）
• 在多的一端添加外键，改外键为另一个表的主键（外键可重复）

3.多对多关联

• 例如：学生-课程，会员-社团
• 方案：额外创建一张关系表，该表必须有两个字段设置为外键（可以有别的字段），分别与两张表主键关联，表叫关系表

4.外键约束

• 外键与另一张表主键的类型长度都要一致
• 方式一：constraint 外键名 foreign key 设为外键的字段名references 关联表的表名（主键名）
  例如：

create table class(
    class_id int primary key,
    class_name varchar(20) not null，
    s_id int,
    constraint FK_STUDENT_CLASS foreign key(s_id) references stu(stu_num)
    );

• 方式二：先创建表，再添加外键
  alter table 表名 add constraint FK_STUDENT_CLASS foreign key(s_id) references stu(stu_num)
• 解除外键：alter table 表名 drop foreign key 外建名；
• 添加外键约束：update table 表名 add constraint 外键名 foreign key（作为外键的字段名） references 另一张表名（表的主键名）ON UPDATE ON CASCADE ON DELETE CASCADE

5.级联操作

• 再添加外键时加入语句 ON UPDATE ON CASCADE ON DELETE CASCADE（级联修改，级联删除）
  在修改（删除）关联的主键时，另一张表的外键数据也会随着修改（删除）

6.连接查询

• 可以使用join实现多表的联合查询--联合查询
• inner join 内连接
• left join 左连接
• right join 右连接

7.内连接INNER JOIN

• 内连接不会显示不匹配的记录
• 若执行select...from 表1 inner join 表2，会获取两个表中的数据集合的笛卡尔积（表1的记录依次与表2的记录匹配
• 笛卡尔积：总数=表1总数*表2总数
• select * from 表1 inner join 表2 where 表1外键=表2主键； 这时就不会有一一匹配的情况
• 使用where设置过滤条件，先生成笛卡尔积再从笛卡尔积中过滤数据，效率低
• select * from 表1 inner join 表2 on 表1外键=表2主键；
• 使用on设置连接查询条件，先判断连接条件是否成立，若成立再组合

8.左连接（LEFT JOIN）右连接（RIGHT JOIN）

需求：请查询出所有的学生信息，如果有对应的班级信息显示出来

SELECT * FROM stu LEFT JOIN class on sid=stu_num;

• 左连接显示左表的所有数据，右连接显示右表的所有数据

数据表别名

如果在连接查询的多张数据表中存在相同名字的字段，我们可以使用表名.字段名来区分，如果表明太长则不便于SQL语句的编写，这时可以使用数据表别名

• *SELECT s. ,class_name（表示查s和class_name）
  FROM stu s（别名为s）
  INNER JOIN class c
  ON c.sid = s.stu_num;
  **

子查询（嵌套查询）

案例1：查询stu_name为zhangsan的学生的班级信息，不知道stu_id的情况下

SELECT stu_num FROM stu WHERE stu_name='zhangsan'; -- 先用name查出num
SELECT * FROM class WHERE sid=1; -- 再用班级表的外键查找班级信息

SELECT * FROM class WHERE sid=（SELECT stu_num FROM stu WHERE stu_name='zhangsan'）；

案二：查询学生年龄为21的学生的班级信息

SELECT * FROM class WHERE sid IN 
(SELECT stu_num FROM stu WHERE stu_age=21); 

• 当括号内的值不唯一是（即年龄为21的学生不唯一）要用IN