MySQL from entry to Entry (02)

# # # practice:

Create mydb4 character set UTF8 and use it

create database mydb4 character set utf8;
	use mydb4;
Copy the code

Select * from emP where id,name,sal,deptId character set UTf8

create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;
Copy the code

Select * from dept; loC; name; utf8

create table dept(id int,name varchar(10),loc varchar(20))charset=utf8;
Copy the code

Insert the following data into the department table: 1 Celestial department 2 Monster department pan Silk hole

insert into dept values(1.Department of Immortals.'heaven'), (2.'Department of Monsters'.'Winding hole');
Copy the code

Insert the data in the employee table: 1 Wukong 5000 1, 2 Bajie 2000 1, 3 Spider jing 8000 2, 4 Bone jing 9000 2

insert into emp values(1.'wukong'.5000.1), (2.'pig'.2000.1), (3.Spider spirit.8000.2), (4.'Ghost of Bones'.9000.2);
Copy the code

Query the name and salary of the employee whose salary is below 6000

 select name,sal from emp where sal<6000;
Copy the code

Changed the name of immortals department to Sutras department

update dept set name='Scripture taking Department' where id=1;
Copy the code

Add the bonus COMM field to the employee

 alter table emp add comm int;
Copy the code

Example Change the bonus of department 1 to 500

 update emp set comm=500 where deptId=1;
Copy the code

Change the address of the Scriptures collection Department to Mount Wutai

update dept set loc='Mount Wutai' where id=1;
Copy the code

Drop two tables

drop table emp;  drop table dept;    
Copy the code

Primary key constraint primary key

Primary key: A field that represents unique data is called a primary key
Constraints: Constraints are constraints that are added to table fields when a table is created
Primary key constraint: Restricts the value of a primary key to be unique and non-null
Testing:

create table t1(id int primary key,name varchar(10));
		insert into t1 values(1.'aaa');
		insert into t1 values(1.'bbb');   //Error Primary key value cannot Duplicate entry'1' for key 'PRIMARY'
		insert into t1 values(null.'ccc');//The primary key value cannot benull Column 'id' cannot be null
Copy the code

Primary key + increment AUTO_increment

Increment rule: The value increases from the historical maximum value to +1
Testing:

create table t2(id int primary key auto_increment,name varchar(10));
		insert into t2 values(null.'aaa');           1
		insert into t2 values(null.'bbb');           2
		insert into t2 values(10.'ccc');             10
		insert into t2 values(null.'ddd');           11
		delete from t2 where id> =10;
		insert into t2 values(null.'eee');           12 
Copy the code

### import *.sql file

D :/emp.sql;
If garbled characters are found, run the set names GBK command. # # # to distinct

Query the different jobs in the employee table

select distinct job from emp;
Copy the code

##is null and is not null

You cannot use = to determine if a field is null

Example Query the information about employees without superior

select * from emp where mgr is null;
Copy the code

Example Query the name and number of an employee with a superior

select ename,mgr from emp where mgr is not null;
Copy the code

### compare operator > < >= <= =! = and <> 16. Query the name and salary of the employee whose salary is less than or equal to 3000

select ename,sal from emp where sal< =3000;
Copy the code

Query the name and job of an employee whose job is not a programmer

select ename,job from emp where job! ='Programmer';
	select ename,job from emp where job<>'Programmer';
Copy the code

And and or

If multiple conditions are used to query data, and if multiple conditions are met at the same time, or can be used if one of these conditions is met
And similar && in Java, or similar in Java | |

Query information about employees whose salaries are greater than 1500 in department 1

select * from emp where deptno=1 and sal>1500;
Copy the code

Query job is hr or salary > 3000 employee name, job and salary.

select ename,job,sal from emp where job='personnel' or sal>3000;
Copy the code

###in(x,y,z)

The in keyword is used when multiple values of a field are queried

Example Query the information about employees whose salaries are 1500,3000, and 5000

select * from emp where sal=1500 or sal=3000 or sal=5000;
	select * from emp where sal in(1500.3000.5000);
Copy the code

###between x and y

Select * from employees whose salary is between 1000 and 2000;

select * from emp where sal> =1000 and sal< =2000;
	select * from emp where sal between 1000 and 2000;
Copy the code

Select * from employees whose salary is between 1000 and 2000

select * from emp where sal not between 1000 and 2000;
Copy the code

Example query the information about employees whose salaries are not 5000,3000,800

select * from emp where sal not in(800.3000.5000);
Copy the code

