Mysql study NOTE _ MySQL-mysql tutorial

As an O & M personnel, you should not only learn how to build and install mysql services, but also understand the use of basic commands and syntaxes. This is the note I have summarized after learning, and I hope it will be helpful to Cainiao like me. 1. log on to the mysql database1. connect to the local mysql database. the default port is 3306. # Mysql-u root-p 123456 //-u: specifies the user-p: specifies the password corresponding to the user. 2. connect to the remote mysql server through the IP address and port. # Mysql-u root-p 123456-h 192.168.100.1-P 3306 //-h: specifies the database server IP address-P: specifies the port number to be connected. 3. manage multiple MySQL instances with different ports through TCP connections (note: This function is available in MySQL or later versions) # mysql-u root-p 123456 -- protocol = tcp -- host = localhost -- port = 3307 4. manage multiple MySQL instances with different ports through socket # mysql-u root-p -- socket =/tmp/mysql3307.sock 5. exit mysql.> exit; II. database operation statements1. display all databases. > Show databases; 2. create a database named test1234. > Create database test1234; 3. select the database test1234. > Use test1234; 4. delete the database test1234. > Drop database test1234; III. table operation statements1. display all tables in the current database.> Show tables; 2. create a user table in the current database. The field id is the primary key and unique index, and the storage engine is MYISAM.> Create table user (id int (5) unsigned, name char (10) not null, pass char (20) not null, primary key (id) engine = myisam; 3. view the structure of the user table.> Describe user; 4. query records in the user table.> Select * from user; 5. specify the condition query in the user table.> Select * from user where name = 'hangsan'; 6. query the user table and sort by id.> Select * from user order by id desc; 7. specify the field name and pass in the user table for query.> Select name, pass from user; 8. query unique records that are not repeated in the user table.> Select distinct name from user; 9. Insert records into the user table.> Insert into user (id, name, pass) values (1, 'hangsan', '000000'); 10. update the specified information in the user table.> Update user set pass = '000000' where name = 'hangsan '; 11. add the date field to the pass field of the user table.> Alter table user add column date char (8) after pass; 12. modify the pass field name and field data type in the user table.> Alter table user change column pass password char (10); 13. delete the date field from the user table.> Alter table user drop column date; 14. delete the specified information in the user table.> Delete from user where id = 1; 15. delete all information in the user table.> Delete from user; 16. delete the user table.> Drop table user; IV. database permission operations1. create an account named test123 with root permission and can log on from any IP address. > Grant all privileges on *. * to 'test123 '@' % 'identified by '000000'; 2. create an account test1234 with data operation permissions for the mysqltest database, and only log on from 192.168.1.10. > Grant select, insert, update, delete on mysqltest to 'test1234' @ '192. 168.1.10 'identified by '123'; 3. revoke some data operation permissions of the test1234 account for the mysqltest database. > Revoke insert, update, delete on mysqltest from 'test1234' @ '192. 168.1.10 'identified by '1234'; 4. delete the user test1234. > Drop user 'test1234' @ '% '; V. mysqld backup and restoration1. back up the mysqltest database. # Mysqldump-u root-p 123456 mysqltest> bak_mysqltest. SQL 2. back up data in the user table in the mysqltest database. # Mysqldump-u root-p 123456 mysqltest user> bak_table_mysqltest_user. SQL 3. back up and compress the mysqltest database. # Mysqldump-u root-p 123456 mysqltest | gzip> bak_mysqltest. SQL .gz 4. restore mysqltest. SQL to the mysqltest database. # Mysql-u root-p 123456 mysqltest <bak_mysqltest. SQL if the database does not contain the mysqltest library, you must first create this library. Then perform the import operation. 5. restore data in the user table. # Mysql-u root-p 123456 mysqltest <bak_table_mysqltest_user. SQL 6133 import data from the compressed file bak_mysqltest. SQL .gz to the mysqltest database. # Gzip <bak_mysqltest. SQL .gz | mysql-u root-p 123456 mysqltest VI. other mysql commands1. start the mysql service. # Service mysqld start source code package installation is not applicable #/etc/init. d/mysqld start source package installation is not applicable #/usr/local/mysql/bin/mysqld_safe-user = mysql & 2. disable the mysql service. # Service mysqld stop source package installation is not applicable #/etc/init. d/mysqld stop source package installation is not applicable #/usr/local/mysql/bin/mysqladmin-p 123456 shutdown here-p refers to the password of the mysql administrator account. 3. restart the mysql service. # Service mysqld restart source package installation is not applicable #/etc/init. d/mysqld restart source package installation is not applicable 4. add the mysql package installed in the source package to start automatically. # Echo "/usr/local/mysql/bin/mysqld_safe -- user = mysql &">/etc/rc. local 5. add the mysql installed in the source code package to the environment variable. # Echo "export PATH = $ PATH:/usr/local/mysql/bin">/etc/profile add the environment variable # source/etc/profile update the environment variable