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. Inquiries purchased several types of goods. And every kind of product with a total price 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 differences:

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 place. 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 primary key, the primary key is only one in the table, either a column or a multi-column
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? Find 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 to list the fields that need to be displayed
From lists all the tables involved. Suggested 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); Start 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 for ' A '-' z ' corresponding Unicode value
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. Use MySQL's function: password ' 123456 ' for MD5 encryption.

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, the value of RESULT1 is taken. When satisfying value2, take the value of RESULT2, otherwise take the value of RESULT3 (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. The total number of employees who inquire about the same gender >2 the sum of their wages. and 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 (1000), gender for female employees +1000, completed on one SQL
Select if (gender= ' female ', salary+1000,salary-1000) as salary from user;

27. Frequently Used function examples

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, often using a field that has no business meaning whatsoever
(2) Referential integrity: A field (a) of a table must refer to a field value of a table (b), 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 a unique field
(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 repeated
(2) Primary key cannot be null
(3) auto_increment is unique to MySQL, starting from 1 by default. The ID value is the same as the table
(4) in multi-person projects. It is common to use UUID to generate unique primary key values, so that entity integrity remains intact when multiple data merges


4 Characteristics of the UNIQUE constraint
(1) non-null values cannot be repeated
(2) ability to insert multiple null values
(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. Frequently used functions:
(1). Date function:

2. Mathematical functions:

3. String functions

 

MySQL Basics and Exercises (5)