MySQL Basics and Exercises (5)

1. Ask for a class math average score.
(1). Select sum (Math)/count (math) as math average score
from student;

(2). Select AVG (math) as math average
from student;

(3). Select AVG (name) as Xiao Ming's average score
From student;//0

2. Ask for the average score of a class total.
(1). Select (Sum (Chinese) +sum (math) +sum (English))/count (*)
from student;

(2). Select AVG (chinese+math+english)
from student;

3. Ask for the highest score and the lowest score of the class language.
Select MAX (name), min (name)
from student;

Drop table if exists teacher;
CREATE TABLE Teacher (
ID int,
Name varchar (20),
Birthday Date
);

Insert into teacher (Id,name,birthday) VALUES (1, ' Jack ', ' 2011-1-1 ');
Insert into teacher (Id,name,birthday) VALUES (2, ' Marry ', ' 2011-2-2 ');
Insert into teacher (Id,name,birthday) VALUES (3, ' Sisi ', ' 2011-3-3 ');

Select Max (Birthday), min (birthday)
from teacher;

4. Display the total price of each type of product after classifying the items in the order form
Select Product as category name, SUM (price) as product category Total
From Orders
Group BY Product;

5. Inquire about the purchase of several types of goods, and the total price of each category is greater than 100
Select Product as category name, SUM (price) as product category Total
From Orders
GROUP BY Product
have sum (price) > 100;

6.where V.s. Having difference:

Where is used primarily for row filters
Having is mainly used for category filters, usually having a group by, but there is a group by where there is not necessarily a having. Hving can be said to be a query against the result set.

Drop table if exists teacher;
CREATE TABLE Teacher (
ID int primary KEY auto_increment,
Name varchar () is not null unique,
Birthday Date
);
Insert into teacher (Name,birthday) VALUES (NULL, ' 2011-1-1 ');
Insert into teacher (Name,birthday) VALUES (' Marry ', ' 2011-2-2 ');
Insert into teacher (Id,name,birthday) VALUES (3, ' Sisi ', ' 2011-3-3 ');

Select Max (Birthday), min (birthday)
from teacher;

7. Delete the primary key, the primary key is only one in the table, either a column or multiple columns
ALTER TABLE teacher drop PRIMARY key;

8. Single-to-one relationship (programme i):
drop table if exists card;
drop table if exists person;

CREATE TABLE Person (
ID int primary KEY auto_increment,
Name varchar (NOT NULL)
);

INSERT into person (name) VALUES (' Jack ');
INSERT into person (name) values (' Marry ');

CREATE TABLE Card (
ID int primary KEY auto_increment,
Location varchar () is not NULL,
PID int,
Constraint PID_FK foreign KEY (PID) references person (ID)
);

Insert into card (location,pid) VALUES (' BJ ', 1);
Insert into card (location,pid) VALUES (' GZ ', 2);
Insert into card (location,pid) VALUES (' CS ', NULL);
Insert into card (location,pid) VALUES (' NJ ', 3);//Error

Delete a record of the person table
Delete from the person where name = ' Jack ';

9. One-to-one relationship (Programme II):
drop table if exists card;
drop table if exists person;

CREATE TABLE Person (
ID int primary KEY auto_increment,
Name varchar (NOT NULL)
);
INSERT into person (name) VALUES (' Jack ');
INSERT into person (name) values (' Marry ');

CREATE TABLE Card (
ID int primary KEY auto_increment,
Location varchar () is not NULL,
Constraint ID_FK foreign key (ID) references person (ID)
);
Insert into card values (' BJ ');
Insert into card values (' GZ ');
Insert into card values (' CS ');//Error
Insert into card values (NULL);

10. One-to-many/multi-pair Relationship:
Drop table if exists employee;
DROP table if exists department;

CREATE TABLE Department (
ID int primary KEY auto_increment,
Name varchar (NOT NULL)
);
INSERT into department (name) VALUES (' software department ');
INSERT into department (name) VALUES (' Sales department ');

