一、在Cenos7上部署mysql5.7
1.通过Xshell 连接到CentOS7服务器
进入到目录/usr/local/中
2.创建目录/usr/local/tools
mkdir -p tools
3创建 /usr/local/mysql 目录,如果已存在则忽略:
mkdir -p mysql
4 进入到目录 /usr/local/tools 中:
cd tools/
5 查看系统中是否已安装 MySQL 服务:
rpm -qa | grep mysql
或
yum list installed | grep mysql
6 如果已安装则删除 MySQL 及其依赖的包:
yum -y remove mysql-libs.x86_64
7 下载 mysql57-community-release-el7-8.noarch.rpm 的 YUM 源:
wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
8 安装 MySQL:
yum install mysql-server
遇到疑问的地方,一路 Y 下去即可;
9.安装完成后,在var/log/mysqlg.log文件中自动生成一个随机密码,首先取到这个随机密码
grep "password" /var/log/mysqld.log
10,安装完成后在cd var/log/mysqld.log
cat mysql.log
#查看日志,获取密码
11.利用默认密码登录后,执行修改密码操作(密码必须是大写字母,小写字母,特殊符号,数字)
12.执行完之后退出quit,使用修改后的密码重新登录。
ps:一开始报mysql.socket找不到,以为是mysql.sock 丢失
直接重装,然并卵。
1.重启mysql服务 systemctl restart mysqld.service
2.shutdowm -h now 关机重启
二、MySQL常用函数
1.数值操作函数:
绝对值:ABS(数值)
圆周率值:pi()
求二次平方根:SQRT(数值)
求余运算:MOD(除数,被除数)
四舍五入:ROUND(目标数值,保留小数点后几位) ,TRUNCATE(目标数值,保留小数点后几位)
乘方运算:POW(目标值,乘方),POWER(目标值,乘方)
计算e的乘方:EXP(目标值);
角度转换为弧度:RADIANS(角度数)
弧度转换为角度:DEGREES(弧度数)
2.字符操作函数:
计算字符长度函数:CHAR_LENGTH(字符串),LENGTH(字符串)
连接字符串函数:CONCAT(str1,str2,str3) 输出 str1+str2+str3
字符串替换:INSERT(目标字符,目标字符索引值,插入字符长度,插入字符串)
小写转大写:LCASE(), UPPER(), UCASE()
大写转小写:LOWER()
返回字符串中左边的字符:LEFT(目标字符,索引值)
返回字符串中右边的字符:RIGHT(目标字符,索引值)
对字符串进行填充操作:LPAD(目标字符,该字符的长度,填充字符)RPAD(目标字符,该字符的长度,填充字符)
删除字符串中两端指定的字符:TRIM( 删除字符 from 目标字符)
重复生成相同的字符串:REPEAT(字符串,字符串个数 )
字符串替代操作:REPLACE(目标字符串,目标字符串中需要替换的字符,替换成什么字符)
比较字符串大小:STRCMP(str1,str2)
获取指定位置处的子字符串: SUBSTRING(目标字符,索引值,切割个数),MID()
获取系统当前日期:CURDATE()
当前日期加时间:CURRENT_TIMESTAMP()
返回时间戳:UNIX_TIMESTAMP()
将UNIX时间戳转换为普通格式时间:FROM_UNIXTIME(时间戳)
三、自定义函数
格式:
create function 函数名(参数1,参数2,参数n)
returns 返回值类型
begin
函数体
end
例:
#根据id查询出userinfo表中的name
delimiter $$
CREATE FUNCTION getNameByID(id INT(10))
RETURNS VARCHAR(255)
BEGIN
DECLARE result VARCHAR(255);
set result=(SELECT u_name FROM userinfo WHERE id=id LIMIT 1);
RETURN CASE (result IS NULL)
WHEN TRUE THEN
'输入参数错误'
WHEN FALSE THEN
result
END;
END
$$
实现数据转移例子:
delimiter $$
CREATE FUNCTION getDataInsertTable(id INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE pwd VARCHAR(20);
DECLARE uname VARCHAR(20);
SET uname=(SELECT u_name from userinfo WHERE id=id LIMIT 1);
if (uname is null)
THEN
RETURN '您搜索的数据不存在';
ELSE
SET pwd=(SELECT u_pwd from userinfo WHERE id=id LIMIT 1);
END IF;
INSERT INTO fortest(IP,OID) VALUES(uname,pwd);
IF ROW_COUNT()>0
THEN
RETURN '插入成功';
END IF;
END
$$
函数执行:
select 函数名(参数)
如果需要在函数中调用函数并赋值的话,可以这么写(注意看看其返回的是什么类型):
declare num int(11);
set num=函数名(参数)
函数删除:
drop function if exists 函数名
四、存储过程
格式:
delimiter $$
create procedure 方法名
begin
函数体
end
$$
删除存储过程:
drop procedure if exists 存储过程名
执行:
call procedure_name(参数)
例:
delimiter $$
CREATE PROCEDURE insertDAta()
begin
set @a=1;
SET autocommit=0;
while @a<100000000 do #如果@a<2000010001则返回true,继续执行
#如果@a<2000010001则返回true,继续执行
set @b=rand_name(5); #随机赋值,值为5位a-zA-Z的任意组合
set @c=rand_sex(1); #随机赋值,值为1位,0或者1
insert into student(name,sex) values(@b,@c);
set @a=@a+1;
end while;
COMMIT;
End
$$
注意:
1.return 只能在函数中使用不能在存储过程中使用
2.存储过程参数,不能在参数名称前加‘@’, 不加@是用户自定义的变量, 加@是用户变量,作用域是当前用户。
3.mysql 存储过程中的每条语句末尾,都要加上分号
4.mysql 存储过程参数没有默认值,所以调用存储过程时,不能省略参数,可以用null 代替。
五、MySQL的事务处理
主要有两种方法
1.用begin,rollback,commit来实现
begin 开始一个事务
rollback 事务回滚
commit 事务确认
2.直接用set来改变MySQL的自动提交模式
MySQL默认是自动提交的,比如你提交一个查询,就直接执行了。
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
例:
事务处理
A账户汇款失败
SELECT * FROM account;
#第一步 关闭事务自动提交模式
SET autocommit=0;
#第二步 开始事务
START TRANSACTION;
#第三步 发现汇款失败,将事务回滚ROLLBACK || 汇款成功将事件commit
UPDATE account SET money=money-500 WHERE username='A';
SELECT * FROM account;
UPDATE account SET money=money+200 WHERE username='B';
ROLLBACK;
#第四步 还原Mysql数据库的的自动提交
SET autocommit=1;
SELECT * FROM account;
也可以参考批插入百万条数据,在存储过程中也是使用这种方式,首先把所有的数据都生成好,然后一次提交。
事务的隔离等级
1.读取未提交级别(Read-Uncommitted)
当事务A更新一条记录未提交时,事务B可以查询出事务A更新的记录。
事务A开启了一个隔离等级为读取未提交内容级别
事务B首先查询数据,然后更新一条数据,事务B的数据已经发生改变。
由下图可知,尽管我们没有进行commit操作,事务A中数据已经发生改变。事务B回滚,事务A数据又变回原始状态。
这种级别事务很不安全。
-
Read-committed(读取提交级别)
首先事务A 开启一个读取提交级别的事务,然后事务A更新一条数据,事务A查询数据,发现数据已经改变。
事务B查询时,数据未发生改变。
然后我们对事务A进行commit,事务B也发生改变。
事务B也跟着发生改变
这种隔离级别只有在事务提交后,才会对其他事务产生影响。
3.REPEATABLE READ(可重读)
当事务A开启一个可重读级别的事务,然后事务B对数据进行更新操作,并提交,查询事务B中数据发生改变,查询事务A,发现事务A并没有发生改变,当事务A进行commit操作,然后再进行查询时,发现数据已更新。 -
SERIERLIZED(可串行化)
当事务A开启一个可串行化事务后,然后事务B开启一个事务,并对数据进行更新操作,不提交事务,事务A进行查询操作,然后一直在等待,直到等待超时。
事务A:
事务B:
六、行转列和列转行
行转列AND 列转行
行转列
1.首先创建一张表
2.写出需要运行的sql语句
SELECT sutdent_name as '姓名',SUM(CASE WHEN subject='语文' THEN score END) as '语文',SUM(CASE WHEN subject='数学' THEN score END) as '数学', SUM(CASE WHEN subject='英语' THEN score END) as '英语'
from score_info
GROUP BY sutdent_name;
3.显示结果:
列转行
1.准备数据表(模拟用户权限)
2.准备sql 语句运行
SELECT operator_id,op_userid,GROUP_CONCAT(permission) as 'permission' from operator GROUP BY operator_id;
group_concat():该函数返回带有来自一个组的连接的非NULL值的字符串结果,其实函数内部执行了group by.
3.显示结果