Shulou(Shulou.com)06/01 Report--

No matter in the interview process, or in the future work, SQL will be tested and used. Therefore, some of the SQL knowledge points I have seen before are summarized and recorded, which can be regarded as a notebook. Friends who have not studied SQL in depth and are not very impressed with SQL can take a look at it, which serves as a quick way to use SQL statements (for people with a little knowledge of SQL).

First of all, the most basic thing of SQL is the use of statements. According to its function, SQL is divided into query statements, insert statements, modify statements and delete statements. The most frequently used statements here are query statements. First of all, let's summarize the usage of query statements:

1. Simple query

(1) select name from table

It means to query a column named name in the table table, which will query all the data under name in the table.

(2) select id,name from table

Indicates that all data under the id and name columns are queried in table, separated by commas between the two queried column names.

(3) select * from table

The * sign indicates all, meaning to query all the contents of the table table.

Note: the query statement in this way is not filtered or sorted, so it is the simplest query result; in SQL, it is not case-sensitive; and the SQL statement can be divided into multiple lines, which many people think is more convenient to read and debug, ending with a ";" sign.

two。 Query with different values

Certainly not all the data stored in a table will be different, and many of the values from the query are the same. If you don't want to display duplicate values, you can use the keyword "DISTINCT" when querying to remove duplicates.

Example: select distinct name from table

It means to query the name column in the table table, and show the results after no repetition. For example, the initial query result is xMagnexLigerxLigue, yPercience, ZLY, and the result after removing the duplicates is xPermie, yMagnez.

3. Restrict result query

Generally speaking, a restricted result query is used in paging, because the purpose of restricting a result query is to have the query results displayed according to the number of rows you want, and only briefly introduce the statements of Oracle and MySQL:

(1) .select name from table where rownum), less than (= 10

It means to query the name and price; of the data in the table table whose id is 1 or 01 and the price is greater than or equal to 10. The priority of and is higher than that of or, so add parentheses, the priority of parentheses is higher, otherwise the result will only show that the price is higher than 10.

(4). Select id,name from table where name like "f%"

This means querying id and name data for all rows in the table table where name starts with "f". The "like" keyword and the wildcard character "%" are used here, which can represent all subsequent characters, and "% f%" means "f" in the name. "f% y" means the beginning and end of "f".

(5) select id as idd from table order by id

This means that the id in the table table is queried and sorted in ascending order, and the result of the query is given a new column named "idd", which can be implemented with the "as" keyword to indicate a different name.

6. Aggregate function query

The aggregation function is divided into Avg (), Max (), Min (), Sum () and Count (), respectively, indicating the average value of a column, the maximum value of a column, the minimum value of a column, the sum of the values of a column, and the number of rows of a column, respectively.

(1) select avg (price) as new_price from table

It means to return the average value of the price attribute in the table table and rename it new_price.

(2). Select count (*) as new from table

It means to count the number of rows in the table table, regardless of whether the table contains null or non-empty values, while count (name) means to count the number of rows in the name column, but the null value is ignored. The other three functions are used in the same way, so they are not specified here.

7. Packet data query

Using grouping, the data can be divided into multiple groups, and it is more convenient to calculate each group.

(1). Select name, count (*) as num from table GROUP BY name

It means to group and calculate the name in table, for example

Name num

A 3

B 2

C 4

You can see that he counted and grouped the data rows whose name is ABC.

(2) select id, COUNT (*) AS orders FROM table GROUP BY id HAVING COUNT (*) > = 2

It means to group the id in table and count each group. Filtering data is also used here, requiring the statistical number to be greater than or equal to 2. The keyword HAVING here can replace the role of "where", but usually "where" should be used. HAVING is used in conjunction with Group By.

8. Subquery

The query result of select can be used in the where clause of another select to realize the function that one statement can directly complete the multi-conditional query.

SELECT cust_id FROM table WHERE order_num IN (SELECT order_num FROM Order WHERE prod_id = '01')

You can split it. First, you can see that the order_num in the order table is queried in parentheses if id is 01; while outside the parentheses means to query the id in table, the condition is why num, where num is the result of the query in parentheses. Join clauses with the keyword "IN" and perform two query operations. You can also nest an IN () in parentheses, and you can also add query statements.

9. Table join

One of the most powerful features of SQL is the ability to join tables during the execution of data queries. Joins are the most important operations that can be performed with SQL's SELECT, and a good understanding of joins and their syntax is a very important part of learning SQL.

SELECT vend_name, prod_name FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id

It means to query two name columns in the Vendor and Product tables, and the id of the two tables are equal.

The join used so far is called equivalent join (equijoin), which is based on the equality test between two tables. This kind of inner join is also called inner join. In fact, you can use a slightly different syntax for this join to specify the type of join. In addition, there is a left outer connection and so on.

At present, it is a summary of my SQL query sentence, which is not quite meticulous and in-depth, but it is also a small learning note on the query sentence, all of which I typed bit by bit, and it is also an original bar, hoping to help myself and other people who see it. In addition, another article will be written after inserting statements and deleting and updating statements, December 3, 2016.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.