Install mysql on linux and use it easily.

Install mysql on linux and use it easily.

1. Install mysql

Sudo apt-get update

Sudo apt-get install mysql-server

Sudo apt-get install python-mysqldb (if you want to use it in python, install it)

2. log on to the root user:

Mysql-u root-p

3. view all databases:

Show databases;

4. Select a database operation:

Use database_name;

5. view all tables in the current database:

Show tables;

6. Create a database:

Create database database_name;

Create a database with UTF-8 encoding.

Create database test1 default character set utf8 collate utf8_general_ci;

7. delete a database:

Drop database database_name;

8. delete a database:

Drop database database_name;

9. delete a table:

Drop table table_name;

10. SQLInsert statement:

Insert into table_name (col1, col2) values (value1, value2 );

11. SQLUpdate statement:

Update table_name set col1 = 'value1 ', col2 = 'value2' where where_definition;

12. SQLQuery statement:

Select * from table_name where...

13. SQLDelete statement:

Delete from table_name where...

14. Add the table structure field:

Alert table table_name add column field1 date, add column field2 time...

15. Delete table structure fields:

Alert table table_name drop field1;

16. view the table structure:

Show columns from table_name;

17. limitUsage:

Select * from table_name limit 3; // only three rows are displayed on each page.
Select * from table_name limit 3, 4 // four results are displayed starting from the third query result.
This can be used for paging.

18. Sort the query results:

Select * from table_name order by field1, orderby field2; multiple sorting

19. Exit

MySQL: exit;

20. Remove the carriage returns and line breaks in the field.

Update test set contont = replace (contont, char (10), ''), char (13 ),'');

Contont is the field name; test is the table name

Separate write:

Update test set contont = replace (contont, char (10 ),'')

Update test set contont = replace (contont, char (13 ),'')

21. display the encoding method of the system

Show variables like 'Char % ';

22. display the encoding method of the database

Show create database database_name;

23. Modify the encoding method of the database

Alter database database_name character set utf8;

Utf8 is the encoding method.

24. display the encoding method of the database table

Show create table table_name;

25. Modify the encoding method of the database table

Alter table table_name convert to character set utf8 collate utf8_general_ci;

COLLATE utf8_general_ci: Database validation rules. ci is short for case insensitive, which means case insensitive. It is relative to cs, that is, case sensitive, which is case sensitive. Another is utf8_bin, is to store each character in the string in binary data, case sensitive.

Utf8_bin: case sensitive;

Utf8_general_cs: case sensitive;

Utf8_general_ci: case insensitive.