The original text was uploaded to Baidu Library. Now it is found that word document is not easy to read, so it was changed to mark down form.

1. Establishment of databases and tables:

1.1 Create a database:

create database lsydb1 default character set utf8;
use lsydb1;

1.2 Create tables (dept):

create table dept (deptno int primary key, dname nvarchar(30), loc nvarchar(30)) default character set utf8;

1.3 Create tables (emp):

create table emp ( empno int primary key, ename nvarchar(30), job nvarchar(30), mgr int , hiredate datetime, sal decimal(6.2), comm decimal(6.2), deptno int ,foreign key(deptno) references dept(deptno)) default character set utf8;

1.4 Insert data into the dept table (first dept and then emp):

insert into dept (deptno, dname, loc) 
values (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

The dept table is as follows:

+--------+------------+----------+ 
| deptno | dname      | loc      | 
+--------+------------+----------+ 
|     10 | ACCOUNTING | NEW YORK | 
|     20 | RESEARCH   | DALLAS   | 
|     30 | SALES      | CHICAGO  | 
|     40 | OPERATIONS | BOSTON   | 
+--------+------------+----------+ 

1.5 Insert data into emp table

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, null, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

emp table:

+-------+--------+-----------+------+---------------------+------+------+--------+ 
| empno | ename  | job       | mgr  | hiredate            | sal  | comm | deptno | 
+-------+--------+-----------+------+---------------------+------+------+--------+ 
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 | 
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 | 
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 | 
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 | NULL |     20 | 
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 | 
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 | NULL |     30 | 
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 | NULL |     10 | 
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 | 
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL |     10 | 
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 | 
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 | NULL |     20 | 
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 | NULL |     30 | 
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 | NULL |     20 | 
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 | NULL |     10 | 
+-------+--------+-----------+------+---------------------+------+------+--------+

2 Basic query operations:

2.1 How to query employees with salaries ranging from 2000 to 2500

select * from emp where sal> 2000 and sal < 2500;
select * from emp where  sal between 2000 and 2500;

Beween takes two sides including 2000 and 2500.

2.2 Fuzzy Query:

Show the employee's name and salary with the initials S

select ename ,sal from emp where ename like "s%";

2.3 shows empno's employees at 123,345,800..

It is more efficient to use in keyword.

Select * from emp where emono in (123,345,800);

3 Complex query operations:

3.1 shows minimum and maximum wages for employees.

 select ename ,sal from emp where sal>= ( select max(sal) from emp) or sal<= (select min(sal) from emp);

3.2 Display average and total wages of employees

select sum(sal),avg(sal) from emp; 

3.3 shows the names and salaries of employees above average, and shows the average wages.

select ename ,sal ,(select avg(sal) from emp) from emp where sal > (select avg(sal) from emp); 

3.4 Indicates the number of employees:

select count(ename) from emp;

group by: Grouping statistics of results used for queries.
Have by: Used to restrict group display results.

3.5 shows the average and maximum wages for each department

 mysql> select deptno,avg(sal) as "Average wage per department" ,max(sal) as "Maximum wage per department" from emp group by deptno;

The results are as follows:

+--------+-----------------------------+-----------------------------+ 
deptno | Average wage per department | Maximum wage per department | 
+--------+-----------------------------+-----------------------------+ 
|     10 |                   2916.6667 |                        5000 | 
|     20 |                   2175.0000 |                        3000 | 
|     30 |                   1566.6667 |                        2850 | 
+--------+-----------------------------+-----------------------------+ 

3.6 shows the average wage and minimum wage for unemployed positions in each department:

select avg(sal),min(sal) ,deptno,job from emp group by deptno,job;

Results:

+-----------+----------+--------+-----------+ 
| avg(sal)  | min(sal) | deptno | job       | 
+-----------+----------+--------+-----------+ 
| 1300.0000 |     1300 |     10 | CLERK     | 
| 2450.0000 |     2450 |     10 | MANAGER   | 
| 5000.0000 |     5000 |     10 | PRESIDENT | 
| 3000.0000 |     3000 |     20 | ANALYST   | 
|  950.0000 |      800 |     20 | CLERK     | 
| 2975.0000 |     2975 |     20 | MANAGER   | 
|  950.0000 |      950 |     30 | CLERK     | 
| 2850.0000 |     2850 |     30 | MANAGER   | 
| 1400.0000 |     1250 |     30 | SALESMAN  | 

3.7 shows the Department whose average wage is less than 2000 and his average wage

(having is often used in conjunction with group by to filter grouped query results)

 select avg(sal)  ,deptno from emp group  by deptno having avg(sal) < 2000 ; 

4 Complex Queries (Multiple Tables)

4.1 Displays the name of the employee and the location of the department:

select emp.ename,dept.loc from emp,dept where emp.deptno=dept.deptno and dept.dname="sales";

Result:

+--------+---------+ 
| ename  | loc     | 
+--------+---------+ 
| ALLEN  | CHICAGO | 
| WARD   | CHICAGO | 
| MARTIN | CHICAGO | 
| BLAKE  | CHICAGO | 
| TURNER | CHICAGO | 
| JAMES  | CHICAGO | 
+--------+---------+

4.2 Display the Department name, employee name and salary of the department number 10.

Select dept.dname,emp.ename,emp.sal from dept,emp where emp.deptno=dept.deptno and dept.deptno=10;

Result:

+------------+--------+------+ 
| dname      | ename  | sal  | 
+------------+--------+------+ 
| ACCOUNTING | CLARK  | 2450 | 
| ACCOUNTING | KING   | 5000 | 
| ACCOUNTING | MILLER | 1300 | 
+------------+--------+------+ 

4.3 Display the name of the employee, the salary of the employee and the name of the Department in which they work, and rank them by department.

 select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dept.dname;

Result:

+--------+------+------------+ 
| ename  | sal  | dname      | 
+--------+------+------------+ 
| CLARK  | 2450 | ACCOUNTING | 
| KING   | 5000 | ACCOUNTING | 
| MILLER | 1300 | ACCOUNTING | 
| SMITH  |  800 | RESEARCH   | 
| JONES  | 2975 | RESEARCH   | 
| SCOTT  | 3000 | RESEARCH   | 
| ADAMS  | 1100 | RESEARCH   | 
| FORD   | 3000 | RESEARCH   | 
| ALLEN  | 1600 | SALES      | 
| WARD   | 1250 | SALES      | 
| MARTIN | 1250 | SALES      | 
| BLAKE  | 2850 | SALES      | 
| TURNER | 1500 | SALES      | 
| JAMES  |  950 | SALES      | 
+--------+------+------------+ 

Self-join: Join queries for the same table.

4.4 Displays the name of the superior leader of an employee, such as the superior of FORD.

 select ename from emp where empno=(select mgr from emp where ename="ford");
 ```

//Figure:

```shell
+-------+ 
| ename | 
+-------+ 
| JONES | 
+-------+ 




<div class="se-preview-section-delimiter"></div>

4.5 Displays the name of each employee and the name of his superior.

4.5.1 self-connection.

select a.ename,b.ename from emp a,emp b where b.empno=a.mgr ; 




<div class="se-preview-section-delimiter"></div>

Result:

+--------+-------+ 
| ename  | ename | 
+--------+-------+ 
| SMITH  | FORD  | 
| ALLEN  | BLAKE | 
| WARD   | BLAKE | 
| JONES  | KING  | 
| MARTIN | BLAKE | 
| BLAKE  | KING  | 
| CLARK  | KING  | 
| SCOTT  | JONES | 
| TURNER | BLAKE | 
| ADAMS  | SCOTT | 
| JAMES  | BLAKE | 
| FORD   | JONES | 
| MILLER | CLARK | 




<div class="se-preview-section-delimiter"></div>

4.5.2 External Links (left and right)

4.6 shows all employees in the same department as SMITH. (Single-line subquery)

• select * from emp where deptno=(select deptno from emp where ename="SMITH");
• select * from emp where deptno in (select deptno from emp where ename="SMITH");

4.7 Displays the employee's name, position, salary and department number that are identical to those of Department 10. (multi-line sub-query)

(Departments include 10)

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10); 




