用户管理与高级SQL语句

靑木312

于 2024-08-01 16:52:07 发布

阅读量428

点赞数 15

文章标签： sql 运维服务器 mysql linux

本文链接：https://blog.csdn.net/minly12/article/details/140851830

版权

一、详细用户管理

//MySQL基本指令
[root@localhost ~] -uroot -ppwd123        //登录MySQL，-u(指定账号)-p(密码)
[root@localhost ~] -uroot -ppwd123 mysql        //直接登录到mysql数据库里面
[root@localhost ~] -uroot -ppwd123 mysql -h localhost        //本地登录，-h 指定登录用户
[root@localhost ~] -uroot -ppwd123 mysql -e 'select * from user\G'        //-e：输入mysql'指令;'
[root@localhost ~] -ulisi -p123456 -h 192.168.10.101 -P 3306        //-h指定目标主机ip，-P远程目标主机的端口(Post)

mysql> use mysql;         //进入mysql数据库，与MySQL有关信息都在里面
mysql> show tables;    //展示所有表
mysql> select * from user\G         //查询所有user表，\G：格式化输出

Host：localhost        //表示允许该账户在哪登录，本地登录
User：mysql.sys        //账户
//下面是用户权限

mysql> flush privleges;        //更新权限指令，使用update等指令更新时需要更新权限

1：用户修改

mysql> create user 'zhangsan'@'localhost' identified by 'pwd123';  //添加用户并指定登录主机，可以允许一个网段主机登录：192.168.10.%，指定密码

//查看用户权限去mysql数据库的user表
mysql> grant all on *.* to 'zhangsan'@'localhost' identified by 'pwd123';         //授予指定账户从本地登录的所有权限的所有库的所有表
mysql> grant all on *.* to 'zhangsan'@'192.168.10.%' identified by 'pwd123';     //添加指定账户允许从192.168.10.%网段登录新表
//grant语句在没有账号时可以创建此账户
//mysql> flush privleges;    //更新

mysql> drop user 'zhangsan'@'localhost';        //删除指定用户的本地登录
mysql> delete from mysql.user where user='zhangsan';    //删除指定用户所有的信息
mysql> delete from mysql.user where user='zhangsan' and host='localhost';    //删除指定用户的指定的允许登录主机

2：密码修改

//借助命令行修改
[root@localhost ~]# mysqladmin -uroot -ppwd123 password'123456'        //-u指定用户;-p验证用户身份，输入后Warning提示操作不合适

//在mysql里面修改密码，进入mysql数据库
mysql> select * from user\G;     
//找到想改的用户的密码
authentication_string：41位        //显示密码一行，老版本MySQL是password
mysql> update mysql.user set authentication_string=password('pwd123') where user='root' and host='localhost';    //修改指定用户登录主机密码
mysql> set password=password('123456');        //仅限于修改当前登录用户密码
mysql> set password for 'zhangsan01'@'localhost'=password('pwd123');     //修改其他用户和登录主机的密码
mysql> set password for 'zhangsan01'@'localhost'='123456';        //不指定修改user里面的账户函数密码也加密，其他表不使用加密函数不加密
//mysql> flush privleges;        //更新

//忘记密码修改方式
[root@localhost ~]# vim /etc/my.cnf
...
skip-grant-tables        //添加跳过数据库验证方式，不能用set更改密码了
[root@localhost ~]# systemctl restart mysqld         //需要重启mysql服务
[root@localhost ~]# mysql -uroot
mysql> update mysql.user set authentication_string='pwd123' where user='root'; 
//mysql> flush privleges;        //更新
[root@localhost ~]# vim /etc/my.cnf        //修改后再次进入配置文件
...
#skip-grant-tables        //添加注释或删除    
[root@localhost ~]# systemctl restart mysqld         //需要重启mysql服务

//另一种忘记密码修改方式
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &        //mysqld_safe早期unux启动mysql，mysql守护进程，在后台运行
[root@localhost ~]# mysql -uroot
mysql> update mysql.user set authentication_string='pwd123' where user='root';
//mysql> flush privleges;        //更新；退出
[root@localhost ~]# systemctl restart mysqld         //需要重启mysql服务

3：授权

