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

Navicat Premium is a management tool that can connect to multiple databases. It allows you to connect to MySQL, Oracle and PostgreSQL databases simultaneously with a single program, making it more convenient to manage different types of databases.

Data is the core of many enterprises, large and small. For example, Facebook stores each user's profile information, including data about their friends and posts in the database system. SQL (short for structured query language) is a programming language that enables developers and database administrators to use this data.

For database work, you should be familiar with some common SQL commands. Excluding data definition language (DDL) or data manipulation language (DML) statements, SQL commands include commands that use select statements to get data from tables and views. Today's article will cover some of the most important queries and some examples of using Navicat Premium as a database client.

Determine the minimum / maximum value of the column

The Sakila sample database contains a number of tables covering topics ranging from actors and studios to the film industry around video rental stores. The query we will build here today will run against it, so you may need to refer to the generate report tutorial on MySQL data for instructions on downloading and installing the Sakila database.

One central table in the Sakila database is the movie table. It contains the details of each movie owned by our fictional video rental store. It includes information such as the movie title, the year of release and the rental price:

Suppose we want to know what the price range is-that is, what are the lowest and highest rents? We can easily find aggregate functions using MIN () and MAX (). The aggregate function performs calculations on a set of values and returns the result of a single value. There are many aggregate functions, including AVG, COUNT, SUM, MIN, MAX, and so on. This is a query that applies MIN () and MAX () to the rental_rate field of the movie table:

SELECT MIN (f.rental_rate) as lowest_price, MAX (f.rental_rate) as highest_priceFROM film f

As expected, each function returns a value:

Group results by category

One of the most powerful clauses in SQL is GROUP BY. It groups rows with the same value into summary rows. Therefore, GROUP BY statements are often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group result sets into one or more columns.

We can use the GROUP BY clause to list the lowest and highest rental costs for each movie rating, such as "General", "PG", "PG-13" and so on. All we need to do is add the rating field to the column list and append the GROUP BY clause to the end of our existing query:

SELECT f.rating, MIN (f.rental_rate) as lowest_price, MAX (f.rental_rate) as highest_priceFROM film fGROUP BY f.rating

Our results show that the price of each rated movie ranges from $0.99 to $4.99:

Conclusion

Today's blog introduces some of the most important queries, as well as some examples of using Navicat Premium as a database client. Navicat helps you quickly write code-complete and customizable code snippets by getting suggestions for keywords and removing duplicates from the coding.

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.