MySQL 外键,多表查询,索引

最新推荐文章于 2024-04-12 17:28:59 发布

EdVzAs

最新推荐文章于 2024-04-12 17:28:59 发布

阅读量256

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_46131409/article/details/105800553

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一.外键约束
1.创建外键:

--每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任

--主表
  PRIMARY KEY:设为主键
    CREATE TABLE ClassCharger(
           id TINYINT PRIMARY KEY auto_increment,
           name VARCHAR (20),
           age INT ,
           is_marriged boolean--show create table ClassCharger: tinyint(1)
    );
    INSERT INTO ClassCharger (name,age,is_marriged) VALUES
            ("冰冰",12,0),
            ("丹丹",14,0),
            ("歪歪",22,0),
            ("姗姗",20,0),
            ("小雨",21,0);

--子表
  FOREIGN KEY:设为外键,在表单设置中单独写出
    [ADD CONSTRAINT name_] FOREIGN KEY fore_field_ REFERENCES tab_name_ pri_field_;
    --name_:外键的名字,删除时使用;默认为tab_name_+_ibfk_1
  ENGINE=INNODB:设置数据库的搜索引擎,加在表设置的结尾")"后
    CREATE TABLE Student(
             id INT PRIMARY KEY auto_increment,
             name VARCHAR (20),
             charger_id TINYINT,--切记:外键和关联主键的数据类型一定要一致
             --FOREIGN KEY charger_id REFERENCES ClassCharger(id)
    ) ENGINE=INNODB;
    INSERT INTO Student(name,charger_id) VALUES
            ("alvin1",2),--外键的值一定要存在于关联主键中
            ("alvin2",4),
            ("alvin3",1),
            ("alvin4",3),
            ("alvin5",1),
            ("alvin6",3),
            ("alvin7",2);

DELETE FROM ClassCharger WHERE name="冰冰";
INSERT student (name,charger_id) VALUES ("yuan",1);
  --删除居然成功,可是alvin3/5还是显示id=1;

--增加/删除外键
  --增加外键:
    ALTER TABLE tab_name_ [ADD CONSTRAINT name_] FOREIGN KEY(field_) REFERENCES tab_name_(field_);
      ALTER TABLE student ADD CONSTRAINT abc
              FOREIGN KEY(charger_id)
              REFERENCES ClassCharger(id);
  --删除外键:
    ALTER TABLE tab_name_ DROP FOREIGN KEY name_
      ALTER TABLE student DROP FOREIGN KEY abc;

2.INNODB支持的ON语句:

--外键约束对子表的含义:
  --如果在父表中找不到对应的候选键
  --则不允许在子表上进行insert/update

--外键约束对父表的含义:
  --在父表上进行update/delete时
  --如果在子表中有对应的候选键
  --父表的行为取决于:在定义子表的外键时指定的on update/on delete子句
  --如果子表中有匹配项,更新/删除时默认报错

FOREIGN KEY fore_field_ REFERENCES tab_name_ pri_field_ ON DELETE innodb_way_
FOREIGN KEY fore_field_ REFERENCES tab_name_ pri_field_ ON UPDATE innodb_way_

--innodb支持的四种方式
  --cascade方式:在父表候选键update/delete时，同步update/delete字表中匹配记录的外键字段值
    --外键的级联删除
    FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
            ON DELETE/UPDATE CASCADE
  --set null方式:在父表候选键update/delete时,将字表中匹配记录的外键字段值设为null
    --注意:子表的外键字段不能为not null
    FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
            ON DELETE/UPDATE SET NULL
  --Restrict方式:拒绝对父表进行删除更新操作(了解)
    FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
            ON DELETE/UPDATE RESTRICT
  --No action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作(了解)
    --在mysql中同Restrict
    FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
            ON DELETE/UPDATE NO ACTION

二.多表查询
1.准备表:

--准备两张表:company.employee和company.department
create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
	  age int,
	  dept_id int
);

