Mysql common operation and command _mysql under Windows platform

Source: Internet
Author: User
Tags mysql client mysql commands ole create database import database mysql command line crosstab
1. Export the entire database

Mysqldump-u user name-p--default-character-set=latin1 Database name > exported file name (database default encoding is latin1)

Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name

Mysqldump-u wcnc-p SMGP_APPS_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 Database

A: Common source commands

Enter the MySQL database console,

such as Mysql-u root-p

Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)

Mysql>source Wcnc_db.sql

B: Use the mysqldump command

Mysqldump-u Username-p dbname < Filename.sql

C: Use MySQL command

Mysql-u Username-p-D dbname < Filename.sql

First, start and exit

1, into MySQL: Start the MySQL Command line Client (MySQL's DOS interface), directly enter the installation of the password can be. The prompt at this point is:mysql>

2, Exit Mysql:quit or exit

Second, the library operation

1. Create a database

Command: CREATE DATABASE < database name >

For example: Create a database named XHKDB

mysql> CREATE DATABASE xhkdb;

2, display all the database

Command: Show databases (note: Last has an S)

mysql> show databases;

3, delete the database

Command: Drop Database < database name >

For example, to delete a database named Xhkdb

mysql> drop Database xhkdb;

4, connect the database

Command: Use < database name >

For example: If the XHKDB database exists, try to access it:

mysql> use XHKDB;

ScreenTip: Database changed

5. View the database currently in use

Mysql> Select Database ();

6, the current database contains table information:

Mysql> Show tables; (Note: Last has an S)

Third, table operation, the operation should be connected to a database

1, build the table

Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];

Mysql> CREATE TABLE MyClass (

> ID int (4) NOT null primary key auto_increment,

> name char (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 the table

Command: DROP table < table name >

For example, to delete a table named MyClass

mysql> drop table MyClass;

4. Inserting data

Command: INSERT into < table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]

For example, insert two records into table MyClass, two records that say: Tom with a number 1 is 96.45, and the number 2 for Joan is 82.99, and the number 3 for Wang is 96.5.

mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

5, the data in the query table

1), Query all lines

Command: Select < Field 1, field 2,...> from < table name > where < expression >

For example: View all data in table MyClass

Mysql> select * from MyClass;

2), query the first few lines of data

For example: View the first 2 rows of data in table MyClass

Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;


Mysql> select * from MyClass limit 0, 2;

6, delete the data in the table

Command: Delete from table name where expression

For example: Delete a record in table MyClass that is numbered 1

Mysql> Delete from MyClass where id=1;

7, modify the data in the table: 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 field type other;

For example, a field passtest is added to the table MyClass, 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 name;

For example: In the table MyClass the name is changed to Youclass

Mysql> Rename table MyClass to Youclass;

Update Field Contents

Update table name set field name = new Content

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

Add 4 spaces before the article

Update article Set Content=concat (", content);

Field type

1. int[(M)] Type: normal size integer type

2. double[(m,d)] [Zerofill] Type: normal size (double precision) floating-point number type

3. Date Date Type: The scope of support is from 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to date columns using strings or numbers

4. CHAR (M) Type: fixed-length string type, when stored, always fill the right to the specified length with a space

5. BLOB text type with a maximum length of 65535 (2^16-1) characters.

6. VARCHAR Type: variable-length string type

5. Import Database Tables

(1) Create. sql file

(2) First create a library such as Auction.c:mysqlbin>mysqladmin-u root-p creat auction, prompts for a password, and then successfully created.

(2) Import Auction.sql file

C:mysqlbin>mysql-u Root-p Auction < Auction.sql.

By doing this, you can create a database auction and one of the table auction.

6. Modify Database

(1) Add fields to the MySQL table:

ALTER TABLE dbname add column userid int (one) NOT null primary key auto_increment;

In this way, a field userid is added to the table dbname, and the type is int (11).

7. Authorization for MySQL Database

Mysql>grant Select,insert,delete,create,drop

On *.* (or test.*/user.*/.)

To User name @localhost

Identified by ' password ';

Such as: Create a new user account so that you can access the database, you need to do the following:

Mysql> Grant Usage

-> on test.*

-> to Testuser@localhost;

Query OK, 0 rows affected (0.15 sec)

Since then, a new user is created: testuser, this user can only connect to the database from localhost and connect to the test database. Next, we must specify what testuser this user can do:

mysql> GRANT Select, INSERT, Delete,update

-> on test.*

-> to Testuser@localhost;

Query OK, 0 rows Affected (0.00 sec)

This action enables TestUser to perform Select,insert and delete and update query operations in each table in the test database. Now we end the operation and exit the MySQL client program:

Mysql> exit


1: Use the show statement to find out what database is currently on the server:

Mysql> show DATABASES;

2:2. Create a database Mysqldata

mysql> Create DATABASE Mysqldata;

3: Select the database you created

mysql> use Mysqldata; (press ENTER to appear when the database changed the operation success!) )

