SQL Review Framework

Database system

1. Data: Date describes the symbol of a thing, can appear a variety of vectors, such as: sound, pictures, text, etc.
2. Databases: Database (abbreviated DB) data warehouses can be stored in computers for long periods of time and can be shared by a collection of data
3. Database management system: (DBMS) Management database some software SQL MySQL Oracle DB2
4. Database system: Includes database management system and Administrator (DBS)

(Part I.): Database

1: System Database

N Master: Record system Information SYS start

N MSDB: Agent database issues Job Alerts

N Tempdb: Staging Database

n Mode model Database L

N Precautions:

N Database management system with data model as core: model of hierarchical model of network model

2. User-defined database

Create a database	View Database	Modify Database	Deleting a database
Create Database dbname	EXEC sp_helpdb (view all databases) EXEC sp_helpdb dbname (view specific database)	Exec sp_renamedb ' oldname ' newname ' (modified name) Alter Database dbname (Modify databases)	Drop Database dbname
Caveats: Data files (primary: MDF secondary: NDF) and transaction log files (LDF)
Instance:	(1) Create student database student Create Database Student (2) using the current database student Use student (3) View the student database student information Exec sp_helpdb Student (4) Modify student database student name is called Stu Exec sp_renamedb ' student ', ' Stu ' (5) Delete student database Stu Drop Database Stu

(PART II): Table

1. Table structure

Data type	(1) integral type tinyint int smallint bigint (2) Real type float real numeric decimal (3) Character char (fixed byte) varchar (variable byte) text (1) Date and Time type datetime
Create a table	View table Structure	Modify Table Structure	Delete Table structure
Create Table Studentinfor ( Son int, Ssname varchar (20), Ssex varchar (20), ssage int )	Exec sp_help studentinfor	Alter TableName ColumnName Add Field ADD Field Name Type Delete a field Drop column Field name Modify column names Exec rename ' Stuno ', ' snum ' Modify Table Name Exec sp_rename modifying properties Alter column Sno varchar (20)	Drop studentinfor
Instance	(1) Create Student information sheet studentinfor (Includes SNO name sname Gender Ssex age Sage) (2) View table structure (3) Modify the table n add field native Snative College Scollege n Delete the age of the learning Section n Modify field Age sage for Stuage n Modify field Sex number sno integer to char type (4) Delete table structure

1. Table constraints

