Oracle from getting started to mastering questions about simple queries

Video lesson: Li Xinghua Oracle from Getting started to mastering video lessons

Learner: Sun Luo nuo

Video Source: 51CTO College

If you want to query, you must use the query part of the DML support, relative to the simple query, the popular understanding is that all records are queried, but you can control the display of the column through the syntax. The SQL syntax structure for a simple query is as follows: ②select [DISTINCT] * | column [alias], not [alias],.......①from table name [alias], both in order of execution is first executed from ①, and then to execute ② example: if you use "*" in the SELECT clause again "Means querying all the data columns in a table. Example: Querying all records in an EMP table SELECT * from EMP; In this program, the FROM clause is the source of the data, as long as it is a table structure (a collection of rows and columns), and the SELECT clause controls the data columns that are required.    Example: the projection of the data ———— control the columns of data that need to be displayed. Query each employee's number, name, base salary syntax format: SELECT empno,ename,sal,job from EMP;! [] (http://i2.51cto.com/images/blog/201807/03/7d3aff027d278824f6971af6d095430e.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) In addition to the basic query columns, arithmetic is supported in simple queries and can be arithmetic directly using the contents of the column. Ask for each employee's number, name, basic annual salary (monthly wage Sal, annual salary *12); Syntax format: SELECT empno,ename,sal*12 from EMP; find the list name in this section is not good, in order to achieve aesthetic effect, you can set the alias 。 We can write like this. However, in general, * * are not recommended for use in Chinese. * * SELECT empno employee number, ENAME employee name, sal*12 annual salary from EMP; Chinese is also supported in the case of data table names or column names that you define later, but you want to develop normal points and try to use English as much as possible. [] (http://i2.51cto.com/images/blog/201807/03/2dd48cf236d99667b03a64f51166BAF8.PNG?X-OSS-PROCESS=IMAGE/WATERMARK,SIZE_16,TEXT_QDUXQ1RP5Y2A5A6I,COLOR_FFFFFF,T_100,G_SE,X_10,Y_10, shadow_90,type_zmfuz3pozw5nagvpdgk=) In fact, in the process of simple query, but also support the data connection operation, using the "| |" To connect.        Example: Observing connections. SELECT Empno | | Ename from EMP; The result diagram is as follows:! [] (http://i2.51cto.com/images/blog/201807/03/5cb68e816646d0fe52fd0f801e8b7acb.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Believe this result diagram, a lot of people see certainly is this what thing? In order to make this connection more effective look a little bit. We can use some text descriptions in the middle. For example: Now hope the final format is: "No.: XXX, Name: XXX", for our numbers and names are data columns that are queried through the data table. For some fixed output the content must be processed, for this part of the processing temporarily only consider the use of two types of data: Ordinary numbers: then write directly. (SELECT ename | | 1 from EMP;)! [] (http://i2.51cto.com/images/blog/201807/03/bfbf86d4ede2f505c969fa6766eedbd5.png?x-oss-process=image/        watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) String: Use single quotation marks for description. (SELECT empno | | ' Hello ' from emp;)! [] (http://i2.51cto.com/images/blog/201807/03/c772c508f582a3ca36af5fca06443813.png?x-oss-process=image/watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90, type_zmfuz3pozw5nagvpdgk=) Example: implementation of formatted output syntax format: SELECT ' number: ' | | Empno | | ' Name: ' ename from EMP;! [] (http://i2.51cto.com/images/blog/201807/03/f62be5d5b627d609023d607cf58a7f4b.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) In a simple query, there is a distinct keyword, the primary purpose of which is to eliminate duplicate content. Example: Find job information for all employees. [] (http://i2.51cto.com/images/blog/201807/03/0c3c32fbe18d0dab3f74c70fcf61142a.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) In the diagram, we will find that many people work together, so now if you do not want duplicate content in it, then you can use distinct in the SELECT clause to eliminate duplicate content. Syntax format: SELECT DISTINCT Job from EMP; results! [] (http://i2.51cto.com/images/blog/201807/03/a0ff2028b3eacb77050ccc13e91f3c5f.png?x-oss-process=image/ Watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Note: If duplicate content is eliminated, it refers to the result of querying the contents of all columns of the data that are queried. Example: Observe the results of the following query SELECT DISTINCT ename,job from EMP; the results are as follows:! [] (http://i2.51cto.com/images/blog/201807/03/cdb6a7c0e4a41d3525bbb9ddefb57e3e.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Believe that you will see another 14 rows of data content. At this point, because the name and position do not have duplicate content, you will not be able to delete the duplicate content. * * Summary: The biggest feature of a simple query is that it controls the data column, but it cannot control the data column. **

Oracle from Getting started to mastering questions about simple queries