4: See what tables exist in the current database

Mysql> show TABLES;

5: Create a database table

Mysql> Create TABLE MYTABLE (name VARCHAR (), Sex CHAR (1));

6: Show the structure of the table:


7: Add a record to the table

mysql> INSERT INTO MYTABLE values ("HyQ", "M");

8: Load data into a database table (for example, d:/mysql.txt) in text mode

mysql> LOAD DATA Local INFILE "D:/mysql.txt" into TABLE MYTABLE;

9: Import. sql file command (e.g. D:/mysql.sql)

Mysql>use database;

Mysql>source D:/mysql.sql;

10: Delete Table


11: Empty the table

Mysql>delete from MYTABLE;

12: Update the data in the table

Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

Here are some of the management experiences of using MySQL inadvertently seen on the web,

Excerpt from:

In Windows, MySQL exists as a service, and you should make sure that the service is started and that the available net start MySQL command is not started before you use it. Linux can be started with the "/etc/rc.d/init.d/mysqld start" command, and notice that the initiator should have administrator privileges.

The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, where the anonymous account deletion, the root account set the password, you can use the following command:

Use MySQL;

Delete from User where user= "";

Update User set Password=password (' NewPassword ') where user= ' root ';

If you want to limit the logon terminals used by the user, you can update the host field for the corresponding user in the users table, restart the database service after making the above changes, and at this point you may be able to log on to the following similar commands:



MySQL mydb-uroot-p;

MySQL Mydb-uroot-pnewpassword;

The command parameters above are part of the common parameters, and the details refer to the documentation. The mydb here is the name of the database to log on to.

In the development and practical applications, users should not only use root to connect the database, although the use of root users to test is very convenient, but it will bring significant security risks to the system, but also not conducive to the improvement of management technology. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete data. MySQL user management is implemented through the user table, there are two ways to add new users, one is to insert the corresponding data rows in the user table, set the appropriate permissions, and the second is to create a user with some kind of permission through the grant command. One of the common uses 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 give this user the ability to manage his or her permissions on the object, add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption, in case the malicious person steals the password. For those who have not used the user should be given clearance, permissions of the user should be timely recall permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.

