MySQL Common skills

1. View MySQL version information:

1, Mount mysql>status;

2, Mount Mysql>select version ();

3, Mount mysql> select @ @version;

4, not login [[email protected] ~]# mysql-v

5, not login [[email protected] ~]# MySQL--help | grep distrib;

2.MySQL Service startup:

1,window Start the service net start MySQL

2,window Stop the service net stop MySQL

3,linux Start Services service MySQL start

4,linux Stop Services service MySQL stop

3. When you start MySQL on multiple ports on the system:

1, connect the way: mysql-uroot [email protected]-P 3306-s/mysqldata/database/data3306/mysql1.sock

2. Perform the following command when there is a problem with the link:

A) Mysql>grant all privileges on * * to [email protected] '% ' identified by ' password ' with GRANT option;

b) Mysql>flush privileges;

Some common viewing commands for 4.MySQL are:

1, view database mysql>show databases;

2, select Database Mysql>use databasesname;

3. View the functions that exist in the database mysql> show function status;

4, view View Mysql>select * from INFORMATION_SCHEMA. views;

5, view table mysql>show tables;

6, view trigger mysql>show triggers;

7, view current user mysql> Select User ();

8, view all users mysql>select User,host,password from Mysql.user;

9, view MySQL character set mysql>show variables like '%char% ';

10, view the character set of the database Mysql>show the CREATE database databasename;

11, view the character set of the table Mysql>show create table Databasename.tablename \g;

12. View the currently selected data Mysql>select database ();

5.MySQL some operation commands for tables and data:

1. Load the data from the text file into the table: Mysql>load data local infile "mytable.txt" into tables mytable;

2, add primary key:mysql> ALTER TABLE TableName ADD PRIMARY key (ID) to the field;

3. Delete primary Key:mysql>alter table tablename drop primary key; or Mysql>drop primary key on TableName;

4. Add a new field: Mysql>alter table tablename Add Column ColumnName char (1) or Mysql>alter table tablename add field int (11) Nosiged NOT NULL;

5, delete field: Mysql>alter table tablename drop column C;

6, add an index on column D and make column a the primary key: Mysql>alter table TableName Index (d), add primary key (a);

7, add a new auto_increment integer column named C:mysql>alter table tablename add c int unsigned not NULL auto_increment, add index (c);

Solutions to problems with 6.MySQL operation:

1, a function cannot be established:

Mysql>show variables like ' log_bin_trust_function_creators ';

Mysql>set Global Log_bin_trust_function_creators=1;

MySQL Common skills