DML语句
添加数据
语法
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
举例
INSERT INTO student(`id`,`name`,`birthday`) VALUES (1,'李四1','2018-11-11');
注意事项
==添加数据
-- 添加数据 一一对应 insert INTO emp3 ( id, NAME, sex, job, salary )VALUES ( NULL, 'rose', "女", '测试', 4000 ); -- 添加数据 省略主键 insert INTO emp3 ( NAME, sex, job, salary )VALUES ( 'yiyi', "女", 'php', 24000 ); -- 添加数据 指定字段 insert INTO emp3 ( NAME, sex, salary )VALUES ( 'yiyan', "女", 90000 ); -- 添加数据 省略字段,字段全写 insert into emp3 values( NULL, 'tom', "男", '抓老jerry', 1000);
==除了数字类型,其他类型需要使用引号(单双都可以)引起来==
【建议大家都使用单引号 '' 不要使用双引号】sql语句拼接 sql语句在java代码中是一个String
==批量插入数据==
INSERT INTO stu1(`id`,`name`,`birthday`) VALUES (1,'李四1','2020-11-11'), (2,'李四2','2020-11-11'), (3,'李四3','2020-11-11'), (4,'李四4','2020-11-11');
删除数据
语法
delete from 表名 [where 条件]
举例
delete from student where id = 1;
注意事项
==如果不加条件,则删除表中所有记录。==
delete from student; -- 删除所有的记录
==如果要删除所有记录==
delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作 truncate table 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表
修改数据
语法
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
举例
update student set name = '王五', birthday = '2020-12-20' where id = 2;
注意事项
如果不加任何条件,则会将表中所有记录全部修改。
DQL语句
查询完整语法
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
select特点
* select 可以查询 表中的字段 表达式 常量值 函数 * 查询出的结果是一个虚拟表 不影响实际表中的数据
数据准备
-- 创建表 create table stu( id int, name varchar(20), chinese double, english double, math double ); -- 插入记录 insert into stu(id,name,chinese,english,math) values(1,'tom',89,78,90); insert into stu(id,name,chinese,english,math) values(2,'jack',67,98,56); insert into stu(id,name,chinese,english,math) values(3,'jerry',87,78,77); insert into stu(id,name,chinese,english,math) values(4,'lucy',88,NULL,90); insert into stu(id,name,chinese,english,math) values(5,'james',82,84,77); insert into stu(id,name,chinese,english,math) values(6,'jack',55,85,45); insert into stu(id,name,chinese,english,math) values(7,'tom',89,65,30);
简单查询
==查询所有记录==
语法: SELECT * FROM 表名;
如果用cmd查询出来的数据为乱码? set names GKB
==查询表中所有学生的姓名和对应的语文成绩==
语法: SELECT 字段名1,字段名2... FROM 表名; 举例: SELECT `name`,chinese FROM stu;
去重复
语法: select DISTINCT 字段名1,字段名2... FROM 表名; 举例: SELECT DISTINCT `name` FROM stu; -- 可以去重复多个字段, a字段重复的同时b字段也要重复才会去重,如果有一个不重得不会去重 SELECT DISTINCT `name`, chinese FROM stu;
==在所有学生数学分数上加10分特长分==
SELECT `name`,math+10 FROM stu;
Ifnull
==统计每个学生的总分==
SELECT `name`, chinese+english+math FROM stu;
发现lucy
的总成绩为null,这是什么呢?
==在做行运算时,null参与的运算,计算结果都为null==
实际情况下,有一门学科没有成绩,是正常的,但是求总和的时候,不能将所有的学科的成绩,都是为null,显然这是不合理的,我们可以使用一个函数来解决这个问题!
ifnull(表达式1,表达式2)
ifnull(参数1,参数2) 如果参数1有值,就走参数1 ,如果参数1为null,就走参数2
SELECT `name`, chinese + IFNULL(english,0) + math FROM stu;
==总结==
* 在做行运算时,null参与的运算,计算结果都为null * SELECT '123' + 100; -- 223 如果能转换成整数就跟后面的数值进行相加 * SELECT 'abc' + 100; -- 100 如果不能转换成整数 转换为0 在跟后面的数值进行相加
别名
在上面求每一个学生的总成绩时,展现出一个结果视图。在这个结果有两列,分别是name, chinese+IFNULL(english,0)+math 。第一个列名,一眼就能看出这列表示的是姓名,但是第二列,你能描述出这是一个学生的总成绩吗? 显然不能。我们就要使用别名的方式,展示别名。
==语法==
as : as也可以省略
==在什么地方使用==
1. 可以用在某个字段上 name as 姓名 2. 可以用在函数上 min(sarlay) as 最低工资 3. 可以用在表上 from sutdent as s 注意事项: 如果给表起了别名,后面在使用的时候,必须要用别名
==例子==
-- 把列的名换一下,起别名 select name , (chinese + ifnull(english,0) + math ) '总分' from stu; select id as '学号' from stu; -- as 可以省略 select id '学号2' from stu; -- 列别名 select id , name , chinese math from stu; select * from stu; -- 表别名,作用: 如果有多个表连查,就用表名可以区分各自的字段 select s.id ,s.name ,s.chinese from stu s; -- 直接表别名 select stu.id ,stu.name ,stu.chinese from stu; -- 如果你指定了表别名,就一定要使用 select s.id ,s.name ,s.chinese from stu;
条件查询
语法
SELECT 字段名 FROM 表名 [WHERE 条件];
运算符
运算符 | 说明 |
---|---|
> 、< 、<= 、>= 、= 、<> != | <>在 SQL 中表示不等于,在 mysql 中也可以使用!= 没有== |
BETWEEN...AND | 在一个范围之内 |
IN( 集合) | 集合表示多个值,使用逗号分隔 |
IS NULL 不为空 is not null | 查询某一列为 NULL 的值,注:不能写=NULL |
LIKE | 模糊查询 占位符: _:单个任意字符 %:多个任意字符 |
AND 或 && | 与,SQL 中建议使用前者,后者并不通用。 |
OR 或 || | 或 |
NOT 或 ! | 非 |
数据导入
-- 创建表 CREATE TABLE stu1 ( id int, name varchar(20), age int, sex varchar(5), address varchar(100), math int, english int ); -- 插入记录 INSERT INTO stu1(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳岩',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65);
条件查询sql
-
==查询math分数大于80分的学生==
SELECT * FROM student2 WHERE math > 80;
-
==查询english分数小于或等于80分的学生==
SELECT * FROM student2 WHERE english <= 80;
-
==查询age等于20岁的学生==
SELECT * FROM student2 WHERE age = 20;
-
==查询age不等于20岁的学生==
SELECT * FROM student2 WHERE age != 20;
-
==查询age大于35且性别为男的学生(两个条件同时满足)==
SELECT * FROM student2 WHERE age > 35 AND sex = '男';
-
==查询age大于35或性别为男的学生(两个条件其中一个满足)==
SELECT * FROM student2 WHERE age > 35 OR sex = '男';
-
==查询id是1或3或5的学生==
SELECT * FROM student2 WHERE id = 1 OR id =3 OR id = 5; -- in关键字 -- 再次查询id是1或3或5的学生 SELECT * FROM student2 WHERE id IN(1,3,5);
-
==查询id不是1或3或5的学生==
SELECT * FROM student2 WHERE id NOT IN(1,3,5);
-
==查询english成绩大于等于77,且小于等于87的学生==
SELECT * FROM student2 WHERE english >=77 AND english <=87; SELECT * FROM student2 WHERE english BETWEEN 77 AND 87;
-
==查询英语成绩为null的学生==
SELECT * FROM student2 WHERE english = NULL; -- null这哥们六亲不认... SELECT * FROM student2 WHERE english IS NULL; SELECT * FROM student2 WHERE english IS NOT NULL;
-
==查询姓马的学生==
SELECT * FROM student2 WHERE name LIKE '马%';
-
==查询姓名中包含'德'字的学生==
SELECT * FROM student2 WHERE name LIKE '%德%';
-
==查询姓马,且姓名有三个字的学生==
SELECT * FROM student2 WHERE name LIKE '马__';
-- 查四个字的学生 select * from stu1 where name like '____';
DQL高级查询
数据准备
-- 创建表 CREATE TABLE stu ( id int, name varchar(20), age int, sex varchar(5), address varchar(100), math int, english int ); -- 插入记录 INSERT INTO stu(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳岩',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65), (9,'唐僧',25,'男','长安',87,78), (10,'孙悟空',18,'男','花果山',100,66), (11,'猪八戒',22,'男','高老庄',58,78), (12,'沙僧',50,'男','流沙河',77,88), (13,'白骨精',22,'女','白虎岭',66,66), (14,'蜘蛛精',23,'女','盘丝洞',88,88);
排序查询
语法
SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];
ASC: 升序,默认值
DESC: 降序
-
==注意事项==
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
案例演示
-
==查询所有数据,使用年龄降序排序==
SELECT * FROM stu ORDER BY age DESC;
-
==查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序==
SELECT * FROM stu ORDER BY age DESC,math DESC;
-
==计算math 和 english总成绩 并按照总成绩降序排序==
SELECT NAME , math + IFNULL(english,0) score FROM stu ORDER BY score DESC;
聚合(集)函数
什么是sql函数
当我们学习编程语言的时候,经常会遇到函数。函数的好处是,它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。这样既提高了编写代码的效率,又提高了可维护性。在sql中函数主要要对数据进行处理!
常用的sql函数
* 算术函数 * 字符串函数 * 日期函数 * 转换函数 * 聚合函数/聚集函数
函数sql语法
SELECT 聚合函数(列名) FROM 表名;
常见聚合函数
聚合函数 | 说明 |
---|---|
count(*) | count(主键) | 计算表中的总记录数 |
max | 计算最大值 |
min | 计算最小值 |
sum | 计算和 |
avg | 计算平均值 |
-
注意:==聚合函数的计算,排除null值。==
-
解决方案:
-
==选择不包含非空的列进行计算==
-
==IFNULL函数==
-
聚合函数sql演示
-
==查询学生总数(null值处理)==
SELECT COUNT(id) FROM stu; SELECT COUNT(english) FROM stu; SELECT COUNT(*) FROM stu;
-
==查询年龄大于40的总数==
SELECT COUNT(*) FROM stu WHERE age >40;
-
==查询数学成绩总分==
SELECT SUM(math) FROM stu;
-
==查询数学成绩平均分==
SELECT AVG(math) FROM stu;
-
==查询数学成绩最高分==
SELECT MAX(math) FROM stu;
-
==查询数学成绩最低分==
SELECT MIN(math) FROM stu;
其它函数
函数名 | 说明 | 作用 |
---|---|---|
length(str) | 字符函数 | 获取字符的字节个数 |
upper(str) | 字符函数 | 将字符转换为大写字符 |
lower(str) | 字符函数 | 将字符转换为小写字符 |
substring(str,pos) | 字符函数 | 截取从指定索引处后面所有的字符 |
substring(str,pos,len) | 字符函数 | 截取从pos索引开始截取len个字符 |
replace(str,from_str,to_str) | 字符函数 | 将str中的字符 from_str字符替换成to_str字符 |
round(x) | 数学函数 | 四舍五入 |
round(x,d) | 数学函数 | 四舍五入 d:代表的是保留小数点后几位 |
cell(x) | 数学函数 | 向上取整 |
floor(x) | 数学函数 | 向下取整 |
mod(n,m) | 数学函数 | 取余数 mod(10,3) 相当于: select 10 % 3 |
str_to_date(str,format) | 日期函数 | 将日期字符转换成指定格式的日期 str_to_date('1990-11-11','%Y-%m-%d'); |
date_format(date,format) | 日期函数 | 将日期转换成字符 date_format(now(),'%Y/%m/%d'); |
分组查询
对一列数据进行分组,相同的内容分为一组,通常与聚合函数一起使用,完成统计工作
语法
SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];
-
==注意事项==
-
分组之后查询的字段:分组字段、聚合函数
-
where
和having
的区别?-
where
在分组之前进行过滤。having在分组之后进行过滤。 -
where
后不可以跟聚合函数,having可以进行聚合函数。 -
having 不能脱离 group by ,如果脱离group by 不能直接使用,一般与group by 一块使用
-
-
where:
操作的数据源: 原始表 -
having:
操作的数据源: 结果集
-
案例演示
-
==查询男女各多少人==
SELECT sex,COUNT(*) FROM stu GROUP BY sex;
-
==查询年龄大于25岁的人,按性别分组,统计每组的人数==
SELECT sex,COUNT(*) FROM stu WHERE age >25 GROUP BY sex;
-
==查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据==
SELECT sex,COUNT(*) FROM stu WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;
分页查询
语法
SELECT * 字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];
LIMIT语法格式
LIMIT offset,length;
-
offset:
起始行数,从 0 开始计数,如果省略,默认就是 0 -
length:显示的条数
计算公式
开始的索引 = (当前的页码 - 1) * 每页显示的条数
-- 每页查询显示3条数据 SELECT * FROM stu LIMIT 0,3; -- 第1页 SELECT * FROM stu LIMIT 3,3; -- 第2页 SELECT * FROM stu LIMIT 6,3; -- 第3页