MySQL查询总结(含优化理解)_DQL_1

前言

通过这篇文章你将收获：批量插入数据的简单理解、DQL最基础的查询与匹配语句复习。
都是最基础的语法，但是网上整体总结的太少了，而且部分有错。应朋友的要求，将DQL常用命令整理了一份，命令太多了，还没整理完。每一条SQL语句都是在小编自己的数据库中跑过的，也会写出自己对于其中语句的一些看法。本人也是小菜鸟一枚，如果有错误还请留言，感谢各位大佬指正！

先给出总结的xmind导图（未完成）：

（右击图片另存为可以保存）
图片说明

DQL

DQL即Data Query Language数据库查询语言。关键字：SELECT ... FROM ... WHERE。即通过select 等字段从数据库中查询想要的信息，工作中涉及到MySQL的初期都是看表和查询，业务中对数据库最多的操作也是查询，所以DQL的编写很重要。

即使你是软件开发工程师，也要在保证DQL正确的基础上，使查询效率要足够高。即针对已有的表结构和信息，对功能正确的SQL进行适当的优化。

创建表

首先，我们简单地创建一个表tb_name，记录人的id,姓名，性别，年龄，地址；接下来的操作都将对这张表进行。

#创建表
DROP TABLE IF EXISTS tb_name ;
CREATE TABLE tb_name (
id INT PRIMARY KEY COMMENT '主键',
username  VARCHAR(32) NOT NULL COMMENT '姓名',
sex VARCHAR(20) NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
addr VARCHAR(50) NOT NULL COMMENT '地址' ,
INDEX (username ,sex)
);

创建存储过程

到了插入数据环节，因为太懒不想一条条插入，所以写个存储过程帮我们插入大量数据。

什么是存储过程

百度百科：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

MySQL存储过程有什么注意事项

1.首先，我们在写存储时，DELIMITER ##语句很关键，因为平时为了避免MySQL长事务的引起 (长事务是什么？危害？如何避免？如果忘了请点击链接) ，会默认使用自动提交，即：set autocommit=1；即每一条语句都会自动提交。平时SQL语句都是以';'结尾，表示一句SQL到此结束，然后就自动提交了。而存储过程需要一段语句一起提交，再不想将autocommit改为0时，就需要用到DELIMITER。

DELIMITER是界定符，DELIMITER ## 表示从现在开始，只有遇到“##”才是完整的SQL语句，当然符号可以自己随意定义。

2.MySQL的 declare一定要写在set前面,即在一个小段落里，要先把参数全部定义再开始使用。不明白逻辑，今天被这个坑了，调试了很久（小编的MySQL是5.5.15版本）。

3.MySQL的if语句一定要以end if结尾，大概格式为 if......then...elseif..then..else..then...end if;

4.当一次插入大量数据时，最好分批提交，每500次提交一次，语句为：

IF id%500=0 THEN COMMIT; END IF ;

为什么要分批提交呢，也是避免长事物的发生，怕生成较大的回滚段。长事物问题链接

企业中为什么很少用存储过程

1.不利于维护

假如存储过程出错了，或者业务逻辑变更了；很少定位到数据库中的存储过程中，即排查问题时难度更大，不利于快速迭代的互联网。

2.存储过程会占用空间

业务代码写在普通的程序中，然后统一地被仓库管理，如果是存储过程，直接存储在数据库中，会占用数据库的空间，且非常不利于整体代码的管理。

3.迁移性不强

理由同上，同样是业务代码，如果用存储过程写，迁移的时候需要将数据库的存储过程也进行复制迁移。

存储过程代码：

# MySQL存储过程笔记：
# declare一定要写在set前面,
# if......then...elseif...else.....end if

