Use the command to create MySQL database (de1), mysqlde1

Source: Internet
Author: User
Tags mysql commands

Use the command to create MySQL database (de1), mysqlde1

1. Connect to MYSQL

Format: mysql-h host address-u user name-p User Password

1. Connect to MYSQL on the local machine.

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

There can be spaces or spaces before the name, but there must be no spaces before the password. Otherwise, you can re-enter the password.

If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is:
Mysql>

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

Enter the following command:

Mysql-h110.110.110.110-u root-p 123)

3. exit MYSQL command: exit (Press ENTER)

Ii. Change the password

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

1. Add a password ab12 to the root user. First, enter the mysql \ bin directory under 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 name;

2. grant select, INSERT, UPDATE, DELETE, CREATE, DROP, alter on database name. * TO database name
@ Localhost identified by 'Password ';

3. SET PASSWORD

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

Execute three commands in sequence to create a database. Note: You must set the Chinese "password" and "Database.

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

Now we will introduce some common MYSQL commands

Note: you must first log on to MYSQL. The following operations are performed at the MYSQL prompt and each command ends with a semicolon.

I. Operation Skills

1. If you forget the extra points after you press Enter when making the command, you don't have to repeat the command. You just need to press a semicolon to press Enter.

That is to say, you can divide a complete command into several lines, and then use a semicolon as the end sign to complete the operation.

2. You can use the cursor to bring up or down the previous commands.

Ii. Common commands

1. display the list of databases on the current database server:

Mysql> show databases;

2. Create a database:

Mysql> create database name;

3. Create a data table:

Mysql> USE Database Name;

Mysql> create table Name (field name VARCHAR (20), field name CHAR (1 ));

4. delete a database:

Mysql> drop database name;

5. delete a data table:

Mysql> drop table name;

6. Clear the records in the table:

Mysql> delete from table name;

7. insert records into the table:

Mysql> insert into table name VALUES ("hyq", "M ");

8. Update table data:

Mysql-> UPDATE table name: SET field name: 1 = 'a'; field name: 2 = 'B' WHERE field name: 3 = 'C ';

9. load data into a data table in text mode:

Mysql> load data local infile "D:/mysql.txt" into table name;

10. Import the. SQL FILE command:

Mysql> USE Database Name;

Mysql> SOURCE d:/mysql. SQL;

11. Change the root password on the command line:

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

Mysql> flush privileges;

3. An instance for creating a database, creating a table, and inserting data

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

Create database sudu; // create a database sudu

Use school; // open the sudu Library

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

); // Table creation ends

// Insert fields as follows

Insert into teacher values ('', 'allen ', 'apsara stack 1', '2017-10-10 ');

Insert into teacher values ('', 'jack', 'apsara stack 2', '2017-12-23 '); if you type
But it is not convenient to debug.

(1)You can write the above commands into a text file as they are, for example, sudu. SQL, then copy them to c: \, and enter the directory in DOS status
\ Mysql \ bin, and then type the following command:

Mysql-uroot-p password <c: \ sudu. SQL

If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. (The preceding command has been debugged. You only need to remove the // annotation to use it ).

(2) You can use mysql> source c: \ sudu. SQL after entering the command line, or import the sudu. SQL file to the database.

4. Transfer text data to the database

1. Text data should conform to the format: field data is separated by the tab key, and the null value is replaced by \ n. For example:

3 rose Apsara Technology 1 1976-10-10

4 mike Apsara Technology 2 1975-12-23

Assume that you store these two sets of data as a sudu.txt file and put it under the c-drive root directory.

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

Note: You 'd better copy the file to the \ mysql \ bin directory and use the use command to create the database where the table is located.

V. Back up the database: (execute the command in the \ mysql \ bin directory of DOS)

1. Export the entire database

The exported files are 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 user name-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 adds 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 command to create a MySQL database (de1) is to share with you all the content, hope to give you a reference, but also hope you can support a lot of help homes.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.