Mysql2-sql 语句 - II.MySQL 数据库的查询操作

MySQL数据库查询

2.1、MySQL单表查询

准备测试表：company.employee5

字段解析	字段名	字段类型
雇员编号	id	int
雇员姓名	name	varchar(30)
雇员性别	sex	enum
雇用时期	hire_date	date
雇员职位	post	varchar(50)
职位描述	job_description	varchar(100)
雇员薪水	salary	double(15,2)
办公室	office	int
部门编号	dep_id	int

mysql> CREATE TABLE company.employee5(
     id int primary key AUTO_INCREMENT not null,
    name varchar(30) not null,
    sex enum('male','female') default 'male' not null,
     hire_date date not null,
     post varchar(50) not null,
     job_description varchar(100),
     salary double(15,2) not null,
     office int,
     dep_id int
     );

mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
	('jack','male','20180202','instructor','teach',5000,501,100),
	('tom','male','20180203','instructor','teach',5500,501,100),
	('robin','male','20180202','instructor','teach',8000,501,100),
	('alice','female','20180202','instructor','teach',7200,501,100),
	('tianyun','male','20180202','hr','hrcc',600,502,101),
	('harry','male','20180202','hr',NULL,6000,502,101),
	('emma','female','20180206','sale','salecc',20000,503,102),
	('christine','female','20180205','sale','salecc',2200,503,102),
  ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
  ('gougou','male','20180205','sale','',2200,503,102);
         

mysql> SELECT   字段名称,字段名称2    from  表名   条件

mysql> SELECT   column_name,column_2  from  table  WHERE   ...


简单查询：
mysql> SELECT * from t3;
mysql> SELECT name, salary, dep_id from employee5;

避免重复DISTINCT
  SELECT post FROM employee5;
	SELECT DISTINCT post  FROM employee5;	

通过四则运算查询
    运算：
    mysql>SELECT 437.4384/5;
    mysql>SELECT 5>3;

    SELECT name, salary, salary*14 FROM employee5;
    SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
    SELECT name, salary, salary*14 Annual_salary FROM employee5;

定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM employee5;

单条件查询
    mysql> SELECT name from employee5 WHERE salary=5000;

多条件查询
    mysql> SELECT name from employee5 WHERE salary>5000 and salary<6000;


关键字BETWEEN AND
	    SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
	    SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

关键字IS NULL
	    SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

	    SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
		
	    SELECT name,job_description FROM employee5 WHERE job_description='';
		
NULL说明：
        1、等价于没有任何值、是未知数。
        2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
        3、对空值做加、减、乘、除等运算操作，结果仍为空。
        4、比较时使用关键字用“is null”和“is not null”。
        5、排序时比其他数据都小，所以NULL值总是排在最前。

关键字IN集合查询
SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
	
SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;

SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;


排序查询
mysql> SELECT name from employee5 order by name;
mysql> SELECT name from employee5 order by name desc;
mysql> SELECT name from employee5 order by name desc limit 3;		//限制次数
mysql> SELECT name from employee5 order by name desc limit 1,3;
mysql> SELECT name from employee5 order by name desc limit 2,3;

    注：
    ascending    美音 /ə'sɛndɪŋ/   升序
    descending  美音 /dɪ'sɛndɪŋ/  降序

    按多列排序:
    入职时间相同的人薪水不同
	  SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
		有差别于
		SELECT * from employee5 ORDER BY hire_date DESC;   
		
		先按入职时间，再按薪水排序
		SELECT * from employee5 ORDER BY hire_date DESC,salary DESC;
		
		
		先按职位，再按薪水排序
		SELECT * from employee5 ORDER BY post,salary DESC;
		
    限制查询的记录数
	  SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5;			//默认初始位置为0 
    SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5;	  //从第4条开始，共显示5条
		
分组查询
  GROUP BY和GROUP_CONCAT()函数一起使用
	SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
	SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5   GROUP BY dep_id;
	
   
模糊查询(通配符)   
    %  所有字符
    mysql> SELECT * from employee5 WHERE salary like '%20%';
  
正则查询
    SELECT * FROM employee5 WHERE salary regexp '72+';
    SELECT * FROM employee5 WHERE name REGEXP '^ali';
    SELECT * FROM employee5 WHERE name REGEXP 'yun$';
    SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
    
    
函数
    count()
    max()
    min()
    avg()
    database()
    user()
    now()
    sum()
    password()


	SELECT COUNT(*) FROM employee5;
	SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
	SELECT MAX(salary) FROM employee5;
	SELECT MIN(salary) FROM employee5;
	SELECT AVG(salary) FROM employee5;
	SELECT SUM(salary) FROM employee5;
	SELECT SUM(salary) FROM employee5 WHERE dep_id=101;

