Javaweb MySQL Database basics

1. Common commands

1.1 Installing the database
Take mysql56 as an example, store on the D drive, open cmd, and then enter the following command:
(1) d:
(2) CD mysql56
(3) CD bin
(4) Mysqld-install
can be installed.
1.2 Starting the database
Enter the following command in the bin directory of the MySQL store:

1.3 Logging into the database
Enter the following command and password in the bin directory:

1.4 Modifying the database password
In the Bin directory, enter the following command and the modified password, 4214963, which is the new password set:

1.5 Closing the database
Enter the following command in the bin directory:

1.6 Viewing all databases in the system
show databases;
Use the command prompt to view:

Use the database tool to view, enter the command, select the command, and click Run:

1.7 Creating a database
For example, create a database named SJK1, enter the database after entering the following command, where the command if not exists is to prevent the creation of the database name in the system already exists, if you can ensure that the name of the database is not duplicated, you can omit if not exists
Create a database named SJK1 using the command prompt:

Create a database named SJK2 using the Database Tools:

View all databases in the system in the tool and check if the database was created successfully:

1.8 using the database
For example, use SJK1:
Using the command prompt:

Using Database Tools:

1.8 Deleting a database
For example, delete sjk1:
Using the command prompt:

Using Database Tools:

f,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=)
1.9 View Storage engine commands
Using the command prompt:

Using Database Tools:

2. The common concept of database and its interrelation

Database: A database management system (DBMS) can manage multiple databases
Data table: Multiple data tables in a database
Data rows: Multiple rows of data in a single data table
Data column (field): Row of data has multiple columns
Views: Virtual Tables
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 a relationship between two tables
Transactions: A program unit that updates data

3. mysql System database

3.1information_schema
Some database objects in the primary storage system
3.2performance_schema
Primary Storage database server performance parameters, typically used by DBAs and advanced users to analyze database performance bottlenecks
3.3Mysql
Primary storage System User rights information

4. Operation commands for tables in the database

Take database Tools as an example
4.1 Creating a table
Create a table named Sjk2_table1, and add the field ID and name, where int and varchar (10) are the ID field and type 10 of the Name field, respectively, to limit the length of the field, primary key is a constraint on the field ID, represents the primary key, Unique is a constraint on name and represents a unique designation:

4.2 Display Table
Show all tables in SJK2:

4.3 copying tables
Copy the table Sjk2_table1, and the copied table name is sjk2_table2:

4.4 Copying tables without copying constraints
Copy the table Sjk2_table1, and the copied table name is sjk2_table3:

4.5 View Table Structure
To view the structure of a table sjk2_table1:

4.6 Modifying a table
4.6.1 Adding a table field
Add a field to the Sjk1_table1 table sex:

4.6.2 Adding an index
To add an index to the Sjk1_table1 table:

4.6.3 Adding a primary key
To add a primary key to the Sjk1_table3 table:

4.6.3 Add unique
Add unique to the Sjk1_table3 table:

4.6.4 Adding a category ID
Add a category Id,classid to the Sjk2_table1 table:

4.6.5 Adding foreign KEY constraints
Add the primary key in Skj2_table3 as a foreign key for Sjk2_table1:

4.6.6 Modifying a table's field names
When modifying a table's field names, be aware of the compatibility and constraints of the field type, unless the column data is nul:

4.6.7 Modifying the field type of a table
When modifying a table's field type, be aware of the compatibility and constraints of the field type, unless the column data is null:

4.6.8 Modifying a table's field constraints
Set Default:
Delete default:
4.6.9 Delete a table field
Delete the Sjk2_table1 field Xingbie:

4.6.10 Deleting a primary key
To delete a Sjk2_table1 primary key:

4.6.11 Deleting an index
To delete the Sjk2_table1 index:

4.6.12 deleting foreign keys
To delete a foreign key for Sjk2_table1:

4.7 Creating a classification table
Create a classification table classtable:

4.8 Disabling constraints
To disable SJK2_TABLE2 constraints:

4.9 Enabling constraints
To enable SJK2_TABLE2 constraints:

4.10 Modifying table names
Change the Sjk2_table2 table name to Sjk2_newtable2:

4.11 Deleting a table
To delete a table sjk2_newtable2:

4.12 Viewing table status

5. View Operation commands

5.1 views
Views are SQL statements that are stored in queries in the database.
5.2 Creating a View
Create View name as Query
5.3 View View
Show Create View Name
5.4 Viewing View details
Show Table Status
5.5 Viewing view fields
DESC View Name
5.6 Modifying views
ALTER view view name as Query
5.7 Creating or modifying a view
Create or Replace view view name as Query

6. Backup and Recovery of database

Note that the backup and restore commands for the database operate in the bin directory.
6.1 Backup of the database
Backing up the database sjk2:

To view the backup in the bin directory:

6.2 Recovery of the database
Recovery has just been backed up but has been removed SJK2:

Javaweb MySQL Database basics