CREATE TABLE Employee (
ID int primary KEY auto_increment,
Name varchar () is not NULL,
did int,
Constraint DID_FK foreign key (did) references Department (ID)
);
INSERT into employee (NAME,DID) VALUES (' Jack ', 1);
INSERT into employee (NAME,DID) VALUES (' Marry ', 1);

11. Question? Query all employees of the software department (Modular)
Select D.name as department name, E.name as employee name
From department as d,employee as E
where d.name = ' software department ';

Think: Is there any other way?

Decomposition:
(1) Select ID from department where name= ' software department ';
(2) Select name from the employee where did = 1;
(total) Embedded SQL

Select Name as Employee
From employee
Where did = (
Select ID
From department
Where name= ' software department '
);

12. Many-to-many relationships:
Drop table if exists middle;
drop table if exists student;
Drop table if exists teacher;

CREATE table if not EXISTS student (
ID int primary KEY auto_increment,
Name varchar (NOT NULL)
);
INSERT into student (name) VALUES (' Jack ');
INSERT into student (name) VALUES (' Marry ');

CREATE table if not EXISTS teacher (
ID int primary KEY auto_increment,
Name varchar (NOT NULL)
);
Insert into teacher (name) values (' Zhao ');
Insert into teacher (name) values (' Cai ');

CREATE table if not exists middle (
Sid Int,
Tid int,
Constraint SID_FK foreign key (SID) references student (ID),
Constraint TID_FK foreign key (TID) references teacher (ID),
Primary KEY (Sid,tid)
);
Insert into middle (sid,tid) values (+);
Insert into middle (sid,tid) values;
Insert into middle (sid,tid) values (2,1);
Insert into middle (sid,tid) values (2,2);

13. Question? Find out all the students that "Zhao" has taught
Select T.name as teacher, s.name as student
From teacher as t,student as s,middle as M
where t.name = ' Zhao ' and m.sid=s.id and m.tid=t.id;

14. Mode:
Select lists the fields that need to be displayed
From list all tables involved, recommended write aliases
Where Business conditions and table association conditions

15. Use MySQL-specific functions:
How many fewer days till the end of the year?
Select DateDiff (' 2011-12-31 ', now ());

16. Intercepting Strings
Select substring (' MySQL ', for each); Starting from 1

17. Keep 2 digits after the decimal point (rounded)
Select Format (3.1415926535657989,3);

18. Rounding Down (intercept)
Select floor (3.14);
Select Floor (-3.14);
Select Floor (3.54);
Select Floor (-3.54);

19. Take Random values
Select Format (rand (), 2);

20. Take a random integer value between 1-6
Select Floor (rand () *6) + 1;

21.MySQL Extension Knowledge:
Check MySQL documentation, using MySQL's functions: randomly generate random characters between ' a '-' Z '.
Randomly generate random characters between ' a '-' Z '
(1) query the Unicode value corresponding to ' a '-' Z '
Select ASCII (' a ');//97
Select ASCII (' Z ');//122

(2) generate a random integer between 97-122
Select Floor (rand () *26) +97;

(3) generate 97-122 corresponding characters
Select char (Floor (rand () *26) +97);

22. Check the MySQL documentation and use the MySQL function: MD5 encryption for password ' 123456 '.
Select MD5 (' 123456 ');

drop table user;
CREATE TABLE User (
ID int primary KEY auto_increment,
Name varchar (20),
Gender varchar (6),
Salary float
);
Insert into User (Name,gender,salary) VALUES (' Jack ', ' Male ', 4000);
Insert into User (Name,gender,salary) VALUES (' Marry ', ' female ', 5000);
Insert into User (Name,gender,salary) VALUES (' Jim ', ' Male ', 6000);
Insert into User (Name,gender,salary) VALUES (' Tom ', ' Male ', 7000);
Insert into User (Name,gender,salary) VALUES (' Soso ', ' female ', NULL);
Insert into User (Name,gender,salary) VALUES (' haha ', ' female ', 3500);
Insert into User (Name,gender,salary) VALUES (' hehe ', ' female ', 4500);
select * from user;

