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];

• ==注意事项==

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

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