in多个条件索引失效 mysql mysql多条件查询索引

转载

技术极客 2023-07-13 14:59:46

文章标签 in多个条件索引失效 mysql mysql 外键主键 文章分类 MySQL 数据库

索引就和字典的查字法一样，有了拼音或者部首查字法，我们就不用翻整个字典来查我们想要的字了。大大的减轻了我们的查找时间。

索引就是这么一种东西，它建立在列上，对列进行排序的一种数据结构。

索引的功能就是为了加快查询的速度。尤其是在大表查询（10000条以上的就可以算作大表了）。

什么时候使用索引？
表中的数据比较多，列中的数据比较整齐
大表

什么时候索引无效？
where条件有 <> !=
where条件有聚合函数
模糊查询 %xxx
列中数据有很多重复的

但是索引也是要占据一些空间的。查询索引也是需要时间的。
所以如果一个表中的数据，频繁发生变化，不要建索引；小表没有必要；尽量选择经常被查询的列创建索引；

索引的类型：
innodb B-tree索引
memory hash索引

创建索引：
1. 普通索引
(1)在创建表的时候，创建索引
基本没用

mysql> create table t2 (id int , name varchar(255), index(id));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

//index也可以换成key，一样的。

(2)在建表之后，再创建索引

mysql> create table t3 (id int ,name varchar(255));
mysql> create index index_id on t3(id);
mysql> desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查看索引的名字：

mysql> show index from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3    |          1 | index_id |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

删除普通索引：

mysql> drop index index_id on t3;

mysql> alter table t2 drop index id;

(2)唯一性索引
unique
约束：列里面的值是唯一的，但是空值无效。
意思就是有值的只能唯一，空值可以为多个。
创建表时添加：

mysql> create table t4 (id int , name varchar(255) unique);


create table 表名（字段 数据类型， 字段 数据类型，unique（字段名），unique（字段名））

有表后添加：

mysql> create unique index name on t4(name);
mysql> insert into t4 values (1111,"cat");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t4 values (1112,"cat");
ERROR 1062 (23000): Duplicate entry 'cat' for key 'name'
mysql>

不能重复。

mysql> insert into t4 (id) values(1114);
mysql> insert into t4 (id) values(1115);

mysql> select * from t4;
+------+------+
| id   | name |
+------+------+
| 1111 | cat  |
| 1113 | NULL |
| 1114 | NULL |
| 1115 | NULL |
+------+------+
4 rows in set (0.00 sec)

空值除外。

(3)约束：非空，列里面的值，不允许是空值。
not null

mysql> create table t4 (id int not null,name char(10));
mysql> insert into t4(name) values("niua");
ERROR 1364 (HY000): Field 'id' doesn't have a default value

唯一性约束+非空约束=主键 primary key
mysql> create table t5 (id int unique not null,name char(10));

mysql> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

或者这样。

mysql> create table t6 (id int primary key,name char(10));

主键：列的数据的唯一标识。
在主键列上的数据，是唯一的并且非空的。
ID号身份证号

外键：外键列里面的数据，必须是主键列里面的。
构建外键：
要求：
表的存储引擎是innodb
表的主键列的数据类型和外键列的数据类型是一致的。

构建2个表：
主键表：
公司的部门信息表
部门ID(主键) 部门名称(非空)

外键表：
公司的员工信息表
员工ID(主键) 名字入职时间所在部门(部门ID，外键)

mysql> create table dept (id int primary key,name varchar(255) not null);
mysql> create table emp (id int primary key , name varchar(255) not null , date date , dept int , foreign key (dept) references dept(id))engine=innodb;
//创建表时指定主键，外键时dept，来自于dept表的id，最后是指定存储引擎是innodb。如果默认就是innodb的话就不用加了。

mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1000 | hr   |
| 1001 | OPS  |
| 1002 | DEV  |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into emp values (20000,"cat","1999-12-12",1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+-------+------+------------+------+
| id    | name | date       | dept |
+-------+------+------------+------+
| 20000 | cat  | 1999-12-12 | 1000 |
+-------+------+------------+------+
1 row in set (0.00 sec)

//外键一定要在另一个表中存在。否则不能添加。

mysql> insert into emp values (20001,"cat","1999-12-12",100999);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`id`))

//外键可以重复，只要表中存在就好。