The following gives an explanation of the common rights that I get from other sources (

Global Administrative permissions:

File: Read and write files on the MySQL server.

PROCESS: Displays or kills a service thread belonging to another user.

RELOAD: Overload access Control table, refresh log, etc.

SHUTDOWN: Turn off MySQL service.

Database/data Table/Data column permissions:

Alter: Modify existing data tables (for example, add/Remove Columns) and indexes.

Create: Create a new database or datasheet.

Delete: Deletes a table record.

Drop: Deletes a datasheet or database.

Index: Create or delete indexes.

Insert: Adds a record of the table.

Select: Displays/searches the records of the table.

Update: Modifies records that already exist in the table.

Special permissions:

All: Allow to do anything (like root).

USAGE: Only allow login-nothing else 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 by connecting MySQL, modifying passwords, 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 by connecting MySQL, modifying passwords, and adding users.

First, connect MySQL

Format: mysql-h host address-u user name-P user Password

1, Example 1: Connect to MySQL on this computer

First in the Open DOS window, and then into the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so direct return can enter into MySQL, The MySQL prompt is:mysql>

2, Example 2: Connect to the remote host MySQL

Assume the IP of the remote host is:, username is root, password is abcd123. Type the following command:


(Note: U and root can be without spaces, others are the same)

3, exit MySQL command: Exit (enter)

Second, modify the password

Format: Mysqladmin-u username-P Old password password new password

1, Example 1: To root add a password ab12. First enter the directory Mysqlbin in DOS, and then type the following command

Mysqladmin-uroot-password AB12

Note: Since Root does not have a password at the beginning, the-p old password can be omitted.

2, Example 2: And then change the root password to djg345

MYSQLADMIN-UROOT-PAB12 Password djg345

MySQL common commands (next)

First, the Operation skill

1, if you hit the command, when you find that you forgot to add a semicolon, you do not need to repeat the command, as long as a semicolon to enter on it. That is to say, you can divide a complete command into lines, and then use a semicolon as the closing sign to make it OK.

2, you can use the cursor up and down keys to pull up the previous command. But I used to use a MySQL older version does not support. I'm using a mysql-3.23.27-beta-win.

Second, display command

1. Display the list of databases.

show databases;

Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, is actually using this library to operate.

2, display the data table in the library:

use MySQL;//open library, learned foxbase must be no stranger

Show tables;

3, display the structure of the data table:

describe table name;

4, build the library:

Create database library name;

5, the establishment of the table:

Use library name;

CREATE TABLE table name (field set list);

6, delete the database and delete the table:

drop Database library name;

The drop table table name;

7. Empty the record of the table:

Delete from table name;

8, display the records in the table:

SELECT * from table name;

Example of 三、一个 and table-building and inserting data

Drop database if exists school; Delete if there is school

Create Database School; Build a library School

Use school; Open Library School

CREATE TABLE teacher//Create tables Teacher


ID int (3) auto_increment NOT null primary key,

Name Char (a) NOT NULL,

Address varchar default ' Shenzhen ',

Year Date

); Build Table End

The following is the Insert field

Insert into teacher values (', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');

Insert into teacher values (', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note: In the table (1), set the ID to a number field of length 3: Int (3) and let it automatically add one for each record: Auto_increment cannot be null: NOT NULL and make him the main field primary key

(2) Set name to a character field of length 10

(3) Set the address to a character field of length 50 and the default value is Shenzhen. What is the difference between varchar and char, only to wait for later articles to say.

(4) Set year as Date field.

If you type the above command at the MySQL prompt, it's not easy to debug. You can write the above command in a text file as if it were school.sql, then copy it to C:\ and enter the directory \mysql\bin in DOS, and then type the following command:

Mysql-uroot-p Password < C:\school.sql

If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged and you can use it only if you remove the comment.)

Iv. transferring text data to a database

1, the text data should conform to the format: The field data is separated by the TAB key, null value with \ n to replace.


3 Rose Shenzhen II 1976-10-10

4 Mike Shenzhen one 1975-12-23

2, Data incoming command load infile "filename" into table name;

Note: You'd better copy the files to the \mysql\bin directory and use the using command to hit the library where the table resides.

V. BACKUP DATABASE: (command executes in DOS \mysql\bin directory)

Mysqldump--opt SCHOOL>SCHOOL.BBB

Note: Back up the database school to the school.bbb file, school.bbb is a text file, file name, open to see if you will find something new.

The complete syntax for a 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]


A partial representation enclosed in brackets ([]) is optional, and the part enclosed in braces ({}) is a representation of the one that must be selected from.

1 FROM clause

The FROM clause specifies the source of the fields in the SELECT statement. The FROM clause is followed by one or more expressions, separated by commas, where the expression can be a single table name, a saved query, or a compound result from a INNER join, a left join, or a right join. If a table or query is stored in an external database, indicate its full path after the IN clause.

Example: The following SQL statement returns all customers with orders:

SELECT Orderid,customer.customerid

From Orders Customers

WHERE Orders.customerid=customers.customeersid

2 All, DISTINCT, Distinctrow, top predicates

(1) All returns all records that satisfy the conditions of the SQL statement. If this verb is not specified, all is the default.

Example: SELECT all Firstname,lastname

From Employees

