数据库篇2（增删查改）

2021/12/11 19:50:05

编程Tag： select 查询数据库 null 增删 -- Student studentno 查改

本文主要是介绍数据库篇2（增删查改），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

                   **目录**

一、数据库级别外键

外键(了解即可)、添加、修改、删除

二、DQL查询数据(最重点)

三、where条件子句之逻辑运算符

四、模糊查询操作符详解

五、分页；子查询和排序查询

六、mysql常见的函数

七、聚合函数和分组过滤

八、联表查询JoinON详解

九、数据库级别的MD5加密(扩展)

数据库篇2

一、数据库级别外键

外键(了解即可)

方式一、在创建表的时候，增加约束(麻烦比较复杂)

CREATE TABLE `grade`(  -- 创建年级表
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)   -- 主键是gradeid
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 默认引擎+编码



CREATE TABLE IF NOT EXISTS `student`(      -- 创建student表 并且引用年级表的id
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
  `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
  `gradeid` INT(10) NOT NULL COMMENT'学生年级', -- 绑定上面年级表的id，现在这两个创建还没有绑定
  `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT'邮件',
  PRIMARY KEY(`id`),
  KEY `FK_gradeid` (`gradeid`), -- 把这个gradeid当做外键。这个FK_gradeid是约束名
  
  -- 学生表的gradeid 字段，要去引用年级表的geadeid
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
  -- CONSTRAINT外键的约束；
  -- FOREIGN KEY (`gradeid`) 意思是添加gradeid为外键
  -- REFERENCES `grade`(`gradeid`)  意思是引用到grade表的gradeid
  -- 总结：一共两步   定义外键key   给这个外键添加约束(执行引用)  reference(引用)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 注：删除有外键关系表的时候，必须先删除引用别人的表(从表)，再删除引用的表（主表）
-- 上面这个例子就是如果要删除年级表(grade)，则必须先删除学生表(student)，因为是student引用grade

方式二：创建表成功后添加外键约束

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)   -- 主键是gradeid
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 默认引擎+编码


CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
  `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
  `gradeid` INT(10) NOT NULL COMMENT'学生年级', 
  `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT'邮件',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 现在的情况是创建的两个表(grade)和(student)没有外键关系
-- 现在把student去引用grade表的gradeid

-- 修改studnet这个表
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`); 

  -- ADD CONSISTENT 添加一个外键的约束，                 
-- FK是一个标识，然后把gradeid当成一个外键，`FK_gradeid`  可以理解成标识这个外键
-- FOREIGN KEY(gradeid)   引用FOREIGN KEY；就是自己这个表的gradeid去引用grade的id
-- REFERENCES `grade`(gradeid)    引用grade表的gradeid

-- 总结：ALTER TABLE 表    ADD CONSTRAINT 约束名  FOREIGN KEY 作为外键的列  REFERENCES 哪个表(哪个字段)


-- 以上的操作都是物理的外键 物理的外键都是数据库级别的外键，不建议使用(避免数据库过多 造成困扰   上面的两个例子了解即可  因为导出的都是这样的格式 要会看)

最佳实现：数据库就是单纯的表只用来存数据。只有行(数据)和列(字段)
我们想使用多张表的数据，想使用外键(程序去实现)

DML语言(全部记住)

数据库的意义：数据存储，数据管理。

DML：数据库管理语言
insert 添加
update 修改
delete 删除

添加

INSERT INTO grade(`gradename`)VALUES('光针')   -- VALUES与VALUE插入相同

INSERT INTO grade(`gradename`)VALUE('啊实打实阿萨德'),('阿萨德'),('很关键')

INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`)
VALUES('李白','qwertt','男','2000-10-09','九','桂林','email')   -- 指定插入


INSERT INTO `student`
VALUES(7,'阿萨德','1564654','女','2020-10-10','1','阿萨德','email')  -- 一一对应插入

修改

