1. Group query

group by ， generally used in conjunction with aggregate functions, the found data is meaningful
*Fields to query:
1. Grouping field itself
2. Aggregate function

-- For example, I group by part number. For example, there are three departments, and then I calculate the average salary of each department. There should be three data displayed
-- General aggregation functions are used in combination with grouping

SELECT deptno AS Department number,AVG(sal) AS average wage FROM emp GROUP BY deptno;


-- How many people are there in each department

SELECT deptno AS Department number,COUNT(*) AS Number of departments FROM emp GROUP BY deptno;


-- Find a reasonable field to group according to sex 

-- According to the salary level, it is divided into a group greater than 1500 and a group less than 1500

SELECT COUNT(*) AS Number of people FROM emp GROUP BY sal>=1500;


-- 1.For example, query the department number of each department and the number of people whose salary is greater than 1500 in each department

-- Before grouping, we also conduct a conditional screening. Those who meet the conditions participate in the grouping and those who do not meet the conditions do not participate in the grouping

SELECT deptno AS Department number,COUNT(*) AS Number of departments FROM emp WHERE sal>1500 GROUP BY deptno;

-- You can also sort after grouping
SELECT deptno AS Department number,COUNT(*) AS Number of departments FROM emp WHERE sal>1500 GROUP BY deptno ORDER BY Number of departments;


-- 2.For example, I want to query the departments whose average salary is greater than 2000
-- We need to filter the result set generated after grouping again having

SELECT deptno AS Department number,AVG(sal) AS average wage FROM emp GROUP BY deptno HAVING average wage>2000 ORDER BY average wage;


-- 3. For example, I want to query the departments where the average salary of employees in each department is greater than 1500 and the average salary is greater than 2000

-- Before grouping, you can also filter the conditions. If the employee's salary is greater than 1500, you can participate in the grouping. After grouping, you should also filter the departments whose average salary is greater than 2000

SELECT deptno AS Department number,AVG(sal) AS average wage FROM emp WHERE sal>1500 GROUP BY deptno HAVING average wage>2000;


-- having and where What's the difference?
-- * where: Qualify conditions before grouping. If the conditions are not met, they will not participate in the grouping
-- * having: Filtering the result set after grouping

2. Paging query

limit
* the index of records starting from limit 0,5, and the number of entries displayed on each page starts from 0
Start record index = (page number - 1) * number of entries displayed on each page

-- paging LIMIT

SELECT * FROM emp;

-- Query the data on the first page and display 3 items on each page
SELECT * FROM emp LIMIT 0,3;


-- Query the data on the second page and display 3 items on each page

SELECT * FROM emp LIMIT 3,3;


-- Query the data on the third page and display 3 items on each page

SELECT * FROM emp LIMIT 6,3;

-- Query the data on the fourth page and display 3 items on each page

SELECT * FROM emp LIMIT 9,3;

-- Query the data on the fifth page and display 3 items on each page

SELECT * FROM emp LIMIT 12,3;


Start index=(Page number-1)*Number of entries per page


-- Query the data on the first page and display 3 items on each page
SELECT * FROM emp WHERE sal>1500 LIMIT 0,3;

SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC LIMIT 0,3;

3. Subquery and define variables

--Query the name of the employee with the highest salary

SELECT ename,MAX(sal) FROM emp; -- This is wrong. There are no conditions

--Query with sub query

SELECT ename,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

--Use a variable to save the maximum salary. Pay attention to the syntax of assignment. Use: = to assign the value
SELECT ename,sal,@gz:=MAX(sal) AS maximum wage FROM emp;
--View the value of a variable
select @gz;

-- Query maximum wage
SELECT MAX(sal) FROM emp;


-- Query the name of the employee with the highest salary

-- Use subquery:The condition value of a main query comes from another sub query

SELECT ename,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

-- Mode 2:

SELECT ename,sal,MAX(sal) FROM emp WHERE sal=5000;

-- Define a variable, save the result of the aggregate function, and use this variable as a condition


SELECT ename,sal,@gz:=MAX(sal) AS Maximum wage FROM emp;

SELECT @gz;



-- Use of variables

-- Use a variable to save the maximum salary. Pay attention to the syntax of assignment := To assign a value
SELECT @gz:=MAX(sal) AS Maximum wage FROM emp;
-- View the value of a variable
SELECT @gz;

SELECT * FROM emp WHERE sal=@gz;

4.case when then end statement

-- -- Give different salaries to employees in different positions and use case when then end Statement to complete


SELECT ename,job,sal AS Original salary,

CASE job

WHEN 'PRESIDENT' THEN sal+1000
WHEN 'MANAGER' THEN sal+800
ELSE sal+300
END AS Salary increase
FROM emp;

