SQL Quick Start

SQL Quick Start

Author: Release Date: Source: http://www.phome.net/document/php/200503/php1110535081313.html

SQL is the abbreviation of structured query language. It refers to the structured query language. The main function of SQL is to establish contact and communicate with various databases. According to ANSI (American National Standards Association), SQL is used as the standard language for relational database management systems. SQL statements can be used to perform various operations, such as updating data in the database and extracting data from the database. Currently, most popular relational database management systems, such as Oracle, Sybase, Microsoft SQL Server, and access, use SQL language standards. Although many databases redevelop and expand SQL statements, they include select, insert, update, delete, create, standard SQL commands, including drop, can still be used to perform almost all database operations. Next, we will introduce the basic knowledge of the SQL language in detail. Database tables A typical relational database usually consists of one or more objects called tables. All data or information in the database is stored in these database tables. Each table in the database has its own unique table name, which is composed of rows and columns. Each column contains the column name, data type, and other attributes of the column, the row contains the records or data of a column. The following is an example of a database table named weather. Highest Temperature and lowest temperature in the city Beijing 10 5 Shanghai 15 8 Tianjin 8 2 Chongqing 20 13 In this table, "city", "maximum temperature" and "minimum temperature" are three different columns, and each row in the table contains specific table data. Data Query Among the many SQL commands, select statements are the most frequently used. The SELECT statement is mainly used to query databases and return results that meet the user's query criteria. The syntax format of the SELECT statement is as follows: Select column1 [, column2, etc] From tablename [Where condition]; ([] Indicates the option) The column name after the select keyword in the SELECT statement is used to determine which columns will be returned as the query result. You can select any column as needed, and use the wildcard "*" to set all columns in the returned table. The table name after the from keyword in the SELECT statement is used to determine the target table to be queried. The where clause in the SELECT statement specifies which data values or rows are returned or displayed as query results. You can use the following operators to set the query criteria in the WHERE clause: = Equal > Greater <Less > = Greater than or equal <= Less than or equal <> Not equal In addition to the operators mentioned above, the like operator is also very important in the WHERE clause. The like operator is very powerful. By using the like operator, you can set to select only records in the same format as specified by the user. In addition, we can use the wildcard "%" to replace any string. Example: Select firstname, lastname, City From employee Where firstname like 'e % '; (Note: The string must be included in the brackets) The preceding SQL statement queries all names starting with "E. Alternatively, use the following statement: Select * from employee Where firstname = 'may '; Query all rows named "may. Create a table The create table statement in SQL is used to create a new database table. The format of the create table statement is as follows: Create Table tablename (Column1 data type, Column2 data type, Column3 data type ); If you want to specify the column restrictions when creating a new table, you can use the optional condition options: Create Table tablename (Column1 data type [constraint], Column2 data type [constraint], Column3 data type [constraint]); Example: Create Table employee (Firstname varchar (15 ), Lastname varchar (20 ), Age number (3 ), Address varchar (30 ), City varchar (20 )); Simply put, when creating a new table, add the name of the table to be created after the keyword "create table", and set the name and data type of each column in brackets, and optional restrictions. Note that all SQL statements must end with the ";" symbol. The names of database tables and columns created using SQL statements must start with a letter, followed by letters, numbers, or underscores. The name length cannot exceed 30 characters. Note: When you select a table name, do not use the reserved keywords in the SQL language, such as select, create, and insert, as the table or column name. The data type is used to set the data type in a specific column. For example, you can only use the varchar or char data type in the name column, but not the number data type. Commonly used data types in SQL are: Char (size): a fixed-length string. The size in parentheses is used to set the maximum length of the string. The maximum length of a char type is 255 bytes. Varchar (size): a variable-length string. The maximum length is set by size. Number (size): number type. The maximum number of digits is set by size. Date: date type. Number (size, d): number type. Size determines the maximum number of digits. D is used to set the number of digits after the decimal point. Finally, when creating a new table, you must note the restrictions on the columns in the table. A condition is a rule that must be followed when data is input to a specific column. For example, the unique condition requires that there cannot be two records with the same value in a column, and the values of all records must be unique. In addition to unique, restrictions on commonly used columns include not null and primary key. Not null indicates that the value of a column in the table cannot be null. The primary key specifies a unique identifier for all records in the table. Insert data to a table The SQL language uses the insert statement to insert or add new data rows to the database table. The format of the insert statement is as follows: Insert into tablename (First_column,... last_column) Values (first_value,... last_value ); For example: Insert into employee (Firstname, lastname, age, address, city) Values ('lil', 'ming', 45, 'No. 77 Changan road', 'Beijing "); Simply put, when adding a new record to a database table, enter the name of the table to be added after the keyword insert into, and then list the names of the columns to add new values in brackets. Finally, after the keyword values, enter all the record values to be added according to the sequence of the columns entered earlier. Update record The SQL language uses the update statement to update or modify existing records that meet the specified conditions. The update statement format is: Update tablename Set columnname = newvalue [, nextcolumn = newvalue2...] Where columnname operator value [and | or column operator value]; For example: Update employee Set age = age + 1 Where first_name = 'Mary 'and last_name = 'williams '; When using the update statement, the key point is to set the where Condition Clause for judgment. Delete record SQL uses the delete statement to delete rows or records in database tables. The format of the delete statement is: Delete from tablename Where columnname operator value [and | or column operator value]; For example: Delete from employee Where lastname = May; To put it simply, when you need to delete a row or a record, enter the table name after the delete from keyword, and then set the judgment condition for deleting the record in the WHERE clause. Note: If you do not set the WHERE clause when using the delete statement, all records in the table will be deleted. Delete database tables Use the drop table command in SQL to delete a table and all records in the table. The format of the drop table command is as follows: Drop table tablename; For example: Drop table employee; If you want to delete a database table completely, you only need to enter the name of the table you want to delete after the drop table command. The drop table command is different from deleting all records in a table. After deleting all records in the table, the table still exists, and the column information in the table does not change. Using the drop table command, all information about the entire database table is deleted. The preceding sections describe the main Commands and statements of the SQL language in detail. It should be said that the syntax structure and style of SQL statements are quite simple and intuitive. As long as you practice more in combination, you will be able to quickly grasp it in the short term. In our daily work of using the SQL language, the most commonly used information is to query information from the database that has been established. Next, we will introduce in detail how to use the SQL language to perform various database query operations. Select... From For convenience, we create the following data table named store_information in the database. Store_information Store_name Sales Date Los Angeles 00 Jan-10-2000 San Diego 0 Jan-11-2000 Los Angeles 0 Jan-12-2000 Boston 0 Jan-12-2000 The simplest Command Used for database query in SQL is select... From, syntax format: Select "column_name" from "table_name" For example, if you want to query all store names in the store_information data table, you can use the following command: Select store_name from store_information The query result is displayed as follows: Store_name Los Angeles San Diego Los Angeles Boston If you want to query multiple fields at a time, you can add the names of the fields to be queried to the select keyword in sequence and separate them with commas. Distinct The Select keyword allows you to query all the data of a specified field in a data table. If you want to query only information with different record values, you can use the distinct keyword in SQL. The syntax format is as follows: Select distinct "column_name" From "table_name" For example, we can use the following command to query all records with different record values in the store_information data table. Select distinct store_name from store_information The query result is as follows: Store_name Los Angeles San Diego Boston Where In addition to selecting records with different record values, we may also need to query data in the database based on certain conditions. For example, we may need to query stores with sales of more than 1000 US dollars in the store_information data table. Therefore, you can use the where keyword of SQL to set query conditions. The syntax format is as follows: Select "column_name" From "table_name" Where "condition" Therefore, we can use the following command to query store information with sales exceeding 1000 US dollars: Select store_name from store_information where sales> 1000 The query result is displayed as follows: Store_name Los Angeles Operation Functions Now we know that you can set flexible query conditions by determining the value when using SQL for database queries. To enhance the support for operations, SQL provides many practical computing functions for the majority of users. For example, we can directly call the sum or AVG functions in the SQL command to calculate the total number and the average number respectively. The syntax format is as follows: Select "function type" ("column_name ") From "table_name" To query the total sales of all stores in the store_information table, run the following command: Select sum (sales) from store_information The query result is displayed as follows: Sum (sales) 50 Count In addition to the sum and AVG functions, the count function is another commonly used computing function in SQL. The count function can be used to calculate the number of records contained in a specified field in a data table. Syntax format: Select count ("column_name ") From "table_name" For example, to query the number of store records in the store_information data table, run the following command: Select count (store_name) From store_information The query result is displayed as follows: Count (store_name) 4 The count function can be used with the distinct keyword to query the number of records with different record values in the specified field in the data table. For example, if you want to query the number of different stores in the store_information data table, you can use the following command: Select count (distinct store_name) From store_information The query result is displayed as follows: Count (distinct store_name) 3 Group Next, let's take a look at the set functions in the SQL language. In the previous article, we used the sum function to calculate the total sales of all stores. What should we do if we want to calculate the total sales of each store? To achieve this goal, we need to do two things: first, we need to query two fields: store name and sales. Then, we use the SQL group by command to group sales by different stores and calculate the total sales of different stores. The syntax format of the Group by command is: Select "column_name1", sum ("column_name2 ") From "table_name" Group by "column_name1" You can use the following command to perform the preceding query: Select store_name, sum (sales) From store_information Group by store_name The query result is displayed as follows: Store_name sum (sales) Los Angeles 00 San Diego 0 Boston 0 Note: The group by keyword is generally used in SQL commands that query multiple fields at the same time and perform arithmetic operations on the fields. Having Another problem that users may want to solve when using the SQL language is to limit the output of the result calculated by sum or other set functions. For example, we may only want to see information about stores with a total sales volume of more than 1500 US dollars in the store_information table. Then we need to use the having clause. Syntax format: Select "column_name1", sum ("column_name2 ") From "table_name" Group by "column_name1" Having (arithematic function condition) (Group by clause is optional) Therefore, we can use the following command to achieve the above query purpose: Select store_name, sum (sales) From store_information Group by store_name Having sum (sales)> 1500 The query result is displayed as follows: Store_name sum (sales) Los Angeles 00 Note: Having clause instead of where clause is used to set query conditions for set functions in SQL. Generally, having clauses are placed at the end of an SQL command. Alias Next, we will focus on how to set aliases in SQL commands. In SQL, two types of aliases are generally used: Field aliases and data table aliases. Simply put, using field aliases can help us effectively organize the output results of queries. For example, in the multiple instances listed above, when we calculate the total sales volume of the store, sum (sales) is displayed in the result ). Although sum (sales) is not inconvenient for us to understand the query results, if we need to use multiple complex operations in the query, the results will not be so intuitive. If the field alias is used at this time, the readability of the query results will be greatly improved. For a data table alias, we can place the alias directly after the data table name in the from clause. Data Table aliases are extremely useful in connecting to multiple data tables for query. The syntax format for fields and data table aliases is as follows: Select "table_alias". "column_name1" "column_alias" From "table_name" "table_alias" That is, aliases are placed directly behind their respective names, separated by spaces. Taking the store_information data table as an example, we can set the following field and data table alias in the SQL command used in the group by section: Select a1.store _ name store, sum (sales) "total sales" From store_information A1 Group by a1.store _ name The query result is displayed as follows: Store total sales Los Angeles 00 San Diego 0 Boston 0 Connect multiple data tables Finally, let's take a look at how to connect multiple data tables using SQL to query multiple data tables. For convenience, we have created two data tables named store_information and region in the database. Store_information Store_name Sales Date Los Angeles 00 Jan-10-2000 San Diego 0 Jan-11-2000 Los Angeles 0 Jan-12-2000 Boston 0 Jan-12-2000 Region Region_name Store_name East Boston East New York West Los Angeles West San Diego Next, let's take a look at how to query sales by region through the connection of data tables. We noticed that the data table named region contains region and store fields, while the data table named store_information contains sales information for each store. Therefore, to obtain the sales information by region, we need to combine the information of two different data tables for query. Through the analysis of the above two data tables, we found that each data table contains a field named store_name. Therefore, we can use the following command to query: Select a1.region _ name region, sum (a2.sales) Sales From geography A1, store_information A2 Where a1.store _ name = a2.store _ name Group by a1.region _ name The query result is displayed as follows: Region Sales East 0 West 50 Note: The first two lines of the preceding query command are used to specify the target fields to be queried, which are the region_name field in the region data table and the total number of sales field records in the store_information data table. Here, we set the aliases of the two fields to region and sales respectively, and the aliases of the two data tables to A1 and A2 respectively. If we only use the field alias without setting the data table alias, the first line of the preceding SQL command will become the following form: Select region. region_name region, sum (store_information.sales) Sales From this we can see that the effective use of data table aliases can greatly simplify the SQL commands for operations on multiple data tables. the 3rd behavior where clause of the preceding query command sets the join conditions for two data tables. Because we want to ensure that the store_name field in the region data table can correspond to the Same Name field in the store_information data table, we stipulate that the record values of the two fields should be equal. When connecting multiple data tables, you must accurately set the connection conditions for the data tables. If the WHERE clause is incorrectly set, many irrelevant data may appear in the query results.