The following articles mainly describe the basic MySQL operations. MySQL is often used in actual operations. Therefore, understanding the basic content of MySQL is also a very important issue, the following is a detailed description of basic MySQL operations.
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;
Create a table through basic MySQL operations
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 % ';
Modify data
MySQL> update test set tname = 'zhang san' where tid = '2 ';
Basic MySQL operations to 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 for basic MySQL operations
MySQL> show variables like 'character _ set _ % ';
SQL File Import command
MySQL> source d:/MySQL. SQL;