Install MySQL on Linux and easy to use

1. Install MySQL

sudo apt-get update

sudo apt-get install Mysql-server

sudo apt-get installs python-mysqldb (install if Python is to be used)

2. Log in 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 the tables under the current database:

Show tables;

6. Create a database:

CREATE DATABASE database_name;

Create a database in which the encoding is UTF8.

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.SQL INSERT statement:

INSERT INTO table_name (COL1,COL2) values (value1,value2);

11.SQL UPDATE statement:

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

12.SQL Query statement:

SELECT * FROM table_name where ...

13.SQL DELETE statement:

Delete from table_name where ...

14. Add a field to the table structure:

Alert table table_name Add column field1 date, add column Field2 time ...

15. Delete The fields of the table structure:

Alert table table_name drop field1;

16. View the structure of the table:

Show columns from table_name;

17.limit Use of:

SELECT * FROM table_name limit 3;//shows only 3 rows per page
SELECT * FROM table_name limit 3,4//from the third start of the query results, four results are displayed.
This is a good place to use for paging.

18. Sort the results of the query:

SELECT * FROM table_name ORDER by Field1,orderby Field2; multiple sorting

19. Exit

Mysql:exit;

20. Remove carriage returns and line breaks in a field

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

Contont is the field name; test is a table name

Can be written separately:

Update test set Contont=replace (Contont,char (10), ")

Update test set Contont=replace (Contont,char (13), ")

21. How to encode the display system

Show variables like ' char% ';

22. Show how the database is encoded

Show CREATE DATABASE database_name;

23. Modify the encoding of the database

ALTER DATABASE database_name character set UTF8;

Where UTF8 is the encoding method

24. Displaying the encoding of database tables

Show CREATE TABLE table_name;

25. Modifying the encoding of database tables

ALTER TABLE table_name convert to Character set UTF8 collate utf8_general_ci;

COLLATE UTF8_GENERAL_CI: Database validation rules, CI is the abbreviation of case insensitive, meaning is not sensitive to capitalization, relative to CS, case sensitive, casing-sensitive, and one is Utf8_bin, is to store each character in a string with binary data, which is case-sensitive.

Utf8_bin: case-sensitive;

Utf8_general_cs: Case sensitive;

UTF8_GENERAL_CI: not case sensitive.

Install MySQL on Linux and easy to use