MySQL Learning Notes

MySQL Learning (2)

Import prepared data

Prepared database tables for bjpowernode.sql

Enter create database bjpownode to create database

Enter use bjpowernode to use the database

Enter the following import data

source  D:\BaiduNetdiskDownload\learn_mysql\document\bjpowernode.sql #No semicolon!

Following source is the path to preparing the data

About imported tables

dept: Department table;emp: employee list;salgrade: wage scale

View data in tables

select * from table name

select * from emp; #View all data in the employee table
select * from dept;
select * from salgrade;

View the structure of the table

desc table name

desc emp;
desc dept;
desc salgrade;

Common mysql commands (continued)

exit; #Exit database
show databases; #View all databases
use test; #Using the test database
create database bjpowernode; #Create a database called bjpowernode
show tables; #See which tables are under the database
select version();  #View version number of mysql database
select databse();  #View the database currently in use
#No semicolon in mysql, semicolon means end
#\c can terminate command input, or ctrl+C

mysql statement learning

Simple Query

Query for a field: select field name from table name;

Note: both select and front are keywords, and field and table names are identifiers

select dname from dept;

Query for two or more fields**:**select field name 1, field name 2...from table name

select deptno,dname from dept;

**Query all fields: *1. Write down each field;2. Use (low efficiency, poor readability, not recommended in actual development)

** Aliase the column of the query: ** Aliase using the as keyword (or omit it directly)

select deptno,dname as deptname from dept;

Note: 1. Only the query result is aliased, the original table is unchanged, and the select statement will not be modified.

2. If an alias has spaces in it, you may make an error, or you may use single or double quotation marks to enclose it (single quotation marks are recommended, since single quotation marks are standard, double quotation marks cannot be used in oracle, only single quotation marks can be used)

**Calculate Annual Pay for Employees: **Mathematical expressions can be used in fields

select ename,sal*12 as yearsal from emp;
select ename,sal*12 as 'Annual salary' from emp;

Conditional Query

Query qualified data

Grammar Format:

Selectect field 1, field 2, field 3...from table name where condition;

All conditions:

# =equal to
select empno,ename from emp where sal = 800; #Query names and numbers of employees with pay equal to 800
select empno,sal from emp where ename = 'SMITH'; #Query SMITH number and salary
# <>or!=Not equal to
select empno,ename from emp where sal != 800; #Query the names and numbers of employees whose salaries are not equal to 800
select empno,ename from emp where sal <> 800; #Unequal Sign of Less Than Sign and Greater Than Sign
# Less than
select empno,ename,sal from emp where sal < 2000; #Query employee names and numbers with pay less than 2000
# <=less than or equal to
select empno,ename,sal from emp where sal <= 3000; #Query names and numbers of employees with pay less than or equal to 3000
# >greater than
select empno,ename,sal from emp where sal > 3000; #Query names and numbers of employees with salaries greater than 3000
# >=greater than or equal to
select empno,ename,sal from emp where sal >= 3000; #Query names and numbers of employees with pay greater than or equal to 3000
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000; 
#Query employee information for salaries between 2450 and 3000?Includes 2450 and 3000
# between...and....between two values, equal to >= and <=
select empno,ename,sal from emp where sal between 2450 and 3000; 
#With between,and must follow a small left, large right, closed interval, including values at both ends
# Is null is null (is not null is not empty)
#select empno,ename,sal,comm from emp where comm = null; 
#This statement does not work, you cannot use =, null means nothing, you cannot measure with equal sign
select empno,ename,sal,comm from emp where comm is null; #Query which employees have null allowances/allowances
select empno,ename,sal,comm from emp where comm is not null; #Query which employees'allowances/allowances are not null
# And and
select empno,ename,job from emp where job = 'MANAGER' and sal>2500; 
#Query employee information for jobs that are MANAGER and pay more than 2500
# Or or
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';#Query Job Is Employee of MANAGER and SALEESMAN
select * from emp where sal > 2500 and deptno = 10 or deptno = 20;
#Because the and priority is higher than or, the above statement executes and then or, which is to find out the employees whose salary is greater than 2500 and whose department number is 10 or all the employees in 20 departments.
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); #Parenthesized for or to execute first
# In contains, equal to more than one or (not in this range), in is not an interval, followed by a specific value
select empno,ename,job from emp where job in('MANAGER', 'SALESMAN'); #Query Job Is Employee of MANAGER and SALEESMAN
select ename,sal from emp where sal in(800, 5000); #Not 800 to 5000, but 800 and 5000
select ename,sal from emp where sal not in(800, 5000, 3000);#Data not in 800,5000,3000
#not is optional and is mainly used in is or in