mysql> grant select,update,insert on *.* to 'lisi'@'localhost' identified by 'pwd123';        //给指定账号指定的权限
mysql> show grants for 'lisi'@'localhost';        //查看指定用户的权限
mysql> rovke update on *.* from 'lisi'@'localhost';        //删除指定用户的指定权限
mysql> grant all on *.* to 'lisi'@'192.168.10.102' identified by 'pwd123';        
//可以使用192.168.10.%指定网段或全部改为%除了localhost以外所有网段

二、高级SQL语句

创建两个测试数据库和表

create database auth;
use auth
create table t1(id int(10), name char(20),level int(10));
insert into t1 value(10,'sagou',42);
insert into t1 value(8,'senoku',45);
insert into t1 value(15,'useless',47);
insert into t1 value(27,'guess',52);
insert into t1 value(199,'useless',48);
insert into t1 value(272,'Theshy',36);
insert into t1 value(298,'leslieF',40);
insert into t1 value(30,'shirley',58);
insert into t1 value(190,'zhangsan',48);
insert into t1 value(271,'lisi',52);
insert into t1 value(299,'wangwu',52);
insert into t1 value(31,'zhaoliu',58);

create table t2(id int(10), name char(20),level int(10));
insert into t2 value(10,'sagou',42);
insert into t2 value(8,'senoku',45);
insert into t2 value(15,'useless',47);
insert into t2 value(27,'guess',52);
insert into t2 value(199,'useless',48);
insert into t2 value(272,'Theshy',36);
insert into t2 value(298,'leslieF',40);
insert into t2 value(30,'shirley',58);
insert into t2 value(190,'zhangsan',48);
insert into t2 value(271,'lisi',52);
insert into t2 value(299,'wangwu',52);
insert into t2 value(31,'zhaoliu',58);

1：select语句

select * from order by asc    //按升序排序
select * from order by desc    //按降序排序

GROUP BY	分组；是以 BY 后面的内容对查询出的数据进行分组，也可以用聚合函数
其他子句
LIMIT	需要返回第一行或者前几行 LIMIT 2,3 也可以显示第 3 条记录开始显示之后的 3 条数据
AS或空格(表格或数据库)	设置别名
通配符
%	百分号表示零个、一个或多个字符
_	下划线表示单个字符
子查询
子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套

2：null值

SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的
创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空
- NULL值和空值有什么区别：
- 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的
- IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的
- 空值的判断使用=’’或者<>’’来处理
- 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算

3：正则表达式

^str	以什么开始
str$	以什么结尾
.	匹配任何单个字符包括回车换行
[a-z]	匹配在方阔内的任意字符列表
^[a-z]	匹配以什么字符开头的
[^a-z]	匹配不包含方阔内的任意字符列表
p1\|p2\|p3	匹配任何模式p1,p2,p3
*	0个或多个前面的元素
+	1个或多个前面的元素
？	0个或1个前面的元素
{n}	前面元素连续出现的n个实例
.{n}	前面元素出现的n个实例
{m，n}	匹配前面元素m到n个实例

4：运算符

（算术运算符）

（比较运算符）

（逻辑运算符）

逻辑非：NOT 或！

逻辑与：AND 或 &&

逻辑或：R 或 ||

逻辑异或：XOR

（位运算符）

运算符	说明	使用形式	举例
\|	或	a \| b	5 \| 8
&	与	a & b	5 & 8
^	异或	a ^ b	5 ^ 8
~	取反	~a	~5
	左移	a	5
>>	右移	a >> b	5 >> 2，表示整数 5 按位右移 2 位

连接查询测试

