MySQL Basic grammar and exercises (4)

1. Create an employee table (and indicate that the character set is UTF8)
Drop table if exists employee;
CREATE TABLE Employee (
ID int,
Name varchar (20),
Gender varchar (6),
Birthday date,
Entry_date date,
Job varchar (30),
Salary float (5,1),
Resume text
);

2. Insert Data:
INSERT into employee (Id,name,gender,birthday,entry_date,job,salary,resume)
VALUES (1, ' Jack ', ' Male ', ' 2011-10-8 ', ' 2011-12-31 ', ' software ', 5000.1, ' hello ');

3. An image column is basically added to the employee table above.
ALTER TABLE employee add image blob;

4. Modify the job column so that it has a length of 60.
ALTER TABLE employee
Modify job varchar (default ' teacher ');

5. Delete the gender column.
ALTER TABLE employee drop gender;

6. Change the table name to user.
Rename table employee to user;

Note: For MySQL, you cannot modify the name of the database, but you can modify the table name

7. Modify the table's character set to GBK.
ALTER TABLE user
Character Set UTF8;

8. The column name name is modified to username.
ALTER TABLE user
Change Column name username varchar (20);

9. Insert a Chinese record into the user table
Insert into User (Username,id,birthday,entry_date,job,salary,resume)
VALUES (' Jack ', 2, ' 2011-10-8 ', ' 2011-12-31 ', ' software ', 5000.1, ' hello ');

Insert into user values (3, ' Marley ', ' 2011-10-8 ', ' 2011-12-31 ', ' software ', 5000.1, ' hello ', NULL);

Insert into user values (4, ' Marley ', ' 2011-10-8 ', ' 2011-12-31 ', ' software ', 5000.1,null,null);

Insert into User (Id,username,birthday,entry_date,job,salary,image)
VALUES (5, ' Marley ', ' 2011-10-8 ', ' 2011-12-31 ', ' software ', 5000.1,null);

10. Modify the encoding used by the client input and output, consistent with the WINDOWXP platform
Set CHARACTER_SET_CLIENT=GBK;
Set CHARACTER_SET_RESULTS=GBK;

11. Change the salary of all employees to 6000 yuan.
Update user Set salary = 6000;

12. Change the salary of the employee whose name is ' Marley ' to $7000.
Update user Set salary = 7000 where username = ' Marley ';

13. Increase the ' Jack ' salary by 1000 yuan on the original basis.
Update user Set salary = salary + where username = ' Jack ';

14. Delete the record with the name ' Jack ' in the table.
Delete from user where username = ' Jack ';

15. Delete all records in the table.
Delete from user;

16. Use truncate to delete records in the table.
TRUNCATE TABLE user;

17. Inquire about all the students in the table.
SELECT * from student;
Select Id,name,math,chinese,english from student;
Select Name,id,math,chinese,english from student;
Select Name,math from student;

18. Check the names of all students in the form and the corresponding English scores.
Select Name,english from student;

19. Filter duplicate data in the table.
Selectdistinct中文版 from student;
SelectdistinctName,english from student;

20. Add 10 extra-long points to all student scores.
Select name,math+10 from student;
Select name as name, math+10 as math from student;

21. Count Each student's total score.
Select Name,math+chinese+english
from student;

22. Use aliases to indicate student scores.
Select Name,math+chinese+english as Total
from student;

23. Check the student's grades named ' Zhang Xiaoming '
SELECT * FROM Student
where name = ' Zhang Xiaoming ';

24. Query students with English scores greater than 90 points
SELECT * FROM Student
where 中文版 > 90;

24. Query all students with a total score greater than 200
Select Name,chinese+math+english as Total
From student
where Chinese+math+english > 200;

25. Check the English score between 80-90 students.
SELECT *
From student
where english>=80 and english<=90;
Or
SELECT *
From student
where 中文版 between and 90;

26. Query Math scores for 89,90,91 students.
SELECT *
From student
where math=89 or math= or math=91;
Or
SELECT *
From student
where math [not] in (89,90,91);

27. Check all the students whose surname is ' Li '.
SELECT *
From student
Where name like ' Li% ';

SELECT * FROM Student
Where name like '% Lee ';

SELECT * FROM Student
Where name like '% li% ';

28. In the site development of multi-conditional query commonly used to
SELECT * FROM Student
Where name like '% ';

SELECT * FROM Student
Where name like ' __ Li ';

SELECT * FROM Student
where math is [not] NULL;

29. Query Math >80 and language >80 students.
SELECT *
From student
where math >80 and chinese>80;

30. Sort the math results after the output.
Ascending:
SELECT *
From student
Order BY math ASC;

Descending:
SELECT *
From student
ORDER BY math desc;

The total score descending after the output.
Select Name,math+chinese+english as Total
From student
ORDER BY math+chinese+english Desc;

31. The total of students whose surname is ' Li ' is output in descending order.
Select Name,math+chinese+english as Total
From student
Where name like ' Li% '
ORDER BY math+chinese+english Desc;

32. How many students are there in a class?
Select COUNT (*) as total number of people
from student;

33. How many students with more than 80 of the statistical math score?
Select COUNT (*) as total number of people
From student
where math > 80;

34. What is the number of people with a total score greater than 250?
Select COUNT (*) as total number of people
From student
where (Math+chinese+english) > 250;

Select COUNT (English) as the total number of people
From STUDENT;//13

Select count (math) as total number of people
from student;

35. Statistic a class mathematics total.
Select sum (Math)
from student;

Select SUM (name)
From student;//0

36. Statistics of a class of Chinese, English, mathematics, the total scores of each section.
Select sum (math) as Math score, sum (Chinese) as language score, SUM (English) as the total score
from student;

37. Statistics A class of Chinese, English, mathematics, the sum of the results.
Select sum (Math) +sum (Chinese) +sum (中文版) as Class total
from student;

38. The average score of a class's language score is counted.
Select sum (Math)/count (math)
from student;

Select sum (Math)/count (*)
from student;

Summarize:

(1). Delete from or TRUNCATE table or drop table's respective differences:
Delete from: Deletes all records in the table by row, but preserves the table, which is suitable for deleting data with little data volume and can be deleted by condition
TRUNCATE TABLE: Copy the original tables structure-〉 one-time delete entire table, automatic recovery of the original table structure, suitable for deleting data of large amount of data, can not be deleted by criteria
drop TABLE: Delete the table itself
When deleting records, be sure to keep an eye on the relationship between the tables

(2). Sort: null value is minimal, use ORDER BY clause, default ascending, ORDER BY clause must be placed in the last
(3). Compound function
(1) Count () function, statistical, no null value
(2) sum () function, statistic and use, do not count non-value, if statistical non-value, return 0

(4). aggregate function
AVG ()
Max (), Min (), when the Max () and Min () functions are in the date type, get the most recent and earliest dates, respectively