Mysql notes and mysql notes

Mysql notes and mysql notes

I am still familiar with oracle and have some scattered knowledge about mysql. Today I have read it and summarized it briefly:

1. View mysql version: mysqladmin -- version
2. view the mysql process: ps-ef | grep mysqld
3. Shut down the database: mysql-u root-p shutdown
4. Start the service: mysqld_safe
5. Create a user:
Insert into user (host, user, authentication_string, select_priv, insert_priv, update_priv) values ('localhost', 'xyz', PASSWORD ('abcd _ 100'), 'y ', 'y', 'y ');
ERROR 1364 (HY000): Field 'ssl _ cipher 'doesn' t have a default value

If an error is reported, mysql prohibits direct insert to create a user for security purposes,
The my-default.ini has a statement:
SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
Delete STRICT_TRANS_TABLES and use insert to add

We recommend that you use the grant method.
Grant select, INSERT, UPDATE, DELETE, CREATE, drop on mysql. * TO 'xyz' @ 'localhost' identified by 'abcd _ 100 ';
Pay attention to flush privileges;

6. view the database: show databases
7. create a database: mysqladmin-u root-p create RUNOOB
8. Delete the database: mysqladmin-u root-p drop RUNOOB
9. Select Database: use RUNOOB
10. view the database in use: select database (); or directly status;
11. Check whether the database is automatically submitted: show variables like '% autocommit %'; you can change it through set autocommit = 0/1.
12. navicat remote connection to mysql:
Host is not allowed to connect mysql
This is a user permission issue. You can use the root user to execute the following statement:
Update user set host = '%' where user = 'xxxx ';
Flush privileges;
13. view the table field: show columns from table1;
14. ADD an INDEX: ALTER table tableName add index indexName (columnName );
15. view the table creation statement: show create table table1;
16. Common query statements:
Command description
Select version () server VERSION information
Select database () Current DATABASE Name (or return NULL)
Select user () Current USER Name
Show status Server STATUS
Show variables server configuration VARIABLES
17. Export the table to a file:
SELECT * FROM passwd into outfile '/tmp/tutorials.txt ';
18. Import the file to the table:
Load data local infile 'dump.txt 'into table mytbl;