(2) DISTINCT If there are multiple records with the same data in the selection field, only one is returned.

(3) Distinctrow If there is a duplicate record, only return a

(4) Top display Query the tail of several records. You can also return the percentage of the record by using the top N percent clause (where N represents a percentage)

Example: Returns 5% orders with the largest order amount

SELECT Top 5 percent*

from [Order Details]

Order by unitprice*quantity* (1-discount) DESC

3 Alias a field with the AS clause

If you want to get a new caption for the returned column, or after you calculate or summarize a field, a new value is generated that you want to put in a new column display, then keep it as.

Example: Returns the FirstName field with an alias of nickname

SELECT FirstName as nickname, LastName, City

From Employees

Example: Returns a new column showing inventory value

SELECT ProductName, UnitPrice, UnitsInStock, Unitprice*unitsinstock as Valueinstock

From Products

Two. WHERE clause Specify query criteria

1 comparison operators

comparison operator meaning

= equals

> Greater than

< less than

>= is greater than or equal to

<= less than or equal

<> Not equal to

!> not greater than

!< not less than

Example: Return to January 96 order

SELECT OrderID, CustomerID, OrderDate

From Orders

WHERE orderdate> #1/1/96# and orderdate< #1/30/96#


Mcirosoft JET SQL, the date is bounded by ' # '. Dates can also be replaced with the DateValue () function. When comparing character data, add single quotes ', trailing spaces are ignored in the comparison.


WHERE orderdate> #96 -1-1#

Can also be expressed as:

WHERE orderdate>datevalue (' 1/1/96 ')

Negates using the not expression.

For example: View orders after January 1, 96

WHERE not orderdate<= #1/1/96#

2 Range (BETWEEN and not BETWEEN)

BETWEEN ... And ... operator specifies a closed interval to search for.

Example: Return the order from January 96 to February 96.

WHERE OrderDate Between #1/1/96# and #2/1/96#

3 list (in, No in)

The in operator is used to match any one of the values in the list. The IN clause can replace a series of conditions that are joined by an OR clause.

To find all the customers who live in London, Paris or Berlin

SELECT CustomerID, CompanyName, ContactName, city

From Customers

WHERE City in (' London ', ' Paris ', ' Berlin ')

4 pattern matching (like)

The like operator verifies that a field value that contains string data matches a specified pattern.

Wildcard characters used in the LIKE operator

Wildcard meaning

? Any single character

* Characters of any length

# 0~9 a single digit between

[Character List] any of the values in the character lists

[! Character List] is not in any of the values in the character lists

-Specify a range of characters, with values on both sides being their upper and lower bounds

Example: Returning a customer with a postal code between (171) 555-0000 to (171) 555-9999

SELECT CustomerID, Companyname,city,phone

From Customers

WHERE Phone like ' (171) 555-#### '

Some styles and meanings of the LIKE operator

Style meaning does not match

Like ' A * ' followed by any length of character bc,c255

Like ' 5[*] ' 5*5 555

There is any character between like ' 5?5 ' 5 and 5 55,5wer5

Like ' 5# #5 ' 5235,5005 5kd5,5346

Any character between like ' [a A-z] ' A-Z 5,%

Any of the characters like ' [!0-9] ' not 0-9 0,1

Like ' [[] ' 1,*

Three. Sort the results with an ORDER BY clause

The order clause sorts query results by one or more (up to 16) fields, either ascending (ASC) or Descending (DESC), and the default is ascending. The order clause is usually placed at the end of the SQL statement.

Multiple fields are defined in the order clause, sorted by field.


SELECT Productname,unitprice, Unitinstock

From Products

ORDER by Unitinstock DESC, UnitPrice DESC, ProductName

You can mix field names and location numbers by using the position number of the field in the select list instead of the field name in the ORDER BY clause.

Example: The following statement produces the same effect as the above.

SELECT Productname,unitprice, Unitinstock

From Products

ORDER by 1 DESC, 2 desc,3

Four. Use the connection relation to realize the multiple table query

Example: Find out the names of suppliers and customers in the same city

SELECT Customers.companyname, Suppliers.ComPany.Name

From Customers, suppliers