update 修改谁(条件) set原来的值 = 新值

语法：update 表明 set colnum_name = value where [条件]

-- 修改一个属性的
-- 修改学员的名字，带了条件
UPDATE `student` SET `name`='杜甫' WHERE id = 3 -- 意思是  要把student表id=3的这个名字改成杜甫

-- 这是不指定条件的；这样的话就会改动所有的表名字都会变成长江七号
UPDATE  `student` SET `name`='长江七号'

-- 设置多个属性；需要用 , 隔开
UPDATE `student`  SET `name`= '珍贵',`email`= '33333333333@qq.com' WHERE id =6

-- update  表明  set colnum_name = value[colnum_name = value,...]  where [条件]





-- 如果想要修改表中列的空值 用IS NULL
UPDATE `subject` SET `classhour`='130' WHERE `classhour`IS NULL

最后的那个条件：where子句运算符 id等于某个值大于某个值在某个区间修改…

操作符号含义范围返回结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
>
<
>=
<=
BETWEEN…and 在某个范围内 [2,5]
AND 我和你 && 5>1 and 1>2 false
OR 我或你 || 5>1 and 1>2 true
```
-- 通过多个条件判断数据，无上限   无限加and条件
UPDATE `student` SET `name`='长江八号' WHERE `name`= '长江七号' AND sex = '女'



-- 下面是例子
UPDATE `student` SET `name` = '长江九号' WHERE `name` = '珍贵'
UPDATE `student` SET `name` ='长江十号'  WHERE  id<>3
UPDATE  `student` SET `pwd` = '88888888' WHERE  id  BETWEEN 3  AND 10
UPDATE `student` SET  `address` = '玉林'  WHERE  `name`= '长江十号' AND  sex = '男'
UPDATE `student` SET  `email` = '888888@qq.com'  WHERE  `address`= '玉林' OR  gradeid = '5'
```
语法：update 表明 set colnum_name = value[colnum_name = value,…] where [条件]

注意：colnum_name 是数据库的列，尽量带上

条件：就是筛选的条件,如果没有指定则会修改所以的列

value：是一个具体的值；或者是一个变量

多个设置的属性之间，使用英文逗号隔开

操作符号	含义	范围	返回结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>
<
>=
<=
BETWEEN…and	在某个范围内	[2,5]
AND	我和你 &&	5>1 and 1>2	false
OR	我或你 \|\|	5>1 and 1>2	true

删除

delete 命令

语法： delete from 表明 [where 条件]

-- 避免这样子去删，这样子会直接删除整个表
DELETE FROM `student`


-- 删除制定数据
DELETE FROM `student` WHERE id = 2;

TRUNCATE命令

作用：完全清除一个数据库表，表的结构、索引、约束不会改变

delete 与 TRUNCATE 的区别

-- 测试delete 与 TRUNCATE 的区别

