MySQL import. SQL file and Common commands

MySQL import. SQL files and Common commands are directly imported into MySQL Qurey Brower *. SQL scripts cannot execute multiple SQL commands at a time. Execute the SQL FILE command in mysql: mysql> source d:/myprogram/database/db. SQL; Appendix mysql Common commands: 1) connect to MYSQL: Format: mysql-h host address-u user name-p User Password 1, Example 1: to connect to MYSQL on the local machine, first open the DOS window, then enter the bin directory under the mysql installation directory, for example, D:/mysql/bin, and then enter the command mysql-uroot-p, after you press enter, you are prompted to enter the password. If you have just installed MYSQL, the Super User root does not have a password, so you can directly press enter to enter MYSQL. The MYSQL prompt is: mysql> 2. Example 2: connect to MYSQL (Remote: IP address) on the remote host. Assume that the IP address of the remote host is 10.0.0.1 and the user name is root. The password is 123. Run the following command: mysql-h10.0.0.1-uroot-p123 (Note: you do not need to add spaces to the u and root nodes.) 3. exit MYSQL Command exit (Press ENTER) (2) change password: Format: mysqladmin-u username-p old password new password 1. Example 1: Add a password 123 to the root user. First, enter the directory C:/mysql/bin in DOS, and then type the following command: mysqladmin-uroot-password 123 Note: because the root has no password at the beginning, therefore, the old-p password can be omitted. 2. Example 2: Change the root password to 456 mysqladmin-uroot-pab12 password 456 (3) Add a new user: (Note: different from the above, the following is a command in the MYSQL environment, so a semicolon is followed as the command Terminator.) Format: grant select on database. * to username @ login host identified by "password" Example 1. Add a user test1 whose password is abc so that he can log on to any host, all databases are permitted to query, insert, modify, and delete databases. First, use the root user to connect to MYSQL, and then type the following command: grant select, insert, update, delete on *. * to test2 @ localhost identified by "abc"; if you do not want test2 to have a password, run another command to remove the password. Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by ""; (4) display command 1. Display database list: show databases; two databases: mysql and test at the beginning. The mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations. 2. display the data table in the database: use mysql; // open the show tables database; 3. display the data table structure: describe table name; 4. create database name; 5. create a table: use database Name; create table Name (field setting list); 6. delete database and table: drop database name; drop table name; 7. Clear the records in the Table: delete from table name; 8. display the records in the Table: select * from table name; export the SQL script mysqldump-u username-p Database Name> storage location mysqldump-u root-p test> c:/. SQL import SQL script mysql-u username-p database name <storage location mysqljump-u root-p test <c:/. SQL Note: The test database must already have the MySQL Export Import command Example 1. export mysqldump-u user name-p Database Name> exported file name mysqldump-u wcnc-p smgp_1__wcnc> wcnc. sql2. export a table mysqldump-u user name-p Database Table Name> export file name mysqldump-u wcnc-p smgp_1__wcnc users> wcnc_users.sql3. export a database structure mysqldump-u wcnc-p-d -- add-drop-table smgp_1__wcnc> d: wcnc_db.sql-d has no data -- add-drop-table add a drop table4. import database common source command before each create statement to enter mysql Database Console, for example, mysql-u root-pmysql> use Database and then use the source command. The following parameter is a script file (as used here. SQL) mysql> source d: wcnc_db. SQL