根isnull类似,expr1不为null的情况下,返回expr1,返回expr2
nullif和ifnull完全不同,它表示如果两个表达式相同,则返回null,否则返回expr1的值
个人感觉应该叫null if equals
发音是core or less,英文的意思是联合/合并,在mysql中的用途是返回第一个不是null的值
只有两个参数的情况下,相当于ifnull
php 中 mysqli 是个类,这个类的函数(方法)有:mysqli::$affected_rows — Gets the number of affected rows in a previous MySQL operation
mysqli::autocommit — 打开或关闭本次数据库连接的自动命令提交事务模式
mysqli::begin_transaction — Starts a transaction
mysqli::change_user — Changes the user of the specified database connection
mysqli::character_set_name — 返回当前数据库连接的默认字符编码
mysqli::$client_info — Get MySQL client info
mysqli::$client_version — Returns the MySQL client version as a string
mysqli::close — 关闭先前打开的数据库连接
mysqli::commit — 提交一个事务
mysqli::$connect_errno — Returns the error code from last connect call
mysqli::$connect_error — Returns a string description of the last connect error
mysqli::__construct — Open a new connection to the MySQL server
mysqli::debug — Performs debugging operations
mysqli::dump_debug_info — 将调试信息输出到日志
mysqli::errno — 返回最近函数调用的错误代码
mysqli::$error_list — Returns a list of errors from the last command executed
mysqli::$error — Returns a string description of the last error
mysqli::$field_count — Returns the number of columns for the most recent query
mysqli::get_charset — Returns a character set object
mysqli::get_client_info — Get MySQL client info
mysqli_get_client_stats — Returns client per-process statistics
mysqli_get_client_version — 作为一个整数返回MySQL客户端的版本
mysqli::get_connection_stats — Returns statistics about the client connection
mysqli::$host_info — 返回一个表述使用的连接类型的字符串
mysqli::$protocol_version — 返回MySQL使用的协议版本号
mysqli::$server_info — 返回MySQL服务器的版本号
mysqli::$server_version — 作为一个整数返回MySQL服务器的版本
mysqli::get_warnings — Get result of SHOW WARNINGS
mysqli::$info — Retrieves information about the most recently executed query
mysqli::init — Initializes MySQLi and returns a resource for use with mysqli_real_connect()
mysqli::$insert_id — Returns the auto generated id used in the last query
mysqli::kill — Asks the server to kill a MySQL thread
mysqli::more_results — Check if there are any more query results from a multi query
mysqli::multi_query — Performs a query on the database
mysqli::next_result — Prepare next result from multi_query
mysqli::options — Set options
mysqli::ping — Pings a server connection, or tries to reconnect if the connection has gone down
mysqli::poll — Poll connections
mysqli::prepare — Prepare an SQL statement for execution
mysqli::query — 对数据库执行一次查询
mysqli::real_connect — 建立一个 MySQL 服务器连接
mysqli::real_escape_string — Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
mysqli::real_query — 执行一个mysql查询
mysqli::reap_async_query — Get result from async query
mysqli::refresh — Refreshes
mysqli::release_savepoint — Removes the named savepoint from the set of savepoints of the current transaction
mysqli::rollback — 回退当前事务
mysqli::rpl_query_type — Returns RPL query type
mysqli::savepoint — Set a named transaction savepoint
mysqli::select_db — 选择用于数据库查询的默认数据库
mysqli::send_query — 发送请求并返回结果
mysqli::set_charset — 设置默认字符编码
mysqli::set_local_infile_default — Unsets user defined handler for load local infile command
mysqli::set_local_infile_handler — Set callback function for LOAD DATA LOCAL INFILE command
mysqli::$sqlstate — Returns the SQLSTATE error from previous MySQL operation
mysqli::ssl_set — Used for establishing secure connections using SSL
mysqli::stat — Gets the current system status
mysqli::stmt_init — 初始化一条语句并返回一个用于mysqli_stmt_prepare(调用)的对象
mysqli::store_result — Transfers a result set from the last query
mysqli::$thread_id — Returns the thread ID for the current connection
mysqli::thread_safe — 返回是否是线程安全的
mysqli::use_result — Initiate a result set retrieval
mysqli::$warning_count — Returns the number of warnings from the last query for the given link
以上函数清单直接来自 www.php.net 网站。你可以进入该网站参看。
分别在 MySQL5.7.25-log 和 8.0.16 环境中实现类似Oracle的分析函数(8.0版本中已支持,直接使用即可)。一、创建测试数据
二、row_number() over()
三、rank() over()
四、dense_rank() over()
五、lag() over()
六、lead() over()
七、待补充
例1:不分组,全部数据添加序列号,类Oracle 的rownum伪列
例2:先按roomid分组,再按照deviceid,counter排序,类Oracle 的row_number() OVER(PARTITION BY ORDER BY )
例1:不分组,全部数据按 roomid 排序,再添加序号,类Oracle 的rank() OVER(ORDER BY)
例2:先按roomid分组,再按deviceid排序,类Oracle 的rank() OVER(PARTITION BY ORDER BY)
例1:不分组,全部数据按roomid排序,再添加序号,类Oracle 的dense_rank() OVER(ORDER BY)
例2:先按roomid分组,再按deviceid排序,类Oracle 的dense_rank() OVER(PARTITION BY ORDER BY)
例1:不分组,全部数据按roomid,deviceid升序排序,类Oracle 的lag() OVER(ORDER BY)
例2:先按roomid分组,再按roomid,deviceid排序,类Oracle 的lag() OVER(PARTITION BY ORDER BY)
例1:不分组,全部数据按roomid,deviceid,counter升序排序,类Oracle 的lead() OVER(ORDER BY)
例2:先按roomid分组,再按deviceid,counter排序,类Oracle 的lead() OVER(PARTITION BY ORDER BY)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)