1: What is DQL

Data Query Language, DQL for short, is a database query language, and the keyword is SELECT.

2: Grammar

 

3: Basic usage

4: as

5: DISTINCT

6: where

where conditional statements are usually followed by conditional statements

 

7: Sort

 

8: Function

1: Statistical quantity

 

2: Statistics and

Sum counts the sum of a column of data

Syntax: select sum (column name) from table name;

1: Count the total score of a class in mathematics?

2: Count the total scores of Chinese, English and mathematics in a class

3: Average

avg counts the average of a column

Select AVG (column name) from table name;

1: Find a class math average?

2: Find the average of the total score of a class?

4: Find the most value

max counts the maximum value of a column and min counts the minimum value of a column

Get the highest and lowest scores of the class (the numerical range is particularly useful in Statistics)

3: Multi table query (join query)

When using database query statements, single table queries sometimes can not meet the business needs of the project. In the process of project development, many requirements involve multi table connection queries

When the final data to be queried comes from different tables, you need to consider using join query at this time;

I emp employee form

drop table if exists emp;
create table if not exists emp(
empno int primary key auto_increment,
ename varchar(10) not null,
job varchar(20),
salary double(8,2),
mgr int(10),
bonus double(6,2),
hiredate date,
deptno int(10)
);

I insert data

 

insert into emp values(null,'mary','sales',7000,6,1000,'2014-1-1',10);
insert into emp values(null,'lily','sales',6000,1,800,'2014-5-1',10);
insert into emp values(null,'tom','sales',5000,1,4000,'2014-3-1',10);
insert into emp values(null,'james','account',8000,6,null,'2014-2-1',20);
insert into emp values(null,'scott','teaching',8000,6,3000,'2014-1-20',30);
insert into emp values(null,'tom','BOSS',38000,null,null,'2013-1-20',30);
insert into emp values(null,'kitty','teaching',7000,5,700,'2014-5-20',30);
insert into emp values(null,'kitty','teaching',6000,5,500,'2014-6-20',30);
insert into emp values(null,'green','analyst',15000,6,1000,'2014-2-20',40);
insert into emp values(null,'brown','analyst',12000,9,7000,'2014-4-20',40);
insert into emp values(null,'danis','department',3000,6,800,'2014-3-4',50);
insert into emp values(null,'brown','department',1800,11,600,'2014-4-20',50);
insert into emp values(null,'smith','department',1200,11,500,'2014-5-20',50);

I Department table dept

drop table if exists dept;
create table if not exists dept(
deptno int primary key,
dname varchar(30),
dlocation varchar(255)
);
insert into dept values(10,'teaching','Beijing');
insert into dept values(20,'analyst','Wuhan');
insert into dept values(30,'department','Shanghai');
insert into dept values(40,'sales','Zhengzhou');
insert into dept values(50,'account','Guangzhou');

1: Introduction to Cartesian product

 

Match all records in the database for the number of records in the two tables.

Match each record in table A with each record in table B to obtain Cartesian product

select * from emp;

select * from dept;

select * from emp,dept; The result is the Cartesian product

The result of Cartesian product is the product of two table records, for example, 3 in table A and 4 in table B

Note: the Cartesian product result is invalid. You must select a valid data result from the Cartesian product!!! Find their relationship

Because all the records are displayed, what we need is often valid data....

 

 

2: Internal connection query

Keywords: inner join --- on

The data of internal link query is the same as that of equivalent Association query, which is common to table 2;

Statement: select * from a_table a inner join b_table b on a.a_id = b.a_id;

3: External connection query

Left outer connection - with the left table as the benchmark table, all data in the left table will be displayed, regardless of whether there are matches in the right table

Keywords: left [outer] join on

A: SELECT * from statement_ table a left join b_ table b ON a.a_ id = b.b_ id;

 

Right outer connection

Keywords: right join on / right outer join on

A: SELECT * from statement_ table a right outer join b_table b on a.a_id = b.b_id;

4: Join query comparison