preface
In all the explanations below, I will explain in the form of basic grammar, cases and links, so as to strengthen the use and understanding of each sentence. I don't need to return the results in the way of mapping. It really takes up space. This article was written by the author after finishing it all night. I hope you will praise it three times in a row. Thank you. Of course, with this article, you will completely use all my mysql statements, and you will no longer need to buy or study disorderly. Due to the rich content of this time, I felt that writing articles in CSDN could be full of words for the first time, so I had no choice but to write several articles.
The most important command of MYSQL
SELECT Extract data from database UPDATE Update data in database DELETE Delete data from database INSERT INTO Insert new data into the database CREATE DATABASE Create a new database ALTER DATABASE modify the database CREATE TABLE Create a new table ALTER TABLE Modify table DROP TABLE Delete table CREATE INDEX Create index (search key) DROP INDEX Delete index
SELECT select statement
This SELECT statement is used to SELECT data from the database. The returned data is stored in a result table called a result set.
SELECT syntax:
SELECT column1, column2, ...
Here, column1, column2,... Are the field names of the table from which to select data. If you want to select all the available fields in the table, use the following syntax:
SELECT * FROM table_name;
Suppose we already have a database Customers as follows:
SELECT column example
The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:
SELECT CustomerName, City FROM Customers;
SELECT * example
The following SQL statement selects all columns from the Customers table:
SELECT * FROM Customers;
Exercise:
1 - get all the columns in the Customers table.
SELECT * FROM Customers;
2 - write a statement that City selects columns from the Customers table.
SELECT City FROM Customers;
3 - select all the different values from the Country column in the Customers table. (I'll talk about it later. It doesn't matter if you don't understand.)
SELECT DISTINCT Country FROM Customers;
SELECT DISTINCT select different statements
This SELECT DISTINCT statement is only used to return different (different) values. In a table, a column usually contains many duplicate values; sometimes you just want to list different (different) values.
SELECT DISTINCT syntax
SELECT DISTINCT column1, column2, ... FROM table_name;
Let's still assume that there is such a Customers database:
SELECT example without DISTINCT
The following SQL statement selects all (including duplicate) values from the Country column of the Customers table:
SELECT Country FROM Customers;
SELECT DISTINCT example
SELECT DISTINCT Country FROM Customers;
The following SQL statement lists the number of different (different) customer countries:
SELECT COUNT(DISTINCT Country) FROM Customers;
practice:
1 - select all the different values Customers from the Country column in the table.
SELECT DISTINC Country FROM Customers;
WHERE query in which clause
This WHERE clause is used to filter records. It is used to extract only records that meet the specified conditions.
WHERE syntax
SELECT column1, column2, ... FROM table_name WHERE condition;
Note: the WHERE clause is not only used in the SELECT statement, but also in UPDATE, DELETE, etc!
Let's assume that there is still a database such as Customers, as follows:
WHERE clause example
The following SQL statement selects all Customers from the country "Mexico" in the "Customers" table:
SELECT * FROM Customers WHERE Country='Mexico';
Text and number fields
SQL requires single quotation marks around text values (double quotation marks are also allowed in most database systems). However, numeric fields should not be contained in quotation marks:
SELECT * FROM Customers WHERE CustomerID=1;
Operator in WHERE clause
You can use the following operators in the WHERE clause:
We can demonstrate the use of these symbols one by one and learn patiently
Suppose we have a database called Product:
Select all products with price of 18 (=)
SELECT * FROM Products WHERE Price = 18;
Select all products with price greater than 30 (>)
SELECT * FROM Products WHERE Price > 30;
Select all products with price less than 30 (<)
SELECT * FROM Products WHERE Price < 30;
Select all products with price greater than or equal to 30 (> =)
SELECT * FROM Products WHERE Price >= 30;
Select all products with price less than or equal to 30 (< =)
SELECT * FROM Products WHERE Price <= 30;
Select all products whose price is not equal to 18 (equivalent to! =)
SELECT * FROM Products WHERE Price <> 18;
Select all products with prices between 50 and 60
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
Search all cities starting with the letter s from the Customers database above
SELECT * FROM Customers WHERE City LIKE 's%';
Find all users in Paris and London from the Customers database
SELECT * FROM Customers
WHERE City IN ('Paris','London');
The above is a demonstration of all symbols.
Let's do some more exercises to consolidate it (we use customers above)
1 - select all records whose City column value is "Berlin".
SELECT * FROM Customers WHERE City='Berlin';
2 - use the NOT keyword to select all records whose City is NOT "Berlin".
SELECT * FROM Customers WHERE NOT City ='Berlin';
3 - select all records whose CustomerID column value is 32.
SELECT * FROM Customers WHERE CustomerID =12;
AND, OR, AND NOT operators
The WHERE clause can combine AND, OR AND NOT operations. The AND and OR operations are used to filter records based on multiple criteria:
This AND operation displays a record, AND is true if all conditions are met.
The OR operation displays a record. If any condition is met, OR is true.
The NOT operation displays if the condition (S) is an incorrect record.
AND syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
NOT syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
Let's assume the following "Customers" table:
AND example
The following SQL statement selects all fields with country "Germany" and city "Berlin" from Customers:
SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
OR example
Select "Berlin" OR "M ü nchen" in all "Customers":
SELECT * FROM Customers WHERE City='Berlin' OR City='München';
NOT example
Select a field from 'Customers' where country is not' Germany ':
SELECT * FROM Customers WHERE NOT Country='Germany';
Combine AND, OR and NOT
Select all fields from "Customers" whose country is "Germany" and whose city must be "Berlin" OR "M ü nchen" (use parentheses to form a complex expression):
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
Select all fields from "Customers" where the country is neither "Germany" nor "USA":
SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
practice
Select all records with City column value of 'Berlin' and PostalCode column value of 12209.
SELECT * FROM Customers WHERE City = 'Berlin' AND PostalCode= 12209;
ORDER BY keyword
The ORDER BY keyword is used to sort the result set in ascending or descending order. ORDER BY by default, the keyword sorts records in ascending order. To sort records in descending order, use the DESC keyword.
ORDER BY syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Let's assume that the "Customers" table is as follows:
ORDER BY example
Select all Customers from the Customers table, sorted by the Country column:
SELECT * FROM Customers ORDER BY Country;
ORDER BY DESC example
Select all Customers from the "Customers" table, and sort by "Country" column and by DESCENDING:
SELECT * FROM Customers ORDER BY Country DESC;
ORDER BY multi column example
Select all Customers from the Customers table, sorted by the Country and CustomerName columns. This means that it is sorted by Country, but if some rows have the same Country, they are sorted by CustomerName:
SELECT * FROM Customers ORDER BY Country, CustomerName;
Select all Customers from the Customers table and sort them in ascending order of Country and descending order of CustomerName column:
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
practice
Select all records from the Customers table and sort the results alphabetically in the city column.
SELECT * FROM Customers ORDER BY City;
INSERT INTO insert statement
This INSERT INTO statement is used to insert a new record into the table.