Method of creating MySQL database of de1 with command

  • 2021-07-18 09:11:43
  • OfStack

1. Connect MYSQL

Format: mysql-h Host Address-u User Name-p User Password

1. Connect to MYSQL on this machine.

First open the DOS window, then enter the directory mysql\ bin, then type the command mysql-u root-p, and prompt you for the password after entering. Pay attention to the user

The name may or may not be preceded by a space, but the password must not be preceded by a space, otherwise you will be asked to re-enter the password.

If you have just installed MYSQL, the super user root has no password, so you can enter MYSQL directly. The prompt of MYSQL is:
mysql >

2. Connect to MYSQL on the remote host. Assume that the remote host has an IP of 110.110. 110.110, a username of root, and a password of abcd123.

Type the following command:

mysql-h110.110-u root-p 123; (Note: There is no need to add spaces between u and root, and others are also 1.)

3. Exit MYSQL command: exit (Enter)

Step 2 Change your 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 under DOS, and then type the following command

mysqladmin -u root -password ab12

2. Change the password of root 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 three commands in turn to complete the database creation. Note: Chinese "password" and "database" are set by users themselves.

----------------------------------------------

Now let's introduce some commonly used MYSQL commands

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

1. Operational skills

1. If you forget to add a semicolon after entering the car when you type the command, you don't need to type the command again, just type a semicolon to enter the car.

That is to say, you can divide a complete command into several lines to type, and then use semicolons as the closing sign to OK.

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

2. Common commands

1. Displays a list of databases in the current database server:

mysql > SHOW DATABASES;

2. Establish a database:

mysql > CREATE DATABASE library name;

3. Establish 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. Empty the records in the table:

mysql > DELETE FROM table name;

7. Insert a record into a 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 in 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. Command line modifies root password:

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

mysql > FLUSH PRIVILEGES;

3. 1 instance of database building and table building and data insertion

drop database if exists school; //Delete if sudu exists

create database sudu; //Build Library sudu

use school; //Open the 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 building

//The following are the insert fields

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

insert into teacher values ('', 'jack', 'Flying Digital Technology 2', '2005-12-23'); If you type above at the mysql prompt
The command of can also be used, but it is not convenient to debug.

(1) You can write the above commands as they are to a text file, assuming sudu. sql, then copy them to c:\\, and enter the directory in DOS state
\ mysql\ bin, and then type the following command:

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

If successful, leave 1 line without any display; If there is an error, there will be a prompt. (The above command has been debugged, so you can use it only by removing the comment of//).

(2) Or use mysql after entering the command line > source c:\ sudu. sql; You can also import the sudu. sql file into the database.

4. Transfer text data to a database

1. Format of text data: Field data are separated by tab key, and null value is replaced by\ n

3 rose Feishu Technology 1 197 6-10-10

4 mike Feishu Technology 2 197 5-12-23

Suppose you save these two sets of data as speed sudu. txt files and put them in the root directory of c disk.

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

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

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

1. Export the entire database

The export file exists in the mysql\ bin directory by default

mysqldump-u Username-p Database Name > File name exported

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. Export 1 table

mysqldump-u Username-p Database Name Table Name > File name exported

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 1 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


Related articles: