Preface:

Because of the requirements of the company, so spent about a week to understand and memory SQL language, based on practical rather than a thorough understanding of the situation, SQL statement is actually very simple, now, I put their time to arrange can let out, for new people to explore, for big guy criticism.

Because it is a new person on the road, so there may be insufficient, welcome correction, I have as far as possible to ensure the correct.

Add data (insert data) :

Specify insert:

Insert into table_name (1, 2, 3......) Values (content 1, content 2, content 3);Copy the code

Default insert:

Insert into values (1, 2, 3);Copy the code

These are two ways to insert data directly into a table,

The first method is to follow the previous content, and the second method is to insert data according to the table content by default.

If there is insufficient data in both, data is inserted into the table as NULL.

Delete data:

Specify row deletion:


delete from table_name whereField name = "specified value";Copy the code

(Empty table contents, do not free space, that is: next time insert table data, id still continue to delete the id continues to increase)

Data deletion:

(note: none of the following deletes will clean up the form of the table, only the contents of the table;)


delete * from table_name;

Copy the code

(delete all items in table directly, unrecoverable;)

Truncate: TRUNCate table name 1;Copy the code

Select * from table where id = 1;

Drop table drop table name 1;Copy the code

(The entire table was deleted, to use the table must be re-created)

Modify data:

Specify changes:

Update the table namesetField name 1= 'new value', field name 2= 'new value'whereField name = the content of the row you want to delete;Copy the code

All modifications:

Update the table namesetField name = 'new value', field name 2= 'new value';Copy the code

Note: Without the WHERE clause, everything is changed to the value you specify;

Query data:

All enquiries:

Select * from table_name;Copy the code

Specify column query:

Select column name from table name;Copy the code

To redo query:

Select distinct * from table name;Copy the code

Conditional query:

Select field name from table namewhereQuery conditions;Copy the code

Sort query:

1, select * from * order by * desc; 1, select * from * order by * asc; (Positive order, ASD can be omitted)Copy the code

Sum query:

Select sum from table1whereConditions;Copy the code

Average query:

Select avg from table1whereConditions;Copy the code

Maximum value query:

Select Max from table1whereConditions;Copy the code

Minimum query:

Select min from table1whereConditions;Copy the code

A table:

(Note that I will use the column_name instead of the name of the column. I hope it will not affect the reading.)

In terms of table association, although functionally this looks like what’s in the query, I’ve listed it separately. After all, that’s what bothered me in the first place.

This keyword is not difficult, simple to say, is to put two tables together, through a field association, to merge the query. The following are the known associations;

Suppose you want to associate the ID field on two tables for a query. The following is an example (the following statements have been tested in ORACLE database and can be used).

The first table

The left link:

select table_name1 as a left join table_name2 as b on a.id =b.id; (In this statement, as can be omitted)Copy the code

Right connection:

select * from table_name1 as a right join table_name2 as b on a.id =b.id; (In this statement, as can be omitted)Copy the code

All related:

select * from table_name1 as a full join table_name2 as b on a.id=b.id; select * from table_name1 a join table_name2 as b on a.id=b.id; (In this statement, both full and as can be omitted)Copy the code

Cross correlation:

Select * from table_name1 as a cross join table_name2 as b on A.id = B.id; select * from table_name1 as a cross join table_name2 as b on A.id = B.id;Copy the code

In the link:

select * from table_name1 as a inner join table_name2 as b on a.id=b.id; (In this statement, as can be omitted);Copy the code

About table association here, I in the first time to see, very noisy do not understand, perhaps is my understanding is too poor, later checked several people’s blog, just figure out what is the matter here, so, here a little say personal opinion.

(here put ahead of the bosses of links: www.cnblogs.com/thomasguan/… If you are interested, you can read directly, and skip the following personal opinions.)

Association, is to you determine the name of the field, connect, and then corresponding to the start of the query, generally associated with the field name is a certain correlation.

By associating field names, you can make a comparison display, and to make a comparison display, there are several possibilities:

1. Left association: the left table is mainly displayed, while the right table does not display the fields not found in the left table;

2, right association: the right table is mainly displayed, do not display the fields in the left table;

3, full association: all comparison between two tables, one-to-one correspondence, regardless of whether there is a row in two tables, will be displayed;

4, internal correlation: still one-to-one correspondence, but just the opposite with the above, there are two, he will not show;

5. Cross-association: take a row from one table, match all rows in another table, and then perform the corresponding to the last row in this table.

The above is a few common forms of associated query, of course, with text may be a little round for beginners, here I want to save trouble, did not put the graph, in fact, if you look at the resulting graph, it will be very easy to understand the association of this piece.

I was a little bit afraid of the place because I stepped in a lot of holes at the beginning. However, after coming over, I found that in fact it is such a thing, too simple, it is a fool can understand things (wait, this will not say that I am a fool? Emmmm…)

Grouping:

group by

(This keyword is simple to use to classify the contents of a table by field name)


select column_name1, column_name2 from table_name group by column_name3;

Copy the code

Example:

Select XB gender,count(1) from CET_01 group by XB;Copy the code

Above, I counted the number of male and female students by group by, and conducted group query based on the content of fields.

having

Where, having, group by Where filter individual records, HAVING filter group records (group first, filter later);

Select XB having count(1) from CET_01 group by XB having count(1)>300;Copy the code

To pick up where we left off, we want to know which of the two has more than 300 men or women. We just have to use the having clause.

Note that there is a hole in the having clause where where is not allowed because the group by group is not allowed.

Conclusion:

Say a sad fact, I originally thought oneself see SQL basic statement so solid, should also no big deal, even if have a problem, with my present ability, at least can understand it?

But two days later, I just some disturbing discovery, the real problem, their level is still only a handyman, there are a lot of local queries, not difficult in the statement, but difficult in the idea and the degree of familiarity with the table.

When really used, more is the use of table associative query, there are alias usage, and the use of association.

In this process, the most used are the built-in SQL functions, such as DECODE(),sum(), AVg (), NVL () and so on.

There e are a lot of places, I write very repeated SQL statements, after the big guys check, most will be rewritten, I found that I write SQL is not spirituality. At least their SQL is simple and has a logic to it, as opposed to my repetitive basic SQL.

I have to say, there is still a long way to go, I am still too green, I still need to work hard.

Finally, if you can spray anywhere, please spray lightly… Or, respray something that can hit a sore spot and be useful…

I was going to release my understanding of the function part in two days, but I was shivering after I saw the big guy’s SQL statement in the company. Fairy writing, a lot of times have a completely unexpected feeling.

I will release my understanding of SQL when I can fully adapt to the function part in SQL. Thank you.