CREATE TABLE `test`(
 `id` INT(4) NOT NULL AUTO_INCREMENT,
 `coll` VARCHAR(20) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8        -- 新建一个test的表  注意id是主键
INSERT `test`(`coll`) VALUES('1'), ('2'), ('3') -- 往表里面插入1 2 3

-- 然后用delete 删除表
DELETE FROM `test` 	  -- 不会影响自增 意思就是如果再插入数据的话 id就会 从4开始

INSERT `test`(`coll`) VALUES('1'), ('2'), ('3') -- 再往表里面插入1 2 3
TRUNCATE `test`     -- 这次用TRUNCATE删除  这个会影响自增，意思是如果再插入数据的话Id就会 从1开始

了解即可：DELETE删除的问题，重启数据库的现象
innoDB 自增列会从1开始 (存在内存中断电即失)
MyISAM 自增列会从上一个自增量开始 (存在文件中不会丢失)

二、DQL查询数据(最重点)

DQL:Data Query Language 数据查询语言

-- 顺序
-- select完整语法 
SELECT[ALL |DISTINCT]
{* | table.* | [table.field[as alias1 ][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE...]  -- 指定结果需满足的条件
[HAVING...] -- 指定结果按照哪几个字段来分组
[GROUP BY...] -- 指定查询记录按一个或多个字段排序
[LIMIT...] -- 指定查询的记录从哪条到哪条 --->   五、详细看分页、子查询排序查询

所有的查询操作都用它 Select
简单查询，复杂查询都能做
数据库中最核心的语言
使用频率最高的语言
指定查询

SELECT * FROM student   --  查询全部的学生 

语法：
select  -- 查询列表
from   -- 表明
where  -- 筛选条件

查询指定字段

例一：SELECT `studentno`,`studentname` FROM `student`        -- 意思是查询student这个表的studentno和studentname 这里两个字段
studentno  studentname  
---------  -------------
     1000  张三       
     1001  李四       
     1002  王五       
     1003  阿萨德           
     -- 别名  给结果起个名字；查询出来不想看到studentno  studentname  的列格式   用AS
     
     
     
例二、     SELECT `studentno`AS 序列号,`studentname`AS 学生名字 FROM `student`   
     
     序列号  学生名字  
---------  --------------
     1000  张三        
     1001  李四        
     1002  王五        
     1003  阿萨德            -- 这样就是起别名
     
     
     
     
     -- 也可以给表起别名
  例三   SELECT `studentno`AS 序列号,`studentname`AS 学生名字 FROM `student` AS s  
  
  
  
  
  例四：  函数  Concat(a,b)
  SELECT CONCAT('姓名：',studentname)AS 新名字 FROM `student`
  新名字           
--------------------
姓名：张三     
姓名：李四     
姓名：王五     
姓名：阿萨德  



语法：SELECT 字段...From	 表
有的时候 列名字不是那么的见名知意，那么就起别名   AS关键字

-- mysql中+号的作用

/*

java 中 + 号的作用
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串

mysql中的加号：
仅仅只有一个功能：运算符
select	100+90;   两个操作数都为字符型，则做加法运算
select   '123'+90;  只要有一方为字符型，mysql会试图将字符型转换成数值型   
                     如果转换成功，则将字符型数值转换成0 
                                      
select    'join'+90;  如果转换失败，则将字符型数值转换成0 
  
select   null+10;      如果其中一方为Null，则结果肯定为null  


*/


-- +号还有另外一个作用；就是让多个属性起一个别名

select 
CONCAT(last_name,first_name) AS 姓名
     
FROM
       employees;                     -- 这个是 last_name 和 first_name字段   共同显示一个叫"姓名"的别名   这个叫拼接  可以拼接多个
       
       
   -- ifnull() 函数是判断有为null的情况   如果有的有null  有的没有  
  -- 那么
  select IFNULL(字段,结果)

去重 distinct

distinct作用：去除SELECT查询出来的结果中重复的数据重复的数据只显示一条

-- 如果一个表中有重复的数据  我们想去掉重复的话就需要用到关键字distinct

SELECT * FROM `student`   -- 先查整个学生表   
studentno  loginpwd  studentname     sex  gradeid  phone   address             borndate             email        identitycard        
---------  --------  -----------  ------  -------  ------  ------------------  -------------------  -----------  --------------------
     1000  11111     张三                1        1  12345   贵港市平南县              1996-12-06 00:00:00  test@qq.com  450921188615503251  
     1001  11111     李四                1        1  12345   桂平市平南县              1996-12-06 00:00:00  test@qq.com  4509211886155032    
     1002  11111     王五                1        1  12345   北京市                 1996-12-06 00:00:00  test@qq.com  450921184486155032  
     1003  11111     阿萨德               1        1  12345   北京市                 1996-12-06 00:00:00  test@qq.com  450118441586155032  
     1005  11111     熊皮哦               1        1  12345   北京市                 1996-12-06 00:00:00  test@qq.com  450118441586155052 
     
SELECT `loginpwd` FROM `student`   -- 查到pwd都是1   
loginpwd  
----------
11111     
11111     
11111     
11111     
11111     

SELECT DISTINCT`loginpwd` FROM `student`   -- 去重
loginpwd  
----------
11111     

-- 案例：查询salary,显示结果为 out put       //因为这里有空格所以需要加上双引号或者单引号
SELECT salary AS 'out put' form employees       



-- 去重  比如查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees   -- 只需要在 字段名的前面加上 DISTINCT

数据库的列 (表达式)

查询数据库的版本： SELECT VERSION()
可以计算： SELECT 100*3-1 AS 计算

三、where条件子句之逻辑运算符

搜索条件由一个或者多个表达式组成！结果是返回一个布尔值类型
作用：检索数据中符合条件的值

逻辑运算符

运算符语法描述
and && a and b a &&b 逻辑与
or || a or b a || b 逻辑或
Not ! not a ! a 逻辑非
- 尽量使用英文字母

运算符	语法	描述
and &&	a and b a &&b	逻辑与
or \|\|	a or b a \|\| b	逻辑或
Not !	not a ! a	逻辑非

-- 逻辑查询

SELECT `subjectno`,`studentresult` FROM `result`         -- 查询result 表中的学号和成绩


-- 比如查询成绩在95-100之间 
SELECT `subjectno`,`studentresult` FROM `result`
WHERE studentresult>=95 AND studentresult<=100	  -- 查询成绩在95~100分之间的成绩   用的是and   用&&也可以


SELECT `subjectno`,`studentresult` FROM `result`
WHERE studentresult>=95 && studentresult<=100             -- 这样子也是可以查的	

-- 也可以用  区间模糊查询  (区间)
SELECT `subjectno`,`studentresult` FROM `result`
WHERE studentresult>=95 BETWEEN 95 AND 100           -- 用 BETWEEN  区间模糊查询也可以查到

-- 也就是说 查一条语句可以有三种方法查询




SELECT `subjectno`,`studentresult` FROM `result`
WHERE subjectno!=1000               --  查询除了1000号的学生          ！= 是不等于的意思

四、模糊查询操作符详解

本质还是比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，则结果为真
IS NOT NULL	a is not null	如果操作符为not null，则结果为真
BETWEEN	a between b and c	若a在b和c之间则结果为真
like	a like b	SQL匹配，如果a匹配到b 则结果为真
in	a in(a1,a2,a3…)	假设a在a1或者a2或者a3…则结果为真

-- ============================================= like 关键字 ==============================================================

-- 模糊查询  比如查学生表表中姓刘的同学     可以用like结合  %(代表0到任意个字符)   _（代表一个字符）
SELECT `studentno`,`studentname` FROM `student` -- 先拿到学生表的全部名字
WHERE studentname LIKE '刘%'     -- 再通过where条件判断 名字带有刘%的  

-- 查询刘后面只带有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘_' 

-- 查询刘后面只带有二个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘__'   -- 那就是两个杠(__)

-- 查询名字带有欣字的 

SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%欣%'  -- 这里说一下为什么不用 '_欣_' 如果 这样子查询   有些人的名字不止三个字  所以得用%  是前后任意字符的




-- ================================================ in关键字(是一个或者多个具体的值) ======================================================

-- 比如我要在几万行数据中查询中间的 1001,1002,1003,1004,1005的学生
SELECT `studentno`,`studentname` FROM `student`
where studentno = 1001  -- 这样子写只可以查一条 查五个学生的话就会需要五条语句   

-- 那么可以用in 关键字
SELECT `studentno`,`studentname` FROM `student`
where studentno in (1001,1002,1003,1004,1005)         -- 这样子就可以把五个学生是1001~1005都查出来


-- 比如要把在北京的学生查出来
SELECT `studentno`,`studentname` FROM `student`
where address in ('北京') -- 注意的是这里写的地址一定要写全 不然查不出来  
SELECT `studentno`,`studentname` FROM `student`
where address in ('北京','贵港平南') -- 或者这样




-- 而且也不可以这样子用
-- 而且也不可以这样子用
-- 而且也不可以这样子用
SELECT `studentno`,`studentname` FROM `student`
where studentno in ('%北京%')    -- 这个% 是在 like 关键字那里用的  而in是一个或者多个具体的值！！！



-- ============================================ NULL     NOT NULL ===========================================================
-- 查询地址为空的学生 null    ''
SELECT `studentno`,`studentname` FROM `student`
where address = ''      -- 这样子可以查出来地址为空的


-- 如果要查多个
SELECT `studentno`,`studentname` FROM `student`
where address = ''  OR address IS NULL

-- 查询有出生日期的同学  有出生日期就是不为空
SELECT `studentno`,`studentname` FROM `student`
where borndate is NOT NULL	
-- 查询没有出生日期的同学  
SELECT `studentno`,`studentname` FROM `student`
where borndate is NULL

五、分页、子查询和排序查询

-- =========================================分页 limit 和排序 order by===========================================================

-- 如果数据库有100万条数据 好比如百度搜图片 总是滑不到底的 这个叫瀑布流  这样就增加数据库的压力  
-- 使用分页的话就会缓解数据库的压力 给人的体验更好  

-- 分页  每页只显示5条数据	
-- 语法：limit(起始下标，pageSize)   
-- 网页应用：当前  总的页数  页面的大小
limit 0,5  -- 这个表示从当前
limit 0,5
 -- limit 0,5  第一到第五条数据
 
 SELECT StudentNO,StudentName,SubjectName,studentResult
FROM student s
INNER JOIN result r
ON s.StudentNO = r. StudentNO
INNER JOIN `subject`  sub
ON r.StudentNO = sub.StudentNO
WHERE SubjectName = '数据库结构-1'
ORDER BY studentResult ASC   -- 升序
LIMIT 0,5           -- 分页

--    总结： 
--    第一页 limit 0,5        (1-1)*5
--    第二页 limit 5,5        (2-1)*5
--    第三页 limit 10,5       (3-1)*5
--    第n页  limit            (n-1)*pageSize，pageSize
--   【pageSize：页面大小】
--   【（n-1）*pageSize起始值，】
--   【n 当前页 】
-- 【数据总数/页面大小=总页数】





 
-- =========================================where子查询=============================================================================

-- where(这个值是计算出来的)
-- 本质:在where语句中嵌套一个子查询语句
-- 语法：where(select*from)


-- 查询分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.StudentNO,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNO = r. StudentNO
WHERE StudentResult>=80



-- 在这个基础上增加一个科目  高等数学-2
--  查询高等数学-2 的编号
SELECT DISTINCT s.StudentNO,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNO = r. StudentNO
WHERE StudentResult>=80 AND subjectNo =( -- 在这个where 里面嵌套一个子查询
SELECT  subjectNo FROM  `subject`
WHERE SubjectName = '高等数学-2'
)
-- ================================ 下面这个是原始的写法  上面是嵌套一个子查询的写法
SELECT DISTINCT s.StudentNO,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNO = r. StudentNO
INNER JOIN `subject` sub
ON r.SubjecNo = sub.SubjecNo
WHERE StudentName = '高等数学-2' AND  StudentResult>=80

-- 再改造(由里及外)
SELECT StudentNO,StudentName FROM student WHERE StudentNO IN(
	SELECT StudentNO FROM result WHERE StudentResult>=80 AND SubjecNo=(
		SELECT SubjecNo FROM `subject` WHERE StudentName='高等数学-2'
)
)







/* 语法：语法：
select  -- 查询列表
from   -- 表明
where  -- 筛选条件
oredr by 排序列表 asc(升序) | desc(降序)

asc  是升序
desc   是降序
如果不写默认是升序

*/

-- 案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary desc    -- 降序

-- 案例2：查询部门编号>=90的员工信息。按入职时间的先后顺序进行排列
SELECT * 
FROM employees
WHERE department_id>=90;
ORDER BY hiredate ASC;

-- 案例三：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_poc,0))年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_poc,0)) DESC;    

