Java学习日志Day28_MySql中的DML语句和DQL语句

1).-- mysql 普通注释 
#注释 引用注释
-- sqlyog:图形界面化工具: 可以提高开发效率 对于前期:需要自己手动写sql

2).-- 模糊查看当前msyql服务器端所有的字符编码集
-- character_set_client =utf8
-- character_set_resultes=utf8
-- character_set_server=utf8

SHOW VARIABLES LIKE '%character%'; 

USE ee_2104 ;
DESC stu ;


3).-- 数据库操作语句:操作表的记录
-- 给stu表插入数据
-- 插入语法
-- 语法1:insert into 表名 values(字段值1,字段值2,...n:全部字段) ;
INSERT INTO stu VALUES(1,'高圆圆',41,'西安市','女','1990-02-27','喜欢足球') ;
-- 语法2:插入部分字段
-- inset into 表名 (字段名称1,字段名称2,字段名称3..) values(值1,值2,值3..) ;
INSERT INTO stu (sid,NAME,age,sex) VALUES(2,'赵又廷',45,'男') ;

-- 语法3:插入全表字段或者部分字段的时候,可以一次性插入多条记录
-- insert into 表名 values(字段值1,字段值2,...n:全部字段),(字段值1,字段值2,...n:全部字段),(...)
INSERT INTO stu VALUES(3,'李四',24,'广州市','女','1998-10-20','喜欢跑步'),
 (4,'王五',27,'运城市','男','1997-11-20','喜欢篮球'),
 (5,'文章',28,'西安市','男','1997-11-20','喜欢足球') ;

/*
注意事项
	插入数据的时候,必须保证插入值对应的字段类型一致,否则会存在潜在问题(类型不匹配)
	          插入全表字段的值必须总数据要和字段列名的总数一致
*/
-- Column count doesn't match value count at row 1

-- INSERT INTO stu VALUES(6,'张佳宁',24,'广州市','女','喜欢跑步');
-- 类型不一致:(1 row(s) affected, 1 warning(s))
-- INSERT INTO stu VALUES('张佳宁',7,24,'广州市','女','1992-09-30','喜欢跑步')




4).-- update语句:修改
-- 基本的用法:带条件进行修改 
-- update 表名 set 字段名称 = 值 where xx =xx ;
-- 将name为7的人的学号改成6
UPDATE stu SET sid = 6 WHERE NAME = '7' ;

-- 将sid为6的学生 把name 改成"杨德财",address改为"西安市" 
UPDATE stu SET NAME = '杨德财',address = '西安市' WHERE sid = 6 ;

-- update 表名 set 字段名称 = 值; (属于批量修改) (都会带条件修改:推荐的)
UPDATE stu SET address = '西安市' ;


5).-- 删除:带条件来删除 delete from 表名  where ...
-- 删除sid=6的学生
DELETE FROM stu WHERE sid = 6 ;

-- delete from 表名:删除全表数据 (只是删除表的记录,表字段都还在!)
DELETE FROM stu ;

-- 删除全表数据
-- TRUNCATE TABLE 表名
TRUNCATE TABLE stu;


-- 删除stu表
DROP TABLE stu ;

/*
	delete from 表名:
	 TRUNCATE TABLE 表名; 
	 

	 都是删除全表数据,那么有什么不同?

delete from 表名:只是删除表中的全部数据,那么当前表的结构还存在,针对自增长主键id,下次
从之前id值继续自增;
	

	 TRUNCATE TABLE 表名; 会将当前表的全部数据以及表都删除掉,

并且自动创建一张一模一样的表,直接影响自增长主键,从0继续开始自增!			
	 
	 
	 
*/
CREATE TABLE stu (
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 学号 (主键,并且自增长)
	NAME VARCHAR(10),
	gender VARCHAR(5),
	address VARCHAR(10)
	
);
6).-- 插入数据
INSERT INTO stu(NAME,gender,address) VALUES('文章','男','西安市'),
('高圆圆','女','北京市'),('杨德财','男','云南省') ;

DELETE FROM stu ;
INSERT INTO stu(NAME,gender,address) VALUES('张飞','男','西安市') ;

TRUNCATE TABLE stu ;