//如果有外键的情况下，是不能删除外键的这条数据的，
//比如删除dept中1000  hr这条，就会报错。因为有一条数据正在使用这个1000 的外键。
mysql> delete from dept id=1000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id=1000' at line 1


//而删除别的没有用的就可以。
mysql> insert into dept values (1111,"KKK");
Query OK, 1 row affected (0.01 sec)

mysql> delete from dept where id=1111;
Query OK, 1 row affected (0.01 sec)

删除外键：

mysql> show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `date` date DEFAULT NULL,
  `dept` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept` (`dept`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`id`)   //这行就是外键的规则
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table emp drop foreign key emp_ibfk_1;
//删除外键

mysql> insert into emp values (20002,"pig","2001-10-10",1000000);
mysql> select * from emp;
+-------+------+------------+---------+
| id    | name | date       | dept    |
+-------+------+------------+---------+
| 20000 | cat  | 1999-12-12 |    1000 |
| 20001 | dog  | 2000-11-11 |    1000 |
| 20002 | pig  | 2001-10-10 | 1000000 |
+-------+------+------------+---------+
3 rows in set (0.00 sec)

//删除外键后就没有那种相互引用的关系了，所以可以添加没有的东西，但是这时如果想重新添加外键，会因为有没有在外键那个表中的内容而不能添加。

注：添加外键后，就会自动创建一个索引，删除外键之后索引并不会被删除。

mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1000 | hr   |
| 1001 | OPS  |
| 1002 | DEV  |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-------+------+------------+---------+
| id    | name | date       | dept    |
+-------+------+------------+---------+
| 20000 | cat  | 1999-12-12 |    1000 |
| 20001 | dog  | 2000-11-11 |    1000 |
| 20002 | pig  | 2001-10-10 | 1000000 |
+-------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> alter table emp add foreign key (dept) references dept(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`#sql-658_4`, CONSTRAINT `#sql-658_4_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `dept` (`id`))
//1000000不在表中外键添加失败

删除那个数据

mysql> delete from emp where name="pig";

mysql> alter table emp add foreign key (dept) references dept(id);

再添加就可以了。

级联修改和级联删除

mysql> alter table emp add foreign key (dept) references dept(id) on delete cascade on update cascade;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0
//添加级联规则

mysql> update dept set id=1111 where name="hr";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1001 | OPS  |
| 1002 | DEV  |
| 1111 | hr   |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-------+------+------------+------+
| id    | name | date       | dept |
+-------+------+------------+------+
| 20000 | cat  | 1999-12-12 | 1111 |
| 20001 | dog  | 2000-11-11 | 1111 |
+-------+------+------------+------+
2 rows in set (0.00 sec)

//修改hr的id为1111 ，另一个表emp也发生改变了

删除包含外键的表时，外键表本身不会变。删除外键表时，所有包含外键的信息删除。

mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1001 | OPS  |
| 1002 | DEV  |
| 1111 | hr   |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-------+------+------------+------+
| id    | name | date       | dept |
+-------+------+------------+------+
| 20001 | dog  | 2000-11-11 | 1111 |
+-------+------+------------+------+
1 row in set (0.01 sec)
//上边是原始数据
mysql> delete from  emp where id = 20001; //删除了empty中数据

mysql> select * from emp;
Empty set (0.00 sec)
//emp 为空
mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1001 | OPS  |
| 1002 | DEV  |
| 1111 | hr   |
+------+------+
3 rows in set (0.01 sec)
//外键表没变化

mysql> insert into emp values(20000,"alice","2001-11-11",1111);
//又添加一条数据

mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1001 | OPS  |
| 1002 | DEV  |
| 1111 | hr   |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-------+-------+------------+------+
| id    | name  | date       | dept |
+-------+-------+------------+------+
| 20000 | alice | 2001-11-11 | 1111 |
+-------+-------+------------+------+
1 row in set (0.00 sec)
//以上为原始数据
mysql> delete from dept where id=1111;
//删除外键表中数据
mysql> select * from dept;
+------+------+
| id   | name |
+------+------+
| 1001 | OPS  |
| 1002 | DEV  |
+------+------+
2 rows in set (0.00 sec)
//外键表中的1111被删除了
mysql> select * from emp;
Empty set (0.00 sec)
//emp也为空

主键也算是一种特殊的索引，如果创建了主键，就自动创建了主键索引，主键索引一定是唯一性索引，但是唯一性所以不一定是主键索引，一个表中可以有多个唯一性索引，但是只能有1个主键。主键列不允许为空值。