-- 案例四：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_poc,0))年薪
FROM employees
ORDER BY 年薪 DESC;  

-- 案例五：按名字的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(lang_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(lang_name) DESC;

六、mysql中常见的函数

概念：类似于Java的方法；将一组逻辑语句封装在方法中；对外暴露方法名

好处：1、隐藏了实现细节 2、提高了代码的重要性

调用：select 函数名(实参列表) from 表；

-- ===============================数学函数==============================  玩玩就好
SELECT ABS(-1)  -- 绝对值 
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)   -- 向下取整
select RAND()         -- 返回一个随机数
SELECT SIGN(10)  -- 0返回0   整数返回1  负数返回-1

-- =============================== 字符函数=---------------------------
select CONCAT('我','爱','你')   -- 拼接
-- ========================= 日期函数==============
select CURRENT_DATE()   -- 获取当前日期
SELECT CURDATE()     -- 获取当前日期
SELECT  NOW()   -- 获取当前日期  精确到秒	
SELECT SYSDATE() -- 获取系统时间

七、聚合函数(常用)和分组过滤

-- ====================== 聚合函数==================
-- 都能够统计表中的数据(想查询一个表中有多少个记录就用COUNT)

SELECT COUNT('BornDate') FROM student; -- COUNT(字段)，会忽略所有的null值
SELECT COUNT(1) FROM student;   -- COUNT(*)，不会忽略所有的null值。本质还是计算行数
SELECT COUNT(*) FROM student;   -- COUNT(1)，不会忽略所以的null值，本质还是计算行数


