Database Learning Note 4 powerful SQL

In fact, this article should be at least one weeks ago should be updated, but the recent small ape and I have been fond of the goddess for many years in hand success, so these days a little drifting.

---创建表结构create table tablename(    id int,    name varchat(20) not NULL,    address varchat(100) ,    primary key (id),    FOREIGN  KEY (id) REFERENCES retable);---删除数据delete from tablename;---删除关系（删除表）drop table tablename;---添加列alter table tablename add name varchat(20);---删除列alter table tablename drop name;---插入数据insert into tablename values(...);---查询语句(多关系查询)select * from tablename where ...;---对查询查询内容进行去重select distinct name from tablename;

Aggregation functions

The most frequently used aggregation function is also

• Avg Average
• Sum sum
• Min min value
• Max Maximum Value
• Count Record Count

Grouped aggregate GROUP BY

For GROUP by just remember that if a property is not present in the GROUP BY clause, it can only be represented as a clustered function in the SELECT clause.

Join using

This study also encountered a pit that did not understand what a join using. I checked it again. You can use join using to simplify join on when two table associated fields use the same specification

SELECT * from A join B on A.name=b.name

Can be replaced by SELECT * from A join B using (name)

Top

Because I am a "SQL Server 2012" and "Database system concept" to see together, so sometimes encounter some small differences

One of the exercises in the database system concept is to find the highest-paid owners (with the highest wages and many people)

My first idea is to use the last few days to see the top mate with ties can be a simple solution to this problem, the implementation of the discovery of MySQL does not support such a grammar with the corresponding is that the corresponding limit,oracle should be the use of rownum.

External connection

The outer connection divides into the left, the right, all three kinds of connection way.

The left OUTER join is left table as the primary table, the null is not in the right table, and the right outer join is opposite.

All outside connections will be judged.

SQL VIEW

Always listen to the big gods around this feature or that feature should be in SQL view perhaps a bit simpler, this time finally to see SQL view hosting.

The view contains rows and columns, just like a real table. A field in a view is a field from a real table in one or more databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can also submit the data as if they came from a single table.

Knowing what's going on with SQL view, I began to tangle with the question of how the application was called. In fact, a simple view is a virtual table stored on a database, but only when it is used is the data queried.

The time in SQL

There are three basic types of time represented in SQL

• Date indicates the day of the month
• Time represents seconds and minutes
• Timestamp is a combination of date and time

Database Learning Note 4 powerful SQL