Label:Recently encountered a more interesting problem in the project, the online search for some methods, summarized here to share. We often encounter such a scenario: the need to query data, there are some query conditions, but when the query, we hope that when a condition is empty, then do not filter this condition, the main idea is to deal with the following:
Use pt-query-digest to find the SQL statement that is not suitable, pt-query-digestsql
Overview of pt-query-digest1.
Indexes can be executed more quickly, but there must be unreasonable indexes. If you want to find those indexes that are not very suitable and optimize them before they become problems, you can use the p
First, simple query
SQL (structured query Language) Structured Query language is a database query and programming language for accessing data and querying, updating, and managing relational database systems. ANSI (American National Standards Institute) claims that
SQLServer how to use the T-SQL command to query a database in which tables, convenient table operations, need friends can refer to the next
SQLServer how to use the T-SQL command to query a database in which tables, convenient table operations, need friends can refer to the next
1.
subqueries, multiple table queries, federated queriesThese three concepts are interpreted slightly differently in different versions of SQL, roughly as follows:
Join connectionCan look at the relevant SQL information, or buy a book of SQL
1, such as: SELECT * from TAB1 where ID in (SELECT id form tab2 where ...)The
1. query all tables in SQL:
Select TABLE_NAME FROM database name. INFORMATION_SCHEMA.TABLES Where TABLE_TYPE = 'base table' after execution, you can see the names of all tables created by yourself in the database.
2. query all tables and columns in SQL:
Select dbo. sysobjects. name as Table_name, dbo. syscolumns.
SQL subquery, multi-Table query, and joint Query
Subquery, multi-Table query, and joint QueryThese three concepts are interpreted slightly differently in different SQL versions, roughly as follows:
JoinYou can check related SQL
Next: T-SQL Dynamic Query (1)--IntroductionObjective:In the development of features, we often encounter scenarios such as the following: The application has a query function that allows the user to select the desired condition in many query conditions. This is also the focus of this series.But sometimes you may find th
SQL query statement "3": Multi-Table query (also called connection query, which is a connection query based on two tables)If a query needs to operate on multiple tables, it is called a connection
1. Query all tables in sql:
Select table_name from database name. Information_schema. Tables Where table_type= ' BASE table ' is executed, you can see the names of all the tables in the database that belong to you
2. Query all tables and columns in sql:Select Dbo.sysobjects.name as TABLE_NAME, dbo.syscolumns.name as column_name from Dbo.syscolumns INNER JOIN
Connection Query
You can use the join operator to query multiple tables. Connection is the main feature of the relational database model and a symbol that distinguishes it from other types of database management systems.
In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information about an object is often stored in a
1. query all tables in SQL:Select TABLE_NAME FROM database name. INFORMATION_SCHEMA.TABLES Where TABLE_TYPE = 'base table' after execution, you can see the names of all tables created by yourself in the database.2. query all tables and columns in SQL:Select dbo. sysobjects. name as Table_name, dbo. syscolumns. name AS Column_name FROM dbo. syscolumns inner join dbo. sysobjects ON dbo. syscolumns. id = dbo.
Label:Next: T-SQL Dynamic Query (3)--Static SQLObjective: There's a lot of talk about dynamic queries, and this article describes some of the ways to use dynamic SQL to solve dynamic queries.Why use Dynamic sql: in many projects, dynamic SQL is widely used and even misused,
Grade> = 90
-- Use "logical expression" as the query condition ......
/*
Logical expressions in SQL:
Not: non
And: corresponds
Or: or
*/
Select * from Student
-- Query the student table for a 19-year-old male student.
Select * from Student where Sage = 19 and Ssex = 'male'
-- Query the student whose age is 19 or 20 in
Advanced query technology is mainly related to multiple tables linked query technology, embedded SELECT statement subquery technology, the combination of multiple queries combined technology.
1. Connection Query
You need to retrieve data from two or more tables at the same time. A link allows you to retrieve data from two tables or more than two tables at the s
Tags: SQL statement electronic win style SPAN different statement column DTS --Connect remote SQL or insert data with OPENROWSET
--if only temporary access, you can directly use OPENROWSET
--Query Example
Select * from OpenRowset('SQLOLEDB'
,'SQL Server name';'User name';'Password', database name. dbo. Table name)
Label:IntroductionSee a popular blog today. NET Senior Engineer face questions of SQL, asked to find the highest score of each department, and by the department number, the student number in ascending order. This query is more complex, but also more typical, since the use of ORM, a long time did not write SQL statements, so I studied the next, I also wrote a: w
Method 1:When the publisher filter condition is NULL, how do you write SQL?1 DECLARE @publishers VARCHAR (50);2 SELECT * FROM dbo. Book WHERE Publishers=isnull (@publishers, publishers)Method 2:DECLARE @cinv varchar (50)SELECT * from inventory where 1=1 and ([email protected] or @cinv is null)Method 3:DECLARE @name VARCHAR (+), @page INT =1SET @name = ' Zhang San 'DECLARE @
Routine collection of commonly used SQL query statements and SQL statements
Common SQL query statements are as follows:
1. Simple query statements
1. view the table structure
SQL> DESC
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.