MySQL statement usage, adding, modifying, and deleting Fields

1. Connect to MySQL

Ii. MySQL management and authorization

3. Simple database operations

Iv. Database Backup

5. Postscript

 

 

 

 

 

 

1. Connect to MySQL

Format: mysql-H remote host address-u username-P press ENTER

Enter the password:

 

Mysql-u root-P press ENTER

Enter Password: enter the password

Mysql> enter

Exit command:> exit or Ctrl + d

 

Ii. MySQL management and authorization

1. Change the password:

Format: mysqladmin-u username-P old Password New Password

 

2. Add new users:

> Grant create, select, update... (grant related operation permissions)

-> On database .*

-> To username @ login host identified by 'Password'

 

Operation instance:

 

Add a password to the root user:

# Mysqladmin-u Root Password 52 netseek

The old-P password can be omitted because the root user does not have a password.

Login test:

# Mysql-u root-P press ENTER

Enter the password and log on successfully.

 

 

Change the original MySQL logon password 52netseek to 52china.

# Mysqladmin-u root-P 52 netseek password '52china'

 

Create a database, add users, and grant corresponding permissions:

Mysql> Create Database phpBB;

Query OK, 1 row affected (0.02 Sec)

 

Mysql> Use phpBB;

Database changed

Mysql> grant create, select, update, insert, delete, alter

-> On phpBB .*

-> To phpbbroot @ localhost identified by '52netseek ';

Query OK, 0 rows affected (0.00 Sec)

 

Grant all permissions:

> Grant all privileges

> On BBS .*

> To bbsroot @ localhost identified by '52netseek'

 

Revoke permissions:

Revoke create, select, update, insert, delete, alter

On phpBB .*

From phpbbroot @ localhost identified by '52netseek ';

 

Delete the user phpbbroot completely:

> Use MySQL

> Delete from user

Where user = 'phpbbroot 'and host = 'localhost ';

> Flush privileges; refresh the database

 

3. Simple database operations

1. display the Database List:

> Show databases;

MySQL

Test

2. Make it the current operating database

> Use MySQL; open the database.

> Show tables; displays data tables in the MySQL database.

3. display the table structure of the data table:

> Describe table name;

> Describe user; displays the table structure of the User table:

4. create databases and tables

> Create Database database name;

> Use Database Name;

> Create Table Name (field setting List)

5. Delete the database, excluding tables

> Drop database database name;

> Drop table name;

6. display the records in the table;

Select * from table name;

7. Modify the database structure:

Add field:

Alter table dbname add column <field Name> <field Options>

Modify Field:

Alter table dbname change <old field Name> <new field Name> <option>

Delete field:

Alter table dbname drop column <field Name>

 

Instance operation:

> Create Database office;

> Use office;

