Summary of common MYSQL commands

1. Connect to MYSQL: mysql-h host address-u user name-p User Password 1. Example 1: connect to MYSQL on the local machine first open the DOS window and then enter the directory mysqlbin, enter the mysql-uroot-p command and press enter to prompt you to enter the password. If you have just installed MYSQL, the Super User root does not have a password, so press enter to enter

1. Connect to MYSQL: mysql-h host address-u user name-p User Password 1. Example 1: connect to MYSQL on the local machine before opening the DOS window, enter the mysql/bin directory, type the mysql-u root-p command, and press enter to prompt you to enter the password. If you have just installed MYSQL, the Super User root has no password, press enter to enter

1. Connect to MYSQL

Format: mysql-h host address-u user name-p User Password

1. Example 1: connect to MYSQL on the local machine

First, open the DOS window, enter the directory mysql/bin, then type the command mysql-u root-p, and press enter to prompt you to enter the password. If you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>

2. Example 2: connect to MYSQL on the remote host

Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h 110.110.110.110-u root-p abcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. exit MYSQL command: exit (Press ENTER)

Ii. Change the password

Format: mysql admin-u username-p old password New password

1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command

Mysql admin-u root-password ab12

Note: because the root account does not have a password at the beginning, the old-p password can be omitted.

2. Example 2: Change the root password to djg345.

Mysqladmin-uroot-pab12 password djg345

Common commands

I. Operation Skills

1. If you forget the extra points after you press Enter when making the command, you don't have to repeat the command. You just need to press a semicolon to press Enter. That is to say, you can divide a complete command into several lines, and then use a semicolon as the end sign to complete the operation.

2. You can use the cursor to bring up or down the previous commands. However, an old MYSQL version I used earlier does not support this feature. I am using a mysql-3.23.27-beta-win.

Ii. Display commands

1. display the Database List.

Show databases;

At the beginning, there were only two databases: mysql and test. The mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations.

2. display the data tables in the database:

Use mysql; // open the database. If you have learned FOXBASE, you will not be unfamiliar with it.

Show tables;

3. display the data table structure:

Describe table name;

4. database creation:

Create database name;

5. Create a table:

Use Database Name;

Create table Name (field setting list );

6. Delete databases and tables:

Drop database name;

Drop table name;

7. Clear records in the table:

Delete from table name;

8. Display records in the table:

Select 'field name' from table name;

3. An instance for creating a database, creating a table, and inserting data

Drop database if exists school; // Delete if SCHOOL exists
Create database school; // create a database SCHOOL
Use school; // open the SCHOOL library
Create table teacher // create table TEACHER
(
Id int (3) auto_increment not null primary key,
Name char (10) not null,
Address varchar (50) default 'shenzhen ',
Year date
); // Table creation ends
// Insert fields as follows
Insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 ');
Insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23 ');

Note: In the table in progress (1), set the ID to a numeric field of 3: int (3) and make it automatically add one: auto_increment for each record. It cannot be blank: not null and set it to the primary key.
(2) Set NAME to a 10-character field
(3) set ADDRESS to a 50-character field, and the default value is Shenzhen. What is the difference between varchar and char? It will only be discussed later.
(4) set YEAR as the date field.