For example: Find products and orders that have a product inventory larger than the order number of the same product

SELECT Productname,orderid, Unitinstock, Quantity

From products, [order Deails]

WHERE Product.productid=[order Details]. ProductID

and unitsinstock>quantity

Another approach is to use the Jnner JOIN that is unique to Microsof JET SQL


From table1 INNER JOIN table2

On Table1.field1 comparision table2.field2

Where comparision is the comparison operator used in the previous where clause.

SELECT firstname,lastname,orderid,customerid,orderdate

From Employees

INNER JOIN Orders on Employees.employeeid=orders.employeeid


INNER join cannot connect to the memo OLE Object single Double data Type field.

To concatenate multiple on clauses in a join statement


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

can also

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 joins return more records, leaving mismatched records in the results, regardless of whether a record that satisfies the condition does not exist to return all records on the other side.

from table [left| Right]join table2

On Table1.field1comparision table.field2

To establish an external connection with left JOIN, the table on the left side of the expression shows all of its data

For example: Return all merchandise, regardless of order volume

SELECT ProductName, OrderID

From Products

Left JOIN Orders on Products.prductsid=orders.productid

The difference between a right and a left connection is that it returns all records from the table on the left, regardless of whether there is a matching record in the left.

For example: If you want to know the customer's information and count the distribution of customers in each region, you can use a right connection, even if there is no customer in a region, to return customer information.

Null values do not match each other, and you can use an outer join to test whether the fields of one of the connected tables have null values.


From Talbe1

Left JOIN table2 on table1.a=table2.c

1 connection query using the IIF function to display null values in 0 values