### Comprehensive practice

Query information about employees in department 3 who have a supervisor

select * from emp where mgr is not null and deptno=3;
Copy the code

Query the name, salary and department number of the employee whose salary is between 1000 and 2000 in department 2

select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;
Copy the code

Query information about employees whose salaries are 800 and 1600 in department 1

select * from emp where deptno=1 and sal in(800.1600);
Copy the code

Query information about employees whose salaries are higher than 2000 in departments 1 and 2

select * from emp where deptno in(1.2) and sal>2000;
Copy the code

Query the department numbers in the employee table

select distinct deptno from emp;
Copy the code

### fuzzy query like

_: indicates an unknown character
%: indicates 0 or multiple unknown characters
For example:
1. Start with x x%
2. End with x %x
3. Contains the x % x %
4. The second character is x _x%
5. The third from the bottom is x %x__
6. The second to last one starting with x is y x%y_

Example Query the name of the employee whose surname is Sun in the employee table

select ename from emp where ename like 'sun %';
Copy the code

Query information about employees whose names end with essence

select * from emp where ename like '% fine';
Copy the code

Query the name and job of the employee whose job includes sales

select ename,job from emp where job like '% sales %';
Copy the code

### order by

Format: ORDER by field ASC (default ascending order)/ DESC written in descending order after SQL statement

Query information about employees whose salaries are higher than 2000 and sort the salaries in ascending order

select * from emp where sal>2000 order by sal;
Copy the code

Query the name, salary, and department number of each employee, sorting by department number in descending order

select ename,sal,deptno from emp order by deptno desc;
Copy the code

Multifield sort, write multiple fields after order by separated by commas

Query the name, salary, and department number of each employee and sort by department number in descending order. If the department number is consistent, sort by salary in descending order

select ename,sal,deptno from emp order by deptno desc,sal desc;
Copy the code

Query information about employees in departments 1 and 2 in ascending order based on department ids. If department ids are the same, employees in descending order based on salaries are displayed

select * from emp where deptno in(1.2) order by deptno, sal desc;
Copy the code

### paging query

Format: limit Number of skipped items, number of requested items (also representing the number of items per page), written at the end of the SQL statement

Request the first 3 items of data in the employee table in descending order of salary (request 3 items of data on the first page)

select * from emp order by sal desc limit 0.3;
Copy the code

Select * from employee where salary is in descending order

select * from emp order by sal desc limit 3.3;
Copy the code

Query 2 items of data on page 3 of employee table (request items 5 and 6)

select * from emp limit 4.2;
Copy the code

Query information about the lowest paid employee

select * from emp order by sal limit 0.1;
Copy the code

SQL > select * from employee table

select * from emp order by sal limit 6.2;
Copy the code

### Comprehensive exercises

Select * from employee table where the second character of the employee’s name is 8

select ename,sal from emp where ename like Eighty percent '_';
Copy the code

Query the name and job of the employee whose job contains the sales word

select ename,job from emp where job like 'sold % %';
Copy the code

Query the name and salary of the employee whose salary is higher than 1000 yuan, in descending order of salary, query the 3 data on the second page

select ename,sal from emp where sal>1000 order by sal desc limit 3.3;
Copy the code

### Numerical calculation + – * /

Query each employee’s name, salary and year-end bonus (3 months salary)

select ename,sal,sal*3 from emp;
Copy the code

- Alias: alias for the queried fieldCopy the code

select ename as 'name',sal as 'wages',sal*3 as 'Year-end bonus' from emp;
	select ename 'name',sal 'wages',sal*3 'Year-end bonus' from emp;
	selectEname name,sal salary,sal*3Annual bonusfrom emp;
Copy the code

Query each employee’s name, salary and salary after a $5 raise

select ename,sal,sal+5After a pay risefrom emp;
Copy the code

### aggregate function

Aggregate function is to query a number of data statistical query, including: average, maximum, minimum, sum, count
The mean avg

Query the average salary of department 1

select avg(sal) from emp where deptno=1;
Copy the code

Query the average programmer salary

select avg(sal) from emp where job='Programmer';
Copy the code

The maximum Max

Query the highest salary in department 3

select max(sal) from emp where deptno=3;
Copy the code

Min min

Query minimum wage for sales

select min(sal) from emp where job='sales';
Copy the code

Sum sum

Query the sum of salaries in department 2

select sum(sal) from emp where deptno=2;
Copy the code

Count count

Query the number of employees whose salary is above 2000

select count(*) from emp where sal>2000;
Copy the code

