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

This article mainly explains "the basic commands of mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the basic commands of mysql.

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

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

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 MyClass order by id limit 0Pol 2

Or:

Mysql > select * from MyClass 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

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

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

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

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!

1: use the show statement to find out what databases currently exist on the server:

Mysql > SHOW DATABASES

2:2, create a database MYSQLDATA

Mysql > Create DATABASE MYSQLDATA

3: select the database you created

Mysql > USE MYSQLDATA; (if Database changed appears by pressing enter key, the operation is successful!)

4: see what tables exist in the current database

Mysql > SHOW TABLES

5: create a database table

Mysql > Create TABLE MYTABLE (name VARCHAR (20), sex CHAR (1))

6: display the structure of the table:

Mysql > DESCRIBE MYTABLE

7: add records to the table

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

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

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

9: import .sql file command (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'

The following is the management experience of using MySql seen inadvertently on the network.

Extracted from: http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html

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 Linux. 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= ""

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 database service after making the above changes. When logging in, you can use 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 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 the use of root users for testing is very convenient, but it will bring major security risks to the system, and is not conducive to the improvement of management skills. 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 identified by "password"

Grant usage on *. * to identified by "password"

Grant select,insert,update on mydb.* to identified by "password"

Grant update,delete on mydb.TestTable to 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.

The following is an explanation of the common permissions I have obtained from other materials ():

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.

/ 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.

-

MYSQL common commands

There are many friends who have installed it but don't know how to use it. In this article, we will learn some common MYSQL commands from connecting to MYSQL, changing passwords, adding users, and so on.

There are many friends who have installed mysql but don't know how to use it. In this article, we will learn some common MYSQL commands from connecting to MYSQL, changing passwords, adding users, and so on.

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)

Second, change the password

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

Common MYSQL commands (part two)

First, operation skills

1. If you enter the order and find that you forgot to add the semicolon, you don't have to type the command again, just hit the semicolon and enter. In other words, you can type a complete command into several lines, and then use a semicolon as a closing mark to OK.

You can use the cursor up and down keys to call up the previous command. But an old version of MYSQL that I used before doesn't support it. I am using mysql-3.23.27-beta-win now.

Second, display commands

1. Display the list of databases.

Show databases

At the beginning, there were only two databases: mysql and test. The mysql library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Use mysql; / / Open the library, those who have studied FOXBASE must be familiar with it.

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

An example of building a database and table as well as inserting data

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build 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 table creation

/ / the following are insert 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 creating the table (1) set ID to a numeric field of length 3: int (3) and let it automatically add one to each record: auto_increment cannot be empty: not null and make it the main field primary key

(2) set NAME to a character field with a length of 10

(3) set ADDRESS to a character field with a length of 50, and the default is Shenzhen. What's the difference between varchar and char? we'll have to wait for a later article.

(4) set YEAR to the date field.

It's OK if you type the above command at the mysql prompt, but it's not easy to debug. You can write the above command as is in a text file assuming school.sql, then copy it to c:\, enter the directory\ mysql\ bin in the DOS state, and type the following command:

Mysql-uroot-p password

< c:\school.sql 如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。 四、将文本数据转到数据库中 　　1、文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替. 例： 3 rose 深圳二中 1976-10-10 4 mike 深圳一中 1975-12-23 　　2、数据传入命令 load data local infile "文件名" into table 表名; 注意：你最好将文件复制到\mysql\bin目录下，并且要先用use命令打表所在的库 。 五、备份数据库：（命令在DOS的\mysql\bin目录下执行） 　　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. 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]

Description:

The part enclosed in square braces ([]) is optional, and the part enclosed in curly braces ({}) indicates that one of them must be selected.

1 FROM clause

The FROM clause specifies the source of the fields in the SELECT statement. The FROM clause is followed by one or more expressions separated by commas, where the expression can be a single table name, a saved query, or a compound result from INNER JOIN, LEFT JOIN, or RIGHT JOIN. If the table or query is stored in an external database, indicate its full path after the IN clause.

Example: the following SQL statement returns all customers with orders:

SELECT OrderID,Customer.customerID

FROM Orders Customers

WHERE Orders.CustomerID=Customers.CustomeersID

2 ALL, DISTINCT, DISTINCTROW, TOP predicates

(1) ALL returns all records that 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

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

Use NOT expressions to invert.

Ex.: check orders after January 1, 1996

WHERE Not OrderDateQuantity

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

Also OK

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)

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

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

New value of SET

WHERE criterion

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

UPDATE OEDERS

SET OrderAmount = OrderAmount * 1.1

Freight = Freight*1.03

WHERE ShipCountry = 'UK'

2 delete query

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

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

The syntax of the DELETE clause:

DELETE [table name. *]

FROM Source Table

WHERE criterion

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:

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

At this point, I believe you have a deeper understanding of the basic commands of mysql, so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.