Basic MySQL database operations

(EnterMySQL \ binDirectory)Note thatMySQLAdd points after each sentence in the console;

Elementary

1. Enter mysql-u username-P Password

2. Export Database mysqldump-u user name-P Password Database Name (Table Name)> path backup. SQL

3. Import database mysql-u username-P Password Database Name <path backup. SQL

Or enter MySQL and use the Source Path to back up the table. You can import the table separately.

4. Show databases of all databases;

5. Use the database use name;

6. Show tables for all tables;

7. Create Database database name;

8. Create a table create table table name (variable name variable type (size) constraints, variable name variable type (variable size) constraints, primary key (primary key ));

9. Describe table name;

10. insert into table name values () into the table ();

11. Select column from Table order by column DESC/ASC

Select count (*) from table name statistics table total number of records

Meaning of descending in descending order

Ascending in ascending order

12. Delete table content

Delete from Table Name

Truncate table name

13. Update table content

Update table name set = ''where Condition

14. Change the table structure

Alter table table name Add ID int (10) [column name data type] add Column

Alter table Table Name drop ID column name delete column

Alter table table name modify ID int (6); Modify columns

15. Change the table name

Rename table table name 1 to table name 2

16. view database and table creation Information

Show create database Database Name

Show create table table name

17. Filter duplicate information in the table

Select distinct column name from Table Name

 

Advanced

1. Create a table with the selected content

Create Table table name 2 (column name and type can be defined) as Select column name, column name from table name 2;

Insert selected results into a table

Insert table 1 Select column name 1, column name 2 ,... From table 2

2. SELECT statement

Select [All/distinct] column name from table name [Where Search Condition] [group by group expression] [having condition] [order by column name] [ASC | DESC]

Select the first few

Select * from table (table name) limit m, where n m is the number of rows and N is the number of rows

Select record within the specified range

Select * from table (table name) Where column name between value1 and value2

Select the record in the specified content

Select * from table (table name) Where in/Not In column (value1, value2 ...)

Select to specify similar content

Select * from table (table name) Where column name like/not like 'string'

 

Wildcard	Description
%	Any number of characters
_	Single Character
[]	Single Character in the specified range
[^]	A single character that is not within the specified range

Select an empty record

Select * from table (table name) Where column name null/not null

Select Result Statistics

Select column name 1, count (column name 2) from table (table name) group by column name 2 having Condition

Function Name	Function
Count	Evaluate the number of items in the Group and return an integer
Sum	Returns the sum of all values in the expression.
AVG	Returns the average of all values in the expression.
Max	Returns the maximum value of all values in the expression.
Min	Returns the minimum value of all values in the expression.
ABS	Returns the absolute value of a numeric expression.
ASCII	Returns the ASCII code of the processed data.
Rand	Returns a random number between 0 and 1.

 

 

 

| Student | create
Table student (

Studno int (5) not null auto_increment,

Studname char (10) Not null,

Studcourse char (50) not null,

Studscore float default '0 ',

Primary Key
('Studno ')

) Engine = InnoDB
Auto_increment = 9 default charset = GBK |

Mysql> select * from student;

+ -------- + ---------- + ------------ + ----------- + ----- +

| Studno | studname | studcourse | Studscore | sex |

+ -------- + ---------- + ------------ + ----------- + ----- +

| 1 | zuruhui | database principle | 90 | male |

| 2 | Maslow | database principle | 80 | male |

| 3 | Lily | database principle | 94 | female |

| 4 | Jia hongji | database principle | 99 | male |

| 5 | Ma sanli | database principle | 69 | male |

| 6 | Guo Donglin | database principle | 65 | male |

| 7 | want to add you | database principle | 55 | female |

| 8 | Hua zhengbai | database principle | 95 | male |

| 9 | Dawn | database principle | 49 | male |

| 10 | Xu xiaojuan | database principle | 79 | female |

+ -------- + ---------- + ------------ + ----------- + ----- +

10 rows in SET (0.00 Sec)

Mysql> select * from class;

+ -------- + ------------------ + ----------- +

| Studno | class |
Gradecode |

+ -------- + ------------------ + ----------- +

| 1 | computer network engineering |
3 |

| 2 | computer network engineering |
1 |

| 3 | computer network engineering | 1 |

| 4 | Computer Science and Technology | 3 |

| 5 | Computer Science and Technology | 3 |

| 6 | Computer Science and Technology | 1 |

| 7 | Computer Science and Technology | 4 |

| 8 | Computer Science and Technology | 2 |

| 9 | Computer Science and Technology | 2 |

| 10 | Computer Software Engineering |
2 |

+ -------- + ------------------ + ----------- +

10 rows in SET (0.00 Sec)

 

 

Select
Information about female students who pass the database principle (including student ID, name, course, and score ).

Mysql> select * from student having (select score from Studscore student where sex = 'femal')> 60;

Create a table View

Create view name as Select column name from Table Name

Create an index

Create unique index column on table name (column name)

An inner join is also called a natural join. It is a common method for combining two tables. The inner join compares the columns in the two tables and combines the rows in the two tables that meet the join conditions as a result. Internal join has two forms of syntax: (Note: Multiple tables are also supported)

Syntax 1:

Select column name from table 1 [inner] Join table 2 on table 1. Column = TABLE 2. Column

Syntax 2:

Select column name from table 1, table 2
Where table 1. Column =
Table 2. Columns

In the inner join, only matching rows in two tables can appear in the result set. In Outer Join, You can restrict only one table, but not the other table (that is, all rows of the table without restriction appear in the result set ).

The outer join can only connect two tables.

Outer Join is divided into left Outer Join and right outer join.

Syntax: Select column nameFrom table 1 <left | right
> [Outer] Join table 2 on table 1. Column = TABLE 2. Column

Join Operations can be performed not only on different tables, but also on the same table to link different rows in the same table. A self-join can be seen as a join between two copies of a table. In auto join, two aliases must be specified for the table to logically form two tables.

Is a special type of inner join

L
How to specify an alias for a table name:

A.Alias is directly given after the table name: The table name alias

B. Add the as keyword between the table name and alias: Table name as Alias

L
MySQLTable alias cannot contain special characters (such as spaces)

L
In addition to table aliases, table aliases can be used in other SQL statements to simplify SQL statements and improve statement readability.

L
A join is also called a non-restricted join. It combines two tables without any constraints. In mathematics, it is the Cartesian product of two tables. The number of rows after the cross join operation is the product of the number of rows in two joined tables.

LSyntax:SelectColumn nameFromTable 1
Cross join table 2

OrSelectColumn nameFromTable 1 and Table 2