Use master
Go
if exists (SELECT * from sysdatabases where name = ' Student Management system ')
Drop DATABASE Student Management system
Create DATABASE Student Management system
On primary
(
Name = ' Student management system _data ',
filename = ' E:\temp\ Student management system _data.mdf ',
Size = 10MB,
FileGrowth = 1MB
)
Log on
(
Name = ' Student management system _log ',
filename = ' E:\temp\ Student management system _log.ldf ',
Size = 10MB,
FileGrowth = 1MB
)
Go
Use Student management system
Go
if exists (select * from sysobjects where name = ' Student table ')
DROP TABLE Student Table
CREATE TABLE Student Table
(
Id int Identity (1) primary key,
Name varchar (TEN) is not NULL,
The age int is not a null check (age < A and age > 10),
Gender varchar (4) NOT NULL,
Class int NOT NULL
)
Go
Use Student management system
Go
if exists (select * from sysobjects where name = ' Class table ')
drop table Class table
CREATE TABLE Class table
(
Id int Identity (1, 1) primary key,
Name varchar (TEN) is not NULL,
People int not NULL,
Grade int NOT NULL
)
Go
Use Student management system
Go
INSERT into student table (Name, age, Gender, Class) VALUES (' Zhang San ', 21, ' Male ', 1)
INSERT into student table (Name, age, Gender, Class) VALUES (' John Doe ', 27, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) VALUES (' Harry ', 24, ' Male ', 5)
INSERT into student table (Name, age, Gender, Class) VALUES (' Saturday ', 26, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) VALUES (' Seven ', 28, ' Male ', 3)
INSERT into student table (Name, age, Gender, Class) VALUES (' Liu Ba ', 24, ' Male ', 1)
INSERT into student table (Name, age, Gender, Class) VALUES (' Shang ', 22, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) values (' Hu Shi ', 25, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) VALUES (' Ann 11 ', 26, ' Male ', 3)
INSERT into student table (Name, age, Gender, Class) VALUES (' Xu 12 ', 24, ' Male ', 1)
INSERT into student table (Name, age, Gender, Class) VALUES (' Pay 13 ', 23, ' Male ', 5)
INSERT into student table (Name, age, Gender, Class) VALUES (' Guo 14 ', 24, ' Male ', 1)
INSERT into student table (Name, age, Gender, Class) values (' Li XV ', 29, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) VALUES (' Qin 16 ', 21, ' Male ', 2)
INSERT into student table (Name, age, Gender, Class) VALUES (' 17 ', 28, ' Male ', 1)
INSERT into student table (Name, age, Gender, Class) VALUES (' Week 18 ', 21, ' Male ', 3)
Go
Use Student management system
Go
INSERT into Class table (Name, people, Grade) VALUES (' computer ', 50, 51)
INSERT into Class table (Name, people, Grade) VALUES (' Information security ', 56, 52)
INSERT into Class table (Name, people, Grade) VALUES (' Software engineering ', 58, 54)
INSERT into Class table (Name, people, Grade) VALUES (' Network engineering ', 54, 57)
Go
Use Student management system
Go
SELECT * FROM Student table
SELECT * FROM class table
Select a.ID, A.name, b.name as ' class name ' from Student table A, Class table B where A.class = b.id */Basic link */
Select A.class, A.name, b.name as ' class name ' from student table A inner JOIN class table B on a.class = b.id where b.id > 0/* Inside connection */
/* Differences between connections and outer joins: The INNER join returns only two tables that match the criteria, and the outer joins are based on a table, and will return if the contents of the other table match the criteria.
Select A.class, A.name, b.name as ' class name ' from student table A left OUTER join class table B on a.class = b.id where a.id > 0/* ieft outer connection */
/* where both on and where are followed by the condition, but not an effect, on the following condition is the condition in the query, where the condition is the condition of the start of the query */
Select A.class, A.name, b.name as ' class name ' from student table A right outer join class table B on a.class = b.id where b.id > 0 */LEFT outer connection */
/* Right outer connection and left outer connection a function, as long as the distinction between the main table and the relationship between the table is good */
Select A.class, A.name, b.name as ' class name ' from student table A full outer join class table B on a.class = b.id/* Fully connected */
/* Full connection is to show the information of two tables from the table without the main table */
Select A.class, A.name, b.name as ' class name ' from student Table A Cross join Class table B/* Crossover connection */
/* Cross join is a Cartesian product that returns two tables */
Select B.name, B.people, A.name from class A, Class table B where A.people = B.people and a.id <> b.id/* Self-connect */
Select a.ID, A.name, a.age from student table A
Union
Select ', ' Average ', AVG (student table. Age) from student table/* UNION query */
/* Union query is to make a chunk of multiple query results, note that each query result should be saved as data type */
Select Id, Name, age from student table where age < (select AVG (age) from student table)/* Sub-query */
/* Sub-query can also be nested */
SELECT * from student table FOR XML AUTO/* The query results are automatically converted to XML format, out of the back of auto, there are many keywords to choose to use. */
/* Cross Query */
/* Poor query */
Go
Use Student management system
Go
DROP TABLE Student Table
drop table Class table
Go
Summary of SQL Sever 2008 Table connection method
