MySql教程

MySQL篇——数据管理,DML管理、DQL查询

本文主要是介绍MySQL篇——数据管理,DML管理、DQL查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

  • MySQL数据管理
  • DML(数据库管理语言 重点)
  • DQL(数据库查询语言 使用频率最高)
    • where条件子句
    • 联表查询
    • 分页/排序/子查询
    • MySQL常用函数
    • 聚合函数(常用)
    • 数据库级别的MD5加密
    • where和having的区别

MySQL数据管理

外键(了解)
首先先创建一个年级表,其中有一个字段grade_id作为外键约束字段:

-- 创建一个年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`grade_name` VARCHAR(30) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式一:在创建表时增加约束(缺点:复杂)

-- 创建一个学生表,学生表的grade_id字段引用年级表的grade_id
-- 定义外键key,给这个外键添加约束(执行引用[references])
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) DEFAULT '张三' COMMENT '姓名',
	`age` INT(11) COMMENT '年龄',
	`sex` CHAR(2) DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`grade_id` INT(11) NOT NULL COMMENT '年级id',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	PRIMARY KEY (`id`),
	KEY `FK_grade_id` (`grade_id`),
	CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

要删除有外键关系的表时,必须要先删除引用外键的表(从表),再删除被引用的表(主表)。

方法二:创建表之后添加外键约束

-- 创建表时没有指定外键关系
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) DEFAULT '张三' COMMENT '姓名',
	`age` INT(11) COMMENT '年龄',
	`sex` CHAR(2) DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`grade_id` INT(11) NOT NULL COMMENT '年级id',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 使用修改语句添加外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
-- 以上语法为:
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的字段名) REFERENCES 关联外键的表名(外键约束字段名)

-- 删除外键	ALTER TABLE 表名 DROP FOREIGN KEY 外键名字
ALTER TABLE `student` DROP FOREIGN KEY `FK_grade_id`

以上的操作都是物理外键,数据库级别的外键不建议使用(避免数据过多造成困扰~)

最佳实践:

  1. 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段);
  2. 要想使用外键(一个地方引用另一个地方时),尽量使用程序实现

DML(数据库管理语言 重点)

  1. 添加(insert)
-- 插入语句(添加)
-- insert into 表名 (字段名1, 字段名2, 字段名3, ...) values ('值1', '值2', '值3', ...)
-- 如果设置了主键自增,则可以忽略主键字段
INSERT INTO `grade` (`grade_name`) VALUES ('一年级1班')

-- 如果不写values前面的对应的字段名,它就会对字段进行一一匹配
INSERT INTO `grade` VALUES (NULL,'一年级2班') -- 设置NULL是为了使主键自增
INSERT INTO `student` VALUES (NULL, '张三', 22, '男', '2000-01-01', 1, '山东济南')

-- 插入多个字段(中间使用逗号隔开)
INSERT INTO `grade` (`grade_name`) VALUES ('一年级3班'), ('一年级4班')

注意事项:
字段和字段之间使用英文逗号隔开;
字段名可省略,但values后面的值必须要一一对应;
可同时插入多条数据,在values后面,每条数据使用括号括起来,使用逗号隔开

  1. 修改(update)
-- 语法:UPDATE 表名 SET 字段名 = 新值 [WHERE=条件]
-- 不指定条件的情况下,会改动所有表(慎重~~~)
UPDATE `student` SET `name` = '王五' WHERE `name` = '张三'

-- 修改多个属性,逗号隔开
UPDATE `student` SET `age` = 20, `grade_id` = 2 WHERE `name` = '王五'

条件:where子句,运算符

操作符含义举例结果
=等于5=6false
<> 或 !不等于5<>6true
>大于2>1true
<小于2<3true
<=小于等于2<=2true
>=大于等于4>=5false
BETWEEN…AND…在某个范围内[2,5]
AND&&2>1and3>4false
OR||2>1and3>4true
CURRENT_TIME	-- 函数:表示当前时间
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '王五'
  1. 删除(delete & truncate)

delete命令

-- 语法
DELETE FROM 表名 [WHERE 条件]
-- 删除数据
DELETE FROM `student`	-- 清空student表(不建议使用)

-- 删除指定行
DELETE FROM `student` WHERE id = 1

truncate命令 作用:完全清空一个数据库表,表的结构和索引约束不变

-- 清空student表
TRUNCATE TABLE `student`

delete & truncate 的异同
同:都能删除数据,都不会删除表结构
异:truncate可以重新设置自增列,计时器会归零,且truncate不会影响事务;delete不会影响自增(会在原来的基础上自增),计时器不会归零,且会影响事务

