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

文章目录

数据操作DML

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

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

CREATETABLE student (
    id INT,
    NAME VARCHAR(10),
    sex CHAR(1),
    birthday date,
    score DOUBLE(5,2),
    email VARCHAR(64),
    tel VARCHAR(15),STATUSTINYINT);

添加数据(insert):

给指定列添加数据:

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);

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

给全部列添加数据:

INSERT INTO 表名 VALUES(值1,值2,…);

-- 给所有的列添加数据INSERTINTO student VALUES(102,'李四','男','2001-10-11',99.9,'[email protected]','13866669999','1');

批量给指定列添加数据:

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

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

批量给全部列添加数据:

INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

-- 批量给全部列添加数据INSERTINTO student
VALUES(106,'小红','女','2000-11-11',89.5,'[email protected]','13868689999','2'),(107,'小明','男','2001-12-12',74.5,'[email protected]','13866669696','1');

修改数据(update):

修改表数据:

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

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

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

删除数据(DELETE):

删除表数据:

DELETE FROM 表名 [WHERE 条件];

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

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

数据查询DQL

查询语法分为下面几种:

基础查询: SELECT 字段列表 FROM 表名列表

条件查询: WHERE 条件列表

排序查询: ORDER BY 排序字段

分组查询:

• GROUP BY 分组字段
• HAVING 分组后条件

分页查询: LIMIT 分页限定

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

-- 创建stu表CREATETABLE 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-- 入学时间);-- 向表中添加数据INSERTINTO 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;

去重复记录语法:

SELECTDISTINCT 字段列表 FROM 表名;

语句演示:

-- 查询adress字段, 并去除重复的记录SELECTDISTINCT 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提供了如下一些条件查询运算符:
符号功能>大于<小于>=大于等于<=小于等于=等于<> 或 !=不等于BETWEEN…AND…在某个范围之间(都包含)IN(…)多选一LIKE 占位符模糊查询

_

单个任意字符

%

多个任意字符IS NULL是NULLIS NOT NULL不是NULLAND 或 && (推荐使用单词)并且OR 或 || (推荐使用单词)或者NOT 或 ! (推荐使用单词)非, 不是

语句演示如下:

-- 查询年龄大于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 >=20AND age <=30;SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age BETWEEN20AND30;-- 查询年龄等于18岁或者等于20岁或者等于22岁的学生信息SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age =18OR age =20OR age =22;SELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE age IN(18,20,22);-- 查询英语成绩为null的学生信息-- 注意: null值的比较不能使用 =或者!=; 需要使用is 或者 is notSELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE english ISNULL;-- 查询出生日期在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 表名 ORDERBY 排序字段名1[排序方式1],排序字段名2[排序方式2] …;

排序方式:

ASC：升序排列（默认是升序）

DESC：降序排列

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

语句演示如下:

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

聚合函数

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

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

常用聚合函数如下：
函数名功能count(列名)统计数量(一般选用不为null的列)max(列名)最大值min(列名)最小值sum(列名)求和avg(列名)平均值
聚合函数语法:

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

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

语法演示:

count函数统计数量有两种常用取值:

1. 主键
2. * : 表示会自动找到不为null且速度最快的列进行统计

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

分组查询

分组查询的语法:

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

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

演示语句:

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

where和having区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

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

语句演示:

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

分页查询

分页查询语法如下:

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

起始索引: 从0开始

查询某一页的起始索引计算公式：

起始索引 = (当前页码 - 1) * 每页显示的条数

语句演示:

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

注意点:

分页查询

limit

是MySQL数据库的方言

Oracle 分页查询使用 rownumber 关键字

SQL Server分页查询使用 top 关键字