Fuzzy Query

like, supports% or underline matching

%matches any number of characters, the underscore matches any one character,%and_Are special symbols

select ename from emp where ename like '%O%'; #Find the one with O in the name
select ename from emp where ename like '%T';  #Find names that end in T
select ename from emp where ename like 'K%';  #Find names that start with K
select ename from emp where ename like '_A%'; #Find the second word for each A
select ename from emp where ename like '__R%'; #Find out that the third letter is R
mysql> select name from t_student where name like '%\_%'; #Find the underscored, \escape character in the name

sort

Sort by a single field:

select ename,sal from emp order by sal; #Ascending by default
select ename,sal from emp order by sal asc;   #Specify ascending order
select ename,sal from emp order by sal desc;  #Specify Descending Order

Sort by two or more fields:

select ename,sal from emp order by sal asc, ename asc;
#Query the employee's name and salary and ask them to be in ascending order, if the salaries are the same, then in ascending order.
select ename,sal from emp order by 2; #2 means the second column sal, sorted by salary, not recommended for development

Comprehensive case

select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
#Find out information about employees whose salaries range from 1250 to 3000 and ask them to be in descending order.

Step 1: from Step 2: where Step 3: Selectect Step 4: order by (Sorting is always last!)

function

Data Processing Functions

Data processing functions, also known as single-line processing functions, have the following characteristics: one input corresponds to one output

As opposed to single-line processing functions, multiline processing functions have multiple inputs corresponding to one output

Single Line Processing Function

Common one-line processing functions:

# lower conversion to lowercase
select lower(ename) as ename from emp;
# upper conversion to uppercase
select upper(name) as name from t_student;
# Substr takes substring (substr (intercepted string, start subscript, intercepted length))
select substr(ename, 1, 1) as ename from emp; #Subscript starts at 1, No 0
# Find employee information whose first letter of employee name is A
# The first method: Fuzzy Query
select ename from emp where ename like 'A%';
# Second way: the substr function
select ename from emp where substr(ename,1,1) = 'A';
# concat function for string stitching
select concat(empno,ename) from emp;
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
#title case
# Length takes length
select length(ename) enamelength from emp;
# trim to remove spaces
select * from emp where ename = trim('   KING');
# str_to_date Converts a string to a date
# date_format format date
# format setting thousands
# case..when..then..when..then..else..end
select ename,job, sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
#When the employee's job is MANAGER, the salary is increased by 10%, when the job is SALESMAN, the salary is increased by 50%, other normal
#Do not modify the database, just display the query result as a pay increase	
# round-off
select round(1236.567, 0) as result from emp; #0 means reserve to integer digits
select round(1236.567, 1) as result from emp; #Keep 1 decimal place
select round(1236.567, 2) as result from emp; #Keep 2 decimal places
select round(1236.567, -1) as result from emp; #Keep to ten
# rand() generates random numbers
select rand() from emp; #Generate random numbers from 0 to 1
select round(rand()*100,0) from emp; #Generate random numbers within 100
# Ifnull converts null to a specific value. ifnull is an empty handler that specifically handles null
# In all databases, as long as NULL participates in mathematical operations, the end result is NULL.
# Usage of ifnull function: ifnull (data, which value is treated as), if "data" is NULL, which value is treated as this data structure.
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
# Calculate the annual salary for each employee: Annual salary = (monthly pay + monthly allowance) * 12

Grouping Function (Multiline Processing Function)

Features: Enter multiple lines, process at most one line

Note: Grouping functions must be grouped before they can be used. Without grouping, the whole table defaults to one group

