The article directories

• One, foreword
• Insert data, delete data, update data
• • 2.1 Inserting Data
  • 2.2 Deleting Data
  • 2.3 Updating Data
• Iii. Query data (full text focus)
• • 3.1 the original table
  • 3.2 Select Statement Introduction
  • 3.3 Basic SELECT query statement
  • 3.4 Setting Search conditions
  • 3.5 Sorting Query Results
  • 3.6 Using aggregate functions
  • 3.7 Grouping query Results
  • 3.8 Specify search criteria for groups or aggregations
  • 3.9 Connection Query
  • • 3.9.1 inside connection
    • 3.9.2 outer join
    • 3.9.3 Cross Connection
  • 3.10 the subquery
  • 3.11 Merge Query
• Fourth, the end

One, foreword

There are three stages to learning SQL statements.

Stage 1: Usage level: understand SQL statements and be able to implement CRUD functions according to business requirements;

The second stage: principle level: understand the basic principle of database, index principle, can locate and analyze database performance bottleneck, know how to optimize database, how to create efficient index reasonably, how to prevent SQL injection network attacks, etc.

Phase 3: Architecture level (combined with highly concurrent distributed projects) :

Tend to architecture, data security, can reasonably design the database table structure, can achieve distributed database, sub-table sub-library, master and slave, read and write separation, data security backup and other work.

This article introduces the first stage, which flexibly uses SQL statements to meet business requirements. The latter two stages will be described in my blog. This article is divided into two parts, including “Insert Data, Delete Data, Update Data” and “Query Data”.

Insert data, delete data, update data

2.1 Inserting Data

INSERT INTO table_name (column1,column2,column3…) VALUES (value1, value2, value3,…). ;

Requirement: Insert data

The SQL statement:

insert into departments(dep_name) values('Personnel Department');
select * from departments;
Copy the code

Running results:

2.2 Deleting Data

delete from employees where emp_id = 2;
select * from employees;
Copy the code

delete from employees where wage < 3000;
select * from employees;
Copy the code

truncate table employees;
select * from employees;
Copy the code

2.3 Updating Data

UPDATE table_name SET column1=value1,column2=value2,… WHERE some_column=some_value;

Demand: all staff salary plus 10 yuan

The SQL statement:

update employees set  wage=wage+10;
select * from employees;
Copy the code

Running results:



Requirement: The salary of personnel department will be increased by 50 yuan per employee

The SQL statement:

update employees set  wage=wage+50 where dep_id=1;
select * from employees;
Copy the code

Running results:

Iii. Query data (full text focus)

3.1 the original table

In the background of the database operation, the query operation is the most commonly used, but also involves the most changes, here separately to explain. Give two tables Employees, Departments as follows:

The Departments table:



Employees table:

3.2 Select Statement Introduction

3.3 Basic SELECT query statement

1. Query all data

Query the name of the select * FROM table

Requirement: Query all employee information

The SQL statement:

select * from employees;
Copy the code

Running results:



2. Query specified column data

Description: Returns only data for the specified column

Requirement: Only name, sex and position of employee

The SQL statement:

select Emp_name,Sex,Title from employees;
Copy the code

Running results:



3. Use the Distinct keyword

Description: The distinct keyword is used to return records that do not duplicate data in a specified column in a result set

Requirement: Query all job data

The SQL statement:

select Title from employees;
Copy the code

Running results:



Requirement: Query all job data, filter out duplicate data

The SQL statement:

select distinct Title from employees;
Copy the code

Running results:



4, limit keyword

Meaning: The limit keyword collects only rows in a specified range

Requirement: query (3,8) employee records

The SQL statement:

select * from employees limit 3.5;
Copy the code

Running results:



Special: Limit has only one parameter

Requirement: Query the first 3 employee records

The SQL statement:

select * from employees limit 3;
Copy the code

Running results:

LIMIT accepts one or two numeric parameters. Both parameters must be integer constants. Given two arguments, the first parameter specifies the offset of the first row to return and the second parameter specifies the maximum number of rows to return, with an initial offset of 0(instead of 1). If there is only one parameter, LIMIT n is equivalent to LIMIT 0,n, so the LIMIT keyword in mysql is the same as the top keyword in SQLServer

5. Change the column headers displayed

Custom query result column headers (instead of using the original table column headers)

Requirement: query the employee’s name, gender, position, salary, ID card, and display the specified Chinese column name

The SQL statement:

selectEmp_name as name, Sex as gender,Title as job,Wage as salary,IdCard as id from Employees;Copy the code

Running results:



You can also omit the AS keyword

3.4 Setting Search conditions

select * from Employees where Wage>3000 and Wage<4000;
Copy the code

select * from Employees where Wage between 3000 and 4000;
Copy the code

select * from Employees where Emp_name in ('Joe'.'bill'.'Cathy');
Copy the code

select *  from Employees where IDCard like '%ddd%'
Copy the code

select * from Employees where IDCard like '110123_adx_'
Copy the code

select *  from Employees where title = 'Department Manager' and sex='male'
Copy the code

3.5 Sorting Query Results

Use the order by clause to sort the query results. The sort expression can be either a column or an expression. ASC indicates the ascending order. DESC indicates the descending order. ASC is the default value. In the Order BY clause, you can sort by more than one sort expression at a time, from left to right.

Requirement: Query employee information and sort by name

The SQL statement:

select *  from Employees order by Emp_name
Copy the code

Running results:

Need: query employee information, and sort by gender and salary (sort by gender first, and then by salary if gender is the same)

The SQL statement:

select *  from Employees order by Sex,Wage
Copy the code

Running results:

3.6 Using aggregate functions

