Learning SQL language

Time:2022-6-8

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
  • Subquery
  • 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


Basic query

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.

SELECT VERSION();
Learning SQL language

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;
Learning SQL language

9. alias constant values that do not exist in the database.

The calculation result of select 1+13*2 as is;
Learning SQL language

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;
Learning SQL language

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;
Learning SQL language

12. de duplication, remove the repeated data, and use the distinct keyword.

SELECT DISTINCT major_name FROM qinfb_class;
Learning SQL language

13.+ sign splicing function: numeric string + numeric, which will automatically convert numeric string to numeric value for operation.

SELECT '123'+13;
Learning SQL language

14. string + value, the string will be converted to 0, and then calculated with the value.

SELECT 'name'+123;
Learning SQL language

15. string + string, the string will be changed to 0, so it is 0+0=0

SELECT 'name'+'age';

16. field + field splicing will lead to confusing results and no error will be reported.

SELECT stu_class+stu_year FROM qinfb_class;
Learning SQL language

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;
Learning SQL language

17. use the concat() function to splice strings.

The result of select concat ('a','b','c') as splicing is:;
Learning SQL language

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;

Condition query

Syntax:

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;
Learning SQL language

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;

page 33