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范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范性 和 性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑以下规范性！
- 故意给某些增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）