MySQL Database knowledge points

1. What is a database

is a file system that operates data----in a file system using the standard SQL language for storing data in a management software system

2. What is a relational database

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra. The relationships between the various entities in the real world and the entities are represented by a relational model.

What is the difference between a 3.where and a having conditional statement?

Where is conditional filtering before grouping, having a conditional filter after grouping

You can use the where place to replace with having, but you can use the grouping function, where you cannot use the grouping function

4.SQL Statement classification by function (definition, manipulation, control, query) classification

DDL data Definition language, defining tables, libraries, views

DML Add, modify, and delete operations on data table records

DCL authorization, transaction control, conditional judgment

DQL (not a category given by the organization) Data Sheet record query

5. Inserting records into a data table

Syntax: INSERT into table name (column name, column name, column name ...) values (value, value, value ...); Assigning values to each column of a data table

Create a new employee table, insert three employee information

@1 INSERT INTO employee (Id,name,gender,birthday,entry_date,job,salary,resume)

VALUES (1, ' Zhangsan ', ' Male ', ' 1990-10-10 ', ' 2010-01-01 ', ' Sales ', 4000, ' Good boy! ');

@2 INSERT INTO employee values (2, ' Lisi ', ' Male ', ' 1988-10-01 ', ' 2008-08-17 ', ' hr ', 3500, ' good hr! ');

@3 INSERT INTO employee (id,name,job,salary) VALUES (3, ' Wangwu ', ' Boss ', 20000);

6. Data record change operation

Syntax: Update table name set column name = value, column name = value .... where condition statement;

Update employee Set salary = salary+1000 where name = ' Wangwu ';

7. Delete operations for data records

Syntax: Delete from table name where conditional statement;

* If there is no where statement, all records in the table will be deleted

Delete from employee where name= ' Zhangsan ';

8. What is the difference between deleting all records in a table using delete and deleting all records in a table using TRUNCATE TABLE?

Truncate DELETE data, the process first deletes the entire table and then re-creates

Delete Deletes data, deletes records line by row

* Truncate efficiency is better than delete

Truncate belongs to DDL, delete belongs to DML ======== transaction management is valid only for DML, and transaction management SQL statements can be rolled back to SQL pre-execution state

9. Data sheet records of the query

syntax One: SELECT [DISTINCT] * | column name, column name ... from table name; SELECT distinct 中文版 from exam;syntax Two: SELECT expression (column name execution operation) from table name;

Select Name,chinese+math+english as total score from exam;

syntax Three: Select column name from table name where conditional statementSELECT * from exam where chinese+math+english > 200;syntax Four: SELECT * from table name order by column name Asc|desc; ----ASC Ascending desc Descending

SELECT * FROM Exam ORDER by 中文版 Desc,math desc;

10. Aggregation function refers to the built-in function in SQL statement----------Grouping function (for statistics)

1) Count statistics query result record Bar select COUNT (*) |count (column name) from table name;

2) Sum statistics A column of data and select SUM (column name) from the table name;

3) AVG Statistics a column average select AVG (column name) from table name;

4) Max Statistics one column Max min statistics a column minimum

11. Handling NULL cases using the Ifnull function

Select SUM (ifnull (chinese,0) +ifnull (math,0) +ifnull (english,0)) from exam; Contains Liu Bei's English and maths scores

12. Keyword like

Like ' Fuzzy query pattern ' for fuzzy query, expression has two placeholder% arbitrary string _ any single character for example: Name "Zhang%" all surname Zhang Trainees

Name like ' Zhang _ ' all surname Zhang name is two characters learners

MySQL Database knowledge points