SQL languages fall into five categories
DQL language: the full name (data query language) is the data query language, which mainly involves the query of select statements.
- Basic query
- Condition query
- Sort query
- Common functions
- Grouping function
- join query
- Paging query
- Union Union query
DML language: data manipulation language, which is mainly used for data addition, deletion, modification and no query.
- Insert statement
- Delete statement
- Modify statement
DDL language: the full name (data definition language) refers to the data definition language, which is mainly about the definition of libraries and tables, such as deleting library tables, defining tables, etc.
- Management of libraries and tables
- Introduction to common data types
- Some common constraints
Tcl language: transaction control language, which mainly involves transaction control
- Transactions and transactions
DCL language: data control language, which mainly involves permission control
1. query a single field in the table.
Select field name from table name; SELECT stu_class FROM qinfb_class;
2. query multiple fields in the table.
SELECT stu_class,stu_year FROM qinfb_class;
3. query all fields in the table.
SELECT * FROM qinfb_class;
4. for accent mark query, when the table name defined in the database or the field name defined in the table conflicts with the keyword name defined in the MySQL database, you need to enable accent marks to distinguish. Generally, whether there is a accent mark or not has no impact.
SELECT `stu_class` FROM qinfb_class;
5. query whether this constant value exists in the table.
SELECT 2019; // Query integer Select'Web project application '// Query string
6. query calculation expressions (addition, subtraction, multiplication, division, remainder, etc.).
SELECT 100*2; SELECT 50%2;
7. query function.
8. alias the query field for easy identification and understanding, and use the as keyword.
(the advantage of using alias is that when the query field name has the same name as the field in other tables, it can be distinguished by using alias.)
SELECT stu_ Class as class, stu_ Year as academic year from qinfb_ class;
9. alias constant values that do not exist in the database.
The calculation result of select 1+13*2 as is;
10. the second way to alias is to use spaces and omit the as keyword directly.
SELECT stu_ Class class, stu_ Year academic year from qinfb_ class;
11. it is worth noting that this alias method of putting together multiple spaces or characters needs to be enclosed in double quotation marks.
SELECT stu_ Class "(class) the class is:" from qinfb_ class;
12. de duplication, remove the repeated data, and use the distinct keyword.
SELECT DISTINCT major_name FROM qinfb_class;
13.+ sign splicing function: numeric string + numeric, which will automatically convert numeric string to numeric value for operation.
14. string + value, the string will be converted to 0, and then calculated with the value.
15. string + string, the string will be changed to 0, so it is 0+0=0
16. field + field splicing will lead to confusing results and no error will be reported.
SELECT stu_class+stu_year FROM qinfb_class;
16. the fields in the correct splicing table need to use the concat() function. The function of this function is to facilitate splicing.
Select concat (stu\u ID, stu\u PWD) as "account password" from qinfb_ student;
17. use the concat() function to splice strings.
The result of select concat ('a','b','c') as splicing is:;
18. Arbitrary line wrapping of SQL statements is not affected.
SELECT stu_class,stu_pwd FROM qinfb_student; SELECT stu_class, stu_pwd FROM qinfb_student;
Select field From surface Where filter criteria
Filter criteria classification:
(1) Filter by conditional expression:
Condition operators used:
>Greater than < less than != Not equal to < > not equal to >=Greater than or not equal to < = less than or equal to
(2) Filter by logical expression:
Logical operators are generally used to splice conditional operators
Logical operators used:
&&And ||Or ! wrong Standard usage: And and (both conditions are required to be true, and the result is true) Or or Not not
(3) Fuzzy filtering:
Does like use First character Between and In determines whether the value of a field belongs to an item in the in list Is null does not exist
1. query the information of all employees whose salary is greater than 3000
SELECT * FROM employees WHERE salary > 3000;
2. query all student IDs that are not equal to this ID number.
SELECT stu_id FROM qinfb_student WHERE stu_id <> 1903010331; ---------------------------------------------Yes SELECT stu_id FROM qinfb_student WHERE stu_id != 1903010331;
3. query employee name, salary and bonus with salary between 6000 and 8000
SELECT employee_name, salary, bonus FROM employees WHERE salary >= 6000 AND salary <= 8000;
4. query the information of employees whose department changes are not between 90 and 110, or whose salary is higher than 12000
SELECT * FROM employees WHERE dep_id < 90 OR dep_id > 110 OR salary > 12000; ----------------------------It can also be expressed in this way SELECT * FROM employees WHERE NOT(dep_id >= 90 AND dep_id <= 110) OR salary > 12000;
5. query all employee information with the character a in the employee list.
Like is a kind of fuzzy query. It is generally used with wildcards Two common wildcards: %Percent sign: any number of qualified characters _ Underscore: any single character that meets the criteria SELECT * FROM employees WHERE name LIKE '%a%';
6. query the employee name and salary with the first character of 3 and the fifth character of 9 in the employee ID
SELECT name, salary FROM employees WHERE name LIKE '__3_9%';
7. query employee information with employee number between 100 and 120.
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;