• 数据库用户管理及高级sql语句

    • 数据库管理

      • 数据库用户管理

        • mysql权限表

          • 在mysql中mysql库中的user表是最重要的权限表,记录允许连接到服务器的账号信息以及全局权限,

          • 在mysql库中db和host表也是重要的权限表

            • db表中存储了用户对某个数据库的操作权限,决定用户能够从那个主机存取那个数据库。

            • host表中存储了某个主机对数据库的操作权限。

        • 查看自己在那个库

          • select database()

        • 用户管理

          • 完整直接的登录命令

            • mysql -u用户 -p密码 -h 从哪登录 -p 连接端口 可以跟库名

          • 例如在系统命令行中执行sql语句

            • mysql -u root -p -h localhost test -e "DESC person;"

              • -e 后用引号引起要使用的sql语句

          • 新建普通用户

            • create user ’用户名‘@’从哪登录‘ identified by ’通过那个密码连接‘

              • create创建后用户权限较低需要赋予权限

            • grant select on *.* to '用户名'@’从哪登录‘ identified by ’登陆密码‘

              • 用授权语句也可以进行用户的创建

            • insert into user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));

              • 直接修改权限表

          • 删除用户

            • drop user ’用户名‘@’那个终端‘

            • DELETE FROM mysql.user WHERE host='localhost' and user='customer1';

              • 注意and限制条件,避免对整体数据进行改动

        • 密码管理

          • 系统命令行修改

            • mysqladmin -u root -p password "123456"

          • 修改权限表

            • UPDATE mysql.user set Password=password("rootpwd2") WHERE User="root" and Host="localhost";

              • 在修改权限表时一定要加where进行限制

          • 使用sql命令

            • SET password=password("rootpwd3");

              • 需要注意该方式是修改当前登录用户的密码

            • SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");

              • 该方式可以利用root权限对其他用户的密码进行修改

          • 管理员密码丢失解决办法

            • 跳过权限表

              • mysqld-safe --skip-grant-tables &

                • 也可以在配置文件中加入该选项,然后进行重启重载的方式。加入”&“ 选项将该命令放到后台执行,并且该行为会被记录错误日志

              • 无密码登录使用update语句对密码字段进行修改

                • update mysql.user set password=password('mypass') where user='root' and host='localhost';

                • 修改完毕后重新加载权限表

                  • flush privileges;

        • 授权管理

          • 默认授权语句的all授权不包括授权权限

            • GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY 'grantpwd' ;

              • WITH GRANT OPTION :该语句能赋予用户的授权权限。

          • 收回权限

            • REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';

              • 单独收取update权限

          • 查看权限

            • 可以查询user表

                • Y表示有权限

                • N表示无权限

            • show grants for ’账户‘@’在那登录‘

          • 访问控制

            • 通过授权时

              • GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY 'grantpwd'

                • 'grantUser'@'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);

            • 创建了两个t1表与t2表

        • 查询并进行筛选排序

          • select id,name,level from t1 where level>=45 order by level desc;

            • 筛选条件level>=45

            • 排序order by level desc

              • desc:降序排序

              • asc:升序排序(默认)

        • 查询结果分组化

          • group by通常与聚合函数一起使用

            • 计数

              • count

            • 求和

              • sum

            • 求平均数

              • avg

            • 最大值

              • max

            • 最小值

              • min

            • 统计等级45以上,以等级分组每个分组有多少人

              • select count(name),level from t1 where level>=45 group by level;

                • count():计数函数

            • 统计等级45以上,以等级分组每个分组有多少人并进行降序排序

              • select count(name),level from t1 where level>=45 group by level order by count(name) desc;

        • 限制结果条目

          • 只返回匹配的记录

            • 使用limit语句

            • 只显示前三个用户的信息

              • select id,name,level from t1 limit 3

            • 也可以结合排序功能,不过要注意limit排序需要放到最后

              • select id,name,level from t1 order by level desc limit 3;

            • 只显示特定行的匹配结果

              • select id,name,level from t1 limit 2,3;

                • 表示显示第二行开始的三行数据

        • 设置别名

          • 为了将某些较长的列名简化增加易读性可以设置别名

              • 在统计数量时会将数量列名设置为count(name)利用别名简化显示

                • select count(*) as number from t1;

                  • as:alias别名,以此作为连接;可以设置汉语,如果采用了utf8等支持汉语的编码格式。

          • 创建表时将其它表的内容导入到该表

            • create table t3 as select * from t1;

        • 通配符

          • 在使用select时限制条件where后的匹配可以使用通配符

            • ”%“:表示匹配所有

            • “_“:表示匹配单个字符

        • 子查询

          • 利用select嵌套实现子查询的需求

            • insert into t2 select * from t1 where id in (select id from t1)

        • NULL

          • 在创建表时可以利用not null 来规定避免出现空值的情况。

          • null与空值有何区别

            • 空值

              • 长度为0

              • 空值的判断使用=’’或者<>’’来处理。

            • null

              • 长度为null占用空间

              • IS NULL 或者 IS NOT NULL,是用来判断字段是不是为 NULL 或者不是 NULL,不能查出是不是空值的。

            • 在通过 count()计算有多少记录数时,如果遇到 NULL 值会自动忽略掉,遇到空值会加入到记录中进行计算。

        • 逻辑运算符

          • 逻辑非:NOT 或 !

            • 有一方不成立,则结果就不成立

          • 逻辑与:AND 或 &&

            • 任何一个值为0,则结果就为0

          • 逻辑或 :R 或 ||

            • 任何一个值为1,则结果就为1

          • 逻辑异或:XOR

            • 双方都成立则最终值不成立,双方都不成立,则最终值也不成立 双方有一个成立,一个不成立,则最终值成立

        • 位运算符

          • &

            • 按位与

              • 任何一个值为0结果就为0

          • |

            • 按位或

              • 任何一个值为1结果就为1

          • ^

            • 按位异或

            • 取反

          • 左移

            • <<

          • 右移

            • >>

        • 连接查询

          • 创建测试用表

            • 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);

          • 内连接

            • select a_id,a_name,a_level from a_t1 inner join b_t1 on a_id=b_id;

                • 相当于查询两个数据表共同有的部分,(交集)

          • 左连接

            • select * from a_t1 a left join b_t1 b on a.a_id=b.b_id;

                • 以左表为主,查询出左表中的所有内容和与右表中相同的内容,不同的以null代替

            • 右连接与左连接相反

        • 数据库函数

          • 聚合函数

            • avg() :返回指定列的平均值

            • count() :返回指定列中非 NULL 值的个数

            • min() :返回指定列的最小值

            • max() :返回指定列的最大值

            • sum(x) :返回指定列的所有值之和

              • mysql>select sum(level) as sum_level from t1; mysql>select max(level) as max_level from t1; mysql>select min(level) as min_level from t1;

          • 字符串函数

            • 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 是本年的第几天

        • 存储过程(是一个对象)

          • 介绍:MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合,存储过程在数据库中创建并保存,它不仅仅是 SQL 语句的集合,还可以加入一些特殊的控制结构,也可以控制数据的访问方式。

          • 优点:

            • 执行后留在缓冲区,再次调用效率高

            • 灵活性高,可以进行复杂的操作

            • 存储过程时存储在服务端,在客户端进行调用时消耗带宽少

            • 支持多次反复调用,多人同时调用

          • 创建存储过程

            • mysql> DELIMITER $$ mysql> CREATE PROCEDURE t1Role() BEGIN SELECT id,name,level from t1 limit 3; END $$

              • DELIMITER $$ :修改mysql结束符为$$,避免与中间的mysql语句的结束符产生冲突,创建存储过程完毕后需要修改回”;“结束符。

            • 修改储存过程就是删除原有储存过程在创建新同名储存过程。

              • DROP PROCEDURE [IF EXISTS] procedure_name;

                • if exists 是检测存储过程依赖,避免删除造成较大影响

                • procedure_name:是具体的存储过程名

以上就是对mysql数据库的管理以及高级sql语言的说明,仅供参考 。

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部