Creation and management of tables

Experimental Summary
Here is the SQL SERVER 2016 version .
There are two ways to create tables and modifications:
-Method 1: using graphical methods
-Method 2: using Transact-SQL statements
For example, in the school database, create the following three tables (student, course, SC):

name

student
column name	meaning	data type	constraint
Sno	School Number	CHAR (8)	main code
sname	NCHAR (6)	non-empty
SID	ID number	CHAR (s)	value is not heavy
ssex	sex	NCHAR (1)	default is "male"
Sage	age	TINYINT	Take a value of 20~28
sdept	is in the same line	NVARCHAR ()

course
column name	meaning	data type	constraint
Cno	Course number	CHAR (7)	main code
Cname	course name	NVARCHAR	non-null
Credit	credits	NUMERIC (3,1)	greater than 0
semester	semester	TINYINT

Sc
Column Name	Meaning	Data type	Constraints
Sno	School Number	CHAR (8)	The main attribute column, the outer code of this table, refers to student's main code
Cno	Course Number	CHAR (7)	The main attribute column, the outer code of this table, refers to course's main code
Grade	Results	TINYINT

First you need to create a school database
Here, let's review.
Three types of data files:
1 main data file. MDF: The primary data file consists of the initial data files in the primary filegroup. A filegroup is a named collection of data files. Contains all the database system tables and the objects and data that are not assigned to the custom filegroup. The primary data file is the starting point for the database, which points to other files in the database. Each database has one primary data file and one primary filegroup, and the primary data file extension is. mdf. (Note: Here is a reference encyclopedia)

2 secondary data files. NDF: Some databases may require many secondary data files because they are too large, or they may use secondary data files on multiple stand-alone disk drives to distribute data across multiple disks. Secondary data files can be placed either in the primary filegroup or in a custom file group. The extension of the secondary data file is. ndf. (Note: Here is a reference encyclopedia)

3 transaction log files. LDF: The log file contains all the log information required to recover the database. Each database must have at least one log file, but can be more than one. The recommended file name extension for the log file is. ldf. (More specific content can refer to the encyclopedia)

1. Ok, then create a simple school database with a graphical approach
The SQL Server file has two names:
logical_file_name (logical file name)
logical_file_name : Is the name used when referencing a physical file in all Transact-SQL statements. The logical file name must conform to the rules for SQL Server identifiers and must be unique in the logical file name in the database. (That's the logical file name for the physical file name you use in the database.)
os_file_name (filename)
os_file_name : Is the physical file name that includes the directory path. It must conform to the operating system file naming rules. (That is, a string containing the file name and the storage path)
The following figure:

You can see that the database has been successfully established. (You can, of course, create a database using Transact-SQL statements (to be continued))
2. Next Start creating the table (the main introduction is the action on the constraint)

If you set a primary key , you can directly right-click on the property (if the primary key contains more than one property, use Shift or CTRL to select the selected property) as shown below

values are different , that is, to set this property to be unique

default values are set directly , as shown below

settings for value range
Select Check Constraint


FOREIGN KEY constraint
The order in which foreign key constraints are created is to first define the primary key of the primary table, and then define the foreign key from the table. That is, only the primary key of the primary table can be used as a foreign key from the table, and the constrained columns from the table may not be primary keys, and the primary table restricts the actions that are updated and inserted from the table.

OK, the constraint operations are basically introduced here.

So how do you view a Transact-SQL statement that creates a table using a graphical method
That is, Method 2: You can see the code to create the table by right-clicking on a table and selecting "Write table script (s)" –> "Create to (c)" –> "New Query Editor window".

(You can, of course, play it by yourself, and the code will be shorter.) )

the next section is to modify the table. (You can also use graphical methods and Transact-SQL statements to modify the table, no longer repeat the graphical modification of the method, because relatively simple)
To modify a table, you cannot check to prevent saving changes (s) that requireyou to re-create the table, or you may fail to make an error in modifying the table.


Here are some small exercises:
(1) Add a professional column to the student table named spec and type char (8).

ALTER TABLE Student 
Add spec char (8)

(2) Change the data type of the spec column to nchar (8).

ALTER TABLE student 
ALTER COLUMN spec NCHAR (8)

(3) Add a constraint to the sdept column of the student table, limiting the range of the column to {computer Department, Information Management department, communication Engineering Department}.

ALTER TABLE student
add constraint ck_sdept check (sdept= ' computer Department ' or sdept= ' information Management Department ' or sdept= ' Communication Engineering Department ')

(4) Add a constraint to the grade column of the SC table, requiring that the range of values for this column be 0~100.

ALTER TABLE SC 
add constraint ck_grade check (Grade between 0 and 100)

(5) Delete the newly added spec column in the student table.

ALTER TABLE student   
 DROP COLUMN spec  

Note When SQL SERVER 2016 finishes creating objects, Object Explorer is not automatically updated and you can click the Refresh button to see what you have created.

Copyright NOTICE: This article is for the original article, the code is only used for communication and learning. Reprint please indicate the source.