Foreword: Today I will tell you: MySQL database table fuzzy/multiline/grouping/sorting/paging query and MySQL data type explanation, of course, if you are interested in the basic operation of the database--database creation/table addition/deletion check, you can see my article-- Basic operations for MySQL database tables (add, delete, change check) - Explanation 1.
5. Query table records
Import the database into mysql
Save it in a.Sql document, make sure there is no Chinese in the path, then open the database, and use source xxx/db10.sql
Or roughly, copy and paste directly into the database (but very low does not typically)
-- Establish db10 Library, emp Table and insert records -- ----------------------------------- -- delete db10 library(If it exists) drop database if exists db10; -- Re-create db10 library create database db10 charset utf8; -- Choice db10 library use db10; -- Delete employee table(If it exists) drop table if exists emp; -- Create employee table create table emp( id int primary key auto_increment, -- Employee number name varchar(50), -- Employee Name gender char(1), -- Employee gender birthday date, -- Employee Birthday dept varchar(50), -- Departments to which they belong job varchar(50), -- Position sal double, -- salary bonus double -- bonus ); -- Insert records into the employee table insert into emp values(null,'King Fuguo','male','1995-10-25','Marketing Department','Department Director','8400','1200'); insert into emp values(null,'Qi Lei-Feng','male','1994-11-6','Marketing Department','Market Surveyer','5600','800'); insert into emp values(null,'Liu Zhixue','female','1996-09-14','Marketing Department','Marketing instructor','3000','1300'); insert into emp values(null,'Chen Zifeng','male','1991-05-18','Technology Department','Department Director','4200','500'); insert into emp values(null,'Liu Haitao','male','1993-11-18','Technology Department','Developer','6600','500'); insert into emp values(null,'Wang Mingyue','female','1998-07-18','Technology Department','Tester','5400','600'); insert into emp values(null,'Cang Ruo Zhi','male','1995-08-18','Ministry of Personnel','Department Director','4850','800'); insert into emp values(null,'Fan Zhonglei','male','1999-09-18','Ministry of Personnel','Human Resources Teacher','3600','600'); insert into emp values(null,'Liu Ke','male','1990-10-18','Ministry of Personnel','assistant','3200','400'); insert into emp values(null,'Korean Yoga','male','1980-12-18',null,'CEO','11000',null);
5.3. Simulated Query
The Like operator is used to search for a specified pattern in a column in a WHERE clause
It can be used with a wildcard (%) where (%) represents zero or more arbitrary characters."" denotes an arbitrary character.
Syntax: SELECT column | * FROM table name WHERE column name LIKE value
For example:
-- 25.query emp Names in tables contain"Thunder"Word for the employee, showing the employee's name. select * from emp where name like '%Thunder%'; -- 26.query emp Names in the table are as follows"king"Employee at the beginning of the word, showing the employee's name. select * from emp where name like 'king%'; -- 27.query emp Names in the table are"Liu"Employee's name, which starts with a two-word name, displays the employee's name. select * from emp where name like 'Liu_'; select * from emp where name like 'Liu__';
5.4. Multiline Function Query
Multiline functions, also known as aggregation functions, are counted by a column or by all columns.
Common multiline functions are:
- COUNT (column name |*): The number of rows in a column or all columns in a statistical result set.
- MAX: The maximum value in a column value in a statistical result set
- MIN: The smallest value in a column value in a statistical result set
- SUM: The sum of all values in a column of a statistical result set
- AVG (column): The average value of a column in a statistical result set
Tip: Multiline functions cannot be used in where clauses
-- 28.Statistics emp Number of employees with pay greater than 3000 in the table select count(bonus) from emp where sal>3000; -- 29.seek emp Maximum pay in table select max(sal) from emp; -- Minimum Salary select min(sal) from emp; -- 30.Statistics emp Total salary of all employees in the table(No bonus included) select sum(sal) from emp; -- 31.Statistics emp Average salary of table employees(No bonus included) select avg(sal) from emp; select sum(sal) / count( * ) from emp;
5.5, Group Query
The GRUOP BY statement groups result sets based on one or more columns.
We can use COUNT, SUM, AVG, MAX, MIN functions on grouped columns.
Syntax: SELECT column | * FROM table name [WHERE clause] GROUP BY column;
Group employees according to department on emp table to see the effect after grouping.
-- 32.Yes emp Table grouped employees by department to see the effect of grouping. select count(*) from emp; -- The default whole query result is also a set -- Group by Department select * from emp group by dept; -- Count the number of people in each group by Department select count(*) from emp group by dept; -- Number of people in each group grouped by bonus select count(*) from emp group by bonus;
Group emp tables by positions and count the number of people in each position to show positions and their counterparts
-- 33.Yes emp The table is grouped by positions and counts the number of people in each position, showing the positions and their counterparts select * from emp group by job; select job, count(*) from emp group by job;
Group emp tables by department, ask for maximum pay (excluding bonuses) for each department, show department name and maximum pay
-- 34.Yes emp Tables are grouped by department for maximum pay per department(No bonus included)，Show department name and maximum pay -- Group by Department select * from emp group by dept; -- Statistics of maximum pay per department grouped by Department select dept, max(sal) from emp group by dept; -- Grouped by department, the highest salary for each department is counted and the corresponding employee name is displayed ( select dept, max(sal), name from emp group by dept;
Without grouping, the default whole query result is also a group, where aggregation functions (count, sum, avg, max, min) are used to count the number, sum, average, maximum, minimum of the group.
5.6, Sort Query
Use the ORDER BY clause to sort the result set by the specified column before returning
Syntax: SELECT column name FROM table name ORDER BY column name [ASC|DESC]
ASC (default) ascending from low to high;
DESC (self-declared) descending from high to low.
-- 35.Yes emp All employees'salaries in the table are in ascending order(From low to high)Sort, showing employee name, salary. SELECT name,sal FROM emp ORDER BY sal; -- Default is also ascending -- 36.Yes emp Decrease the order of bonuses for all employees in the table(From high to low)Sort, showing employee name, bonus. select name,bonus from emp order by bonus desc; -- Sort by bonus and salary (bonus is in descending order, salary is in ascending order, first by bonus, and then by salary if bonus is the same) select name,bonus,sal from emp order by bonus desc,sal asc;
5.7, Paging Query
In mysql, page-by-page queries are made with limit:
*select * from emp limit (page number-1) Number of records per page, Number of records per page
-- 37.query emp All records in the table, paginated display: 3 records per page, back to page 1. select * from emp limit 0, 3; -- 38.query emp All records in the table, paginated display: 3 records per page, back to page 2. select * from emp limit 3, 3; -- query emp Information on the top three highest paid employees in the table select * from emp order by sal desc limit 0,3;
5.8, Other Functions
Query all employees in the emp table who were born between 1993 and 1995 to show their names and dates of birth.
-- 39.query emp All employees born between 1993 and 1995 in the table, showing their names and dates of birth select name, birthday from emp where birthday between '1993-1-1' and '1995-12-31'; -- or select name, birthday from emp where year(birthday) between 1993 and 1995;
Query all employees in emp table for birthdays this month
-- 40.query emp List all employees who have birthdays this month select * from emp where month( now() ) = month( birthday ); -- Query employees for birthdays next month select * from emp where (month( now() )+1) % 12 = month( birthday ) % 12;
Query the Employee's name and salary in the emp table (salary qualifications: XXX (Yuan))
-- 41.query emp Name and salary of the employee in the form (salary qualifications are: xxx(element) ) select name, concat(sal, '(element)') from emp;
6. Data type of mysql
6.1, Numeric Type
MySQL supports multiple integers, which are largely the same, but store values in different sizes.
- tinyint: takes 1 byte, relative to byte in java
- smallint: takes up 2 bytes, relative to short in java
- Int: takes up 4 bytes, relative to int in java
- bigint: 8 bytes, relative to long in java
There are also floating point types: float and double
- Float:4-byte single-precision floating-point type, relative to float in java
- Double:8 byte double precision floating point type, relative to double in java
6.2, String Type
6.2.1 char(n)
Fixed-length string, up to 255 characters long.n denotes the number of characters, for example:
-- Establish user Table, specifying the user name char Type, no more than 10 characters in length create table user( username char(10), ... --Don't write an ellipsis when you use the code; it's a simple ellipsis );
Fixed length: When the length of the value inserted is less than the specified length, the remaining space is filled with spaces.(This wastes space)
Therefore, the char type is suitable for fixed-length data (such as phone numbers, ID numbers, etc.) so that there is no waste of space and it is more efficient than the varchar type.
6.2.2 varchar(n)
Variable-length string, up to 65535 bytes, n denotes the number of characters, generally more than 255 characters, using the text type
For example:
-- Establish user Table, specifying the user name varchar Type, no longer than 10 create table user( username varchar(10), );
The so-called indefinite length is when the length of the value inserted is less than the specified length, the remaining space can be left for other data to use.(Space saving)
Therefore, varchar types are appropriate for data of variable length, which is less efficient than char types but does not waste space.
6.2.3 text (long text type)
The maximum size is 65535 bytes, and text is typically used for columns with more than 255 characters.For example:
-- Establish user Table: create table user( resume text, );
There are also a variety of texts, with bigtext storing data of approximately 4 GB in length.
char(n), varchar(n), text can all represent string types, the difference is:
(1)char(n) When saving data, if the length of the string stored is less than the specified length n, the space will be used to complete it later, which may result in a waste of space, but char types store faster than varchar and text.
Therefore, the char type is suitable for storing fixed-length data so that there is no space waste and storage efficiency is faster than both!
(2) When varchar (n) saves data, it stores the data according to its true length. The remaining space can be reserved for other data, so varchar does not waste space.
Therefore, varchar is suitable for storing data of variable length so that there is no waste of space.
(3)text is a large text type. Generally, if the text length exceeds 255 characters, it will be stored using the text type.
6.3, Date type (Understanding)
date: year, month, day
time: hours and seconds
datetime: year, month, day, hour, second
Timestamp: The timestamp (actually stored as a time millisecond value) is in the same format as the datetime storage date.The difference is:
(1)timestamp represents 2038 at its maximum, while datetime ranges from 1000 to 9999
(2)timestamp can be automatically updated to the current system time when inserting or modifying data
createtime datetime 2019-12-27 14:27:35
createtime timestamp 2019-12-27 14:27:35
| id | username | password | createtime |
|---|---|---|---|
| 1 | xxh | 123456 | 2019-12-27 14:27:35 |
There will be a third explanation, which will cover mysql's field (column) constraints, and so on.