MySQL 用户管理

// 创建视图CREATE VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

SHOW CREATE VIEW view_name     # 只能看视图定义SHOW CREATE TABLE view_name    # 可以查看表和视图

// 删除视图DROP VIEW [IF EXISTS]view_name [, view_name] ...[RESTRICT | CASCADE]

// 由于多表查询的命令很长MariaDB [hellodb]> select st.name,co.Course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;+-------------+----------------+-------+| name        | Course         | score |+-------------+----------------+-------+| Shi Zhongyu | Kuihua Baodian |    77 || Shi Zhongyu | Weituo Zhang   |    93 || Shi Potian  | Kuihua Baodian |    47 || Shi Potian  | Daiyu Zanghua  |    97 || Xie Yanke   | Kuihua Baodian |    88 || Xie Yanke   | Weituo Zhang   |    75 || Ding Dian   | Daiyu Zanghua  |    71 || Ding Dian   | Kuihua Baodian |    89 || Yu Yutong   | Hamo Gong      |    39 || Yu Yutong   | Dagou Bangfa   |    63 || Shi Qing    | Hamo Gong      |    96 || Xi Ren      | Hamo Gong      |    86 || Xi Ren      | Dagou Bangfa   |    83 || Lin Daiyu   | Taiji Quan     |    57 || Lin Daiyu   | Jinshe Jianfa  |    93 |+-------------+----------------+-------+15 rows in set (0.00 sec)// 因此我们可以使用创建视图的方式 ( 简化命令 )// '创建视图' ( 类似将上述命令创建别名 )MariaDB [hellodb]> create view v_st_co_sc as select st.name,co.Course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;Query OK, 0 rows affected (0.00 sec)// 视图会默认生成一张虚拟表MariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes           || coc               || courses           || scores            || students          || teachers          || toc               || v_st_co_sc        |                # 创建的视图 ( 也就是一张虚拟表 )+-------------------+8 rows in set (0.00 sec)// 我们可以直接 select 查询该视图内容MariaDB [hellodb]> select * from v_st_co_sc;+-------------+----------------+-------+| name        | Course         | score |+-------------+----------------+-------+| Shi Zhongyu | Kuihua Baodian |    77 || Shi Zhongyu | Weituo Zhang   |    93 || Shi Potian  | Kuihua Baodian |    47 || Shi Potian  | Daiyu Zanghua  |    97 || Xie Yanke   | Kuihua Baodian |    88 || Xie Yanke   | Weituo Zhang   |    75 || Ding Dian   | Daiyu Zanghua  |    71 || Ding Dian   | Kuihua Baodian |    89 || Yu Yutong   | Hamo Gong      |    39 || Yu Yutong   | Dagou Bangfa   |    63 || Shi Qing    | Hamo Gong      |    96 || Xi Ren      | Hamo Gong      |    86 || Xi Ren      | Dagou Bangfa   |    83 || Lin Daiyu   | Taiji Quan     |    57 || Lin Daiyu   | Jinshe Jianfa  |    93 |+-------------+----------------+-------+15 rows in set (0.00 sec)------     // 查看表状态 ( 可以得知该表是视图 )MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'v_st_co_sc'\G*************************** 1. row ***************************Name: v_st_co_scEngine: NULLVersion: NULLRow_format: NULLRows: NULLAvg_row_length: NULLData_length: NULLMax_data_length: NULLIndex_length: NULLData_free: NULLAuto_increment: NULLCreate_time: NULLUpdate_time: NULLCheck_time: NULLCollation: NULLChecksum: NULLCreate_options: NULLComment: VIEW            # 视图Max_index_length: NULLTemporary: NULL1 row in set (0.001 sec)[root@centos8 ~] ls /var/lib/mysql/hellodb/

// version 返回数据库的版本号MariaDB [(none)]> select version();+----------------+| version()      |+----------------+| 5.5.68-MariaDB |+----------------+1 row in set (0.01 sec)// sum 函数返回指定字段的总和// 返回 students 表 age 字段的总和MariaDB [hellodb]> select sum(age) from students;+----------+| sum(age) |+----------+|      685 |+----------+1 row in set (0.00 sec)// count 函数返回查询的记录总数// 整个查询的目的是计算学生表中所有学生的年龄的总和除以学生的总数, 从而得到平均年龄.MariaDB [hellodb]> select sum(age)/count(*) from students; +-------------------+| sum(age)/count(*) |+-------------------+|           27.4000 |+-------------------+1 row in set (0.00 sec)// avg 函数返回一个表达式的平均值MariaDB [hellodb]> select avg(age) from students;+----------+| avg(age) |+----------+|  27.4000 |+----------+1 row in set (0.00 sec)

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])RETURNS {STRING|INTEGER|REAL}runtime_body

// 无参 UDFCREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";// 有参数 UDFDELIMITER //CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)BEGINDELETE FROM students WHERE stuid = id;RETURN (SELECT COUNT(*) FROM students);END//DELIMITER ;

// 使用 自定义函数select deleteById(23);// 验证数据表select * from students;

// 默认 MySQL8.0 开启二进制日志, 而不允许创建函数mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)mysql> select @@log_bin;+-----------+| @@log_bin |+-----------+|         1 |+-----------+1 row in set (0.00 sec)mysql> show variables like 'log_bin_trust_function_creators';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin_trust_function_creators | OFF   |+---------------------------------+-------+1 row in set (0.00 sec)// 打开此变量允许二进制日志信息函数创建mysql> set global log_bin_trust_function_creators=ON;Query OK, 0 rows affected (0.00 sec)mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";Query OK, 0 rows affected (0.01 sec)mysql> SHOW FUNCTION STATUS like 'simple%'\G*************************** 1. row ***************************Db: hellodbName: simpleFunType: FUNCTIONDefiner: root@localhostModified: 2021-02-01 21:28:41Created: 2021-02-01 21:28:41Security_type: DEFINERComment:character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)

// Mariadb 默认没有开启二进制日志, 所以可以创建函数[root@centos8 ~] vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]log-bin[root@centos8 ~] systemctl restart mariadbMariaDB [hellodb]> select @@log_bin;+-----------+| @@log_bin |+-----------+|         1 |+-----------+1 row in set (0.000 sec)// 开启二进制功能后, 也不能创建函数MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)MariaDB [hellodb]> show variables like 'log_bin_trust_function_creators';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin_trust_function_creators | OFF   |+---------------------------------+-------+1 row in set (0.001 sec)// 修改变量允许创建函数MariaDB [hellodb]> set global log_bin_trust_function_creators=ON;Query OK, 0 rows affected (0.000 sec)MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";Query OK, 0 rows affected (0.000 sec)MariaDB [hellodb]> SHOW FUNCTION STATUS\G*************************** 1. row ***************************Db: hellodbName: simpleFunType: FUNCTIONDefiner: root@localhostModified: 2021-02-01 21:32:23Created: 2021-02-01 21:32:23Security_type: DEFINERComment:character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.001 sec)

DELIMITER //CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)RETURNS SMALLINTBEGINDECLARE a, b SMALLINT UNSIGNED;SET a = x, b = y;RETURN a+b;END//DELIMITER ;

.....DECLARE x int;SELECT COUNT(*) FROM tdb_name INTO x;RETURN x;END//

// 方法 1MariaDB [hellodb]> select count(*) from students into @num;// 方法 2MariaDB [hellodb]> select count(*) into @num from students;// 查看变量MariaDB [hellodb]> select @num;+------+| @num |+------+|   24 |+------+1 row in set (0.000 sec)

// 创建无参存储过程delimiter //CREATE PROCEDURE showTime()BEGINSELECT now();END//delimiter ;CALL showTime;

// 创建含参存储过程: 只有一个 IN 参数// 这个存储过程的作用: 根据传入的学生 ID 从学生表中选择对应的学生记录delimiter //CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)BEGINSELECT * FROM students WHERE stuid = id;END//delimiter ;// 执行  call selectById(2);

create table testlog (id int auto_increment primary key,name char(10),salary int default 20);delimiter $$create procedure sp_testlog()begin  declare i int;set i = 1;while i <= 100000 doinsert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));set i = i +1;end while;end$$delimiter ;

delimiter //CREATE PROCEDURE dorepeat(n INT)BEGINSET @i = 0;SET @sum = 0;REPEAT SET @sum = @sum+@i;SET @i = @i + 1;UNTIL @i > n END REPEAT;END//delimiter ;CALL dorepeat(100);SELECT @sum;

