Mysql基础2

最新推荐文章于 2025-04-30 11:09:52 发布

Downhill Wind

最新推荐文章于 2025-04-30 11:09:52 发布

阅读量89

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.youkuaiyun.com/Cream336/article/details/131175775

版权

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( 集合)/not 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 stu3 (
  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,'沈腾',43,'男','齐齐哈尔',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值处理）

(1)count(*)—包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为NULL的记录。

(2)count(1)—忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略列值为NULL的记录。

(3)count(列名)—只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录(不包括空字符串和0)，即列值为NULL的记录不统计在内。

(4)count(distinct 列名)—只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录(不包括空字符串和0)，即列值为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)	字符函数	获取字符的字节个数
char_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:代表的是保留小数点后几位
ceil(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’);

值含义
秒 %S、%s 两位数字形式的秒（ 00,01, …, 59）
分 %I、%i 两位数字形式的分（ 00,01, …, 59）
小时 %H 24小时制，两位数形式小时（00,01, …,23）
%h 12小时制，两位数形式小时（00,01, …,12）
%k 24小时制，数形式小时（0,1, …,23）
%l 12小时制，数形式小时（0,1, …,12）
%T 24小时制，时间形式（HH:mm:ss）
%r 12小时制，时间形式（hh:mm:ss AM 或 PM）
%p AM上午或PM下午
周 %W 一周中每一天的名称（Sunday,Monday, …,Saturday）
%a 一周中每一天名称的缩写（Sun,Mon, …,Sat）
%w 以数字形式标识周（0=Sunday,1=Monday, …,6=Saturday）
%U 数字表示周数，星期天为周中第一天
%u 数字表示周数，星期一为周中第一天
天 %d 两位数字表示月中天数（01,02, …,31）
%e 数字表示月中天数（1,2, …,31）
%D 英文后缀表示月中天数（1st,2nd,3rd …）
%j 以三位数字表示年中天数（001,002, …,366）
月 %M 英文月名（January,February, …,December）
%b 英文缩写月名（Jan,Feb, …,Dec）
%m 两位数字表示月份（01,02, …,12）
%c 数字表示月份（1,2, …,12）
年 %Y 四位数字表示的年份（2015,2016…）
%y 两位数字表示的年份（15,16…）

分组查询（*）

对一列数据进行分组，相同的内容分为一组，通常与聚合函数一起使用，完成统计工作

语法

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页