7).-- 查询stu所有数据
-- 开发中查询全表字段 
SELECT 
	s.id '学号',
	s.name '姓名',
	s.gender  '性别',
	s.address  '住址'
	
FROM 
	

	stu  s; -- 表名起别名

--  自己使用:*
SELECT * FROM stu ;




8).--  DQL语句: 数据库查询语句(重点)
-- 基本查询
-- 带where条件查询
-- 模糊查询
-- 聚合查询
-- 分组查询
-- 筛选查询
-- 分页查询


-- 创建一张表
 CREATE TABLE student ( 
 id INT, -- 编号
 NAME VARCHAR(20), -- 姓名
 age INT, -- 年龄 
 sex VARCHAR(5), -- 性别 
 address VARCHAR(100), -- 地址 
 math INT, -- 数学
 english INT -- 英语 
 );
-- 插入数据
 INSERT INTO student(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); 

SELECT * FROM student;

9).-- DQL语句的基本查询
-- 带指定字段的查询
-- 查询student表中学生信息(id,name,age,sex,address)
SELECT
	id,
	NAME,
	age,
	sex,
	address
FROM
	student ;
-- 查询学生地址信息
SELECT 
	address
FROM 
	student ;
-- 问题:字段重复
-- 如何去重的: 查询字段时候:前面带上distinct 去重
SELECT
   DISTINCT address
FROM
 student ;


-- 查询学生的姓名 年龄以及他们的英语和数学的总分
SELECT
	NAME,
	age,
	(math+english)
FROM
	student;

-- 问题:当前计算总分时:id为5 学生的总分null, 英语没有成绩
-- 可以使用mysql自带的函数ifnull(字段,期望值)
-- 优化
SELECT
	NAME,
	age,
	(math+IFNULL(english,0))

FROM  
	student ;
	
-- 查询字段的时候给别名	as "别名" (as可以省略)
SELECT
	NAME AS '姓名',
	age AS '年龄',
	(math+IFNULL(english,0)) AS '总分'
FROM
	student ;

-- 进行字段求和/求差,字段类型必须一致,否则出现警告!
	

10).-- DQL语句之带where条件的查询
-- where 赋值运算符  =
-- where  比较运算符 ==,<,<=,>,>=,!=,<>(不等于)
-- where  其他符号:&& ,|| , and ,or , between  and ...
-- where  字段名称 in(值1,值2,值3) 等价于:字段名称= xx or xx or xx
-- where 字段名称 是否为null  (is null)  :为空   is not null 


-- 查询姓名是马云的学生信息
SELECT
	*
FROM 
	student
WHERE
	NAME = '马云' ;	
	
-- 查询年龄小于或者等于20岁的学生的姓名,age,住址
SELECT
	NAME,
	age,
	address
FROM
	student
WHERE 
	age <= 20 ;
	
-- 另一种写法	
SELECT
	NAME,
	age,
	address
FROM
	student
WHERE 
	age < 20 OR age = 20;	
	
	
-- 查询年龄大于20岁的学生信息
SELECT
    *		
FROM
    student 
WHERE 
    age > 20 ;    
-- 查询年龄大于等于20 并且小于等于30的学生信息
SELECT 
    *
FROM 
     student
     
WHERE 
     age >= 20 && age <= 30;  -- &&:Java中逻辑运算符

-- and符号:表示并列条件(连接多个条件:推荐使用and)
SELECT
    *
FROM 
     student
WHERE
    age >=20 AND age <=30 ; 
-- mysql 介于某个值和另一个值之间:  between  and ...       
SELECT 
	*	
FROM
	student
WHERE 
     age BETWEEN 20 AND 30 ;
     
-- 查询年龄不等于20岁的学生信息
SELECT
	*
FROM
	student
WHERE
	age != 20 ; -- Java中!= 判断

-- mysql:判断某个字段不等于<>	
SELECT
	*
FROM
	student
WHERE
	age <> 20 ;
	
-- 查询 学生的年龄是18或者20或者30的学生信息
SELECT
	*
FROM
	student
WHERE	
	age = 18 
	OR	-- ||
	age = 20
	OR	-- ||
	age = 30 ;
