sql-Basic Learning 1--select,limit,distinct, notes

1.1 Basic Concepts1. Databases (database) a container that holds organized data (usually a file or a set of files) Note: Common MySQL, such as database management system DBMS; The container created and manipulated by these software is the database; 2. Table table is a structured file, can be used to store a specific type of data, you can save the customer list, product catalogs and other information; it's like, put something in a drawer, not just throw it in a drawer, but put some folders in the drawer, put the relevant things in the relevant folder; The drawer is a database, a container, and a folder is Tables are used to store specific types of data; Name: The name of the table should be unique in a database; schemas: information about the layout and characteristics of databases and tables. For example: Defines how the data is stored in the table, what data is stored, how the data is decomposed, how the information is named, and so on; 3. Columns (column) and data type tables are composed of columns. Column stores information about a portion of a table. Think of a table as a spreadsheet in which each column in the grid stores a specific kind of information. For example, in a Customer table, one column stores the customer number, the other stores the customer's name, and the address, city, state, and zip code are all stored in their respective columns. The type of data allowed by the data type. Each table column has a corresponding data type that restricts (or allows) the data stored in the column 4. A record in the row table. The data in the table is stored on a row, and each saved record is stored in its own row. If you think of a table as a grid, the grid is a vertical column of columns, and the horizontal behavior is the table row. 5. The primary key is a column (or set of columns) whose value uniquely identifies each row in the table. Each row in the table should have a column (or columns) to uniquely identify itself. The Customer table can use the customer number, and the order form can use the order ID. An employee table can use an employee ID or an employee Social Security number. The primary key satisfies the condition:

• No two rows have the same primary key value
• Each row must have a primary key value (the primary key column does not allow null values)
• Values in primary key columns are not allowed to be modified or updated
• Primary key values cannot be reused (if a row is deleted from a table, its primary key value cannot be assigned to a new row later)

  1.2 sql-structured Query LanguageTask-Provides a simple and efficient way to read and write data from a database. Advantages:

• SQL is not a proprietary language for a particular database vendor. Almost all of the important DBMS support SQL;
• SQL is easy to learn. Its statements are all composed of very strong descriptive English words, and the number of these words is not many.
• SQL, while seemingly simple, is actually a powerful language with the flexibility to use its language elements for very complex and advanced database operations.

Lesson 2nd retrieving data 2.1 SELECT statementThe purpose is to retrieve information from one or more tables. Retrieving a column

Input: SELECT  from Products ; output: Prod_name -- ----------------- Fish bean bag toybird bean bag toyrabbit bean bag toy 8 inch Teddy Bear  A inch Teddy Bear  - inch Teddy Bearraggedy annking Dollqueen Doll

Analysis: The above statement uses the SELECT statement to retrieve a column named Prod_name from the Products table. The required column names are written after the Select keyword, and the FROM keyword indicates which table to retrieve the data from. Note: The retrieved data is unordered because no query results are explicitly sorted; Multiple SQL statements are separated by semicolons, and all spaces are ignored when the SQL statement is processed, including carriage returns; SQL statement retrieves multiple columns with only a semicolon end

Input:SELECTprod_id, Prod_name, Prod_price fromProducts ; output: prod_id prod_name prod_price---------     --------------------     ----------BNBG01 Fish Bean Bag Toy3.4900BNBG02 Bird bean bag Toy3.4900BNBG03 Rabbit bean bag Toy3.4900BR018Inch Teddy Bear5.9900BR02 AInch Teddy Bear8.9900BR03 -Inch Teddy Bear11.9900RGAN01 raggedy Ann4.9900RYL01 King Doll9.4900RYL02 Queen Dool9.4900

Retrieving all Columns

SELECT *  from Products;

Warning: Using wildcard characters
In general, it is best not to use the * wildcard character unless you really need each column in the table. Although using wildcards can save you the hassle of not explicitly listing the required columns, retrieving the columns you don't need often reduces the performance of your search and applications.
Tip: Retrieving unknown columns using wildcards has a big advantage. Columns with unknown names can be retrieved because the column names are not explicitly specified (because asterisks retrieve each column). 2.2 Retrieving different valuesthat shows only the different values, the same only once;Keywords: distinct such as:

Input: SELECT vend_id  from Products ; output: vend_id -- -------- brs01brs01brs01dll01dll01dll01dll01fng01fng01

and

Input:SELECT DISTINCTvend_id fromProducts ; output:+---------+|vend_id|+---------+|BRS01||DLL01||FNG01|+---------+3Rowsinch Set(0.00Sec

Analysis: SELECT DISTINCT vend_id tells the DBMS to return only the different (unique) vend_id rows, so just like the following output, there are only 3 rows. If you use the DISTINCT keyword, it must be placed directly in front of the column name. Note: You cannot partially use the DISTINCTDISTINCT keyword for all columns, not just the one that follows. For example, you specify select DISTINCT vend_id, Prod_price, unless the two specified columns are identical, all rows will be retrieved. 2.3 Limiting resultsThe SELECT statement returns all matching rows in the specified table, most likely each row. What if you want to return only the first row or a certain number of rows? This is possible, but unlike the various DBMS in MySQL, MariaDB, PostgreSQL, or sqlite, you need to use the limit clause, like this:

Input: SELECT Prod_name  from  5; output: Prod_name-----------------8  inch Teddy Bear  A inch Teddy Bear  - inch Teddy Bearfish bean bag Toybird bean bag toy

Parsing the preceding code uses the SELECT statement to retrieve a single column of data.  LIMIT 5 Instructs the DBMS, such as MySQL, to return no more than 5 rows of data. If you want data from the next 5 rows, like this:

Input: SELECT Prod_name  from  55; output: Prod_name-------------------Rabbit bean bag Toyraggedy annking Dollqueen Doll

Analysis LIMIT 5 OFFSET 5 instructs the DBMS such as MySQL to return 5 rows of data from line 5th. The first number refers to where to start, and the second number is the number of rows retrieved. Therefore, the limit specifies the number of rows returned. The limit with offset specifies where to start. Note: The first row in line No. 0 is the No. 0 row, not the 1th row. Therefore, the LIMIT 1 OFFSET 1 retrieves the 2nd row instead of the 1th line. Tip: MySQL and mariadb shortcut keys MySQL and mariadb support the simplified version of the limit 4 OFFSET 3 statement, which is limit 3, 4. With this syntax, the previous value corresponds to limit, and then the value corresponds to offset. 2.4 Using Annotations

SELECT prod_name    --  This is a note from the  products;

Parsing annotations are nested within a row using-(two hyphens). The text that follows is a comment, for example, which is good for describing the columns in the CREATE TABLE statement.

Multi-line comments /*  */SELECT  prod_name from Products;

Comments start with/* and any content between/* and/* is commented. This approach is often used to annotate code, as shown in this example, where two SELECT statements are defined, but the first one is not executed because it has been commented out.

sql-Basic Learning 1--select,limit,distinct, notes