If you type the preceding command at the mysql prompt, debugging is not convenient. You can write the above commands as they are in a text file, assume it is school. SQL, then copy it to c: \, and enter \ mysql \ bin in the DOS state, and then type the following command:
Mysql-uroot-p password <c: \ school. SQL
If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. (The preceding command has been debugged. You only need to remove the // annotation to use it ).

4. Transfer text data to the database

1. Text data should conform to the format: field data is separated by the tab key, and the null value is replaced by \ n.
Example:
3 rose:
4 mike: Shenzhen No. 1,-12-23

2. data Import command load data local infile "file name" into table name;
Note: You 'd better copy the file to the \ mysql \ bin directory and use the use command to create the database where the table is located.

V. Back up the database: (execute the command in the \ mysql \ bin directory of DOS)

Mysql dump -- opt school> school. bbb
Note: Back up the database school to the school. bbb file. school. bbb is a text file with any file name. Open it and you will find new discoveries.

1. The complete syntax of the SELECT statement is:
SELECT [ALL | DISTINCT | DISTINCTROW | TOP]
{* | Talbe. * | [table.] field1 [AS alias1] [, [table.] field2 [AS alias2] [,…]}
FROM tableexpression [,…] [IN externaldatabase]
[WHERE…]
[Group by…]
[HAVING…]
[Order by…]
[With owneraccess option]
Note:
The Section enclosed by braces ([]) is optional. The section enclosed by braces ({}) indicates that one of them must be selected.
1 FROM clause
The FROM clause specifies the field source in the SELECT statement. The FROM clause is followed by one or more expressions (separated by commas ), the expression can be a single table name, saved query, or compound result obtained by inner join, left join, or right join. If a table or query is stored IN an external database, specify its complete path after the IN clause.
For example, the following SQL statement returns all customers with orders:
SELECT OrderID, Customer. mermerid
FROM Orders MERs
WHERE Orders. CustomerID = Customers. CustomeersID

2 ALL, DISTINCT, DISTINCTROW, and TOP predicates
(1) ALL returns ALL records that meet the SQL statement conditions. If this predicate is not specified, the default value is ALL.
Example: select all FirstName, LastName
FROM Employees
(2) DISTINCT if the selected fields of multiple records have the same data, only one is returned.
(3) If DISTINCTROW has repeated records, only one
(4) TOP displays several records at the beginning and end of the query. You can also return the percentage of records. This is to use the top n percent clause (where N represents the percentage)
For example, the maximum order amount of 5% is returned.
Select top 5 PERCENT *
FROM [Order Details]
Order by UnitPrice * Quantity * (1-Discount) DESC

3. Use the AS clause to get the alias for the Field
If you want to get a new title for the returned column, or after calculating or summarizing the field, a new value is generated and you want to display it in a new column, it is retained with.
For example, if the returned FirstName field is named nick name
SELECT FirstName AS NickName, LastName, City
FROM Employees
For example, return a new column to show the inventory value.
SELECT ProductName, UnitPrice, UnitsInStock, UnitPrice * UnitsInStock AS valueInStock
FROM Products

II. The WHERE clause specifies the query conditions.

1 comparison operator
Comparison operator meaning
= Equal
> Greater
<Less
> = Greater than or equal
<= Less than or equal
<> Not equal
!> Not greater
! <Not less
For example, return the order from January 1, January.
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate> #1/1/96 # AND OrderDate <#1/30/96 #
Note:
In Mcirosoft jet SQL, dates are bounded. The date can also be replaced by the Datevalue () function. When comparing character data, you must add single quotation marks ''. Trailing spaces are ignored during comparison.
Example:
WHERE OrderDate> #96-1-1 #
It can also be expressed:
WHERE OrderDate> Datevalue ('2014/1/96 ')
Use the NOT expression to reverse the request.
For example, view the orders placed after January 1, January 1.
WHERE Not OrderDate <=# 1/1/96 #
Range 2 (BETWEEN and not)
BETWEEN... AND... The operator specifies a closed interval to be searched.
For example, an order from January 1, January to January 31, February is returned.
WHERE OrderDate Between #1/1/96 # And #2/1/96 #
3 list (IN, not in)
The IN operator is used to match any value IN the list. An IN clause can replace a series of conditions connected by an OR clause.
For example, you need to find all customers who live in London, Paris, or Berlin.
SELECT CustomerID, CompanyName, ContactName, City
FROM MERs
WHERE City In ('London ', 'Paris', 'berlin ')
4. pattern matching (LIKE)
The LIKE operator checks whether the value of a field containing string data matches a specified pattern.
Wildcard characters used in the LIKE Operator
Wildcard characters
? Any single character
* Any length of Characters
#0 ~ A single number between 9
[Character List] any value in the Character List
[! Character List] any value not in the Character List
-Specify the character range. The upper and lower limits are the values on both sides.
Example: return the customer whose zip code is between (171) 555-0000 and (171) 555-9999.
SELECT CustomerID, CompanyName, City, Phone
FROM MERs
WHERE Phone Like '( 171) 555 -####'
Style and meaning of the LIKE Operator
Invalid style meaning
LIKE 'a * 'a followed by any length of characters Bc, c255
LIKE '5 [*] '5*5 555
LIKE '5? Any character between 5 and 5 is 55, 5wer5
LIKE '5 # 5' 5235,5005 5kd5, 5346
LIKE '[a-z]' any character between a-z 5, %
LIKE '[! 0-9] 'any character other than 0-9, 0, 1
LIKE '[[] '1 ,*
3. Sort the results using the order by clause
The ORDER clause sorts the query results by one or more (up to 16) fields, either ascending or descending. The default value is ascending. The ORDER clause is usually placed at the end of an SQL statement.
Multiple fields are defined in the ORDER clause.
Example:
SELECT ProductName, UnitPrice, UnitInStock
FROM Products
Order by UnitInStock DESC, UnitPrice DESC, ProductName
In the order by clause, you can replace the field name with the position number in the field selection list. You can mix the field name and position number.
For example, the following statement produces the same effect as the preceding one.
SELECT ProductName, UnitPrice, UnitInStock
FROM Products
Order by 1 DESC, 2 DESC, 3
4. Multi-Table query using connection relationships
Example: Find the names of suppliers and customers in the same city
SELECT Customers. CompanyName, Suppliers. ComPany. Name
FROM MERs, Suppliers
WHERE Customers. City = Suppliers. City
For example, find out the products and orders with the product inventory greater than the order quantity of the same product.
SELECT ProductName, OrderID, UnitInStock, Quantity
FROM Products, [Order Deails]
WHERE Product. productID = [Order Details]. ProductID
AND UnitsInStock> Quantity
Another method is to use the jnner join exclusive to Microsof jet SQL.
Syntax:
FROM table1 inner join table2
ON table1.field1 comparision table2.field2
Comparision is the comparison operator used by the WHERE clause.
SELECT FirstName, lastName, OrderID, CustomerID, OrderDate
FROM Employees
Inner join Orders ON Employees. EmployeeID = Orders. EmployeeID
Note:
Inner join cannot be used to connect Memo OLE Object Single Double data type fields.
JOIN multiple ON clauses in a JOIN statement
Syntax:
SELECT fields
FROM table1 inner join table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2 OR
ON table1.field3 compopr table2.field3
Yes.
SELECT fields
FROM table1 INNER JOIN
(Table2 inner join [(] table3
[Inner joer] [(] tablex [inner join]
ON table1.field1 compopr table2.field1
ON table1.field2 compopr table2.field2
ON table1.field3 compopr table2.field3
External connections return more records and keep unmatched records in the results. No matter there are no records that meet the conditions, all records on the other side are returned.
FROM table [LEFT | RIGHT] JOIN table2
ON table1.field1comparision table. field2
Use the left join to create an external join. All the data in the table on the left of the expression is displayed.
For example, all products are returned regardless of the order quantity.
SELECT ProductName, OrderID
FROM Products
Left join Orders ON Products. PrductsID = Orders. ProductID
The difference between the right join and the left join is that, no matter whether there are matching records in the left table, it returns all records from the left table.
For example, if you want to know the customer information and calculate the customer distribution in each region, you can use a right connection to return the customer information even if there are no customers in a region.
NULL values do not match each other. You can use an external connection to test whether the fields in a joined table have null values.
SELECT *
FROM talbe1
Left join table2 ON table1.a = table2.c
1. Use the Iif function in connection query to display null values with 0 values
Iif expression: Iif (IsNull (Amount, 0, Amout)
For example, whether the order is greater than or less than ￥50, a flag is returned.
Iif ([Amount]> 50 ,? Big order ?,? Small order ?)
5. Grouping and summarizing query results
In SQL syntax, the GROUP BY and HAVING clauses are used to summarize data. The group by clause specifies which fields are grouped. After grouping records, the HAVING clause is used to filter these records.
Group by clause syntax
SELECT fidldlist
FROM table
WHERE criteria
[Group by groupfieldlist [HAVING groupcriteria]
Note: Microsoft Jet Database cannot group remarks or OLE object fields.
The Null Value in the group by field is used for grouping, but cannot be ignored.
No Null value is calculated in any SQL aggregate function.
The group by clause can contain up to ten fields, and the sorting priority is arranged from left to right.
For example, After grouping by title in the employee table of the 'wa 'region, find all titles with the same title with more than 1 employee.
SELECT Title, Count (Title) as Total
FROM Employees
WHERE Region = 'wa'
Group by Title
HAVING Count (Title)> 1
Accumulation functions in JET SQL
Significance of Aggregate functions
SUM () summation
AVG () Average Value
Number of records in the COUNT () Expression
COUNT (*) calculates the number of records
MAX
MIN minimum
VAR variance
STDEV standard error
FIRST Value
LAST Value
6. Use Parameters to declare and create a parameter query
Syntax declared by Parameters:
PARAMETERS name datatype [, name datatype [,…]
Here, name is the identifier of the parameter. You can use the identifier to reference the parameter.
Datatype indicates the Data Type of the parameter.
You must place the PARAMETERS declaration before any other statements.
Example:
PARAMETERS [Low price] Currency, [Beginning date] datatime
SELECT OrderID, OrderAmount
FROM Orders
WHERE OrderAMount> [low price]
AND OrderDate> = [Beginning date]
VII. Function Query
The so-called function query is actually an operation query, which can perform fast and efficient operations on the database. it selects matching data for the purpose of selecting a query and then processes the data in batches. function query includes update query, delete query, add query, and generate Table query.
1. Update Query
The UPDATE clause can change the data in one or more tables at the same time. It can also change the values of multiple fields at the same time.
Update query syntax:
UPDATE table name
SET new value
WHERE Criterion
For example, the order volume of UK customers increased by 5% and the cargo volume increased by 3%.
UPDATE OEDERS
SET OrderAmount = OrderAmount * 1.1
Freight = Freight * 1.03
WHERE ShipCountry = 'U'
2. Delete Query
The DELETE clause allows you to DELETE a large amount of outdated or redundant data.
Note: The query object is deleted as a whole record.
Syntax of the DELETE clause:
DELETE [Table name. *]
FROM source table
WHERE Criterion
Example: delete all Orders 94 years ago
DELETE *
FROM Orders
WHERE OrderData <#94-1-1 #
3 append Query
The INSERT clause can append one or more records to the end of one or more tables.
The INTO clause specifies the table that accepts the new record.
The valueS keyword specifies the data value included in the new record.
Syntax of the INSERT clause:
Insetr into target table or query (Field 1, Field 2 ,...)
ValueS (value 1, value 2 ,...)
Example: Add a customer
Insert into Employees (FirstName, LastName, title)
ValueS ('Harry ', 'Washington', 'trainee ')
4. Generate a table Query
All matching records can be copied to a new table at a time. A backup or copy of the records is usually created or serves as the basis of the report.
The select into clause is used to create the query syntax for the generated table:
SELECT Field 1, Field 2 ,...
INTO new table [IN external database]
FROM source database
WHERE Criterion
For example, create an archive backup for the order
SELECT *
INTO OrdersArchive
FROM Orders
8. Joint Query
The UNION operation combines the results of multiple queries into a single result set for display.
General Syntax of UNION operations:
[Table] query 1 UNION [ALL] query 2 UNION...
Example: return the names and cities of all suppliers and customers in Brazil
SELECT CompanyName, City
FROM Suppliers
WHERE Country = 'Brazil'
UNION
SELECT CompanyName, City
FROM MERs
WHERE Country = 'Brazil'
Note:
By default, the UNION clause does not return repeated records. to display ALL records, add the ALL option.
The UNION operation requires that the query has the same number of fields. However, the field data types do not have to be the same.
You can use the group by clause or HAVING clause to GROUP each query parameter. to display the returned data in the specified order, you can use the oreer by clause at the end of the last query.
9. Cross Query
Cross-query can calculate the sum, average, Count, or other sum of data. The data is grouped by two types of information: one is displayed on the left of the table, the other is displayed on the top of the table.
Microsoft Jet SQL uses the TRANSFROM statement to create the cross tabulation query syntax:
TRANSFORM aggfunction
SELECT statement
Group by clause
Struct tfield [IN (value1 [, value2 [,…]) ]
Aggfounction refers to the SQL accumulation function,
SELECT the field as the title in the SELECT statement,
GROUP BY GROUP
Note:
The field or expression used to create a column title IN the query result set. The optional IN clause is used to limit its value.
Value indicates the fixed value of the created column title.
For example, the number of orders received by each employee in each quarter of 1996 is displayed:
TRANSFORM Count (OrderID)
SELECT FirstName & ''& LastName AS FullName
FROM Employees inner join Orders
ON Employees. EmployeeID = Orders. EmployeeID
WHERE DatePart ("yyyy", OrderDate) = '201312'
Group by FirstName & ''& LastName
Order by FirstName & ''& LastName
POVOT DatePart ("q", OrderDate) & 'quarterly'
10. subquery
A subquery can be understood as a set of queries. A subquery is a SELECT statement.
1. Compare the expression value with the single value returned by the subquery.
Syntax:
Expression comparision [ANY | ALL | SOME] (subquery)
Note:
Any and some predicates are synonyms and are used together with comparison operators (=, <, >,< =, >=. returns a Boolean value of True or False. ANY means that the expression is compared with a series of values returned by the subquery one by one. If a comparison produces a True result, ANY tests return a True value (both the result of the WHERE clause ), the current record corresponding to this expression will be included in the results of the primary query. the ALL test requires that the expression and a series of values returned by the subquery produce the True result before returning the True value.
For example, the unit price returned by the primary query is higher than the unit price of any product whose discount is greater than or equal to 25%.
SELECT * FROM Products
WHERE UnitPrice> ANY
(SELECT UnitPrice FROM [Order Details] WHERE Discount> 0.25)

2. Check whether the expression value matches a value of a group returned by the subquery.
Syntax:
[NOT] IN (subquery)
For example, products whose inventory value is greater than or equal to 1000 are returned.
SELECT ProductName FROM Products
WHERE ProductID IN
(SELECT PrdoctID FROM [Order DEtails]
WHERE UnitPrice * Quantity & gt; = 1000)

3. Check whether the subquery returns any records.
Syntax:
[NOT] EXISTS (subquery)
Example: Search UK customers with EXISTS
SELECT ComPanyName, ContactName
FROM Orders
WHERE EXISTS
(SELECT *
FROM MERs
WHERE Country = 'uk 'AND
Customers. CustomerID = Orders. CustomerID)