了解DELETE删除的问题,用delete执行完清空表中的数据操纵后,重启数据库,现象:

  1. 如果是INNODB引擎,则自增列会重新从1开始(原因:INNODB引擎的数据库存在内存中,断电即失)
  2. 如果是MyISAM引擎,则继续从上一个自增量开始(原因:MyISAM引擎的数据库存在文件中,不会丢失)

DQL(数据库查询语言 使用频率最高)

-- 起别名 AS
-- 函数 CONCAT(a, b, ...)
SELECT CONCAT('姓名',':',`name`) AS `name` FROM `student`

去重(去掉重复数据) distinct
作用:取出查询出来的重复数据,重复的数据只显示一条

SELECT DISTINCT `name` FROM `student`

数据库的列(表达式)

-- 查询系统版本(函数)
SELECT VERSION()
-- 计算(表达式)
SELECT 10*2+2 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment

数据库中的表达式:文本值,列,NULL,函数,计算表达式,系统变量…

where条件子句

作用:检索数据中符合条件的值。
搜索的条件有一个或多个表达式组成,表达式之间用and连接,结果为布尔值。

逻辑运算符

运算符语法描述
and(&&)a and b(a && b)逻辑与,表达式两边都为真结果即真
or(||)a or b(a || b)逻辑或,表达式其中一个为真,结果即真
not(!)not a(!a)逻辑非,真为假,假为真
-- 模糊查询(区间)
SELECT * FROM `student` WHERE `age` BETWEEN 16 AND 20	--查询年龄16-20的学生

模糊查询:比较运算符

运算符语法描述
IS NULLa IS NULL如果操作符为NULL,结果为真
IS NOT NULLa IS NOT NULL如果操作符不为NULL,结果为真
BETWEENa BETWEEN b AND c如果a在b和c之间,则结果为真
LIKEa LIKE bSQL匹配,如果a匹配b,则结果为真
INa IN(a1, a2, a3, …)假设a在a1,a2,a3其中某一个值中,结果为真

like结合:

  1. % 代表0到任意个字符
  2. _ 匹配一个字符

联表查询

七种join理论:
在这里插入图片描述

操作描述
inner join查询两表的并集,不查询包含null值的数据
left join从左表中返回所有值,即使右表中没有匹配
right join从由表中返回所有值,即使左表中没有匹配
-- join (连接的表) on(判断的条件)	连接查询
-- where	等值查询
/*
	联表查询思路:
		1.分析需求,分析查询的字段来自哪几张表
		2.确定使用哪种连接查询
		3.确定交叉点
*/

分页/排序/子查询

SELECT 语法

SELECT [ALL | DISTINCT]
{* | 表名.* | [表1.字段名1[as 别名1] [, 表1.字段名2 [as 别名2]] [, ...]]}
FROM 表1 [as 别名1]
	[left | right | inner join 表2]	-- 联合查询
	[WHERE ...]	--指定条件
	[GROUP BY ...]	-- 指定结果按照哪个字段分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];	--指定查询的记录从哪条到哪条

注意:[]括号代表可选条件,{}括号代表必选条件。

排序

-- 排序	升序:ASC	降序:DESC
-- ORDER BY 通过哪个字段排序 ASC | DESC

分页
为什么要分页?缓解数据库压力,给用户更好的体验。

-- LIMIT 起始页,页面显示记录条数	第一页从0开始
-- 第一页:LIMIT 0,5
-- 第二页:LIMIT 5,5
-- 第三页:LIMIT 10,5
-- 第N页:LIMIT (n-1) * pageSize, pageSize
-- [pageSize:页面大小]
-- [n:当前页]
-- [总页数 = 数据总记录数 / 页面显示记录条数]
-- 语法:LIMIT (查询起始下标, pageSize)

子查询/嵌套查询(由里及外):在where语句中嵌套一个子查询语句

-- 查询王五的学生信息(姓名,班级,住址)
SELECT
	s.`name`,
	g.grade_name,
	s.address 
FROM
	student s
	INNER JOIN grade g ON s.grade_id = g.grade_id 
WHERE
	s.`name` = (
	SELECT
		s.`name` 
	FROM
		student s 
WHERE
	s.`name` = '王五'
	)
-- 其中,where中条件的值是计算出来的用子查询更好一些,以上查询没必要用子查询,直接用下面的方法即可查询到想要的学生信息
SELECT
	s.`name`,
	g.grade_name,
	s.address 
FROM
	student s
	INNER JOIN grade g ON s.grade_id = g.grade_id 
WHERE
	s.`name` = '王五'

