2018-2-1 There is an example of how to change database tables and additions

The first is to build the database and create the tables in the database:

#创建数据库create database z_stu; #创建表/* Property name data type can be empty including   righteousness Sno varchar  (20) No study number (main code) Sname        varchar  (20) No student name Ssex varchar  (20) No student sex Sbirthday Datetime can Student's date of birth class varchar  (20) can be student's class */create table if not EXISTS student (sno varchar () NOT NULL CO Mment "School Number", sname varchar () NOT NULL comment "student name", Ssex varchar (NOT null comment "Student sex", Sbirthday datetime Comment "Student's birth date", class varchar (comment) "Student class", primary key (SNO)) charset utf8;/* attribute name data type can be empty including   C No varchar  (20) No course number (Master code) Cname varchar  (20) No course name TNO varchar  (20) No faculty number (outside code) */create table if not EXISTS course (CNO varchar () is not NULL comment "course number", CNAME var    char (a) NOT NULL comment "course name", TNO varchar (not NULL comment "teacher number", primary key (CNO)) CharSet utf8;/* property name Can the data type be empty   inclusiveSno varchar  (20) No study number (outside code) Cno varchar  (20) No course number (outside code) degree Decimal (4 , 1) can score main code: sno+ cno*/create table if not EXISTS score (Sno varchar (a) NOT NULL        Comment "study number", CNO varchar () NOT NULL comment "course number", Degree decimal (4,1), primary key (SNO,CNO)) CharSet utf8;/* property name        Data type can be empty including   TNO varchar  (20) No Faculty number (master code) tname varchar  (20) No faculty name Tsex varchar  (20) No Faculty sex tbirthday datetime can teach Date of birth Prof varchar  (20) can be title depart varchar  (20) No faculty is located in the Department */creat    e table if not EXISTS teacher (TNO varchar (a) NOT NULL comment "faculty number", Tname varchar (a) NOT null comment "teacher name", Tsex varchar () NOT NULL comment "gender", Tbirthday datetime comment "Birthday", Prof varchar comment "title", Depart Varch AR (+) not NULL comment "Faculty Department ", primary Key (TNO)) charset UTF8; 

There are 4 tables created here, student, course, score, teacher, and now add data to the table (that is, increase):

Student

INSERT into student (Sno,sname,ssex,sbirthday,class) VALUES ("108", "Zeng Hua", "male", "1977-09-01", "95033"), ("105", "Kuanming", "Male", " 1975-10-02 "," 95031 "), (" 107 "," Wang Li "," female "," 1976-01-23 "," 95033 "), (" 101 "," Li June "," male "," 1976-02-20 "," 95033 "), (" 109 "," Wang Fang "," Female "," 1975-02-10 "," 95031 "), (" 103 "," Contacts "," Male "," 1974-06-03 "," 95031 ");

Course

Insert into course values ("3-105", "Introduction to Computer", "825"), ("3-245", "Operating System", "804"), ("6-166", "Digital circuit", "856"), ("9-888", "Advanced mathematics", " 831 ");

Score:

INSERT into score values ("103", "3-245", "86"), ("105", "3-245", "75"), ("109", "3-245", "68"), ("103", "3-105", "92"), ("105 "," 3-105 "," 88 "), (" 109 "," 3-105 "," 76 "), (" 101 "," 3-105 "," 64 "), (" 107 "," 3-105 "," 91 "), (" 108 "," 3-105 "," 78 "), (" 101 "," 6-166 "," 85 "), (" 107 "," 6-166 "," 79 "), (" 108 "," 6-166 "," 81 ");

Teacher

("804", "sung", "Male", "1958-12-02", "Associate Professor", "Computer Department"), ("856", "Zhang Xu", "Male", "1969-03-12", "lecturer", "Electronic Engineering Department"), ("825", "Wang Ping", "female", "1972-05 -05 "," Ta "," Computer Department "), (" 831 "," Liu Bing "," female "," 1977-08-14 "," ta "," Electronic Engineering Department ");
　　　　
　

