mysql查询并键表_Mysql复杂语句—键&表连接&子查询&索引

主要参考文章(本文主要内容来自该链接，以下为个人梳理总结)

一、键

1、定义

主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性；

外键：用来和其他表建立联系用的— 是另一表的主键, 外键可以有重复的, 可以是空值(此处涉及外键至少有两张表)

参考键图

外键一定是在从表中创建，从而找到与主表之间的联系，这里外键和主键的关系是：Id=Dept_id

PS：主键、外键和索引的区别：

区别

2、添加语句

设置主键：

create table department(  id int primary key auto_increment );

设置外键：

方式一：在创建表的时候进行添加

方式二：表已经创建好了，继续修改表的结构来添加外键

【方式一】在创建表的时候进行添加[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)

REFERENCES tbl_name (主表的字段2)[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}

CONSTRAINT symbol：可以给这个外键约束起一个名字，有了名字，以后找到它就很方便了。如果不加此参数的话，系统会自动分配一个名字。

FOREIGN KEY：将从表中的字段1作为外键的字段。

REFERENCES：映射到主表的字段2。

ON DELETE后面的四个参数：代表的是当删除主表的记录时，所做的约定。

RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。

CASCADE(级联)：如果主表的记录删掉，则从表中相关联的记录都将被删掉。

SET NULL：将外键设置为空。

NO ACTION：什么都不做。

注：一般是RESTRICT和CASCADE用的最多。

【方式二】表已经创建好了，继续修改表的结构来添加外键。ALTER  TABLE  employee  ADD  FOREIGN  KEY(dept_id)  REFERENCES  department(id);

ALTER TABLE employee：在从表employee中进行操作；

ADD FOREIGN KEY(dept_id)：将从表的字段dept_id添加为外键；

REFERENCES department(id)：映射到主表department当中为id的字段。

二、表连接

例子：

department部门表：

department部门表

employee员工表：

employee员工表：

外键对应关系为：employee.dept_id=department.id。employee.leader中的数字的含义为：生命壹号的leader是生命二号，生命二号没有leader，生命叁号的leader是生命壹号。

1、内连接——只列出这些连接表中与连接条件相匹配的数据行

eg：选出两表中e.dept_id=d.id匹配的name信息selecte.name, d.name from employee e inner  join  department done.dept_id=d.id;

结果

2、外连接

左外连接(LEFT [OUTER] JOIN)——左表列出全部，右表只列出匹配的记录

右外连接(RIGHT [OUTER] JOIN)——右表列出全部，左表只列出匹配的记录

全外连接(FULL [OUTER] JOIN)  注：MySQL5.1的版本暂不支持——两个表(全外连接)中所有符合WHERE过滤条件

交叉连接(CROSS  JOIN)——返回的是连接表中所有数据行的笛卡尔积

(其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。)

eg :SELECT   *  FROM  employee  e  RIGHT JOINdeparment dON

e.dept_id=d.id

三、子查询

不熟悉的，这几个子查询的例子都值得好好看看

我们一下面的这张员工表举例：

1

1、举例：查询月薪最高的员工的名字

上面的例子中，我们就是先通过聚合函数查出最高的月薪，然后根据这个值查出对应员工的名字。

2、举例：查询出每个部门的平均月薪

2

上面的例子中，先将部门进行分类(前提是部门不能为空)，然后分别单独求出各类中的薪水平均值。

注：这里我们没有用到子查询，因为比较麻烦。

3、举例：查询月薪比平均月薪高的员工的名字(我们知道，整体的平均工资是250)

3

疑问：如果要查询比部门平均工资高的员工，该怎么写呢？下面的这种写法是错误的：

4

四、索引

方式一：直接创建索引：CREATE   INDEX  索引名    ON    表名(列名[(length)]…);

举例：

5

然后，我们在表中可以看到新创建的索引：(我们可以在这个navicat的可视化界面中修改索引类型)

6

方式二：修改表时添加索引ALTER    TABLE  表名    ADD    INDEX    [索引名](列名[(length)]…);

方式三：创建表的时候指定索引：CREATE    TABLE   表名 ( 表名 ([...],  INDEX    [索引名](列名[(length)]…);

注意：如果要创建索引的列的类型是CHAR、VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

删除索引：DROP  INDEX  索引名  ON 表名