SQL Server-T language learning

SQL Server - t language learning

Example:

Table 1: Student table student

School Number	Name	Gender	Class
2007001	Li Xiaoming	Man	Civil 0701
2007002	Wang Li	Woman	0701 of the letter meter

Table 2: Score table score

School Number	Course Number	Course Name	Results
2007001	1949	Advanced mathematics	85
2007001	1823	C Language	98
2007002	1950	Data	60

1. Data Definition language

The database language belongs to the fourth language and differs from other languages in that it is:

Case insensitive

Strings are all enclosed in single quotes

Do not differentiate between characters and strings

Do not split between statements with semicolons

A few more data types (for example, Date)

(1) Create a table

Grammar:

CREATE table < table name >

(

< column name >< data type >[ column integrity constraint ]

[,< column name >< data type >[ column integrity constraint ]]

......

[,< column name >< data type >[ column integrity constraint ]]

)

The format of the column integrity constraint condition:

[[ Default defaults ]]| [[Identity [(Seed,increment) ]] identity, Identity seed, increment

[NULL | NOT NULL] default is null

[PRIMARY KEY | Unique] The former can only have one, but some properties are not allowed to repeat, such as ID card and phone number, you can write more than one UNIQUE

Example 1:

Create Table Student

(

Id bigint Identity (2007001,1) PRIMARY KEY,

Name varchar (TEN) is not NULL,

Sex char (2) NOT NULL,

Classname varchar (TEN) NOT NULL

)

Example 2:

Create Table Score

(

ID bigint not NULL Foreign Key References student (ID), effect as table

Classid int NOT NULL,

Classname varchar (TEN) is not NULL,

Score int,

Constraint xh_kch Primary Key (ID,CLASSID) Federated Primary Key,xh_kch can be a random name

)

It is a good idea to add the use Test description in each new query to the table in which to operate, preventing the other tables from being mistakenly manipulated (system tables).

(2) Modify the table

Grammar:

ALTER table < table name >

[add< New column name >< data type >[ integrity constraint ]]

[drop< integrity constraint name > ( column name )]

[modify< column name >< data type ;]

Example 3:

Alter Table Student Add academic varchar (20)

Description: TheSQL Server format is a bit different in terms of modification , and ADD and DROP are the same

ALTER TABLE student ALTER COLUMN name varchar (20)

(3) Delete a table

Grammar:

DROP Table name

Example 4:

Drop Table Student

2. Data Manipulation language

(1) Add record

Grammar:

Insert [into] table name values ( column value 1,... ) column Values N)

Example:

Insert student values (' Li xiaoming ', ' Male ', ' civil 0701') here must be strictly consistent correspondence, if there are more than one, you can then write the parentheses

Or:

Insert into student (name,sex,classname) VALUES (' Li xiaoming ', ' Male ', ' civil 0701')

(2) Deleting records

Grammar:

Delete [from] table name WHERE condition

Example:

Delete score where classid=1821

( the conditions here can be used classid like ' 2010% ' where the wildcard character % represents an arbitrary string,_ represents a string fuzzy query )

(3) Change of record

Grammar:

Update table name set column name = expression [...,]

WHERE Condition

Example:

Update score set classid=1821 where classid=1823 Change 1823 to 1821

(Can have logical representations of and and or )

(4) Inquiry record

Grammar:

Selcet column 1 [, column 2,...]

From table name

WHERE Condition

GROUP BY grouping expressions

Having group statistics conditions

Oredr by sort expression

Example:

Example 1: Querying the basic information of all students

Select * FROM Student

Example 2: Check the test scores of all students and show their professional

Select Student.id,name,academic,score.classname,score

From Student,score

Where student.id=score.id

Example 3: Find out all the information of students who fail in their grades

Select Student.id,name,sex,student.classname,score.classid,score.classname,score

From Student,score

Where student.id=score.id

Example 4: The "advanced mathematics" course results of the students who havestudied the number 2007001

Select Score.score

From Student,score

Where student.id= ' 2007001 ' and score.classname= ' advanced mathematics '

Example 5: Query all students for the highest score details

SELECT *

From score

where Score.score in

(

Select MAX (Score.score)

From score

)

Note: Some base functions are available in the database, such as:max,min,count , etc.

Example 6: Search for information from all "credit" students

SELECT *

From student

Where classname like ' meter % '

SQL Server-T language learning