2.2、MySQL多表查询

多表查询 
    多表连接查询
    复合条件连接查询
    子查询

一、准备两张测试表
表company.employee6
mysql> create table employee6( 
emp_id int auto_increment primary key not null, 
emp_name varchar(50), 
age int, 
dept_id int);

mysql> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name | varchar(50) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| dept_id  | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

mysql> insert into employee6(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);

mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | tianyun  |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | natasha  |   28 |     204 |
+--------+----------+------+---------+


表company.department6
mysql> create table department6(
dept_id int,
dept_name varchar(100)
);

mysql> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id   | int(11)      | YES  |     | NULL    |       |
| dept_name | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

mysql> insert into department6 values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');

mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
注：
Financial department:财务部门 fd


二、多表的连接查询
交叉连接：      生成笛卡尔积，它不使用任何匹配条件  自己了解就好，这个生产用会把数据库跑死

内连接：		    只连接匹配的行

外连接：（了解）
    	左连接：	会显示左边表内所有的值，不论在右边表内匹不匹配
    	右连接：	会显示右边表内所有的值，不论在左边表内匹不匹配

全外连接：（了解） 	包含左、右两个表的全部行




=================内连接=======================
两种方式：
    方式1：使用where条件 
    方式2：使用inner join
只找出有部门的员工 (部门表中没有natasha所在的部门)
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name
from employee6,department6 
where employee6.dept_id = department6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | sale      |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+

使用别名：
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,department6 b where a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | sale      |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+

使用inner join
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join department6 b on a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | tianyun  |   19 | hr        |
|      2 | tom      |   26 | it        |
|      3 | jack     |   30 | it        |
|      4 | alice    |   24 | sale      |
|      5 | robin    |   40 | hr        |
+--------+----------+------+-----------+


外连接语法：
SELECT 字段列表
	    FROM 表1 LEFT|RIGHT JOIN 表2
	    ON 表1.字段 = 表2.字段;

=================外连接(左连接 left join)=======================
mysql> select emp_id,emp_name,dept_name from  employee6 left join department6 on employee6.dept_id = department6.dept_id;
找出所有员工及所属的部门，包括没有部门的员工
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | tianyun  | hr        |
|      5 | robin    | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      6 | natasha  | NULL      |
+--------+----------+-----------+


=================外连接(右连接right join)=======================
mysql> select emp_id,emp_name,dept_name from  employee6 right join department6 on employee6.dept_id = department6.dept_id;
找出所有部门包含的员工，包括空部门
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | tianyun  | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      5 | robin    | hr        |
|   NULL | NULL     | fd        |
+--------+----------+-----------+


============================全外连接=================================
> select * from employee6 full  join department6;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | tianyun  |   19 |     200 |     200 | hr        |
|      1 | tianyun  |   19 |     200 |     201 | it        |
|      1 | tianyun  |   19 |     200 |     202 | sale      |
|      1 | tianyun  |   19 |     200 |     203 | fd        |
|      2 | tom      |   26 |     201 |     200 | hr        |
|      2 | tom      |   26 |     201 |     201 | it        |
|      2 | tom      |   26 |     201 |     202 | sale      |
|      2 | tom      |   26 |     201 |     203 | fd        |
|      3 | jack     |   30 |     201 |     200 | hr        |
|      3 | jack     |   30 |     201 |     201 | it        |
|      3 | jack     |   30 |     201 |     202 | sale      |
|      3 | jack     |   30 |     201 |     203 | fd        |
|      4 | alice    |   24 |     202 |     200 | hr        |
|      4 | alice    |   24 |     202 |     201 | it        |
|      4 | alice    |   24 |     202 |     202 | sale      |
|      4 | alice    |   24 |     202 |     203 | fd        |
|      5 | robin    |   40 |     200 |     200 | hr        |
|      5 | robin    |   40 |     200 |     201 | it        |
|      5 | robin    |   40 |     200 |     202 | sale      |
|      5 | robin    |   40 |     200 |     203 | fd        |
|      6 | natasha  |   28 |     204 |     200 | hr        |
|      6 | natasha  |   28 |     204 |     201 | it        |
|      6 | natasha  |   28 |     204 |     202 | sale      |
|      6 | natasha  |   28 |     204 |     203 | fd        |
+--------+----------+------+---------+---------+-----------+

三、复合条件连接查询
示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
select emp_id,emp_name,age,dept_name FROM employee6,department6 WHERE employee6.dept_id=department6.dept_id AND age > 25;

