1. Export the entire database
mysqldump -u username -p –default-character-set=latin1
database name > exported filename (the database default encoding is latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2. Export a table
mysqldump -u username -p database name table name> exported filename
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 means no data; –add-drop-table
adds a drop table statement before each create statement
4. Import a database
A: Commonly used source command
Enter the mysql database console,
for example mysql -u root -p
mysql>use database
Then use the source command, followed by the script file as the parameter (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 the mysql command
mysql -u username -p -D dbname < filename.sql
I. Start and exit
1. Enter MySQL: Start MySQL Command Line
Client (MySQL’s DOS interface), and directly enter the password set during installation. At this point, the prompt is: mysql>
2. Exit MySQL: quit or exit
II. Database operations
1. Create a database
Command: create database <database name>
Example: create a database named xhkdb
mysql> create database xhkdb;
2. Show all databases
Command: show databases (note: there is an s at the end)
mysql> show databases;
3. Delete a database
Command: drop database <database name>
Example: delete the database named xhkdb
mysql> drop database xhkdb;
4. Connect to a database
Command: use <database name>
Example: if the xhkdb database exists, try accessing it:
mysql> use xhkdb;
Screen prompt: Database changed
5. View the currently used database
mysql> select database();
6. Table information contained in the current database:
mysql> show tables; (note: there is an s at the end)
3. Table operations; before performing them, you should connect to a database
1. Create a 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(20) not null,
> sex int(4) not null default ‘0’,
> degree double(16,2));
2. Get the table structure
Command: desc table name, or show columns from table name
mysql>DESCRIBE MyClass
mysql> desc MyClass;
mysql> show columns from MyClass;
3. Delete a table
Command: drop table <table name>
Example: delete the table named MyClass
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.
These two records indicate: the score of Tom with ID 1 is 96.45, the score of Joan with ID 2
is 82.99, and the score of Wang with ID 3
is 96.5.
mysql> insert into MyClass
values(1,’Tom’,96.45),(2,’Joan’,82.99),
(2,’Wang’, 96.59);
5. Query data in the table
1) Query all rows
Command: select <field 1, field 2, …> from < table name
> where < expression >
Example: view all data in the MyClass table
mysql> select * from MyClass;
2) Query the first few rows of data
Example: view the first 2 rows of data in the MyClass table
mysql> select * from MyClass order by id limit 0,2;
Or:
mysql> select * from MyClass limit 0,2;
6. Delete data from the table
Command: delete from table name where expression
Example: delete the record with ID 1 from the MyClass table
mysql> delete from MyClass where id=1;
7. Modify data in the table: 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 table_name add field type other;
For example: add a field named passtest to the MyClass table, with the type int(4) and a default value of 0
mysql> alter table MyClass add passtest int(4) default
‘0’
8. Change the table name:
Command: rename table old_table_name to new_table_name;
For example: change the table name MyClass 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 4 spaces before the article
update article set
content=concat(‘ ’,content);
Field types
1. INT[(M)] type: standard-sized integer type
2. DOUBLE[(M,D)] [ZEROFILL] type:
standard-sized (double-precision) floating-point number type
3. DATE
date type: the supported range 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, the right side is always padded with spaces to the specified length
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 a .sql file
(2) First create a database such as auction. c:mysqlbin>mysqladmin -u
root -p creat auction, you will be prompted to enter the password, and then it will be created successfully.
(2) Import the auction.sql file
c:mysqlbin>mysql -u root -p auction <
auction.sql。
Through the above operations, you can create a database named auction and a table named auction in it.
6. Modify the database
(1) Add a field to a mysql table:
alter table dbname add column userid int(11) not null
primary key auto_increment;
In this way, a field userid of type int(11) is added to the table dbname.
7. Authorization for the mysql database
mysql>grant select,insert,delete,create,drop
on *.* (or test.*/user.*/..)
to username@localhost
identified by ‘password’；
For example: to create a new user account so that it can access the database, perform the following operations:
mysql> grant usage
-> ON test.*
-> TO [email protected];
Query OK, 0 rows affected (0.15 sec)
After that, a new user named testuser is created. This user can only connect to the database from localhost and can connect to the test
database. Next, we must specify which operations the user testuser can perform:
mysql> GRANT select, insert, delete,update
-> ON test.*
-> TO [email protected];
Query OK, 0 rows affected (0.00 sec)
This operation allows testuser to execute SELECT, INSERT, DELETE, and UPDATE queries on every table in the test database. Now we finish and exit the MySQL client program:
mysql> exit
Bye9!
1:Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;
2:2. Create a database named MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3:Select the database you created
mysql> USE MYSQLDATA; (If “Database changed” appears after pressing Enter,
it means the operation was successful!)
4:Check what tables currently exist in the 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 a record to the table
mysql> insert into MYTABLE values
(“hyq”,”M”);
8:Load data into the database table from a text file (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE
“D:/mysql.txt” INTO TABLE MYTABLE;
9:Command to import an .sql file (for example, D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10:Delete the table
mysql>drop TABLE MYTABLE;
11:Clear the table
mysql>delete from MYTABLE;
12:Update data in the table
mysql>update MYTABLE set sex=”f” where
name=’hyq’;
Below are some MySql management tips that I happened to come across online,
Excerpted from:http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html
In Windows, MySql exists as a service. Before using it, you should make sure this service has already been started. If it has not been started, you can use the net
start
mysql command to start it. In Linux, you can use the “/etc/rc.d/init.d/mysqld
start” command when starting it. Note that the person starting it should have administrator privileges.
A newly installed MySql includes a root account with an empty password and an anonymous account. This is a major security risk. For some important applications, we should improve security as much as possible. Here, the anonymous account should be deleted and a password should be set for the root account. You can do this with the following commands:
use mysql;
delete from User where User=””;
update User set
Password=PASSWORD(‘newpassword’) where
User=’root’;
If you want to restrict the login terminal used by a user, you can update the Host field of the corresponding user in the User table. After making the above changes, you should restart the database service. At this point, you can log in with commands similar to the following:
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
The above command parameters are only some of the commonly used ones; for details, please refer to the documentation. Here, mydb is the name of the database to log in to.
During development and in practical applications, users should not connect to the database using only the root user. Although using the root user is convenient for testing, it creates major security risks for the system and is not conducive to improving management skills. We should grant the most appropriate database privileges to the user used by an application. For example, 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 common ways to add a new user: one is to insert the corresponding data row into the User table while setting the appropriate privileges; the other is to create a user with certain privileges through the GRANT command. Common usage of GRANT is as follows:
grant all on mydb.* to [email protected] identified
by “password” ;
grant usage on *.* to [email protected] identified
by “password”;
grant select,insert,update on mydb.* to [email protected] identified
by “password”;
grant update,delete on mydb.TestTable to [email protected] identified
by “password”;
If you want to give this user the ability to manage their privileges on the corresponding objects, you can add the WITH
GRANT
OPTION option after GRANT. As for users added by inserting into the User table, the Password field should be updated and encrypted using the PASSWORD
function to prevent malicious people from peeking at the password. Users who are no longer needed should be removed, and users with excessive privileges should have those privileges revoked in a timely manner. Privileges can be revoked either by updating the corresponding fields in the User table or by using the REVOKE operation.
Below is an explanation of commonly used privileges that I obtained from other materials (www.cn-java.com):
Global administrative privileges:
FILE: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload access control tables, refresh logs, etc.
SHUTDOWN: Shut down the MySQL service.
Database/table/column privileges:
Alter: Modify existing tables (for example, add/delete columns) and indexes.
Create: Create new databases or tables.
Delete: Delete records from tables.
Drop: Delete tables or databases.
INDEX: Create or delete indexes.
Insert: Add records to tables.
Select: Display/search table records.
Update: Modify existing records in tables.
Special privileges:
ALL: Allowed to do anything (just like root).
USAGE: Only allowed to log in—nothing else is permitted.
———————
Commonly Used MYSQL Commands
Although many friends have installed mysql, they do not know how to use it. In this article, we will learn some commonly used MYSQL commands from aspects such as connecting to MYSQL, changing passwords, and adding users.
Although many friends have installed mysql, they do not know how to use it. In this article, we will learn some commonly used MYSQL commands from aspects such as connecting to MYSQL, changing passwords, and adding users.
I. Connecting to MYSQL
Format: mysql -hhost address -uusername －puser password
1. Example 1: Connect to MYSQL on the local machine
First open the DOS window, then enter the mysqlbin directory, and type the command mysql
-uroot
-p. After pressing Enter, you will be prompted to enter the password. If MYSQL has just been installed, the superuser root has no password, so just press Enter directly to enter MYSQL. The MYSQL prompt is: mysql>
2. Example 2: Connect to MYSQL on a remote host
Suppose the IP of the remote host is: 110.110.110.110, the username is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -uroot -pabcd123
(Note: there does not need to be a space between u and root; the same applies to the others.)
3. Command to exit MYSQL: exit (press Enter)
II. Changing the Password
Format: mysqladmin -uusername -pold password password new password
1. Example 1: Set the password for root to ab12. First enter the mysqlbin directory in DOS, then type the following command
mysqladmin -uroot -password ab12
Note: because root has no password initially, the -pold password part can be omitted.
2. Example 2: Change root’s password again to djg345
mysqladmin -uroot -pab12 password djg345
Commonly Used MYSQL Commands (Part 2)
I. Operating Tips
1. If you type a command and, after pressing Enter, realize you forgot to add a semicolon, you do not need to retype the whole command. Just type a semicolon and press Enter. In other words, you can split a complete command into several lines, and when finished, use a semicolon as the ending marker.
2. You can use the up and down arrow keys to recall previous commands. However, an older version of MYSQL I used before did not support this. I am currently using mysql-3.23.27-beta-win.
II. Display Commands
1. Display the list of databases.
show databases;
At the beginning there are only two databases: mysql and test. The mysql database is very important; it contains MYSQL system information. When we change passwords and add new users, we are actually operating on this database.
2. Display the data tables in a database:
use mysql； ／／open the database; anyone who has learned FOXBASE will certainly find this familiar
show tables;
3. Display the structure of a data table:
describe table name;
4. Create a database:
create database database name;
5. Create a table:
use database name；
create table table name (field definition list)；
6. Delete a database and delete a table:
drop database database name;
drop table table name；
7. Clear the records in a table:
delete from table name;
8. Display the records in the table:
select * from table name;
III. An example of creating a database and a table, and inserting data
drop database if exists school; //Delete SCHOOL if it exists
create database school; //Create the SCHOOL database
use school; //Open the SCHOOL database
create table teacher //Create the TEACHER table
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ‘Shenzhen’,
year date
); //End of table creation
//The following inserts fields
insert into teacher
values(”,’glchengang’,’Shenzhen No. 1 Middle School’,’1976-10-10′);
insert into teacher
values(”,’jack’,’Shenzhen No. 1 Middle School’,’1975-12-23′);
Note: In the table creation, (1) ID is set as a numeric field of length 3: int(3), and it is automatically incremented by one for each record: auto_increment, cannot be null: not
null, and is made the primary field: primary key
(2) NAME is set as a character field of length 10
(3) ADDRESS is set as a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char? That will have to wait for a later article.
(4) YEAR is set as a date field.
You can also type the above commands at the mysql prompt, but that is not convenient for debugging. You can write the above commands as-is into a text file, suppose it is named school.sql, then copy it to c:, and in DOS enter the mysqlbin directory, then type the following command:
mysql -uroot -ppassword < c:school.sql
If successful, it will leave a blank line with no display; if there is an error, there will be a prompt. (The above command has already been tested; you only need to remove the comments marked with // to use it).
IV. Importing text data into the database
1. The format the text data should follow: field data should be separated by the tab key, and null values should be replaced with n.
Example:
3 rose Shenzhen No. 2 Middle School 1976-10-10
4 mike Shenzhen No. 1 Middle School 1975-12-23
2. Data import command: load data local infile
“filename” into table table name;
Note: You had better copy the file to the mysqlbin directory, and first use the use command to open the database where the table is located
.
V. Backing up the database: (the command is executed in the mysqlbin directory under DOS)
mysqldump –opt school>school.bbb
Note: Back up the school database to the school.bbb file. school.bbb is a text file; the filename can be anything. Open it and you will discover something new.
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]
Explanation:
The parts enclosed in square brackets ([]) are optional, and the parts enclosed in curly braces ({}) indicate that one of them must be selected.
1. FROM clause
The FROM
clause specifies the source of the fields in the SELECT statement. After the FROM clause comes one or more expressions (separated by commas). These expressions can be a single table name, a saved query, or a composite result produced by
INNER JOIN, LEFT JOIN or RIGHT JOIN.
If the table or query is stored in an external database, specify its full path after the IN
clause.
Example: The following SQL statement returns all customers who have 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 predicate is not specified, the default is ALL.
Example: SELECT ALL FirstName,LastName
FROM Employees
(2) DISTINCT
If the selected fields contain the same data in multiple records, only one is returned.
(3) DISTINCTROW If there are duplicate records, only one is returned.
(4)
TOP displays a certain number of records from the beginning or end of the query results. It can also return a percentage of records, in which case the
TOP N PERCENT clause is used (where N indicates the percentage).
Example: Return the 5% of orders with the highest order amounts.
SELECT TOP 5 PERCENT*
FROM [ Order Details]
ORDER BY UnitPrice*Quantity*(1-Discount) DESC
3 Use the AS clause to assign aliases to fields
If you want to give the returned column a new title, or if a new value is generated after calculating or summarizing a field and you want to display it in a new column, use AS.
Example: Return the FirstName field with the alias NickName
SELECT FirstName AS NickName ,LastName ,City
FROM Employees
Example: Return a new column showing inventory value
SELECT ProductName ,UnitPrice ,UnitsInStock
,UnitPrice*UnitsInStock AS valueInStock
FROM Products
II. The WHERE clause specifies query conditions
1 Comparison Operators
Comparison operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!> Not greater than
!< Not less than
Example: return orders from January 1996
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate>#1/1/96# AND OrderDate<#1/30/96#
Note:
In Mcirosoft JET SQL,
dates are delimited by ‘#’. Dates can also be represented with the Datevalue() function. When comparing character data, add single quotes ”. Trailing spaces are ignored in comparisons.
Example:
WHERE OrderDate>#96-1-1#
can also be written as:
WHERE OrderDate>Datevalue(‘1/1/96’)
Use the NOT expression for negation.
Example: view orders after January 1, 1996
WHERE Not OrderDate<=#1/1/96#
2 Range (BETWEEN and NOT BETWEEN)
The BETWEEN …AND… operator specifies a closed interval to search.
Example: return orders from January 1996 to February 1996.
WHERE OrderDate Between #1/1/96# And #2/1/96#
3 List (IN, NOT IN)
The IN
operator is used to match any value in a list. The IN clause can replace a series of conditions connected by the OR clause.
Example: find all customers located 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 checks whether a field value containing string data matches a specified pattern.
Wildcards used with the LIKE operator
Wildcard Meaning
? Any single character
* Characters of any length
# A single digit between 0 and 9
[character list] Any value in the character list
[！character list] Any value not in the character list
– Specifies a character range; the values on both sides are the lower and upper limits respectively
Example: return customers whose postal code is between (171)555-0000 and (171)555-9999
SELECT CustomerID ,CompanyName,City,Phone
FROM Customers
WHERE Phone Like ‘(171)555-####’
Some LIKE operator patterns and their meanings
Pattern Meaning Does not match
LIKE ‘A*’ A followed by characters of any length Bc,c255
LIKE’5[*]’ 5*5 555
LIKE’5?5’ Any one character between 5 and 5 55,5wer5
LIKE’5##5’ 5235,5005 5kd5,5346
LIKE’[a-z]’ Any one character between a-z 5,%
LIKE’[!0-9]’ Any one character not between 0-9 0,1
LIKE’[[]’ 1,*
III. Sort results with the ORDER BY clause
The ORDER clause sorts query results by one or more (up to 16) fields, either ascending (ASC) or descending (DESC); the default is ascending. The ORDER clause is usually placed at the end of the SQL statement.
If multiple fields are defined in the ORDER clause, they are sorted according to the order of the fields.
Example:
SELECT ProductName,UnitPrice, UnitInStock
FROM Products
ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName
In the ORDER BY
clause, the position number of a field in the select list can be used instead of the field name, and field names and position numbers can be mixed.
Example: the following statement produces the same effect as above.
SELECT ProductName,UnitPrice, UnitInStock
FROM Products
ORDER BY 1 DESC , 2 DESC,3
IV. Use join relationships to implement multi-table queries
Example: find the names of suppliers and customers in the same city
SELECT Customers.CompanyName, Suppliers.ComPany.Name
FROM Customers, Suppliers
WHERE Customers.City=Suppliers.City
Example: find products and orders where the product stock is greater than the quantity ordered for the same product
SELECT ProductName,OrderID, UnitInStock, Quantity
FROM Products, [Order Deails]
WHERE Product.productID=[Order Details].ProductID
AND UnitsInStock>Quantity
Another method is to use the Microsof JET SQL-specific JNNER JOIN
Syntax:
FROM table1 INNER JOIN table2
ON table1.field1 comparision table2.field2
where comparision is the comparison operator used earlier in the WHERE clause.
SELECT FirstName,lastName,OrderID,CustomerID,OrderDate
FROM Employees
INNER JOIN Orders ON
Employees.EmployeeID=Orders.EmployeeID
Note:
INNER JOIN cannot join fields of the Memo, OLE Object, Single, or Double
data types.
Join multiple ON clauses in one JOIN statement
Syntax:
SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2 OR
ON table1.field3 compopr table2.field3
Also possible
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
Outer joins return more records, preserving unmatched records in the result; regardless of whether matching records satisfying the condition exist, all records on the other side must be returned.
FROM table [LEFT|RIGHT]JOIN table2
ON table1.field1comparision table.field2
Use a left join to create an outer join; the table on the left side of the expression will display all its data
Example: return all products regardless of whether there is any order quantity
SELECT ProductName ,OrderID
FROM Products
LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID
The difference between a right join and a left join is: regardless of whether there are matching records in the table on the left side, it returns all records from the left-side table.
Example: if you want to understand customer information and count the distribution of customers in each region, you can use a right join; even if a region has no customers, customer information should still be returned.
Null values do not match each other. Only through an outer join can you test whether a field in one of the joined tables has a null value.
SELECT *
FROM talbe1
LEFT JOIN table2 ON table1.a=table2.c
1 Use the Iif function in join queries to display null values as 0
Iif expression: Iif(IsNull(Amount,0,Amout)
Example: return a flag whether the order is greater than or less than ￥50.
Iif([Amount]>50,?Big order?,?Small order?)
V. Group and summarize query results
In SQL syntax, the GROUP
BY and HAVING clauses are used to summarize data. The GROUP
BY clause specifies which fields to group by, and after records are grouped, the HAVING clause filters those records.
Syntax of the GROUP BY clause
SELECT fidldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist [HAVING groupcriteria]]
Note: Microsoft Jet database Jet
cannot group Memo or OLE Object fields.
Null values in GROUP BY fields are used for grouping but cannot be omitted.
Null values are not calculated in any SQL aggregate function.
The GROUP
BY clause can contain up to ten fields, and the sort priority is arranged from left to right.
Example: in the employee table for the ‘WA’ region, after grouping by title, find all titles with more than one employee holding the same title.
SELECT Title ,Count(Title) as Total
FROM Employees
WHERE Region = ‘WA’
GROUP BY Title
HAVING Count(Title)>1
Aggregate functions in JET SQL
Aggregate function Meaning
SUM ( ) Sum
AVG ( ) Average
COUNT ( ) Number of records in the expression
COUNT (* ) Count of records
MAX Maximum value
MIN Minimum value
VAR Variance
STDEV Standard deviation
FIRST First value
LAST Last value
VI. Create parameter queries with a Parameters declaration
Syntax of the Parameters declaration:
PARAMETERS name datatype[,name datatype[, …]]
where name is the identifier of the parameter, and the parameter can be referenced through the identifier.
Datatype specifies the data type of the parameter.
When using it, place the PARAMETERS declaration before any other statement.
Example:
PARAMETERS[Low price] Currency,[Beginning date]datatime
SELECT OrderID ,OrderAmount
FROM Orders
WHERE OrderAMount>[low price]
AND OrderDate>=[Beginning date]
VII. Action queries
So-called action queries are actually a kind of operation query. They can perform fast and efficient operations on the database. Their purpose is based on select queries: select data that meets the conditions, then process the data in batches. Action queries include update queries, delete queries, append queries, and make-table queries.
1 Update query
The UPDATE clause can modify 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: increase the order amount of UK customers by 5%, and freight by 3%
UPDATE OEDERS
SET OrderAmount = OrderAmount *1.1
Freight = Freight*1.03
WHERE ShipCountry = ‘UK’
2 Delete query
The DELETE clause allows users to delete large amounts of outdated or redundant data.
Note: the object of a delete query is the entire record.
Syntax of the DELETE clause:
DELETE [table name.*]
FROM source table
WHERE criteria
Example: delete all orders before 1994
DELETE *
FROM Orders
WHERE OrderData<#94-1-1#
3 Append query
The INSERT clause can append one record or a set of records to the end of one or more tables.
The INTO clause specifies the table that receives the new records
The valueS keyword specifies the data values contained in the new records.
Syntax of the INSERT clause:
INSETR INTO destination table or query(field1,field2,…)
valueS(value1,value2,…)
Example: add a customer
INSERT INTO Employees(FirstName,LastName,title)
valueS(‘Harry’,’Washington’,’Trainee’)
4 Make-table query
You can copy all records meeting the conditions into a new table at one time. This is usually used to make backups or copies of records, or as the basis for reports.
The SELECT INTO clause is used to create a make-table query. Syntax:
SELECT field1,field2,…
INTO new table[IN external database]
FROM source database
WHERE criteria
Example: create an archive backup for orders
SELECT *
INTO OrdersArchive
FROM Orders
VIII. Union query
The UNION operation can combine the results of multiple queries into one result set for display.
General syntax of the UNION operation:
[table]query1 UNION [ALL]query2 UNION …
Example: return the names and cities of all suppliers and customers in Brazil
SELECT CompanyName,City
FROM Suppliers
WHERE Country = ‘Brazil’
UNION
SELECT CompanyName,City
FROM Customers
WHERE Country = ‘Brazil’
Note:
By default, the UNION clause does not return duplicate records. If you want to display all records, you can add the ALL option
The UNION operation requires the queries to have the same number of fields. However, the field data types do not have to be the same.
Each query parameter can use a GROUP BY clause or a HAVING
clause for grouping. To display the returned data in a specified order, you can use the OREER
BY clause at the end of the last query.
IX. Crosstab query
A crosstab query can calculate sums, averages, counts, or other aggregate calculations on data. The data is grouped by two kinds of information: one displayed on the left side of the table, and the other at the top of the table.
Microsoft Jet SQL uses the TRANSFROM statement to create a crosstab query. Syntax:
TRANSFORM aggfunction
SELECT statement
GROUP BY clause
PIVOT pivotfield[IN(value1 [,value2[,…]]) ]
Aggfounction refers to an SQL aggregate function,
The SELECT statement selects the fields used as headings,
GROUP BY grouping
Description:
Pivotfield
is the field or expression used to create column headings in the query result set, and the optional IN clause limits its values.
value represents the fixed values used to create column headings.
Example: show the number of orders taken by each employee in each quarter of 1996:
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’
X. Subquery
A subquery can be understood as a nested query. A subquery is a SELECT statement.
1 Compare the value of an expression with the single value returned by a subquery
Syntax:
expression comparision [ANY|ALL|SOME](subquery)
Description:
ANY
and SOME predicates are synonyms, used together with comparison operators (=,<,>,<>,<=,>=). They return a Boolean value, True or
False. ANY means that the expression is compared one by one with a series of values returned by the subquery; as long as one comparison produces a True result, the ANY test returns
True (that is, the result of the WHERE clause), and the current record corresponding to that expression will enter the result of the main query. The ALL test requires that comparisons between the expression and the series of values returned by the subquery all produce
True before it returns True.
Example: the main query returns all products whose unit price is higher than the unit 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 Check whether the value of an expression matches any value in the set of values returned by a 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 records
Syntax:
[NOT]EXISTS (subquery)
Example: use EXISTS to retrieve customers from the UK
SELECT ComPanyName,ContactName
FROM Orders
WHERE EXISTS
(SELECT *
FROM Customers
WHERE Country = ‘UK’ AND
Customers.CustomerID= Orders.CustomerID)