MySQL basic command set

1: Use the show statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named mysqldata

Mysql> Create Database mysqldata;

3: select the database you created

Mysql> Use mysqldata; (when you press the Enter key to see database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> Create Table mytable (name varchar (20), sex char (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into mytable values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into Table mytable;

9: import the. SQL file command (for example, D:/MySQL. SQL)
Mysql> use database;

Mysql> source D:/MySQL. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> Delete from mytable;

12: Update table data

Mysql> Update mytable set sex = "F" where name = 'hyq ';

13. query time: select now ();
Query the current user: Select User ();
Query the database version: Select version ();
Query the currently used database: select database ();

14. Delete the students data table in the student_course database:
Rm-F student_course/students .*
　　
15. Back up the database: (back up the database test)
Mysqldump-u root-P test> C: \ test.txt
Backup table: (back up the mytable table under the test database)
Mysqldump-u root-P test mytable> C: \ test.txt
Import the backup data to the database: (import back to the test database)
Mysql-u root-P test <c: \ test.txt
　　
16. Create a temporary table: (create a temporary table zengchao)
Create temporary table zengchao (name varchar (10 ));
　　
17. To create a table, first determine whether the table exists.
Create Table if not exists students (......);
　　
18. Copy the table structure from an existing table
Create Table Table2 select * From Table1 where 1 <> 1;
　　

19. Copy a table
Create Table Table2 select * From Table1;
　　
20. Rename the table
Alter table Table1 Rename as Table2;
　　
21. Modify the column type
Alter table Table1 modify ID int unsigned; // modify the column ID type to int unsigned
Alter table Table1 change ID Sid int unsigned; // modify the column ID name to Sid and the attribute to int unsigned
　　
22. Create an index
Alter table Table1 add index ind_id (ID );
Create index ind_id on Table1 (ID );
Create unique index ind_id on Table1 (ID); // create a unique index
　　
23. delete an index
Drop index idx_id on Table1;
Alter table Table1 drop index ind_id;

24. Combine characters or multiple columns (link the column ID with ":" And column name and "=)
Select Concat (ID, ':', name, '=') from students;
　　

25. Limit (10 to 20 records selected) <the number of the first record set is 0>
Select * from students order by ID limit 9, 10;
　　
26. functions not supported by MySQL
Transaction, view, foreign key and reference integrity, stored procedure and trigger