Common MySQL operations and commands on Windows

MySQL common operations and commands on Windows platforms. For more information about mysql, see.

MySQL common operations and commands on Windows platforms. For more information about mysql, see.

1. Export the entire database
Mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1)
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement
4. Import the database
A: common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source wcnc_db. SQL

B: Use the mysqldump command.

Mysqldump-u username-p dbname <filename. SQL

C: Use the mysql Command

Mysql-u username-p-D dbname <filename. SQL

1. Start and exit
1. Go to MySQL: Start MySQL Command Line Client (MySQL DOS Interface) and enter the password for installation. The prompt is: mysql>
2. exit MySQL: quit or exit
Ii. Database Operations
1. Create a database
Command: create database <数据库名>
For example, create a database named xhkdb.
Mysql> create database xhkdb;
2. display all databases
Command: show databases (Note: The last s is available)
Mysql> show databases;
3. delete a database
Command: drop database <数据库名>
For example, delete a database named xhkdb.
Mysql> drop database xhkdb;
4. Connect to the database
Command: use <数据库名>
For example, if the xhkdb database exists, try to access it:
Mysql> use xhkdb;
On-screen prompt: Database changed
5. view the currently used database
Mysql> select database ();

6. Table information contained in the current database:
Mysql> show tables; (Note: There is a last s)

Iii. Table operations. A database should be connected before operations
1. Create a table
Command: create table <表名> ( <字段名1> <类型1> [,.. <字段名n> <类型n> ]);

Mysql> create table MyClass (
> Id int (4) not null primary key auto_increment,
> Name char (20) not null,
> Sex int (4) not null default '0 ',
> Degree double (16, 2 ));
2. Get the table structure
Command: desc table name or show columns from Table Name
Mysql> DESCRIBE MyClass
Mysql> desc MyClass;
Mysql> show columns from MyClass;
3. delete a table
Command: drop table <表名>
For example, delete a table named MyClass.
Mysql> drop table MyClass;
4. insert data
Command: insert <表名> [( <字段名1> [,.. <字段名n> ])] Values (value 1) [, (value n)]
For example, insert two records into the MyClass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.
Mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );
5. query the data in the table
1) query all rows
Command: select <字段1，字段2，...> From <Table Name> where <expression>
For example, you can view all data in the MyClass table.
Mysql> select * from MyClass;
2) query the first few rows of data
For example, view the first two rows of data in the MyClass table.
Mysql> select * from MyClass order by id limit 0, 2;

Or:

Mysql> select * from MyClass limit 0, 2;
6. Delete table data
Command: delete from table name where expression
For example, delete the record numbered 1 in MyClass.
Mysql> delete from MyClass where id = 1;
7. Modify Table data: update table name set field = new value ,... Where condition
Mysql> update MyClass set name = 'Mary 'where id = 1;
7. Add fields to the table:

Command: alter table name, add, other field types;
For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.
Mysql> alter table MyClass add passtest int (4) default '0'
8. Change the table name:
Command: rename table original table name to new table name;
For example, the MyClass name in the table is changed to YouClass.
Mysql> rename table MyClass to YouClass;

Update field content
Update table name set field name = new content
Update table name set field name = replace (field name, 'old content', 'new content ');

Add four spaces before the article
Update article set content = concat ('', content );

Field Type
1. INT [(M)] type: normal Integer type
2. DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type
3. DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to assign values to the DATE column using strings or numbers
4. CHAR (M) type: fixed-length string type. When stored, it always fills the Right to the specified length with spaces
5. blob text type, with a maximum length of 65535 (2 ^ 16-1) characters.
6. VARCHAR: variable-length string type

