07--MySQL自学教程：DQL（Data Query Language：数据库查询语言）简介、基础查询、条件查询、模糊查询以及排序（一）

超、凢脫俗 2022-06-15 02:13 120阅读 0赞

# 1. DQL（Data Query Language：数据库查询语言）知识点概览 #

**DQL（Data Query Language：数据库查询语言）：**

用来查询数据(记录)，不会对数据进行改变，而是让数据库发送结果集给客户端

**概览：**

![这里写图片描述][SouthEast]

# 2.基本语法 #

![这里写图片描述][SouthEast 1]

**select 列名1，列名2…….列名n from 表名 where 条件 group by 列名 having 条件 order by 列名；**

这个SQL语句包含了DQL部分所有的查询知识点。简单点说，只要记住这句话，查询部分的语法基本就都掌握了。  
下面我们将对各个部分分开进行详细说明。

# 3.查询方法概览 #

![DQL查询方法概览][DQL]

**创建一个数据库和表格，方便查询演示。代码如下：**

> **这里我是使用SQLyog创建的表格，使用cmd命令窗口也可以直接执行下列语句，后面的所有演示都是使用该软件写的，方便进行说明和注释**

-- 创建数据库和表格方便演示使用
    CREATE DATABASE queryDemo;  -- 创建数据库queryDemo
    USE queryDemo;              -- 进入queryDemo数据库
    -- 在queryDemo中创建表格demo1Student
    CREATE TABLE demo1Student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,score INT);
    
    -- 插入数据
    INSERT INTO demo1Student (NAME,age,score) VALUES ("张三",18,89);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("李四",23,77);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("王五",19,69);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("赵六",18,89);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("田七",20,92);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("小明",22,85);
    INSERT INTO demo1Student (NAME,age,score) VALUES ("小红",25,97);

![这里写图片描述][SouthEast 2]

# 4.基础查询 #

![DQL基础查询][DQL 1]

**代码演示**

-- 1.1查询所有列： 
    SELECT * FROM demo1Student;
    -- 1.2查询指定列：
    SELECT id,NAME FROM demo1Student;
    SELECT NAME,age,score FROM demo1Student;

**效果图：**

**SELECT \* FROM demo1Student;**  
![这里写图片描述][SouthEast 3]

**SELECT id,NAME FROM demo1Student;**  
![这里写图片描述][SouthEast 4]

**SELECT NAME,age,score FROM demo1Student;**  
![这里写图片描述][SouthEast 5]

# 5.条件查询 #

![DQL条件查询][DQL 2]

**代码演示**

-- 条件查询  运算符  关键字
       -- 1.运算符 =、!=、<>、<、<=、>、>=；
        -- 这些都是普通的数学运算符，就就不做说明了，  <>：表示不等于
       SELECT * FROM demo1Student WHERE id=5;
       SELECT * FROM demo1Student WHERE age!=18;
       SELECT * FROM demo1Student WHERE age<>18;
       SELECT * FROM demo!student WHERE score>90;
       SELECT NAME,age FROM demo1Student WHERE NAME="张三";
    
       -- 2.BETWEEN…AND； IN(set)；         IS NULL；  IS NOT NULL；  AND；OR；NOT
         -- 在...之间     与set集合交集     是否为空   是否为非空     并且 或   非
       SELECT * FROM demo1Student WHERE id BETWEEN 2 AND 4;  -- id在2到4之间的学生
    
       -- in(set)：指与数据库中数据与set的交集，获得包含有set中数据的记录（行）
       SELECT * FROM demo1Student WHERE age IN(25,22,30);
       SELECT * FROM demo1Student WHERE NAME IN ("小","张三","哈哈");
    
       -- 检验null， 所以添加一个新数据
       INSERT INTO demo1student VALUES (8,"小花",20,NULL);
    
       SELECT * FROM demo1Student WHERE score IS NULL;       -- 分数为空的学生
       SELECT * FROM demo1Student WHERE score IS NOT NULL;   -- 分数不为空的学生
       SELECT * FROM demo1Student WHERE age=18 AND score>78; 
       SELECT * FROM demo1Student WHERE age=22 OR age=25; 
       SELECT * FROM demo1Student WHERE  NOT age=18;         -- age不是18岁的学生

**运行结果：**

--------------------

**运算符**  
SELECT \* FROM demo1Student WHERE id=5;  
![这里写图片描述][SouthEast 6]  
SELECT \* FROM demo1Student WHERE age!=18;  
![这里写图片描述][SouthEast 7]  
SELECT \* FROM demo1Student WHERE age<>18;  
![这里写图片描述][SouthEast 8]  
SELECT \* FROM demo1student WHERE score>90;  
![这里写图片描述][SouthEast 9]  
SELECT NAME,age FROM demo1Student WHERE NAME=”张三”;  
![这里写图片描述][SouthEast 10]

--------------------

**关键字**

SELECT \* FROM demo1Student WHERE id BETWEEN 2 AND 4; – id在2到4之间的学生  
![这里写图片描述][SouthEast 11]

--------------------

– in(set)：指与数据库中数据与set的交集，获得包含有set中数据的记录（行）  
SELECT \* FROM demo1Student WHERE age IN(25,22,30);  
![这里写图片描述][SouthEast 12]  
SELECT \* FROM demo1Student WHERE NAME IN (“小”,”张三”,”哈哈”);  
![这里写图片描述][SouthEast 13]

--------------------

SELECT \* FROM demo1Student WHERE score IS NULL; – 分数为空的学生  
![这里写图片描述][SouthEast 14]  
SELECT \* FROM demo1Student WHERE score IS NOT NULL; – 分数不为空的学生  
![DQL查询][DQL 3]  
SELECT \* FROM demo1Student WHERE age=18 AND score>78;  
![DQL查询][DQL 4]  
SELECT \* FROM demo1Student WHERE age=22 OR age=25;  
![DQL查询][DQL 5]  
SELECT \* FROM demo1Student WHERE NOT age=18; – age不是18岁的学生  
![DQL查询][DQL 6]