<div class="se-preview-section-delimiter"></div>

Figure:

+--------+-----------+------+--------+ 
| ename  | job       | sal  | deptno | 
+--------+-----------+------+--------+ 
| SMITH  | CLERK     |  800 |     20 | 
| JONES  | MANAGER   | 2975 |     20 | 
| BLAKE  | MANAGER   | 2850 |     30 | 
| CLARK  | MANAGER   | 2450 |     10 | 
| KING   | PRESIDENT | 5000 |     10 | 
| ADAMS  | CLERK     | 1100 |     20 | 
| JAMES  | CLERK     |  950 |     30 | 
| MILLER | CLERK     | 1300 |     10 | 
+--------+-----------+------+--------+ 




<div class="se-preview-section-delimiter"></div>

(Departments excluding 10)

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10; 




<div class="se-preview-section-delimiter"></div>

Figure:

+-------+---------+------+--------+ 
| ename | job     | sal  | deptno | 
+-------+---------+------+--------+ 
| SMITH | CLERK   |  800 |     20 | 
| JONES | MANAGER | 2975 |     20 | 
| BLAKE | MANAGER | 2850 |     30 | 
| ADAMS | CLERK   | 1100 |     20 | 
| JAMES | CLERK   |  950 |     30 | 




<div class="se-preview-section-delimiter"></div>

Use subqueries in from statements.

