24.23 total / statistical function
24.23.1 count
--How many students are there in a class?
--How many students have statistical mathematics scores greater than 90?
--How many people have a total score of more than 250?
--Difference between count(*) and count (column)
--Explanation: count(*) returns the number of rows of records that meet the conditions
--Count (column): count how many columns meet the conditions, but null will be excluded
24.23.2 sum
--Demonstrate the use of sum function
--Count the total score of a class in mathematics?
--Count the total scores of Chinese, English and mathematics in a class
--Count the total score of a class in Chinese, English and mathematics
--Count the average score of a class in Chinese
24.23.3 avg
--Demonstrate the use of avg
--Exercise:
--Find a class math average?
--Find the average of the total score of a class
24.23.4 max/min
--Demonstrate the use of max and min
--Get the highest and lowest scores of the class (the numerical range is particularly useful in Statistics)
--Get the highest and lowest scores in class mathematics
24.23.5 use the group by clause to group columns [create test table first]
24.23.6 using the having clause to filter the grouped results is often used in combination with group by
Create a test table to test group by
Three test sheets:
[First sheet]-- Employee table CREATE TABLE emp (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /number/ ename VARCHAR(20) NOT NULL DEFAULT "", /name/ job VARCHAR(9) NOT NULL DEFAULT "",/work/ mgr MEDIUMINT UNSIGNED ,/Superior number/ hiredate DATE NOT NULL,/Entry time/ sal DECIMAL(7,2) NOT NULL,/salary/ comm DECIMAL(7,2) ,/Bonus/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /Department number/ ); -- Add test data INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30), (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30), (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20), (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30), (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30), (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10), (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20), (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10), (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30), (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30), (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20), (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10); SELECT * FROM emp;
[Second sheet]-- Wage scale #Wage scale CREATE TABLE salgrade ( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /Wage scale/ losal DECIMAL(17,2) NOT NULL, /* Minimum wage at this level */ hisal DECIMAL(17,2) NOT NULL /* Maximum wage at this level*/ ); INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); SELECT * FROM salgrade; SELECT * FROM dept; SELECT * FROM emp;
[[sheet 3]--Department table CREATE TABLE dept( /Department table/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ); INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'); SELECT * FROM dept;
--1 ? How to display the average wage and maximum wage of each department
--Why are there 10, 20, 30 and 40 Department numbers, but the query results only show the numbers of three departments?
--Because there's no one in department 40!
-- 2？ Displays the average wage and minimum wage for each position in each department
In the previous question, you only need to write a depto according to the grouping of departments. Now the question is grouped according to the two standards of departments and positions, so you need to write these two depto and jobs
--3 ? Displays the department number whose average salary is less than 2000 and its average salary / / alias
-- Use mathematical methods to deal with decimal points SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno FROM emp GROUP BY deptno; -- ?Displays the average wage and minimum wage for each position in each department -- Teacher analysis 1. Displays the average wage and minimum wage for each department -- 2. Displays the average wage and minimum wage for each position in each department SELECT AVG(sal), MIN(sal) , deptno, job FROM emp GROUP BY deptno, job; -- ?Displays the department number whose average salary is less than 2000 and its average salary // alias -- Teacher analysis [write sql The idea of sentence is to simplify,crush one by one] -- 1. Displays the average salary and department number of each department -- 2. Based on the results of 1, filter and retain AVG(sal) < 2000 -- 3. Filter using aliases SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; -- Use alias SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;
24.24 string correlation function
--Demonstrates the use of string related functions, using the emp table
--1 CHARSET(str)
Returns the string character set
--2 CONCAT (string2 [,... ]) Concatenate strings to splice multiple columns into one column
--3 instr (string, substring) returns the position where substring appears in the string, but does not return 0
--dual sub table and system table can be used as test table
--4 convert ucase (string2) to uppercase
--5 convert lcase (string2) to lowercase
--6 left (string2, length) takes length characters from the left in string2
--Right (string2, length) takes length characters from the right in string2
--7 length (string) string length [in bytes]
--8 REPLACE (str ,search_str ,replace_str )
--Replace in str_ STR replace search_str
--If it is manager, replace it with manager
--9 STRCMP (string1 ,string2 )
Compare two string sizes character by character
-- SUBSTRING (str , position [,length ])
--Start from str's position [calculate from 1], and take length characters
--Take 2 characters from the first position of the ename column
--Both left and right sides of ltrim (string2) rtrim (string2) trim (string) can be removed
--Remove front-end or back-end spaces
--Exercise: display the names of all employee emp forms in lowercase
--Method 1
--First, take out the first character of ename and turn it into lowercase
--Splice it with the following string and output it
24.25 mathematical correlation function
--Demonstrate mathematical correlation functions
--1 ABS(num) absolute value
--2 BIN (decimal_number) decimal to binary
--3 CEILING (number2) rounds up to get the smallest integer larger than num2
--4 CONV(number2,from_base,to_base) binary conversion. The following meaning is that 8 is decimal 8, which is converted to binary output
--6 the following meaning is that 16 is hexadecimal 16, which is converted to hexadecimal output
--7 FLOOR (number2) rounds down to get the largest integer smaller than num2
--8 FORMAT (number,decimal_places )
--9 HEX (DecimalNumber) to hex
--10 LEAST (number , number2 [,..]) Find the minimum value
--11 mod (numerator, denominator) remainder
--12 RAND([seed])
24.26 time date correlation function date sql
Date correlation function
--Date time correlation function
--1 CURRENT_DATE() current date
--2 CURRENT_TIME() current time
-- CURRENT_TIMESTAMP() current timestamp
-- Create test table information table -- Date time correlation function -- CURRENT_DATE ( ) current date SELECT CURRENT_DATE() FROM DUAL; -- CURRENT_TIME ( )current time SELECT CURRENT_TIME() FROM DUAL; -- CURRENT_TIMESTAMP () Current timestamp SELECT CURRENT_TIMESTAMP() FROM DUAL; -- Create test table information table CREATE TABLE mes( id INT , content VARCHAR(30), send_time DATETIME ); -- Add a record INSERT INTO mes VALUES(1, 'Beijing News', CURRENT_TIMESTAMP()); INSERT INTO mes VALUES(2, 'Shanghai News', NOW()); INSERT INTO mes VALUES(3, 'Guangzhou news', NOW()); SELECT * FROM mes; SELECT NOW() FROM DUAL;
--Application instance on
--1. Display all news information. The release date only shows the date, not the time
--Please check the news released within 10 minutes and be sure to sort out your ideas
Mode 1:
Mode 2:
--3 please find the difference between 2011-11-11 and 1990-1-1 in the sql statement of mysql
--Please use the sql statement of mysql to find out how many days you have lived? [exercise] born on November 11, 1986
--If you can live to be 80, find out how many days you can live [exercise] born on November 11, 1986
--First find out what date X is when you are 80 years old
--Then use DateDiff (x, now ()); 1986-11-11->datetime
--INTERVAL 80 YEAR: YEAR can be YEAR, month, day, hour, minute and second
--'1986-11-11' can be date, datetime and timestamp
--Please find the difference between 2011-11-11 and 1990-1-1 in the sql statement of mysql
-- YEAR|Month|DAY| DATE (datetime )
year
month
day
Specific month, November
-- unix_timestamp(): returns the number of seconds from 1970-1-1 to the present
-- FROM_UNIXTIME(): a unix_timestamp seconds [timestamp], converted to the date in the specified format
--The format of% Y-%m-%d is specified, which means month, year and day
--Meaning: in development, you can store an integer and then represent the time through FROM_UNIXTIME conversion
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL; SELECT * FROM mysql.user \G
24.27 encryption and system functions PWD sql
--Demonstrate encryption and system functions
--You can view which users log in to mysql and the login IP
--User @ IP address
--DATABASE() queries the currently used database name
--MD5(str) calculates a string of MD5 32 for the string, which is commonly encrypted (user password)
--The root password is HSP - > encrypted MD5 - > the encrypted password is stored in the database
--Demo user table. When storing passwords, it is md5
-- Demonstrate encryption functions and system functions
-- USER() Query user
-- Can view login to mysql Who are the users and who are logged in IP
SELECT USER() FROM DUAL; -- user@IP address
-- DATABASE()Query the currently used database name
SELECT DATABASE();
-- MD5(str) Calculate a value for the string MD5 32 String, common(User password)encryption
-- root The password is hsp -> encryption md5 -> The encrypted password is stored in the database
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- Demonstrate the user table. When saving the password, yes md5
CREATE TABLE hsp_user (
id INT ,
name VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, 'oracle ', MD5('hsp'));
SELECT * FROM hsp_user;
SELECT * FROM hsp_user
WHERE name = 'oracle ' AND pwd = MD5('hsp');
-- PASSWORD(str) -- Encryption function(Another algorithm is used), MySQL The user password of the database is PASSWORD Function encryption
SELECT PASSWORD('hsp') FROM DUAL; -- Database *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G From original password str Calculates and returns the password string
-- Usually used for mysql User password encryption for database
-- mysql.user Represents the database.surface
SELECT * FROM mysql.user24.28 process control function
#Demonstrate process control statements
#IF(expr1,expr2,expr3) if expr1 is True, expr2 is returned; otherwise, expr3 is returned
#IFNULL(expr1,expr2) if expr1 is not NULL, expr1 is returned, otherwise expr2 is returned
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [similar to multiple branches.]
#If expr1 is TRUE, expr2 is returned; if expr2 is t, expr4 is returned; otherwise, expr5 is returned
-- 1. Query the emp table. If comm is null, 0.0 will be displayed
--The teacher explained that to judge whether it is null, use is null. If it is not empty, use is not
Mode 2:
-- 2. If the job of emp table is CLERK, the employee will be displayed; if it is MANAGER, the MANAGER will be displayed
--If it is SALESMAN, the sales personnel will be displayed, and others will be displayed normally
