(1) Database creation

1) Database classification

Note: the master database should not be moved. This is the database of the database. We mainly study the user database;

2) User database file composition

Note: a database has and only has one mdf, but can have multiple NDFs and LDFs (but at least one)

3) Create database

Create a master data file and a log file

Create multiple data and log files

Delete method:

4) Detach and Attach Databases

The necessity and syntax of separating database

• When the database service is running, it is usually impossible to move and copy the database files directly;
• The so-called separation of database means that the database file being used is used to lift the service limit;
  

The necessity and syntax of additional database

• Additional database means that the database files in the specified location are added to the database service center and run;
• Only after the database is attached can the user operate the data through DBMS;
  

(2) Data types in SQL Server

1) Text type: character data contains any combination of letters, symbols, or numeric characters

Note: the data type with n is twice as long as that without n, for example, n char (1) and char(2) are the same length;

2) Integer type

Note: multi use int;

3) Exact number type

Note: use numeric(18, 2) to represent 18 digit integer and two digit decimal;

4) Approximate number (floating point) type

Note: less use;

5) Date type

Note: the most commonly used format is 2013-01-01, this format;

6) Currency type

7) Bit type

8) Binary type

(3) Data table creation

1) Syntax of table building

The characteristics of the column contain:

• Blank (NULL): when data is entered and the database column is allowed to be blank, no data can be entered; otherwise, data must be entered. Whether the column is empty should be determined according to the specific requirements of database design, and it must be forbidden to be empty for key columns;
• Whether it is an identification column (automatic numbering);
• Whether there is a default value: if a column in the data table does not enter data, you want to provide a default content. For example, if the user does not enter an address, the default address is unknown;
• Is it a primary key: the primary key is the unique identification of the entity to ensure that the entity is not duplicated. A data table must have a primary key to make sense;

Description of identity column

• Meaning of identity column:
  

• Indicates how to use columns:
  

• Attention:
  

2) Code analysis

3) Batch statement

4) code

--Create student information data sheet
use StudentManageDB1
go

--Judge whether the data table exists
if exists(select * from sysobjects where name = 'Students')
drop table Students
go

--Create data table
create table Students
(
	StudentsId int identity(10000,1), --Student number, starting from 10000, increasing by 1 each time; identification column cannot be empty
	StudentName varchar(20) not null,--Full name
	Gender char(2) not null, --Gender
	Birthday datetime not null,--Date of birth
	StudentIdNo numeric(18, 0) not null,--ID number
	Age int not null, --Age
	PhoneNumber varchar(50),
	StudentAdress varchar(500),
	ClassId int not null --Class foreign key
)
go

--Create class table
if exists(select * from sysobjects where name = 'StudentClass')
drop table StudentClass
go 

create table StudentClass
(
	ClassId int primary key, --Define primary key (a table must contain primary key)
	ClassName varchar(20) not null,

)
go

--Create score sheet
if exists(select * from sysobjects where name = 'ScoreList')
drop table ScoreList
go

create table ScoreList
(
	Id int identity (1,1) primary key,
	StudentId int not null,--Student key
	CSharp int null,
	SQLServer int null,
	UpdateTime datetime not null --Update time
)
go


--Create administrator table
if exists(select * from sysobjects where name = 'Admins')
drop table Admins
go

create table Admins
(
	LoginId int identity (1000,1) primary key,
	LoginPwd varchar(20) not null,--Login password
	AdminName varchar(20) not null
)
go
select * from Students

(four) basic operation of data

1) Insert entity

Insert entity (data row) syntax

Sample SQL statement for inserting entity

insert into Students(StudentName, Gender, Birthday,
Age, StudentIdNo, PhoneNumber, StudentAdress, ClassId)--Try to write the column names in reverse order, but value Values must be consistent
	values('Zhang San', 'male', '1990-01-01', 21, 12345678945612345, '12345678952','Xingtai, Hebei', 4)

Be careful:

• Number of column names = number of corresponding values;
• Data of non value type must be in single quotation marks;
• The data value type must be the same as the defined field type;
• The identity column is an automatic growth class, which is maintained by the system and does not need to be added;

2) Query entity

Basic grammar

Example:

select Student, StudentName from Students -- Query two fields
 select * from Students -- * indicates to query all fields

3) Operators in T-SQL

Example:

select StudentId, StudentName, Gender from Students
where Age>=22

4) Update entity

Syntax:

Example SQL statement for update entity:

update Students set StudentAdress = 'Tianjin', Phonenumber = '13456' where StudentId = 10003

5) Delete entity

Syntax:

Example: