"MySQL" Add, modify, delete, query grammar handouts

I. DML-Data Manipulation language

INSERT-New to implement data table data

UPDATE-Make changes to data table data

Delete-implements deletion of data table data

Second, INSERT

Syntax: INSERT into table name [(Field 1, Field 2,...)] VALUES (' Value 1 ', ' Value 2 ',...);

1.insert executes 1 times, you can add 1 records to the table

2. For auto-generated fields (auto_increment), you do not need to display the insert.

Third, UPDATE

Syntax: UPDATE table name SET column_name = value [, column_name2 = value2, ....]
[WHERE condition];

Example: Update student set sex= ' female '; --The sex column values of all data rows have been modified for female

UPDATE student SET sex= ' female ';

UPDATE student SET sex= ' man ' WHERE studentno=3;

UPDATE student SET sex= ' male ', gradeid=2, phone= ' 13985746510 ' WHERE studentno=1;

UPDATE student SET sex= ' unknown ' WHERE studentno between 2 and 3

Iv. DELETE

Syntax: DELETE from table name [WHERE condition];

Example: delete from student; --Delete all the data in the table

Delete from student where studentno=5; --Delete the record number 5

Delete from student where Studentno between 2 and 5; --Delete a record with a number between 2-5

Note: Deleted data can be retrieved through the log file.

Wu, TRUNCATE

Syntax: TRUNCATE [TABLE] table_name

Note: The deleted data cannot be recovered.

Vi. SELECT

Implement data query (data retrieval)

Syntax: SELECT [All | DISTINCT]
{* | table.* | [Table.field1 [As ALIAS1] [, table.field2 [as alias2]][, ...] }
From table_name [as Table_ alias]
[Left|out|inner join Table_name2] #联合查询
[WHERE ...] #指定结果需满足的条件
[GROUP by ...] #指定结果按照哪几个字段来分组
[Having ...] #过滤分组的记录必须满足的次要条件
[ORDER by ...] #指定查询记录按一个或者多个条件排序
[LIMIT {[offset,] row_count | row_count offset offset}]; #指定查询的记录从哪条至哪条

Example: SELECT * from grade; --Extracts all columns of data from the grade table (all rows)

Select distinct gradename from grade; --distinct used to reject repeated rows of data in the result

"MySQL" Add, modify, delete, query grammar handouts