4.8 shows information about employees who are above the average salary of the department.

 select emp.ename,emp.sal,emp.deptno,tmp.myavg  from emp,(select avg(sal) myavg ,deptno from emp group by deptno )  tmp where emp.sal>myavg and tmp.deptno=emp.deptno;
 ```
//Figure:




<div class="se-preview-section-delimiter"></div>

```sql
+-------+------+--------+-----------+ 
| ename | sal  | deptno | myavg     | 
+-------+------+--------+-----------+ 
| KING  | 5000 |     10 | 2916.6667 | 
| JONES | 2975 |     20 | 2175.0000 | 
| SCOTT | 3000 |     20 | 2175.0000 | 
| FORD  | 3000 |     20 | 2175.0000 | 
| ALLEN | 1600 |     30 | 1566.6667 | 
| BLAKE | 2850 |     30 | 1566.6667 | 
+-------+------+--------+-----------+ 
 ```
##  4.9 Displays 5 to 10 entry-level employees (in chronological order)
```sql
select * from emp order by hiredate asc  limit 4,7; 




<div class="se-preview-section-delimiter"></div>

Figure:

+-------+--------+-----------+------+---------------------+------+------+--------+ 
| empno | ename  | job       | mgr  | hiredate            | sal  | comm | deptno | 
+-------+--------+-----------+------+---------------------+------+------+--------+ 
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 | NULL |     30 | 
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 | NULL |     10 | 
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 | 
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 | 
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL |     10 | 
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 | NULL |     20 | 
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 | NULL |     30 | 




<div class="se-preview-section-delimiter"></div>

4.10 Left and Right External Connections

• (Left External Connection): All table records on the left are displayed, and NULL is displayed if no matching records are found.
• (Right External Connection): All table records on the right are displayed, and NULL is displayed if no matching records are found.
  Examples are as follows:
  

4.11 shows the names of each employee and his superiors. If there is no superior, the name should also be displayed.

select a.ename,b.ename  from emp a left join emp b on  a.mgr=b.empno;




<div class="se-preview-section-delimiter"></div>

Figure:

+--------+-------+ 
| ename  | ename | 
+--------+-------+ 
| SMITH  | FORD  | 
| ALLEN  | BLAKE | 
| WARD   | BLAKE | 
| JONES  | KING  | 
| MARTIN | BLAKE | 
| BLAKE  | KING  | 
| CLARK  | KING  | 
| SCOTT  | JONES | 
| KING   | NULL  | 
| TURNER | BLAKE | 
| ADAMS  | SCOTT | 
| JAMES  | BLAKE | 
| FORD   | JONES | 
| MILLER | CLARK | 
+--------+-------+ 




<div class="se-preview-section-delimiter"></div>

5 Topics See the following picture:

5.1 First create the goods table.

create table goods (goodsId nvarchar(50) primary key, goodsName nvarchar(80) not null, unitPrice decimal(8,2) check(unitPrice > 0), category nvarchar(3) check(catagory in("food","Daily Necessities")), provider nvarchar(50)) default character set utf8;




<div class="se-preview-section-delimiter"></div>

Figure:

+-----------+--------------+------+-----+---------+-------+ 
| Field     | Type         | Null | Key | Default | Extra | 
+-----------+--------------+------+-----+---------+-------+ 
| goodsId   | varchar(50)  | NO   | PRI | NULL    |       | 
| goodsName | varchar(80)  | NO   |     | NULL    |       | 
| unitPrice | decimal(8,2) | YES  |     | NULL    |       | 
| category  | varchar(3)   | YES  |     | NULL    |       | 
| provider  | varchar(50)  | YES  |     | NULL    |       | 




<div class="se-preview-section-delimiter"></div>

5.2 Create customer table:

create table customer  (customerId nvarchar(50) primary key, custName nvarchar(50) not null, address nvarchar(100), email nvarchar(100) unique, sex nchar(1) default "male" check(sex in("male","female")) , cardId nvarchar(18) ) default character set utf8;




<div class="se-preview-section-delimiter"></div>

Figure:

+------------+--------------+------+-----+---------+-------+ 
| Field      | Type         | Null | Key | Default | Extra | 
+------------+--------------+------+-----+---------+-------+ 
| customerId | varchar(50)  | NO   | PRI | NULL    |       | 
| custName   | varchar(50)  | NO   |     | NULL    |       | 
| address    | varchar(100) | YES  |     | NULL    |       | 
| email      | varchar(100) | YES  | UNI | NULL    |       | 
| sex        | char(1)      | YES  |     | male      |       | 
| cardId     | varchar(18)  | YES  |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+ 




<div class="se-preview-section-delimiter"></div>

5.3 Create the purchase table.

create table purchase ( 
customerId nvarchar(50) , 
goodsId nvarchar(50) , 
nums int check(nums>0) , 
CONSTRAINT `purchase_fk_customerId` FOREIGN KEY (`customerId`) REFERENCES `customer` (`customerId`), 
CONSTRAINT `purchase_fk_goodsId'` FOREIGN KEY (`goodsId`) REFERENCES `goods` (`goodsId`)) default character set utf8;




<div class="se-preview-section-delimiter"></div>

Figure:

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| customerId | varchar(50) | YES  | MUL | NULL    |       | 
| goodsId    | varchar(50) | YES  | MUL | NULL    |       | 
| nums       | int(11)     | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

[Original] reprinted please indicate from Han Shunping's java Initiation to Serrsql Notes in Proficiency (including emp and dept tables, linux version of mysql)