selectCount (*) as number of records from employees;Copy the code

selectAvg (wage) as from employees;selectAvg (wage) as wage from employees where sex='male';
Copy the code

selectSum (wage) as from employees;Copy the code

3.7 Grouping query Results

When the SELECT clause contains an aggregate function, you can use the Group by clause to Group the query results and calculate the summary value of each Group of records.

Demand: The highest salary for all employees by gender

The SQL statement:

selectSex as sex, Max (wage) as maximum salary from employees group by sexCopy the code

Running results:

Demand: The highest salary for all male employees

The SQL statement:

selectSex as sex, Max (wage) as maximum salary from employees where sex='male' 
group by sex
Copy the code

Running results:

3.8 Specify search criteria for groups or aggregations

selectDep_id, Max (wage) as maximum salary from employees group by dep_id having Max (wage)>6000
Copy the code

3.9 Connection Query

Join queries involve joining multiple tables, in this case the Employees and departments tables, using tertiary headings “inner join”, “outer join”, and “cross join”

3.9.1 inside connection

select t1.Dep_name , t2.Emp_name
from departments t1 ,employees t2
where t1.Dep_id = t2.Dep_id
Copy the code

select t1.Dep_name , t2.Emp_name
from departments t1  inner join employees t2
on t1.Dep_id = t2.Dep_id
Copy the code

3.9.2 outer join

Each row of the primary table is used to match the data columns in the secondary table. The data in the symbolic join condition will be directly returned to the result set. For the columns that do not meet the join condition, the NULL value will be filled and then returned to the result set. Outer connection can be divided into left outer connection and right outer connection.

1. Left-outer JOIN meaning: Left-outer JOIN takes the table to the left of the JOIN clause as the primary table, and all records in the primary table will appear in the result set. If the records in the primary table have no matching data in the right table, the column value in the right table in the result set is NULL. LEFT OUTER JOIN can be implemented using the LFET OUTER JOIN or LEFT JOIN keywords.

Requirement: Query employees from all departments (departments DEPARTMENTS DEPARTMENTS, employees as primary table, employees as secondary table)

The SQL statement:

select t1.Dep_name , t2.Emp_name
from departments t1  left join employees t2
on t1.Dep_id = t2.Dep_id
Copy the code

Running results:



2. Right outer connection

The right outer JOIN takes the table to the right of the JOIN JOIN clause as the primary table, and all records in the primary table will appear in the result set. If the records in the primary table have no matching data in the left table, the column value in the left table in the result set is NULL. Use the RIGHT OUTER JOIN or RIGHT JOIN keyword to define the RIGHT OUTER JOIN.

Requirement: Query departments of all employees (employees primary table, departments DEPARTMENTS departments secondary table)

The SQL statement:

select t1.Dep_name , t2.Emp_name
from departments t1  right join Employees t2
on t1.Dep_id=t2.Dep_id
Copy the code

Running results:



Requirement: Query employees from all departments (departments DEPARTMENTS DEPARTMENTS, employees as primary table, employees as secondary table)

The SQL statement:

select t1.Dep_name , t2.Emp_name
from  Employees t2   right join  departments t1 
on t1.Dep_id=t2.Dep_id
Copy the code

Running results:



Requirement: Query employees from all departments, which must be NULL (departments DEPARTMENTS departments departments departments departments primary table, employees secondary table)

The SQL statement:

select t1.Dep_name , t2.Emp_name
from departments t1   left join    Employees t2
on t1.Dep_id=t2.Dep_id
where t2.Emp_name is null
Copy the code

Running results:

3.9.3 Cross Connection

select t1.Dep_name , t2.Emp_name
from departments t1  cross join   Employees t2
Copy the code

3.10 the subquery

A subquery is a SELECT statement nested within another SELECT statement. SELECT statements can be nested in both the WHERE and HAVING clauses. The realization of four sub-query: use IN keyword to connect sub-query, use equal sign to connect sub-query, use EXISTS keyword to connect sub-query, use sub-query IN the HAVING clause

1, IN keyword connection sub-query demand: query and Zhang SAN IN the same department of all the employees’ names

The SQL statement:

select Emp_name from Employees where Dep_id in
(select dep_id from Employees where Emp_name = 'Joe')
Copy the code

Running results:

2. Join subqueries with equal signs

Need: Query the names of all employees in the same department as John

The SQL statement:

select Emp_name from Employees where Dep_id =
(select dep_id from Employees where Emp_name = 'Joe')
Copy the code

Running results:

Requirement: Query information of all employees in finance department

The SQL statement:

select * from Employees where dep_id
= (select dep_id from departments where dep_name= 'Finance Department' )
Copy the code

Running results:



Join subqueries using the EXISTS keyword

Requirement: Query information of all employees in finance department

The SQL statement:

select * from Employees where Exists
(select * from departments where dep_id=Employees.Dep_id and dep_name='Finance Department' )
Copy the code

Running results:

4. Use subqueries in the HAVING clause

Demand: Query records where the average salary of the department is greater than the average salary of all employees (department + average salary of the department)

The SQL statement:

selectdepartments.dep_name,avg(Employees.wage) from Employees,departments where Employees.dep_id = departments.dep_id group by  departments.dep_name having avg(employees.wage) > (select avg(wage) from employees)
Copy the code

Running results:

3.11 Merge Query

select dep_id , dep_name from departments 
union
select dep_id , emp_name from employees where title ='Department Manager' 
Copy the code

Fourth, the end

This article is divided into two parts, including “Insert data, delete data, update data” and “Query data”, to help readers complete the first stage of MySQL learning — understand SQL statements, can implement CRUD function according to business requirements. Play code every day, progress every day!