相关主键的代码分为两部分。
一个是建立唯一索引，另一部分就是添加约束。
因此主键是由索引和约束组合而成的。

	定义	作用	个数
主键	唯一标识一条记录，不能有重复，不允许为空	保证数据完整性	只能有一个主键
外键	另一表的主键，可以重复，允许为空	和其他表建立联系	可以有多个外键
索引	没有重复值，但可以有一个空值	提高查询排序的速度	可以有多个唯一索引

可以查看一下效率和执行时间。

mysql> set profiling=1;
mysql> select * from shop;
mysql> desc shop;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | YES  | MUL | NULL    |       |
| name   | varchar(255) | YES  |     | NULL    |       |
| proce  | float(5,2)   | YES  |     | NULL    |       |
| city   | varchar(255) | YES  |     | NULL    |       |
| street | varchar(255) | YES  |     | NULL    |       |
| date   | date         | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select id from shop;l
mysql> select * from shop where id = 1003;
mysql> select id from shop where id = 1003;

mysql> select * from shop ;
mysql> select * from shop where id = 1002;

mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00110900 | select * from shop                  |
|        2 | 0.00107725 | desc shop                           |
|        3 | 0.00048500 | select id from shop                 |
|        4 | 0.00097750 | select * from shop where id = 1003  |
|        5 | 0.00049100 | select id from shop where id = 1003 |
|        6 | 0.00009550 | select * from shop                  |
|        7 | 0.00053375 | select * from shop where id = 1002  |
+----------+------------+-------------------------------------+

可以看到这个表的id有索引，可以看到最后两次的结果，按理说查询全表的速度要高于索引，但是现在看最后两次，查询索引的时间反而比全表查询要多，因为查询索引也要时间，而且说明小表不需要索引，就好比5页的书，有个30页的目录。

使用优化器 explain

mysql> explain select * from shop \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: NULL

当type 为all的时候说明这条语句是非常烂的语句。

自增：

mysql> create table t6 (id int primary key auto_increment,name char(10));
mysql> desc t6;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> insert into t6(name) values("name1");
mysql> insert into t6(name) values("name2");
mysql> insert into t6(name) values("name3");


mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
+----+-------+
//没有指定id的值，默认从1开始加

mysql> insert into t6 values(10,"name3");
mysql> insert into t6(name) values("niub4");

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
| 10 | name3 |
| 11 | name4 |
+----+-------+
//出现断档的按最大的加

mysql> delete from t6 where id=11;
mysql> insert into t6(name) values("name5");
mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
| 10 | name3 |
| 12 | name5 |
+----+-------+
//删除也不会清空记录，而是会继续加。

mysql> truncate t6;
mysql> select * from t6;
Empty set (0.00 sec)

mysql> insert into t6 (name) values ("name6");
mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | name6 |
+----+-------+
//清空操作会让这个重新开始计算。

默认值
default

mysql> desc t7;
+-------+--------------+------+-----+----------+-------+
| Field | Type         | Null | Key | Default  | Extra |
+-------+--------------+------+-----+----------+-------+
| id    | int(11)      | YES  |     | NULL     |       |
| age   | int(11)      | YES  |     | 20       |       |
| city  | varchar(255) | YES  |     | shenyang |       |
+-------+--------------+------+-----+----------+-------+
3 rows in set (0.01 sec)
//这个很好理解，没有指定值按照这个默认值添加数据。
mysql> insert into t7 (id) values (555);
mysql> select * from t7;
+------+------+----------+
| id   | age  | city     |
+------+------+----------+
|    1 |   20 | shenyang |
|    1 |   25 | shanghai |
|  555 |   20 | shenyang |
+------+------+----------+
3 rows in set (0.00 sec)
//如果指定就按照指定的来记录

多表查询：
原始数据

mysql> select * from likesomething;
+------------+-------+
| sid        | likes |
+------------+-------+
| 2019010101 | eat   |
| 2019010102 | tell  |
| 2019010103 | phone |
+------------+-------+
3 rows in set (0.01 sec)

mysql> select * from student ;
+------------+-------+-------------+------+------+
| sid        | name  | tel         | sex  | age  |
+------------+-------+-------------+------+------+
| 2019010101 | alice | 18612345678 | m    |   20 |
| 2019010102 | kitty | 18612345699 | w    |   22 |
| 2019010103 | jack  | 18612342399 | m    |   22 |
| 2019010104 | sam   | 18616742399 | m    |   21 |
+------------+-------+-------------+------+------+
4 rows in set (0.00 sec)

