SQL基础语句 - 数据操作DML和数据查询DQL语句详细介绍

叁歲伎倆 2023-09-23 14:46 232阅读 0赞

#### 文章目录 ####

*   *  数据操作DML
     *  数据查询DQL
     *   *  基础查询
         *  条件查询
         *  排序查询
         *  聚合函数
         *  分组查询
         *  分页查询

### 数据操作DML ###

**DML：是对表中的数据进行增删改的操作**

> 创建下面这样一个学生表进行演示

CREATE TABLE student (
    	id INT,
    	NAME VARCHAR ( 10 ),
    	sex CHAR ( 1 ),
    	birthday date,
    	score DOUBLE ( 5, 2 ),
    	email VARCHAR ( 64 ),
    	tel VARCHAR ( 15 ),
    	STATUS TINYINT 
    );

**添加数据(insert)**:

> 给指定列添加数据: `INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);`

-- 给指定列添加数据
    INSERT INTO student(id, name) VALUES(101, "张三");

> 给全部列添加数据: `INSERT INTO 表名 VALUES(值1,值2,…);`

-- 给所有的列添加数据
    INSERT INTO student VALUES(102, '李四', '男', '2001-10-11', 99.9, 'abc@163.com', '13866669999', '1');

> 批量给指定列添加数据: `INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;`

-- 批量给指定列添加数据
    INSERT INTO student(id, name, sex) VALUES(103, '王五', '男'), 
    	(104, '小黄', '女'), (105, '小黑', '男');

> 批量给全部列添加数据: `INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;`

-- 批量给全部列添加数据
    INSERT INTO student
    VALUES
    	( 106, '小红', '女', '2000-11-11', 89.5, 'xiaohong@163.com', '13868689999', '2' ),
    	( 107, '小明', '男', '2001-12-12', 74.5, 'xiaoming@163.com', '13866669696', '1' );

**修改数据(update)**:

> 修改表数据: `UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;`

-- 修改表中张三的性别和分数
    UPDATE student SET sex = '女', score = 96.99 WHERE name = '张三';

> 注意：修改语句中如果不加条件，则将表中的所有数据都修改！

**删除数据(DELETE)**:

> 删除表数据: `DELETE FROM 表名 [WHERE 条件];`

-- 删除表中name为张三的数据
    DELETE FROM student WHERE name = '张三';

> 注意：删除语句中如果不加条件，则将表中所有数据都删除！

### 数据查询DQL ###

**查询语法分为下面几种**:

> 基础查询: SELECT 字段列表 FROM 表名列表
> 
> 条件查询: WHERE 条件列表
> 
> 排序查询: ORDER BY 排序字段
> 
> 分组查询:
> 
>  *  GROUP BY 分组字段
>  *  HAVING 分组后条件
> 
> 分页查询: LIMIT 分页限定

**创建如下一个学生表作为演示**:

-- 创建stu表
    CREATE TABLE stu (
     id int, -- 编号
     name varchar(20), -- 姓名
     age int, -- 年龄
     sex varchar(5), -- 性别
     address varchar(100), -- 地址
     math double(5,2), -- 数学成绩
     english double(5,2), -- 英语成绩
     hire_date date -- 入学时间
    );
    
    -- 向表中添加数据
    INSERT INTO stu(id, name, age, sex, address, math, english, hire_date)
    VALUES
    (1,'马运',55,'男','杭州',66,78,'1995-09-01'),
    (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
    (3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
    (4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
    (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
    (6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
    (7,'张学右',22,'女','香港',99,99,'1998-09-01'),
    (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

#### 基础查询 ####

**查询多个字段语法**:

SELECT 字段列表 FROM 表名;
    SELECT * FROM 表名; -- 查询表的所有字段的数据

> 注意: 一般公司中不建议使用 `*`
> 
> 语句演示:

-- 查询stu表中id和name字段的数据
    SELECT id, name FROM stu;
    -- 查询stu表中所有的字段的数据
    SELECT * FROM stu;

**去重复记录语法**:

SELECT DISTINCT 字段列表 FROM 表名;

> 语句演示:

-- 查询adress字段, 并去除重复的记录
    SELECT DISTINCT address FROM stu;

**给字段起别名**:

> 例如给name, math, english起别名展示

-- 起别名
    SELECT name as 姓名, math as 数学, english as 英语 FROM stu;

> AS: AS 也可以省略, 但是字段名和别名之间至少有一个空格

-- 起别名
    SELECT name 姓名, math 数学, english 英语 FROM stu;

#### 条件查询 ####

**条件查询的语法**:

SELECT 字段列表 FROM 表名 WHERE 条件列表;

**SQL提供了如下一些条件查询运算符**:

<table> 
 <thead> 
  <tr> 
   <th>符号</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt; 或 !=</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>BETWEEN…AND…</td> 
   <td>在某个范围之间(都包含)</td> 
  </tr> 
  <tr> 
   <td>IN(…)</td> 
   <td>多选一</td> 
  </tr> 
  <tr> 
   <td>LIKE 占位符</td> 
   <td>模糊查询 <code>_</code>单个任意字符 <code>%</code>多个任意字符</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>是NULL</td> 
  </tr> 
  <tr> 
   <td>IS NOT NULL</td> 
   <td>不是NULL</td> 
  </tr> 
  <tr> 
   <td>AND 或 &amp;&amp; (推荐使用单词)</td> 
   <td>并且</td> 
  </tr> 
  <tr> 
   <td>OR 或 || (推荐使用单词)</td> 
   <td>或者</td> 
  </tr> 
  <tr> 
   <td>NOT 或 ! (推荐使用单词)</td> 
   <td>非, 不是</td> 
  </tr> 
 </tbody> 
</table>

> 语句演示如下:

-- 查询年龄大于20岁的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age > 20;
    
    -- 查询年龄大于等于20的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age >= 20;
    
    -- 查询年龄等于18岁的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age = 18;
    
    -- 查询年龄不等于18岁的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age != 18;
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age <> 18;
    
    -- 查询年龄大于等于20且小于等于30的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age >= 20 AND age <= 30;
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age BETWEEN 20 AND 30;
    
    -- 查询年龄等于18岁或者等于20岁或者等于22岁的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age = 18 OR age = 20 OR age = 22;
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age IN (18, 20, 22);
    
    -- 查询英语成绩为null的学生信息
    -- 注意: null值的比较不能使用 =或者!=; 需要使用is 或者 is not
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE english IS NULL;
    
    -- 查询出生日期在1998-09-01 到 1999-09-01之间的学生信息, SQL中的日期是可以直接比较大小的
    SELECT
    	id,
    	name,
    	age,
    	sex,
    	address,
    	math,
    	english,
    	hire_date 
    FROM
    	stu 
    WHERE
    	hire_date BETWEEN '1998-09-01' 
    	AND '1999-09-01';

**LIKE模糊查询**:

> `_`代表单个任意字符
> 
> `%`代表多个任意字符

-- 查询姓马的学员信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE name LIKE '马%';
    -- 查询名字第二个字是花的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE name LIKE '_花%';
    -- 查询名字中包含德的学生信息
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE name LIKE '%德%';

#### 排序查询 ####

**排序查询的基本语法如下**:

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

**排序方式**:

> ASC：升序排列（默认是升序）
> 
> DESC：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

**语句演示如下**:

-- 查询学生信息, 按照年龄升序
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu ORDER BY age ASC;
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu ORDER BY age; -- 默认值是升序可省
    
    -- 查询学生信息. 按照数学成绩降序
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu ORDER BY math DESC;
    
    -- 查询学生信息, 按照数学成绩降序, 如果数学成绩一样, 再按照英语成绩升序排列
    SELECT id, name, age, sex, address, math, english, hire_date FROM stu ORDER BY math DESC, english;

#### 聚合函数 ####

**学习分组查询先学习一下聚合函数**:

> 聚合函数: 将一列数据作为一个整体，进行纵向计算。

**常用聚合函数如下**：

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>count(列名)</td> 
   <td>统计数量(一般选用不为null的列)</td> 
  </tr> 
  <tr> 
   <td>max(列名)</td> 
   <td>最大值</td> 
  </tr> 
  <tr> 
   <td>min(列名)</td> 
   <td>最小值</td> 
  </tr> 
  <tr> 
   <td>sum(列名)</td> 
   <td>求和</td> 
  </tr> 
  <tr> 
   <td>avg(列名)</td> 
   <td>平均值</td> 
  </tr> 
 </tbody> 
</table>

**聚合函数语法**:

> 注意：null 值不参与所有聚合函数运算

SELECT 聚合函数名(列名) FROM 表;

**语法演示**:

> count函数统计数量有两种常用取值:
> 
> 1.  主键
> 2.  `*` : 表示会自动找到不为null且速度最快的列进行统计

-- 统计表中一共有多少个学生, 选择没有null的一列字段即可
    SELECT COUNT(name) FROM stu;
    -- 有null的值不会被统计
    SELECT COUNT(english) FROM stu;
    -- 查询数学成绩的最高分
    SELECT MAX(math) FROM stu;
    -- 查询数学成绩的最低分
    SELECT MIN(math) FROM stu;
    -- 查询数学成绩的总分
    SELECT SUM(math) FROM stu;
    -- 查询数学成绩的平均值
    SELECT AVG(math) FROM stu;

#### 分组查询 ####

**分组查询的语法**:

> 注意：分组之后，查询的字段只能为聚合函数和分组字段，查询其他字段无任何意义

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

> 演示语句:

-- 查询男同学和女同学各自的数学平均分
    SELECT sex, AVG(math) FROM stu GROUP BY sex;
    
    -- 查询男同学和女同学各自的数学平均分, 以及各自的人数
    SELECT sex, AVG(math), COUNT(*) FROM stu GROUP BY sex;

**where和having区别**：

> 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
> 
> 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

**执行顺序： where > 聚合函数 > having**

**语句演示**:

-- 查询男女同学的数学平均分, 以及各自人数; 要求: 分数低于70的不参与分组
    SELECT sex, AVG(math), COUNT(*) FROM stu WHERE math >= 70 GROUP BY sex;
    
    -- 查询男女同学的数学平均分, 以及各自人数; 要求: 分数低于70的不参与分组, 分组之后人数要大于2人
    SELECT sex, AVG(math), COUNT(*) FROM stu WHERE math >= 70 GROUP BY sex HAVING COUNT(*) > 2;

#### 分页查询 ####

**分页查询语法如下**:

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

> 起始索引: 从0开始
> 
> 查询某一页的起始索引计算公式：`起始索引 = (当前页码 - 1) * 每页显示的条数`

**语句演示**:

-- 从0开始查询, 查询3条数据
    SELECT * FROM stu LIMIT 0, 3;
    -- 每页显示3条数据, 显示第1页
    SELECT * FROM stu LIMIT 0, 3;
    -- 每页显示3条数据, 显示第2页
    SELECT * FROM stu LIMIT 3, 3;
    -- 每页显示3条数据, 显示第3页
    SELECT * FROM stu LIMIT 6, 3;

**注意点**:

> 分页查询 `limit` 是MySQL数据库的方言
> 
> Oracle 分页查询使用 rownumber 关键字
> 
> SQL Server分页查询使用 top 关键字