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
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;
Query qualified data
Selectect field 1, field 2, field 3...from table name where condition;
# =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
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 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
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!)
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;
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;