A detailed introduction to the method of creating a MySQL database using commands

The following editor will bring you an article on how to create a MySQL database(de1) using commands. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

1. Connect to MYSQL

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

1. Connect to MYSQL on this machine.

First open the 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 your password. Note that the user name can be preceded by

There can be no spaces, but there must be no spaces before the password, otherwise you will be asked to re-enter the password.

If you have just installed MYSQL, the super user root does not have a password, so just press Enter You can now 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 user name 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 is true for others )

3. Exit the MYSQL command: exit (Enter)

## 2. Change the 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. Then change the root password to djg345.

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

##3. Create database1. 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 3 commands in sequence to complete Database creation. Note: Chinese "Password" and "Database" need to be set by the user himself.

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

Now introduce some commonly used MYSQL commandsNote: You must first log in to MYSQL. The following operations are all prompted by MYSQL symbol, and each command ends with a semicolon.

1. Operation skills#1. If you find that you forgot to add a semicolon after pressing Enter when typing a command, you do not need to type it again. command, just hit a semicolon and press Enter.

That is to say, you can divide a complete command into several lines and type it with a semicolon as the end mark.

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

2. Commonly used commands

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

mysql> SHOW DATABASES;

2. Create database:

mysql> CREATE DATABASE library name;

3. Create data table ：

mysql> USE library name;

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

4. Delete database:

mysql> DROP DATABASE library name;

5. Delete data table:

mysql> DROP TABLE table name;

6. Clear the records in the table:

mysql> DELETE FROM table name;

7. Go to Insert records into the table:

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

8. Update data in the table:

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

9. Use Load data into the data table in text mode:

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 modification root password:

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

mysql> FLUSH PRIVILEGES;

3. An instance of creating a database, creating a table, and inserting data using the

drop database if exists school; //如果存在sudu则删除
create database sudu; //建立库sudu
use school; //打开库sudu
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
); //建表结束
//以下为插入字段
insert into teacher values('','allen','飞数科技1','2005-10-10');
insert into teacher values('','jack','飞数科技2','2005-12-23');如果你在mysql提示符键入上面

command is also possible, but it is inconvenient for debugging.

(1)

You can write the above command as it is into a text file, assuming it is sudu.sql, and then copy it to c:\\ and run it in DOS state Enter the directory \mysql\bin and type the following command:

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

If successful, a blank line will be left blank; if there is an error, there will be a prompt. (The above command has been debugged, you only need to remove the // comment to use it).

(2) Or use mysql> source c:\sudu.sql; after entering the command line. You can also 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 are separated by tab keys On, null values ​​are replaced by \n. Example:

3 rose Feishu Technology 1 1976-10-10

4 mike Feisu Technology 2 1975-12-23

Suppose you save these two sets of data as a speed sudu.txt file and place it in the root directory of the c drive.

2. Data input 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 you must first use the use command to open 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 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 is the detailed content of A detailed introduction to the method of creating a MySQL database using commands. For more information, please follow other related articles on the PHP Chinese website!