// 创建含参存储过程: 包含 IN 参数和 OUT 参数delimiter //CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)BEGINDELETE FROM students WHERE stuid >= id;SELECT row_count() into num;END//delimiter ;call deleteById(20,@Line);SELECT @Line;// 说明: 创建存储过程 deleteById, 包含一个 IN 参数和一个 OUT 参数.// 调用时, 传入删除的 ID 和保存被修改的行// 数值的用户变量 @Line,select @Line; 输出被影响行数#row_count() 系统内置函数// 用于存放前一条SQL修改过的表的记录数

CREATE [DEFINER = { user | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROWtrigger_body

// 创建表CREATE TABLE student_info (stu_id INT(11) NOT NULL AUTO_INCREMENT ,stu_name VARCHAR(255) DEFAULT NULL,PRIMARY KEY (stu_id));// 创建表CREATE TABLE student_count (student_count INT(11) DEFAULT 0);// 学生数量初始值: 0INSERT INTO student_count VALUES(0);// ( 创建触发器 )// 在向学生表 INSERT 数据时, 学生数增加+1CREATE TRIGGER trigger_student_count_insertAFTER INSERTON student_info FOR EACH ROWUPDATE student_count SET student_count=student_count+1;// ( 创建触发器 )// DELETE 学生时, 学生数减少-1CREATE TRIGGER trigger_student_count_deleteAFTER DELETEON student_info FOR EACH ROWUPDATE student_count SET student_count=student_count-1;// 插入数据验证insert student_info(stu_name)values('alice');select * from student_info;select * from student_count;// 插入数据验证insert student_info(stu_name)values('bob');select * from student_count;// 删除数据验证delete from student_info where stu_id >=2;select * from student_info;select * from student_count;[root@centos8 ~] cat /var/lib/mysql/hellodb/trigger_student_count_delete.TRNTYPE=TRIGGERNAMEtrigger_table=student_info[root@centos8 ~] cat /var/lib/mysql/hellodb/trigger_student_count_insert.TRNTYPE=TRIGGERNAMEtrigger_table=student_info[root@centos8 ~] cat /var/lib/mysql/hellodb/student_info.TRG

// 在当前数据库对应的目录下, 可以查看到新生成的相关文件: trigger_name.TRN,table_name.TRGSHOW TRIGGERS;+------------------------------+--------+--------------+--------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+| Trigger                      | Event  | Table        | Statement                                              | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |+------------------------------+--------+--------------+--------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+| trigger_student_count_insert | INSERT | student_info | UPDATE student_count SET student_count=student_count+1 | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  || trigger_student_count_delete | DELETE | student_info | UPDATE student_count SET student_count=student_count-1 | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |+------------------------------+--------+--------------+--------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+2 rows in set (0.00 sec)// 查询系统表 information_schema.triggers 的方式指定查询条件, 查看指定的触发器信息USE information_schema;MariaDB [information_schema]> SELECT  * FROM triggers WHERE trigger_name='trigger_student_count_insert';+-----------------+----------------+------------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME                 | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT                                       | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |+-----------------+----------------+------------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+| def             | hellodb        | trigger_student_count_insert | INSERT             | def                  | hellodb             | student_info       |            0 | NULL             | UPDATE student_count SET student_count=student_count+1 | ROW                | AFTER         | NULL                       | NULL                       | OLD                      | NEW                      | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |+-----------------+----------------+------------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+1 row in set (0.00 sec)

// 默认事件调度功能是关闭的// @@ 表示变量MariaDB [(none)]> select @@event_scheduler;+-------------------+| @@event_scheduler |+-------------------+| OFF               |+-------------------+1 row in set (0.000 sec)// 临时开启事件调度功能MariaDB [(none)]> set global event_scheduler=ON;Query OK, 0 rows affected (0.000 sec)// 开启事件调度功能后, 会自启动一个 event_scheduler 线程MariaDB [(none)]> show processlist;+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+| Id | User            | Host      | db   | Command | Time | State                  | Info             | Progress |+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+|  7 | root            | localhost | NULL | Query   |    0 | NULL                   | show processlist |    0.000 ||  8 | event_scheduler | localhost | NULL | Daemon  |    8 | Waiting on empty queue | NULL             |    0.000 |+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+2 rows in set (0.00 sec)// 临时关闭事件调度功能MariaDB [(none)]> set global event_scheduler=OFF;Query OK, 0 rows affected (0.000 sec)MariaDB [(none)]> show processlist;+----+------+-----------+------+---------+------+-------+------------------+----------+| Id | User | Host      | db   | Command | Time | State | Info             | Progress |+----+------+-----------+------+---------+------+-------+------------------+----------+|  7 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |    0.000 |+----+------+-----------+------+---------+------+-------+------------------+----------+1 row in set (0.00 sec)// 持久开启事件调度[root@centos8 ~] vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]event_scheduler=ON[root@centos8 ~] systemctl restart mariadb

CREATE[DEFINER = { user | CURRENT_USER }]EVENT[IF NOT EXISTS]event_nameON SCHEDULE schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE | DISABLE ON SLAVE][COMMENT 'comment']DO event_body;schedule:AT timestamp [+ INTERVAL interval] ...| EVERY interval[STARTS timestamp [+ INTERVAL interval] ...][ENDS timestamp [+ INTERVAL interval] ...]interval:quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

ALTER[DEFINER = { user | CURRENT_USER }]EVENT event_name[ON SCHEDULE schedule][ON COMPLETION [NOT] PRESERVE][RENAME TO new_event_name][ENABLE | DISABLE | DISABLE ON SLAVE][COMMENT 'comment'][DO event_body]

MariaDB [(none)]> create database testdb;Query OK, 1 row affected (0.000 sec)MariaDB [(none)]> use testdbDatabase changed// 创建一个表记录每次事件调度的名字和事件戳MariaDB [testdb]> create table events_list(event_name varchar(20) not null, event_started timestamp not null);Query OK, 0 rows affected (0.005 sec)// 临时关闭事件调度功能MariaDB [testdb]> set global event_scheduler=0;Query OK, 0 rows affected (0.000 sec)MariaDB [testdb]> show variables like 'event_scheduler';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | OFF   |+-----------------+-------+1 row in set (0.001 sec)// ( 创建一次性事件 )MariaDB [testdb]> create event event_now on schedule at now() do insert into events_list values('event_now', now());Query OK, 0 rows affected (0.001 sec)// 因为事件调度功能禁用, 所有表中无记录MariaDB [testdb]> select * from events_list;Empty set (0.000 sec)// 查看事件MariaDB [test]> show events\G*************************** 1. row ***************************Db: testdbName: event_nowDefiner: root@localhostTime zone: SYSTEMType: ONE TIMEExecute at: 2019-12-02 22:06:29Interval value: NULLInterval field: NULLStarts: NULLEnds: NULLStatus: ENABLEDOriginator: 1character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: latin1_swedish_ci1 row in set (0.001 sec)// 任务计划存放在 mysql.event 表中MariaDB [testdb]> select * from mysql.event\G*************************** 1. row ***************************db: testdbname: event_nowbody: insert into events_list values('event_now', now())definer: root@localhostexecute_at: 2020-02-19 02:46:04interval_value: NULLinterval_field: NULLcreated: 2020-02-19 10:46:04modified: 2020-02-19 10:46:04last_executed: NULLstarts: NULLends: NULLstatus: ENABLEDon_completion: DROPsql_mode:STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUB STITUTIONcomment:originator: 1time_zone: SYSTEMcharacter_set_client: utf8collation_connection: utf8_general_cidb_collation: latin1_swedish_cibody_utf8: insert into events_list values('event_now', now())1 row in set (0.000 sec)// 开启事件调度功能MariaDB [testdb]> set global event_scheduler=1;Query OK, 0 rows affected (0.000 sec)// 事件立即执行, 每秒插入一条记录MariaDB [testdb]> select * from events_list;+------------+---------------------+| event_name | event_started       |+------------+---------------------+| event_now  | 2019-12-02 22:07:11 |+------------+---------------------+1 row in set (0.001 sec)// 事件执行完成后自动删除MariaDB [testdb]> show events;Empty set (0.001 sec)

// ( 创建每秒执行的事件 )MariaDB [testdb]> CREATE EVENT event_every_second ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_list VALUES('event_now', now());MariaDB [testdb]> SHOW EVENTS\G*************************** 1. row ***************************Db: testdbName: event_every_secondDefiner: root@localhostTime zone: SYSTEMType: RECURRINGExecute at: NULLInterval value: 1Interval field: SECONDStarts: 2019-12-02 22:26:52Ends: NULLStatus: ENABLEDOriginator: 1character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: latin1_swedish_ci1 row in set (0.002 sec)// 事件是存放在 mysql.event 表中MariaDB [testdb]> select * from mysql.event\G*************************** 1. row ***************************db: testdbname: event_every_secondbody: INSERT INTO events_list VALUES('event_now', now())definer: root@localhostexecute_at: NULLinterval_value: 1interval_field: SECONDcreated: 2019-12-02 22:26:52modified: 2019-12-02 22:26:52last_executed: 2019-12-02 14:30:06starts: 2019-12-02 14:26:52ends: NULLstatus: ENABLEDon_completion: DROPsql_mode:STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONcomment:originator: 1time_zone: SYSTEMcharacter_set_client: utf8collation_connection: utf8_general_cidb_collation: latin1_swedish_cibody_utf8: INSERT INTO events_list VALUES('event_now', now())1 row in set (0.000 sec)MariaDB [testdb]> select *from events_list;+------------+---------------------+| event_name | event_started       |+------------+---------------------+| event_now  | 2019-12-02 22:07:11 || event_now  | 2019-12-02 22:26:52 || event_now  | 2019-12-02 22:26:53 || event_now  | 2019-12-02 22:26:54 || event_now  | 2019-12-02 22:26:55 || event_now  | 2019-12-02 22:26:56 || event_now  | 2019-12-02 22:26:57 || event_now  | 2019-12-02 22:26:58 || event_now  | 2019-12-02 22:26:59 || event_now  | 2019-12-02 22:27:00 || event_now  | 2019-12-02 22:27:01 || event_now  | 2019-12-02 22:27:02 || event_now  | 2019-12-02 22:27:03 || event_now  | 2019-12-02 22:27:04 || event_now  | 2019-12-02 22:27:05 || event_now  | 2019-12-02 22:27:06 || event_now  | 2019-12-02 22:27:07 || event_now  | 2019-12-02 22:27:08 || event_now  | 2019-12-02 22:27:09 || event_now  | 2019-12-02 22:27:10 |+------------+---------------------+20 rows in set (0.000 sec)MariaDB [testdb]> drop event event_every_second;Query OK, 0 rows affected (0.000 sec)MariaDB [testdb]> SHOW EVENTS\GEmpty set (0.001 sec)MariaDB [testdb]> select * from mysql.event\GEmpty set (0.000 sec)

'USERNAME'@'HOST'@'HOST': 主机名: user1@'web1.magedu.org'IP 地址或 Network通配符: %   _示例: wang@172.16.%.%  user2@'192.168.1.%'mage@'10.0.0.0/255.255.0.0'

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password'];// 示例:create user test@'10.0.0.%' identified by 123456;create user test1@'10.0.0.0/255.255.255.0' identified by '123456';

// 创建用户 test 可以在 192.168.80.x 网段登录 ( 无密码 )create user test@'192.168.80.%';// 验证用户信息MariaDB [(none)]> select user,host,password from mysql.user;+------+--------------+----------+| user | host         | password |+------+--------------+----------+| root | localhost    |          || root | blog         |          || root | 127.0.0.1    |          || root | ::1          |          ||      | localhost    |          ||      | blog         |          || test | 192.168.80.% |          |+------+--------------+----------+7 rows in set (0.00 sec)// 远程主机登录验证mysql -utest -h192.168.80.130    ( 无密码远程登录 )

// 增加密码 ( 两种方式 )alter user test@'192.168.80.%' identified by '123456';update mysql.user set password=password('123456') where user='test';// 生效配置flush privileges;// 验证select user,host,password from mysql.user;// 远程主机登录验证mysql -utest -h192.168.80.130 -p123456    ( 基于密码远程登录 )

// 方法 1, 用户可以也可通过此方式修改自已的密码// MySQL8.0 版本不支持此方法, 因为 password 函数被取消SET PASSWORD FOR 'user'@'host' = PASSWORD('password');  // MySQL8.0 版本支持此方法// 此方式直接将密码 123456 加密后存放在 mysql.user 表的 authentication_string 字段set password for root@'localhost'='123456';// 方法 2// 通用改密码方法, 用户可以也可通过此方式修改自己的密码, MySQL8 版本修改密码ALTER USER test@'%' IDENTIFIED BY '123456';// 方法 3// 此方式 MySQL8.0 不支持, 因为 password 函数被取消UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;// mariadb 10.3update mysql.user set authentication_string=password('123456') where user='mage'// 此方法需要执行下面指令才能生效FLUSH PRIVILEGES; 

// 当启用这个选项时// MySQL/MariaDB 会在启动时跳过权限检查// 允许任何人以任何用户身份连接到数据库服务器, 而不需要提供密码。--skip-grant-tables// 这个选项禁用了 MySQL/MariaDB 服务器的网络远程连接功能// 通过本地套接字进行连接, 这在仅需本地访问而不希望通过网络远程连接时很有用--skip-networking

[root@centos8 ~] vim /etc/my.cnf[mysqld]skip-grant-tables  skip-networking[root@centos8 ~] systemctl restart mysqld|mariadb[root@centos8 ~] mysql    ( 绕过了密码认证 )// 方法 1// mariadb 旧版和 MySQL5.6 版之前# 修改所有 user 字段为 root 的行信息MariaDB [(none)]> update mysql.user set password=password('123456') where user='root';MariaDB [(none)]> select user,host,password from mysql.user;+------+--------------+-------------------------------------------+| user | host         | password                                  |+------+--------------+-------------------------------------------+| root | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || root | blog         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || root | 127.0.0.1    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || root | ::1          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+------+--------------+-------------------------------------------+6 rows in set (0.00 sec)// mariadb 新版MariaDB [(none)]> update mysql.user set authentication_string=password('123456') where user='root';// 方法 2MariaDB [(none)]> flush privileges;    ( 刷新权限后再执行 )MariaDB [(none)]> alter user root@'localhost' identified by '123456';// 取消绕过密码认证配置[root@centos8 ~] vim /etc/my.cnf[mysqld]# skip-grant-tables         # skip-networking[root@centos8 ~] systemctl restart mysqld|mariadb[root@centos8 ~] mysql -uroot -p123456

[root@centos8 ~] vim /etc/my.cnf[mysqld]skip-grant-tables  skip-networking  // MySQL8.0 不需要[root@centos8 ~] systemctl restart mysqld// 方法 1[root@centos8 ~] mysqlmysql> update mysql.user set authentication_string='' where user='root' andhost='localhost';// 方法 2mysql> flush privileges;// 再执行下面任意一个命令mysql> alter user root@'localhost' identified by '123456';mysql> set password for root@'localhost'='123456';// 取消绕过密码认证配置[root@centos8 ~] vim /etc/my.cnf[mysqld]# skip-grant-tables         # skip-networking[root@centos8 ~] systemctl restart mysqld[root@centos8 ~] mysql -uroot -p123456

// 此方法适用于包安装方式的 MySQL 或 Mariadb[root@centos8 ~] systemctl stop mysqld[root@centos8 ~] rm -rf /var/lib/mysql/*[root@centos8 ~] systemctl start mysqld[root@centos8 ~] mysql

// 创建用户create user wangj@'192.168.80.%' identified by '123456';// 授权grant all on hellodb.* to wangj@'192.168.80.%';// 生效配置flush privileges;// 验证mysql -uwangj -h192.168.80.130 -p123456show databases;

// 创建用户并授权 ( MySQL 8.0 之前的版本 )GRANT ALL ON *.* TO root@'192.168.80.%' IDENTIFIED BY '123456';                // 授权所有数据库的操作权限GRANT ALL ON wordpress.* TO root@'192.168.80.%' IDENTIFIED BY '123456';        // 仅授权 WordPress 数据库的操作权限// 验证select user,host,password from mysql.user;

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];priv_type: ALL [PRIVILEGES]object_type:TABLE | FUNCTION | PROCEDUREpriv_level: *(所有库)  |*.*   | db_name.*  | db_name.tbl_name  | tbl_name (当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)with_option: GRANT OPTION| MAX_QUERIES_PER_HOUR count| MAX_UPDATES_PER_HOUR count| MAX_CONNECTIONS_PER_HOUR count| MAX_USER_CONNECTIONS count

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' ;GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%'  WITH GRANT OPTION;// 创建用户和授权同时执行的方式在 MySQL8.0 取消了GRANT ALL ON wordpress.* TO wordpress@'192.168.8.%' IDENTIFIED BY 'magedu';GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu' WITH GRANT OPTION;

// 先创建用户并授权GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu' WITH GRANT OPTION;