insert into employee(emp_name,age,dept_id) values
      ('A',19,200),
	  ('B',26,201),
	  ('C',30,201),
      ('D',24,202),
      ('E',20,200),
      ('F',38,204);

create table department(
      dept_id int,
      dept_name varchar(100)
);

insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财政部');

mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | 人事部    |
|     201 | 技术部    |
|     202 | 销售部    |
|     203 | 财政部    |
+---------+-----------+
4 rows in set (0.01 sec)

2.连接查询

条件通常为tab1和tab2的关联点(相同的字段)

(1)笛卡儿积查询:

SELECT field_ FROM tab1_,tab2_
--获得tab1(m行)中记录,tab2(n行)中记录的全部组合(m*n条)构成的表
--即tab1,tab2的笛卡儿积(tab1 x tab2)

mysql> SELECT * FROM employee,department;
--相当于select employee.emp_id,employee.emp_name,
             --employee.age,department.dept_name
             --from employee,department;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技术部    |
|      1 | A        |   19 |     200 |     202 | 销售部    |
|      1 | A        |   19 |     200 |     203 | 财政部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      2 | B        |   26 |     201 |     202 | 销售部    |
|      2 | B        |   26 |     201 |     203 | 财政部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     202 | 销售部    |
|      3 | C        |   30 |     201 |     203 | 财政部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      4 | D        |   24 |     202 |     203 | 财政部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技术部    |
|      5 | E        |   20 |     200 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     203 | 财政部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技术部    |
|      6 | F        |   38 |     204 |     202 | 销售部    |
|      6 | F        |   38 |     204 |     203 | 财政部    |
+--------+----------+------+---------+---------+-----------+

(2)内连接查询:

SELECT field_ FROM tab1_,tab2_ WHERE 条件
SELECT field_ FROM tab1_ INNER JOIN tab2_ ON 条件
--相当于查询tab1 ∩ tab2(交集表示满足条件的记录的集合)
--查询两张表中都有的关联数据,相当于利用条件从笛卡尔积的结果中筛选出正确结果

tab1_.field1_:表tab1中的字段field1

select * from employee,department where employee.dept_id = department.dept_id;
--相当于select * from employee inner join department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
+--------+----------+------+---------+---------+-----------+

(3)外连接查询:

--左外连接:在内连接的基础上增加左边有右边没有的结果
  SELECT field_ FROM tab1_ LEFT JOIN tab2_ ON 条件
    --相当于查询(tab1 ∩ tab2) ∪ tab1
    --未匹配的项设为null
  select * from employee left join department on employee.dept_id = department.dept_id;
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+---------- -+
  |      1 | A        |   19 |     200 |     200 | 人事部    |
  |      5 | E        |   20 |     200 |     200 | 人事部    |
  |      2 | B        |   26 |     201 |     201 | 技术部    |
  |      3 | C        |   30 |     201 |     201 | 技术部    |
  |      4 | D        |   24 |     202 |     202 | 销售部    |
  |      6 | F        |   38 |     204 |    NULL | NULL      |
  +--------+----------+------+---------+---------+-----------+

--右外连接:在内连接的基础上增加右边有左边没有的结果
  SELECT field_ FROM tab1_ RIGHT JOIN tab2_ ON 条件
    --相当于查询(tab1 ∩ tab2) ∪ tab2
    --未匹配的项设为null
  select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+-----------+
  |      1 | A        |   19 |     200 |     200 | 人事部    |
  |      2 | B        |   26 |     201 |     201 | 技术部    |
  |      3 | C        |   30 |     201 |     201 | 技术部    |
  |      4 | D        |   24 |     202 |     202 | 销售部    |
  |      5 | E        |   20 |     200 |     200 | 人事部    |
  |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
  +--------+----------+------+---------+---------+-----------+

