Java教程

sql学习2

本文主要是介绍sql学习2,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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值。==

  • 解决方案

    1. ==选择不包含非空的列进行计算==

    2. ==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];

  • ==注意事项==

    • 分组之后查询的字段:分组字段、聚合函数

    • wherehaving的区别?

      • 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页

这篇关于sql学习2的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!