Primary key: There can be only one in a table, there may be multiple fields, primary key value cannot be empty (primary key)	Foreign keys: A table can have more than one, the reference to that field must be the primary key foreign key table is called the Foreign key table (from the table) (Foreign key)	Unique: Only one empty can appear (unique)	Default: You can have more than one (default)	Empty/Non-empty (Null/not null	Check (check) A table can have more than one
Identity column Identity	Applies only to numeric types, the default starting value is 1, and the increment is 1
	Create Student Information Sheet stuinfor School Number Name Gender Age Phone College Sno Sname Ssex Sage Sphone Scollege Create a student curriculum cinfor Course Number Course Name Cno CName Create a Student score table scinfor School Number Course Number Results Sno Cno Scroe
instance ( CREATE TABLE after adding)	Create table stuinfor (Sno int, Sname varchar), Ssex varchar, Sage int, Sphone varchar, Scollege varchar ()) Alter table Stuinfor Add constraint Zj_1 primary key (SNO) Alter column sname int not null ADD constraint  jc_1  Ch Eck (ssex= ' male ' or ssex= ' female ') Add constraint jc_2 Check (sage between) Add constraint wy_1 unique (sphone) ADD constraint df_1 default ' soft ' for scollege
Instance ( CREATE TABLE Add at the same time)	Create Table Stuinfor ( Sno int PRIMARY KEY, Sname varchar () NOT NULL, Ssex Varcar () Check (ssex= ' male ' or ssex= ' female) Default ' female ', Sage varchar Check (sage between and 30), Sphone varchar (a) unique, Scollege varchar (default ' soft work ') ) Create Table Cinfor ( Cno int PRIMARY KEY, Cname varchar (NOT NULL) ) Create Table Scinfor ( Sno int foreign KEY (Sno) references primary key stuinfor (Sno), Cno int foreign KEY (Cno) references primary key cinfor (Cno), Scroe int check (check between 0 and 100) )

1. Table Operations

Add insert    into	n  Full add Insert into table name values (value, value,....) n  section Add INSERT into Table name (field, field,...) values (value, value,...) n  add more than one line at a time INSERT into Table name (field, field,...) values (value, Value,...), (value, value,..) n  abbreviated format Insert Table values (value, value,..) //(1) Add 1, Zhang San, male, 22,13245673452, soft worker to stuinfor INSERT into stuinfor values (1, ' Zhang San ', ' Male ', $, ' 13245673452 ', default) //(2) Add 2, John Doe, Male to stuinfor Insert Inton stuinfor (Sno,sname,ssex) VALUES (2, ' John Doe ', ' male ') //(3) Add 1,html  2.java    3JAVASCRIP to cinfor Insert into cinfor values (1, ' HTML '), (2, ' Java '), (3, ' JavaScript ') //(4) Add 1,1,68 to Scinfor (abbreviated format) insert  scinfor   VALUES (1,1,68)
Change Updae	n  one or more field changes for all records in the table      Update table Set field = value, field = value  update stuinfor set sage=sage+1 n  One or more fields in the table that meet the criteria record change the Update table Set field = value, field = value   where condition (and or ...) n  changes with null conditions Update table Set field = value   where field is null   //(1) Change the College of all students in the Stunfor table to the environment Update stuinfor Set scolege= ' environment ' //(2) Change the College of gender-male students to a soft worker and one year older Update stuinfor set scolege= ' soft work ', sage=sage+1 where ssex= ' man ' //(3) Change the age of all students who have empty school records to a minimum of Update stuinfor Set sage=20 where scollege is null
Remove Delete	N Delete a record that satisfies a condition Delete from stuinfor where condition n Delete with null condition Delete from table where field is null n Delete Shorthand format Delete Table n Delete all records in a table Delete from table (1) Delete the information of all female students in the advertising academy Delete from stuinfor where scollege= ' ads ' and ssex= ' women ' (2) Delete all records with blank calls Delete from stuinfor where sphone is null (3) Delete all records in the table Delete from Stuinfor
Query Select	(a) Single-table query n querying all records in a table SELECT * FROM table n The specified field record in the query table Select field, field,... from table n Query Top N records Select top N percent * from table n Query with conditions SELECT * FROM table where condition N Query removes duplicates Select distinct field from table n Queries using aggregate functions (and aliases) Max () sum () min () Avg () count () Select A=b from table Select B A From table Select B as a from table N Fuzzy Query Like _ single character% one or more characters [] within range [^] not in range n Sort fields when querying ORDER BY (ASC + desc) N Merge Query Results Union n query to create a new table Select field, field into new table name from table n queries use the Grouping command. SELECT * FROM Stuinfor --statistics on the average age of female students Select AVG (stuage) from stuinfor where ssex= ' female ' --statistics on the average age of male students Select AVG (stuage) from stuinfor where ssex= ' man ' --statistics on the average age of male and female students Select Stusex,avg (stuage) from Stuinfor GROUP by Stusex (b) Multi-table query N Cross-Connect SELECT * FROM Stuinfor,cinfor,scinfor n Connections (correct) Select field, field from table, table where table. field = table. field Select field, field from table inner join table on table. field = table. Fields and ... n OUTER JOIN outer JOIN L Left Outer connection Left table full display right table satisfies explicit not satisfied with NULL L Right Outer connection (right join) full explicit left table satisfies explicit unsatisfied with null display L Fully connected (full join) left + right (1) Check all records in the student information sheet SELECT * FROM Stuinfor (2) Inquiry Student Information Form students ' School number, name, college Select Sno,sname,scollege from Stuinfor (3) Check student information sheet for soft or animated college student information Select * from stuinfor where scollege= ' soft work ' or scollege= ' animation ' SELECT * from Stuinfor where scollege in (' Soft work ', ' animation ') (4) Check the student information sheet for age in 23-27 student information Select * from stuinfor where sage between and 27 SELECT * from Stuinfor where sage>=23 and sage<=27 (5) Check the student information sheet for age >28 and soft worker information Select * from Stuinfor where sage<28 and scollege= ' soft work ' (6) Check the Student Information table for the first five records of the number, name, gender Select Top 5 sno,sname,ssex from Stuinfor (7) Inquiry student Information Form which college students come from (several) Select Scollege from Stuinfor (select COUNT (distinct scollege) from stuinfor) (8) The maximum age and minimum age of students in the Student information form Select Max (Sage) maximum, min (sage) minimum from stuinfor (9) The total score and the average of the elective course 1th in the Enquiry score table Select sum (Score), AVG (score) from Scinfor where cno=1 (10) The number of eligible grades in the Enquiry score table Select count (score) from Scinfor where score>=60 (11) Search students ' information in the Student score table in order from high to low Select * from Scinfor ORDER BY score Desc (12) Inquire about student information of surname Li Select * from stuinfor where sname like '% Li ' (13) The average age of each college is counted separately Select avg (SAGE) from Stuinfor GROUP by Scollege (14) Inquiry Student's school number, name, college create a new table N1 Select Sno,sname,scollege to N1 from Stuinfor (15) Check the student number, name, gender, college and achievement of all students taking the Java course Select Stuinfor.sno,sname,ssex,scollege,scroe from Stuinfor,cinfor,scinfor where Scinfor.sno=stuinfor.sno and Scinfor.cno=cinfor.cno and Cname= ' Java ' Select Stuinfor.sno,sname,ssex,scollege,scroe From stuinfor inner join scinfor on Stuinfor.sno =scinfor.sno INNER JOIN Cinfor on SCINFOR.CNO=CINFOR.CNO and Cname= ' Java ' (16) Check the course of all students SELECT * from stuinfor LEFT join Scinfor on Stuinfor.sno=scinfor.sno

(Part III) T-SQL

1. Basic concepts

N T-SQL

DDL DCL DML Process Control statements

Declaration of N variables, assignment, calculation, output

n function

Classification	Name of function	Role
Aggregation functions
Mathematical functions
String functions
Date and Time functions
Conversion functions

1. Process Control Statements

N If: Else

N Case

n while

N Begin...end

N Return

N Goto

N waitfor

(Fourth part) view

Create a View	View View	Modify a View	Delete a view
CREATE VIEW View_1 As SELECT * FROM SELECT * from sysobjects where type= ' V '

(part Fifth) security of the database

N Separation and additional addition

n Backup and restore

N Modifying the authentication mode

SQL Review Framework