• The total number of eligible records in the SQL query database


    1. Select count (*) from table; / / count tuples 2. Select count (column name) from table; / / count the number of values in a column 3. Select count (*) from table where field = “; / / the total number of records that meet this condition 4. sql_count = “select count(*) from article […]

  • SQL: query the latest published record of each category


    1、 Problem scenario In a table, there are key fields TID, action, dateline, etc. TID represents the ID of the post, action represents the status of the post being operated, and dateline represents the time stamp of the data being inserted; In this table, each TID will have multiple details inserted by different datelines. The […]

  • SQL Server restores the master database by rebuilding


    SQL Server restores the master database through reconstruction, as follows 1. Backup master database 2. Stop the service and delete the master data file directly 3. Rebuild master data file with setup The installation directory under the console will not be mentioned D:\SetUp\sqlserver2012>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=sqlserver2012     /SQLSYSADMINACCOUNTS=UserName /SAPWD= *** 4. Start SQL Server service in […]

  • Leetcode data query notes (medium)


    626. Change seats Xiaomei is an information technology teacher in a middle school. She has oneseatSeat table, usually used to store students’ names and seat IDS corresponding to them. In whichidIs continuously increasing Xiaomei wants to change the seats of the two students next door. Can you help her write a SQL query to output […]

  • Leetcode data query notes (difficult)


    601. The flow of people in the gymnasium A new gymnasium has been built in city x, and the daily traffic information is recorded in these three columns:Serial number (id)、date (date)、 Visitors flowrate (people)。 Please write a query statement to find the peak period, which requires three consecutive days or more, and the daily traffic […]

  • How to view locked tables and unlock methods in SQL Server


    To view the locked table: select spId from master..SysProcesses Where db_name (dbid) = ‘database name’ and spId <> @@SpId and dbID <> 0 Unlock: exec (‘Kill ‘+cast(@spid as varchar)) To view the locked table: select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type=’OBJECT’ SPID lock table process Tablename locked table name Unlock: declare @spid int Set […]

  • Summary of typical scenarios in which indexes are not used in MySQL data SQL optimization


    In some cases, although there is an index, it is not selected by the optimizer. Here are some situations that the index cannot be used in the development process: 1. The like query starting with% cannot use the b-tree index. If the key value in the execution plan is null, the index is not used. […]

  • Show table.vbs of SQL get table structure (freezing point limit NP)


    Copy codeThe code is as follows: set arg=wscript.arguments If arg.count = 0 Then show_help() wsh.quit End If Server = arg(0) User = arg(1) pass = arg(2) database_name = arg(3) table_name = arg(4) set Conn = CreateObject(“ADODB.Connection”) Conn.Open “Driver={SQL Server};Server=” & Server & “;UID=” & User & “;PWD=” & Pass & “;Database=” & database_name Set rs […]

  • Solution to SQL Server 2000 9003 error (SQL2000 only)


    About sqlserver 9003 error resolution only for SQL2000: Unable to open new database ‘pos’. Create database aborted. (Microsoft SQL server, error: 9003)“ Looking at the 9003 error, I think it may be due to the log file. Looking at the database file, I think of the DBCC CHECKDB instruction The method is as follows: 1. […]

  • Maybe you can do this when you forget the MySQL password


    Sometimes you can clobber your configuration. As such, it’s easier to start over, as if the package had never been installed. In your case, we are looking at MySQL. 1. use Yum to Remove MySQL, like so: $ yum remove mysql mysql-server 2. removed MySQL, and safely backup the configuration: $ mv /var/lib/mysql /var/lib/mysql_old_backup 3. […]

  • SQL Server compilation and recompilation


    SQL Server compilation and recompilation Meaning of compilation When SQL server receives any instruction, including query, batch, stored procedure, trigger , the prepared statement and the dynamic SQL statement need to complete the syntax interpretation and statement interpretation, Then compile to generate the execution plan that can run. During compilation, SQL server will estimate the […]

  • Intensive reading of SQL vs flux


    1 Introduction There are two ways to process time series data, as shown in the figure. On the right is SQL, on the left is custom query language, also known as NoSQL. In the middle is SQL-like. This paper tries to give some comparison by comparing timescaledb of SQL camp with influxdb of NoSQL camp. […]