# max maximum
select max(sal) from emp; #Find the maximum wage
# min minimum
select min(sal) from emp; #Find the minimum wage
# sum of sums
select sum(sal) from emp; #Calculate wages and
# avg mean
select avg(sal) from emp; #Calculate average wage
# Count	count
select count(ename) from emp; #Calculate Number of Employees

Notes when using grouping functions:

1. Grouping functions automatically ignore NULL and do not need to process NULL in advance.

2. What is the difference between count(*) and count (specific field) in the grouping function?

Count (field specific): Represents the total number of elements that are not NULL under this field.

count(*): The total number of rows in the statistics table. (Only one row of data counts is ++).Because each row of records cannot be NULL, it is meaningless to be NULL. If a column in a row of data is not NULL, the row of data is valid.

3. Grouping functions cannot be used directly in where clauses.

4. All grouping functions can be combined.

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

Grouped queries (very important)

In practical applications, there may be a need to group and then operate on each group of data, at which point we need to use group queries.

select ... from ... group by ...

Combine all the previous keywords together, using the order:

select ... from ... where ... group by ... order by ...

Execution order: 1. from 2.where 3.group by 4. select 5.order by

Why can't grouping functions be used directly behind where?

select ename,sal from emp where sal > min(sal); #Report errors.

Grouping functions must be grouped before they can be used. When where is executed, there is no grouping yet, so there cannot be a grouping function after where.

select sum(sal) from emp; 

This is not grouped because select executes after group by, so the sum() function can be used

# Find out the salary and
# The way to achieve this is to divide jobs into groups and then sum up wages
select job,sum(sal) from emp group by job;
# Execution order: Query data from emp table, group according to job field, and sum(sal) each group of data
select ename,job,sum(sal) from emp group by job;
# The above statement can be executed in mysql, but it doesn't make sense.
# The above statement executes an error in oracle, which has a stricter syntax than mysql. (The syntax of MySQL is relatively loose!)

Conclusion: In a select statement, if there is a group by statement, the select can only be followed by: the field participating in the grouping, as well as the grouping function, the others cannot follow.

# Find the highest salary for each department
# Realization ideas: Grouped according to department number, find the maximum value of each group.
select deptno,max(sal) from emp group by deptno;
# Find out the highest salary for "every department, different job"
# Tip: Combine two fields into one field to see. (Joint grouping of two fields)
select deptno, job, max(sal) from emp group by deptno, job;

**having statement: **Use having to further filter data after grouping.

Having cannot be used alone, where cannot be replaced by having, and must be used in conjunction with group by.

# Find the highest salary for each department and ask to show those with the highest salary greater than 3000
# Step 1: Find the highest salary for each department, group by Department number, and find the maximum for each group.
select deptno,max(sal) from emp group by deptno;
# Step 2: Require maximum salary above 3000
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
#Such two-step statements are inefficient to execute
#The practical idea: first find out all the items larger than 3000, then group them.
select deptno,max(sal) from emp where sal > 3000 group by deptno;

Optimize strategy: where and have, choose where first, where can't really be done, choose having again.

# where can't do it
# Find out the average salary per department and ask to show that the average salary is above 2500.
# Step 1: Find out the average salary per department
select deptno,avg(sal) from emp group by deptno;
# Step 2: Require that the average salary is above 2500
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;

summary

Enter order (not reversible): select...from...where...group by...having...order by...

Execution order: 1.from 2. where 3. group by 4. having 5. select 6.order by

Query data from a table.
Valuable data is filtered first by where criteria.
Grouping these valuable data.
After grouping, you can use having to continue filtering.
select query out.
Last sorted output!

#Find out the average salary for each job and ask to show that if the average salary is greater than 1500, the average salary is in descending order except for MANAGER jobs.
		select 
			job, avg(sal) as avgsal
		from
			emp
		where
			job <> 'MANAGER'
		group by
			job
		having
			avg(sal) > 1500
		order by
			avgsal desc;

Tags: Database MySQL

Posted on Sat, 04 Sep 2021 12:50:31 -0400 by leeharvey09