Mysql common operation and command _mysql under Windows platform

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;





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>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)