基于sql查询的面试问题 [英] Interview question based on sql query

问题描述

在我之前的采访中，面试官问我关于sql查询



假设我有学生数据库，它包含10张桌子



i想要从包含特定单词的表中查找行。



限制条件如下



1.我没有餐桌名称

2.我没有列名

3.我只有数据库名称和要搜索的单词。



这可能吗？如果是，你可以分享查询



感谢您的帮助......

解决方案

在我的知识中，您首先需要查询以获取表格和列的名称。



没有标准化的SQL方法可以做到这一点，但是大多数RDBMS实现确实提供了一种方法。这称为元数据或架构查询。有时，RDBMS提供帮助程序来执行此操作，有时它会将模式公开为可以查询的表或视图。例如：

MySQL

 SHOW TABLES; 
 SHOW COLUMNS  IN  Customers; 

或

  SELECT  table_name  FROM  INFORMATION_SCHEMA.TABLES; 
  SELECT  COLUMN_NAME，DATA_TYPE，IS_NULLABLE，COLUMN_DEFAULT 
  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  table_name = ' 客户; 

T-SQL

  SELECT  *  FROM  information_schema.tables;  / *   SQL Server> = 2005 * /  
  SELECT  *  FROM  sysobjects  WHERE  xtype = '  U';  / *   SQL Server< 2005; U ==用户表* /  
  SELECT  COLUMN_NAME，DATA_TYPE，IS_NULLABLE，COLUMN_DEFAULT 
  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  table_name = ' 客户; 

我认为，他/她的目标是：



全文索引是一个很好的功能，可以解决数据库问题，在SQL Server数据库中搜索特定单词和短语的文本数据列。全文索引可用于使用FREETEXT（）和CANTAINS（）以及和或或运算符搜索单词，短语和多种形式的单词或短语。



http://blog.sqlauthority.com / 2008/09/05 / sql-server-creating-full-text-catalog-and-index / [ ^ ]

我不知道为什么面试官问这个问题

但是根据问题，以下查询将产生

结果在MssqlServer 2005及以上

  USE < databasename> 
  DECLARE   @ word   AS   VARCHAR （ 100 ）= ' 测试' 
  DECLARE   @sql  < span class =code-keyword> AS   NVARCHAR （MAX）= ' ' 
  SELECT   @ sql  = < span class =code-sdkkeyword> @sql  + ISNULL（'  SELECT * FROM [' + T.name + ' ] WHERE' + STUFF（（
  SELECT  ' 或[' + c。[name] + ' ]，如''％' + @ word + ' ％''' 
  FROM  sys。[columns]  AS  c 
  WHERE   TYPE_NAME （c.system_type_id） IN （'  varchar'，'  nvarchar'，'  char '，'  text'，'  ntext'） AND  
 c。[object_id] = t。[object_id] 
  FOR  XML PATH（' '）
）， 1 ， 3 ，' '）+ ' ; 
'，' '）
  FROM  sys.tables  AS  t 
  EXEC  sp_executesql @ SQL< /数据库名称> 

In my previous interview , interviewer asked me about sql query

suppose i have student databse & it contain 10 tables

i want to find an rows from tables containing specific word .

Restrictions as below

1. I don't have table name
2. I don't have column name
3. I just have Database name & the word to be searched.

Is this possible to do? if yes can u plz share the query

Thanks for Help......

解决方案

To the best of my knowledge, you would first need to query to get the names of the tables and columns.

There isn't a standardized SQL way to do this, but most RDBMS implementations do offer a way to do it. This is called metadata or schema querying. Sometimes the RDBMS provides helpers to do this, sometimes it exposes the schema as a table or view that can be queried. For example:

MySQL

SHOW TABLES;
SHOW COLUMNS IN Customers;

or

SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Customers';

T-SQL

SELECT * FROM information_schema.tables; /* SQL Server >= 2005 */
SELECT * FROM sysobjects WHERE xtype='U'; /* SQL Server < 2005; U == User table */
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Customers';

I think, He/She is targeting something like:

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with "and" or "or" operators.

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/[^]

I am not sure why the interviewer ask this kind of question
but as per the question the following query will produce
the result in MssqlServer 2005 and above

USE <databasename>
DECLARE @word AS VARCHAR(100) ='Test'
DECLARE @sql AS NVARCHAR(MAX) =''
SELECT @sql = @sql + ISNULL('SELECT * FROM ['+T.name+'] WHERE  '+STUFF((
        SELECT 'OR  ['+c.[name]+'] like ''%'+@word +'%'' '
        FROM   sys.[columns] AS c
        WHERE  TYPE_NAME(c.system_type_id) IN ('varchar', 'nvarchar', 'char', 'text', 'ntext') AND
               c.[object_id] = t.[object_id]
               FOR XML PATH('')
       ), 1,3,'') +';
       ','')
FROM   sys.tables AS t
EXEC sp_executesql @sql</databasename>

这篇关于基于sql查询的面试问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！