5. Constraints

effect:
In order to ensure the validity and integrity of data

Common constraints in mysql:
Primary key constraint
Self growth constraint Auto_ Increment is added to the integer field and used with the primary key constraint
Unique constraint
Non NULL constraint (not null)
Foreign key constraint
sex ENUM('male ',' female ') -- setting the data type of a field to enumeration type can also play a constraint effect
Nonnegative constraint UNSIGNED
For example, the range that this data type TINYINT can represent is - 128 --- 127
I added this constraint. The range of TINYINT UNSIGNED is 0-255, that is, there is no negative value

6. Primary key constraint

Primary key constraint: the modified field is unique and non empty
Note: a table can only have one primary key, which can contain multiple fields
Suggestion: we also strongly recommend that you have a primary key when creating a table. Generally, it is recommended to give an id integer field as the primary key when creating a table,

Delete primary key constraint: there are two cases
Case 1: this field has only primary key constraints. Delete the primary key constraints in two steps
Step 1: alter table name drop primary key; -- in this way, only the unique constraint is deleted. It also has a non empty constraint, so we have to delete the non empty constraint again
Step 2: alter table [table name] modify [column name] varchar(20) null-- Modify the field name and add null to the original field
Case 2: this field is an int type field with both primary key constraints and self growth constraints. You have to delete the self growth constraint first before deleting the primary key constraint
Step 1: to delete the self growth constraint is to change the self growth field name and data type to the original field name and type
alter table name change field name field name field name data type-- Delete self growth constraint
Step 2: delete the primary key constraint
alter table name drop primary key;
Step 3: delete non empty constraints
                ALTER TABLE test3 MODIFY sid INT NULL; -- the modified field value can be null

-- Syntax for adding primary key constraints
-- Method 1: when creating a table, add a primary key constraint to a field
CREATE TABLE test(
	id INT, 
	username VARCHAR(20) PRIMARY KEY  -- Add a primary key constraint to this field
);


INSERT INTO test VALUES(1,'tom');-- success

INSERT INTO test VALUES(2,'tom');-- fail

INSERT INTO test VALUES(2,'jery');-- success

INSERT INTO test VALUES(2,NULL); -- fail



-- Method 2: when creating a table, add a primary key constraint to a field
CREATE TABLE test2(
	id INT,
	username VARCHAR(20),
	PRIMARY KEY(id) -- Specify the primary key field here
);



INSERT INTO test2 VALUES(1,'tom');-- success

INSERT INTO test2 VALUES(1,'tom2');-- fail




-- Method 3: after the table is created, add the primary key constraint
CREATE TABLE test3(
	id INT,
	username VARCHAR(20)
	
);

-- The primary key is added by modifying the table

ALTER TABLE test3 ADD PRIMARY KEY(username);



INSERT INTO test3 VALUES(1,'tom');-- success

INSERT INTO test3 VALUES(2,'tom');-- fail


-- Joint primary key: add primary key constraints by taking multiple fields as a whole

CREATE TABLE test4(
	id INT,
	username VARCHAR(20)
	
);
-- hold id and  username As a whole, add a federated primary key
ALTER TABLE test4 ADD PRIMARY KEY(id,username);


INSERT INTO test4 VALUES(1,'tom');-- success

INSERT INTO test4 VALUES(1,'jery');-- success

INSERT INTO test4 VALUES(1,NULL);-- fail


INSERT INTO test4 VALUES(NULL,'tom');-- fail


-- We also strongly recommend that you have a primary key when creating a table. Generally, it is recommended to give a primary key when creating a table id Integer field, put this id Field as the primary key,

7. Unique constraints

--Unique constraint feature: values cannot be repeated
--Note: the unique constraint has no effect on null values.

CREATE TABLE test5(
	id INT,
	username VARCHAR(20) UNIQUE -- Add unique constraint
	
);


INSERT INTO test5 VALUES(1,'tom');-- success



CREATE TABLE test6(
	id INT,
	username VARCHAR(20),
	UNIQUE(id) -- Unique constraint
	
);

INSERT INTO test6 VALUES(1,'tom');-- success

INSERT INTO test6 VALUES(1,'jery');


CREATE TABLE test7(
	id INT,
	username VARCHAR(20)
	
	
);

ALTER TABLE test7 ADD UNIQUE(username);

INSERT INTO test7 VALUES(1,'tom');-- success

INSERT INTO test7 VALUES(1,NULL);-- success


INSERT INTO test7 VALUES(1,NULL);-- success

8. Non NULL constraint

Features: the modified field is not empty

CREATE TABLE test8(
	id INT,
	username VARCHAR(20) NOT NULL  -- Non NULL constraint
	
);


