Basic MySQL knowledge and exercises (5) _ MySQL

1. calculate the average mathematical score of a class. (1) selectsum (math) count (math) as mathematical average score fromstudent; (2) selectavg (math) as mathematical average score fromstudent; (3) selectavg (name) as James average score 1. calculate the average mathematical score of a class.
(1). select sum (math)/count (math) as mathematical average score
From student;

(2). select avg (math) as mathematical average score
From student;

(3). select avg (name) as James average score
From student; // 0

2. calculate the average score of the total score of a class.
(1). select (sum (chinese) + sum (math) + sum (english)/count (*)
From student;

(2). select avg (chinese + math + english)
From student;

3. calculate the highest score and lowest score for 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', '2014-1-1 ');
Insert into teacher (id, name, birthday) values (2, 'marry', '2017-2-2 ');
Insert into teacher (id, name, birthday) values (3, 'ssis', '2017-3-3 ');

Select max (birthday), min (birthday)
From teacher;

4. after the items in the order table are classified, the total price of each category of items is displayed.
Select product as category name, sum (price) as total product category price
From orders
Group by product;

5. query the purchased products with a total price of more than 100.
Select product as category name, sum (price) as total product category price
From orders
Group by product
Having sum (price)> 100;

6. differences between where v. s. having:

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

Drop table if exists teacher;
Create table teacher (
Id int primary key auto_increment,
Name varchar (20) not null unique,
Birthday date
);
Insert into teacher (name, birthday) values (NULL, '2017-1-1 ');
Insert into teacher (name, birthday) values ('marry', '2017-2-2 ');
Insert into teacher (id, name, birthday) values (3, 'ssis', '2017-3-3 ');

Select max (birthday), min (birthday)
From teacher;

7. delete a primary key. the primary key has only one or more columns in the table.
Alter table teacher drop primary key;

8. one-to-one relationship (solution 1 ):
Drop table if exists card;
Drop table if exists person;

Create table person (
Id int primary key auto_increment,
Name varchar (20) 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 (20) 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 in the person table
Delete from person where name = 'Jack ';

9. one-to-one relationship (solution 2 ):
Drop table if exists card;
Drop table if exists person;

Create table person (
Id int primary key auto_increment,
Name varchar (20) 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 (20) not null,
Constraint id_FK foreign key (id) references person (id)
);
Insert into card (location) values ('BJ ');
Insert into card (location) values ('gz ');
Insert into card (location) values ('CS '); // error
Insert into card (location) values (NULL );

10. one-to-many/multiple-to-one relationship:
Drop table if exists employee;
Drop table if exists department;

Create table department (
Id int primary key auto_increment,
Name varchar (20) not null
);
Insert into department (name) values ('software part ');
Insert into department (name) values ('Salesman ');

Create table employee (
Id int primary key auto_increment,
Name varchar (20) 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 (combined)
Select d. name as department name, e. name as employee name
From department as d, employee as e
Where d. name = 'software part ';

Think: Is there any other way?

Decomposition:
(1) select id from department where name = 'software group ';
(2) select name from employee where did = 1;
(Total) Embedded SQL

Select name as employee
From employee
Where did = (
Select id
From department
Where name = 'software part'
);

12. many-to-many relationship:
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 (20) 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 (20) 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 (1, 1 );
Insert into middle (sid, tid) values (1, 2 );
Insert into middle (sid, tid) values (2, 1 );
Insert into middle (sid, tid) values (2, 2 );

13. question? Query all students taught by Zhao
Select t. name as, instructor, student s. name
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 fields to be displayed
From is used to list all tables involved. we recommend that you write aliases.
Where business conditions and table Association conditions

15. use MySQL-specific functions:
How many days will there be by the end of the year?
Select datediff ('2017-12-31 ', now ());

16. truncate a string
Select substring ('mysql', 1, 2); // starts from 1

17. retain the second decimal point (rounded)
Select format (3.1415926535657989, 3 );

18. round down (truncation)
Select floor (3.14 );
Select floor (-3.14 );
Select floor (3.54 );
Select floor (-3.54 );

19. random value
Select format (rand (), 2 );

20. take a random integer between 1 and 6.
Select floor (rand () * 6) + 1;

21. MySQL extension knowledge:
Query MySQL documents and use MySQL functions to 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 characters
Select char (floor (rand () * 26) + 97 );

22. check the MySQL document and use the MySQL function to perform MD5 encryption on the password '123456.
Select md5 ('200 ');

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 ('sososo', 'female ', NULL );
Insert into user (name, gender, salary) values ('hahaha', 'female', 3500 );
Insert into user (name, gender, salary) values ('hei', 'female', 4500 );
Select * from user;

23. MySQL-specific process control functions:
1) if (value, first value, second value );
Value is true. The first value is used. Otherwise, the second value is used.
Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "starting salaries"
Similar to the three-object operator in Java

Select if (salary> = 5000, 'payby', 'payby ')
From user;

2) ifnull (value1, value2)
Value1 is null, replaced by value2
Identify an employee whose salary is NULL as "unpaid"

Select name as employee, ifnull (salary, 'unpaid ') as salary
From user;

3) case when [value] then [result1] else [result2] end;
If the value of the value expression is true, the value of result1 is used; otherwise, the value of result2 is used (if... else ...)
Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "starting salaries"