Modify data (change):

Update student set ssex= "female" where Sname= "Li June";

View table (check):

SELECT * FROM Student;select * from Course;select * to Score;select * from teacher;

Delete data (by deleting, in the Act Unit):

Delete from student where Sno in (101,103);

To view an instance of the data:

#1,   queries the sname, Ssex, and class columns of all records in the student table. Select Sname,ssex,class from student; #2,   query teachers All units are non-repeating depart columns. Select distinct depart from teacher; #3,   queries all records of the student table. SELECT * from student; #4,   queries all records in the score table with scores from 60 to 80. SELECT * from score where degree between and #5,   queries the records in the score table for scores 85, 86, or 88. SELECT * from score where degree in (85,86,88), #6,   Query student table in the "95031" class or sex for "female" students record. SELECT * FROM student where class = "95031" or ssex = "female";  #以Class降序查询Student表的所有记录. SELECT * FROM student order by class desc;#  all records in CNO Ascending, Degree descending query score table. SELECT * FROM Score ORDER by Cno,degree desc;  #查询 The number of students in "95031" class. Select COUNT (*) from the student group by class has class= "95033"; #10,   queries the highest score in the score table for student number and course number. (subquery or sort) Select Max (degree) from Score;select Sno,cno to score where degree= (select Max (degree) from score);#  query per door The average grade of the class. Select Cno,avg (degree) from score Group by CNO, #12, query score table with at least 5 students taking an average score of 3 courses that begin with the course. Select AVG (degree) from score Group by CNO have CNo like "3%" and Count (*) >=5; #查询分数大于70, sno columns less than 90. Select Sno from score where degree between, #查询所有学生的Sname, CNO, and degree columns. Select Student.sname,score.cno,score.degree from Studentjoin score on Student.sno=score.sno; #15, check all students ' sno, CNAME and degree columns. Select Student.sno,course.cname,score.degree from Studentjoin score on Score.sno=student.snojoin course on course.cno= score.cno; #16, query the sname, CNAME, and degree columns for all students. Select A.sname,b.cname,c.degreefrom Student as a,course as b,score as cwhere A.sno = C.sno and B.cno = C.cno;select sname, Cname,degree from student join score on Student.sno=score.sno join course on COURSE.CNO=SCORE.CNO; #17,   Inquiry for "95033" class students The average score. Select Sno from student where class= "95033", select AVG (degree) from score where Sno in (select Sno from student where class = "95033"); #1. Queries for the "3-105" course results are higher than the "109" student scores of all the students record. #20, query score the number of students who choose to learn more than the highest score of the record. #选修多门课的学生select Sno,count (*) from score GROUP by Sno; #找到最高分select Max (degree) from Scoreselect * from score where Sno in (s Elect Sno FroM score Group by Sno) and degree <(SelectMax (degree) from score), #21, the query score is higher than the number of "109", the course number is "3-105" all records of the results. Select degree from score where Sno= "109"and CNO= "3-105"; Select degree from score where degree>(select degree from score where sno= "109" and cno= "3-105"); #22, query, and student number 108 students of all students born in the same year Sno, sname and Sbirthday columns. Select Left (sbirthday,4) as date from Student;select Sno,sname,sbirthday to student where Sbirthday like Concat ((select Left (sbirthday,4) as date from student where sno= "108"), "", "%"), #23, query "Zhang Xu" the students ' performance in the classroom. #1, from the Score table query student performance #2, the condition is that this course is "Zhang Xu" teacher Classroom #a) from the Teacher's table to check the name Zhang Xu number #b) According to the teacher number in the curriculum to find the corresponding course select depart from teacher where Tname= "Zhang Xu" , select CNO from Course where depart= (select depart from teacher where Tname= "Zhang Xu"), #24, query the name of a teacher who has more than 5 students in a course. Select CNO from Score GROUP by CNO have count (*) > "5"; Select Tno from Course where CNO in (select CNO from Score Group by CNO have count (*) > "5"), select Tname from teacher where tno= (select Tno from Course where CNO in (select CNO from SC Ore GROUP by CNO have count (*) > "5"), #25, check the records of 95033 classes and 95031 classes of all students. SELECT * from student the Where class in (95033,95031); #26,   queries There are 85 + scores of courses Cno.select CNO from score where degree> "85" #27, to find out "computer Department" teachers teaching coursesScore table. Select TNO from teacher where depart= "computer system", select CNO from Course where TNO in (select TNO from teacher where depart= "computer system SELECT * FROM score where CNO in (select CNO from Course where TNO in (select TNO from teacher where depart= "computer system")); #29, The CNO, Sno, and degree of the students with the elective number "3-105" are at least higher than those of the elective number "3-245" and are sorted by degree from highest to lowest order. Select Max (degree) from score where cno= "3-245", select * from score where cno= "3-105" and Degree> (select Max (degree) fr OM score where cno= "3-245") Order BY degree desc, #30, query elective number "3-105" and score higher than the elective "3-245" course of the students CNO, SNO and Degree.select * from Score where cno= "3-105" and Degree> (select Max (degree) from score where cno= "3-245"); #31,   Query the name of all teachers and classmates, Sex and Birthday.select sname,ssex,sbirthday from student union select Tname,tsex,tbirthday from teacher; #32, query all "female" teachers and "women" Classmate's name, Sex and Birthday.select sname,ssex,sbirthday from student where ssex= "female" union select Tname,tsex,tbirthday from Teacher where tsex= "female"; #33,   query scores of students who have a lower average score than the course. SELECT * FROM Score GROUP by CNO haveDegree <avg(degree); #34, query all instructor Tname and Depart.select CNO from score Group by Cno;select TNO from course where CNO in (select CNO  From score Group by CNO), select Tname,depart from teacher where TNO on (select Tno from Course where CNO in (select CNO from Score GROUP by CNO); #35, query the Tname and depart of all teachers who have not lectured. Select Tname,depart from teacher where TNO not in (select TNO from Course where CNO in (select CNO from Score Group by CNO)) #36, check the class number of at least 2 men. The Select class from student group by class has count (Ssex= "Male")>= "2", #37, query student in the table of the students not named "Wang" record. SELECT * FROM student where sname don't like "Wang%"; #38, query the name and age of each student in the student table. Select Sname, (2018-left (sbirthday,4)) as age from student; #39, query student the maximum and minimum sbirthday date values in the table. Select Max (sbirthday), Min (sbirthday) from student, query all records in the student table in the order of the class number and age from the largest to the youngest. SELECT * FROM student order BY class Desc, (2018-left (sbirthday,4)) desc, #41, query "male" teachers and their courses. Select TNO from teacher where tsex= "male", select Teacher.tname,course.cname from Teacher,course where Course.tno in (select TN O from teacher where tsex= "man") and course.tno= Teacher.tno; #42, query the Sno, CNO, and degree columns of the highest scores. Select Max (degree) from score, select * from score where degree= (select Max (degree) from score), #43, Query and "Li June" all classmates with sex sname . Select Ssex from student where Sname= "Li June"; Select sname from student where ssex= (select Ssex from student where Sname= "Li June" Sname.select class from student where Sname= "Li June" for #44, inquiries and "Li June" with sex and classmates, select sname from student where ssex= (select Ssex F Rom student where sname= "Li June") and class= (select Class From student where sname= "Li June"), #45 and query all the "male" students who took the "Introduction to Computer" course. Select CNO from course where cname= "Introduction to Computers"; Select Sno from student where ssex= "male"; select * from score where Sno in (select Sno from student where ssex= "male") and CNO in (select CNO from Course where cname= "Introduction to Computers");

2018-2-1 There is an example of how to change database tables and additions