INSERT INTO test8 VALUES(1,NULL);-- success

-- UNIQUE NOT NULL, When combined, it can add to multiple fields in the table, but it is not the same as the primary key constraint. There can only be one primary key constraint in a table.
CREATE TABLE test9(
	id INT UNIQUE NOT NULL,
	username VARCHAR(20) UNIQUE NOT NULL  -- Unique non NULL constraint
	
);

9. Enumeration type

--Enumeration types can act as constraints

CREATE TABLE test91 (
  username VARCHAR (20) PRIMARY KEY,  
   age INT UNIQUE NOT NULL, -- Unique constraint
   sex ENUM('male','female')  -- The data type of a field is an enumeration type, which can serve as a constraint
);




INSERT INTO test91 VALUES('aaa',10,'male');

INSERT INTO test91 VALUES('bbb',20,'female');

INSERT INTO test91 VALUES('ccc',30,'Demon');

10. Nonnegative constraints

UNSIGNED

INSERT INTO test92 VALUES('ccc',-1);

CREATE TABLE test93 (
  username VARCHAR (20) PRIMARY KEY,  
   age TINYINT UNSIGNED -- Nonnegative constraint 

);

TINYINT 1 Bytes  -128---127
TINYINT UNSIGNED  0---255
 

INSERT INTO test93 VALUES('ccc',1);

INSERT INTO test93 VALUES('ddd',255);

11. Self growth constraints

--Self growth constraint: generally used in integer type fields, together with primary key constraints

--In the future, we will design an id field for each table, which is set as the primary key self growth

CREATE TABLE test94 (
   id INT PRIMARY KEY AUTO_INCREMENT,   -- Primary key self growth
  username VARCHAR (20),
   age TINYINT UNSIGNED -- Nonnegative constraint 

);


INSERT INTO test94(username,age) VALUES('ddd',255);

INSERT INTO test94(username,age) VALUES('bbb',255);

12. Relationship between entities

--Entity: correspond the real things with the table

Student: ------------ student form
         
Student No.: sid
        
Name: sname
Sex = sex
age
address
        
--class Student in java - student table

Student No.: sid
        
Name: sname
Sex = sex
age
address
        
        
Table: it can be regarded as an entity
  
  
There are some corresponding relationships between entities
  
One to one: husband and wife relationship, one person can only have one ID number.
One to many: user table # order table # one user can correspond to multiple orders.
Many to many: teachers and students. A teacher can teach many students, and a student can also be taught by multiple teachers
  
One order can contain multiple goods, and one goods can also belong to multiple orders
           
           
Mall project:
Which entities are analyzed: users, goods orders
    
class USER ----- user table
class ShangPin -- commodity list
class orders - order form

13.ER diagram

ER diagram can describe the relationship between entities
Entities: represented by rectangles
Attributes: represented by ellipses
Relationship: represented by diamond

ER diagram
There are four components in the ER diagram:
Rectangular box: represents an entity, and the entity name is recorded in the box.
Diamond box: indicates contact, and enter the contact name in the box.
Oval box: indicates the attribute of the entity or contact. Record the attribute name in the box. For the main attribute name, underline it.
Connection: between entities and attributes; Between entities and linkages; The connection and attribute are connected by a straight line, and the type of connection is marked on the straight line.
(for one-to-one connection, write 1 in the connection direction of two entities
For one to many connections, write 1 on one side and N on the other side; For many to many relationships, write N,M in the direction of the connection between the two entities.)

14. Foreign key constraints

-- foreign key constraint: to ensure the validity and integrity of data.
    
    
-- the purpose of adding foreign key constraints is to ensure the validity and integrity of data.
-- we add a foreign key constraint on the multi table side to associate the primary key of the main table side.
-- after adding foreign key constraints, it has the following characteristics
    -- 1. One side of the main table cannot be deleted, and one side of multiple tables is still referencing data
    -- 2. One side of multiple tables cannot be added. There is no data described in the main table.

-- Create user table
	CREATE  TABLE USER(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20)
	);
	
	-- Create order table
	CREATE  TABLE orders(
		id INT PRIMARY KEY AUTO_INCREMENT,
		totalprice DOUBLE,
		user_id INT   -- This field means which user the order belongs to, that is, the same field makes the table correspond to the table before it
	);

 -- Adding foreign key constraints by modifying tables
    -- alter table Multi table name add foreign key(Foreign key name) references First table name(Primary key);
    
   ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);
   
   
   
   CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,  -- Primary key
    zname VARCHAR(20)

);

CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT, -- Foreign key
 -- Method 2: foreign key constraints are added when creating tables
    FOREIGN KEY(zzid) REFERENCES zhu(zid)
);