Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "sorting out the basic operating sentences of MySql applications". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Export the entire database

Mysqldump-u user name-p-- default-character-set=latin1 database name > exported file name (database default code is latin1)

Mysqldump-u wcnc-p smgp_apps_wcnc > wcnc.sql

two。 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 adds a drop table before each create statement

4. Import database

A: common source commands

Go to the mysql database console

Such as mysql-u root-p

Mysql > use database

Then use the source command, followed by a script file (such as .sql used here)

Mysql > source wcnc_db.sql www.2cto.com

B: use the mysqldump command

Mysqldump-u username-p dbname

< filename.sql C:使用mysql命令 mysql -u username -p -D dbname < filename.sql 一、启动与退出 1、进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql>

2. Exit MySQL:quit or exit

II. Library operation

1. Create a database

Command: create database

For example, set up a database called 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 the database

Command: drop database

For example: delete the database named xhkdb

Mysql > drop database xhkdb

4. Connect to the database

Command: use

For example, if the xhkdb database exists, try to access it:

Mysql > use xhkdb

Screen Tip: Database changed

5. View the database currently in use

Mysql > select database ()

6. The table information contained in the current database:

Mysql > show tables; (Note: there is an s at the end)

Third, table operation, should connect to a database before operation

1. Create a table www.2cto.com

Command: create table ([,.. ])

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 (16Pol 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

For example: delete a table named MyClass

Mysql > drop table MyClass

4. Insert data

Command: insert into [[,.. ])] Values (value 1) [, (value n)]

For example, insert two records into the table MyClass, indicating that the scores of Tom, Joan and Wang are 96.45, 82.99 and 96.5 respectively.

Mysql > insert into MyClass values (1), (2), (2)

5. Query the data in the table

1), query all rows

Command: select from

< 表名 >

Where

< 表达式 >

For example: view all data in table MyClass

Mysql > select * from MyClass

2) query the first few rows of data

For example: view the first two rows of data in table MyClass

Mysql > select * from emp order by empno limit 0Pol 2

Or:

Mysql > select * from emp limit 0Pol 2

6. Delete the data in the table

Command: delete from table name where expression

For example: delete the record numbered 1 in table MyClass

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 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 table name

For example: change the name of MyClass to YouClass in the table

Mysql > rename table MyClass to YouClass

9. Update the field content

Update table name set field name = new content

Update table name set field name = replace (field name, 'old content', 'new content')

Add four spaces in front of the article

Update article set content=concat ('', content)

Field Type

1.INT [(M)] type: normal size integer type

2.DOUBLE [(MMagar D)] [ZEROFILL] type: normal size (double precision) floating point number type

3.DATE date type: the supported range is 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 side 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 www.2cto.com

5. Import database tables

(1) create a .sql file

(2) generate a library such as auction.c:mysqlbin > mysqladmin-u root-p creat auction, prompt for a password, and then create it successfully.

(2) Import auction.sql file

C:mysqlbin > mysql-u root-p auction

< auction.sql。 　　 通过以上操作，就可以创建了一个数据库auction以及其中的一个表auction。 6．修改数据库 　　 （1）在mysql的表中增加字段： 　　 alter table dbname add column userid int(11) not null primary key auto_increment; 　　 这样，就在表dbname中添加了一个字段userid，类型为int(11)。 7．mysql数据库的授权 　　 mysql>

Grant select,insert,delete,create,drop

On *. * (or test.*/user.*/..)

To user name @ localhost

Identified by 'password'

For example, to 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)

After that, a new user is created called testuser, who can only connect to the database from localhost and can connect to the test database. Next, we must specify what the user testuser 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 as well as UPDATE query operations on tables in each test database. Now let's finish the operation and exit the MySQL client:

Mysql > exit

Bye9!

8: display the structure of the table:

Mysql > DESCRIBE MYTABLE

9: add records to the table

Mysql > insert into MYTABLE values ("hyq", "M")

10: load data into database tables in text (for example, D:/mysql.txt)

Content: 3 rose Shenzhen No.2 Middle School 1976-10-10

4 mike Shenzhen No.1 Middle School 1975-12-23

Mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE

11: import .sql file command (for example, D:/mysql.sql)

Mysql > use database

Mysql > source d:/mysql.sql

MySql exists as a service in windows. Before using it, make sure that the service has been started and that the net start mysql command is not available to start it. The "/ etc/rc.d/init.d/mysqld start" command can be used when starting in, and note that the initiator should have administrator privileges.

The newly installed MySql contains a root account with an empty password and an anonymous account, which is a big security risk. For some important applications, we should improve the security as much as possible. Here, delete the anonymous account and set the password for the root account. Use the following command:

Use mysql

Delete from User where User= ""

Change the password: update User set Password=PASSWORD ('newpassword') where User='root'

If you want to restrict the login terminal used by the user, you can update the Host field of the corresponding user in the User table, and restart the service after making the above changes. When logging in, you can use the following command:

