MySQL Database (ii) Basic operation of MySQL database

The last time the MySQL database was installed, there is no login operation, this time to briefly introduce the basic operation of MySQL

I. Access to the MySQL database (after the installation of the initialization process, the default administrator user name of the MySQL database is: "Root", the password is empty; Note that this root is not the root account of the Linux system administrator)

1. Log in to MySQL

When the password is not set, MySQL adds "-u" to specify the user.

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/79/5E/wKiom1aPVk6Tqj3pAACxvHw5tdk087.jpg "title=" 16.jpg "alt=" Wkiom1apvk6tqj3paacxvhw5tdk087.jpg "/>

If root sets a password, you also need to specify the password with the "-P" option after the user name

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/79/5D/wKioL1aPWU-QbldZAADF6QhGkBk687.jpg "title=" 2.jpg " alt= "Wkiol1apwu-qbldzaadf6qhgkbk687.jpg"/>

2. Execute the MySQL action statement

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/79/5F/wKiom1aPWtfgWU4YAABXCjb8B2Q968.jpg "title=" 3.jpg " alt= "Wkiom1apwtfgwu4yaabxcjb8b2q968.jpg"/>

NOTE: Each MySQL statement is terminated with a semicolon; it's not case-sensitive in MySQL, but it's customary to capitalize the keywords in the MySQL statement

3. Exit MySQL (You can exit MySQL database using the command exit or quit)

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/79/5E/wKioL1aPW5zASAXkAAASrORPsw8071.jpg "title=" 4.jpg " alt= "Wkiol1apw5zasaxkaaasrorpsw8071.jpg"/>

Two. Managing MySQL database structure

1. See which libraries are in the current database server
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/79/5F/wKioL1aPap_Dr_lMAABm6h0IfOU447.jpg "title=" 5.jpg " alt= "Wkiol1apap_dr_lmaabm6h0ifou447.jpg"/>

2. See what tables are in the current database (this is the game library example)

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/79/60/wKiom1aPa4rgBM9FAABy9_jzVZI490.jpg "title=" 6.jpg " alt= "Wkiom1apa4rgbm9faaby9_jzvzi490.jpg"/>

3. View the structure of the table (here is an example of the users table in the game library, which, of course, also needs to switch to the game library)

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/79/60/wKiom1aPaaOAjF-mAABgw0J26KU713.jpg "title=" 7.jpg " alt= "Wkiom1apaaoajf-maabgw0j26ku713.jpg"/>

4. Create a new library

Command: CREATE DATABASE name

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/79/5E/wKioL1aPY3DzDolaAAAb4iHZa_c817.jpg "title=" 8.jpg " alt= "Wkiol1apy3dzdolaaaab4ihza_c817.jpg"/>

5. Create a new table

Command: CREATE TABLE table name (field 1 name type, field 2 name Type, ..., primary key (primary key name))

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/79/60/wKiom1aPZP6jyXNEAABcfKgO5cA205.jpg "title=" 9.jpg " alt= "Wkiom1apzp6jyxneaabcfkgo5ca205.jpg"/>

Description: The field is the column in the table, the type refers to the data type entered in this column, the value of the primary key cannot be duplicated

, the name of the table to be created is "users", the first column created in the table is named "Name" and the data in this column is a string of no more than 16 bytes, the second column is named "passwd" and the column data is a string of no more than 20 bytes, the default value is NULL, and the value in the primary key is The data inside the "name" column cannot be duplicated

6. Delete a database

Command: Drop database name

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/79/60/wKiom1aPbQODKcubAAAa7STTyn0220.jpg "title=" 10.jpg "alt=" Wkiom1apbqodkcubaaaa7sttyn0220.jpg "/>

7. Delete a table

Command: Drop table library name. Table Name

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/79/60/wKiom1aPbUixlx2NAAAbtI4SmEM328.jpg "title=" 11.jpg "alt=" Wkiom1apbuixlx2naaabti4smem328.jpg "/>

Description: The tables in the game library are represented by the library name, table name, and the library name is preceded by the table name with "." Separated

Three. Data management (INSERT, view, modify and delete) also here is an example of the users table in the previous game library

1. Inserting new data

Command: INSERT into table name (Field 1, field 2 ...) VALUES (Value of field 1, Value of field 2 ...)

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/79/60/wKioL1aPct_Cof4kAAA_A2K79RM088.jpg "title=" 12.jpg "alt=" Wkiol1apct_cof4kaaa_a2k79rm088.jpg "/>

Description: Switch to the game library, then insert the data in the Users table, insert the Name column into the zhangsan;passwd column insert 123456,password here is used to encrypt the data, the following parentheses in the 123456 encryption into ciphertext

Note: When you want to enter character and date data in a table, you need to enclose it in single quotation marks.

2. View data

Command: Select field Name 1, field name 2,... from table name where conditional expression

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/79/61/wKioL1aPeV7yd53yAADhnaUTrCE557.jpg "title=" 13.jpg "alt=" Wkiol1apev7yd53yaadhnautrce557.jpg "/>

3. Modify the data

Command: Update table name set field Name 1 = Field 1 value, field name 2 = field value 2 Where condition expression

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/79/61/wKioL1aPfpvB2EAoAADWuwX8Msw090.jpg "title=" 14.jpg "alt=" Wkiol1apfpvb2eaoaadwuwx8msw090.jpg "/>

4. Delete data

