Basic MySQL database operation demonstration

The following articles mainly describe the basic operations of the MySQL database. We may have some knowledge about the MySQL database, today, we will discuss with you the basic operations of the MySQL database, and hope you will gain some benefits.

Log on to the database

D: \ phpStudy \ MySQL \ bin> MySQL-uroot-proot

View Database

MySQL> show databases;

Select Database

MySQL> use bugfree;

Set Character Set

MySQL> set names 'gbk ';

Query tables in a database

MySQL> show tables;

Basic MySQL database operations; create tables

MySQL> create table test (

-> Tid int (10) not null,

-> Tname varchar (100) not null,

-> Tdate datetime not null default '2017-00-00 ',

-> Primary key (tid ));

View table structure

MySQL> desc test;

Add Column

MySQL> alter table test add (tage int (3 ));

Modify the original table structure

MySQL> alter table test modify tage int (5) not null;

Modify the column Default Value

MySQL> alter table test alter tage set default '0 ';

Remove column default values

MySQL> alter table test alter tage drop default;

Delete column

MySQL> alter table test drop column tage;

Insert data

MySQL> insert into test (tid, tname, tdate) value (1, 'angjuqi ', '2017-03-21 ');

Query data

MySQL> select * from test;

Fuzzy search

MySQL> select * from test where tname like '% Yang % ';

Basic MySQL database operations: modify data

MySQL> update test set tname = 'zhang san' where tid = '2 ';

Delete data

MySQL> delete from test where tid = '2 ';

Delete table

MySQL> drop table test;

Rename a table

MySQL> alter table test rename testbak;

Paging query (the number of rows to be taken from the start line of limit)

MySQL> select * from testbak limit 2, 1;

Refresh Database

MySQL> flush privileges;

Show database version

MySQL> select version ();

Show current time

MySQL> select current_date;

Change User Password

D: \ phpStudy \ MySQL \ bin> MySQLadmin-uroot-proot password yangjuqi

Write the queried data to a file.

MySQL> select * from testbak into outfile "d:/test.txt" fields terminated ",";

View database status

MySQL> status;

View All codes

MySQL> show variables like 'character _ set _ % ';

SQL File Import command

The above content is an introduction to the basic operations of the MySQL database.