Mysql Common Command Daquan Scripting Home Summary _mysql

MySQL Common commands
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 database 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;
Or:
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
bye9!
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:
Mysql> DESCRIBE MYTABLE;
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
Mysql>drop TABLE MYTABLE;
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: http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html
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-uroot-p;
Mysql-uroot-pnewpassword;
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 (www.cn-java.com):
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: 110.110.110.110, username is root, password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(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.
Cases:
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&GT;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]
Description
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#
Attention:
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.
Cases:
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.
Cases:
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
WHERE customers.city=suppliers.city
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
Grammar:
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
Attention:
INNER join cannot connect to the memo OLE Object single Double data Type field.
To concatenate multiple on clauses in a join statement
Grammar:
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.
SELECT *
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.
Cases:
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
DELETE *
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
SELECT *
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 '
UNION
SELECT companyname,city
From Customers
WHERE Country = ' Brazil '
Note:
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
Description
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:
TRANSFORM Count (OrderID)
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
Grammar:
Expression comparision [any| all| SOME] (subquery)
Description
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
Grammar:
[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
Grammar:
[NOT] EXISTS (subquery)
Example: Using exists to retrieve British customers
SELECT Companyname,contactname
From Orders
WHERE EXISTS
(SELECT *
From Customers
WHERE Country = ' UK ' and
Customers.customerid= Orders.CustomerID)