Learning simple mysql commands

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, then enter the directory mysql/bin, then type the command mysql-uroot-p, and press enter to prompt you to enter the password. note that there can be space or space before the user name, but there must be no space 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. Run the following command: mysql-h110.110.110.110-uroot-p123; (Note: you do not need to add spaces between u and root.) 3. exit MYSQL: exit (Press ENTER) 2. Change the password. Format: mysqladmin-u username-p old password new password (this command was not found during the test, it turns out that the version is too low) Official Website: mysqladmin-Client for Administering a MySQL Server http://dev.mysql.com/doc/refman/5.1/en/mysqladmin.html 1. Add a password ab12 to the root user. First, enter the mysql/bin directory in DOS, and then type the following command mysqladmin-u root-password ab12. Note: because the root has no password at the beginning, therefore, the old-p password can be omitted. 2. Change the root password to djg345. Mysqladmin-u root-p ab12 password djg345 3. Add new users. (Note: Unlike the above, the following commands are 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" 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 [email = test1 @ "%] test1 @" % [/email] "Identified by" abc "; however, the added users are very dangerous, if someone knows the password of test1, he can log on to your mysql database on any computer on the internet and do whatever he wants for your data, for the solution, see 2. 2. Add a user named "test2" with the password "abc" so that the user can only log on to localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located. In this way, the user knows the password of test2, and cannot directly access the database from the internet, but can only access the database through the web page on the MYSQL host. Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by "abc"; www.2cto.com if you do not want test2 to have a password, you can run another command to remove the password. Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by ""; next I will discuss database operations in MYSQL. 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 Tips 1. If you forget the extra points when you press enter, you don't have to repeat the command. Just press a semicolon and 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. 2. Display command 1. display the Database List on the current database server: mysql> show databases. Note: the mysql database contains MYSQL system information. Change the password and add new users, this database is actually used for operations. 2. display the data TABLES in the database: mysql> USE Database Name; mysql> show tables; 3. display the data table structure: mysql> DESCRIBE table name; 4. Create a database: mysql> create database name; 5. CREATE a data TABLE: mysql> use database Name; mysql> create table Name (field name: VARCHAR (20); field name: CHAR (1 )); 6. DELETE a DATABASE: www.2cto.com mysql> drop database name; 7. DELETE a data TABLE: mysql> drop table name; 8. Clear the TABLE records: mysql> delete from table name; 9. Display records in the Table: mysql> SELECT * FROM table name; 10. INSERT records INTO the table: mysql> insert into table name VALUES ("hyq", "M "); 11. Update table data: mysql-> UPDA TE table name SET field name 1 = 'A', field name 2 = 'B' WHERE field name 3 = 'C'; 12. load data into the data table in text mode: mysql> load data local infile "D:/mysql.txt" into table Name; 13. Import. SQL FILE command: mysql> USE Database Name; mysql> SOURCE d:/mysql. SQL; 14. Run the command line to change the root password: mysql> UPDATE mysql. user SET password = PASSWORD ('new password') WHERE User = 'root'; mysql> flush privileges; 15. display the DATABASE Name of use: mysql> select database (); 16. display the current user: mysql> select user (); add: mysql> statusmysql Ver 14.12 D Istrib 5.0.45, for Win32 (ia32) Connection id: 6 Current database: database Name Current user: root @ localhostSSL: Not in useUsing delimiter:; www.2cto.com Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL) Protocol version: 10 Connection: localhost via TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306 Uptime: 35 min 48 sec 3. One Database creation, table creation, and data insertion instance drop database if exists school; // if there is SCHOOL, delete create database school; // create a database SCHOOLuse school; // open the database SCHOOLcreate table teacher // create the table TEACHER (id int (3) auto_increment not null primary key, name char (10) not null, www.2cto.com address varchar (50) default 'shenzhen ', year date); // The end of table creation // insert into teacher values (", 'allen', 'dalian Zhongyi ', '1970-10-10'); insert into teacher values (", 'jack', 'dalian No. 2 middle 2', '19 -12-23 '); if you type the above command at the mysql prompt, but it is not convenient for debugging. (1) You can write the above commands into a text file as they are, for example, school. SQL, copy them to c: //, and enter the directory [url = file:////mysql//bin ] // Mysql // bin [/url], and then enter the following command: mysql-uroot-p password <c: // school. if the SQL statement is successful, no display is displayed for a blank row. If an error occurs, 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: // school. SQL after entering the command line. You can also import the school. SQL file to the database. 4. convert text data to the database. 1. The text data must conform to the format: field data is separated by the tab key, and null values are separated by [url = file:////n ] // N [/url] to replace. Example: 3 rose Dalian No. 2 Middle School 1976-10-104 mike Dalian No. 1 1975-12-23assume that you save the two sets of data as a school.txt file and put it under the c-drive root directory. 2. data Import command load data local infile "c: // school.txt" into table name; Note: You 'd better copy the file to [url = file:////mysql//bin ] // Mysql // bin [/url] directory, and use the use command to create the database where the table is located. Www.2cto.com v. Back up the database: (the command is in the DOS [url = file:////mysql//bin ] // Mysql // bin [/url] Directory) 1. by default, the exported file for the entire database exists in mysqldump-u username-p Database Name> exported file name mysqldump-u user_name-p123456 database_name> outfile_name.sql2 In the mysql/bin directory. 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.sql3. 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 table4. language before each create statement export the mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name. SQL author wenjinglian