示例2：以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示
SELECT emp_id,emp_name,age,dept_name FROM employee6,department6 where employee6.dept_id=depaartment6.dept_id ORDER BY age asc;


四、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等

1. 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
SELECT * FROM employee6 WHERE dept_id IN (SELECT dept_id FROM department6);

2. 带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
SELECT dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERER age >= 25);

3. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

department表中存在dept_id=203，Ture
SELECT * from employee6 WHERE EXISTS (SELECT * FROM depratment6 WHERE dept_id=203);

2.3、Mysql安全控制

1. 确保MySQL运行用户为一般用户

# groupadd mysql
# useradd -M -s /sbin/nologin -g mysql mysql

# vim /etc/my.cnf
user = mysql

#注意点:
	改变拥有者和所属组对于mysql的安装目录

2. 建议修改默认端口3306,改为其他的一些端口

# vim /etc/my.cnf
port = 3306 false
port = 10086 true

3. 开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点

# vim /etc/my.cnf
log_bin = othername

4.删除空口令账号

#禁用匿名账号
# vim /etc/my.cnf
	skip-grant-tables = 1. --改成 "#skip-grant-tables = 1"

#删除空口令用户
mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> create user 'newrain'@'localhost';	#(这是在做实验)创建空口令账户
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| newrain      | localhost |                                           |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user 'newrain'@'localhost';	#这是删除空口令账户
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

1. 禁止root账户远程访问(允许普通用户远程访问，某个网段即可)

mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> create user 'root'@'10.0.11.%' identified by "123";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | 10.0.11.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user 'root'@'10.0.11.%';
Query OK, 0 rows affected (0.00 sec)

2. 使用mysql的时候,经常会遇到MySQL: ERROR 1040: Too many connections这样的问题,一种是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数

##设置最大连接数02
修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
max_connections=256

DCL（Data Control Language 数据库控制语言）

用于数据库授权、角色控制等操作

GRANT 授权，为用户赋予访问权限

REVOKE 取消授权，撤回授权权限

用户管理

登录和退出MySQL
远程登陆：
客户端语法：mysql  -u  用户名  -p  密码  -h  ip地址   -P端口号:如果没有改端口号就不用-P指定端口
# mysql -h192.168.62.148 -P 3306 -uroot -p123

创建用户
    create user '用户名'@'客户端来源IP地址' identified by '密码';
    mysql> create user newrain@'192.168.62.%' identified by '123';
    
删除用户
    drop user '用户名'@'客户端来源IP地址';
    mysql> drop user newrain@'192.168.62.%';

修改用户
    rename user '用户名'@'客户端来源IP地址' to '新用户名'@'客户端来源IP地址' ;
    mysql> rename user newrain@'192.168.62.%' to ehome@'%';
修改密码
    // 第一种方法：
    set password for '用户名'@'IP地址'=Password('新密码')
	mysql> set password for ehome@'%'=Password('123');
	
    // 第二种方法：
    alter user '用户名'@'客户端来源IP地址' identified by '新密码';

   // 第三种方法(忘记密码时，必须使用此方法修改密码)：
    UPDATE mysql.user SET authentication_string=password('QFedu123!') WHERE user='root' and host='localhost';
    
＝＝＝root修改自己密码
# mysqladmin -uroot -p'123' password 'new_password'	    //123为旧密码
案例:
# mysqladmin -uroot -p'123' password 'qf@123';
  
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

权限管理

grant  权限 on 数据库.表  to  '用户'@'客户端来源IP地址' identified by '密码';   -- 授权并设置密码
revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址'    -- 取消权限


mysql> grant all privileges on company.* to ehome@'%';
mysql> revoke all privileges on company.* from ehome@'%';
mysql> flush privileges;		#关于权限方面的修改，注意刷新权限，否则有可能不生效

查看授权信息

查看授权语句

show grants for '用户'@'客户端来源IP地址';  
mysql> show grants for ehome@'%';
+-----------------------------------+
| Grants for ehome@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'ehome'@'%' |
+-----------------------------------+
1 row in set (0.00 sec

查看生效的授权信息

针对所有库和表的权限，比如 *.* 。 去 mysql.user 中查看

SELECT * from mysql.user WHERE user='root'\G

mysql> SELECT * from mysql.user WHERE user='ehome'\G
*************************** 1. row ***************************
                  Host: %
                  User: ehome
           SELECT_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2019-08-20 19:35:41
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

针对具体到库的权限，比如db_name.* 。 去 mysql.db 中查看

mysql> SELECT * from mysql.db  WHERE user='ehome'\G
*************************** 1. row ***************************
                 Host: %
                   Db: company
                 User: ehome
          SELECT_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)