-uroot-p; www.2cto.com

Mysql-uroot-pnewpassword

Mysql mydb-uroot-p

Mysql mydb-uroot-pnewpassword

The above command parameters are part of the commonly used parameters. Please refer to the documentation for details. Mydb here is the name of the database to be logged in.

In the development and practical application, users should not only use root users to connect to the database. Although it is very convenient to use root users for testing, it will bring major security risks to the system and is not conducive to the improvement of management technology. We give the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be given the right to delete data. The user management of MySql is realized through the User table. There are two common ways to add new users: one is to insert the corresponding data row in the User table and set the corresponding permissions at the same time; the other is to create users with certain permissions through the GRANT command. The common uses of GRANT are 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 administrative ability to give him permissions on the corresponding object, add the WITH GRANT OPTION option after GRANT. For users who are added by inserting the User table, the Password field is updated and encrypted using the PASSWORD function to prevent unscrupulous people from reading the password. Users who are no longer in use should be cleared, and those whose permissions are out of bounds should be reclaimed in time, which can be done by updating the corresponding fields in the User table or by using REVOKE operation.

Global administrative permissions:

FILE: read and write files on the MySQL server.

PROCESS: displays or kills service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off the MySQL service.

Database / data Table / data column permissions:

Alter: modify existing data tables (such as adding / deleting columns) and indexes.

Create: create a new database or data table.

Delete: deletes the record of the table.

Drop: delete a data table or database.

INDEX: create or delete an index.

Insert: add the record of the table.

Select: show / search 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 login is allowed-nothing else is allowed.

Common MYSQL commands:

1. Connect MYSQL

Format: mysql-h host address-u user name-p user password

1. Example 1: connect to the MYSQL on this computer

First, open the DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p. Enter prompts you to enter your password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing enter. The prompt for MYSQL is: mysql >

Example 2: connect to the MYSQL on the remote host

Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-uroot-pabcd123

(note: U and root do not have to add spaces, and so do others)

3. Exit MYSQL command: exit (enter)

Change the password www.2cto.com

Format: mysqladmin-u username-p old password password new password

1. Example 1: add a password ab12 to root. First enter the directory mysqlbin under 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: change the password of root to djg345

Mysqladmin-uroot-pab12 password djg345

Transfer the text data to the database

1. The format that the text data should conform to: the field data is separated by tab key, and the null value is replaced by\ n.

Example:

3 rose Shenzhen No.2 Middle School 1976-10-10

4 mike Shenzhen No.1 Middle School 1975-12-23

2. Data input command load data local infile "file name" into table table name

Note: you'd better copy the files to the\ mysql\ bin directory and type the library where the table is located with the use command.

4. Backup the database: (the command is executed under the\ mysql\ bin directory of DOS)

Mysqldump-- opt school > school.bbb

Note: back up the database school to the school.bbb file. School.bbb is a text file with any file name. Open it and you will find something new.

1. ALL, DISTINCT, DISTINCTROW, TOP predicates

(1) ALL returns all records that meet the conditions of the SQL statement. If this predicate is not specified, it defaults to ALL.

Example: SELECT ALL FirstName,LastName FROM Employees

(2) if DISTINCT has more than one record with the same data in the selected field, only one is returned.

(3) if DISTINCTROW has duplicate records, only one is returned.

(4) TOP displays several records at the beginning and end of the query. You can also return the percentage of the record, which is to use the TOP N PERCENT clause (where N represents the percentage)

Example: return the order with the largest order volume of 5%

SELECT TOP 5 PERCENT* FROM [Order Details] ORDER BY UnitPrice*Quantity* (1-Discount) DESC

2. Alias the field with the AS clause

If you want to take a new title for the returned column, or, after calculating or summarizing the field, produce a new value that you want to display in a new column, keep it with AS.

Example: return the FirstName field and alias it to NickName

SELECT FirstName AS NickName, LastName, City FROM Employees

Example: return a new column to show the inventory value

SELECT ProductName, UnitPrice, UnitsInStock, UnitPrice*UnitsInStock AS valueInStock FROM Products

Where clause specifies the query condition

1. Comparison operator

Comparison operator meaning

= equal to

> greater than

< 小于 >

= greater than or equal to

Not greater than

!

< 不小于 例：返回96年1月的定单 SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate>

# 1 AND OrderDate#96-1-pound

It can also be expressed as:

WHERE OrderDate > Datevalue ('1Compact 1Accord 96')

Www.2cto.com

2. Range (BETWEEN and NOT BETWEEN)

BETWEEN... AND... Operator specifies a closed range to search.

Ex.: return to the order 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 the list. The IN clause can replace a series of conditions connected with the OR clause.

4. Pattern matching (LIKE)

The LIKE operator verifies that a field value that contains string data matches a specified pattern.

Wildcard meaning:

? Any single character

* characters of any length

A single number between # 09s

[character list] any value in the character list

[! Character list] any value not in the character list

