算法竞赛的挑战，总结实现_Python_JavaScript_下载.zip

SQL (Structured Query Language) ====== **SQL** is used to access and manipulate a **database**. **MySQL** is a program that understands **SQL**. SQL can: - insert, update, or delete records in a database. - create new databases, table, stored procedures, views. - retrieve data from a database, etc. SQL is an ANSI (American National Standards Institute) standard, but there are different versions of the SQL language. Most SQL database programs have their own proprietary extensions in addition to the SQL standard, but all of them support the major commands. ## Concept A **database** is a collection of data that is organized in a manner that facilitates ease of access, as well as efficient management and updating. A database is made up of **tables** that store relevant information. A table stores and displays data in a structured format consisting of **columns** and **rows** that are similar to those seen in Excel spreadsheets. ### Primary Key A primary key is a field in the table that uniquely identifies the table records. The primary key's main features: - It must contain a **unique value** for each row. - It cannot contain `NULL` values. Note that, - Tables are limited to **ONE** primary key each. - The primary key's value must be different for each row. ## Syntax ### Logical Operators Note that, to compare with a text value, you need to surround the text that appears in the statement with **single quotation marks (')**. | Operators | Notes | | ----------------- | ------------------------------------------------------------ | | `=` | Equal | | `!=` | Not equal | | `>` | Greater than | | `<` | Less than | | `>=` | Greater than or equal | | `<=` | Less than or equal | | `BETWEEN a AND b` | Between an inclusive range, that is, `a <= x <= b` | | `AND` | True if **both** expressions are True | | `OR` | True if **either** expressions is True | | `a IN (b, c, d)` | True if the operand is equal to one of a list of expressions | | `NOT` | Returns True if expression is not True | | `a LIKE pattern` | SQL **pattern** matching enables you to use `_` to match any single character and `%` to match an arbitrary number of characters (including zero characters). | ### Arithmetic Operators | Operators | Notes | | --------- | -------------- | | `+` | addition | | `-` | subtraction | | `*` | multiplication | | `/` | division | ### Data Types | Type | Category | Notes | | -------------- | ------------- | ------------------------------------------------------------ | | `int` | Numeric | A normal-sized integer that can be signed or unsigned. | | `float(n, d)` | Numeric | A floating-point number that cannot be unsigned. You can optionally define the display length (`n`) and the number of decimals (`d`). | | `double(n, d)` | Numeric | A double precision floating-point number that cannot be unsigned. You can optionally define the display length (`n`) and the number of decimals (`d`). | | `date` | Date and Time | A date in `YYYY-MM-DD` format. | | `datetime` | Date and Time | A date and time combination in `YYYY-MM-DD HH:MM:SS` format. | | `time` | Date and Time | A time in `HH:MM:SS` format. | | `timestamp` | Date and Time | A timestamp, calculated from midnight, January 1, 1970. | | `char(n)` | String | Fixed-length (`n`) character string. Size is specified in parenthesis. Max 255 bytes. | | `varchar(n)` | String | Variable-length(`n`)character string. Max size is specified in parenthesis. | | `blob` | String | **Binary Large Objects** and are used to store large amounts of binary data, such as images or other types of files. | | `text` | String | Large amount of text data. | ### Multiple Queries SQL allows to run multiple queries or commands at the same time. Remember to end each SQL statement with a **semicolon** to indicate that the statement is complete and ready to be interpreted. In this tutorial, we will use **semicolon** at the end of each SQL statement. ```sql SELECT FirstName FROM customers; SELECT City FROM customers; ``` ### Case Sensitivity SQL is case **insensitive**. It is common practice to write all SQL commands in **upper-case**. ### Separator A single SQL statement can be placed on one or more text lines. In addition, multiple SQL statements can be combined on a single text line. White spaces and multiple lines are ignored in SQL. However, it is recommended to avoid unnecessary white spaces and lines. Combined with proper spacing and indenting, breaking up the commands into logical lines will make your SQL statements much easier to read and maintain. ## Command ### Database | Commands | Notes | | ---------------- | ----------------------------------------- | | `SHOW DATABASES` | list the databases managed by the server. | ### Table #### SQL constraints - `NOT NULL` - Indicates that a column cannot contain any NULL value. - `UNIQUE` - Does not allow to insert a duplicate value in a column. The `UNIQUE` constraint maintains the uniqueness of a column in a table. More than one `UNIQUE` column can be used in a table. - `AUTO_INCREMENT` - Auto-increment allows a unique number to be generated when a new record is inserted into a table. By default, the starting value for `AUTO_INCREMENT` is 1, and it will increment by 1 for each new record. - `PRIMARY KEY` - Enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster. - `CHECK` - Determines whether the value is valid or not from a logical expression. - `DEFAULT` - While inserting data into a table, if no value is supplied to a column, then the column gets the value set as `DEFAULT`. #### Views In SQL, a VIEW is a **virtual table** that is based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view always shows up-to-date data! The database engine uses the view's SQL statement to recreate the data each time a user queries a view. Views allow us to: - Structure data in a way that users or classes of users find natural or intuitive. - Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. - Summarize data from various tables and use it to generate reports. | Commands | Notes | | ------------------------------------------------------------ | ------------------------------------------------------------ | | `SHOW TABLES` | display all of the tables in the currently selected MySQL database. | | `CREATE TABLE Users ( UserID int NOT NULL AUTO_INCREMENT, FirstName varchar(40) NOT NULL, LastName varchar(40) NOT NULL, PRIMARY KEY(UserID) )` | Note the **parentheses** in the syntax. When inserting a new record into the Users table, it's not necessary to specify a value for the `UserID` column; a unique new value will be added automatically. | | `DROP TABLE table`