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

What are the common basic operations of MySQL? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

MySQL common operation basic operation, the following are all tests under MySQL5.0 pass the first description, remember to add at the end of each command; (semicolon)

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

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 d:wcnc_db.sql

I. start and exit

1. Enter MySQL: start MySQL Command Line Client (the DOS interface of MySQL) and enter the password for installation directly. The prompt at this time is: 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. Currently selected (connected) database

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

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

After reading the above, have you mastered the common basic operations of MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.