mysql> select * from serce;
+------------+---------+------+---------+
| sid        | english | math | history |
+------------+---------+------+---------+
| 2019010101 |      80 |   90 |      70 |
| 2019010102 |      90 |   90 |      60 |
| 2019010103 |      93 |   91 |      88 |
+------------+---------+------+---------+
3 rows in set (0.00 sec)

3个表，学生表，成绩表，爱好表。

mysql> desc likesomething;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| sid   | int(11)      | YES  | MUL | NULL    |       |
| likes | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc student ;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sid   | int(11)       | NO   | PRI | NULL    |       |
| name  | varchar(255)  | NO   |     | NULL    |       |
| tel   | varchar(255)  | NO   |     | NULL    |       |
| sex   | enum('m','w') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc serce;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| sid     | int(11) | YES  | MUL | NULL    |       |
| english | int(11) | YES  |     | NULL    |       |
| math    | int(11) | YES  |     | NULL    |       |
| history | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

//一些格式

1、内连接：

mysql> select student.sid ,name ,math from student,serce where student.sid=serce.sid;
+------------+-------+------+
| sid        | name  | math |
+------------+-------+------+
| 2019010101 | alice |   90 |
| 2019010102 | kitty |   90 |
| 2019010103 | jack  |   91 |
+------------+-------+------+
3 rows in set (0.00 sec)
//查询成绩名字和sid   这样讲两个表相连，其中连接的方式是相同的sid，也就是外键。
//student.sid，因为2个表相连会出现2个sid，就会乱，所以要指定输出某个表的sid。

//from  表1，表2 where 表1外键=表2外键。（连接条件）

mysql> select student.sid ,name ,math from student inner join serce on  student.sid=serce.sid;
+------------+-------+------+
| sid        | name  | math |
+------------+-------+------+
| 2019010101 | alice |   90 |
| 2019010102 | kitty |   90 |
| 2019010103 | jack  |   91 |
+------------+-------+------+
3 rows in set (0.01 sec)

//这个也是连接的方式，和上边的等价。只不过语法结构不同。
//from  表1 inner join  表2 on  连接条件。

两个表连接后就相当于一个大表了。可以进行表的所有操作。

mysql> select student.sid ,name ,math from student inner join serce on  student.sid=serce.sid where math >90;
+------------+------+------+
| sid        | name | math |
+------------+------+------+
| 2019010103 | jack |   91 |
+------------+------+------+

2、外部连接：

（1）左连接：
表1 left outer join on 表2
以左边为主的。
表1 在左边。表2在右边

outer可以省略

mysql> select student.sid , name , math from student left outer join  serce on student.sid=serce.sid;
+------------+-------+------+
| sid        | name  | math |
+------------+-------+------+
| 2019010101 | alice |   90 |
| 2019010102 | kitty |   90 |
| 2019010103 | jack  |   91 |
| 2019010104 | sam   | NULL |
+------------+-------+------+
4 rows in set (0.00 sec)
//这个语句中student在left outer join的左边以这个为主，student 有4条数据，serce有3条数据，所以最后一个为空。

（2）右连接：

表1 right outer join 表2
以右边为主。

outer可以省略

mysql> select student.sid , name , math from student right outer join  serce on student.sid=serce.sid;
+------------+-------+------+
| sid        | name  | math |
+------------+-------+------+
| 2019010101 | alice |   90 |
| 2019010102 | kitty |   90 |
| 2019010103 | jack  |   91 |
+------------+-------+------+
//还是那个，左边的为setudent 右边的是serce，右连接以右边为主，为空的那个就不显示，只有3条。

另一种：
原始数据

mysql> select * from animal2;
+------+-------+-----------+
| id   | name  | city      |
+------+-------+-----------+
| 1001 | dog   | chongqing |
| 1002 | pig   | shanghai  |
| 1004 | snick | shandong  |
+------+-------+-----------+

mysql> select * from animal1;
+------+------+-----------+
| id   | name | city      |
+------+------+-----------+
| 1000 | cat  | beijing   |
| 1001 | dog  | chongqing |
| 1002 | pig  | shanghai  |
| 1003 | fish | liaoning  |
+------+------+-----------+
这两个表中油相同的数据 1001和1002.

内连接：
内连接取交集，取相同的部分

