"Database" MySQL from install to command

One, MySQL is installed in the configuration

I was through the way Baidu cloud disk download. Recommended login Baidu Cloud Terminal, and then click the link below, select the version to install, unzip the installation.

Http://www.h2ero.cn/pan/share/17cde6_aWlxjeu.html

(1) Preparation before installation

After decompression, put the MySQL folder into the specified file directory. I was placed directly under the C drive because I had only one drive letter configured in the virtual machine. It is recommended to place other drive characters.

In addition, if you choose to install a free version, the name of the folder after compression is called MySQL. If the version is not installed, then there will be a different version number and the server version number.

(2) Configuring environment variables

There are many. exe files in the bin directory of the mysql-5.6.24-winx64. We installed the MySQL database through the Mysqld.exe file. Such as.

Installation method, we are here by the command line way to install, follow up the Update method. (Note: Do not directly double-click Mysql.exe directly installed)

In addition, to facilitate the use of the command line without first entering the bin directory and then calling it, it is recommended to first configure the environment variables. This allows you to point directly to the directory each time you call MySQL. The environment variable is configured as.

(3) Configuring the My.ini configuration file

After the Mysql-5.6.24-winx64 decompression directory should have a Default.ini file such as. This is the MySQL configuration file, we need to manually set some of them before we can install MySQL, or the direct installation of the MySQL service will not start properly.

If you do not have this file, we recommend that you manually create the My.ini file.

The above shows the Default.ini file that the system automatically configures to us. It is recommended to use this file directly on its basis to edit our My.ini configuration file. If you are afraid of errors in editing, it is recommended to first back up this Defalut.ini file.

Below is the specific content of the My.ini file.

Comments:

Skip-grant-tables This statement is the meaning of setting a null password on the root account when the MySQL database is logged in and skipping it directly. Of course, you can also set the password of root and other login account beforehand. This is because the diagram is convenient to directly configure the blank password login.

Sql_mode=no_auto_create_user,no_engine_substitution Select SQL mode, it is recommended to configure it as No_auto_create_user and no_engine_substitution. Otherwise, when you learn the MySQL statement next time

You will find an error when we insert a table entry using the INSERT statement. This error message and the cause of the error are not explained here.

(4) Command line installation

Mysqld Install

Note: We are installing through the Mysqld.exe in the bin directory instead of Mysql.exe. So be sure not to hit MySQL install when you knock on the command line.

Because I have installed the MySQL database, the command line prompt is already present. If this is the first time the installation is displayed, install successfuly should be installed.

After installation we then enter services.msc via the command line to open our Windows service window and discover a MySQL service. Started status for automatic.

The service that just installed MySQL is automatic state is not started. If you want to start the service. Continue to enter the net start MySQL command.

Add: If net start MySQL appears error:1067 or error:2 error, it must be My.ini configuration error.

It is recommended that you reconfigure My.ini. You can also refer to the following links for troubleshooting. When I installed, there were 2 kinds of errors, the final solution is

The MySQL service was removed via SC delete MySQL first. Then re-take default.ini on its basis to edit the My.ini file and then reinstall the MySQL service after the last restart of the service becomes started state.

Https://jingyan.baidu.com/article/d5c4b52bee6e69da560dc5ec.html

Second, the basic operation of MySQL

(1) Log in to MySQL database

We are logging on to the MySQL database using the following syntax. You can also refer to the contents of the picture in the following steps.

Of course,-h-localhost or-h 127.0.0.1 are meant to be logged in for this machine. Specify the Object server path if MySQL is installed on a different server

Syntax: mysql-h server name-U login account name-p password

(2) Create my first MySQL database

For MySQL database creation we created it through the Create command on the MySQL command line. The specific syntax is as follows.

When we have finished creating the database, we can view it through the command of Show databases.

Syntax: CREATE database name;

(3) Select database

When we use show databases, the command will find that the system lists all the MySQL database currently in the list. It also contains the default database that is automatically created by the system. such as Test.

But when we need to specify a database, we need to select the database by command. This command is for use specific syntax as follows.

Syntax: use database name;

(4) Creating a data table

In a database can have a lot of data tables, we usually also through the data table to store data. Then the data table is divided into different types of data types to store.

For different types of data, specify the data type and format to be stored. Here's how.

Syntax: creat table name ([parameter, type, comment, etc.]); Only one primary key (primary key) can be set in the parameter
Note: (1) Primary key primary KEY (2) default defaults (3) Unique key allowed NULL

Note: Because Chinese is entered in the table, it is necessary to set the following encoding format before entering the code Utf-8
Create database My_home character set UTF8 collate utf8_general_ci;

After you have created the data table, you can view it through the show tables statement. However, you must first use the database name beforehand.