#批量插入存储过程
DROP PROCEDURE IF EXISTS insert_people;
DELIMITER ##
CREATE PROCEDURE insert_people(IN start_id INT,IN num INT )
BEGIN
    DECLARE id INT;
    DECLARE sex  VARCHAR(20) DEFAULT '男';
    DECLARE addr VARCHAR(32) DEFAULT '北京'; 
    DECLARE username  VARCHAR(32);    
    DECLARE age INT ;
    DECLARE username_string VARCHAR(50);
    DECLARE rand_num1 INT;
    DECLARE rand_num2 INT;
    DECLARE count_num INT DEFAULT num + 1;
    SET id = start_id;
    SET username_string ='张三不是丰好好学数据库您将受益无穷%_';
    REPEAT         
        SET rand_num1 = FLOOR(1+RAND()*19);
        SET rand_num2 = FLOOR(1+RAND()*19);        
        SET username  = CONCAT(SUBSTRING(username_string,rand_num1,1),MID(username_string,rand_num2,1));
        IF id%3=0 THEN SET addr='广州';SET sex='未知'; ELSEIF id%2=0 THEN SET addr='北京';SET sex='女';ELSE SET addr='广州';SET sex='男';  END IF ;
        SET age = FLOOR(1+RAND()*99);
        INSERT IGNORE INTO tb_name  VALUES(id,username,sex,age,addr); 
        SET id = id + 1;
        IF id%500=0 THEN COMMIT; END IF ;
        UNTIL id = count_num
    END REPEAT;
    COMMIT;
END ##
DELIMITER ;

基础的查询

用*查所有数据

SELECT * FROM tb_name;

用全部字段查所有数据

SELECT id,username,sex,age,addr FROM tb_name;

查所有的年龄信息

select age FROM tb_name；

查所有的性别

SELECT sex FROM tb_name;

深入思考：select * 和select 字段平时该如何取舍？

从效率上考虑

如果是查询全部信息，功能和查询效率上两者没有差别

        2. 如果只是查询部分信息，且该信息有索引的话，select字段效率更高

从可读性考虑

1.select字段能更清楚地告诉我们，这个表到底有什么字段，更方便进行处理；而select * 不行

从维护上考虑

1.如果表结构有更改，比如属性名修改了、属性新增或删除了，select *可以避免相应的维护；而select 字段需要程序员手动维护

从便捷上考虑

1.select * 只需要敲一个*，即可查询所有信息；而select 字段需要我们对着表复制粘贴，更加复杂麻烦

对结果去重

可以用distinct对结果集合去重，即重复的不再显示；相应的，查询性能会降低。

--        显示全部年龄，重复的不再显示
SELECT DISTINCT age FROM tb_name;

--        显示全部性别，重复的不再显示
SELECT DISTINCT sex FROM tb_name;

where条件筛选

like通配符

1.直接使用like语句，等同于=

2.like与通配符一起使用，可以模糊匹配，即模糊查询

%（百分号）匹配零个或者多个任意字符

_（下划线）匹配一个任意字符

3.like与通配符和转义符使用，在通配符前加/ 后面加escape ’/‘；可以查询通配符相关的信息

#like通配符
SELECT * FROM tb_name WHERE username LIKE'张%';

SELECT * FROM tb_name WHERE username LIKE '%张%';

SELECT * FROM tb_name WHERE username LIKE'%张';

SELECT * FROM tb_name WHERE username LIKE '张_';

SELECT * FROM tb_name WHERE username LIKE '张_丰';

SELECT * FROM tb_name WHERE username LIKE '_三';

SELECT * FROM tb_name WHERE addr LIKE '广州';

SELECT * FROM tb_name WHERE addr NOT LIKE '广州';

SELECT * FROM tb_name WHERE username LIKE '%/%' ESCAPE '/'; 

SELECT * FROM tb_name WHERE username LIKE '%/_' ESCAPE '/';

比较运算符

MySQL的比较运算符包括：大于、小于、等于、不等于、大于等于、小于等于。

