1, SELECT clause
/*
Basic query:
select * from Table name [where condition];
select Field list from table name [where condition];
Extended query, 5 clauses of select statement:
(1)where
where Condition is used to filter out the qualified records (rows) from the table
(2)group by
(3)having
(4)order by
(5)limit
These five clauses can appear at the same time or only a part of them. If there is a having, there must be a group by, but if there is a group by, there may not be a having
If more than one of the five clauses appear at the same time, it must be in the order of (1) - (5)
For example, before grouping statistics, you need to filter out the rows that meet the conditions, or exclude the rows that do not meet the conditions before statistics
*/
#Query all female employees
SELECT * FROM t_employee WHERE gender = 'female';
#Query the names and salaries of all female employees
SELECT ename,salary FROM t_employee WHERE gender = 'female';
1. Grouping function
/*
Grouping function
(1)sum
(2)count
(3)avg
(4)max
(5)min
*/
#Inquire how much the whole company will pay this month, regardless of bonus and deducted money for the time being
#That is, to query the total wages of all employees in the company
SELECT SUM(salary) AS "Total wages" FROM t_employee;
#Query the total number of employees in the whole company
SELECT COUNT(eid) AS "Total number" FROM t_employee; #If count (field name), the row whose field is null will be excluded
SELECT COUNT(1) AS "Total number" FROM t_employee; #If count or count(*), the number of rows is counted
SELECT COUNT(*) AS "Total number" FROM t_employee;
#Query the average salary of the whole company
SELECT AVG(salary) AS "Average salary of the whole company" FROM t_employee;
#Query the company's maximum wage
SELECT MAX(salary) AS "Company wide maximum wage" FROM t_employee;
#Query the company's minimum wage
SELECT MIN(salary) AS "Company wide minimum wage" FROM t_employee;
#Query the average wage, maximum wage and total wage of female employees in the whole company
SELECT AVG(salary),MAX(salary),SUM(salary) FROM t_employee WHERE gender = 'female';
#Query the average wage, maximum wage and total wage of female employees in department did=5
SELECT AVG(salary),MAX(salary),SUM(salary) FROM t_employee WHERE gender = 'female' AND did = 5;
2. GROUP BY
/*
group by: Grouping statistics
*/
#Query the average salary of each department
SELECT did,AVG(salary) FROM t_employee GROUP BY did;
#Query the average salary of each department to exclude employees without departments
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did ;
#Query each department number, department name, and average salary to exclude employees without Department
#Joint query required
#Use Select did, AVG (salary) from t "employee where did is not null group by did; result and department table to jointly query
#Query the maximum wage of female employees in each department
SELECT did,MAX(salary) FROM t_employee WHERE gender = 'female' GROUP BY did;
SELECT did,MAX(salary) FROM t_employee WHERE gender = 'female' AND did IS NOT NULL GROUP BY did;
#Query the average wage of male and female employees
SELECT gender,AVG(salary) FROM t_employee GROUP BY gender;
#Query the average wage of male and female employees in each department
#First by department, then by men and women
SELECT did, gender, AVG(salary) FROM t_employee GROUP BY did,gender;
SELECT did, gender, AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did,gender;
#Query the total wages of each department
SELECT did,SUM(salary) FROM t_employee GROUP BY did;
#Query the number of male and female employees in each department
SELECT did, gender, COUNT(*) FROM t_employee WHERE did IS NOT NULL GROUP BY did,gender;
3. Good partner of GROUP BY, group filtering: HAVING
/*
having: Writing condition
where And having:
(1)where After that, grouping function is not allowed. After having, grouping function can be added
(2)where It is used to filter the records in the original table, and having is used to filter the statistics results
*/
#Query the average wage of each department. Only those with an average wage of more than 12000 are displayed
SELECT did,AVG(salary) FROM t_employee GROUP BY did HAVING AVG(salary) > 12000
#Query the maximum wage of each department, and it is required to display that the maximum wage is less than 12000
SELECT did,MAX(salary) FROM t_employee GROUP BY did HAVING MAX(salary) < 12000
SELECT did,MAX(salary) AS "m" FROM t_employee GROUP BY did HAVING m < 12000
4. The result is too messy. ORDER BY
/*
order by: sort
order by Field name / statistical result [DESC/ASC], field name / statistical result [DESC/ASC]
*/
#Query employee's name and salary, and sort by salary from high to low
SELECT ename,salary FROM t_employee ORDER BY salary DESC;
#Query the employee's number, name and salary, and sort by salary from high to low. If the salary is the same, then sort by number in ascending order
SELECT eid,ename,salary FROM t_employee ORDER BY salary DESC,eid ASC;
#Query the average wage of each department, and sort it in ascending order
SELECT did,AVG(salary) FROM t_employee GROUP BY did ORDER BY AVG(salary) ASC;
#Query the average wage of female employees in each department, sort by the ascending order of the average wage, and display only those whose average wage is higher than 12000
SELECT did,AVG(salary)
FROM t_employee
WHERE gender = 'female'
GROUP BY did
HAVING AVG(salary) > 12000
ORDER BY AVG(salary) ASC;
5. Too many results, LIMIT expert: LIMIT
/*
limit m,n
Starting from item m, output n items; apply to paging scenario
m = (page - 1)*Records per page
n = Records per page
*/
#Query employee information, display 10 items per page, display the first page
SELECT * FROM t_employee LIMIT 0,10
#Query employee information, display 10 items per page, display the second page
SELECT * FROM t_employee LIMIT 10,10
#Query employee information, display 5 items per page, display the third page
SELECT * FROM t_employee LIMIT 10,5
#Query the average wage of female employees in each department, sort by the ascending order of average wage, and only display those whose average wage is higher than 12000,
#1 bar per page, second page
SELECT did,AVG(salary)
FROM t_employee
WHERE gender = 'female'
GROUP BY did
HAVING AVG(salary) > 12000
ORDER BY AVG(salary) ASC
LIMIT 1,1;
2, Subquery
/*
In some cases, when a query is made, the required condition or data is the result of another select statement,
At this time, subqueries are used.
Queries executed before the current query and nested in the current query are called subqueries.
Sub queries are divided into:
(1) where type
Subqueries are nested in where
There are two types of operators for conditions:
=, >, > =, <, < =,! = the result of the subsequent sub query must be "single value"
In, = any, > all, >
(2) from type
Subqueries nested after from
(3) exists type
*/
1. WHERE type
/*
where type
*/
#Query the information of employees with the highest salary in the company
#(1) Query maximum wage
SELECT MAX(salary) FROM t_employee;
#(2) Query the information of the highest paid employee
SELECT * FROM t_employee WHERE salary = 130990
#(3) together
SELECT * FROM t_employee WHERE salary = (SELECT MAX(salary) FROM t_employee)
#Query any employee with the same salary as sun Honglei, Liu Ye and Fan Bingbing
#Query the salaries of sun Honglei, Liu Ye and Fan Bingbing
SELECT salary FROM t_employee WHERE ename IN ('Honglei Sun','Liu Ye','Fan Bingbing');
#Query any employee with the same salary as sun Honglei, Liu Ye and Fan Bingbing
SELECT * FROM t_employee WHERE salary IN (SELECT salary FROM t_employee WHERE ename IN ('Honglei Sun','Liu Ye','Fan Bingbing'))
SELECT * FROM t_employee WHERE salary = ANY (SELECT salary FROM t_employee WHERE ename IN ('Honglei Sun','Liu Ye','Fan Bingbing'))
#Query female employees with higher salary than Li Bingbing
SELECT * FROM t_employee WHERE gender = 'female' AND salary > (SELECT salary FROM t_employee WHERE ename = 'Li Bingbing');
#Query the information of employees with the highest salary in the company
SELECT * FROM t_employee WHERE salary >= ALL(SELECT salary FROM t_employee)
2, type FROM
/*
from type
*/
#Query each department number, department name, and average salary to exclude employees without departments, including those without employees
#The first step is to query the average salary of each department and exclude employees without departments
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did;
#Step 2: query jointly with the result and T Department
SELECT t_department.*, temp.pingjun
FROM t_department LEFT JOIN (SELECT did,AVG(salary) AS pingjun FROM t_employee WHERE did IS NOT NULL GROUP BY did) AS temp
ON t_department.did = temp.did
3. EXISTS type
/*
exists type
*/
#Query department information. The Department must have employees
SELECT * FROM t_department
WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.did = t_department.did)
#For each line record of 'select * from T Department', substitute it into (select * from t employee) according to t employee.did = t department.did. If the result can be found, it means that the line should be left, otherwise it will be excluded
#Query department information. The Department must have employees
#Using inner connection query, you can also
SELECT DISTINCT t_department.*
FROM t_department INNER JOIN t_employee
ON t_employee.did = t_department.did
SELECT t_department.*
FROM t_department INNER JOIN t_employee
ON t_employee.did = t_department.did
GROUP BY did
3, Single line function
/*
Single line and group functions:
Only the records of a certain line are calculated,
The grouping function is a multi row statistics / total operation.
Grouping function: sum,count,avg,max,min all seek a result for multiple lines
1. Mathematical function
Mathematical functions:
round(x,y): take y place after decimal point and round
truncate(x,y): directly cut off, keep the decimal point of X and take the Y position
*/
#Query employee's name, salary and keep one decimal place
SELECT ename,salary,ROUND(salary,1) FROM t_employee;
#Query employee's name, salary and keep one decimal place
SELECT ename,salary,TRUNCATE(salary,1) FROM t_employee;
#select ceil(2.2),floor(2,2);
SELECT CEIL(2.2),FLOOR(2.2);
#Average salary of each department
SELECT did, ROUND(AVG(salary),2) FROM t_employee GROUP BY did;
2. String function
/*
String function
*/
#Query the last name of each employee, regardless of multiple surnames
SELECT ename AS "Full name", LEFT(ename,1) AS "surname" FROM t_employee;
/*
java: Subscripts start at 0,
str.substring(index)
str.substring(start,end)
mysql: Subscript starts at 1
substring(str,index)
substring(str,start,len)
*/
SELECT ename AS "Full name", SUBSTRING(ename,1,1) AS "surname" FROM t_employee;
#Length of query employee's name
/*
java China:
str.length()
mysql China:
length(str): Find the length of string, number of bytes
char_length(str)
*/
SELECT ename, LENGTH(ename) FROM t_employee;
SELECT ename, CHAR_LENGTH(ename) FROM t_employee;
#Query all employees whose names are three words
SELECT * FROM t_employee WHERE CHAR_LENGTH(ename) = 3;
/*
java String splicing in:
(1)+
(2)str.concat(xx)
mysql String splicing in:
Only concat()
In mysql, the +, are all summations. If they are not numbers, they will try their best to sum. The results are not necessarily right
*/
#To query the name and mobile number of the employee, the result should be displayed as follows: Sun Honglei: 13789098765
SELECT CONCAT(ename,':',tel) FROM t_employee;
/*
In java, trim() means to remove the blank space before and after
In mysql, the functions of trim series
*/
SELECT CONCAT('[',TRIM(' hello world '),']')
SELECT CONCAT('[',LTRIM(' hello world '),']')
SELECT CONCAT('[',RTRIM(' hello world '),']')
SELECT CONCAT('[',TRIM(BOTH '&' FROM '&&&&&hello world&&&&'),']')
SELECT CONCAT('[',TRIM(LEADING '&' FROM '&&&&&hello world&&&&'),']')
SELECT CONCAT('[',TRIM(TRAILING '&' FROM '&&&&&hello world&&&&'),']')
3. Date time function
/*
Date time function
*/
#Get current system time
SELECT NOW(),SYSDATE()
SELECT CURRENT_DATE(),CURRENT_TIME()
#Query current year
SELECT YEAR(CURRENT_DATE())
#Query employees who meet the age of 40
SELECT * FROM t_employee WHERE YEAR(NOW()) - YEAR(birthday) > 40
#Query employees who have been employed for 5 years
SELECT * FROM t_employee
WHERE YEAR(NOW()) - YEAR(hiredate) > 5;
SELECT * FROM t_employee WHERE (DATEDIFF(CURRENT_DATE(),hiredate) DIV 365) >=5;
#Calculate the current date. What's the date after 130 days
SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 130 DAY)
#Calculate the current date. What's the date 45 days ago
SELECT DATE_ADD(CURRENT_DATE(),INTERVAL -45 DAY)
/*
In addition to JAVA, you can convert string to date time or date time to string,
It can also be used in mysql
*/
SELECT DATE_FORMAT(NOW(),'%y year%c month%e day');
SELECT STR_TO_DATE('19 January 18th 2013','%y year%c month%e day')
4. Encryption function
/*
Encryption function:
password(x)
md5(x)
*/
INSERT INTO t_user VALUES(2,'lin',PASSWORD('123456'));
INSERT INTO t_user VALUES(3,'yan',MD5('123456'));
SELECT * FROM t_user WHERE username='lin' AND `password` = PASSWORD('123456');
Process control
/*
In Java, there are if..else,switch...case and other process control statement structures
mysql There are corresponding functions in
(1)ifnull(x,value): If x is null, value is used, otherwise x is used
(2)CASE
WHEN Condition 1 THEN result1
WHEN Condition 2 THEN result2
...
[ELSE resultn]
END
*/
#Query employee's name, salary, bonus proportion and paid salary
#Paid salary = salary + salary * bonus ratio
SELECT ename,salary,commission_pct, salary + salary * IFNULL(commission_pct,0) AS "Real wages" FROM t_employee;
/*Query employee information,
If the salary is higher than 20000, it shows that the employee is "rich and handsome",
Display "potential stock" if salary is between 15000-20000
If the salary is between 10000-15000, "promising youth" is displayed
If the salary is below 10000, display "loser"“
Equivalent to if...else if
*/
SELECT ename, salary,
CASE
WHEN salary>=20000 THEN "Tall, rich and handsome"
WHEN salary>=15000 THEN "Potential share"
WHEN salary>=10000 THEN "Promising youth"
ELSE "Loser"
END AS "Label"
FROM t_employee;
#Query order table, display order number, and order status. If order status is 0, display new order, yes 1, display paid
/*
Equivalent to switch...case
*/
SELECT oid ,price,
CASE state
WHEN 0 THEN "New order"
WHEN 1 THEN "Paid"
WHEN 2 THEN "Shipped"
WHEN 3 THEN "Received goods"
END
FROM t_order;
Four. Affairs
/*
Transaction: indicates a set of operations (sql) that either succeed or fail at the same time. Then this operation constitutes a transaction.
For example:
Zhang San transfers 500 yuan to Li Si
(1)Reduce Zhang San's balance by 500
...
(2)Increase Li Si's balance by 500
Not allowed: Zhang San's money was reduced by 500, while Li Si's money was not increased.
The transaction needs to be opened before (1). If it succeeds at the same time, the transaction will be committed. Otherwise, the previous state will be rolled back (restored to (1)).
Statements involved:
(1)Statement to open a transaction
start transaction;
(2)Commit transaction / rollback transaction
commit;/rollback;
mysql By default, the transaction mode is auto commit, that is, execute one sentence and auto commit one sentence. Once committed, it cannot be rolled back.
If you want to start the manual submission mode, you can complete it by the following statement: set autocommit = false;
(1)Manual commit mode
If the manual commit mode is started, a set of sql statements will not end until commit or rollback,
From here, commit/rollback to the next commit or rollback is another transaction.
Each set of operations is manually committed or rolled back.
(2)Auto commit mode, and then a separate set of operations. If you want to start manual commit mode, you can use
start transaction; #It can only be used on the command line, not on the Java generation of JDBC.
commit;/rollback;
*/
#Turn on the manual submission mode, the scope of action is one connection (from login to exit)
SET autocommit = FALSE;
DELETE FROM t_department WHERE did > 5;
INSERT INTO t_department VALUES(NULL,'yy','yyyy');
ROLLBACK;
#COMMIT;
SET autocommit = TRUE;
DELETE FROM t_department WHERE did > 5;
START TRANSACTION;
INSERT INTO t_department VALUES(NULL,'yy','yyyy');
UPDATE t_department SET dname = 'Logistics Service Department' WHERE did = 5;
COMMIT;
/*
Interview question: what are the characteristics of the business?
ACID:
(1)Atomicity: the partition of sql statements of transactions must be as small as possible.
Whether this set of operations really requires success or failure at the same time.
(2)Consistency: ensure data consistency before and after transactions
For example: Zhang three original balance: 500, Li Si original balance: 2000, Zhang San to Li Sizhuan 500
Uniformity:
Before business: Zhang San original balance: 500, Li Si original balance: 2000
After transaction: if it fails, Zhang San's balance: 500, Li Si's original balance: 2000
If successful, Zhang San's balance: 0, Li Si's original balance: 2500
(3)Isolation:
The two transactions are independent.
For example: Zhang San gives Li sizhuan 500
Li Si to Wang Wu for 1000
Zhao Liuzhi to Li sizhuan 800
These three transactions are independent. The success and failure of Zhang San's transfer to Li Si have nothing to do with the other two operations.
(4)Persistence: once submitted, it is determined.
mysql Only Innodb engine in supports transactions.
The transaction is valid only for DML statements and not for DDL statements.
*/
/*
Isolation level of transaction:
1. Why quarantine?
To ensure the independence of affairs.
But in many cases, transactions interact with each other. The modification of two transactions to the same record of the same table affects each other.
In Java, it's thread safety. In mysql, this problem is also a thread safety problem.
Then it shows the problem phenomenon:
(1) Dirty reading
One transaction reads the uncommitted data of another
(2) Non repeatable reading
One transaction read another transaction "modify" the submitted data, resulting in two reads of the same data during a transaction, with inconsistent results.
(3) Illusory reading
One transaction read the newly added and committed data of another transaction, resulting in different records during a transaction.
One transaction read the data deleted and committed by another, resulting in different records during a transaction.
The default isolation level of mysql is: (3)
(1) READ-UNCOMMITTED: read uncommitted data
(2) READ-COMMITTED: read submitted data
(3) REPEATABLE-READ: repeatable
(4) SERIALIZABLE: SERIALIZABLE
serializable
View the isolation level of the current connection: select@ @ TX \ isolation;
Modify the isolation level of the current connection: set TX ﹣ isolation ='read-uncommitted ';
If the isolation level of the current connection is READ-UNCOMMITTED, you will read uncommitted data, which is called dirty read.
If "dirty read" is avoided, the isolation level is increased to READ-COMMITTED or above.
If the isolation level of the current connection is READ-UNCOMMITTED and READ-COMMITTED, there will be non repeatable phenomena,
If you want to avoid "non repeatable reading", you should increase the isolation level to REPEATABLE-READ or above.
(3) REPEATABLE-READ and (4) SERIALIZABLE can avoid dirty reading, non repeatable reading and unreal reading. What's the difference?
REPEATABLE-READ: Line
SERIALIZABLE: table
*/
5, Rights management
/*
mysql: permission verification is divided into two stages
(1) Can I connect
The authentication of mysql users is through the host address + user name + password
If the host address is% written, it means any IP can access it
If the host address is written as localhost, it can only be accessed locally and with localhost/127.0.0.1
(2) Verify permissions
A: Global permissions, permissions for all libraries, all tables, and all fields
If an operation has global permission, the object level permission will not be judged
B: Object level permissions
Library > Table > Field
Permissions are set separately for each operation.
*/