-- 提供另一种语法:查询的时候带where条件 某个字段名称 in(值1,值2,值3)   
SELECT
	*
FROM
	student
WHERE
	age 
		IN
	(18,20,30) ;	

-- 查询英语成绩为null 的学生信息
/*
select
	*
FROM
	student
where
	english == null ;  java语法这块不支持 ==null
*/ 
SELECT
	*
FROM 
	student
WHERE 
	english IS NULL ;
	
-- 查询英语不为null的 学生信息  !=null(错误的语法)  
-- mysql: is not null
SELECT	
	*
FROM
	student
WHERE
	english IS NOT NULL ;
	

11).-- DQL语句的模糊查询
-- select 字段列表 from 表名 字段名称 like(关键字) '%xx%'
-- %:表示可以匹配的任意的多个字符  一般都是 '%字符值%':包含指定的字符的信息 模糊查询出来
-- _:表示代替一个字符: 模糊查询 名字为三个字符的人 '___'
	
-- : 模糊查询姓名为两个字符的人,'_化'	
-- 模糊查询当前mysql服务器中所有的字符集的编码
SHOW VARIABLES LIKE '%character%' ;

-- 模糊查询姓名包含马的学生信息
SELECT
	*
FROM
	student
WHERE
        NAME
LIKE
	'%马%' ;
-- 模糊查询第二个字符是化的学生新
SELECT
	*
FROM
	student
WHERE
	NAME
LIKE
	'%_化%' ;-- 这个字符第二个字符化  (可以包含任意多个字符)
	
-- 查询姓名为三个字符的人
SELECT
	*
FROM
	student
WHERE
	NAME
LIKE
	'___' ;
	

-- 应用场景
/*
	lunce
	solr
	es:搜索引擎 --将数据库中的相关的信息---存储在索引库中
		中文分析器(中文分词)
	搜索系统---搜索框中 "手机"	"%手机%"
*/	
	
	
12).-- DQL语句聚合函数查询	
-- count(字段名称):查询总记录数
-- avg(字段名称):查询当前字段的平均值
-- max(字段名称):查询当前字段的最大值
-- min(字段名称):查询当前字段的最小值
-- sum(字段名称):当前字段的求和值

-- 查询当前学生表的总记录数
-- count(字段):字段名称必须非null
SELECT
	COUNT(NAME) -- name不为null
FROM
	student ;
	
SELECT
	COUNT(english) -- 有一个学生的英语成绩null (存在问题)
FROM
	student ;	

-- 非得使用带null的字段的查询总记录数
SELECT
	COUNT(IFNULL(english,0)) -- ifnull(字段名称,期望值)
FROM
	student;
	
-- count(字段):字段名称都使用的非业务字段
-- 一般情况:都是表中id字段(主键自增长的字段)
SELECT 
	COUNT(id)
FROM
	student;
/*
 注意:不要使用业务字段作为条件 查询总记录数
		学生管理系统  业务字段---都是可能发生变化的字段 (不建议使用这种方式查询)
		学生表		count(name)---10条件记录
		id	name  
		1       张三
		
		10条记录
*/	
-- 查询当前学生的数学平均分
-- 聚合函数查询出来的结果:都是单行单列

/*
	多表查询---子查询
	查询数学平均分的学生信息
		select
		     *
		     from 
		     student
		      where avg(math) = 70;		  
*/
SELECT
​	AVG(math) '数学平均分'
FROM 
​	student ;
​	
-- 查询英语成绩的总和
SELECT
​	SUM(IFNULL(english,0)) '英语总成绩'
FROM
​       student ;​       
-- 查询数学成绩的最高分 
SELECT
​	MAX(math) 
FROM
​	student ;
​	
-- min(字段):最小值	
13).-- DQL之 排序查询
-- select 字段列表 from 表名 order by 字段名称 排序方式
-- 排序方式:asc :升序(默认值)    desc :降序
-- 将的数学成绩升序排序 查询全部学生信息
SELECT
	*
FROM
	student
ORDER	 BY   
	math  ; -- asc省略了
	
-- 查询学生表数据,英语成绩降序:英文成绩null,给0
SELECT
	*
FROM
	student
ORDER BY IFNULL(english,0) DESC ;