Select
Case when salary> = 5000 then 'high quota'
Else 'start' end
From user;

4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end;
When express meets value1, obtain the result1 value. if value2 is satisfied, obtain the result2 value. otherwise, obtain the result3 value (switch... case ..)
Mark an employee of 7000 yuan as "high salary", an employee of 6000 yuan as "medium salary", and an employee of 5000 yuan as "starting salary". Otherwise, the employee is marked as "low salary"

Select
Case salary
When 7000 then 'highpaying'
When 6000 then 'medium sale'
When 5000 then 'start'
Else 'low sale' end
From user;

25. query the total number of employees with the same sex> 2 and sort them by the descending order of the total number of employees.
Select count (*) as number of members, gender as gender, sum (salary) as salary and
From user
Group by gender
Having count (*)> 2
Order by sum (salary) desc;

26. set gender to male employee salary to-1000, gender to female employee salary to + 1000, and complete the SQL statement.
Select if (gender = 'female', salary + 1000, salary-1000) as salary from user;

27. Examples of common functions

Select now ();
Select year (now ());
Select month (now ());
Select day (now ());
Select floor (datediff (now (), '2017-01-01 ')/1999); // year interval
Select format (rand (), 2 );
Select floor (rand () * 5) + 1; [1-5] random value
Select length (trim ('Jack '));
Select strcmp ('A', 'w ');

Summary:

1. link integrity

(1) entity (row) integrity: each record has a unique identifier, which is usually expressed by a field without any business meaning.
(2) integrity of reference: A field in one (A) table must reference A field value in another (B) table, and the field in Table B must exist first.
(3) domain (column) integrity: the domain is the unit data, and the values in the domain must comply with certain rules, such as the value field of the field and the type of the word segment.

2 Key Concept
(1) primary key: only unique fields
(2) primary key combination: a unique field is formed by combining multiple fields.
(3) foreign key: for association between multiple tables

3 Primary key features
(1) primary keys cannot be repeated.
(2) the primary key cannot be NULL.
(3) auto_increment is unique to MySQL. it starts from 1 by default and the ID value is the same as that of the table.
(4) in multi-person projects, UUID is usually used to generate unique primary key values, so that entity integrity is maintained when multiple data are merged.

4 Unique constraints
(1) non-NULL values cannot be repeated
(2) multiple NULL values can be inserted.
(3) 'null' empty strings and NULL are different concepts.

5 non-empty constraints
(1) NULL values cannot be inserted.
(2) primary key constraint = non-NULL constraint + unique constraint

6 Foreign health points
(1) the foreign key value must come from the referenced primary key value of another table, or NULL

7 correlation
(1) one-to-one (external root services)
(2) one-to-many or multiple-to-one (foreign keys are placed in multiple parties)

(3) many-to-many (external keys are placed in the associated table, splitting one many-to-many into two one-to-many relationships)

8. common functions:
(1). date functions:

2. mathematical functions:

3. string functions