文章目录
- 数据操作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 studentVALUES( 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提供了如下一些条件查询运算符:
| 符号 | 功能 |
|---|---|
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| 或 != | 不等于 |
| BETWEEN…AND… | 在某个范围之间(都包含) |
| IN(…) | 多选一 |
| LIKE 占位符 | 模糊查询 _单个任意字符 %多个任意字符 |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| AND 或 && (推荐使用单词) | 并且 |
| 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 >= 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 notSELECT id, name, age, sex, address, math, english, hire_date FROM stu WHERE english IS NULL;-- 查询出生日期在1998-09-01 到 1999-09-01之间的学生信息, SQL中的日期是可以直接比较大小的SELECTid,name,age,sex,address,math,english,hire_date FROMstu WHEREhire_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;聚合函数
学习分组查询先学习一下聚合函数:
聚合函数: 将一列数据作为一个整体，进行纵向计算。
常用聚合函数如下：
| 函数名 | 功能 |
|---|---|
| count(列名) | 统计数量(一般选用不为null的列) |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
聚合函数语法:
注意：null 值不参与所有聚合函数运算
SELECT 聚合函数名(列名) FROM 表;语法演示:
count函数统计数量有两种常用取值:
- 主键
*: 表示会自动找到不为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 关键字