Database additions and deletions change

CREATE TABLE Lovostudebt (
Studentcode VARCHAR (PRIMARY KEY),
Studentname VARCHAR (20),
Age INT,
Sex VARCHAR (10),
Grade INT,
Clasddname VARCHAR (20)
) DEFAULT Charset=utf8;

INSERT into Lovostudebt (studentcode,studentname,age,sex,grade,clasddname)
VALUES (' xh1001 ', ' Jacky ', ' 20 ', ' Male ', ' all ', ' T01 '),
(' xh1002 ', ' simth ', ' 30 ', ' Male ', ' a ', ' T02 '),
(' xh1003 ', ' Jay ', ' 18 ', ' Male ', ' a ', ' T01 '),
(' xh1004 ', ' Helen ', ' 19 ', ' Female ', ' the ' T02 '),
(' xh1005 ', ' Lily ', ' 22 ', ' Woman ', ' all ', ' T03 '),
(' xh1006 ', ' Green ', ' 23 ', ' Male ', ' T02 '),
(' xh1007 ', ' Redchar ', ' 18 ', ' Male ', ' a ', ' T01 '),
(' xh1008 ', ' Kevin ', ' 17 ', ' Female ', ' a ', ' T03 ');

SELECT *from lovostudebt;
DROP TABLE lovostudebt;


--To get all the students ' total sum to indicate sum
SELECT SUM (grade) as ' total score ' from LOVOSTUDEBT;

--wait until the highest score for all students Max indicates maximum value
SELECT Max (grade) as ' highest score ' from LOVOSTUDEBT;

--Get the lowest score for all students, min indicates minimum value
SELECT min (grade) as ' lowest score ' from LOVOSTUDEBT;

--average score for all students, AVG says averaging
SELECT avg (grade) as ' average ' from lovostudebt;


--Total number of students received
--COUNT (Geade) The total number of peers, ignoring the record for which the column is null.
--COUNT (*) does not ignore records for which the column is null.
SELECT COUNT (grade) as ' number ' from LOVOSTUDEBT;

SELECT COUNT (*) as ' number ' from LOVOSTUDEBT;




--Group to query the total score of each class
--What group by IS grouped by
SELECT Clasddname,sum (grade) as ' total score ' from Lovostudebt GROUP by Clasddname;


--Group query the average maximum score of each class the minimum number of references
--Clasddname class name
--The fields that appear in select after grouping, the Weaver is a grouping field (the field after group by) or an aggregate function
SELECT Clasddname,sum (grade) as ' total score ',
AVG (grade) as ' average score ',
Max (grade) as ' highest score ',
Min (grade) as ' lowest score ',
COUNT (grade) as ' reference number '
From Lovostudebt GROUP by Clasddname;

--average of 85 students
--The Where is not followed by the aggregate function, because where is executed before GROUP by
--Having execution after grouping, you can follow the aggregation function
SELECT clasddname from Lovostudebt GROUP by Clasddname have AVG (grade) >=75;


--Check that class has girls, and tell the number of girls
SELECT Clasddname as ' class name ', COUNT (Sex) as ' number of females ' from lovostudebt WHERE sex= ' Women ' GROUP by Clasddname;

CREATE TABLE Student (
ID INT PRIMARY KEY auto_increment,
Studentcode INT,---School number
Studentname VARCHAR (20),--Student achievement
Subjectname VARCHAR (20),
Grade INT,--Score
ClassID INT--Class number

) DEFAULT Charset=utf8;

CREATE TABLE Class (
ID INT PRIMARY KEY auto_increment,
ClassName VARCHAR (20)--Class

) DEFAULT Charset=utf8;

--Add FOREIGN KEY constraints
ALTER TABLE student ADD CONSTRAINT fk_class FOREIGN KEY (ClassID) REFERENCES class (ID);


INSERT into Class (ClassName) VALUES (' T01 '), (' T02 '), (' T03 '), (' T04 ');

SELECT *from class;
DROP TABLE class;


INSERT into student (Studentcode, STUDENTNAME,SUBJECTNAME,GRADE,CLASSID)
VALUES (1, ' Zhang San ', ' Computer Basics ', 60, 1),
(1, ' Zhang San ', ' C language ', 88, 1),
(1, ' Zhang San ', ' Relational database Basics ', 90, 1),
(2, ' Harry ', ' Computer Basics ', 80,2),
(2, ' Harry ', ' C language ', 70,2),
(2, ' Harry ', ' Relational database Basics ', 55,2),
(3, ' John Doe ', ' C language ', 66,4),
(3, ' John Doe ', ' Relational Database Foundation ', 90,4);
select* from student;
DROP TABLE student;
--The name of the student who shows the highest score and highest score of the C language course
SELECT MAX (grade), studentname from student WHERE subjectname= ' Computer Fundamentals '
and Grade= (SELECT MAX (grade) from student WHERE subjectname= ' Computer Foundation ');


SELECT * FROM student WHERE subjectname= ' Computer Foundation ' ORDER by grade DESC LIMIT 0, 1;

--Show All student information, request to convert class number to class name
Select S.*, (select ClassName from class as C WHERE c.id = S.classid) as ' class name ' from student as s;


--Query the students who have better examination than Harry C language
SELECT * FROM student WHERE subjectname = ' C language ' and
Grade > (SELECT grade from student WHERE subjectname= ' C language ' and Studentname= ' Harry ');


--Querying relational database and Zhang San student information
SELECT *from student WHERE subjectname = ' relational Database Foundation ' and
Grade= (SELECT grade from student WHERE subjectname = ' Relational database Basics '
And Studentname= ' Zhang San ') and Studentname!= ' Zhang San ';



--Check that class has students
Select ClassName from class WHERE ID in (SELECT DISTINCT ClassID from student);


--Query that class no students
Select ClassName from class WHERE ID not in (SELECT DISTINCT ClassID from student);


--Find out the highest score and student name for each course
SELECT * FROM student S1 WHERE grade in (
SELECT MAX (grade) from student S2
WHERE s1.subjectname=s2.subjectname GROUP by Subjectname);

--Check all student names, subjects, grades, classes
SELECT S.studentname,s.subjectname,s.grade,c.classname from
Student S JOIN Class C on s.classid=c.id;


SELECT S.studentname,s.subjectname,s.grade,c.classname from
Student S, Class C WHERE s.classid=c.id;

CREATE TABLE t_student (
ID INT PRIMARY KEY auto_increment,
Studentname VARCHAR (),
Chinese INT,
Math INT,
中文版 int,
Cardcode VARCHAR (20)-Test number
) DEFAULT Charset=utf8;
INSERT into T_student (studentname,chinese,math,english,cardcode)
VALUES (' Zhang Mowgli ', ' 90 ', ' 80 ', ' 70 ', ' 12121525121 '),
(' Wang Zhentian ', ' a ', ' 12454545551 ', ' the ' "', ' '" ') ', '
(' Hu ', ' I ', ' the ' "', '" ') ', ' 11245454545 '),
(' Li Jinfa ', ' 70 ', ' 90 ', ' 84 ', ' 52524645254 ');

Select*from t_student;

DROP TABLE t_student;


--Change the language of Mowgli to 95, math to 85,
UPDATE t_student SET chinese= ' up ', math= ' WHERE studentname= ' Zhang Mowgli ';--can be replaced by ID

-Adds 5 points to the math score
UPDATE t_student SET math=math+5;

--The person surnamed Zhang English minus 10 minutes
--Zhang%, indicating a wildcard character, matching all the strings of the beginning (Zhang San, Zhang Four, Steven Cheung)
--% of the%, indicating (Liu Changyun, Yang Zhangyun)
UPDATE t_student SET english= english-10 WHERE studentname like ' Zhang% '

--Add the school column
ALTER TABLE t_student Add school VARCHAR;

--Modify Zhang Mowgli's school for Chengdu 12, Hu School for Chengdu Seven medium

UPDATE t_student SET school= ' Chengdu 12 ' where id=1;
UPDATE t_student SET school= ' Chengdu VII ' where id=3;



--revision of the school's null record, the school changed to Chengdu Four
UPDATE t_student SET school= ' Chengdu IV ' WHERE School is NULL;


--Delete Hu
DELETE from t_student WHERE studentname= ' Hu ';

--Delete records between 60-70
DELETE from T_student WHERE english>=60 and english<=70;

--can also be written
DELETE from T_student WHERE 中文版 between and 70;

--Delete all
TRUNCATE TABLE t_student;


--Query all records
--* denotes all the fields in the table
SELECT * from T_student;

--Check the names and language scores of all students
SELECT Studentname,chinese from T_student;


--as indicates an alias to the column
SELECT studentname as ' name ', Chinese as ' language ' from t_student;


--DISTINCT to remove duplicate records
SELECT DISTINCT School from T_student;

Database additions and deletions change