Basic Mysql operations

Basic Mysql operations

Today, I finally reviewed the Knowledge System of the MySQL database and sorted out some common basic operations below.

I. MySQL server startup and Shutdown

1. Start the MySQL server

Start-> Run and enter "cmd", and then enter the "net start MySQL" command at the command prompt.

2. Connect to the MySQL server

Enter the command: MySQL-uroot-h127.0.0.1-p111 (-h127.0.0.1 depends on your situation)

Note: The username is "root", the MySQL database server address is "127.0.0.1", and the password is "111". There must be spaces between them.

3. Shut down the MySQL server

Start-> Run and enter "cmd", and then enter the "net stop MySQL" command at the command prompt.

Ii. Operate MySQL Databases

1. Create a database

Create database name;

2. view the database

Show databases;

3. Select a database

Use Database Name;

4. delete a database

Drop database name;

Note: The "C:/AppServ/MySQL/data" folder in the MySQL installation directory is automatically deleted.

Iii. Operate MySQL Data Tables

1. Create a table

Create table Name (column_name column_type not null ,...)

Attributes of the create table statement

Attribute	Description	Attribute	Description
Column_name	Field name	Primary key	Whether the column is the primary code
Column_type	Field Type	AUTO_INCREMNT	Whether the column is automatically numbered
Not null | null	Whether the column can be empty

 

 

 

 

After a data table is created, the corresponding table file ("table name. frm "," table name. MYD "," table name. MYI ")

2. View tables in the database

Show tables;

3. view all tables in the database

Show tables; (the premise is to use the use database ;)

4. view the data table structure

Describe table name;

5. Modify the data table structure

Alter table name

Add [column] create_definition [first | after column_name] // add a new field

Add primary key (index_col_name,...) // add the master code name

Alter [column] col_name {set default literal | rop default} // modify the field name

Change [column] old_col_name create_definition // modify the field name and type

Modify [column] create_definition // modify the field type

Drop [column] col_name // delete a field

Drop primary key // Delete the master code

Rename [as] new_tablename // change the table name

Eg: alter table Admin_Info

Drop A_Pwd,

Rename as Admin_Info2;

6. delete a specified data table

Drop table name;

4. Operate MySQL Data

1. Add Table Data

Syntax 1: insert into table name values (value 1, value 2,...) (null should be written for self-increasing columns)

Syntax 2: insert into Table Name (Field 1, Field 2,...) values (value 1, value 2 ,...)

Syntax 3: insert into table name set field 1 = value 1, Field 2 = value 2 ,...

2. Update table data

Update table name set field 1 = value 1 where query Condition

If no query condition exists, all data rows in the table are modified.

3. Delete table data

Delete from table name where query Condition

If no query condition exists, all data rows in the table are deleted.

4. query table data

Select * from table name;

5. Limit the number of query records

Select * from table name limit [start] length

Start: indicates the number of rows from which records are output, and 0 indicates 1st rows.