5. Import database tables
(1) create a. SQL File
(2) first generate a database such as auction. c: mysqlbin> mysqladmin-u root-p creat auction. A prompt is displayed, indicating that the password is entered and created successfully.
(2) import the auction. SQL File
C: mysqlbin> mysql-u root-p auction <auction. SQL.
With the preceding operations, you can create a database auction and a table auction.
6. Modify the database
(1) Add fields to the mysql table:
Alter table dbname add column userid int (11) not null primary key auto_increment;
In this way, a field userid is added to the dbname table and the type is int (11 ).
7. mysql database authorization
Mysql> grant select, insert, delete, create, drop
On *. * (or test. */user .*/..)
To username @ localhost
Identified by 'Password ';
For example, to create a new user account for database access, perform the following operations:
Mysql> grant usage
-> ON test .*
-> TO testuser @ localhost;
Query OK, 0 rows affected (0.15 sec)
Then a new user named testuser is created. This user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that the user testuser can perform:
Mysql> GRANT select, insert, delete, update
-> ON test .*
-> TO testuser @ localhost;
Query OK, 0 rows affected (0.00 sec)
This operation enables testuser to perform SELECT, INSERT, DELETE, and UPDATE queries on the tables in each test database. Now we end the operation and exit the MySQL client program:
Mysql> exit
Bye9!

1: Use the SHOW statement to find out the current database on the server:
Mysql> show databases;
2. Create a database named MYSQLDATA
Mysql> Create database mysqldata;
3: select the database you created
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
4: view the tables in the current database
Mysql> show tables;
5. Create a database table
Mysql> Create table mytable (name VARCHAR (20), sex CHAR (1 ));
6: display the table structure:
Mysql> describe mytable;
7. Add records to the table
Mysql> insert into MYTABLE values ("hyq", "M ");
8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> load data local infile "D:/mysql.txt" into table mytable;
9: import the. SQL FILE command (for example, D:/mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;
10: delete a table
Mysql> drop table mytable;
11: Clear the table
Mysql> delete from MYTABLE;
12: Update table data
Mysql> update MYTABLE set sex = "f" where name = 'hyq ';

The following are the management experiences of using MySql on the Internet,
From: http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html

In windows, MySql exists as a service. Before using mysql, make sure that the service has been started and the net start MySql command is not enabled. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator must have administrator privileges.
The newly installed MySql contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run the following command:
Use mysql;
Delete from User where User = "";
Update User set Password = PASSWORD ('newpassword') where User = 'root ';
If you want to restrict the logon terminal used by the User, you can update the Host field of the corresponding User in the User table. After making the above changes, restart the database service, at this time, the following commands can be used for Logon:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
Mysql mydb-uroot-p;
Mysql mydb-uroot-pnewpassword;
The preceding command parameters are part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.
In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySql User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the GRANT command to create a user with certain permissions. The common usage of GRANT is as follows:
Grant all on mydb. * to NewUserName @ HostName identified by "password ";
Grant usage on *. * to NewUserName @ HostName identified by "password ";
Grant select, insert, update on mydb. * to NewUserName @ HostName identified by "password ";
Grant update, delete on mydb. TestTable to NewUserName @ HostName identified by "password ";
To GRANT the user the ability to manage permissions on the corresponding object, you can add the with grant option after GRANT. For users inserted into the User table, use the Password function to update and encrypt the PASSWORD field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To REVOKE permissions, you can update the corresponding fields in the User table or use the REVOKE operation.
The following is my interpretation of common permissions from other information (www.cn-java.com:
Global Management permissions:
FILE: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
SHUTDOWN: Shut down the MySQL service.
Database/data table/data column permissions:
Alter: Modify existing data tables (such as adding/deleting columns) and indexes.
Create: Create a new database or data table.
Delete: Delete table records.
Drop: delete a data table or database.
INDEX: Create or delete an INDEX.
Insert: Add Table records.
Select: displays/searches for table records.
Update: Modify existing records in the table.
Special permissions:
ALL: allow anything (same as root ).
USAGE: Only logon is allowed. Other operations are not allowed.

---------------------
MYSQL Common commands
Many friends have installed mysql but do not know how to use it. In this article, we will learn some common MYSQL commands from the aspects of connecting to MYSQL, changing the password, and adding users.
Many friends have installed mysql but do not know how to use it. In this article, we will learn some common MYSQL commands from the aspects of connecting to MYSQL, changing the password, and adding users.

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 mysqlbin, then type the command mysql-uroot-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-h110.110.110.110-uroot-pabcd123

(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: mysqladmin-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

Mysqladmin-uroot-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

MYSQL Common commands (below)
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 * 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)

Mysqldump -- 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 performs data. 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)