1.DMSQL classification

• DML (data management language)

- insert
- update
- delete
- select

• DDL (data definition language)

- create
- drop 
- alert
- truncate

• DCL (data control language)

- grant
- revoke

• TCL (transaction control)

- commit 
- rollback
- savepoint

2. Writing specification of SQL statement

• Keywords cannot be abbreviated or split
• Improve statement readability through line feed and indentation
• SQL is not case sensitive except in quotation marks

3. Simple query

• Query syntax

select ()from ();
The first bracket writes the query character, and the second bracket fills in the query object, ending with a semicolon

• Single column query

Query name of employee table
select employee_name from dmhr.employee;

• Multi column query

Query the salary of the corresponding employee
select employee_name,salary from dmhr.employee;
Query all information in the table
select * from dmhr.employee;

• The alias is listed for easy query. The alias is replaced by as, or the following cases can not be referred to

select employee_name,salary gz from dmhr.employee;
select employee_name as mingzi,salary gz from dmhr.employee;
select employee_name mz,salary gz from dmhr.employee;

• Join column||

"***What's your salary***"
select employee_name||'What's your salary:'||salary from dmhr.employee;

• To remove the duplicate (distinct), it will automatically sort - ascending - from small to large

Check the department number and remove the duplicate number
select distinct department_id from dmhr.employee;

• Expression, add 1000 yuan to each person's salary and display the original salary

select employee_name,salary,salary+1000 as jgz from dmhr.employee;

4. Filter query

• Syntax:

select()from()where()
The first bracket writes the query character, the second bracket fills in the query object, and the third bracket connects the logical operator and,or Or something, ending with a semicolon

4.1 comparison operator

= Greater than,>Greater than, >=Greater than or equal to, <=Less than or equal to, <Less than, <> and!=Not equal to

• Comparison operator case 1

What is Chen Xian's salary
select employee_name,salary from dmhr.employee where employee_name='Chen Xian';

• Comparison operator case 2

View the name and number of people with a salary greater than 30000
select employee_name,salary,department_id from dmhr.employee where salary >=30000;

4.2 logical operators: and, or, not

• Logical operator case 1

and Returns if both the first and second conditions are true
 In department 103, the salary is greater than 1 W Who are you
select employee_name,salary,department_id from dmhr.employee where salary >= 10000 and department_id=103;

• Logical operator case 2

or If one of the two conditions is true, it will be returned
 Either belong to department 101, or the salary is higher than 15000
select employee_name,salary,department_id from dmhr.employee where salary >= 15000 or department_id=101;

4.3 other operators

• in case

Show who's in departments 102 and 104
select employee_name,department_id from dmhr.employee where department_id in (102,104);

• is null case

select employee_name,department_id from dmhr.employee where job_id is null;

• is not null case

select employee_name,department_id from dmhr.employee where job_id is not null;

• between... and

select employee_name,salary,department_id from dmhr.employee where salary between 20000 and 30000
 Equivalent to
select employee_name,salary,department_id from dmhr.employee where salary >=  20000 and salary <= 30000;

• like
  • Match one character:_

select employee_name from dmhr.employee where employee_name like 'horse_';

• Match 0 or more characters:% 1
• Case 1:

%What did you start with after the horse
select employee_name from dmhr.employee where employee_name like 'horse%';

• Case 2:

%What ended with a horse before
select employee_name from dmhr.employee where employee_name like '%horse';

• Case 3:

%Before and after the horse
select employee_name from dmhr.employee where employee_name like '%horse%';

4.4 sorting

• Ascending asc: sort from small to large

select employee_name,salary from dmhr.employee order by salary asc;

• Descending desc: sort from large to small

select employee_name,salary from dmhr.employee order by salary desc;

4.5 grouping function

Syntax: select()from()where() group by ()having(); having must follow group by. Having cannot be used alone

• count()
• max()
• min ()
• avg()
• sum()
• Ask for departments with a total salary of more than 120000

select department_id,sum(salary) from dmhr.employee
group by department_id
having sum(salary) >120000;

4.6 multi table connection query

4.6. 1 internal connection

The result set displays only records that meet the criteria

• natural join

select department_id,employee_name,salary from dmhr.department natural join dmhr.employee;
Query some fields in two tables

• Cross join (Dika set) multiply the two fields before and after the cross join

select count(*) from dmhr.department;  The result is 46
select count(*) from dmhr.employee;   Result 856
select count(*) from dmhr.department cross join dmhr.employee; The result is 39376
 Equivalent to
select count(*) from dmhr.department,dmhr.employee;

• using connection

When multiple connection columns are satisfied, you can specify a column as a connection, and a table cannot be added in front of the connection column
Name or prefix

select employee_name,department_name from dmhr.employee join dmhr.department using(department_id);

• on

select a.employee_name,b.department_name from dmhr.employee a join dmhr.department b on a.department_id=b.department_id;
Equivalent to
select a.employee_name,b.department_name from dmhr.employee a,dmhr.department b where a.department_id=b.department_id;

• hash join

optimizer CBO Generated from join columns of small tables hash Value, according to hash Value connection table
 Scan large table

4.6. 2 external connection

In the result set, in addition to the records that meet the conditions, the records that do not meet the conditions are also displayed, and null is used instead

• Left outer connection: left join

All the records on the left of the left join are displayed, and only the records that meet the conditions are displayed on the right. Those that do not meet the conditions are replaced with null

select c.city_name,l.street_address from dmhr.city c left join dmhr.location l on c.city_id=l.city_Id;

• right join

All the records on the right side of the right join are displayed, and only the records that meet the conditions are displayed on the left. Those that do not meet the conditions are replaced with null

select c.city_name,l.street_address from dmhr.city c right join dmhr.location l on c.city_id=l.city_Id;

• full join

Left outer connection + right outer connection

select c.city_name,l.street_address from dmhr.city c full join dmhr.location l on c.city_id=l.city_id;

4.7 sub query

When a query is a condition of another query, it is called a sub query. The sub query runs first, and the result of the sub query is the condition of the external query. Subqueries can be used as temporary tables and embedded subqueries.

• Single line sub query, only one record is returned at a time
  = > >= < <= <>

Query all employees of Chen Xian in one department
select employee_name,department_id from dmhr.employee where department_id=(select department_id from dmhr.employee where employee_name='Chen Xian');

• Multi line sub query, which returns multiple records at a time
  in and all

Inquire about people with higher wages than department 103
select department_id,employee_name,salary from dmhr.employee where salary > all(select salary from dmhr.employee where department_id=103);