//创建测试表
CREATE TABLE `a_t1` ( 
`a_id` int(11) DEFAULT NULL, 
`a_name` varchar(32) DEFAULT NULL, 
`a_level` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b_t1` ( 
`b_id` int(11) DEFAULT NULL, 
`b_name` varchar(32) DEFAULT NULL, 
`b_level` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
insert into a_t1(a_id, a_name, a_level) values(1, 'aaaa', 10); 
insert into a_t1(a_id, a_name, a_level) values(2, 'bbbb', 20); 
insert into a_t1(a_id, a_name, a_level) values(3, 'cccc', 30); 
insert into a_t1(a_id, a_name, a_level) values(4, 'dddd', 40); 
insert into b_t1(b_id, b_name, b_level) values(2, 'bbbb', 20); 
insert into b_t1(b_id, b_name, b_level) values(3, 'cccc', 30); 
insert into b_t1(b_id, b_name, b_level) values(5, 'eeee', 50); 
insert into b_t1(b_id, b_name, b_level) values(6, 'ffff', 60);

inner join on	内连接
left join on	左连接
right	右连接

1：数据库函数

聚合函数
COUNT	计数
SUM	求和
AVG	平均数
MAX	最大值
MIN	最小值
数学函数
abs(x) ：返回 x 的绝对值 rand() ：返回 0 到 1 的随机数 mod(x,y) ：返回 x 除以 y 以后的余数 power(x,y) ：返回 x 的 y 次方 round(x) ：返回离 x 最近的整数 round(x,y) ：保留 x 的 y 位小数四舍五入后的值	truncate(x,y) ：返回数字 x 截断为 y 位小数的值 ceil(x) ：返回大于或等于 x 的最小整数 floor(x) ：返回小于或等于 x 的最大整数 least(x1,x2...) ：返回集合中最小的值 sqrt(x) ：返回 x 的平方根 greatest(x1,x2...) ：返回集合中最大的值
字符串函数
length(x) 返回字符串 x 的长度第 trim() 移除字符串两侧的空白字符或其他预定义字符 concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串 upper(x) 将字符串 x 的所有字母变成大写字母 lower(x) 将字符串 x 的所有字母变成小写字母 left(x,y) 返回字符串 x 的前 y 个字符 right(x,y) 返回字符串 x 的后 y 个字符	repeat(x,y) 将字符串 x 重复 y 次 space(x) 返回 x 个空格 replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1 substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 reverse(x) 将字符串 x 反转
日期时间函数
curdate() 返回当前时间的年月日 curtime() 返回当前时间的时分秒 now() 返回当前时间的日期和时间 month(x) 返回日期 x 中的月份值 week(x) 返回日期 x 是年度第几个星期 hour(x) 返回 x 中的小时值	minute(x) 返回 x 中的分钟值 second(x) 返回 x 中的秒钟值 dayofweek(x) 返回 x 是星期几，1 星期日，2 星期一 dayofmonth(x) 计算日期 x 是本月的第几天 dayofyear(x) 计算日期 x 是本年的第几天

三、存储过程

需要原因
- 从客户端（Clent）通过网络向服务器（Server）发送SQL代码并执行不妥当，数据不安全
- 影响应用程序的运行性能

//使用 CREATE PROCEDURE 语句创建存储过程，其语法格式
CREATE PROCEDURE<过程名>( [过程参数[,...]])<过程体>
[过程参数[....]]格式
[IN |OUT |INOUT ]<参数名><类型>
//MySQL 的参数分为:输入参数、输出参数和输入/输出参数,分别用 IN、OUT 和 INOUT三个关键字表示
//，输入参数可以传递给一个存储过程;输出参数用于存储过程需要返回一个操作结果的情形;而输入/输出参数既可以充当输入参数也可以充当输出参数

mysql> DELIMITER $$         //DELIMITER：用于定义SQL语句的结束符，默认的结束符是分号
mysql> CREATE PROCEDURE t1Role()     //创建t1Role存储过程
BEGIN         //开始
SELECT id,name,level from t1 limit 3;     //MySQL语句
END $$    //结束加上自定义的结束符号
mysql> DELIMITER ;         //改回默认结束符；(DELIMITER ;) 分号前有空格
mysql> call t1Role();    //执行存储过程

修改删除存储过程

//特征的修改可以使用 ALTER PROCEDURE 来实现;其语法结构
ALTER PROCEDURE<过程名>[<特征>... ]
//使用 DROP PROCEDURE 语句即可删除存储过程，其语法格式
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
//从以上语法结构可以看出，在删除时存储过程的名字是放到最后的，前面可以添加 IFEXISTS 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误

靑木312

关注

15
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
用户管理与高级SQL语句

substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串。replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y。strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1。truncate(x,y) ：返回数字 x 截断为 y 位小数的值。round(x,y) ：保留 x 的 y 位小数四舍五入后的值。right(x,y) 返回字符串 x 的后 y 个字符。left(x,y) 返回字符串 x 的前 y 个字符。
复制链接

扫一扫