Shulou(Shulou.com)06/01 Report--

1. Common commands

1.1 install the database

   take mysql56 as an example, store it on d disk, open cmd, and enter the following commands:

(1) d:

(2) cd   mysql56

(3) cd   bin

(4) mysqld  -install

Can be installed.

1.2 start the database

   enters the following command in the bin directory of the mysql storage:

1.3 Log in to the database

   enter the following command and password in the bin directory:

1.4 modify database password

   enter the following command and the modified password in the bin directory, such as 4214963 in the figure, which is the new password set:

1.5 close the database

   enter the following command in the bin directory:

1.6 View all databases in the system

   show   databases

   uses the command prompt to view:

   uses the database tool to view. After entering the command, select the command and click run:

1.7 create a database

  , for example, create a database named sjk1. Enter the following command after entering the database. The if not exists in the command is to prevent the created database name from already existing in the system. If you can ensure that the database name you see will not be duplicated, you can omit if not exists.

   uses the command prompt to create a database called sjk1:

   uses the database tool to create a database called sjk2:

   looks at all the databases in the system in the tool and checks whether the database has been created successfully:

1.8 using the database

  , for example, uses sjk1:

   uses the command prompt:

   uses database tools:

1.8 Delete the database

  , for example, delete sjk1:

   uses the command prompt:

   uses database tools:

Frecinct ZmFuZ3poZW5naGVpdGk =)

1.9 View Storage engine commands

   uses the command prompt:

   uses database tools:

2. The common concepts of database and their interrelations.

   database: a database management system (DBMS) can manage multiple databases

   data tables: there are multiple data tables in a database

   data rows: multiple data rows in a data table

   data columns (fields): a row of data has multiple columns

   view: virtual tabl

   index: used to speed up access to table data

   primary key: the primary key field is not allowed to be empty and uniquely identifies a row in the table

   foreign key: used to specify the relationship between two tables

   transaction: a program unit that updates data

3. The system database of MYSQL

3.1information_schema

Some database objects in the main storage system of   

3.2performance_schema

   mainly stores database server performance parameters, which are generally used by DBA and advanced users to analyze database performance bottlenecks

3.3Mysql

   main storage system user rights information

4. Operation commands of tables in the database

   takes database tools as an example

4.1 create tables

   creates a table named sjk2_table1 and adds fields id and name, where int and varchar (10) are id fields and name fields, respectively. Type 10 is the limit on the length of the field, primary key is the constraint on the field id, indicates the primary key, and unique is the constraint on name, which represents the unique indication:

4.2 display table

   displays all tables in sjk2:

4.3 copy the table

   replicates table sjk2_table1, and the copied table is named sjk2_table2:

4.4 copy the table but not the constraint

   replicates table sjk2_table1, and the copied table is named sjk2_table3:

4.5 View table structure

   looks at the structure of table sjk2_table1:

4.6 modify the table

4.6.1 add Table Field

   adds a field sex to the sjk1_table1 table:

4.6.2 add Index

   adds indexes to the sjk1_table1 table:

4.6.3 add primary key

   adds a primary key to the sjk1_table3 table:

4.6.3 add unique

   adds unique to the sjk1_table3 table:

4.6.4 add category id

   adds a category id,classid to the sjk2_table1 table:

4.6.5 add a foreign key constraint

   adds the primary key in skj2_table3 as the foreign key of sjk2_table1:

4.6.6 modify the field name of the table

   should pay attention to the compatibility and constraints of field types when modifying the field name of a table, unless the column data is nul:

4.6.7 modify the field type of the table

   should pay attention to the compatibility and constraints of the field type when modifying the field type of the table, unless the column data is null:

4.6.8 modify the field constraints of the table

   Settings default:

   delete default:

4.6.9 Delete table field

   deletes the field xingbie of sjk2_table1:

4.6.10 remove primary key

   removes the primary key of sjk2_table1:

4.6.11 Delete index

   deletes the index of sjk2_table1:

4.7 create a classification table

   creates a classification table classtable:

4.8 disable constraint

   disables constraints for sjk2_table2:

4.9 enable constraints

   enables constraints for sjk2_table2:

4.10 modify the table name

   changed the sjk2_table2 table name to sjk2_newtable2:

4.11 Delete table

   delete table sjk2_newtable2:

4.12 View table status

5. View operation command

5.1 View

The    view is the SQL statement of the query stored in the database.

5.2 create a view

   Create view View name as query

5.3 View View

   Show Create view View name

5.4 View details

   Show table status

5.5 View View Field

   Desc View name

5.6 modify the view

ALTER VIEW View name AS query

5.7 create or modify views

Create or replace view View name as query

6. Backup and recovery of database

   note that the backup and restore commands of the database operate under the bin directory.

6.1 backup of the database

   backup database sjk2:

   views backups in the bin directory:

6.2 recovery of database

   restore just backed up but has been deleted sjk2:

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.