MYSQL Getting started: basic operations _ MySQL

MYSQL: basic operations bitsCN.com

MYSQL: Basic operations

1. log on to the database

Command: mysql-u username-p (mysql-h host address-u username-p user password)

Description: press enter to enter the password. After the password is verified, you can access the database.

2. View data status

Command: show database;

Description: This command displays all existing databases.

3. create and delete databases

Command: create database db;

Description: the name of the database to be created.

Command: drop database db;

Description: This command completely deletes a database named db, regardless of whether there is data in the database, so be careful when using this command.

4. select a database

Command: use db;

Description: This command enables the current session to enter the database named in the database. you can create, modify, delete tables, and insert data.

Command: select database ();

Description: view the name of the currently used database.

5. view the current system status

Command: select now ();

Description: The current date and time.

Command: select user ();

Description: The User currently logged on to mysql.

Command: select version ();

Description: The current mysql version.

6. create a table

Command: create table tablename (filed1, filed2, filed3,..., filedN );

Description: creates a table named tablename. The field names are specified by filedN.

The basic syntax for declaring a field attribute (filedN) is:

Can the field name data type and size be blank? is it a primary key default value remarks

The field name, data type, and size are mandatory, and others can be left empty.

The rules are described as follows:

Field name: it is better to use English letters, numbers, and underscores. Case-insensitive. the length cannot exceed 64.

Data type and size: for example, int, int unsigned, and char (10.

Null or not: not null indicates that this field cannot be blank. if not specified, it can be blank.

Whether it is a primary key: if the statement contains a primary key, it is expressed as a primary key.

Default value: if default 'ABC' is entered, the default value of this field is abc. if the value of this field is not specified when new data is inserted, the default value is inserted.

Note: If the auto_increment statement is inserted in the remarks, this field will be automatically added (limited to integer type) when new data is inserted ). When new data is inserted, this field can be specified as NULL.

Example: create table abc (number int not null primary key auto_increment, name char (10) default 'unknow ');

7. delete a table

Command: drop table tablename;

Description: used with caution to delete a table named tablename, just like a database deletion command.

8. display the table status

Command: show tables;

Description: tables created in the database.

Command: desc (describe) tablename;

Description: Structure of a table named tablename.

9. table name and structure modification

Command: alter table tablename add fieldN + 1

Description: add a field to the table. fieldN + 1 is the same as the preceding description.

Command: alter table oldtablename rename newtablename;

Description: modify the table oldtablename to newtablename.

Command: alter table tablename modify filedN;

Description: modify the attributes of the field filedN in the tablename table. in filedN, specify the name of the field to be modified and the new attributes.

Command: alter table tablename change oldfiledN newfiledN;

Description: change the table tablename field filedN to filedN + 1; oldfiledN only needs to specify the name of the field to be changed, while newfiledN needs to specify the new field name and type.

For example, alter table newname change follow newfollow varchar (40 );

Command: alter table tablename drop filedN;

Description: used to delete the filedN field in the tablename table.

10. create a database

Mysql> Create Database name;

11. create and authorize a user

Mysql> grant permission on database name. * to username @ login host identified by "password ";

For example, grant all on test. * to test @ localhost identified by "test ";

12. change the password

Mysqladmin-u username-p old password new password

13. delete a user

Mysql> use mysql; delete from user where user = "zaho" host = "localhost ";

14. import SQL files to the database

Mysql-uroot-p databasename <d:/datasource/databasename. SQL

For example, mysql-utest-ptest test

BitsCN.com