--全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
  SELECT field_ FROM tab1_ FULL JOIN tab2_ ON 条件
  --mysql不支持full join,使用此种方式间接实现全外连接:
    左连接 UNION [ALL] 右连接
      --注意:union与union all的区别在于union会去掉相同的纪录
  select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
                           UNION [ALL]
  select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+-----------+
  |      1 | A        |   19 |     200 |     200 | 人事部    |
  |      2 | B        |   26 |     201 |     201 | 技术部    |
  |      3 | C        |   30 |     201 |     201 | 技术部    |
  |      4 | D        |   24 |     202 |     202 | 销售部    |
  |      5 | E        |   20 |     200 |     200 | 人事部    |
  |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
  |      6 | F        |   38 |     204 |    NULL | NULL      |
  +--------+----------+------+---------+---------+-----------+

3.复合条件连接查询:

条件1 AND 条件2:同时满足条件1,条件2
条件1 OR 条件2:满足条件1或条件2

--查询年龄大于等于25岁的员工所在的部门
  SELECT DISTINCT department.dept_name
	    FROM employee,department
	    WHERE employee.dept_id = department.dept_id
	    AND [employee.]age>25;
--以内连接查询employee和department表，并按age字段的升序显示
  select employee.emp_id,employee.emp_name,
	    employee.age,department.dept_name
	    from employee,department
	    where employee.dept_id = department.dept_id
	    order by age asc;

4.子查询:

--子查询是将一个查询语句嵌套在另一个查询语句中
--内层查询语句的查询结果,为外层查询语句提供查询条件
  --关键字:IN,NOT IN,ANY,ALL,EXISTS,NOT EXISTS等
  --还可以包含比较运算符:=,!=,>,<,<>,>=,<=

--带IN关键字的子查询:指定字段属于子查询结果
  SELECT field_ FROM tab_ WHERE field_ IN (子查询句)
  --查询employee表中dept_id属于department表的记录:
    select * from employee
            where dept_id IN
            (select dept_id from department);
	+--------+----------+------+---------+
	| emp_id | emp_name | age  | dept_id |
	+--------+----------+------+---------+
	|      1 | A        |   19 |     200 |
	|      2 | B        |   26 |     201 |
	|      3 | C        |   30 |     201 |
	|      4 | D        |   24 |     202 |
	|      5 | E        |   20 |     200 |
	+--------+----------+------+---------+
	5 rows in set (0.01 sec)

--带比较运算符的子查询:
  =,!=,<>,>,>=,<,<=
  --查询员工年龄大于等于25岁的部门:
    select dept_id,dept_name from department
            where dept_id IN
            (select DISTINCT dept_id from employee where age>=25);

--带EXISTS关键字的子查询:表示子查询结果是否存在
  SELECT field_ FROM tab_ WHERE EXISTS (子查询句)
  --使用EXISTS关键字时,内层查询语句返回一个真假值
  --当返回Ture时,外层查询语句将进行查询;当返回False时,外层查询语句不进行查询
  --如果department中存在id=203的记录,查询employee:
    select * from employee
            WHERE EXISTS
            (SELECT dept_name from department where dept_id=203);
    --department表中存在dept_id=203,子句返回Ture
  --如果department中存在id=205的记录,查询employee:
    select * from employee
            WHERE EXISTS
            (SELECT dept_name from department where dept_id=205);
    Empty set (0.00 sec)

--创建表时也可以嵌套:新建表中的记录来自查询已有表的返回值
  CREATE TABLE tab_name_(查询子句)
  --将表t2中的所有记录复制到表t1:
    create table t1(select * from t2);

三.索引
1.索引(Index)

索引在MySQL中也叫"键(key)",是存储引擎用于快速找到记录的一种数据结构
表中数据量越大,索引对性能的影响越大;索引优化是优化查询性能最有效的手段,能轻易将查询性能提高几个数量级;但创建/维护索引会消耗很多时间/磁盘资源
索引相当于字典的音序表,如果查询时不使用音序表,就需要在全部内容中主页查找

2.索引语法:

--PRIMARY KEY/UNIQUE属性字段都是是索引