# 6.模糊查询 #

![DQL模糊查询][DQL 7]

**代码演示**

-- 3.模糊查询
        -- _ 任意一个字符
        SELECT * FROM demo1student WHERE age LIKE "2_";
        SELECT * FROM demo1student WHERE age LIKE "_8";
        SELECT * FROM demo1student WHERE NAME LIKE "小_";
        -- %：任意0~n个字符
        SELECT * FROM demo1student WHERE age LIKE "%8";
        SELECT * FROM demo1student WHERE NAME LIKE "%小%";

--------------------

**运行结果**

**– \_ 任意一个字符**  
SELECT \* FROM demo1student WHERE age LIKE “2\_”;  
![DQL查询][DQL 8]  
SELECT \* FROM demo1student WHERE age LIKE “\_8”;  
![DQL查询][DQL 9]  
SELECT \* FROM demo1student WHERE NAME LIKE “小\_”;  
![DQL查询][DQL 10]

--------------------

**– %：任意0~n个字符**

SELECT \* FROM demo1student WHERE age LIKE “%8”;  
![DQL查询][DQL 11]

SELECT \* FROM demo1student WHERE NAME LIKE “%小%”;

![DQL查询][DQL 12]

# 7.排序 #

![DQL排序查询][DQL 13]

**代码演示**

-- 4.排序order by
    -- 升序asc
       SELECT * FROM demo1student WHERE score>80 ORDER BY score ASC;
    
    -- 降序desc
       SELECT * FROM demo1student WHERE score>80 ORDER BY score DESC;

--------------------

**运行结果**

– 升序asc  
SELECT \* FROM demo1student WHERE score>80 ORDER BY score ASC;  
![DQL查询][DQL 14]

– 降序desc  
SELECT \* FROM demo1student WHERE score>80 ORDER BY score DESC;  
![DQL查询][DQL 15]

# 8.其他四种排序见下节 #

![DQL查询][DQL 16]

> 地址：[http://blog.csdn.net/baidu\_37107022/article/details/72627463][http_blog.csdn.net_baidu_37107022_article_details_72627463]

[SouthEast]: /images/20220615/fb7aaf6187ca4013a5a37d84a3a3eb71.png
[SouthEast 1]: /images/20220615/174b2cc7d9d14f68a2e4e26d959d71de.png
[DQL]: /images/20220615/3e960fa6539e450494172e07a5ec2417.png
[SouthEast 2]: /images/20220615/bbd71e442768452a8b8a23f15df94006.png
[DQL 1]: /images/20220615/253fd867cbc2427b80d22256a268a284.png
[SouthEast 3]: /images/20220615/cc66cb1bc2cf4dbd8dfdd9e8d0023cca.png
[SouthEast 4]: /images/20220615/f155a9bb4001471b8f1d4a47b1c4f15d.png
[SouthEast 5]: /images/20220615/b5f313cfd48a4f94a3ed222206e30d7d.png
[DQL 2]: /images/20220615/345351a90c2b4649869e8b9a9df659fa.png
[SouthEast 6]: /images/20220615/29d0c1a887674c7484e28454650da7c3.png
[SouthEast 7]: /images/20220615/265476ee9b8d4991b0c9d4887b95e18c.png
[SouthEast 8]: /images/20220615/712c13d8a04b4f99b63b76b7b3d478b5.png
[SouthEast 9]: /images/20220615/b542c849dc0b487eb567a4f1eabbe70f.png
[SouthEast 10]: /images/20220615/a4306df0e9b749d3bf0070eb19d59cdc.png
[SouthEast 11]: /images/20220615/566c2bbe48a44f818e8555849284016d.png
[SouthEast 12]: /images/20220615/77d882c0e30146de968da22e4f6502b2.png
[SouthEast 13]: /images/20220615/0f6f078efd48422890714d5b4e7c56d7.png
[SouthEast 14]: /images/20220615/fc7ae8ffe8114786a91a80302063cfab.png
[DQL 3]: /images/20220615/77a86f6538d546249d6f74f283744e59.png
[DQL 4]: /images/20220615/c163af975f114a3e9a3e02e2ddbad3e3.png
[DQL 5]: /images/20220615/2fcdf082df8344d88beaefe0328fb114.png
[DQL 6]: /images/20220615/1ee0f7d125984c048424f6cbe0d5f375.png
[DQL 7]: /images/20220615/9adbc87fb5ca438ebf92d511791c7d5a.png
[DQL 8]: /images/20220615/d1e66050ce284165bd38d73578af9055.png
[DQL 9]: /images/20220615/4fe436eac17d4e0da2fb552c2e31ae6b.png
[DQL 10]: /images/20220615/5d658945f2c24a3c90fa26fb9f9634b7.png
[DQL 11]: /images/20220615/033c785c51e2474fa555aa3d2fdea4b4.png
[DQL 12]: /images/20220615/20264855d4bd44bb8353aa95af341690.png
[DQL 13]: /images/20220615/3287111d313c48848478ec0418da70e9.png
[DQL 14]: /images/20220615/1603fab1795e497982976dd514dff73f.png
[DQL 15]: /images/20220615/680d7d48841b424ebb95bbeeba80ce07.png
[DQL 16]: /images/20220615/5ee289f5665b43ada99352e43a60ef34.png
[http_blog.csdn.net_baidu_37107022_article_details_72627463]: http://blog.csdn.net/baidu_37107022/article/details/72627463