-- 需求:将id为6的英语成绩改为95分
UPDATE student SET english = 95 WHERE id = 6 ;


14).-- 排序查询:查询学生所有数据,数学成绩升序排序,同时英语降序排序(多个字段同时排序)
-- 如果当前数学成绩相同的,则按照英语降序排
SELECT
	*
FROM
	student
ORDER BY 
	math ASC,
	IFNULL(english,0) DESC ;
	
	
15).-- 分组查询:关键字:group by
/*
	针对分组查询的时候,可以查询什么?
		1)可以查询分组的字段
		2)查询聚合函数  
		

		select 字段列表 ,聚合函数 from 表名 group by 分组的字段;
		注意:group by 后面不能跟聚合函数,否则语法通过不了

*/

-- 需求:按照性别分组,查询男,女的数学平均分

SELECT
	sex '性别',  -- 查询的分组的子弹
	AVG(math) '数学平均分' -- 查询聚合函数
FROM
	student
-- 分组的语法
GROUP BY 
	sex ;
	
-- 需求:按照性别分组,查询男,女的数学平均分,以及查询总人数
SELECT
	sex '性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM
	student

GROUP BY 
	sex ;
	
-- 需求:按照性别分组,查询男,女的数学平均分,以及查询总人数
-- 分组查询的时候带条件: 数学成绩不大于70分的学生不参与分组
/*
SELECT
	sex '性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM
	

	student

GROUP BY 
	sex  
where math > 70 ;	
*/

16).-- 分组查询:group by 带条件查询,必须先使用条件,然后在参与分组	
-- where 语句必须放置在group by之前;

/*
   查询时候
	分析:查询哪个字段
	     带什么条件查
	     有没有分组(需要分组,where条件必须在前)
	     有没有筛选,如果有筛选,有分组
	     先where,
	     再分组
	     再筛选...

*/

SELECT
	sex '性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM
	

	student

WHERE
	math > 70 
	
GROUP BY 
	sex ;


-- 筛选:having 
/*
	筛选后面可以使用聚合函数,而且如果sql语句有where条件,有group by ,有having
	where必须置于group by之前,
	group by 必须置于having 之前!
*/

-- 需求:
/*
-- 需求:按照性别分组,查询男,女的数学平均分,以及查询总人数
-- 分组查询的时候带条件: 数学成绩不大于70分的学生不参与分组
-- 筛选出 "总人数大于2的信息"
*/
SELECT 
	sex,
	AVG(math),
	COUNT(id)
FROM
	student
WHERE 
	math > 70

GROUP BY
	sex
	
17).-- 筛选
HAVING 
	COUNT(id) > 2 ;	

-- 优化 :加别名
SELECT 
	sex 性别,
	AVG(math) 数学平均分,
	COUNT(id) 总人数
FROM
	student
WHERE 
	math > 70

GROUP BY
	sex
	
-- 筛选
HAVING 
	总人数 > 2 ;	



INSERT INTO student VALUES
(9,'高圆圆',41,'女','西安',97,89),
(10,'文章',35,'男','西安',89,78),
(11,'马伊琍',44,'女','西安',100,94),
(12,'赵又廷',46,'男','西安',78,80);
INSERT INTO student VALUES
(13,'耿明刚',20,'男','西安',97,89);

17).-- 分页查询
-- mysql:limit 起始行数,每页显示的条数;

-- select 字段列表 from 表名 limt 起始行数,每页显示的条数;
/*
	起始行数是从0开始算:
	

			每页显示3
	
	后台:分页的逻辑:
			起始行数=(必须知道当前页码-1)*每页显示的条数

*/

-- 	需求:查询第一页的数据
SELECT
	*
FROM
	student
	
LIMIT 0 ,3 ;	
-- 需求:查询第二页的数据
SELECT 
     *
FROM  
	student
LIMIT 3,3 ;
-- 第三页数据
SELECT

	*

FROM
	student
LIMIT 6,3 ;	

-- 第四页数据
SELECT

	*

FROM
	student
LIMIT 9,3 ;

-- 第五页
SELECT
    *
FROM
	student 
LIMIT 12,3;

SELECT * FROM student;