Database on-Machine scripts

On the importance of U disk backup ...

The USB flash drive is suddenly broken, and it's a hardware problem ... Software engineering documentation, parallel computing of large jobs, all databases on the machine scripts are not there. Cry to death ...

CREATE table Student (sno char) NOT NULL, sname varchar (TEN) null, Sage tinyint,                     Ssex char (1), Sdept char (2), primary key (SNO), Check (Ssex in (' m ', ' F ')) inserts into Student (sno,sname,sage,ssex,sdept) VALUES (' 20123221 ', ' Hair pass ', ', ' M ', ' CS '); insert Into Student (sno,sname,sage,ssex,sdept) VALUES (' 20123219 ', ' nanjng ', +, ' M ', ' is ') and INSERT into Student (Sno,sname,sage, ssex,sdept) VALUES (' 20123218 ', ' Liu Tongbin ', +, ' M ', ' MA '), INSERT into Student (sno,sname,sage,ssex,sdept) VALUES (' 20123217 ', ' Jia Xiaodong ', ' m ', ' Ms '), insert into Student (sno,sname,sage,ssex,sdept) VALUES (' 20123207 ', ' Sun Rui ', ', ' m ', ' Ms '); INSERT into Student (sno,sname,sage,ssex,sdept) VALUES (' 20121226 ', ' Summer changxing ', ' M ', ' MS '); SELECT * from student;create table SS (Sno int NOT NULL, sname varchar () NULL, Sbirth Datet IME, primary KEY (SNO)) drop table Ss;insert into SS (Sno,sname,sbirth) VALUES (0001, ' Zhang San ', ' 2014-02-01 '); select * FROM Ss;create table Course (CNO char (TEN) NOT NULL, CNAME varchar (10),                    Cpno char (TEN) null, credit tinyint, primary key (CNO), Foreign KEY (Cpno) references Course) INSERT into Course (cno,cname,cpno,credit) VALUES (' 2 ', ' Math ', nu ll,2); insert into Course (cno,cname,cpno,credit) VALUES (' 6 ', ' data structure ', null,2); insert into Course (Cno,cname,cpno,credit) VALUES (' 7 ', ' Pascal language ', ' 6 ', 4); insert into Course (Cno,cname,cpno,credit) VALUES (' 4 ', ' Operating system ', ' 6 ', 3); INSERT INTO Course ( Cno,cname,cpno,credit) VALUES (' 5 ', ' data structure ', ' 7 ', 4); INSERT into Course (cno,cname,cpno,credit) VALUES (' 1 ', ' Database ', ' 5 ', 4) INSERT into Course (cno,cname,cpno,credit) VALUES (' 3 ', ' Information system ', 4,5); SELECT * from Course;create table SC (Sno char (TEN) not NULL, CNO char (TEN) not NULL, Grade tin              Yint, primary key (SNO,CNO), foreign key (SNO) references Student,  Foreign KEY (CNO) references Course, check (grade>=0 and grade<=100)) INSERT into SC (SN O,cno,grade) VALUES (' 20123219 ', ' 1 ', +), insert into SC (sno,cno,grade) VALUES (' 20123218 ', ' 2 ', +); INSERT into SC (SNO, Cno,grade) VALUES (' 20123221 ', ' 3 ', +), insert into SC (sno,cno,grade) VALUES (' 20123217 ', ' 4 ', +); INSERT into SC (SNO, Cno,grade) VALUES (' 20123207 ', ' 5 ', +), INSERT into SC (sno,cno,grade) VALUES (' 20121226 ', ' 6 ', 100); SELECT * from SC; SELECT * from student;--find Selectselect * from Student Where sname= ' Summer changxing '; SELECT * from Student Where sname= ' nanjng ' and sage=22; SELECT sname,ssex from Student where sage=20; SELECT sname,ssex,2014-sage from Student; The location of the--* can be a column, or it can be an expression with a column participation, or a simple expression select Distinct Sage from student;--Remove the repetition age, only show the age composition (multiple same age shows only one) SELECT Sage as ages From student;--to a column in a table property name, scope: function only in executing this statement select * from Student as all_student;--alias to table select * from Student Where Sage in (2 0,22);--in, select data within the range of (20,22) SELECT * from Student Where sage between and 22;--between, select data aged between 20 and 22 (including 20,22) SELECT * from Student Where sage>=18 and sage<=20; SELECT Student.sno,student.sname,sc.grade from Student,sc Where Student.sno=sc.sno and sc.grade>90;-- Select data combination output from two tables select * from SC where grade>80 order by grade asc;--Ascending (ASC), sorted by score Ascending select * from SC Where grade>80 Ord Er by grade desc;--descending (DSC), sorted by grade descending-character match, wildcard:% _select sname from Student Where sname like ' Summer% ';  SELECT sname from Student Where sname like ' lv _ '; --character match, escape character select sname from Student WHERE sname like ' nanjng \_ ' escape ' \; Select Sname from Student Where Sno was not null;--(not) NULL null value, select all name data NOT null--aggregate function (the result table column property name after the variable that is written immediately after the aggregate function is counted as the aggregate function) SE Lect Count (*) Num,avg (Sage) num1 from Student Where ssex= ' M ';--aggregate function COUNT, AVG; Select the number of male students (under NUM Column properties) and age average (NUM1) Select Count (*) Num,avg (grade), MAX (grade), MIN (grade), SUM (grade) from SC WHERE grade>90; --five aggregate functions--group statement Group Byselect COUNT (*) from SC GROUP by grade;--grouped by score, returns a statement of score count select grade from SC GROUP by grade;--Group statement , the data item after the SELECT statement can be an aggregate function, or it can be a groupdata item after by;--This example returns all the different scores in the group Select Sdept,max (SAGE) from Student GROUP by sdept;--having pairs                                                                    Group computed result set filtering select Sdept,count (*) from Student GROUP by Sdept have Count (*) >0;--GROUP by groups results, grouped by academy and calculated per group  --only returns the number of outputs on a group >0 the number of groups--three tables connected select Course.cno,course.cname                                From Student join SC in Student.sno=sc.sno join Course on SC.CNO=COURSE.CNO where student.ssex= ' M '; The common attribute of the two tables before and after--join is the join condition--subquery Select Sno from SC where CNO in (select CNO from Course Where CNAME like '% database ');--Query the student number of selected database course--nested subqueries Select Sname,sno from Student where Sno in (select Sno from SC where CNO in (select CNO from Course where cname like '% Database ')); --Query the name of the students in the selected database course and learn it well--the use of = and in nested subqueries select * from Student where sage= (select sage from Student where sno= ' 20123219 ');--clause filter The age of the student is 20123219--when the clause returns more than one value to use in, do not use =select * from Student where Sno in (select Sno from Student where sage=22); --top or Top percent select top 2 * from Student ORDER by sage Desc;  --Default ascending (ASC), the age of the table is sorted in descending order, filter out the top 2 select top percent * from Student order by Sage desc;--age--Add and delete--alter table Student DROP Ssex restrict;--Delete a column of data alter TABLE Student add birth VARCHAR (30);--Add a column of data delete from Student where sname= ' nanjng ';--before deleting a record, make sure it All columns in the record are not related to other tables, this sentence cannot be executed select * FROM student;--delete Table drop tables student;--Modify update Student SET sage=18 Where sname= ' Jia Xiaodong ';-- The revision number is ... The name is ... Update SC set grade=80 where sno=20123221;--modify results update SC SET grade=grade*1.2 where grade<90;--help sp_help student;--view C reate view SCS2 (SNO,SNAME,CNAME,CNO) as SELECT student.sno,student.sname,course.cno,course.cname from Student as a join S C as B on A.sno=b.sno joins Course as C on b.cno=c.cno; CREATE View SCS1 (sno,sname,cname,cno                                ) as SELECT student.sno,student.sname,course.cno,course.cname from Student join SC on Student.sno=sc.sno Join Course on Sc.cno=course.cno ; select * from scs1;--view drop view SCS1; 

Database on-Machine scripts