Command: Delete from table name where conditional expression

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/79/61/wKioL1aPgTCimx_EAACyS9MKXqk166.jpg "title=" 15.jpg "alt=" Wkiol1apgtcimx_eaacys9mkxqk166.jpg "/>

Four. Basic maintenance of MySQL database

1. Grant permissions (because the root account of the MySQL database has full access to all libraries, tables, frequent use of the root account will add a certain amount of risk to the database, so the actual work, usually set up some low-privileged users, only responsible for a portion of the library, table management and maintenance operations)

Command: Grant permission list on Library name. Table name to User name @ source address [identified by ' Password ']

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/79/64/wKiom1aPlzLAfjpfAAAuXqUtsN0596.jpg "title=" 16.jpg "alt=" Wkiom1aplzlafjpfaaauxqutsn0596.jpg "/>

Description

GRANT statement: Specifically used to set permissions for a database user, creates a new user when the user does not exist, and modifies user information when the user is present (note that this user is a user created by the MySQL database itself, regardless of the user in the Linux system, Database user information is saved in the user table in the MySQL library

Permissions List-used to list the various actions used by authorization, separated by commas, using "all" to represent all permissions. is granted permission to insert and query

The name of the library. Table name-Specifies which table of the library the object of the authorization action is, and uses the wildcard "*" to represent all. The object in the authorization action in the game library is all tables in the

User name @ Source Address-Specifies the user who is granted permission and the address of the client that is allowed to access it (that is, who can connect, where to connect), the source address can write a domain name, an IP address, or a wildcard "%" to represent all addresses within a region or network segment, such as "%. Domain Name", "192.168.1 % "and so on. The localhost in indicates the source address is native

Identified by-set a password to this user, if omitted this option means that the password is empty

2. View permissions (users view authorization information for database users)

Command: Show grants for user name @ Source Address

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/79/63/wKioL1aPmAfzjuyqAACvDlK4LAY052.jpg "title=" 17.jpg "alt=" Wkiol1apmafzjuyqaacvdlk4lay052.jpg "/>

3. Revoke permissions (used to revoke database operation permissions for a specified user)

Command: Revoke permissions list on library name. Table name from user name @ Source Address

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/79/64/wKiom1aPmfGw8PoVAACnrtBhZAQ051.jpg "title=" 18.jpg "alt=" Wkiom1apmfgw8povaacnrtbhzaq051.jpg "/>

Note: users who revoke permissions can still connect to the MySQL database, but they are forbidden to perform the corresponding permissions. After revoking the permissions that you just granted to Zhangsan, use View permissions to view Zhangsan when the permissions are gone.

Five. Database backup and restore (timely backup of the database is an important work of information security management, backup methods are also many, such as the previous database document describes the data stored in the directory/usr/local/mysql/data, we can package the backup, we can also use professional tools to export, Here we introduce the next mysqldump command)

1. Back up some of the tables in the specified library

Command: mysqldump [options] Library Name table name 1 Table Name 2 ...>/backup path/backup file name

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/79/64/wKiom1aPnx-iRQyPAACG65KrPZE637.jpg "title=" 19.jpg "alt=" Wkiom1apnx-irqypaacg65krpze637.jpg "/>

Note: The commonly used options are "-U" and "-P", respectively, to specify the user name and password, where the above command is entered, you also need to enter the root password to perform the backup work

2. Back up one or more complete libraries

Command: mysqldump [options]--databases library name 1 Library Name 2 >/backup path/backup file name

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/79/65/wKiom1aPrKfgbuzZAABX8Rs3Bho889.jpg "title=" 20.jpg "alt=" Wkiom1aprkfgbuzzaabx8rs3bho889.jpg "/>

3. Back up all libraries of the MySQL server

Command: mysqldump [Options]--all-databases >/backup path/backup file name

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/79/64/wKiom1aPoiWzXNsCAAB5Bu89dFQ426.jpg "title=" 21.jpg "alt=" Wkiom1apoiwzxnscaab5bu89dfq426.jpg "/>

4. Restore the tables in the database (that is, restore the database)

Command: MySQL [options] Library Name Table name </backup path/file name of backup

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/79/65/wKiom1aPrijgDqH2AAAf_b000GQ810.jpg "title=" 22.jpg "alt=" Wkiom1aprijgdqh2aaaf_b000gq810.jpg "/>

5. Recovering libraries in the database

Command: MySQL [options] </backup path/file name of backup

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/79/64/wKioL1aPrzyQ2gmpAAAdzpdqA1k699.jpg "title=" 23.jpg "alt=" Wkiol1aprzyq2gmpaaadzpdqa1k699.jpg "/>

Six. Modify the default administrator root password

Method One: The various users to access the database including the root user information is stored in the MySQL Library user table, you can directly modify the data in it, as shown in

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/79/64/wKioL1aPtL-S5MXXAAB719soWaY098.jpg "title=" 24.jpg "alt=" Wkiol1aptl-s5mxxaab719soway098.jpg "/>

Method Two: Use the Mysqladmin tool in the Linux system environment to modify

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/79/64/wKioL1aPspmza4dmAAAfxQqWtIo276.jpg "title=" 25.jpg "alt=" Wkiol1apspmza4dmaaafxqqwtio276.jpg "/>

Note: If you are adding a password for the first time, do not add the "-P" option

MySQL has a lot of knowledge, but the basic operation must first grasp

MySQL Database (ii) Basic operation of MySQL database