Mysql> Create Table personal (

-> Member_no char (5) not null,

-> Name char (,

-> Birthday date,

-> Exam_score tinyint,

-> Primary Key (member_no)

-> );

Query OK, 0 rows affected (0.01 Sec)

> DESC personal; displays the table structure:

+ ------------ + ------ + ----- + --------- + ------- +

| FIELD | type | null | key | default | extra |

+ ------------ + ------ + ----- + --------- + ------- +

| Member_no | char (5) | pri |

| Name | char (| Yes | null |

| Birthday | date | Yes | null |

| Exam_score | tinyint (4) | Yes | null |

+ ------------ + ------ + ----- + --------- + ------- +

4 rows in SET (0.00 Sec)

 

Insert into personal values ('001', 'netsecret', '2017-03-15 ', '95 ');

Insert into personal values ('002 ', 'heihei', '2017-02-24', '90 ');

Insert into personal values ('003 ', 'gogogo', '2017-05-21', '85 ');

Insert into personal values ('004 ', 'hahaha', '2017-02-25', '84 ');

Insert into personal values ('005 ', 'linlin', '2017-04-28', '85 ');

 

The MySQL tutorial you are reading is: MySQL Database Study Notes. Insert into personal values ('006 ', 'xinxin', '2017-03-15 ', '75 ');

 

Mysql> select * from personal;

+ ----------- + --------- + ------------ +

| Member_no | Name | birthday | exam_score |

+ ----------- + --------- + ------------ +

| 001 | netseek | 1983-03-15 | 95 |

| 002 | Heihei | 1982-02-24 | 90 |

| 003 | Gogo | 1985-05-21 | 85 |

| 004 | Haha | 1984-02-25 | 84 |

| 005 | Linlin | 1982-04-28 | 85 |

| 006 | xinxin | 1985-03-15 | 75 |

+ ----------- + --------- + ------------ +

 

Modify database table:

Requirement: Add a field of height after birthday, whose data type is tinyint.

Rename the exam_score field to scores, and the Data Type remains unchanged.

> Alter table personal

-> Add column height tinyint after birthday,

-> Change column exam_score scores tinyint;

 

Mysql> select * from personal;

+ ----------- + --------- + ------------ + -------- +

| Member_no | Name | birthday | height | scores |

+ ----------- + --------- + ------------ + -------- +

| 001 | netseek | 1983-03-15 | null | 95 |

| 002 | Heihei | 1982-02-24 | null | 90 |

| 003 | Gogo | 1985-05-21 | null | 85 |

| 004 | Haha | 1984-02-25 | null | 84 |

| 005 | Linlin | 1982-04-28 | null | 85 |

| 006 | xinxin | 1985-03-15 | null | 75 |

+ ----------- + --------- + ------------ + -------- +

 

Insert data to the table:

> Update personal set scores = 95 + 5 where name = 'netsecret ';

> Select scores from personal where name = 'netsecret ';

+ -------- +

| Scores |

+ -------- +

| 1, 100 |

+ -------- +

 

Delete all the information in the table named 'gogogo:

> Delete from personal where name = 'gogogo ';

 

Remove tables from the database:

Mysql> drop table if exists personal;

 

Iii. Import and export Databases

Export:

Use the select into OUTFILE 'filename' statement

Use the mysqldump Utility

Use the select into OUTFILE 'filename' statement

 

1. Only one table can be processed, and only data in the output file, without table structure

Assume that officehas a table named "personal", and now we need to dismount the" personal" file into the "out.txt" file:

> Use office;

> Select * from personal into OUTFILE 'out.txt '; you can see out.txt under/var/lib/MySQL/office/directory.

Select * from personal into OUTFILE './out.txt'; you can see that out.txt is used in the/var/lib/MySQL/directory of out.txt.

 

2. Use the mysqldump utility (you can easily process multiple tables)

# Cd/var/lib/MySQL

Export the table creation and insertion commands for creating related tables

# Mysqldump BBS> bbs. SQL import the database BBS to BBs. SQL

 

If you want to import bbs. SQL to the database, you can use:

Mysql> Create Database bbstest; Create a database named office first.

# MySQL bbstest <bbs. SQL (this is often used to upload local database files to the server and then import them to the database)

 

Just want to export the table creation command:

# Mysqldump-d bbs> bbscreate. SQL

SQL commands that only want to export the inserted data:

# Mysqldump-t bbs> bbsinsert. SQL

Export the database table creation commands and data in the table at the same time:

# Mysqldump-T./BBS cdb_admingroups (where./indicates the current directory and cdb_admingroups is a table in the BBS database)

# Ls

Cdb_admingroups. SQL export the table creation command

Cdb_admingroups.txt exports table data

 

Import:

Load a database from a file:

Mysql> load data infile "/tmp/name.txt" into table names;

Mysql> select * from names;

 

 

Iv. Database Backup

 

1. manually copy backup:

MySQL database files are stored in the/var/lib/MySQL directory. The database creates a directory for each database. All database files are stored in these directories.

[Root @ linuxhero MySQL] # ls

[Root @ linuxhero MySQL] # servcie mysqld stop database first

BBS MySQL mysql. Sock phpBB test office displays the databases.

 

If we want to back up the current database directory as MySQL. Bak.

[Root @ linuxhero lib] # cp-RF MySQL mysql. Bak

If the database is damaged, you need to restore the database:

[Root @ linuxhero lib] # cp-RF mysql. bak/* MySQL

After the database is restored, the files in var/lib/MySQL have been changed. To change the permissions of the files, you must change the user's read and write permissions of the MySQL database.

So we have to start and run MySQL and log on to the database:

[Root @ linuxhero lib] #/etc/init. d/mysqld start

[Root @ linuxhero lib] # MySQL

 

The MySQL tutorial you are reading is: MySQL Database Study Notes. -U root-P

Enter Password: enter the password to log on.

Mysql> show databses;

 

2. Use mysqldump to back up the database

[Root @ linuxhero MySQL] # mysqldump -- opt BBS-u root-P> BBs. SQL

Enter password:

Note: Other Options for adding backup with -- opt, where BB is one of the database names,

The above indicates that the backup is written to the file BB. SQL using the redirection output.

[Root @ linuxhero MySQL] # less bbs. SQL

 

To restore the database BB, perform the following operations:

[Root @ linuxhero MySQL] # MySQL BBS-u root-P <BBs. SQL

 

To back up all databases:

[Root @ linuxhero MySQL] # mysqldump -- Opt -- all-databases-u root-P> MySQL. Bak

Enetr password: enter the password.

Recover all databases without entering the Database Name:

[Root @ linuxhero MySQL] # mysql-u root-P <MySQL. Bak

Enetr password: enter the password.

 

5. Postscript:

MySQL database personal learning notes, which is a simple summary of my personal learning process. These are commonly used and hope to help those who want to learn MySQL, if anything is wrong or inappropriate, please give me more advice. You are welcome to share your knowledge about Linux with me.