Query the number of people in department 2

select count(*) from emp where deptno=2;
Copy the code

# # # exercises

Query the name and salary of the employee whose salary is higher than 2000 in the employee table

select ename,sal from emp where sal>2000 order by sal  limit 2.2;
Copy the code

Query the sum of salaries for sales related jobs

select sum(sal) from emp where job like '% sales %';    6625
Copy the code

Query the number of programmers

select count(*) from emp where job='Programmer';      2
Copy the code

Query the highest salary for an employee with a supervisor in department 1

select max(sal) from emp where deptno=1 and mgr is not null;
Copy the code

Query department 2’s maximum and minimum wage alias

select max(Sal) the highest salary,minThe minimum wagefrom emp where deptno=2;
Copy the code

Query the names of employees whose names contain empty characters in department 1

select ename from emp where deptno=1 and ename like 'empty % %';      
Copy the code

Group query

For each xx in the topic requirement, xx is used as a grouping field
Query the average salary of the employee table

select avg(sal) from emp;
Copy the code

Query the average salary of department 1

select avg(sal) from emp where deptno=1;  
Copy the code

Query the average salary for each department

select deptno,avg(sal) from emp group by deptno;
Copy the code

Query the highest salary per job

select job,max(sal) from emp group by job;
Copy the code

Query the number of people in each department

select deptno,count(*) from emp group by deptno;
Copy the code

Query the sum of salaries for each job

select job,sum(sal) from emp group by job;
Copy the code

Query the number of employees in each department who earn more than $1000

select deptno,count(*) from emp where sal>1000 group by deptno;
Copy the code

Query the highest salary for departments 1 and 2

select deptno,max(sal) from emp where deptno in(1.2) group by deptno;
Copy the code

Query the sum of salaries for each job in departments 1 and 2

select job,sum(sal) from emp where deptno in(1.2) group by job;
Copy the code

###having

General field conditions can only be written after WHERE. Aggregate functions cannot be written after WHERE
Having special conditions to write aggregate functions behind, and is used in conjunction with grouped queries
Order of each keyword: select… From tablename WHERE tablename group by having tablename order by having tablename limit… ;

Query the average salary of each department. Query only the information where the average salary is higher than 2000

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
Copy the code

Query the number of jobs for each job type

select job,count(*) from emp group by job having count(*)=1;
Copy the code

Query the average salary of each department, only those with a salary between 1000 and 3000, and filter out those with a salary below 2000.

select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)> =2000;
Copy the code

### Comprehensive practice

Query the employee id, name, salary of the employee who has no superior

select empno,ename,sal from emp where mgr is null;
Query the name and bonus of the employee with bonus

select ename,comm from emp where comm>0;
Query the name of the employee whose name contains the essence and salary

Select ename,sal from emp where ename = '% % %';
Query information about employees whose second character in their names is 8

Select * from emp where ename = '_ %';
Query information about employees whose salary is greater than 2000 in department 1

select * from emp where deptno=1 and sal>2000;
Query information about department 2 or employees whose salary is less than 1500

select * from emp where deptno=2 or sal<1500;
Query information about employees whose salaries are 3000, 1500, and 5000 in ascending salary order

Select * from emp where sal in(3000,1500,5000) order by sal;
Query the sum of salaries in department 3

select sum(sal) from emp where deptno=3;
Query the number of employees whose salaries are greater than 1000 in each department and sort them in ascending order

select deptno,count() from emp where sal>1000 group by deptno order by count();
Query the number of employees with supervisors in each job in descending order

select job,count() from emp where mgr is not null group by job order by count() desc;
Query the information of all employees and sort the salaries in ascending order by department ids. If the department ids are the same, the salaries are in descending order

select * from emp order by deptno,sal desc;
Query employees with leaders, the number of each department and the highest salary

select deptno,max(sal) from emp where mgr is not null group by deptno;
Query employees with leaders, in ascending order of salary, 2 columns on page 3

Select * from emp where MGR is not null order by sal limit 4,2;
Query the total salary of each department. Query only the employees with superiors and the total salary is greater than 5400. Finally, query only the first data in the result in descending order of the total salary

Deptno,sum(sal) from emp where MGR is not null group by deptno having sum(sal)>5400 order by deptno desc limit 0,1;

And and or

Related Posts

Ali bitter ten, difficult to win the Offer, summarized the twists and turns of the experience

Write backend services in Express+TypeScript

If your interviewer asks how fault tolerant a distributed architecture is, tell them!