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

1. DQL(Data Query Language：数据库查询语言)知识点概览

DQL(Data Query Language：数据库查询语言)：web

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

概览：sql

2.基本语法

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

这个SQL语句包含了DQL部分全部的查询知识点。简单点说，只要记住这句话，查询部分的语法基本就都掌握了。

下面咱们将对各个部分分开进行详细说明。svg

3.查询方法概览

建立一个数据库和表格，方便查询演示。代码以下：spa

这里我是使用SQLyog建立的表格，使用cmd命令窗口也能够直接执行下列语句，后面的全部演示都是使用该软件写的，方便进行说明和注释.net

-- 建立数据库和表格方便演示使用

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);

4.基础查询

代码演示3d

-- 1.1查询全部列：

SELECT * FROM demo1Student;

-- 1.2查询指定列：

SELECT id,NAME FROM demo1Student;

SELECT NAME,age,score FROM demo1Student;

效果图：code

SELECT * FROM demo1Student;

xml

SELECT id,NAME FROM demo1Student;

blog

SELECT NAME,age,score FROM demo1Student;

5.条件查询

代码演示

-- 条件查询 运算符 关键字

-- 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;

SELECT * FROM demo1Student WHERE age!=18;

SELECT * FROM demo1Student WHERE age<>18;

SELECT * FROM demo1student WHERE score>90;

SELECT NAME,age FROM demo1Student WHERE NAME=”张三”;

关键字

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 (“小”,”张三”,”哈哈”);

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岁的学生

6.模糊查询

代码演示

-- 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_”;

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 “%小%”;

7.排序

代码演示

-- 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;

– 降序desc

SELECT * FROM demo1student WHERE score>80 ORDER BY score DESC;

8.其余四种排序见下节