(5) View of data sheet

There are 2 different ways to view data in datasheets and data tables. Below we hit show.

Method One: Show columns from database name. data table name;

Method Two: Describe database name. data table name;

Whichever way you choose. But here is the describe statement except that table can be listed. You can also specify values in the table to enumerate. As shown in.

When the values are too much, the desciribe is more convenient when you want to filter the statements.

(6) Modification of data table structure and data table entry

Modification of the data table structure

Syntax: ALTER TABLE data table name rename changed data table name
Precautions:
When modifying a data table structure, be sure to first select a database using the USE statement.

Modification of data table field information

Syntax: ALTER TABLE data table name change field name [parameter];

Modification of Data table field type information

Syntax: ALTER TABLE data table name change field firstname name [parameter (changes to parameter to change)]


Note: Comparing the upper and lower statements will find the type information for the field name. VARCHAR (10) has been replaced by Char (15). The type is also changed from varchar to char.

Data table modifying field defaults (as in the previous usage)

Syntax: ALTER TABLE data table name change field firstname name [parameter default ' new specified information name '];

Example: ALTER TABLE Moher change major major varchar () default ' Electronic Engineering ' comment ' subject ';

(6) Adding field information in a datasheet

Syntax: ALTER TABLE data table name add field name [parameter]
Example: Alter TABLE mother add address varchar (n) NOT null comment ' home_address ';

Third, the deletion of MySQL data table fields and the deletion of the structure of data tables

The drop command is required when we delete a field in the datasheet or delete it itself.

Please refer to the following picture for specific operation. Note that we are targeting the deletion of data in the database, so when using the drop command to delete the time must be confirmed repeatedly, the command is executed without error.

(1) Deletion of data table fields

Syntax: ALTER TABLE data table name drop field name;
Example: ALTER TABLE student DROP Tel;

(2) Deletion of data table structure

Syntax: DROP table Data structure name
Example: Drop table mother;//once deleted. Data cannot be recovered.

In addition, when the drop statement is used, it can also be used with a combination of parameters. such as if exists,show warnings and so on.

If the addition of the if exists is equivalent to adding a judging condition to see if it exists. If it does not exist, an error message will appear. and combine show warnings; usage. The specific error message can be displayed.

drop table if exists student1111; When deleting a nonexistent data structure, it is recommended to add if exists.

Show warnings; Using the IF exists to use show warnings can capture error-specific information. If exists is not added, the error is automatically thrown.

Four, backup and restore of MySQL database

1. Backup

There are many backup methods for MySQL database, which can be backed up by commands or through third-party tools (for example: Mysqlyog), and today is a command-line way to make a backup of the MySQL database.

Syntax: mysqldump--opt database name-u user name-p-r database address path

2. Restore

For the restore is also very simple, it is important to note that the restore is not using the mysqldump instructions but MySQL.

Syntax: MYSQL-H database Installation Object Server-U login account name-p database name < backup file address

Five, MySQL INSERT statement

1. Take the following list to do an example. If our requirement is to add John Doe personal information, including Name,major,brithday,tel and Sid, to the table.

Then we need to add the INSERT statement. The specific syntax is as follows.

Syntax: INSERT into database name (argument name, parameter name (multiple))

---> Values (' Value of corresponding option ', ' value of corresponding option ');

And even though we haven't talked about the SELECT statement yet. But this is actually listed in the picture. You can conditionally filter the contents of a list by using the SELECT statement. So that we can get what we want.

In addition to the usage of the INSERT statement, the following usage is used in addition to the above usage.

Method two;

Grammar:

Insert into data table name

-->set Field name = ' field value '

-Field name = ' field value ';

Six, select statement

1. Simple Find method

Grammar:

Select field name, field name, field name form data table name

-->where conditional Judgment statement;

If you want to filter multiple conditions, the condition and the condition are connected with the and keyword.

Note: If the filtered results are not met, then "Empty Set (0.00 sec)" is returned, and if the criteria are matched then the matching entries and lists are listed.

2. Select statement with result sorting

Grammar:

Select field name, field name, field name form data table name

--where Field name condition statement

-->order by field name [Field name][asc (ascending)][desc (descending)]

(2) SELECT statement with result ordering

Grammar:
Select field name from table name
where field name condition statement
Order By field name [Field name][asc (ascending)][desc (descending)]

Example 1 "desc":

Example 2 "ASC":

(3) SELECT statement that qualifies the query result entry
Grammar:
Select field name from table name
where field name condition statement
Limit [offset], row_count

Seven, UPDATE statement

Grammar:

Mysql> Update data table name
---->set Field name = ' field value '
---->where Field name condition statement
---->order By field name number of limit entries

"Database" MySQL from install to command