MS SQL Server Database Brief review

MS SQL Server database 1. Create a databaseCREATE database Javateam; 2. Using the databaseUse javateam; 3. Create a tableCREATE TABLE table name(field FirstName field type primary key fields grow (starting with X, X at a time),field name fields type,field name segment type);

Create Table Peoplenone (    intprimarykeyidentity(1,1) ,    varchar(+),    varchar(+));

  4. Enquiry Form--select * from Peoplenone;--select distinct pname,psex from Peoplenone;  5. Add Data

Insert  into Values ('MR. MA','male'); Insert  into Values ('MR. LU','female');

  6. Delete a table(1) Delete table, delete the structure of the tabledrop table Peoplenone;(2) Clear the table data without deleting the table structuretruncate TABLE peoplenone;(3) Delete according to conditionsDelete from peoplenone where pname = ' MR. MA '; 7. Modify the tableUpdate peoplenone set pname = ' MR. LU ', psex = ' male ' where pid = 2; 8. Adding Constraints(1) Syntax:ALTER TABLE table nameadd constraint Constraint name foreign (foreign key) key References primary key table (primary key)alter table stu_info add constraint fk_s foreign key(sid) references stu_table (Sid); (2) Adding data

Insert  into stu_table (sname,sclass)values(' local tyrants Zhang','2 ' ); Insert  into Stu_info (sid,sphone)values('2','  12111');

(3) Update, the updated SID number needs to exist in the primary keyUpdate stu_info set sphone = 8955 where sid = 1; (4) Delete, when the primary foreign key formation constraints, delete the primary key will be abnormal, you need to first delete the foreign keyDelete from stu_info where sid = 1; (5) Joint EnquirySelect sname,sclass,sphone from stu_table s,stu_info b where b . sid = s. Sid; 9. Compound query (subquery)1. Keywords: and or not

Select Count(*) as 'number of record strips'  fromAblum;Select *  fromAblumwhereAidbetween 1  and 3;Select *  fromAblumwhere  notAid= 2;

2. Fuzzy querysyntax: Like a%%a%%a A_ (similar to regular expressions)Select * from ablum where aname like '%f% '; 3. Othersyntax: Max min, Max minSelect min(aid) as ' minimum ' from ablum; syntax: Compound query, circumvention. Exclude top threeselect*  from ablum where aid not in (Select top 3 aid from ablum); 4. PagingSyntax:Select top (page length) * From table name where primary key not in(select top (page length * (pages 1)) primary key from table name);

Select Top (4*from wherenot in(Selecttop (4* (2-1 from Ablum);

MS SQL Server Database Brief review