SELECT SUM('StudentResult') AS 总和 FROM result
SELECT AVG('StudentResult') AS 平均数 FROM result
SELECT MAX('StudentResult') AS 最大值 FROM result
SELECT MIN('StudentResult') AS 最小值 FROM result


-- ============================分组和过滤========================================
-- 查询不同课程的平均分，最高分、最低分，平均分大于80；
-- 核心：（根据不同的课程分组）
SELECT SubjectName, AVG(studentResult) AS 平均分，MAX(studentResult) AS 最高分, MIN(studentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub 
ON r.SubjectNO = sub.SubjectNO
GROUP BY r.SubjectNO
HAVING 平均分>80

八、联表查询JoinON详解

七种JOIN对比

INNER JOIN

-- ======================================== 联表查询 join ==========================================================

-- 查询参加了考试的同学 (学号，姓名，科目编号，分数)  比如这个表里面没有姓名，那么就要到另一个表里面去查询  这就需要联表查询

SELECT * FROM `student`       -- 需要查的数据有的在这个表里
SELECT * FROM `result`         -- 有的在这个表里


-- 思路：1、分析需求：分析查询的阻字段来自哪些表，如果超过一个表那么就需要连接查询
-- 2、确定使用哪种连接查询？  7种
-- 3、确定交叉点(就是交集，确定两个表中哪些数据是相同的)
-- 判断条件：学生表中的studentNO = 成绩表中的 studentNO     因为同一学生在两个表中存在，唯一的交集就是studentNO相通


        --  学号，     姓名，       科目编号，     分数
SELECT studentno,studentname,subjectno，studentresult -- 这里 studentno 是 student表 和 result表 共有的属性  
-- studentname属性在student表里；subjectno和  studentresult 在result表里
FROM student  -- 在 student 表拿上面的四个属性(因为其中连两个是没有的 所以拿不完 所以要联表)
INNER  JOIN result  -- 连接result表 连接之后 它们两个都有studentno这个属性 所以就要取别名



SELECT studentno,studentname,subjectno，studentresult
FROM student AS s -- 把student表取别名为s
INNER  JOIN result AS r  -- 把result表取别名为r 

-- 然后想取哪个表的studentno就变得非常简单，就在第一行选择那里用取的 别名.studentno就OK  像这样

SELECT s.studentno,studentname,subjectno，studentresult
FROM student AS s
INNER  JOIN result AS r         -- 这里拿的是student表的 studentno


SELECT r.studentno,studentname,subjectno，studentresult
FROM student AS s
INNER  JOIN result AS r         -- 这里拿的是result表的studentno


-- 最后还有一个交叉值没有写 
SELECT s.studentno,studentname,subjectno，studentresult
FROM student AS s
INNER  JOIN result AS r   
WHERE s.studentno = r.studentno  -- 让学生表的studentno = 成绩表的 studentno

-- 最后就是这个样子
SELECT s.studentno,studentname,subjectno，studentresult
FROM student AS s
INNER  JOIN result AS r   
WHERE s.studentno = r.studentno


-- ==========================================================  RIGHT  JOIN =========================================================
                                           id  mmmm         aaaa              -- 这是a表
		 							------  ---------  --------
    								    1  张三           20
    								    2  王五           40
    								    3  周芷若         10
    								    4  小龙女         16
    								    
    							-- --------------------------------------------------------------	    
    								    
    								    name          age      id  
										---------  ------  --------            -- 这是b表
                                            殷素素         19      1
                                            hzx            19     3
                                            赵敏           20      5
                                            xxx            20      7
                                            
-- ---------------------------------------------------------------------------------------------------------
SELECT *
 FROM a                                                                  
RIGHT JOIN b
ON a.`id`= b . `id` -- 右查询  答案如下

    id  mmmm         aaaa  name          age      id  
------  ---------  ------  ---------  ------  --------
     1  张三             20  殷素素            19         1
     3  周芷若            10  hzx            19         3
(NULL)  (NULL)     (NULL)  赵敏             20         5
(NULL)  (NULL)     (NULL)  xxx            20         7

-- ---------------------------------------------------------------------------------------------------------------
SELECT *
 FROM a
LEFT JOIN b
ON a.`id`= b . `id`               -- 左查询  答案如下

    id  mmmm         aaaa  name          age      id  
------  ---------  ------  ---------  ------  --------
     1  张三             20  殷素素            19         1
     3  周芷若            10  hzx            19         3
     2  王五             40  (NULL)     (NULL)    (NULL)
     4  小龙女            16  (NULL)     (NULL)    (NULL)


-- 总结 MYSQL中左(右)连接的区别
-- 右连接：在 RIGHT JOIN 右边的数据会会全部查出来，左边只会查出ON后面符合条件的数据，不符合的会用NULL代替
-- 左连接：在 LEFT JOIN 左边的表里面数据被全部查出来，右边的数据只会查出符合ON后面的符合条件的数据，不符合的会用NULL代替。

操作	描述
INNER JOIN	如果表中至少有一个匹配；就返回行
RIGHT JOIN	即使右边表中没有匹配；也会从左表中返回所有的值
LEFT JOIN	即使左边表中没有匹配；也会从右边表中返回所有的值

自连接(了解即可)

自己的表和自己的表连接，核心：一张表拆成两张一样的表

九、数据库级别的MD5加密 (扩展)

什么是MD5？

主要增强算法的复杂的和不可逆性。

MD5不可逆 MD5破解网站原理，背后有一个字典，MD5加密后的值加密前的值

-- ===========测试MD5加密===============
CREATE TABLE `testmd5`(
`id` INT(5) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd`  VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 铭文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')

-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1  -- id = 1 的密码被加密了
UPDATE testmd5 SET pwd=MD5(pwd) -- 整个testmd5 表被加密了

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))

-- 如何校验  将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

这篇关于数据库篇2（增删查改）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！