Common mysql commands

I don't know how the database works, but I don't know how to operate the database. It's not difficult to use commands. I have a couple of plants and birds. Haha, it's very nice to use it, reprinted a good blog written by everyone to learn this article from: http://www.cnblogs.com/hateislove214/archive/2010/11/05/1869889.html

 

 

1. export 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. sql2. export a table mysqldump-u user name-p database name Table Name> export file name mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users.sql3. export a database structure mysqldump-u wcnc-p-d-add-drop-table smgp_1__wcnc> d: wcnc_db.sql-d does not have data-add-drop-table add A drop table4. import database A before each create statement: common source commands go to the mysql Database Console, such as m Ysql-u root-pmysql> use Database and then use the source command. The following parameter is a script file (as used here. SQL) mysql> source wcnc_db.sqlB: Use the mysqldump command mysqldump-u username-p dbname <filename. sqlC: use mysql Command mysql-u username-p-D dbname <filename. SQL 1. Start and Exit 1. Enter MySQL: Start MySQL Command Line Client (DOS interface of MySQL) and enter the password for installation. At this time, the prompt is: mysql> 2. exit MySQL: quit or exit 2. database operation 1. create database Command: create database <database Name> example: create a database named xhkdb mysql> create database xhkdb; 2. Display All database commands: show databases (Note: There is finally s) mysql> show databases; 3. database deletion command: drop database <database Name> For example: delete a database named xhkdb mysql> drop database xhkdb; 4. database Connection command: use <database Name> example: if the xhkdb Database exists, try to access it: mysql> use xhkdb; on-screen prompt: database changed5, view the currently used Database mysql> select database (); 6. Table information contained in the current database: mysql> show tabl Es; (Note: There is a last s) III. Table operations. before the operation, connect to a database. 1. Table creation 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 (20) not null,> sex int (4) not null default '0',> degree double (); 2. Table Structure command: desc table name, alternatively, run the show columns from table name mysql> DESCRIBE MyClassmysql> desc MyClass; mysql> show columns from MyClass; 3. Run the drop table command. For example, delete a table named My Class table mysql> drop table MyClass; 4. insert Data command: insert into <table Name> [(<field name 1> [,.. <field name n>])] values (value 1) [, (value n)] For example, insert two records into the MyClass table. The two records indicate: the result of "Tom" in "1" is 96.45, the result of "Joan" in "2" is 82.99, and the result of "Wang" in "3" is 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 the line commands: select <Field 1, Field 2,...> from <Table Name> where <expression> example: view all data in the MyClass table mysql> select * from ACC. Lass; 2) query the data of the first few rows, for example: view the data of the first two rows in the MyClass table mysql> select * from MyClass order by id limit; or: mysql> select * from MyClass limit; 6. delete table data command: delete from table name where expression example: delete 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 a field to the table: Command: alter table name add field type other; for example: A passtest field is added to the MyClass table and the type is int (4). The default value is 0 mysql> alter table MyClass add passtest int (4) default '0' 8. Change table name: Command: rename table original table name to new table name; for example, change MyClass name 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 to update article before the 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 the DATE value in YYYY-MM-DD format, but allows you to assign the value to the DATE column with a string or number 4. CHAR (M) type: fixed-length string type. When stored, it is always filled with spaces to the right to the specified length of 5. blob text type. The maximum length is 65535 (2 ^ 16-1) characters. 6. VARCHAR: variable-length string type 5. import database table (1) create. SQL file (2) First generates a library such as auction. c: mysqlbin> mysqladmin-u root-p creat auction. A message 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 database (1) add a field to the mysql table: alter table dbname add column userid int (11) not null primary key auto_increment; in this way, in the dbname table, a field userid is added, whose 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 user account to access the database, you need to 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 has been created, this user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that 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 Query operations on 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;. Create a DATABASE MYSQLDATAmysql> Create database mysqldata; 3: select the mysql> use mysqldata Database you created. (If you press the Enter key to change the Database, the operation is successful !) 4: Check what TABLES exist 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 the record mysql> insert into MYTABLE values ("hyq", "M") to the table; 8: load data into a database TABLE (for example, D:/mysql.txt) using text. mysql> load data local infile "D:/mysql.txt" into table mytable; 9: import. SQL file commands (such as D:/mysql. SQL) mysql> use database; mysql> source d:/mysql. SQL; 10: Delete TABLE mysql> drop TABLE MYTABLE; 11: Clear TABLE m Ysql> delete from MYTABLE; 12: update table data mysql> update MYTABLE set sex = "f" where name = 'hyq '; the following is the management experience of using MySql on the network, which is excerpted from the restart start mysql command. 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, delete anonymous accounts and Set passwords for root accounts. Run the following command: use mysql; delete from User where User = ""; update User set Password = PASSWORD ('newpassword') where User = 'root'; to restrict the logon terminal used by the User, you can update the Host field of the corresponding User in the User table, after 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 this 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 to do anything (same as root ). USAGE: Only logon is allowed. Other operations are not allowed. ------------------- MYSQL commands are commonly used by many friends who 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. I. MYSQL connection 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 and then enter the directory mysqlbin, enter the mysql-uroot-p command and press enter to prompt you to enter the password. If MYSQL is just installed, the Super User root has no password, so press enter to enter MYSQL, MYSQL prompt: 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 username is root, and the password is abcd123. Run the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: you do not need to add spaces to the u and root nodes.) 3. exit MYSQL: 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 has no password at the beginning, the old-p password can be omitted. 2. Example 2: Change the root password to djg345 mysqladmin-uroot-pab12 password djg345MYSQL Common commands (below) I. Operation Skills 1. If you call a command, after you press enter, you will find that you have forgotten the extra points. You don't have to repeat the command. You just need to press a semicolon and 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. 2. Display command 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; // when you open the database, you will not be unfamiliar with learning FOXBASE. show tables; 3. display the data table structure: describe table name; 4. database creation: create database name; 5. table creation: use database Name; create table Name (field setting list); 6. database deletion and table deletion: drop database name; drop table name; 7. Clear the records in the table: delete from table name; 8. display the records in the table: select * from table name; 3. drop database if exists school as an instance for creating a database, creating a table, and inserting data; // if SCHOOL exists, delete create database school; // create a database SCHOOL; // open the database SCHOOL 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 ); // The end of table creation // insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 ') for the insert field below '); insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23'); Note: Table creation (1) set the ID to a numeric field of 3: int (3) and make it automatically add one for each record: auto_increment cannot be blank: not null and set it as the main field primary key (2) Set NAME to a character field with a length of 10 (3) set ADDRESS to a length of 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 into a text file as they are. SQL, then copy to c: \, and enter the directory \ mysql \ bin in DOS status, and then enter the following command: mysql-uroot-p password <c: \ school. if the SQL statement is successful, no display is displayed for a blank row. If an error occurs, a prompt is displayed. (The preceding command has been debugged. You only need to remove the // annotation to use it ). 4. convert text data to the database. 1. The text data must conform to the format: field data is separated by the tab key, and the null value is replaced by \ n. example: 3 rose Shenzhen No. 2 Middle School 1976-10-10 4 mike Shenzhen No. 1 1975-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 school. bbb file, school. bbb is a text file with any file name. Open it and you will find a new one. I. 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] Description: The section enclosed in brackets ([]) indicates that it is optional and braces ({}) are used ({}) the enclosed part indicates that you must select one of them. 1 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. customerID FROM Orders Customers WHERE Orders. customerID = MERs. customeersID2 ALL, DISTINCT, DISTINCTROW, and TOP predicates (1) ALL return ALL records that meet the SQL statement conditions. If this predicate is not specified, the default value is ALL. For 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) DISTINCTROW if there are duplicate records, only one (4) TOP is returned to display several records at the beginning and end of the query. You can also return the percentage of records. This is an example of using the top n percent clause (where N represents the percentage: returns the select top 5 PERCENT * FROM [Order Details] order by UnitPrice * Quantity * (1-Discount) DESC3 uses the AS clause to get an alias for a 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, if you want to display it in a new column, use AS to keep it. Example: return the FirstName field with the alias NickName SELECT FirstName AS NickName, LastName, City FROM Employees. For example, return a new column showing the inventory value: SELECT ProductName, UnitPrice, UnitsInStock, unitPrice * UnitsInStock AS valueInStock FROM Products II. the WHERE clause specifies the query condition 1 comparison operator meaning = equal to> greater than <less than> = greater than or equal to <= less than or equal to <> not equal to!> Not greater! <Not less than example: return the SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate> #1/1/96 # AND OrderDate Orders FROM January. <#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. For example, WHERE OrderDate> #96-1-1 # can also be expressed as: WHERE OrderDate> Datevalue ('2014/1/96 ') using the NOT expression to reverse. For example, check the order WHERE Not OrderDate <= #1/1/96 #2 range (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) 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, SELECT CustomerID, CompanyName, ContactName, City FROM MERs WHERE City In ('London ', 'Paris', 'berlin') for all Customers In London, Paris, or Berlin ') 4. The LIKE operator checks whether the value of a field containing string data matches a specified pattern. What are the meanings of wildcard characters used in the LIKE operator? Any single character * character of any length #0 ~ A single number between 9 [Character List] any value in the character list [! Character List] any value not in the character list-specifies the character range, and the values on both sides are their upper and lower limits. Example: return zip code between (171) 555-0000 to (171) for Customers between 555-9999, SELECT CustomerID, CompanyName, City, Phone FROM Customers WHERE Phone Like '(171) 555-#### some styles and definitions of the 'like operator. The style meanings do not conform to the LIKE 'a * 'a followed by any length of characters Bc, c255 LIKE '5 [*] '5x5 555 LIKE '5? Between 5 and 5, there is any character 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 results by 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. For example, SELECT ProductName, UnitPrice, UnitInStock FROM Products order by UnitInStock DESC, UnitPrice DESC, and ProductName order by clauses can replace the field name with the position number in the selection list, you can mix the field names and positions. 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 example using the connection relationship: find the name of the supplier and customer in the same city SELECT MERs. companyName, Suppliers. comPany. name FROM MERs, Suppliers WHERE Customers. city = Suppliers. city: SELECT ProductName, OrderID, UnitInStock, Quantity FROM Products, [Order Deails] WHERE Product. productID = [Order Details]. productID AND Uni TsInStock> Quantity another method is to use the jnner join syntax exclusive to Microsof jet SQL: FROM table1 INNER JOIN table2 ON table1.field1 comparision table2.field2 WHERE comparision is the comparison operator used in the preceding WHERE clause. SELECT FirstName, lastName, OrderID, CustomerID, OrderDate FROM Employees inner join Orders ON Employees. EmployeeID = Orders. EmployeeID Note: inner join cannot be connected to Memo OLE Object Single Double data type fields. JOIN multiple ON Clause syntax 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 distinct compopr tables can also SELECT fields FROM table1 inner join (table2 inner join [(] table3 [injoer] [(] 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 records that meet the conditions, all records on the other side are returned. FROM table [LEFT | RIGHT] JOIN table2 ON table1.field1comparision table. field2 uses a left join to establish an external join. All data examples are displayed in the table on the left of the expression: SELECT ProductName, orderID FROM Products left join Orders ON Products. prductsID = Orders. the difference between the right join and the left join of ProductID is that it returns all records from the left table regardless of the matching records in 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 the JOIN query to display null Iif expressions with 0 values: Iif (IsNull (Amount, 0, Amout) 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 summary 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. 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 function in jet SQL aggregate function meaning SUM () sum AVG () Average Value COUNT () expression number of records COUNT (*) calculation number of records MAX maximum min var variance STDEV standard error FIRST value LAST value 6. use Parameters Declaration to create a parameter to query the 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 is actually an operation query that can perform quick 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. The UPDATE query 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: UPDA New WHERE criterion for TE table name SET: orders of UK customers increased by 5%, cargo volume increase 3% update oeders set OrderAmount = OrderAmount * 1.1 Freight = Freight * 1.03 WHERE ShipCountry = 'uk '2 DELETE query DELETE clause allows users 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: to DELETE all Orders deleted 94 years ago * FROM Orders WHERE OrderData <#94-1-1 #3 append query INSERT clauses, one or more records can be appended to one or more tables.. the INTO clause specifies the table valueS keyword that accepts the new record to specify the data value contained in the new record. INSERT clause Syntax: INSET R 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 to copy all records meeting the conditions to a new table at a time. A backup or copy of a record is usually created or serves as the basis for a report. the select into clause is used to create the generated Table query syntax: SELECT Field 1, Field 2 ,... INTO new table [IN external database] FROM source database WHERE criterion example: Create an archive backup for the Order SELECT * INTO OrdersArchive FROM Orders 8. the UNION operation of the joint query can merge the results of multiple queries into a single result set for display. general syntax for UNION operations: [Table] query 1 UNION [ALL] query 2 UNION... Example: return the names of all Suppliers and Customers in Brazil and the cities SELECT CompanyName, City FROM Suppliers WHERE Country = 'Brazil 'union select CompanyName, City FROM Customers WHERE Country = 'Brazil'. Note: by default, the UNION clause does not return repeated records. if you want to display ALL records, you can add the ALL option UNION operation to query fields with the same number. 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: calculates the sum, average, Count, or other sum calculation methods 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. micro Soft Jet SQL uses the TRANSFROM statement to create a cross tabulation query syntax: TRANSFORM aggfunction SELECT statement GROUP BY clause contains struct tfield [IN (value1 [, value2 [,…])] Aggfounction refers to the SQL accumulation function. The SELECT statement selects the field as the title. GROUP BY GROUP Description: The field or expression used BY the Pivotfield to create the column title in the query result set, use an optional IN clause to limit its values. 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) = '000000' group by FirstName & ''& LastName order by FirstName &'' & LastName POVOT DatePart ("q", OrderDate) & 'quarter 10. A subquery can be considered as a set query. 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) Description: ANY and SOME predicates are synonyms, used 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. 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 IN a group returned by the subquery. Syntax: [NOT] IN (subquery) Example: return products whose inventory value is greater than or equal to 1000. SELECT ProductName FROM Products WHERE ProductID IN (SELECT PrdoctID FROM [Order DEtails] WHERE UnitPrice * Quantity> = 1000) 3. Check whether the subquery returns any record Syntax: [NOT] EXISTS (subquery) Example: Use EXISTS to retrieve UK customers' SELECT ComPanyName, ContactName FROM Orders where exists (SELECT * FROM MERs WHERE Country = 'uk' AND MERs. customerID = Orders. customerID)