#比较运算符
SELECT * FROM tb_name  WHERE sex = '男';
SELECT * FROM tb_name  WHERE sex != '未知';
SELECT * FROM tb_name  WHERE age > 9;
SELECT * FROM tb_name  WHERE age >= 9;
SELECT * FROM tb_name  WHERE age < 9;
SELECT * FROM tb_name  WHERE age <= 9;

逻辑运算符

逻辑运算符是用来拼接其他条件的，用and或者or来连接两个条件。and表示逻辑与，or表示逻辑或。

#逻辑运算符
SELECT * FROM tb_name  WHERE age >= 3 AND age <= 9;
SELECT * FROM tb_name  WHERE age >= 80 OR age <= 18;
SELECT * FROM tb_name  WHERE sex = '男' AND age <= 9;
SELECT * FROM tb_name  WHERE sex = '男' OR age <= 9;
SELECT * FROM tb_name  WHERE sex ='男' OR sex ='女';
SELECT * FROM tb_name  WHERE addr = '广州' OR addr='北京';

IN

IN字段指定多个值查询，格式为：field IN (value1,value2,value3,....)；即在小括号里的都会匹配上。

#IN字段指定多个值查询
SELECT * FROM tb_name  WHERE sex IN ('男','女');
SELECT * FROM tb_name  WHERE addr IN ('广州','北京');
SELECT * FROM tb_name  WHERE age IN (1,3,5,7,9);

BETWEEN AND

BETWEEN AND 区间查询，格式为：field BETWEEN value1 AND value2；MySQL的区间查询为两边都包含，以下语句等同于age >= 3 and age <=9 ；

#BETWEEN AND 区间查询
SELECT * FROM tb_name  WHERE age  BETWEEN 3 AND 9;

where优化建议

从数据库怎么存数据来思考，假设我们是优化器，会给执行器下达怎样的执行命令呢。可以把优化器看做一个记忆力超群，效率超高，但技能有限的孩子。

首先，我们可以把索引看成目录，通过索引，能清楚的知道每棵索引树上有多少行数据和包含什么字段信息。信息一定准确，行数是抽样调查得到的不精确值。

现在别人要让我们来查询时，我们会优先考虑能使用到的索引，通过查询字段是否在索引树上得知；然后查看字段是否为等值查询、范围查询，能清楚地知道对应每棵树上的数据行数。如果返回值也在树上的话，我们能很开心地快速返回。即覆盖索引（条件覆盖）。
如果是非等值查询，如<> 或 != 或 is null 或 is not null 或 not in，则超出了我们优化器的能力范围，偶尔不优化。
即使有时候是in 和 or，优化器也可能会觉得任务过重而直接罢工选择不优化。如： in(1,2,3,4,5,6,7,8,9,10,11,12),得一个个去对应，优化器会觉得好麻烦，直接不干了。连续的值应尽量使用>= and <= 或者 between and，明确地给优化器说，你看，是一个范围，不难查。
优化器小屁孩经常罢工，我们就让他长点技能，即最左前缀匹配和索引条件下推（MySQL5.6引入）。这样，他的效率又高起来了。

全部SQL代码：

#创建表
DROP TABLE IF EXISTS tb_name ;
CREATE TABLE tb_name (
id INT PRIMARY KEY COMMENT '主键',
username  VARCHAR(32) NOT NULL COMMENT '姓名',
sex VARCHAR(20) NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
addr VARCHAR(50) NOT NULL COMMENT '地址' ,
INDEX (username ,sex)
);

# MySQL存储过程笔记：
# declare一定要写在set前面,
# if......then...elseif...else.....end if

