MySQL frequently used commands

MySQL frequently used commands show databases; database create database name; create a database use databasename; to select database drop database name directly delete the database, do not remind the show TABLES; display table describe tablename; specific table structure select distinct plus remove duplicate field mysqladmin drop databaseName delete the database before tips.
Display the current MySQL version and the current date select version (), CURRENT_DATE;
Modify the mysql root password:
shell> mysql-h localhost-u root-p / / login mysql> update user set password = the password (xueok654123) WHERE User = ‘root’;
mysql> FLUSH privileges / / refresh the database mysql> use dbname; open the database:
mysql> show databases; database mysql> show tables; Show all tables in the database mysql: first where USE mysql; then MySQL> describe user; displayed in the user table in the mysql database table column information);
grant
The create the user firstdb (password to firstdb) and databases, and give permission in firstdb database mysql> create database firstdb;
mysql> grant all on firstdb. * to firstdb identified by ‘firstdb’
Will automatically create a user firstdb
mysql default is the local host is localhost, the corresponding IP address is 127.0.0.1, so you use your IP address to log in to be wrong, if you want to use your IP address to log must first be authorized with the grant command.
mysql> grant all on *. * to identified by “123456”;
Description: between grant and on a variety of privileges, such as: insert, select, update, etc. on the database and table names after the first * means that all databases, the second * table root can be changed to your user name, @ with the domain name or IP address, identified by the back of the login password can be omitted, the default password is empty password.
drop database to firstdb that;
Create a connect to the server from anywhere a full super-user, but you must use a password to do something this mysql> grant all privileges on *. * To identified by ‘something’ with
Increase new user format: Grant select on database * to username @ log into the host identified by “password”
GRANT ALL PRIVILEGES ON *. * TO IDENTIFIED BY ‘something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *. * TO “IDENTIFIED BY ‘Something’ WITH GRANT OPTION;
Remove authorized:
mysql> revoke all privileges on *. * from “;
mysql> delete from user where user = “root” and host = “%”;
mysql> flush privileges;
Create a user custom in particular client it363.com login, you can access a particular database fangchandb
mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom@it363.com identified by ‘passwd’
Rename table:
mysql> alter table t1 rename t2;
mysqldump
Backup database shell> mysqldump-h host-u root-p dbname> dbname_backup.sql
Restore the database shell> mysqladmin-h myhost-u root-p create dbname
shell> mysqldump-h host-u root-p dbname <dbname_backup.sql
If you want to unloading create table instruction, the command is as follows:
shell> mysqladmin-u root-p-d databasename> a.sql
If you just want to unload the SQL command to insert data, without the need to create the table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename> a.sql
So if I only want the data, but you do not want what SQL commands, how does it work?
mysqldump-T. / phptest driver
Of these, only specified the-T parameter before unloading a plain text file, which means that the unloading data directory, / represents the current directory, and mysqldump the same directory. If you do not specify a driver table will be discharged to the data of the entire database. Each table will generate two files, one for sql file contains a built tables. Another txt file contains only data, and not the sql command.
Queries can be stored in a file and tell mysql to read from the file query instead of waiting for keyboard input. Available shell type redirect utility to get the job done. For example, if stored in the file my_file.sql queries, these queries can be executed as follows:
For example, if you want to write-ahead will be built table statement in sql.txt
mysql> mysql-h myhost-u root-p
Supported character set Mysql5.0 MySQL character set control to do a fine job, can be divided into the database, table, and field-level (and ORACLE). The last time I changed the character set of the database level, the table sysuser no impact, so change the character set but the same can not be inserted in Chinese.
Drop TABLE IF EXISTS `firstdb`. `Users`;
Create TABLE `firstdb`. `Users` (
`Id` int (11) NOT NULL auto_increment,
`Username` varchar (40) default NULL,
`Birthday` date default NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = gb2312;
When compiling MySQL, specify a default character set, the character set is latin1;
Install MySQL, a default character set specified in the configuration file (my.ini), if not specified, this value is inherited from the compile-time specified;
Start mysqld command line parameters to specify a default character set, if not specified, this value is inherited from the configuration file;
The this time character_set_server is set to the default character set;
When you create a new database, unless explicitly specified, the database character set is the default setting, character_set_server;
When a database is selected, character_set_database is set as the default character set of the database;
When you create a table in the database, the table default character set is set to character_set_database is the default character set of the database;
When a column in the table set, unless otherwise expressly provided in this column default character set is the default character set;
This character set is the character set of the data is actually stored in the database using mysqldump out is the character set under too bad; Query Browser1.1 to support Chinese input, you can use the notebook was written, and then copy over the implementation of the update firstdb.users set username = ” where id = 3;
MYSQL commonly used commands to export the entire database mysqldump-u username-p – default-character-set = latin1 database export file name (database default encoding is latin1)
mysqldump-u wcnc-p smgp_apps_wcnc> wcnc.sql
Export a table mysqldump-u username-p database watches name> Export 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 data-add-drop-table add a drop table before each create statement
4 into the database A: common source command console, enter the mysql database
Such as mysql-u root-p
mysql> use database and then use the source command behind parameters of script files (such as used here. sql)
mysql> source wcnc_db.sql
B: Using mysqldump command mysqldump-u username-p dbname <filename.sql
C: Using the mysql command mysql-u username-p-D dbname <filename.sql
Start and exit, to enter MySQL: start the MySQL Command Line Client (DOS interface for MySQL), directly enter the password when the installation. The prompt is: mysql>
2, exit MySQL: quit or exit
Second, the library operation, create a database command: CREATE database <database name>
The named xhkdb For example: Create a database mysql> create database xhkdb;
2, all database commands: show databases (Note: the last s)
mysql> show databases;
3, delete the database command: drop database <database name>
For example: delete named xhkdb database mysql> drop database xhkdb;
4, connect to the database command: USE <database name>
For example: If the database xhkdb exists, try to access it:
mysql> use xhkdb;
Screen Tip: Database changed
5, the currently used database mysql> select database ();
6, this database contains a table:
mysql> show tables; (Note: The last s)
Table operation, the operation should be connected a database to create the table command: the create table <table name> (<field name> <type> .. <field name> <type>]);
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 a 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 table command: drop table <table name>
For example: Delete table named MyClass table mysql> drop table MyClass;
4, insert data command: insert into <table name> [(<field name 1> .. <field name n>])] values ??(value 1)
(N)]
For example, to insert two records in Table MyClass two records: the No. 1 named Tom score of 96.45, No. 2 named Joan score of 82.99, number 3 named Wang’s results was 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), the query all rows command: select <field 1, field 2, …> from <table name> where <expression>
For example: View all the data in the table MyClass mysql> select * from MyClass;
2), the query first few lines of data such as: Table MyClass first 2 rows of data 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 expressions such as: Delete table MyClass No. 1 mysql> delete from MyClass where id = 1;
7, modify the data in the table: update table name set field = new value … where conditions mysql> update MyClass set name = ‘Mary’ where id = 1;
7, to increase the field in the table:
Command: alter table table name add field type;
In example: Table MyClass Add field passtest, type int (4) default value is 0
mysql> alter table MyClass add passtest int (4) default ‘0 ‘
8, change the name of the table:
Command: Rename the table to the original table name to the new name of the table;
For example: in the table MyClass name changed to YouClass
mysql> rename table MyClass to YouClass;
Update the field content update table set field name = new content update table set field name = replace (field name, ‘old’ and ‘new’);
The article added in front of the four spaces Update article set Content = concat (”, content);
The field the type 1.INT [(M)] type: normal size integer type 2.Double [(M, D)] type [ZEROFILL]: normal size (double-precision) floating-point the digital type 3.DATE Date Type: support 1000-01-01 to 9999-12-31. MySQL to YYYY-MM-DD
Format displays DATE values, but allows you to use string or numeric value is assigned to the type of the DATE column 4.CHAR (M): fixed-length string type, when storage is always filled with spaces on the right to the specified length 5.BLOB TEXT type, maximum length of 65535 (2 ^ 16-1) characters.
The 6.VARCHAR type: variable-length string type 5 into the database table (1) create a sql file (2) first generated a library auction.c: mysqlbin> mysqladmin-u root-p creat
auction will be prompted to enter a password, and then successfully created.
(2) the import auction.sql file c: mysqlbin> mysql-u root-p Auction <auction.sql,.
Through the above operation, you can create a database of auction and one of the tables auction
.
Modify the database (1) an increase in the mysql table field:
alter table dbname add column userid int (11) not null primary key
auto_increment;
The, Table dbname a field userid of type int (11).
7.mysql database authorized mysql> grant select, insert, delete, create, drop
on *. * (or test * / user * / ..)
to username @ localhost
IDENTIFIED BY ‘password’;
Such as: create a new user account so that you can access the database, you need to proceed as follows:
mysql> grant usage
-> ON test.
-> TO ;
Query OK, 0 rows affected (0.15 sec)
Since then create a new user called: testuser, the user can only connect from localhost to the database and can be connected to the test database. Next, we must specify what you can do testuser this user:
mysql> GRANT select, insert, delete, update
-> ON test.
-> TO ;
Query OK, 0 rows affected (0.00 sec)
This makes testuser can be performed on a table in a test database Select, Insert, and Delete, and Update query operation. Now we finish and exit the MySQL client program:
mysql> exit
Bye9!
1: Use the SHOW statement to find out what database exists on the server:
mysql> SHOW DATABASES;
2:2, create a database MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3: Select you create a database mysql> USE MySQLdata; (Database changed, press the Enter key instructions!)
4: View the database table mysql> SHOW TABLES;
5: Create a database table mysql> CREATE TABLE mytable (name varchar (20), sex CHAR (1));
6: shows the structure of the table:
mysql> DESCRIBE MYTABLE;
7: records added to the table mysql> insert into mytable values ??(“hyq”, “M”);
8: text data into a database table (for example the D :/ mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D :/ mysql.txt” INTO TABLE MYTABLE;
9: Importing sql file commands (for example the 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 table data mysql> update mytable set sex = “f” where name = ‘hyq’;
The following is inadvertently see the use of MySQL in the network management experience,
Taken from:
html
Exist prior to use to ensure that this service has been launched, not started available net Start mysql command to start a service in Windows in MySQL. Available in the Linux start “/ etc / rc.d / init.d / mysqld
start “command, note that the start should have administrator privileges.
Just installed MySql contains a root account with a blank password and an anonymous account, which is a big security risk for some important applications security should be to maximize, where the anonymous account should delete the root account Set the password be ordered as follows:
use mysql;
delete from User where User = “”;
update User set Password = PASSWORD (‘newpassword’) where User = ‘root’;
If you want to restrict user login terminal, the user can update the User table in the Host field,
After the above changes should restart the database service available, log on at this time similar to the following command:
mysql-uroot-p;
mysql-uroot-pnewpassword;
mysql mydb-uroot-p;
mysql mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, detailed reference documentation. Where mydb is the name of the database you want to log.
During the development and practical application, the user should not only use the root user to connect to the database, use the root user test is very convenient, but the system will bring significant security risks, is not conducive to management techniques improve. We give the user an application used to give the most appropriate database permissions. Such as a data insertion, the user should not be given to delete data. MySQL user management through the User table, there are two ways to add new users, and set the appropriate permissions in the User table into the corresponding data line; GRANT command to create a permission users. GRANT common usage is as follows:
Grant all on mydb * to identified by “password”;
Grant usage on *. * to identified by “password”;
Grant select, insert, update on the mydb * to identified
by “password”;
of the Grant update, delete on mydb.TestTable to identified
by “password”;
To give the user gives his authority on the corresponding object management capabilities can be added behind the GRANT WITH
GRANT OPTION option. Inserted in the User table to add user Password field application PASSWORD
The function update encryption, see the password to prevent the evil designs stolen. For those who have not should be given clear, user permissions overspill should be timely recovery of permissions recovery permissions can update the User table fields, you can use the REVOKE operation.
I obtained from other data ( ) interpretation of the common permission is given below :
Global administrative privileges:
FILE: read and write files in the MySQL server.
PROCESS: display or kill service threads that belong to other users.
RELOAD: Reload access control table, refresh the log, and so on.
Shutdown: Close MySQL service.
Database / data tables / columns of data permissions:
Alter: To modify an existing data table (for example, add / remove columns) and indexes.
Create: create a new database or data table.
Delete: delete table records.
Drop: delete the data table or database.
INDEX: create or delete indexes.
Insert: increase records in the table.
Select: / search records in the table.
Update: modify existing records in the table.
Special privileges:
ALL: allow to do anything (and root).
USAGE: only allowed to log – Other not allowed to do.
———————
MYSQL commonly used commands have many friends installed mysql but do not know how to use it. In this article we to learn from the connection MYSQL, change passwords, increase user some MYSQL common command.
There are a lot of friends installed mysql but do not know how to use it. In this article we to learn from the connection MYSQL, change passwords, increase user some MYSQL common command.
First, the connection MYSQL
Format: mysql-H host address-u username-p user password 1, Example 1: Connecting to MySQL on the machine
First, open the DOS window, then enter directory mysqlbin, and then type the command mysql-uroot-p
Enter prompt you lose the password, If you have just installed MYSQL, super-user root without a password, so you can directly enter into the MYSQL, MYSQL prompt is: mysql>
Example 2: Connecting to MySQL on remote host
Assuming that the remote host’s IP: 110.110.110.110, user called root password to abcd123. Type the following command:
mysql-h110.110.110.110-uroot-pabcd123
(Note: u and the root can not have spaces, other)
3, exit MYSQL command: exit (Enter)
Second, modify the password format: mysqladmin-u username-p password old password new password 1, Example 1: to add a root password ab12. First into the directory mysqlbin under DOS, and then type the following command mysqladmin-uroot-password ab12
Note: Since the beginning of root has no password,-p old password one can be omitted.
2, Example 2: then the root password to djg345
mysqladmin-uroot-pab12 password djg345
MySQL frequently used commands (below)
Operating skills, if you hit command, a carriage return after the discovery forget the semicolon, you do not need to retype the command, you can make a semicolon Enter. This means that you can put a complete command into several lines to fight, finished with a semicolon marks the end OK.
2, you can use the cursor up and down keys to recall the previous command. But before I used an older version of MYSQL is not supported. I am using the mysql-3.23.27-beta-win.
Display command to display the list of databases.
show databases;
Beginning when the two databases: mysql, and test. the mysql library very important it MYSQL system information, change your password and add users, in fact, use this library to operate.
2, the display of the data in the table in the library:
USE mysql; / / open the library, learned FOXBASE certainly not familiar with it show tables;
3, shows the data structure of the table:
describe table;
4, building a database:
create database library name;
5, the construction of the table:
use the library name;
create table table name (field set list);
6, delete the database and delete the table:
drop database library name;
drop table table name;
7 records in the table empty:
delete from table name;
8 records in the table:
select * from table name;
Third, a database and build tables and insert data into an instance of drop database if exists school; / / If there SCHOOL deleted the create database School; / / create a library SCHOOL
Use school; / / open the library SCHOOL
create table teacher / / create table teacher
(
id int (3) auto_increment not null primary key,
name char (10) not null,
Address varchar (50) default ‘Shenzhen’,
year date
); / / End of the construction of the table / / Insert Field insert INTO Teacher values ??(”, ‘glchengang’, ‘Shenzhen’, ‘1976 -10-10 ‘);
INSERT INTO Teacher values ??(”, ‘jack’, ‘Shenzhen’, ‘1975 -12-23 ‘);
Note: (1) in the construction of the table the ID is set to numeric field with a length of 3 automatically incremented by one: int (3) and allow it to each record: auto_increment and can not be null: not null and let him become the primary key of the primary field
(2) NAME character field with a length of 10 (3) ADDRESS character field of length 50, and the default value Shenzhen. varchar and char
What difference does it say, only wait for a future article.
(4) YEAR set the date field.
If you’re in the mysql prompt, type the command above can be, but is not convenient debugging. You can be written as the above command is assumed to be school.sql a text file, and then copied to the C: \ in DOS mode to enter the directory \ mysql \ bin, and then type the following command:
mysql-uroot-p password <c: \ school.sql
If successful, vacated his no display; any errors will be prompt. (The above command has been commissioning, as long as you / / uncomment to use).
Fourth, the text data to the database, text data should be consistent with the format: field data separated by a tab key, null value \ n instead.
Example:
3 Rose Shenzhen Second 1976-10-10
Mike Shenzhen 1975-12-23
2, data incoming command load data local infile “file name” into table table name;
Note: you’d better copy the files to the \ mysql \ bin directory, and first hit the table where the library with the use command.
Backup database: (command in DOS \ mysql \ bin directory)
mysqldump – opt school> school.bbb
Note: backup the database School to school.bbb file school.bbb is a text file,
The file name of any check, open look you have found.
A Select statement of the complete syntax:
Select [ALL | DISTINCT | DISTINCTROW | TOP]
{* | Talbe. * | [Table.] Field1 [AS alias1] [, [table.] Field2 [AS alias2] [, …]]}
FROM tableexpression [, …] [IN externaldatabase]
[Where …]
[GROUP BY …]
[HAVING …]
[ORDER BY …]
[The WITH OWNERACCESS OPTION]
Description:
Part of said enclosed in brackets ([]) are optional, curly braces ({}) enclose part is said to have to choose one of them.
1 FROM clause The FROM clause specifies the source of the field in the Select statement. FROM clause is followed by one or more expressions (separated by commas), in which the expression can be a single table name, saved queries or INNER
JOIN, LEFT JOIN or RIGHT JOIN composite results. If the table or stored in an external database, after the IN clause to specify the full path.
Example: The following SQL statement returns all customer orders:
Select orderID, Customer.customerID
FROM orders Customers
Where orders.CustomerID = Customers.CustomeersID
2 ALL, DISTINCT, DISTINCTROW, TOP predicate (1) ALL to return all the records that meet the conditions of the SQL statement. If the predicate is not specified, the default is ALL.
Example: Select ALL FirstName, LastName
FROM Employees
If there are multiple records (2) DISTINCT select the field data, returns only one.
(3) DISTINCTROW If there are duplicate records, returns only one (4) TOP query the head and tail of certain records. Also return the percentage of records, which is to use TOP N
PERCENT clause (where N denotes the percentage)
Example: Back to the maximum order of 5% of the order amount Select TOP 5 PERCENT *
From [order Details]
orDER BY UnitPrice * Quantity * (1-Discount) DESC
AS clause for a field alias if you want to take a new title for the returned column, or after a calculation or summary of the field, resulting in a new value, put it in a new column AS reserved.
Example: return to the FirstName field alias NickName
Select FirstName AS NickName, LastName, City
FROM Employees
Example: Return a new inventory value Select ProductName, UnitPrice, UnitsInStock, UnitPrice * UnitsInStock AS
valueInStock
FROM Products
Comparison Operators Comparison operators meaning of the WHERE clause specifies query = equal to> greater than <less than> = greater than or equal to <= less than or equal to <> not equal to> greater than <less than Example: Back to January 96 order Select orderID, CustomerID, orderDate
FROM orders
Where orderDate> # 1/1/96 # AND orderDate <# 1/30/96 #
Note:
Mcirosoft JET SQL, date delimitation of the ‘#’. Date can also use the DateValue () function instead.
In comparison to the character data to be enclosed in single quotes”, trailing spaces in the comparison is ignored.
Example:
Where orderDate> # 96-1-1 #
Can also be expressed as:
WHERE OrderDate> DateValue (‘1 / 1/96 ‘)
NOT expression negates.
Example: See the order after January 1, 1996 Where Not orderDate <= # 1/1/96 #
2 range (BETWEEN and NOT BETWEEN)
BETWEEN … AND … operator to specify you want to search a closed interval.
Example: Back orders from 96 January to 96 February.
Where orderDate Between # 1/1/96 # And # 2/1/96 #
3 list (IN, NOT IN)
IN operator is used to match any of the values ??in the list. IN clause instead of the OR clause connected to a series of conditions.
Example: To find all of our customers live in London, Paris or Berlin Select CustomerID, CompanyName, ContactName, City
FROM Customers
Where City In (‘London’, ‘Paris’, ‘Berlin’)
4 pattern matching (LIKE)
LIKE operator test a string data field value matches a specified pattern.
LIKE operator wildcard wildcard meaning? Any single character * any length character # single digits between 0 and 9 [list of characters in the character list any of the values ??[! List of characters is not in the list of characters any value – the specified range of characters on both sides of the value of their respective upper and lower cases: Back zip code (171) 555-0000 (171) 555-9999 between customers Select CustomerID, CompanyName, City, Phone
FROM Customers
Where Phone Like ‘(171) 555 – # # # #’
LIKE operator does not meet the meaning of style and meaning of style LIKE ‘A *’ A followed by any character length BC, C255
LIKE’5 [*] ‘5 * 5 555
LIKE’5? 5 ‘5 and 5 between any of the characters 55,5 wer5
LIKE’5 # # 5 ‘5235,5005 5kd5, 5346
LIKE ‘[az]’ the AZ between any one character 5%
LIKE ‘[0-9]’ non 0-9 between any one character 0,1
LIKE ‘[[]’ 1, *
Field sort query results by one or more (up to 16) with the ORDER BY clause to sort results order clause, is ascending (ASC) or descending (DESC), the default is ascending. The ORDER clause usually placed the last of the SQL statement.
The ORDER clause defines multiple fields, sorted in accordance with the order of the field.
Example:
Select ProductName, UnitPrice, UnitInStock
FROM Products
orDER BY UnitInStock DESC, UnitPrice DESC, ProductName
ORDER BY clause in the choice of field position number instead of the field names in the list, can be mixed field name and location number.
Example: The following statement produces the same effect as the above.
Select ProductName, UnitPrice, UnitInStock
FROM Products
orDER BY 1 DESC, 2 DESC, 3
Use the connection relationship to achieve multi-table query example: to find the name Select suppliers and customers in the same city Customers.CompanyName Suppliers.ComPany.Name
FROM Customers, Suppliers
Where Customers.City = Suppliers.City
Example: to identify the number of product inventory is greater than the same product orders and order Select ProductName, OrderID, UnitInStock, Quantity
FROM Products, [Order Deails]
Where Product.productID = [Order Details]. ProductID
AND UnitsInStock> Quantity
Another method is unique with Microsof JET SQL JNNER JOIN
Syntax:
FROM table1 INNER JOIN table2
ON table1.field1 comparision table2.field2
Which comparision front WHERE clause is used in the comparison operator.
Select FirstName, lastName, OrderID, CustomerID, OrderDate
FROM Employees
INNER JOIN orders ON Employees.EmployeeID = Orders.EmployeeID
Note:
INNER JOIN can not connect Memo OLE Object Single Double data type field.
In a JOIN statement in connection multiple ON clauses 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
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 connection to return more records retained in the results do not match the record, no matter it exists or not meet the conditions of record must return all the records of the other side.
FROM table [LEFT | RIGHT] JOIN table2
ON table1.field1comparision table.field2
Left connected to create an external connection, the expression table on the left will display all cases: whether or not to set the volume to return all goods Select the ProductName, OrderID
FROM Products
LEFT JOIN orders ON Products.PrductsID = Orders.ProductID
Right left difference is that there is no matching record: Regardless of the left list, it returns all records from the table on the left.
Example: If you want to understand the customer’s information and statistical distribution of customers in various regions, can be connected with a right, even if a region has no customers, but also returned to the client.
Null values ??do not match each field of a table can be connected through the outer join to test for null values.
Select *
FROM talbe1
LEFT JOIN table2 ON table1.a = table2.c
Join queries using Iif function to achieve a value of 0 empty value IIF expression: IIf (IsNull (Amount, 0, AMOUT)
Example: Regardless of orders greater than or less than ¥ 50, and must return a flag.
Iif ([Amount]> 50,? Big order?,? Small order?)
Grouping and summary of results in the syntax of SQL, GROUP BY and HAVING clause is used to summarize the data. GROUP BY clause specified in accordance with which several fields to group records into groups with the HAVING clause to filter records.
The the syntax Select fidldlist of the GROUP BY clause
FROM table
Where criteria
[GROUP BY groupfieldlist [HAVING groupcriteria]]
Note: The Microsoft Jet database Jet can not be grouped Memo or OLE Object field.
Null value in the GROUP BY field for grouping, however, can not be omitted.
Null values ??are not calculated in any SQL aggregate functions.
GROUP BY clause can be up with ten fields, the sort priority arranged left to right.
Example: ‘WA’ employees table grouped by title, to identify the number of employees with the same title more than 1 title.
Select Title, COUNT (Title) as Total
FROM Employees
Where Region = ‘WA’
GROUP BY Title
HAVING Count (title)> 1
Jet SQL in the accumulation function gathered function significance SUM () the summation AVG () average COUNT () the number of records in the expression COUNT (*) calculate the number of records MAX MAX MIN minimum VAR variance the STDEV standard error FIRST values ??LAST last value 6. Parameters statement to create a parameter query parameters declaration syntax:
PARAMETERS name datatype [, name datatype [, …]]
Where name is the identifier of the parameter can be referenced by identifier parameter.
Datatype description of the data type of the parameter.
Use the PARAMETERS statement should be placed before any other statements.
Example:
PARAMETERS [Low price] Currency, [Beginning date] datatime
Select orderID, OrderAmount
FROM orders
Where orderAMount> [low price]
AND OrderDate> = [Beginning date]
Function queries the so-called functional query, in fact, is an action query, it can be fast and efficient operation of the database select query for the purpose, the selection of a qualified data, batch data function query includes update query, delete query, add a query, and make-table query.
An update query the Update clause can change the data in one or more tables It is also possible to change the value of multiple fields at the same time.
Update query syntax:
UPDATE table name SET new value Where criteria Example: UK customers given a 5% increase in volume, cargo volume increased by 3%
Update OEDERS
SET orderAmount = orderAmount * 1.1
Freight = Freight * 1.03
Where ShipCountry = ‘UK’
Delete query the Delete clause allows the user to delete a large number of obsolete or redundant data.
Note: The the delete query object is the entire record.
Delete clauses syntax:
Delete [table name. *]
The FROM source table Where criteria Example: To delete all of 94 years ago, the order Delete *
FROM orders
Where orderData <# 94-1-1 #
3 append query Insert clause one or a group of records can be appended to the end of the one or more tables.
INTO clause specifies to accept a new record in the table VALUES keyword to specify the data values ??included in the new record.
Insert clauses syntax:
INSETR INTO destination table or query (Field 1, Field 2, …)
values ??(value 1, value 2, …)
For example: add a client INSERT INTO Employees (FirstName, LastName, title)
valueS (‘Harry’, ‘Washington’, ‘Trainee’)
4-table query once all the records meet the conditions copied to a new table. Usually made a backup or copy of the record or as the basis of the report.
SELECT INTO clause is used to create a make-table query syntax:
Select Field 1, Field 2, …
INTO table [IN external database]
The FROM source database Where criteria Example: order to create an archive backup Select *
INTO ordersArchive with
FROM orders
Eight union query the UNION operator the results of multiple queries can be combined into a result set.
The UNION operator of general syntax:
[Table] query UNION [ALL] query UNION …
Example: return the name and city of the Brazilian suppliers and customers Select CompanyName, City
FROM Suppliers
Where Country = ‘Brazil’
UNION
Select CompanyName, City
FROM Customers
Where Country = ‘Brazil’
Note:
Case of default, the UNION clause does not return duplicate records. If you want to display all the records, you can add the the ALL option UNION operator requires query with the same number of field, but the field data type does not have to be the same.
Each query parameters can be grouped using the GROUP BY clause or HAVING clause in the order specified in order to display the returned data, you can use the end of the last query the OREER BY clause.
IX. Cross Sourcing cross-check sum of the data can be, on average, count or other sum calculation method, these data are grouped by two types of information: a display in the left portion of the table, and the other displayed in the top of the table.
Microsoft Jet SQL syntax to create a crosstab query with the TRANSFROM statement:
TRANSFORM aggfunction in
Select statement GROUP BY clause PIVOT in pivotfield are [IN (value1 [, value2 [, …]])]
Aggfounction refers to the SQL accumulation function,
Select statement as the title of the field,
GROUP BY grouping Description:
Pivotfield field or expression used in the query result set to create column headings, with the optional IN clause to limit its value.
The representative of value to create a fixed value of the column headings.
Example: Show the number of orders accepted by every 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. A subquery can be understood as a set of query sub-query is a Select statement.
The value of the expression subquery returns a single value compared syntax:
Expression comparision [ANY | ALL | SOME (subquery)
Description:
ANY and SOME predicate is synonymous with comparison operators (=, <>, <>, <=,> =) used in conjunction with returns a Boolean value True or False.ANY of the mean expression returned by the subquery value of a series of by-side comparison, as long as a True produce results, the ANY test the return value of True (both the WHERE clause of the results), corresponding to the current record of the expression will enter the main query results All test the expression sub-query returns a series of value comparison produces a True result, came back to return the value True.
Example: main query returns unit price than any other discount greater than or equal to 25% of the unit price of all products Select * FROM Products
Where UnitPrice> ANY
(Select UnitPrice FROM [Order Details] Where Discount> 0.25)
2 Check the value of the expression matches a value in a set of values ??returned by the subquery syntax:
[NOT] IN (subquery)
Example: inventory value greater than or equal to 1000.
Select ProductName FROM Products
Where ProductID IN
(The Select PrdoctID FROM [Order Details]
Where UnitPrice * Quantity> = 1000)
3 detection sub-query returns any record syntax:
[NOT] EXISTS (subquery)
Example: EXISTS to retrieve customers Select ComPanyName, ContactName
FROM orders
Where EXISTS
(Select *
FROM Customers
Where Country = ‘UK’ AND
Customers.CustomerID = orders.CustomerID)
1: Use the SHOW statement to find out what database exists on the server:
mysql> SHOW DATABASES;
2:2, create a database MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3: Select you create a database mysql> USE MySQLdata; (Database changed, press the Enter key instructions!)
4: View the database table mysql> SHOW TABLES;
5: Create a database table mysql> CREATE TABLE mytable (name varchar (20), sex CHAR (1));
6: shows the structure of the table:
mysql> DESCRIBE MYTABLE;
7: records added to the table mysql> insert into mytable values ??(“hyq”, “M”);
8: text data into a database table (for example the D :/ mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D :/ mysql.txt” INTO TABLE MYTABLE;
9: Importing sql file commands (for example the 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 table data mysql> update mytable set sex = “f” where name = ‘hyq’;
The following is inadvertently see the use of MySQL in the network management experience,
Taken from:
Exist prior to use to ensure that this service has been launched, not started available net Start mysql command to start a service in Windows in MySQL. Available in the Linux start “/ etc / rc.d / init.d / mysqld start” command, note that the start should have administrator privileges.
Just installed MySql contains a root account with a blank password and an anonymous account, which is a big security risk for some important applications security should be to maximize, where the anonymous account should delete the root account Set the password be ordered as follows:
use mysql;
delete from User where User = “”;
update User set Password = PASSWORD (‘newpassword’) where User = ‘root’;
If you want to restrict user login terminal, you can update the user in the User table Host field, during the above changes should restart the database service available, log on at this time similar to the following command:
mysql-uroot-p;
mysql-uroot-pnewpassword;
mysql mydb-uroot-p;
mysql mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, detailed reference documentation. Where mydb is the name of the database you want to log.
During the development and practical application, the user should not only use the root user to connect to the database, use the root user test is very convenient, but the system will bring significant security risks, is not conducive to management techniques improve. We give the user an application used to give the most appropriate database permissions. Such as a data insertion, the user should not be given to delete data. MySQL user management through the User table, there are two ways to add new users, and set the appropriate permissions in the User table into the corresponding data line; GRANT command to create a permission users. GRANT common usage is as follows:
Grant all on mydb * to identified by “password”;
Grant usage on *. * to identified by “password”;
Grant select, insert, update on mydb * to identified by “password”;
Grant update, the delete on mydb.TestTable to identified by “password”;
To give the user gives his authority on the corresponding object management capabilities can be added behind the GRANT WITH GRANT OPTION option. Inserted in the User table to add user Password field application PASSWORD function to update encryption to prevent misconduct, theft see the password. For those who have not should be given clear, user permissions overspill should be timely recovery of permissions recovery permissions can update the User table fields, you can use the REVOKE operation.
I obtained from other data ( ) interpretation of the common permission is given below :
Global administrative privileges:
FILE: read and write files in the MySQL server.
PROCESS: display or kill service threads that belong to other users.
RELOAD: Reload access control table, refresh the log, and so on.
Shutdown: Close MySQL service.
Database / data tables / columns of data permissions:
Alter: To modify an existing data table (for example, add / remove columns) and indexes.
Create: create a new database or data table.
Delete: delete table records.
Drop: delete the data table or database.
INDEX: create or delete indexes.
Insert: increase records in the table.
Select: / search records in the table.
Update: modify existing records in the table.
Special privileges:
ALL: allow to do anything (and root).
USAGE: only allowed to log – Other not allowed to do.
MySQL frequently used commands, create database name; create a database use databasename; select the database drop database name directly delete the database, do not remind the show TABLES; display table describe tablename; coupled with a detailed description of the table select distinct remove duplicate field mysqladmin drop the databasename to delete database before, there are tips.
Display the current MySQL version and the current date select version (), CURRENT_DATE;
2, modify the mysql root password:
shell> mysql-u root-p
mysql> update user set password = password (“xueok654123”) where user = ‘root’;
mysql> FLUSH privileges / / refresh the database mysql> use dbname; open the database:
mysql> show databases; database mysql> show tables; Show all tables in the database mysql: first where USE mysql; then MySQL> describe user; displayed in the user table in the mysql database table column information);
3, Grant
Create a connect to the server from anywhere a full super-user, but you must use a password to do something this mysql> grant all privileges on *. * To identified by ‘something’ with
Increase new user format: Grant select on database * to username @ log into the host identified by “password”
GRANT ALL PRIVILEGES ON *. * TO IDENTIFIED BY ‘something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *. * TO “IDENTIFIED BY ‘Something’ WITH GRANT OPTION;
Remove authorized:
mysql> revoke all privileges on *. * from “;
mysql> delete from user where user = “root” and host = “%”;
mysql> flush privileges;
Create a user custom in particular client it363.com login, you can access a particular database fangchandb
mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom@it363.com identified by ‘passwd’
Rename table:
mysql> alter table t1 rename t2;
4, mysqldump
Backup database shell> mysqldump-h host-u root-p dbname> dbname_backup.sql
Restore the database shell> mysqladmin-h myhost-u root-p create dbname
shell> mysqldump-h host-u root-p dbname <dbname_backup.sql
If you want to unloading create table instruction, the command is as follows:
shell> mysqladmin-u root-p-d databasename> a.sql
If you just want to unload the SQL command to insert data, without the need to create the table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename> a.sql
So if I only want the data, but you do not want what SQL commands, how does it work?
mysqldump-T. / phptest driver
Of these, only specified the-T parameter before unloading a plain text file, which means that the unloading data directory, / represents the current directory, and mysqldump the same directory. If you do not specify a driver table will be discharged to the data of the entire database. Each table will generate two files, one for sql file contains a built tables. Another txt file contains only data, and not the sql command.
5, the query is stored in a file and tell mysql to read from the file query instead of waiting for keyboard input. Available shell type redirect utility to get the job done. For example, if stored in the file my_file.sql queries, these queries can be executed as follows:
For example if you want Create-table statement is advance written in sql.txt:
mysql> mysql-h myhost-u root-p database <sql.txt
/ / Start the service mysqld – console
/ / Stop the service mysqladmin-u root shutdown
/ / Log in to use the database mysql
mysql-u root-p mysql
mysql-u root-p-h 11.11.11.11 database
/ / Create database create database db_name [default character set = gbk]
/ / Set the database default character set alter databse db_name default character set gbk
/ / Replace the database use database test after log on
use test
/ / Create the image field with a table create a table mypic to store picture
create table mypic (picid int, picname varchar (20), content blob);
/ / Display the structure of the table describe table mypic
desc mypic
/ / Display the current table tabulation statement show create table table_name
/ / Change the table type alter table table_name engine innodb | myisam | memory
/ / Insert a record insert a record
insert into mypic VALUES (1, ‘second chapter’, 0x2134545);
/ / Current user show current user
select user ();
/ / Show current password of the current user password
select password (‘root’);
/ / Display the current date show Current date
select now ();
/ / Change the user password change user password
update user set password = password (‘xxx’) where user = ‘root’;
/ / Assign user permissions grant
grant all privileges on * the *
The of grant select, insert, delete, update, alter, create, drop on LyBBS. for * identified by LyBBS;
grant select, insert, delete, update, alter, create, drop on LyBBS * by LyBBS;
/ / Refresh user rights in the case of not restart flush privileges
flush privileges
/ / Add a primary key add primary key to the table
alter table mypic add primary key (picid)
/ / Modify table structure add a new field to add a new column userid after picid
alter table mypic add column userid int after picid
/ / Change the column type, when the stored image is too large, the default blob over, but 100k
alter table userpic change image image longblob;
alter table userpic modify image longblob;
/ / Set the default character set gb2312
mysqld – default-character-set = gb2312
/ / Display the details, including the character set encoding show full columns from userpic;
/ / Change the encoding of the table ALTER TABLE userpic CHARACTER SET gb2312;
/ / Mysql JDBC connection url Chinese jdbc: mysql :/ / localhost / test? UseUnicode = true & characterEncoding = gb2312
/ / Execute an external script source
MySQL is the most popular open source SQL database management system, developed by MySQL AB, publish, and support. MySQL AB is a commercial company based on the MySQL developers, is a successful business model to combine open source values ??and square **** second-generation open source company. MySQL is a registered trademark of MySQL AB.
MySQL is a fast, multi-threaded, multi-user and robust SQL database server. MySQL Server supports mission-critical, heavy-load production systems use, it can also be embedded in a large configuration (mass-deployed) software.

Posted by databasesql