1. Basic query
(1). concept
[1]. An important function of database management system is data query. Data query should not only simply return the data stored in the database, but also filter the data according to needs and determine what format the data is displayed.
[2].MySQL provides powerful and flexible statements to realize these operations
[3].MySQL database uses select statement to query data.
(2). Syntax format
select [all|distinct] <Expression 1 for target column> [alias], <Expression 1 for target column> [alias] ... from <Table name or view name> [alias], <Table name or view name> [alias] ... [where<Conditional expression>] [group by <Listing> [having <Conditional expression>]] [order by <Listing> [asc|desc]] [limit <Number or list>]
Simplified syntax:
select *| Listing from surface where condition
(3). Examples
[1]. Data preparation
[a]. Create databases and tables
-- Create databases and tables create database if not exist mydb2; use mydb2; -- Create item table: create table product( pid int primary key auto_increment, -- Item number pname varchar(20) not null, -- Commodity name price double, -- commodity price category_id varchar(20) -- Commodity classification );
[b]. Add data
insert into product values(null, 'Haier washing machine', 5000, 'c001'); insert into product values(null, 'Midea refrigerator', 3000, 'c001'); insert into product values(null, 'Gree air conditioner', 5000, 'c001'); insert into product values(null, 'Jiuyang rice cooker', 5000, 'c001'); insert into product values(null, 'Woodpecker shirt', 300, 'c002'); insert into product values(null, 'Hengyuanxiang trousers', 800, 'c002'); insert into product values(null, 'Playboy jacket', 440, 'c002'); insert into product values(null, 'Jinba casual pants', 266, 'c002'); insert into product values(null, 'Hailan home sweater', 180, 'c002'); insert into product values(null, 'Jack Jones Sweatpants', 430, 'c002'); insert into product values(null, 'Lancome face cream', 300, 'c003'); insert into product values(null, 'Estee Lauder essence water', 200, 'c003'); insert into product values(null, 'Chanel perfume', 350, 'c003'); insert into product values(null, 'SK-II GHb ', 350, 'c003'); insert into product values(null, 'Shiseido liquid foundation', 180, 'c003'); insert into product values(null, 'Old Beijing instant noodles', 56, 'c004'); insert into product values(null, 'Liangpin shop kelp silk', 17, 'c004'); insert into product values(null, 'Three squirrel nuts', 88, null);
[2]. Simple query
-- 1.Query all products select * from product; -- 2.Query commodity name and price select pname, price from product; -- 3.The keyword used in alias query is as (as Can be omitted) -- 3.1 Table alias select * from product as p; -- 3.2 Column alias select pname as pn from product; -- 4.Remove duplicate values select distinct price from product; -- 5.The query result is an expression(Operation query):Price of all goods+10 element select pname, price+10 from product;
2. Operator
(1). brief introduction
After the table structure in the database is established, the meaning of the data represented in the table has been determined. Through the operation of MySQL operator, you can obtain another kind of data other than the table structure.
For example, there is a birth field in the student table, which indicates the year of birth of the student. Using MySQL operator to subtract the student's year of birth from the current year will get the student's actual age data
(2).MySQL supports four operators
[1]. Arithmetic operator
[2]. Comparison operator
[3]. Logical operator
[4]. Bitwise Operators
(3). Arithmetic operator
[1]. classification
Arithmetic operator | explain |
---|---|
+ | Addition operation |
- | Subtraction operation |
* | Multiplication |
/Or DIV | Division, return quotient |
%Or MOD | The remainder operation returns the remainder |
[2]. Examples
select 6 + 2; select 6 - 2; select 6 * 2; select 6 / 2; select 6 % 2; -- Add 10 to the price of each item select name, price + 10 as new_price from product; -- Raise the price of all goods by 10% select pname, price * 1.1 as new_price from product;
(4). Comparison operator
[1]. classification
Comparison operator | explain |
---|---|
= | be equal to |
< and<= | Less than and less than or equal to |
>And >= | Greater than and equal to |
<=> | When both opcodes are NULL, the obtained value is 1; When an opcode is NULL, the resulting value is 0 |
< > or= | Not equal to |
IS NULL or ISNULL | Determine whether a value is NULL |
IS NOT NULL | Determine whether a value is not NULL |
LEAST | Returns the minimum value when there are two or more parameters |
GAEATEST | Returns the maximum value when there are two or more parameters |
BETWEEN AND | Judge whether a value falls between two values |
IN | Judge whether a value is any value IN the IN list |
NOT IN | Judge whether a value is not any value IN the IN list |
LIKE | Wildcard matching |
REGEXP | Regular Expression Matching |
[2]. Examples
-- The query product name is"Haier washing machine"All information about the product select * from product where pname = 'Haier washing machine'; -- Inquire about goods with a price of 800 select * from product where price = 800; -- Query all items whose price is not 800 select * from product where price != 800; select * from product where price <> 800; select * from product where not(price = 800); -- Query all commodity information with commodity price greater than 60 yuan select * from product where price > 60 -- Query all products with commodity prices between 200 and 1000 select * from product where price >= 200 and price <= 1000; select * from product where price between 200 and 1000; -- Query all items with a price of 200 or 800 select * from product where price = 200 or price = 800; select * from product where price in (200, 800); -- Query contains'shoes'All goods of the word select * from product where pname like '%shoes%'; -- Query to'sea'All items at the beginning select * from product where pname like 'sea%'; -- The second word of query is'Cardamom'All goods select * from product where pname like '_Cardamom%'; -- query category_id by null Commodity select * from product where category_id is null; -- query category_id Not for null Commodity select * from product where category_id is not null; -- use least Find the minimum value select least(10, 20, 30); -- 10 select least(10, null, 30); -- null -- use greatest Find the maximum value select greatest(10, 20, 30); -- 30 select greatest(10, null, 30); -- null
(5). Logical operator
[1]. classification
Logical operator | explain |
---|---|
NOT or| | Logical non |
AND or&& | Logic and |
OR or|| | Logical or |
XOR | Logical XOR |
(6). Bit operation
[1]. classification
Bitwise Operators | explain |
---|---|
| | Bitwise OR |
& | Bitwise AND |
^ | Bitwise XOR |
<< | Shift left by bit |
>> | Shift right by bit |
~ | Invert by bit, inverting all bits |
Note: bitwise operators are operators that evaluate on binary numbers.
[2]. Examples
select 3&5; -- Bit and select 3|5; -- Bit or select 3^5; -- Bit exclusive or select 3>>1; -- Bit shift left select 3<<1; -- sftr select ~3; -- Bit inversion
3. Sort query
(1). concept
If we need to sort the read data, we can use the order by clause of MySQL to set the sort method of the field you want to sort, and then return the search results.
(2). grammar
select Field name 1, Field name 2, ... from Table name order by Field name 1 [asc|desc], Field name 2 [asc|desc] ...
(3). characteristic
[1].asc stands for ascending order and desc for descending order. If you don't write, the default ascending order is.
[2].order by is used in clauses. It can support single fields, multiple fields, expressions, functions and aliases
[3]. The order by clause is placed at the end of the query statement, except the LIMIT clause
(4). Examples
-- 1.Use price order(Descending order) select * from product order by price desc; -- 2.Sort in price(Descending order)On the basis of classification(Ascending order) select * from product order by price desc, category asc; -- 3.Displays the price of the item(To repeat),And sort(Descending order) select distinct price from product order by price desc;
4. Aggregate query
(1). concept
The previous queries are horizontal queries, which are judged according to the conditions row by row, while the query using aggregate function is vertical query, which is calculated by the value of a column, and then returns a single value; In addition, the aggregate function ignores null values.
(2). Aggregate function
Aggregate function | effect |
---|---|
count() | Counts the number of record rows whose specified column is not NULL |
sum() | Calculates the numerical sum of the specified column. If the specified column type is not a numerical type, the calculation result is 0 |
max() | Calculate the maximum value of the specified column. If the specified column is of string type, the string sorting operation is used; |
min() | Calculate the minimum value of the specified column. If the specified column is of string type, the string sorting operation is used; |
avg() | Calculates the average value of the specified column. If the specified column type is not a numeric type, the calculation result is 0 |
(3). Examples
-- 1.Total number of items queried select count(pid) from product; select count(*) from product; -- 2.Query the total number of goods with a price greater than 200 select count(pid) from product where price > 200; -- 3.Query classification is'c001'The sum of all commodity prices select sum(price) from product where category_id = 'c001'; -- 4.Query the maximum price of goods select max(price) from product; -- 5.Query the minimum price of goods select min(price) from product; -- 6.Query classification is'c002'Average price of all goods select avg(price) from product where category_id = 'c002';
(4).Null value processing
[1]. brief introduction
[a]. Processing of null value by count function:
If the parameter of the count function is an asterisk (*), the number of all records will be counted. If the parameter is a field, the number of records with null value is not counted.
[b] Processing of null value by sum and avg functions
These two functions ignore the existence of null value as if the record did not exist.
[c]. Processing of null value by Max and min functions
The max and min functions also ignore the existence of null values
[2]. Examples
-- Create table create table test_null( c1 varchar(20), c2 int ); -- insert data insert into test_null values('aaa', 3); insert into test_null values('bbb', 3); insert into test_null values('ccc', null); insert into test_null values('ddd', 6); -- test select count(*), count(1), count(c2) from test_null; select sum(c2), max(c2), min(c2), avg(c2) from test_null;
5. Group query
(1). brief introduction
Grouping query refers to grouping query information using the group by clause
(2). format
select Field 1, Field 2 ... from Table name group by Grouping field having Grouping conditions;
(3). Examples
-- 1.Count the number of commodities in each category select category_id, count(*) from product group by category_id;
(4). Conditional filtering after grouping - having
[1]. If the statistical results are filtered after grouping, you must use having instead of where
[2]. The where clause is used to filter the rows generated by the operation specified in the FROM clause
[3]. The group by clause is used to group the output of the WHERE clause
[4]. The having clause is used to filter rows from the grouping results.
-- Count the number of commodities in each category, and only display the information with the number greater than 4 select category_id, count(*) from product group by category_id having count(*) > 4;
6. Paging query
(1). brief introduction
Paging query is common in projects. Due to the large amount of data and the limited length of the display screen, the data is displayed in paging. For example, there are 30 data items in total, 5 items are displayed on each page, 15 items are displayed on the first page and 610 items are displayed on the second page
(2). format
-- Mode 1 - Before display n strip select Field 1, Field 2 ... from Table name limit n -- Mode 2 - Pagination display select Field 1, Field 2 ... from Table name limit m, n parameter: m: integer, Indicates the index from which to calculate(Current page - 1) * Number of items displayed per page n: integer, Indicates how many pieces of data to query
(3). Examples
-- query product The first five records in the table select * from product limit 5 -- Display from 4 to 5 select * from product limit 3,5
7.insert into select statement
(1). brief introduction
To import data from one table into another, you can use the insert into select statement
(2). format
insert into table2(field1, field2, ...) select value1, value2, ... from table1; perhaps insert into table2 select * from table1;
8. Examples
(1). Case 1
[1]. Data preparation
-- Create database create database mydb2; use mydb2; -- Create table create table student( id int, name varchar(20), gender varchar(20), chinese int, english int, math int ); -- insert data insert into student(id, name, gender, chinese, english, math) values(1, 'Zhang Ming', 'male', 89, 78, 90); insert into student(id, name, gender, chinese, english, math) values(2, 'Li Jin', 'male', 67, 53, 95); insert into student(id, name, gender, chinese, english, math) values(3, 'Wang Wu', 'female', 87, 78, 77); insert into student(id, name, gender, chinese, english, math) values(4, 'Li Yi', 'female', 88, 98, 92); insert into student(id, name, gender, chinese, english, math) values(5, 'Li Cai', 'male', 82, 84, 67); insert into student(id, name, gender, chinese, english, math) values(6, 'Zhang Bao', 'male', 55, 85, 45); insert into student(id, name, gender, chinese, english, math) values(7, 'Huang Rong', 'female', 75. 65, 30); insert into student(id, name, gender, chinese, english, math) values(7, 'Huang Rong', 'female', 75, 65, 30);
[2]. query
-- Query the information of all students in the table select * from student; -- Query all students in the table and their corresponding English scores select name, english from student; -- Filter duplicate data in the table select distinct * from student; -- Count the total score of each student select name, (chinese + english + math) as total_score from student; -- Add 10 points to the total score of all students and 10 points for special skills select name, (chinese + english + math + 10) as total_score from student; -- Use alias to represent student scores select name, chinese 'grade scores of Chinese', english 'English achievement', math 'Mathematics achievement' from student -- Query students with English scores greater than 90 select * from student where english > 90; -- Query all students whose total score is greater than 200 select * from student where (chinese + english + math) > 200; -- Query English score at 80~90 Students between select * from student where english between 80 and 90; perhaps select * from student where english >=80 and english <= 90; -- Query English score is not 80~90 Between classmates select * from student where not (english between 80 and 90); perhaps select * from student where english not between 80 and 90; perhaps select * from student where not (english >=80 and english <= 90); perhaps select * from student where english < 80 or english >90; -- The query math score is 89, 90, 91 My classmate select * from student where math in (89, 90, 91); -- Query math score is 89, 90, 91 My classmate select * from student where math not in (89, 90, 91); -- Check the English scores of all students surnamed Li select name, english from student where name like 'Lee%'; -- Query the students who are divided into 80 in mathematics and 80 in Chinese select * from student where math = 80 and chinese = 80; -- Query English 80 or 200 students select * from student where math = 80 or (chinese + math + english) = 200; -- Sort and output the math scores in descending order select * from student order by math desc; -- Output after sorting the total score, and then output from high to low select * from student order by (chinese + math + english) desc; -- Sort and output the total score of students surnamed Li select * from student where name like 'Lee%' order by (chinese + math + english) desc; -- Query the number of boys and girls respectively, sort and output the number in descending order, and query the gender number information with the number greater than 4 select gender, count(*) as total_cnt from student group by gender having total_cnt >4 order by total_cnt desc;
(2). Case 2
[1]. Prepare data
create table emp( empno INT, -- Employee number ename varchar(50), -- Employee name job varchar(50), -- Job name mgr INT, -- Superior leader No hiredate DATE, -- Entry date sal INT, -- salary comm INT, -- bonus deptno INT -- Department number ); INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2450, NULL, 10); INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 3000, NULL, 20); INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 5000, NULL, 20); INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 2000, NULL, 10); INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-19-08', 1500, 0, 30); INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
[2]. Sort the information of employees who do not work in department 10 in ascending order of employee number
select * from emp where deptno != 10 order by empno asc;
[3]. Query the information of employees whose second letter of name is not 'A' and whose salary is greater than 1000 yuan, in descending order of annual salary
-- Annual salary:12*a monthly salary + bonus select * from emp where ename not like '_A%' and sal > 1000 order by(12*sal + ifnull(comm, 0)) desc;
[4]. Find the average salary of each department
select deptno, avg(sal) as avg_sal from emp group by deptno;
[5]. Ask for the highest salary in each department
select deptno, max(sal) as max_sal from emp group by deptno;
[6]. Ask for the highest salary for each position in each department
select deptno, job, max(sal) as max_sal from emp group by deptno, job;
[7]. Find the department number with average salary greater than 2000
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000;
[8]. The average salary of the Department is listed in descending order, and the average salary of the Department is greater than 1500
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000 order by avg_sal desc;
[9]. Select the name and salary of employees with bonus in the company
select * from emp where comm is not null;
[10]. Query the gap between the maximum wage and the minimum wage of employees
select max(sal) - min(sal) as 'Pay gap' from emp;