#批量插入存储过程
DROP PROCEDURE IF EXISTS insert_people;
DELIMITER ##
CREATE PROCEDURE insert_people(IN start_id INT,IN num INT )
BEGIN
    DECLARE id INT;
    DECLARE sex  VARCHAR(20) DEFAULT '男';
    DECLARE addr VARCHAR(32) DEFAULT '北京'; 
    DECLARE username  VARCHAR(32);    
    DECLARE age INT ;
    DECLARE username_string VARCHAR(50);
    DECLARE rand_num1 INT;
    DECLARE rand_num2 INT;
    DECLARE count_num INT DEFAULT num + 1;
    SET id = start_id;
    SET username_string ='张三不是丰好好学数据库您将受益无穷%_';
    REPEAT         
        SET rand_num1 = FLOOR(1+RAND()*19);
        SET rand_num2 = FLOOR(1+RAND()*19);        
        SET username  = CONCAT(SUBSTRING(username_string,rand_num1,1),MID(username_string,rand_num2,1));
        IF id%3=0 THEN SET addr='广州';SET sex='未知'; ELSEIF id%2=0 THEN SET addr='北京';SET sex='女';ELSE SET addr='广州';SET sex='男';  END IF ;
        SET age = FLOOR(1+RAND()*99);
        INSERT IGNORE INTO tb_name  VALUES(id,username,sex,age,addr); 
        SET id = id + 1;
        IF id%500=0 THEN COMMIT; END IF ;
        UNTIL id = count_num
    END REPEAT;
    COMMIT;
END ##
DELIMITER ;

#清空表
TRUNCATE tb_name ;
#批量插入
CALL insert_people(1,2000);
#插入特定
REPLACE  INTO tb_name  VALUES(201,'张三','男',99,'四川'),(202,'张三丰','男',99,'四川');

#####################################准备工作完成###############################
SHOW INDEX FROM tb_name ;
#查所有
SELECT * FROM tb_name;
SELECT id,username,sex,age,addr FROM tb_name;
SELECT age FROM tb_name;
SELECT sex FROM tb_name;

#去重
SELECT DISTINCT age FROM tb_name;
SELECT DISTINCT sex FROM tb_name;
########################################where语句###############################

#like通配符
SELECT * FROM tb_name WHERE username LIKE'张%';

SELECT * FROM tb_name WHERE username LIKE '%张%';

SELECT * FROM tb_name WHERE username LIKE'%张';

SELECT * FROM tb_name WHERE username LIKE '张_';

SELECT * FROM tb_name WHERE username LIKE '张_丰';

SELECT * FROM tb_name WHERE username LIKE '_三';

SELECT * FROM tb_name WHERE addr LIKE '广州';

SELECT * FROM tb_name WHERE addr NOT LIKE '广州';

SELECT * FROM tb_name WHERE username LIKE '%/%' ESCAPE '/'; 

SELECT * FROM tb_name WHERE username LIKE '%/_' ESCAPE '/'; 

#比较运算符
SELECT * FROM tb_name  WHERE sex = '男';
SELECT * FROM tb_name  WHERE sex != '未知';
SELECT * FROM tb_name  WHERE age > 9;
SELECT * FROM tb_name  WHERE age >= 9;
SELECT * FROM tb_name  WHERE age < 9;
SELECT * FROM tb_name  WHERE age <= 9;


#BETWEEN AND 区间查询
SELECT * FROM tb_name  WHERE age  BETWEEN 3 AND 9;


#逻辑运算符
SELECT * FROM tb_name  WHERE age >= 3 AND age <= 9;
SELECT * FROM tb_name  WHERE age >= 80 OR age <= 18;
SELECT * FROM tb_name  WHERE sex = '男' AND age <= 9;
SELECT * FROM tb_name  WHERE sex = '男' OR age <= 9;
SELECT * FROM tb_name  WHERE sex ='男' OR sex ='女';
SELECT * FROM tb_name  WHERE addr = '广州' OR addr='北京';

#IN字段指定多个值查询
SELECT * FROM tb_name  WHERE sex IN ('男','女');
SELECT * FROM tb_name  WHERE addr IN ('广州','北京');
SELECT * FROM tb_name  WHERE age IN (1,3,5,7,9);