IIf expression: IIf (IsNull (amount,0,amout)

For example: if the order is greater than or less than ¥50, return a flag.

Iif ([Amount]>50,? Big Order?,? Small order?)

Five. Grouping and summarizing query results

In the syntax of SQL, GROUP by and having clauses are used to summarize data. The GROUP BY clause indicates which fields to group by, and the records are filtered by the HAVING clause after grouping them.

The syntax of the GROUP by clause

SELECT fidldlist

From table

WHERE criteria

[GROUP by Groupfieldlist [GroupCriteria]]

Note: Microsoft Jet database jet cannot group notes or OLE Object fields.

The null value in the Group by field is for grouping but cannot be omitted.

Null values are not computed in any SQL aggregate function.

The GROUP by clause can have up to 10 fields, sorted in order of precedence, from left to right.

For example: After grouping by title in the employee table in the ' WA ' region, find all titles with an equal title of more than 1 employees.

SELECT Title, Count (Title) as Total

From Employees

WHERE Region = ' WA '

GROUP by Title

Having Count (Title) >1

Accumulating functions in JET SQL

The meaning of aggregate function

SUM () sum

AVG () Average

Number of records in COUNT () expression

COUNT (*) calculates the number of records

Max Max value

Min min value

VAR Variance

STDEV standard Error

First value

Last value

Six. Create parameter query with parameters declaration

Syntax for parameters declarations:

PARAMETERS name Datatype[,name datatype[, ...]

Where name is the identifier for the parameter, you can reference the parameter by a marker.

DataType describes the data type of the parameter.

Use to place the parameters declaration before any other statement.


Parameters[low Price] Currency,[beginning date]datatime

SELECT OrderID, OrderAmount

From Orders

WHERE Orderamount>[low Price]

and orderdate>=[beginning Date]

Seven. function query

The so-called function query, in fact, is a kind of operation query, it can be a fast and efficient operation of the database. It selects the eligible data and then batches the data in order to select the query. Feature queries include update queries, delete queries, add queries, and make-table queries.

1 Update Query

The UPDATE clause can change 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 criteria

Example: British customers increased the volume of orders by 5%, freight volume increased by 3%

UPDATE oeders

SET OrderAmount = OrderAmount *1.1

Freight = freight*1.03

WHERE ShipCountry = ' UK '

2 Delete Query

The DELETE clause allows the user to delete a large number of obsolete or redundant data.

Note: The object that deletes the query is the entire record.

Syntax for DELETE clause:

DELETE [table name. *]

From source table

WHERE criteria

To delete all orders that were 94 years old


From Orders

WHERE orderdata< #94 -1-1#

3 Append Query

An INSERT clause can append one or a group of records to the tail of one or more tables.

INTO clause specifies the table to accept the new record

The values keyword specifies the data value that the new record contains.

Syntax for INSERT clause:

Insetr into destination table or query (field 1, Field 2,...)

Values (value 1, value 2,...)

Example: Adding a Client

INSERT into Employees (firstname,lastname,title)

ValueS (' Harry ', ' Washington ', ' trainee ')

4 Generating Table queries

You can copy all the records that meet the criteria at once to a new table. Usually make a backup or copy of a record or a basis for a report.

The SELECT into clause is used to create a build table query syntax:

SELECT Field 1, Field 2,...

into a new table [in external database]

From source database

WHERE criteria

Example: Make an archive backup for an order


Into ordersarchive

From Orders

Eight. Joint enquiries

The Union operation can combine the results of multiple queries into a single result set.

General syntax for union operations:

[Table] Query 1 union [all] Query 2 union ...

Returns the names and cities of all suppliers and customers in Brazil

SELECT companyname,city

From suppliers

WHERE Country = ' Brazil '


SELECT companyname,city

From Customers

WHERE Country = ' Brazil '


By default, the Union clause does not return duplicate records. If you want to show all the records, you can add the All option

The Union operation requires that the query have the same number of fields. However, the field data types do not have to be the same.

You can group by using either the GROUP BY clause or the HAVING clause in each query parameter. To display the returned data in a specified order, you can use the Oreer by clause at the end of the last query.

Nine. Cross-check

A crosstab query can calculate the sum, average, count, or other sum of data, grouped by two kinds of information: one displayed at the left of the table and the other at the top of the table.

Microsoft Jet SQL creates crosstab query syntax with the TRANSFROM statement:

TRANSFORM aggfunction

SELECT statement

GROUP BY clause

PIVOT pivotfield[in (value1 [, value2[,...]])]

Aggfounction refers to the SQL accumulation function,

The SELECT statement selects the field as the caption,

GROUP BY group


Pivotfield the field or expression used to create a column header in a query result set, limiting its value with an optional in clause.

Value represents a fixed value for creating a column header.

Example: shows the number of orders received by each employee in each quarter in 1996 years:


SELECT firstname& ' &lastname as FullName

From Employees INNER JOIN Orders

On employees.employeeid = Orders.EmployeeID

WHERE DatePart ("yyyy", OrderDate) = ' 1996 '

GROUP by firstname& ' &lastname

ORDER by firstname& ' &lastname

Povot DatePart ("Q", OrderDate) & ' quarter '

10. Sub-query

Subqueries can be interpreted as nested queries. A subquery is a SELECT statement.

The value of the 1 expression is compared to the single value returned by the subquery


Expression comparision [any| all| SOME] (subquery)


The any and some predicates are synonyms and are used with comparison operators (=,<,>,<>,<=,>=). Returns a Boolean value of true or false.any meaning that the expression is compared to a series of values returned by the subquery. Whenever one of the comparisons yields a true result, the any test returns a true value (both the result of the WHERE clause) and the current record of the expression is entered in the result of the main query. The all test requires the expression to have a true result from a comparison of a series of values returned by the subquery, returning a true value.

Example: The main query returns all products with a unit price higher than the price of any product with a discount greater than or equal to 25%

SELECT * FROM Products

WHERE Unitprice>any

(SELECT UnitPrice from[order Details] WHERE discount>0.25)

2 checks whether the value of an expression matches a value of a set of values returned by a subquery


[NOT] In (subquery)

Example: Returns a product with an inventory value greater than or equal to 1000.

SELECT ProductName from

WHERE ProductID in

(SELECT prdoctid from [order DEtails]

WHERE unitprice*quantity>= 1000)

3 Detect whether a subquery returns any records


[NOT] EXISTS (subquery)

Example: Using exists to retrieve British customers

SELECT Companyname,contactname

From Orders



From Customers

WHERE Country = ' UK ' and

Customers.customerid= Orders.CustomerID)
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.