How to create a MySQL database (de1) using commands

1. Connect to MYSQL

Format: mysql -h host address -u username -p user password

1. Connect to MYSQL on this machine.

First open a DOS window, then enter the directory mysql\bin, and then type the command mysql -u root -p. After pressing Enter, you will be prompted to enter the password. Note that the user

There can be a space before the name or not, but there must be no space before the password, otherwise you will be asked to re-enter the password.

If MYSQL has just been installed, the super user root has no password, so just press Enter to enter MYSQL. The MYSQL prompt is:
mysql>

2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the username is root, and the password is abcd123.

Then type the following command:

mysql -h110.110.110.110 -u root -p 123; (Note: there is no need to add a space between u and root, and the same applies to the others)

3. Exit MYSQL command: exit (Enter)

2. Change password

Format: mysqladmin -u username -p old password password new password. For example

1. Add a password ab12 to root. First, enter the directory mysql\bin in DOS, and then type the following command

mysqladmin -u root -password ab12

2. Change the root password to djg345.

mysqladmin -u root -p ab12 password ******

3. Create a database

1. CREATE DATABASE database name;

2. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON database_name.* TO database_name
@localhost IDENTIFIED BY 'password';

3. SET PASSWORD FOR

'Database name'@'localhost' = OLD_PASSWORD('password');

Execute the three commands in sequence to complete the database creation. Note: The Chinese "password" and "database" need to be set by the user himself.

—————————————————————————————————————————————

Now let's introduce some commonly used MYSQL commands

Note: You must log in to MYSQL first. The following operations are all performed at the MYSQL prompt, and each command ends with a semicolon.

1. Operation skills

1. If you forget to add a semicolon after pressing Enter when typing a command, you do not need to retype the command, just type a semicolon and press Enter.

That is to say, you can break a complete command into several lines and use a semicolon as the end mark.

2. You can use the cursor up and down keys to call up previous commands.

2. Common commands

1. Display the list of databases in the current database server:

mysql> SHOW DATABASES;

2. Establish database:

mysql> CREATE DATABASE library name;

3. Create a data table:

mysql> USE library name;

mysql> CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1));

4. Delete the database:

mysql> DROP DATABASE library name;

5. Delete the data table:

mysql> DROP TABLE table name;

6. Clear the records in the table:

mysql> DELETE FROM tablename;

7. Insert records into the table:

mysql> INSERT INTO table name VALUES ("hyq","M");

8. Update the data in the table:

mysql-> UPDATE table name SET field name 1 = 'a', field name 2 = 'b' WHERE field name 3 = 'c';

9. Load data into the data table using text:

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE table name;

10. Import .sql file command:

mysql> USE database name;

mysql> SOURCE d:/mysql.sql;

11. Modify the root password via command line:

mysql> UPDATE mysql.user SET password=PASSWORD('new password') WHERE User='root';

mysql> FLUSH PRIVILEGES;

3. An example of building a database and table and inserting data

drop database if exists school; //If sudu exists, delete it

create database sudu; //Create database sudu

use school; //Open library sudu

create table teacher //Create table TEACHER

(

id int(3) auto_increment not null primary key,

name char(10) not null,

address varchar(50) default 'Shenzhen',

year date

); //End of table creation

//The following is the inserted field

insert into teacher values('','allen','Feishu Technology 1','2005-10-10');

insert into teacher values('','jack','飞数科技2','2005-12-23');If you type the above command in the MySQL prompt, it will work, but it is not convenient for debugging.

(1) You can write the above command as is into a text file, let's say sudu.sql, and then copy it to c:\\ and enter the directory in DOS mode.
\mysql\bin, and then type the following command:

mysql -uroot -p password < c:\sudu.sql

If successful, a line will be left blank without any display; if there is an error, there will be a prompt. (The above commands have been debugged, you just need to remove the // comments to use them).

(2) Alternatively, you can use mysql> source c:\sudu.sql; after entering the command line to import the sudu.sql file into the database.

4. Transfer text data to the database

1. The format that text data should conform to: field data is separated by the tab key, and null values ​​are replaced by \n. Example:

3 rose Feishu Technology 1 1976-10-10

4 Mike Feishu Technology 2 1975-12-23

Suppose you save these two sets of data as the speedsudu.txt file in the root directory of drive C.

2. Data transfer command load data local infile "c:\sudu.txt" into table table name;

Note: You'd better copy the file to the \mysql\bin directory, and use the use command to open the library where the table is located first.

5. Back up the database: (the command is executed in the \mysql\bin directory of DOS)

1. Export the entire database

The export file is stored in the mysql\bin directory by default.

mysqldump -u username -p database name > exported file name

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. Export a table

mysqldump -u username -p database name table name > exported file name

mysqldump -u user_name -p database_name table_name > outfile_name.sql

3. Export a database structure

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql

-d no data --add-drop-table add a drop table before each create statement

4. Export with language parameters

mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt
database_name > outfile_name.sql

The above method of using commands to create a MySQL database (de1) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.