字段存储，设计必须这么存fieldis_showis_dup_keyxxx00xxx10xxx01sql查询时压缩，c_rule_md5与业务表关联，这是query1SELECT c_rule_md5, field_id, MAX(is_show) AS is_show, MAX(is_dup_key) AS is_dup_key FROM t_rel_complicated_rules_fields trcrf WHERE trcrf.is_dup_key != 1字段信息和字段表和表基本信息表连接查询，获取字段所属表名称，字段名称，字段中文名等，这是query5SELECT ttf.column_name_cn, ttf.column_name, ttbi.table_name, ttbi.table_schema, ttbi.table_schema_cn, ttbi.table_name_cn, query1.c_rule_md5, query1.is_show, query1.is_dup_key, ttf.id AS field_id FROM ( SELECT c_rule_md5, field_id, MAX( is_show ) AS is_show, MAX( is_dup_key ) AS is_dup_key FROM t_rel_complicated_rules_fields trcrf WHERE trcrf.is_dup_key != 1 GROUP BY c_rule_md5, field_id ) query1 LEFT JOIN t_table_field ttf ON query1.field_id = ttf.id LEFT JOIN t_table_base_info ttbi ON ttbi.table_schema = ttf.table_schema AND ttbi.table_name = ttf.table_name WHERE ttbi.table_schema = 'db_enterprise' AND ttbi.table_name = 't_address' AND query1.field_id = 396 )查最新一条报告，这是query3SELECT tcrr.id, tcrr.rule_md5, tcrr.is_alarming, tcrr.create_time AS last_execute_time FROM t_complicated_rule_report tcrr JOIN ( SELECT rule_md5, MAX( create_time ) AS max_create_time FROM t_complicated_rule_report GROUP BY rule_md5 ) query2 ON tcrr.rule_md5 = query2.rule_md5 AND tcrr.create_time = query2.max_create_time最新一条报告和主表连接，tcrc与query3左连接，生成query4SELECT tcrc.id AS id, tcrc.rule_name, tcrc.rule_md5, tcrc.rule_type_id AS rule_type_id, tcrc.create_user, tcrc.create_time, tcrc.cron_type, tcrc.rule_status AS is_online, tcrc.rule_update_time, tcrc.online_approve_id, query3.is_alarming AS is_alarming, query3.last_execute_time, tcrc.try_running_percent FROM t_complicated_rule_config tcrc LEFT JOIN ( SELECT tcrr.id, tcrr.rule_md5, tcrr.is_alarming, tcrr.create_time AS last_execute_time FROM t_complicated_rule_report tcrr JOIN ( SELECT rule_md5, MAX( create_time ) AS max_create_time FROM t_complicated_rule_report GROUP BY rule_md5 ) query2 ON tcrr.rule_md5 = query2.rule_md5 AND tcrr.create_time = query2.max_create_time ) query3 ON query3.rule_md5 = tcrc.rule_md5 ) query4query4与query5进行内连接，生成query6( SELECT query4.*, query5.column_name_cn, query5.column_name, query5.table_name, query5.table_schema, query5.table_schema_cn, query5.table_name_cn, query5.is_show, query5.is_dup_key, query5.field_id FROM (...) query4 INNER JOIN (...) query5 ON query4.rule_md5 = query5.c_rule_md5 ) query6mysql 5.7没有row_number()，需要通过mysql变量生成row_number( SELECT @row_number := 0, @current_md5 := '', @is_show_sum := 0 ) AS vars使用变量 @row_number、@current_md5 和 @is_show_sum 来实现分组和过滤逻辑，生成query7，在这里的目的是生成每个分组内的row_number并判断每个分组内的is_show是否全部为0；注意query6.is_show DESC，可以确保分组内存在is_show=1的值在前，则存在is_show=1分组内每行的is_show_sum>0；SELECT query6.*, @row_number :=IF( @current_md5 = query6.rule_md5, @row_number + 1, 1 ) AS row_number, @is_show_sum := IF( @current_md5 = query6.rule_md5, @is_show_sum + query6.is_show, query6.is_show ) AS is_show_sum, @current_md5 := query6.rule_md5 FROM (...) query6, ( SELECT @row_number := 0, @current_md5 := '', @is_show_sum := 0 ) AS vars ORDER BY query6.rule_md5 ASC, query6.is_show DESC ) query7如果分组内 is_show 有一个不为0，则保留整个分组。如果分组内 is_show 全为0，则只保留分组中的第一行（即 row_number = 1）。SELECT * FROM query7 WHERE ( query7.is_show_sum > 0 OR ( query7.is_show_sum = 0 AND query7.row_number = 1 ))总体：SELECT query7.* from ( select query6.*, @row_number := IF(@current_md5 = query6.rule_md5, @row_number + 1, 1) AS row_number, @is_show_sum := IF(@current_md5 = query6.rule_md5, @is_show_sum + query6.is_show, query6.is_show) AS is_show_sum, @current_md5 := query6.rule_md5 from ( SELECT query4.*, query5.column_name_cn, query5.column_name, query5.table_name, query5.table_schema, query5.table_schema_cn, query5.table_name_cn, query5.is_show, query5.is_dup_key, query5.field_id FROM ( SELECT tcrc.id as id, tcrc.rule_name, tcrc.rule_md5, tcrc.rule_type_id as rule_type_id, tcrc.create_user, tcrc.create_time, tcrc.cron_type, tcrc.rule_status as is_online, tcrc.rule_update_time, tcrc.online_approve_id, query3.is_alarming as is_alarming, query3.last_execute_time, tcrc.try_running_percent FROM t_complicated_rule_config tcrc LEFT JOIN ( SELECT tcrr.id, tcrr.rule_md5, tcrr.is_alarming, tcrr.create_time as last_execute_time FROM t_complicated_rule_report tcrr JOIN ( SELECT rule_md5, MAX(create_time) AS max_create_time FROM t_complicated_rule_report GROUP BY rule_md5 ) query2 ON tcrr.rule_md5 = query2.rule_md5 AND tcrr.create_time = query2.max_create_time ) query3 ON query3.rule_md5 = tcrc.rule_md5 ) query4 INNER JOIN ( SELECT ttf.column_name_cn, ttf.column_name, ttbi.table_name, ttbi.table_schema, ttbi.table_schema_cn, ttbi.table_name_cn, query1.c_rule_md5, query1.is_show, query1.is_dup_key, ttf.id as field_id FROM ( SELECT c_rule_md5, field_id, MAX(is_show) AS is_show, MAX(is_dup_key) AS is_dup_key FROM t_rel_complicated_rules_fields trcrf WHERE trcrf.is_dup_key != 1 GROUP BY c_rule_md5, field_id ) query1 LEFT JOIN t_table_field ttf ON query1.field_id = ttf.id LEFT JOIN t_table_base_info ttbi ON ttbi.table_schema = ttf.table_schema AND ttbi.table_name = ttf.table_name WHERE ttbi.table_schema = 'db_enterprise' AND ttbi.table_name = 't_address' AND query1.field_id = 396 ) query5 ON query4.rule_md5 = query5.c_rule_md5 ) query6, ( SELECT @row_number := 0, @current_md5 := '', @is_show_sum := 0) AS vars order by query6.rule_md5 asc, query6.is_show desc ) query7 where (query7.is_show_sum > 0 OR (query7.is_show_sum = 0 AND query7.row_number = 1)) AND rule_type_id = 4 AND query7.is_online = 1 AND (query7.is_alarming = 0 or query7.is_alarming is NULL) AND query7.try_running_percent = 100
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。权限表user表user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。mysql> desc user; +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(255) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int unsigned | NO | | 0 | | | max_updates | int unsigned | NO | | 0 | | | max_connections | int unsigned | NO | | 0 | | | max_user_connections | int unsigned | NO | | 0 | | | plugin | char(64) | NO | | caching_sha2_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | | Create_role_priv | enum('N','Y') | NO | | N | | | Drop_role_priv | enum('N','Y') | NO | | N | | | Password_reuse_history | smallint unsigned | YES | | NULL | | | Password_reuse_time | smallint unsigned | YES | | NULL | | | Password_require_current | enum('N','Y') | YES | | NULL | | | User_attributes | json | YES | | NULL | | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ 51 rows in set (0.01 sec) mysql> 用户列user表的用户列包括Host、User、authentication_string，分别表示主机名、用户名和密码。其中User和Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。权限列权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应的权限；N表示用户没有对应的权限。可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。安全列安全列有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。资源控制列max_questions——用户每小时允许执行的查询操作次数。max_updates——用户每小时允许执行的更新操作次数。max_connections——用户每小时允许执行的连接操作次数。max_user_connections——用户允许同时建立的连接次数。db表mysql> desc db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 22 rows in set (0.01 sec) 用户列db表用户列有3个字段，分别是Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。db表中create_routine_priv和alter_routine_priv这两个字段表明用户是否有创建和修改存储过程的权限。user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。tables_priv表和columns_priv表mysql> desc tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | varchar(288) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.00 sec) mysql> desc columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 7 rows in set (0.00 sec) tables_priv表有8个字段Host、Db、User和Table_name 4个字段分表示主机名、数据库名、用户名和表名。Grantor表示修改该记录的用户。Timestamp字段表示修改该记录的时间。Table_priv表示对表的操作权限，包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。columns_priv表只有7个字段，分别是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv。其中，Column_name用来指定对哪些数据列具有操作权限。procs_priv表procs_priv表可以对存储过程和存储函数设置操作权限。mysql> desc procs_priv; +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | | | Grantor | varchar(288) | NO | MUL | | | | Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.01 sec) procs_priv表包含8个字段:Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。 -Routine_type表示存储过程或函数的类型。Routine_type字段有两个值，分别是FUNCTION和PROCEDURE：FUNCTION表示这是一个函数，PROCEDURE表示这是一个存储过程。Grantor是插入或修改该记录的用户。Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant 3种。Timestamp表示记录更新时间。权限管理权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。CREATE和DROP权限，可以创建新数据库和表，或删除（移掉）已有数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户可以删掉MySQL访问权限保存的数据库。SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。SELECT权限只有在它们真正从一个表中检索行时才被用到。INDEX权限允许创建或删除索引，INDEX适用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语句中包括索引定义。ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。FILE权限给予用户使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件授权语法：GRANT priv_type [ (columns) ] [，priv_type [ (columns)]] . . . ON [object_type] tablel, table2.... tablen TO user [WITH GRANT OPTION] object_ type = TABLEI | FUNCTION | PROCEDUREpriv_type参数表示权限类型；columns参数表示权限作用于哪些列上，不指定该参数，表示作用于整个表；table1,table2,…,tablen表示授予权限的列所在的表；object_type指定授权作用的对象类型包括TABLE（表）、FUNCTION（函数）和PROCEDURE（存储过程）；当从旧版本的MySQL升级时，要使用object_tpye子句，必须升级授权表；user参数表示用户账户，由用户名和主机名构成，形式是“'username'@'hostname'”；IDENTIFIED BY参数用于设置密码。WITH关键字后可以跟一个或多个with_option参数。GRANT OPTION：被授权的用户可以将这些权限赋予别的用户。MAX_QUERIES_PER_HOUR count：设置每个小时可以执行count次查询。MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。mysql> create user ronie@localhost identified by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for ronie@localhost; +-------------------------------------------+ | Grants for ronie@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `ronie`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> grant all on *.* to 'ronie'@'localhost' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> show grants for ronie@localhost \G; *************************** 1. row *************************** Grants for ronie@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for ronie@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `ronie`@`localhost` WITH GRANT OPTION 2 rows in set (0.00 sec) ERROR: No query specified收回权限REVOKE语句，REVOKE语句必须和FROM语句一起使用。FROM语句指明需要收回权限的账户。REVOKE priv_type [(columns)] [, priv_type [(columns)]] ... ON table1, table2,…, tablen FROM 'user'@'host'[, 'user'@ 'host' ...]mysql> show grants for ronie@localhost \G; *************************** 1. row *************************** Grants for ronie@localhost: GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION 查看权限语法：SHOW GRANTS FOR 'user'@'host';mysql> show grants for ronie@localhost \G; *************************** 1. row *************************** Grants for ronie@localhost: GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION
视图简介：视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动反映到视图中。优点：简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响。创建视图创建视图使用CREATE VIEW语句，语法：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGEI TEMPTABLE} ] VIEW view_ name [ (column_ list) ] AS SELECT statement [WITH [CASCADED | LOCAL] CHECK OPTION]。CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法，UNDEFINED表示MySQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。view_name为视图的名称，column_list为属性列；SELECT_statement表示SELECT语句；WITH [CASCADED | LOCAL]，CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。mysql> create table user( -> id int primary key auto_increment, -> name varchar(150) -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table user_info( -> user_id int, -> department varchar(250), -> age int, -> sex enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into user (name) values ('ronie'), ('jack'), ('kelly'), ('lucy'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into user_info values -> (1, 'IT', 23, 'male'), -> (2, 'art', 34, 'male'), -> (3, 'sale', 33, 'female'), -> (4, 'IT', 24, 'female'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+ | id | name | +----+-------+ | 1 | ronie | | 2 | jack | | 3 | kelly | | 4 | lucy | +----+-------+ 4 rows in set (0.00 sec) mysql> select * from user_info; +---------+------------+------+--------+ | user_id | department | age | sex | +---------+------------+------+--------+ | 1 | IT | 23 | male | | 2 | art | 34 | male | | 3 | sale | 33 | female | | 4 | IT | 24 | female | +---------+------------+------+--------+ 4 rows in set (0.00 sec) mysql> create view userinfo (id, name, department, age, sex) as select user.id, user.name, department, age, sex from user, user_info where user_id=id; Query OK, 0 rows affected (0.01 sec) mysql> select * from userinfo; +----+-------+------------+------+--------+ | id | name | department | age | sex | +----+-------+------------+------+--------+ | 1 | ronie | IT | 23 | male | | 2 | jack | art | 34 | male | | 3 | kelly | sale | 33 | female | | 4 | lucy | IT | 24 | female | +----+-------+------------+------+--------+ 4 rows in set (0.00 sec) 查看视图查看视图是查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE、SHOW TABLE STATUS和SHOW CREATE VIEW.DESCRIBE语句-- DESCRIBE一般情况下都简写成DESC，输入这个命令的执行结果和输入DESCRIBE的执行结果是一样的。 mysql> desc userinfo; +------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------------+------+-----+---------+-------+ | id | int | NO | | 0 | | | name | varchar(150) | YES | | NULL | | | department | varchar(250) | YES | | NULL | | | age | int | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +------------+-----------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) SHOW TABLE STATUS语句-- 表的说明Comment的值为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。 mysql> show table status like 'userinfo' \G; *************************** 1. row *************************** Name: userinfo Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: 2022-10-14 09:47:44 Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW 1 row in set (0.00 sec) ERROR: No query specified SHOW CREATE VIEW语句mysql> show create view userinfo \G; *************************** 1. row *************************** View: userinfo Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `userinfo` (`id`,`name`,`department`,`age`,`sex`) AS select `user`.`id` AS `id`,`user`.`name` AS `name`,`user_info`.`department` AS `department`,`user_info`.`age` AS `age`,`user_info`.`sex` AS `sex` from (`user` join `user_info`) where (`user_info`.`user_id` = `user`.`id`) character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> views表中查看mysql> SELECT * FROM information_schema.views where table_name='userinfo' \G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: ronie TABLE_NAME: userinfo VIEW_DEFINITION: select `ronie`.`user`.`id` AS `id`,`ronie`.`user`.`name` AS `name`,`ronie`.`user_info`.`department` AS `department`,`ronie`.`user_info`.`age` AS `age`,`ronie`.`user_info`.`sex` AS `sex` from `ronie`.`user` join `ronie`.`user_info` where (`ronie`.`user_info`.`user_id` = `ronie`.`user`.`id`) CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> 修改视图MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。使用CREATE OR REPLACE VIEW语句mysql> create or replace view userinfo (name, department, sex) as select user.name, department, sex from user, user_info where user_id=id; Query OK, 0 rows affected (0.00 sec) mysql> select * from userinfo; +-------+------------+--------+ | name | department | sex | +-------+------------+--------+ | ronie | IT | male | | jack | art | male | | kelly | sale | female | | lucy | IT | female | +-------+------------+--------+ 4 rows in set (0.00 sec) mysql> desc userinfo; +------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------------+------+-----+---------+-------+ | name | varchar(150) | YES | | NULL | | | department | varchar(250) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +------------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 使用ALTER语句mysql> alter view userinfo as select name, sex from user, user_info where id=user_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from userinfo; +-------+--------+ | name | sex | +-------+--------+ | ronie | male | | jack | male | | kelly | female | | lucy | female | +-------+--------+ 4 rows in set (0.01 sec) mysql> desc userinfo; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | name | varchar(150) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 更新视图当视图中包含有如下内容时，视图的更新操作将不能被执行：视图中不包含基表中被定义为非空的列。在定义视图的SELECT语句后的字段列表中使用了数学表达式。在定义视图的SELECT语句后的字段列表中使用聚合函数。在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。视图的更新操作使用UPDATE语句mysql> create or replace view userinfo (name, department, sex) as select user.name, department, sex from user, user_info where user_id=id; Query OK, 0 rows affected (0.00 sec) mysql> select * from userinfo; +-------+------------+--------+ | name | department | sex | +-------+------------+--------+ | ronie | IT | male | | jack | art | male | | kelly | sale | female | | lucy | IT | female | +-------+------------+--------+ 4 rows in set (0.00 sec) mysql> select * from user; +----+-------+ | id | name | +----+-------+ | 1 | ronie | | 2 | jack | | 3 | kelly | | 4 | lucy | +----+-------+ 4 rows in set (0.00 sec) mysql> select * from user_info; +---------+------------+------+--------+ | user_id | department | age | sex | +---------+------------+------+--------+ | 1 | IT | 23 | male | | 2 | art | 34 | male | | 3 | sale | 33 | female | | 4 | IT | 24 | female | +---------+------------+------+--------+ 4 rows in set (0.00 sec) mysql> update userinfo set department='sale' where name='ronie'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 对视图更新后，基本表的内容也会更新，同样当对基本表更新后，视图中的内容也会更新。 mysql> select * from userinfo; +-------+------------+--------+ | name | department | sex | +-------+------------+--------+ | ronie | sale | male | | jack | art | male | | kelly | sale | female | | lucy | IT | female | +-------+------------+--------+ 4 rows in set (0.00 sec) mysql> select * from user_info; +---------+------------+------+--------+ | user_id | department | age | sex | +---------+------------+------+--------+ | 1 | sale | 23 | male | | 2 | art | 34 | male | | 3 | sale | 33 | female | | 4 | IT | 24 | female | +---------+------------+------+--------+ 4 rows in set (0.00 sec) -- 通常来说不能向视图删除插入数据, 除非视图中的所有数据都来自同一个表, mysql> insert into userinfo (name, department, sex) values ('mike', 'IT', 'male'); ERROR 1393 (HY000): Can not modify more than one base table through a join view 'ronie.userinfo' 使用insert语句mysql> insert into user (name) values ('tom'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+-------+ | id | name | +----+-------+ | 1 | ronie | | 2 | jack | | 3 | kelly | | 4 | lucy | | 5 | tom | +----+-------+ 5 rows in set (0.00 sec) mysql> insert into user_info values(5, 'IT', 22, 'male'); Query OK, 1 row affected (0.00 sec) mysql> select * from userinfo; +-------+------------+--------+ | name | department | sex | +-------+------------+--------+ | ronie | sale | male | | jack | art | male | | kelly | sale | female | | lucy | IT | female | | tom | IT | male | +-------+------------+--------+ 5 rows in set (0.00 sec) 删除视图使用DROP VIEW语句mysql> drop view if exists userinfo; Query OK, 0 rows affected (0.00 sec) mysql> drop view if exists userinfo; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +-------+------+--------------------------------+ | Level | Code | Message | +-------+------+--------------------------------+ | Note | 1051 | Unknown table 'ronie.userinfo' | +-------+------+--------------------------------+ 1 row in set (0.00 sec)
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATAE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。触发器的创建及使用语法：CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt。触发器程序可以使用BEGIN和END作为开始和结束，中间包含多条语句trigger_name表示触发器名称，用户自行指定trigger_time表示触发时机，可以指定为before或after；trigger_event表示触发事件，包括INSERT、UPDATE和DELETE；tbl_name表示建立触发器的表名，即在哪张表上建立触发器；trigger_stmt是触发器执行语句。可以使用BEGIN和END作为开始和结束，中间包含多条语句。mysql> create trigger total_age before insert on user for each row set @a=@a+new.age; Query OK, 0 rows affected (0.01 sec) mysql> select sum(age) into @a from user; Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 97 | +------+ 1 row in set (0.00 sec) mysql> insert into user (name, email, age) values ('baby', 'baby@163.com', 20); Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 117 | +------+ 1 row in set (0.00 sec) mysql> 查看触发器查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。SHOW TRIGGERS语句查看触发器信息mysql> show triggers \G; *************************** 1. row *************************** Trigger: total_age Event: INSERT Table: user Statement: set @a=@a+new.age Timing: BEFORE Created: 2022-10-13 20:00:30.02 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> Trigger表示触发器的名称；Event表示激活触发器的事件；Table表示激活触发器的操作对象表；Timing表示触发器触发的时间；Statement表示触发器执行的操作在triggers表中查看触发器信息在MySQL中，所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT查看。命令：SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;mysql> select * from information_schema.triggers where trigger_name like 'total_%' \G; *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: ronie TRIGGER_NAME: total_age EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: ronie EVENT_OBJECT_TABLE: user ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: set @a=@a+new.age ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2022-10-13 20:00:30.02 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> TRIGGER_SCHEMA表示触发器所在的数据库；TRIGGER_NAME后面是触发器的名称；EVENT_OBJECT_TABLE表示在哪个数据表上触发；ACTION_STATEMENT表示触发器触发的时候执行的具体操作；ACTION_ORIENTATION是ROW，表示在每条记录上都触发；ACTION_TIMING表示触发的时刻是AFTER；剩下的是和系统相关的信息。删除触发器使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器，语法：DROP TRIGGER [schema_name.]trigger_name。schema_name表示数据库名称。如果省略了schema，将从当前数据库中舍弃触发程序；trigger_name是要删除的触发器的名称。mysql> drop trigger total_age; Query OK, 0 rows affected (0.01 sec) mysql>
初始化数据库表mysql> create table user( -> id int primary key auto_increment, -> name varchar(250), -> email varchar(100), -> age tinyint -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into user (name, email, age) values -> ('ronie', 'ronie@163.com', 20), -> ('jack', 'jack@163.com', 34), -> ('jerry', 'jerry@163.com', 22), -> ('lucy', 'lucy@163.com', 21); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+---------------+------+ | id | name | email | age | +----+-------+---------------+------+ | 1 | ronie | ronie@163.com | 20 | | 2 | jack | jack@163.com | 34 | | 3 | jerry | jerry@163.com | 22 | | 4 | lucy | lucy@163.com | 21 | +----+-------+---------------+------+ 4 rows in set (0.00 sec) 存储过程和函数存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。创建存储过程和函数创建存储过程语法：CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics ...] routine_body。proc_parameter为指定存储过程的参数列表，[ IN | OUT | INOUT ] param_name type，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。characteristics指定存储过程的特性，取值：LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。COMMENT 'string'：注释信息，可以用来描述存储过程或函数。-- DELIMITER //语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号;。 -- 为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以END //结束存储过程。 -- 存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。 mysql> DELIMITER // mysql> create procedure avg_age() -> begin -> select avg(age) from user; -> end // Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ; mysql> call avg_age(); +----------+ | avg(age) | +----------+ | 24.2500 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> create procedure num_of_user(out arg int) -> begin -> select count(*) into arg from user; -> end // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call num_of_user(@num); Query OK, 1 row affected (0.00 sec) mysql> select @num; +------+ | @num | +------+ | 4 | +------+ 1 row in set (0.00 sec)创建存储函数语法：CREATE FUNCTION func_name ([proc_parameter]) return type [characteristics ...] routine_body。CREATE FUNCTION为用来创建存储函数的关键字；func_name表示存储函数的名称；func_parameter为存储过程的参数列表，RETURNS type语句表示函数返回数据的类型；characteristic指定存储函数的特性，取值与创建存储过程时相同。如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。mysql> DELIMITER // mysql> create function max_age() -> returns tinyint -> reads sql data -> return (select max(age) from user); -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> select max_age(); +-----------+ | max_age() | +-----------+ | 34 | +-----------+ 1 row in set (0.00 sec) 变量在存储过程中使用DECLARE语句定义变量，DECLARE var_name[,varname]… date_type [DEFAULT value];。var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。mysql> DELIMITER // mysql> create procedure example(out arg int) -> begin -> declare var int default 10; -> select var into arg; -> end -> // Query OK, 0 rows affected (0.01 sec) mysql> call example(@num); -> // Query OK, 1 row affected (0.00 sec) mysql> select @num// +------+ | @num | +------+ | 10 | +------+ 1 row in set (0.00 sec) mysql> 为变量赋值，为变量赋值可以改变变量的默认值。在MySQL中，使用SET语句为变量赋值，SET var_name = expr [, var_name = expr] ...;。mysql> DELIMITER // mysql> create procedure example(out arg int) -> begin -> declare var int default 10; -> set var=30; -> select var into arg; -> end -> // Query OK, 0 rows affected (0.01 sec) mysql> call example(@num)// Query OK, 1 row affected (0.00 sec) mysql> select @num// +------+ | @num | +------+ | 30 | +------+ 1 row in set (0.00 sec) mysql> select * from user; -> // +----+-------+---------------+------+ | id | name | email | age | +----+-------+---------------+------+ | 1 | ronie | ronie@163.com | 20 | | 2 | jack | jack@163.com | 34 | | 3 | jerry | jerry@163.com | 22 | | 4 | lucy | lucy@163.com | 21 | +----+-------+---------------+------+ 4 rows in set (0.00 sec) mysql> drop procedure example// Query OK, 0 rows affected (0.00 sec) mysql> create procedure example(out n char(50), out m int) -> begin -> select name, age into n, m from user where id=1; -> end -> // Query OK, 0 rows affected (0.00 sec) mysql> call example(@name, @age); -> // Query OK, 1 row affected (0.00 sec) mysql> select @name, @age// +-------+------+ | @name | @age | +-------+------+ | ronie | 20 | +-------+------+ 1 row in set (0.00 sec) 定义条件和处理程序定义条件定义条件使用DECLARE语句，DECLARE condition name CONDITION FOR [condition type][condition_ type] :SQLSTATE [VALUE] sqlstate value| mysql error code。condition_name参数表示条件的名称；condition_type参数表示条件的类型；sqlstate_value和MySQL_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_error_code为数值类型错误代码。处理程序使用DECLARE语句定义处理程序，语法：DECLARE handler_ type HANDLER FOR condition value[， . ..] sp_ statement handler_ type: CONTINUEI EXIT| UNDO condition value: SQLSTATE [VALUE] sqlstate_ value | condition name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_ error codehandler_type为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；condition_value表示错误类型，可以有以下取值：SQLSTATE [VALUE] sqlstate_value包含5个字符的字符串错误值；condition_name表示DECLARE CONDITION定义的错误条件名称；SQLWARNING匹配所有以01开头的SQLSTATE错误代码；NOT FOUND匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。MySQL_error_code匹配数值类型错误代码。sp_statement参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。mysql> DELIMITER // mysql> create procedure example(in tb_name varchar(250), out num int) -> begin -> declare drop_tb_error condition for 1051; -> declare exit handler for drop_tb_error select 1051 into num; -> drop table @tb_name; -> end// Query OK, 0 rows affected (0.01 sec) mysql> set @tb_name='not_exists'// Query OK, 0 rows affected (0.00 sec) mysql> call example(@tb_name, @num)// Query OK, 1 row affected (0.00 sec) mysql> select @num// +------+ | @num | +------+ | 1051 | +------+ 1 row in set (0.00 sec)
ANY、SOME关键字ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。mysql> create table nums( -> num int -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into nums values (2), (4), (5), (1), (7); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from nums; +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> create table nums2( -> num int -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into nums2 values (2), (9), (5), (8), (7); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from nums2; +------+ | num | +------+ | 2 | | 9 | | 5 | | 8 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num < any(select num from nums2); +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num < some(select num from nums2); +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num > some(select num from nums2); +------+ | num | +------+ | 4 | | 5 | | 7 | +------+ 3 rows in set (0.00 sec)ALL关键字ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。mysql> select num from nums where num < all(select num from nums2); +------+ | num | +------+ | 1 | +------+ 1 row in set (0.01 sec) mysql> select num from nums where num > all(select num from nums2); Empty set (0.00 sec) EXISTS关键字EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。mysql> select * from user where exists(select id from orders where id=3); Empty set (0.00 sec) mysql> select * from user where exists(select id from orders where id=2); +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | | 3 | mike | NULL | male | +----+-------+----------+--------+ 3 rows in set (0.00 sec) mysql> select * from user where not exists(select id from orders where id=3); +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | | 3 | mike | NULL | male | +----+-------+----------+--------+ 3 rows in set (0.00 sec) IN关键字IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。mysql> select * from user where order_id in (select id from orders); +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | +----+-------+----------+--------+ 2 rows in set (0.00 sec)
初始化表mysql> create table user( -> id int primary key auto_increment, -> name varchar(200), -> order_id int, -> sex enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table orders( -> id int primary key auto_increment, -> info varchar(250), -> create_time datetime -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc user; +----------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(200) | YES | | NULL | | | order_id | int | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +----------+-----------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> desc orders; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | info | varchar(250) | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into user (name, order_id, sex) values -> ('ronie', 1, 'male'), -> ('kelly', 2, 'female'), -> ('mike', null, 'male'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into orders (info, create_time) values -> ('fish', current_date()), -> ('meat', current_date()); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | | 3 | mike | NULL | male | +----+-------+----------+--------+ 3 rows in set (0.00 sec) mysql> select * from orders; +----+------+---------------------+ | id | info | create_time | +----+------+---------------------+ | 1 | fish | 2022-10-10 00:00:00 | | 2 | meat | 2022-10-10 00:00:00 | +----+------+---------------------+ 2 rows in set (0.00 sec) 内连接查询内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。使用WHERE子句定义连接条件比较简单明了，而INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件，而且WHERE子句在某些时候会影响查询的性能-- 完全限定表名（格式为“表名.列名”） -- 使用where查询 mysql> select user.id, name, sex, orders.info from user, orders where user.order_id = orders.id; +----+-------+--------+------+ | id | name | sex | info | +----+-------+--------+------+ | 1 | ronie | male | fish | | 2 | kelly | female | meat | +----+-------+--------+------+ 2 rows in set (0.00 sec) -- 使用内连接查询 mysql> select user.id, name, sex, orders.info from user inner join orders on user.order_id = orders.id; +----+-------+--------+------+ | id | name | sex | info | +----+-------+--------+------+ | 1 | ronie | male | fish | | 2 | kelly | female | meat | +----+-------+--------+------+ 2 rows in set (0.00 sec) 外连接查询LEFT JOIN左连接：左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from user left join orders on user.order_id=orders.id; +---------+----------+-------+--------+------+ | user_id | order_id | name | sex | info | +---------+----------+-------+--------+------+ | 1 | 1 | ronie | male | fish | | 2 | 2 | kelly | female | meat | | 3 | NULL | mike | male | NULL | +---------+----------+-------+--------+------+ 3 rows in set (0.00 sec) mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders left join user on user.order_id=orders.id; +---------+----------+-------+--------+------+ | user_id | order_id | name | sex | info | +---------+----------+-------+--------+------+ | 1 | 1 | ronie | male | fish | | 2 | 2 | kelly | female | meat | +---------+----------+-------+--------+------+ 2 rows in set (0.00 sec) RIGHT JOIN右连接：右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from user right join orders on user.order_id=orders.id; +---------+----------+-------+--------+------+ | user_id | order_id | name | sex | info | +---------+----------+-------+--------+------+ | 1 | 1 | ronie | male | fish | | 2 | 2 | kelly | female | meat | +---------+----------+-------+--------+------+ 2 rows in set (0.00 sec) mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders right join user on user.order_id=orders.id; +---------+----------+-------+--------+------+ | user_id | order_id | name | sex | info | +---------+----------+-------+--------+------+ | 1 | 1 | ronie | male | fish | | 2 | 2 | kelly | female | meat | | 3 | NULL | mike | male | NULL | +---------+----------+-------+--------+------+ 3 rows in set (0.00 sec) mysql> 复合条件连接查询mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders right join user on user.order_id=orders.id order by name desc; +---------+----------+-------+--------+------+ | user_id | order_id | name | sex | info | +---------+----------+-------+--------+------+ | 1 | 1 | ronie | male | fish | | 3 | NULL | mike | male | NULL | | 2 | 2 | kelly | female | meat | +---------+----------+-------+--------+------+ 3 rows in set (0.00 sec)
创建新表并插入新数据mysql> create table user( -> id int primary key auto_increment, -> name varchar(100) not null, -> age tinyint, -> email varchar(30), -> gender enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc user; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint | YES | | NULL | | | email | varchar(30) | YES | | NULL | | | gender | enum('male','female') | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> insert into user (name, age, email, gender) values -> ('ronie', 18, '123@163.com', 'male'), -> ('jack', 22, '456@163.com', 'male'), -> ('lucy', 20, '789@163.com', 'female'), -> ('mike', 30, '147@163.com', 'male'), -> ('kelly', 28, '258@163.com', 'female'), Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into user (name, gender) values ('jerry', 'male'); Query OK, 1 row affected (0.00 sec) mysql> insert into user (name, age, gender) values ('tom', 20, 'male'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+-------+------+-------------+--------+ | id | name | age | email | gender | +----+-------+------+-------------+--------+ | 1 | ronie | 18 | 123@163.com | male | | 2 | jack | 22 | 456@163.com | male | | 3 | lucy | 20 | 789@163.com | female | | 4 | mike | 30 | 147@163.com | male | | 5 | kelly | 28 | 258@163.com | female | | 6 | jerry | NULL | NULL | male | | 7 | tom | 20 | NULL | male | +----+-------+------+-------------+--------+ 7 rows in set (0.00 sec) MySQL集合函数COUNT()函数COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。mysql> select count(email) from user; +--------------+ | count(email) | +--------------+ | 5 | +--------------+ 1 row in set (0.00 sec) SUM()函数SUM()是一个求总和的函数，返回指定列值的总和。SUM()可以与GROUP BY一起使用，来计算每个分组的总和。SUM()函数在计算时，忽略列值为NULL的行。mysql> select sum(age) from user; +----------+ | sum(age) | +----------+ | 138 | +----------+ 1 row in set (0.00 sec) mysql> select gender, sum(age) from user group by gender; +--------+----------+ | gender | sum(age) | +--------+----------+ | male | 90 | | female | 48 | +--------+----------+ 2 rows in set (0.00 sec) AVG()函数AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。mysql> select avg(age) from user; +----------+ | avg(age) | +----------+ | 23.0000 | +----------+ 1 row in set (0.01 sec) mysql> select gender, avg(age) from user group by gender; +--------+----------+ | gender | avg(age) | +--------+----------+ | male | 22.5000 | | female | 24.0000 | +--------+----------+ 2 rows in set (0.00 sec) MAX()函数MAX()返回指定列中的最大值。MAX()函数不仅适用于查找数值类型，也可应用于字符类型，在对字符类型数据进行比较时，按照字符的ASCII码值大小进行比较。mysql> select max(age) from user; +----------+ | max(age) | +----------+ | 30 | +----------+ 1 row in set (0.00 sec) mysql> select gender, max(age) from user group by gender; +--------+----------+ | gender | max(age) | +--------+----------+ | male | 30 | | female | 28 | +--------+----------+ 2 rows in set (0.00 sec) mysql> select max(name) from user; +-----------+ | max(name) | +-----------+ | tom | +-----------+ 1 row in set (0.00 sec) MIN()函数MIN()返回查询列中的最小值。mysql> select min(age) from user; +----------+ | min(age) | +----------+ | 18 | +----------+ 1 row in set (0.00 sec) mysql> select gender, min(age) from user group by gender; +--------+----------+ | gender | min(age) | +--------+----------+ | male | 18 | | female | 20 | +--------+----------+ 2 rows in set (0.00 sec) mysql> select min(name) from user; +-----------+ | min(name) | +-----------+ | jack | +-----------+ 1 row in set (0.00 sec) mysql>