MySQL中的函数
<1> 加密函数
- password(str)
- 该函数可以对字符串str进行加密，一般情况下，此函数给用户密码进行加密
- select password(‘ruochen666‘);
- select PASSWORD(ename) from emp;
- md5(str)
- 对字符串str进行散列加密，可用户对于一些普通的不需要解密的数据进行加密
- select MD5(‘ruochen666‘);
- select MD5(ename) from emp;
<2> 聚合函数
- avg()
- select avg(sal) from emp; # 平均薪资
- count()
- select count(*) from emp; # 员工数量
- min()
- select MIN(sal) from emp; # 最低薪资
- max()
- select MAX(sal) from emp; # 最高薪资
- sum()
- select SUM(sal) from emp; # 薪资求和
<3> 数学相关的函数
- rand()
- 返回0～1的随机数
- select RAND();
- ads(num)
- 获取num的绝对值
- select ABS(-1);
- mod(num1, num2)
- 取余数
- select MOD(10, 3);
- ceiling(num)
- 向上取整
- select CEILING(10.1);
- floor(num)
- 向下取整
- select FLOOR(10.1);
- round(num)
- 返回离num最近的数 --- 四舍五入
- select ROUND(10.4);
- round(num, n)
- 保留num小数点后的n位
- select ROUND(10.12345, 2);
- least(num, num2 ....)
- 求最小值
- select LEAST(10, 5, 11, 23, 54);
<4> 字符相关的函数
- char_length(str)
- 返回字符串str的长度，单位是字符
- select CHAR_LENGTH(‘ruochen‘);
- length(str)
- 返回字符串str的长度
- select LENGTH(‘ruochen‘);
- instr(str, substr)
- 返回字符串str中子字符串substr的位置
- select INSTR(‘ruochen‘, ‘chen‘);
- insert(str1, index, len, str2)
- 将字符串str2替换str1的index开始长度为len的数据
- select INSERT(‘123456‘, 1, 3, ‘abc‘);
- replace(str, oldstr, newstr)
- 字符串替换
- select replace(‘hello‘, ‘ll‘, ‘oo‘);
- left(str, len) / right(str, len)
- 返回字符串str的前 / 后len的字符串
- select LEFT(‘helloword‘,5);
- select RIGHT(‘helloword‘,4);
<5> 日期函数
- now()
- 获取当前日期和时间
- select now();
- curdate() / current_date();
- 获取当前日期
- select curdate();
- select current_date();
- curtime() / current_time();
- 获取当前时间
- select curtime();
- select current_time();
- year(date)
- 获取年份
- select year(‘1995-01-01‘);
<6> 流程控制函数
- if(expr, v1, v2)
- 如果表达式expr成立，则返回v1的值，否则返回v2的值
- select if(5>3, ‘大于‘, ‘小于‘);
- ifnull(v1, v2)
- 如果v1的值不为null，则返回v1的值，否则返回v2的值
- select ifnull(‘ruochen‘, ‘‘);
- nullif(expr1, expr2)
- 如果expr1=expr2，则返回null值，否则返回expr1的值
- select nullif(2, 2);
MySQL用户创建和授权(DCL)
<1> 授权
-
语法格式
- GRANT 权限 ON 数据库.表 TO 用户名@登陆的主机 IDENTIFIED BY 密码;
-
新建数据库testdb create datebase testdb;
-
新建用户 laowang 并赋予 testdb数据库相应的权限 grant all privileges on testdb.* to laowang@localhost identified by ‘123‘;
-
如果想指定部分的权限给用户 grant select, update on testdb.* to laowang@localhost identified by ‘123‘;
-
赋予用户 laowang 所有数据库的某些权限 grant select, update, insert, create, drop on . to laowang@"%" identified by ‘123‘;
注意：all privileges, ., "%"(所有的登陆主机)
常用的权限 select 对所有表进行查询操作 insert 对所有表进行插入操作 update 对所有表进行更新操作 delete 对所有表进行删除操作 create 数据库、表、索引 drop 数据库和表的删除操作 alter 对所有表进行更改
<2> 取消权限
- revoke 权限 on 数据库.表 from 用户名@登陆的主机;
- revoke select on testdb.* from laowang@localhost;
<3> 显示授予的权限
- show grants;
<4> 删除用户
- use mysql;
- delete from user where user=‘laowang‘;
- flush privileges; -- 刷新权限
MySQL中的索引
<1> 概念
- 是一种高效获取数据的数据结构(树), 以文本的形式存在，以某种数据结构存储特定的数据(地址)。
- 好比是一本书的目录，能够加快数据库查询的速度。
<2> 作用
- 提高查询的效率
- 在多表连接的时候，作为条件加速连接的速度
- 在分组和排序检索数据的时候，减少分组和排序所消耗的时间
<3> 类型
-
普通索引(最基本的索引)
- 创建索引
-
语法格式：create index index_name on table_name(column)
create table index_tb1( id int primary key auto_increment, name varchar(32) ); create index index_name on index_tb1(name); 格式：alter table table_name add index index_name(column);
-
- 查看索引
- 语法格式：show index from table_name;
- 删除索引
- 语法格式：drop index index_name on table_name;
- drop index index_name on index_tb1;
- 创建索引
-
唯一索引
-
主键索引
注意：使用整型优于字符型，额外维护一个与其他字段不相关的列，一般设置为整数类型并且自增长。
MySQL数据库的备份和恢复
<1> 数据库的备份
- 使用mysqldump 命令可以生成指定的数据库脚本文件
- 语法：
- mysqldump -uroot -p密码 数据库名称 > 生成的脚本文件存放的路径
- 例如；
- mysqldump -uroot -proot ruochen > D:\ruochen.sql（sql脚本文件） 不需要登录到mysql数据库中
<2> 数据库的恢复
-
语法格式：
- soure 脚本文件存放的路径 (先选择恢复到目的数据库，需要登录到mysql数据库中)
- mysql -uroot -p 密码 新数据库 < 脚本文件存放的路径（不需要登陆到mysql数据库中）
-
注意
- 指定数据存放的数据库
- 表名称不要重复
数据库的优化
1. 遵循设计的范式
- 第一范式：对属性的原子性的约束，属性不能再分解
- 第二范式：对记录的唯一性的约束，要求每一条记录都有唯一标识，实体完整性
- 第三范式：对字段的冗余约束，字段没有冗余。允许字段适当的冗余，提高数据操作的效率。
2. 选择合适的存储引擎
- Innodb
- 支持事务
- 行级别的锁
- 外键
- 推荐使用
- MyISAM
- 不支持
- 表级别的锁
- 不支持外键
- 查询效率更高
3. 选择合适的数据类型
- 优先考虑整型、日期、字符
- 推按使用枚举，enum(‘男‘, ‘女‘)
4. 适当的建立索引
- where、order by、group by 后面经常出现的字段
- 多表的连接的字段
5. 避免使用 select *
6. 为每一张表设置id并且自增长
7. 只需要查询一条数据，limit 1