--创建表时创建索引:
  CREATE TABLE tab_name_ (
      field1_ type_ [完整性约束条件],
                  ...
      fieldn_ type_ [完整性约束条件],
      [UNIQUE/FULLTEXT/SPATIAL] INDEX/KEY--二者均可
      [索引名] (field_[(length_)] [ASC/DESC])
  );
  --创建普通索引:
    INDEX/KEY [索引名] (field_[(length_)] [ASC/DESC])
    --默认索引名与字段名相同
    --实例:
      CREATE TABLE emp1 (
          id INT,
          name VARCHAR(30),
          INDEX index_emp_name (name)
      );
  --创建唯一索引:唯一索引的字段不得重复
    UNIQUE INDEX/KEY [索引名] (field_[(length_)] [ASC/DESC])
    --实例:
      CREATE TABLE emp2 (
          id INT,
          name VARCHAR(30),
          bank_num CHAR(18) UNIQUE,
          UNIQUE INDEX index_emp_name (name)
      );
  --创建全文索引:给长文本字段定义该索引
    FULLTEXT INDEX/KEY [索引名] (field_[(length_)] [ASC/DESC])
    --实例:
      CREATE TABLE emp3 (
          id INT,
          name VARCHAR(30),
          resume VARCHAR(50),
          FULLTEXT INDEX index_resume (resume)
      );
  --创建多列索引:被多个字段共享(按各字段查询时,均按其共同的多列索引查询)
    INDEX/KEY [索引名] (field1_[(length)],...fieldn_[(length_)] [ASC/DESC])
      --1个n列索引创建/维护消耗小于n个单列索引,但查询效率较低
    --实例:
      CREATE TABLE emp4 (
          id INT,
          name VARCHAR(30),
          resume VARCHAR(50),
          INDEX index_name_resume (name,resume)
      );
  --创建空间索引:
    SPATIAL INDEX/KEY [索引名] (field_[(length_)] [ASC/DESC])
    --实例:
      CREATE TABLE emp4 (
          id INT,
          name VARCHAR(30),
          resume VARCHAR(50),
          SPATIAL INDEX index_name_resume (name,resume)
      );

--添加索引
  --通过CREATE添加索引:
    CREATE [UNIQUE/FULLTEXT/SPATIAL] INDEX 索引名 ON tab_ (field_[(length_)] [ASC/DESC]);
    --实例:
      CREATE INDEX index_emp_name ON emp1(name);
  --通过ALTER TABLE添加索引:
    ALTER TABLE tab_ ADD [UNIQUE/FULLTEXT/SPATIAL] INDEX 索引名 (field_[(length_)] [ASC/DESC]);
    --实例:
      ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num (bank_num);

--删除索引:
  DROP INDEX 索引名 ON tab_;
  --实例:
    DROP INDEX index_emp_name ON emp1;
    DROP INDEX bank_num ON emp2;

3.索引测试实验:

--创建表:
CREATE TABLE Indexdb.t1(id INT,name VARCHAR(20));

--存储过程:
DELIMITER $$;--更改结束符
CREATE PROCEDURE autoinsert();
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<(500000)DO
INSERT INTO Indexdb.t1 VALUES(i,"yuan");
SET i=i+1;
END WHILE;
END $$

DELIMITER ;--将结束符改回默认

--调用函数
CALL autoinsert();

--花费时间比较:
  --创建索引前:
    SELECT * FROM Indexdb.t1 WHERE id=300000;--0.32s
  --创建索引后:
    CREATE INDEX index_id ON Indexdb.t1(id);
    SELECT * FROM Indexdb.t1 WHERE id=300000;--0.00s

EdVzAs

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 外键,多表查询,索引

一.外键约束1.创建外键:--每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任--主表 --PRIMARY KEY:设为主键 CREATE TABLE ClassCharger( id TINYINT PRIMARY KEY auto_increment, name VARCHAR (20), age...
复制链接

扫一扫