-specifies the character range, and the values on both sides are its upper and lower limits respectively

Three. Sort the 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 order. 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 in 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 field name can be replaced by the location number of the field in the selection list, and the field name and location number can be mixed.

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. Using connection relation to realize multi-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 out the products and orders whose product inventory is greater than the quantity of orders for the same product.

SELECT ProductName,OrderID, UnitInStock, Quantity FROM Products, [Order Deails] WHERE Product.productID= [Order Details]. ProductID

AND UnitsInStock > Quantity

Another way is to use JNNER JOIN unique to Microsof JET SQL

Syntax:

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

Note:

INNER JOIN cannot connect to Memo OLE Object Single Double data type fields.

Join multiple ON clauses in a 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

You can also www.2cto.com.

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

The external connection returns more records, keeping mismatched records in the results, and all records on the other side are returned regardless of whether there are records that meet the criteria.

FROM table [LEFT | RIGHT] JOIN table2

ON table1.field1comparision table.field2

Use the left join to establish an external connection, and the table on the left side of the expression displays all its data

Ex.: return all goods whether there is an order or not

SELECT ProductName, OrderID

FROM Products

LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID

The difference between a right join and a left join is that it returns all records from the left table, regardless of whether there are matching records in the left table.

Ex.: if you want to know the customer information and count the customer distribution in each region, you can use a right connection to return customer information even if there are no customers in a certain area.

Null values do not match each other, and you can test whether there are null values in the fields of a connected table through an external join.

SELECT *

FROM talbe1

LEFT JOIN table2 ON table1.a=table2.c

1. Use Iif function in join query to display null value with 0 value.

Iif expression: Iif (IsNull (Amount,0,Amout))

Ex.: a flag will be returned regardless of whether the order is greater than or less than ¥50.

Big order?,?Small order ([Amount] > 50)

Www.2cto.com

five。 Group and summarize query results

In the syntax of SQL, the GROUP BY and HAVING clauses are used to summarize data. The GROUP BY clause indicates which fields are grouped, and after the records are grouped, they are filtered with the having clause.

Syntax of GROUP BY clause

SELECT fidldlist

FROM table

WHERE criteria

[GROUP BY groupfieldlist [HAVING groupcriteria]]

Note: the Microsoft Jet database Jet cannot group memo or OLE object fields.

The Null value in the GROUP BY field is ready for grouping but cannot be omitted.

Null values are not calculated in any SQL aggregate function.

The GROUP BY clause can be followed by up to ten fields, sorted in order from left to right.

Ex.: after grouping by title in the employee table in the 'WA' area, find out all the titles where the number of employees with the same title is greater than 1.

SELECT Title, Count (Title) as Total

FROM Employees

WHERE Region = 'WA'

GROUP BY Title

HAVING Count (Title) > 1

Accumulation function in JET SQL

The meaning of aggregate function

SUM () summation

Average of AVG ()

The number of records in the COUNT () expression

COUNT (*) calculates the number of records

MAX maximum www.2cto.com

MIN minimum

VAR variance

STDEV standard error

The first value of FIRST

Last value of LAST

six。 Create a parameter query with a Parameters declaration

The syntax of the Parameters declaration:

PARAMETERS name datatype [, name datatype [,...]]

Where name is the parameter marker, you can reference the parameter through the marker.

Datatype specifies the data type of the parameter.

Use 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]

seven。 Function query

The so-called functional query is actually a kind of operational query, which can operate the database quickly and efficiently. For the purpose of selecting query, it selects the qualified data and then batches the data. Functional queries include update queries, delete queries, add queries, and generate table queries.

1. Update query

The UPDATE clause can change data in one or more tables simultaneously. It can also change the values of multiple fields at the same time.

Update query syntax:

UPDATE Table name SET New value WHERE guidelines

Ex.: UK customers' orders increased by 5% and freight volume increased by 3%.

UPDATE OEDERS SET OrderAmount = OrderAmount * 1.1Freight = Freight*1.03 WHERE ShipCountry = 'UK'

2. Delete query

The DELETE clause enables users to delete large amounts of outdated or redundant data.

Note: the object to delete the query is the entire record.

Syntax of the DELETE clause: www.2cto.com

DELETE [Table name. *] FROM Source Table WHERE guidelines

Ex.: to delete all orders from 1994

DELETE * FROM Orders WHERE OrderDataANY (SELECT UnitPrice FROM [Order Details] WHERE Discount > 0.25)

2. Check whether the value of the expression matches a value of a set of values returned by the subquery

Syntax: www.2cto.com

[NOT] IN (subquery)

Ex.: return products with inventory value 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: searching customers in the UK with EXISTS

SELECT ComPanyName,ContactName FROM Orders WHERE EXISTS (SELECT * FROM Customers WHERE Country = 'UK' AND Customers.CustomerID= Orders.CustomerID)

This is the end of the content of "sorting out the basic operating sentences of MySql applications". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.