mysql> select * from animal1 a1 inner join animal2 a2 on a1.id=a2.id;
+------+------+-----------+------+------+-----------+
| id   | name | city      | id   | name | city      |
+------+------+-----------+------+------+-----------+
| 1001 | dog  | chongqing | 1001 | dog  | chongqing |
| 1002 | pig  | shanghai  | 1002 | pig  | shanghai  |
+------+------+-----------+------+------+-----------+

其中的animal1 a1 和 animal2 a2 是起别名，在后边的条件中就可以直接写a1 和a2 了。

in多个条件索引失效 mysql mysql多条件查询索引_mysql

外连接：

（1）左外连接

animal1 在左边以左边为主。

mysql> select * from animal1 a1 left outer join animal2 a2 on a1.id=a2.id;
+------+------+-----------+------+------+-----------+
| id   | name | city      | id   | name | city      |
+------+------+-----------+------+------+-----------+
| 1001 | dog  | chongqing | 1001 | dog  | chongqing |
| 1002 | pig  | shanghai  | 1002 | pig  | shanghai  |
| 1000 | cat  | beijing   | NULL | NULL | NULL      |
| 1003 | fish | liaoning  | NULL | NULL | NULL      |
+------+------+-----------+------+------+-----------+
4 rows in set (0.00 sec)
以左为主，4条数据，索然后边的为空了、

in多个条件索引失效 mysql mysql多条件查询索引_主键_02

（2）右外连接

mysql> select * from animal1 a1 right outer join animal2 a2 on a1.id = a2.id;
+------+------+-----------+------+-------+-----------+
| id   | name | city      | id   | name  | city      |
+------+------+-----------+------+-------+-----------+
| 1001 | dog  | chongqing | 1001 | dog   | chongqing |
| 1002 | pig  | shanghai  | 1002 | pig   | shanghai  |
| NULL | NULL | NULL      | 1004 | snick | shandong  |
+------+------+-----------+------+-------+-----------+
3 rows in set (0.00 sec)
正好相反以右边为主，3条数据，左边的1为空

in多个条件索引失效 mysql mysql多条件查询索引_mysql_03

三个表联结：

mysql> select s.sid, name ,math,likes  from
     > (student s inner join serce on s.sid=serce.sid)
     > inner join likesomething l1 on l1.sid=s.sid 
     >;
+------------+-------+------+-------+
| sid        | name  | math | likes |
+------------+-------+------+-------+
| 2019010101 | alice |   90 | eat   |
| 2019010102 | kitty |   90 | tell  |
| 2019010103 | jack  |   91 | phone |
+------------+-------+------+-------+
3 rows in set (0.00 sec)

笛卡尔积
没有指定联结条件的查询。

会输出所有行的乘积。
student 4行 serce 3行 3x4=12 行。

mysql> select student.sid,math from student, serce;
+------------+------+
| sid        | math |
+------------+------+
| 2019010101 |   90 |
| 2019010101 |   90 |
| 2019010101 |   91 |
| 2019010102 |   90 |
| 2019010102 |   90 |
| 2019010102 |   91 |
| 2019010103 |   90 |
| 2019010103 |   90 |
| 2019010103 |   91 |
| 2019010104 |   90 |
| 2019010104 |   90 |
| 2019010104 |   91 |
+------------+------+
12 rows in set (0.00 sec)

子查询：
子查询：
里层的查询结果，是外层的查询条件。

需求：shop表，输出商品价格最贵的人名？

(1)使用比较运算符
> < = …
条件限制：里层的查询结果输出是1个值。

mysql> select name from shop where proce=(select max(proce) from shop);
+---------+
| name    |
+---------+
| zhoushi |
+---------+

里层的查询结果输出是1个值。

mysql> select name from shop where proce=(select proce from shop where proce>=20);
ERROR 1242 (21000): Subquery returns more than 1 row

(2)使用 IN , not IN 关联子查询的多个值

mysql> select name from shop where proce in (select proce from shop where proce>=20);
+---------+
| name    |
+---------+
| 6niu6   |
| maba    |
| zhaojiu |
| zhoushi |
+---------+

mysql> select name from shop where proce not in (select proce from shop where proce>=20);
+--------+
| name   |
+--------+
| name   |
| .1niu1 |
| .2niu2 |
| .3niu3 |
| 4niu4  |
| 5niu5  |
| wangqi |
+--------+
7 rows in set (0.00 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。