-- 由于子查询还需要再查一遍数据库,如果数据量较大则会导致查询较慢,工作中应尽量避免使用到子查询(需要用到时也不能吝啬)

MySQL常用函数

对应MySQL官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

-- 数学运算
SELECT ABS(-1)	--绝对值
SELECT CEILING(8.5)	-- 向上取整
SELECT FLOOR(8.5)	-- 向下取整
SELECT RAND()	-- 返回一个0~1之间的随机数
SELECT SING(10)	--判断一个数的符号	0:返回0,负数:返回-1,正数:返回1

-- 字符串函数
SELECT CHAR_LENGTH('获取字符串长度') char_length	--字符串长度	结果:7
SELECT CONCAT('hello', 'world') str	-- 拼接字符串	结果:helloworld
SELECT INSERT('好好学习天天向上', 1,2,'热爱')	--插入,从某个位置(第一个字符的下标从1开始)替换指定长度	结果:热爱学习天天向上
SELECT UPPER('mysql')	-- 字母全部转大写	结果:MYSQL
SELECT LOWER('MySQL')	-- 字母全部转小写	结果:mysql
SELECT INSTR('hello','o')	-- 返回第一次出现的子串的索引	结果:5
SELECT REPLACE('好好学习天天向上','好好','天天')	-- 替换出现的指定字符串	结果:天天学习天天向上
SELECT SUBSTR('好好学习天天向上', 1,4)	-- 截取指定长度的字符串(源字符串,截取位置,截取的长度)如果不指定截取的长度则截取到最后	结果:好好学习
SELECT REVERSE('好好学习天天向上')	-- 反转	结果:上向天天习学好好

-- 时间和日期函数
SELECT CURRENT_DATE()	--获取当前日期
SELECT CURDATE()	-- 获取当前日期
SELECT NOW()	-- 获取当前时间
SELECT LOCALTIME()	-- 获取本地时间
SELECT SYSDATE()	-- 获取系统时间
SELECT YEAR(NOW())	-- 获取当前年
SELECT MONTH(NOW())	-- 获取当前月
SELECT DAY(NOW())	-- 获取当前日
SELECT HOUR(NOW())	-- 获取时
SELECT MINUTE(NOW())	-- 获取分
SELECT SECOND(NOW())	-- 获取秒

-- 关于系统函数
SELECT SYSTEM_USER()	-- 获取当前系统用户:root@localhost
SELECT USER()	-- 获取当前用户:root@localhost
SELECT VERSION()	-- 获取当前版本:8.0.25

聚合函数(常用)

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
-- 统计表中共有多少记录数
SELECT COUNT('grade_name') FROM grade	-- COUNT(列名),会忽略字段对应列中的所有null值
SELECT COUNT(*) FROM grade	-- COUNT(*),不会忽略null值,本质:计算行数
SELECT COUNT(1) FROM grade	-- COUNT(1),不会忽略null值,本质:计算行数
/*
	效率上:
		列名为主键,COUNT(列名)比COUNT(1)快
		列名不为主键,COUNT(1)比COUNT(列名)快
		如果表没有主键,则COUNT(1)比COUNT(*)快
		如果有主键,则SELECT COUNT(主键)执行效率最优
		如果表只有一个字段,则SELECT COUNT(*)最优
*/

数据库级别的MD5加密

MD5:增强算法复杂度和不可逆性,对数据进行加密

MD5破解网址(破解简单密码):https://www.cmd5.com/

-- 新建一个测试表,用于测试MD5加密和解密
CREATE TABLE IF NOT EXISTS `testmd5`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`pass` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 添加数据
INSERT INTO testmd5 VALUES (NULL, '张三', '123'),(NULL, '李四', '123'),(NULL, '王五', '123')

-- 修改密码为加密密文
UPDATE testmd5 SET pass = MD5(pass) WHERE id = 1

UPDATE testmd5 SET pass = MD5(pass)	-- 加密全部密码

-- 插入时加密
INSERT INTO testmd5 VALUES (NULL, '赵六', MD5('123'))

-- 校验:将用户传递进来的密码进行md5加密,比对加密后的值
SELECT * FROM testmd5 WHERE `name` = '张三' AND pass = MD5('123')

where和having的区别

where:

  1. where是一个约束声明,使用where来约束来自数据库的数据;
  2. where是在结果返回之前起作用的;
  3. where中不能使用聚合函数。

having:

  1. having是一个过滤声明;
  2. 在查询返回结果集以后,对查询结果进行的过滤操作;
  3. 在having中可以使用聚合函数。
这篇关于MySQL篇——数据管理,DML管理、DQL查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!