MySQL Note 2 Basic commands

1. Start MySQL console in cmd under Window

Run cmd: D: // enter the D disk CD MySQL Directory D: \ myslq \ bin> mysql-u root-p -- enter the password

 

Ii. Configure preparations before MySQL imports SQL files

1. Users with server permissions. Copy the file directly. Win is generally your username under data in the MySQL installation directory. Linux is the VaR in the installation directory, which is unknown to other systems.

 

2. In the MySQL + PhP combination, MySQL has a size limit when importing SQL files, which is a PHP configuration problem. You need to modify the configuration in PHP. ini.

upload_max_filesizememory_limitpost_max_size

 

 

Iii. MySQL console SQL File Import

Method 1: Use the source command

Log on to the MySQL console and then operate the database.

Mysql> Use Database Name mysql> Source Path \ SQL File

 

Method 2:

Import the SQL statements in the file into the database from E: \ mysql \ mydb2. SQL:

1. Enter MySQL from the command line, and then run the create database mydb2 command to create the database mydb2.

2. To exit MySQL, enter the command exit or quit;

3. Enter the following command in cmd:

c:\>MySQL -h localhost -u root -p mydb2 < e:\MySQL\mydb2.sql 

Then enter the password, and then OK.

 

 

 

4. Export Database files from a database:

PS: mysqldump preparations before use:

How does mysqldump use mysqldump? The mysqldump syntax is incorrect:

Check whether the mysqldump command is running in mysql> or c: \ Documents and Settings \ Administrator>; however, if 'mysqldump 'is not an internal or external command, it is not a executable program or a batch file. Your environment variables are not properly configured; the environment variables are set:

1: first find the bin directory of MySQL (my directory is D: \ Program Files \ mysql \ MySQL Server 5.1 \ bin)

2: Right-click my computer-> properties-> advanced-> environment variables-> system variables-> double-click the PATH variable name-> modify the variable value with a semicolon, if yes, do not paste the bin directory of MySQL;

 

1. Export the database mydb to the E: \ mysql \ mydb. SQL file:

Open start> RUN> Enter cmd to enter Command Line Mode

c:\>MySQLdump -h localhost -u root -p mydb >e:\MySQL\mydb.sql 

Enter the password and wait for a moment until the export is successful. You can check whether the export is successful in the target file.

2. Export mytable in mydb to the E: \ mysql \ mytable. SQL file:

c:\>MySQLdump -h localhost -u root -p mydb mytable>e:\MySQL\mytable.sql 

3. Export the database mydb structure to the E: \ mysql \ mydb_stru. SQL file:

c:\>MySQLdump -h localhost -u root -p mydb --add-drop-table >e:\MySQL\mydb_stru.sql 

-H localhost can be omitted, which is generally used on a VM.

Parameter description:

-N: -- no-create-dB (no information for creating a database)-D: -- no-data (no data exported)-T: -- no-create-Info (no information for creating database tables)-R: -- routines dump stored routines (functions and procedures)-E: -- events (exporting events in the database) -A: -- all-databases (export all databases)-B: -- databases db1 DB2 (export the specified database)

 

5. Collect Common commands

Exist?

MySQL> use imss;  MySQL> select if(exists(select * from information_schema.columns where table_name = 'imss_righttemplate' and column_name='id_righttemplate'),'yes','no') as cz ;

Common MySQL commands: Primary Key

MySQL> alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id); 

Add a new column

MySQL> alter table t2 add d timestamp;  MySQL> alter table infos add ex tinyint not null default '0'; 

Delete column

MySQL> alter table t2 drop column c; 

Rename a column

MySQL> alter table t1 change a b integer; 

Change column type

MySQL> alter table t1 change b b bigint not null;  MySQL>alter table infos change list list tinyint not null default '0'; 

Common MySQL commands: rename a table

MySQL> alter table t1 rename t2; 

Add Index

Mysql> alter table tablename change depno int (5) not null; mysql> alter table tablename add Index name (field name 1 [, field name 2…]); Mysql> alter table tablename add index emp_name (name );

Index with primary keywords

MySQL> alter table tablename add primary key(id); 

Add an index with unique conditions

MySQL> alter table tablename add unique emp_name2(cardnumber); 

Delete An index

MySQL>alter table tablename drop index emp_name; 

Add Field

MySQL> ALTER TABLE table_name ADD field_name field_type; 

Modify the original field name and type

MySQL> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 

Delete Field

MySQL> ALTER TABLE table_name DROP field_name;