23.MySQL Unique Process Control functions:
1) if (value, first value, second value);
Value is true, take the first value, otherwise take the second value
Identify employees above 5000 yuan (including) as "high salary" or "starting salary".
Similar to the three-mesh operator in Java

Select if (salary>=5000, ' High salary ', ' starting salary ')
from user;

2) ifnull (value1,value2)
Value1 is null and replaced with value2
Identify employees with a salary of NULL as "unpaid"

Select name as employee, Ifnull (salary, ' unpaid ') as salary situation
from user;

3) If [value] then [RESULT1] else [result2] end;
When the value expression evaluates to True, take the value of RESULT1, otherwise take the value of RESULT2 (If...else ...).
Identify employees above 5000 yuan (including) as "high salary" or "starting salary".

Select
case where salary>=5000 then ' high salary '
Else ' starting salary ' end
from user;

4) case [Express] [value1] then [RESULT1] When [value2] then [result2] else [RESULT3] end;
When Express satisfies value1, take result1 value, satisfy value2, take result2 value, otherwise take RESULT3 value (switch...case.)
The employee of 7000 yuan is identified as "high salary", the employee of 6000 yuan is identified as "middle salary", and 5000 yuan is identified as "starting salary", otherwise it is identified as "low salary"

Select
Case Salary
When 7000 and then ' high salary '
When 6000 and then ' middle pay '
When the "starting salary"
Else ' low pay ' end
from user;

25. Query the total number of employees of the same gender >2 the sum of the wages, sorted by the sum of wages in descending order
Select COUNT (*) as the number of members, gender as gender, sum (salary) as wages and
From user
GROUP BY gender
Having Count (*) >2
ORDER by sum (salary) desc;

26. Salary for male employees of gender-1000, gender for female employees +1000, completed on one SQL
Select if (gender= ' female ', salary+1000,salary-1000) as salary from user;

27. Examples of commonly used functions

Select Now ();
Select year (now ());
Select Month (now ());
Select Day (now ());
Select Floor (DateDiff (now (), ' 1999-01-01 ')/365);//Interval year
Select Format (rand (), 2);
Select Floor (rand () +1); [1-5] Random values
Select Length (Trim (' Jack '));
Select strcmp (' A ', ' w ');

Summarize:

1. Completeness of the relationship

(1) Entity (line) Integrity: Each record has a unique identifier, usually represented by a field without any business meaning
(2) Referential integrity: A field (a) of a table must refer to a field value from another (b) table, and the field of table B must first exist.
(3) domain (column) Integrity: Fields are cell data, and values in a field must conform to certain rules, such as constraints on the field's domain value, the type of field, and so on.


2-Key Concept
(1) Primary key: only unique fields
(2) Combined primary key: Multiple fields combined to form a unique field
(3) foreign key: for the association between multiple tables


3 features of the primary key
(1) Primary key cannot be duplicated
(2) Primary key cannot be null
(3) auto_increment is MySQL-specific, default starting from 1, the ID value and table with the same birth
(4) in a multi-person project, the UUID is typically used to generate a unique primary key value, which makes it easier to maintain entity integrity when multiple data merges


4 Characteristics of the UNIQUE constraint
(1) non-null values cannot be duplicated
(2) Multiple null values can be inserted
(3) ' null ' empty string and null are different concepts


5 non-null constraint characteristics
(1) cannot insert null value
(2) PRIMARY KEY constraint = non-null constraint + UNIQUE constraint


6 External Health features
(1) The foreign key value must originate from the referenced table primary key value, or null
  
7 Association Relationships
(1) One-to-one (external root business related)
(2) one to many or more pairs (foreign keys are placed in multiple parties)

(3) Many-to-many (external health is placed in the association table, will be a many-to-many split into two one-to-many relationship)

8. Common functions:
(1). Date function:

2. Mathematical functions:

3. String functions