Previously, I talked about some simple use of the SELECT statement. This time, I will learn more about the SELECT statement.
Including: Where condition limit query, range query, fuzzy query, and sorting of query results.Where condition-limited Query
We all know that there are nine SQL statements in the world of SQL, and the most complex of these statements is our SELECT statement, which is the most used statement in our database, therefore, if we have mastered the SELECT statement, we will have
Select Format:SELECT [All | DISTINCT] From ,[, ...][WHERE [GROUP BY [having[Order by [ASC | DESC]]
Statement Description:[] square brackets are optional[GROUP BY [havingThe result is grouped by the value of the , which is equal to a set of
There is a field "name" in Table A,and the "name" value may be the same between different records,Now you need to query the records in the table, the "name" value duplicates the item;Select Name,count (*) from-A Group by Name has Count (*) > 1If the
You have seen two types of MySQL-so-far queries: queries we used to build tables and queries, we used to insert data into our newly created table. The query in this lesson is the selection that is used to get information from the database, so that
1. The FROM clause assembles data from different data sources;
2. The WHERE clause filters the record rows based on the specified criteria;
3. The GROUP BY clause divides the data into multiple groupings;
4, using aggregation function to calculate;
5
SQL Server Databasethe Count () function returns the number of rows that match the specified criteria. The syntax SQL count (column_name) syntax count (column_name) function returns the number of values for the specified column (NULL does not count
Note: The double and enclosed variables must be constant before query execution, for example, & previous day &
Select a. billdate as fdate, max (Year (A. billdate) as fyear,Max (month (A. billdate) as fperiod, 'record' as fgroupid, '1' as fnumber,'20
Preface
The best way to learn is practice! Therefore, my learning is based on practice. The most basic SQL statement is the query statement. Therefore, I also learned from the select statement. (In fact, this part of the author) has been mastered,
I. About indexes:
Rationality of using indexes:
The variable sequence in the condition clause should be the same as that in the index key sequence. (Try to create an index on the join and order by fields as much as possible) place the most
The cursor select operation will not perform any lock settings on the row being processed, so that other sessions connected to the database can change the selected data, using the for update clause, A mutex lock is added to the corresponding row of
Select sum (1) From mytable
If the table contains 100 records, 100 constant 1 is returned.
It is generally used as a condition. When used as a condition:Select 1 from authorsAndSelect * from authors is equivalent
Delete from table_aWhere not
1. The from clause assembles data from different data sources;
2. The WHERE clause filters record rows based on specified conditions;
3. The group by clause divides data into multiple groups;
4. Use Aggregate functions for computation;
5. Use
To avoid table join queries, you need to add a field to the SQL statement results:
The original SQL statement is:
Select C. * From (select rownum as R, B. * From (select T ., T ., T ., T ., T ., t. user_id_encrypt, T ., t. create_time, T ., T ., T
Execute immediate replaces dbms_ SQL package in Oracle8i. it parses and immediately executes dynamic SQL statements or PL/SQL blocks created when they are not running. the performance of dynamic creation and execution of SQL statements is advanced.
1. The from clause assembles data from different data sources;2. The where clause filters record rows based on specified conditions;3. The group by clause divides data into multiple groups;4. Use Aggregate functions for computation;5. Use the having
Let me introduce you to the concept of join (connection). Two test tables were prepared for this purpose: album (album table) and track (track table).
Album table: Contains an overview of 200 music CDs from Amazon.
Album (ASIN, title, artist,
All syntax
scalar_expression {= | <> |!= | > | >= |!> |
Scalar_expressionAny valid expression.
{= | <> |!= | > | >= |!> | comparison operator.
SubqueryReturns a subquery for a single-column result set. The data type of the returned column must
SELECT * FROM (select xxx from XXX) as a inner joins (select xxxx from xx) as B where a.xx=b.xxSELECT * FROM (select ID,PID,KHDM,KHMC to Com_base_kehu where khdm2= ' DHB003 ') as a LEFT join (select a.ID as sequence number, a.org_id,
1. Single-Table query: Filter the records in the table according to where conditions, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select
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.