Follow me to learn sql: (i) data query

Source: Internet
Author: User
Tags define include logical operators
The data does not say whether you are working on programming or are not going to learn SQL, but virtually every developer will eventually encounter it. You probably don't have to be responsible for creating and maintaining a database, but you should know some of the following about SQL knowledge.
I wrote this overview article about basic SQL syntax for interested developers or readers who can benefit from database operations. This article mainly discusses basic data manipulation queries, and subsequent articles will continue to discuss how to modify the database itself and more advanced query concepts.

What happened to the SQL database?

SQL (Structured Query language) is the standard for database interaction with ANSI maintenance. The latest version is SQL-99, and a new standard sql-200n is still in the making process. Most databases adhere to at least some subset of the ANSI-92 standard. However, there are still some arguments about the effectiveness of the latest standards. Proprietary database manufacturers develop their own products based on these standards, while developing their own unique database storage operations new concepts. Almost all of the different databases contain their own collection of grammars, but they are usually similar to ANSI standards. In most cases, although some database instances have different results depending on the database depending on the specific extended syntax, the new syntax is generally an extension of the original standard. If the database operation does not get the results you want, you may wish to read the product instructions provided by the database manufacturer beforehand.

If you've been encountering SQL language for the first time, you'll have to understand some basic SQL concepts first. I try to keep this basic knowledge simple and concise, and if you can tolerate those database terms, you can skip to the next section and you can also submit your questions to the following discussion area.

Generally speaking, "SQL database" is actually a relational database management system (RDMS) popular term. For some systems, a "database" also refers to a set of data tables, data, and configuration information that is distinguished from one another but similar in structure. In this case, the installation of each SQL database may consist of several databases. On some systems, this database refers to tablespace.

A datasheet is a database construct that contains multiple rows of data, which are constructed of named columns. A data table is typically constructed to contain relational information, and several data tables can be created within the same database or tablespace.

A column in a table holds a type of data and should be named based on the contents of the data it holds. For example, a column called "LastName" should contain a last name entry in each row. It is this premise that allows relational database queries to return consistent results.

field refers to the data (or where the data is saved) for a column in a row. In addition, the data set refers to multiple rows and columns of data, and the data set usually indicates all the data in your database or datasheet. The result set, which is the data returned from a database query, can describe all the information from a single field to the full range of data in the database.

A database query is an SQL instruction sent to a database requesting a function that is applied to a data collection or database.

Now let's take a look at basic database queries, which mainly involve manipulating data within the database. In this article, all examples are standard SQL languages, and these basic functions can be converted to applications in almost every environment.

Data query Type

The data query in SQL language is divided into 4 basic types:

SELECT: This statement requires the database to return a collection of data for the specified result, which you can use to retrieve the information stored in the database.
INSERT: This statement is used to add a new row of data to the datasheet.
Delete: This statement deletes several rows of data from your database.
UPDATE: This statement modifies existing data in the database.
These statements all have a variety of qualifiers and functions that you can use to define the relevant data set, while controlling the collection of results returned by the query. The SELECT statement has the most options. There are many kinds of query options that combine select, such as join and union. But as far as we are concerned, this article mainly focuses on basic uses.

Retrieving saved information with a SELECT statement
In order to obtain the information stored in the database, a SELECT statement must be used. Its basic functionality is limited to operating on a single datasheet, and of course, there are other ranges of constructs. To return all rows of data for a particular column, you can use the following statement:

SELECT Column1, column2 from table_name;

In addition, use the wildcard "*" to select all columns from the table:

SELECT * FROM table_name;

You have to be willing to encode your own analysis of the results returned above of course, but you can completely use the convenient WHERE clause to limit the return of the result set, which allows you to select the data to define certain conditions. The following query returns the data row with the Column1 value equal to 3:
SELECT * FROM table_name WHERE column1 = 3;

In addition to the "=" (equals) condition, you can also use the following conditional operators:

Table A

= equals

<> Not equal to

> Greater than

< less than

>= is greater than or equal to

<= less than or equal to


SQL Conditional statement

In addition, you can combine the where statement with between, like comparison operators, and logical operators such as and and OR. Note that the or statement is inclusive. Here's an example that combines these concepts:

SELECT * FROM table_name WHERE (age <) and (LastName BETWEEN ' Anderson ' and ' Miller ')) OR company like '%school% ';

In natural language, this choice is meant to select data rows that are younger than 18 years old and whose surnames are between "Anderson" and "MIller", or that have "School" in their company name class, from the datasheet.

Add new data with INSERT statement
Use the INSERT statement to create a new data row. If you want to assign a value to a field in a row, use the UPDATE statement.

The syntax of the INSERT statement is as follows:
INSERT into table_name (column1, Column2, Column3)
VALUES (' data1 ', ' data2 ', ' data3 ');

If you want to insert all the values in the same order as the existing columns in the table, you do not have to specify the column names, and of course, it is best not to do so in terms of readability. Also, if you list column names, you do not have to include them in the order in which they appear in the database, as long as you list values that correspond to their one by one. Some columns you don't have to enter new information so you naturally don't need to list them.

This is similar to the fact that once data is in the database to be modified.

UPDATE statement and WHERE clause
Update is used to modify an existing value or a blank field in a row, so it must provide an acceptable value at the same time that it matches the current set of data. Unless you really want to modify the values on all data rows, you must use the WHERE clause.
UPDATE table_name SET column1 = ' data1 ', column2 = ' data2 '
WHERE column3 = ' data3 ';

You can use the WHERE clause to match any column randomly, and a column that you are modifying is OK. This will help you modify a particular value to another value:

UPDATE table_name SET FirstName = ' Shelley '
WHERE FirstName = ' Shelly ' and LastName = ' Doll ';


Be careful with the DELETE statement
The DELETE statement deletes the entire row from the data table in the database. If you just want to delete a single field, you should use the UPDATE statement to modify it to represent other null values for nulls in your application. Be careful to use a DELETE statement with a WHERE clause, or you may experience the risk of emptying the entire datasheet.
DELETE from table_name WHERE column1 = ' data1 ';

Once you delete a row of data in your database, you can no longer regret it, so in general, it is best to include a column in the datasheet as "IsActive" or a similar indication, so you could set the column data to zero to indicate that the data is disabled. You can use the DELETE statement only if you are sure you no longer need the affected information.

Summary
SQL is the language of the database, and we have learned the most basic command statements used in data queries. But there are a lot of basic concepts that are not covered, such as sun and count functions, but the commands listed above should be enough to start the database operation.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.