官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html
5.1、常用函数
-- ==============常用函数===============
-- 数字运算
SELECT ABS(-7) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 负数返回-1,正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('我爱数据库') -- 字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT LOWER('ZhangGuorong') -- 小写字母
SELECT UPPER('ZhangGuorong') -- 大写字母
SELECT INSERT('我爱编程helloWorld',1,2,'超级热爱') -- 查询,从某个位置开始替换某个长度
SELECT SUBSTR('荣哥说坚持就能成功',4,6) -- 返回指定的子字符串
SELECT REPLACE('荣哥说坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT REVERSE('你爱我') -- 反转
-- 查询 姓 周的同学 ,把周替换成张
SELECT REPLACE(studentName,'周','张') FROM student
WHERE studentName LIKE '周%'
-- 时间和日期函数 (记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW()) -- 显示当前年份
SELECT DAY(NOW()) -- 天
SELECT SECOND(NOW()) -- 秒
-- 系统
SELECT VERSION()
SELECT USER()
SELECT SYSTEM_USER()
5.2、聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
... | ... |
5.3数据库级别的MD5加密(扩展)
什么是MD5
主要增强算法的复杂性和不可逆性
MD5 不可逆,具体的值的 MD5 是一样的
MD5 破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值
-- 加密
UPDATE student SET pwd=MD5(pwd) WHERE id=1
UPDATE student SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO student (`id`,`name`,`pwd`) VALUES('13','胡',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM student WHERE `name` = '胡' AND pwd=MD5('123456')
select总结
6、事务
6.1、什么是事务
要么都成功,要么都失败
事务原则:ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读。。。)
原子性:要么都成功,要么都失败
一致性:事务前后的数据完整性要保证一致
持久性:事务一旦提交则不可逆,被持久化到数据库中
隔离性:事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数数据所干扰,多个并发事务之间要相互隔离
-- 模拟转账:事务
SET autocommit = 0 -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET money = money-500 WHERE `name` = 'A' -- A减500
UPDATE `account` SET money = money+500 WHERE `name` = 'B' -- B加500
COMMIT; -- 提交事务,就被持久化了,这时候在按回滚就回不去了
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
7、索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引 (PRIMARY KEY)
-
-
唯一的标识,主键不可重复,只能有一个列作为主键
-
-
唯一索引(UNIQUE KEY)
-
-
避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
-
-
常规索引 (KEY/INDEX)
-
-
默认的, index、key关键字来设置
-
-
全文索引(FullText)
-
-
在特定的数据库引擎下才有,MyISAM
-
快速定位数据
-
8、权限管理和备份
8.1、用户管理
SQL yog 可视化管理
SQL命令操作
用户表:mysql.user
本质:读这张表进行增删改查
-- 创建用户 create user 用户名 identified by '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('111111')
-- 重命名
RENAME USER kuangshen TO zhang
-- 用户授权 all privileges 全部的权限, 库.表
-- all privileges 除了给别人授权,其他都能干
GRANT USER PRIVILEGES ON *.* TO kuangshen2
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户权限
SHOW GRANTS FOR root@localhost
-- ROOT用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
-- 删除用户
DROP USER kuangshen
8.2、MySQL备份
为什么要备份:
-
保证重要的数据不丢失
-
数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件(在date文件里面)
-
在Sqlyog这种可视化工具中手动导出
-
-
右键点击表,选择备份或导出
-
-
使用命令行导出 mysqldump 命令行使用(cmd)
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school>d:/a.sql
#导入(先在命令行登录数据库,然后在use 要导入的数据库后在执行以下操作)
#登录的情况中,切换到指定的数据库
#source 备份文件
1.source d:/a.sql (推荐使用)
2.mysql -u用户名 -p密码 库名< 备份文件(不推荐)
假设你要备份数据库,防止数据丢失。
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
-
数据冗余,浪费空间
-
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
-
程序的性能差
良好的数据库设计:
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中,关于数据库的设计
-
分析需求:分析业务和需要处理的数据库需求
-
概要设计:设计关系图E-R图
9.2、三大范式
为什么需要数据规范化?
-
信息重复
-
更新异常
-
插入异常(无法正常显示信息)
-
删除异常(丢失有效的信息)
三大范式(了解)
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一、er范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范性 和 性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
-
在规范性能的问题的时候,需要适当的考虑以下规范性!
-
故意给某些增加一些冗余的字段。(从多表查询中变为单表查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
-