Operation Methods for Database Tables in SqlServer (Creating Database, Creating Tables, Modification Statements)

Key points of learning:

　　SQL-The complete list of basic SQL statements for creating a database, creating a table, creating constraints, and relationships is as follows: Lifting something up and putting it down is called lifting, and lifting something up and not being able to put it down is called carrying. The head must have courage, and looking up must have confidence. Learning should be added, pride should be reduced, opportunities should be seized, and laziness should be eliminated. The three major difficulties in life are thinking, loving, and loving alone.

SQL-Creating a database, creating a table, creating constraints, relationships, and part T-SQL statement

---Create a library Before creating a library, first check whether the secondary database exists in the database to be deleted 
--- if exists(select * from sys.sysdatabases where name='ConstructionDB')begin use master drop database ConstructionDB end go create database ConstructionDB on()
if exists(select * from sysobjects where name ='ConstructionDB') --Find command
drop DATABASE ConstructionDB --Delete command
Create database ConstructionDB
on(
name='ConstructionDB_date',
filename='E:\Skill Inspection Test Module Two (Database)\Questions\1\Task One\ConstructionDB_date.mdf',
size=3mb,
maxsize=10mb,
filegrowth=5% --The growth rate is
)
log on(
name='ConstructionDB_log',
filename='E:\Skill Inspection Test Module Two (Database)\Questions\1\Task One\ConstructionDB_date.ldf',
size=2mb,
maxsize=5mb,
filegrowth=1mb
)
--Using T-SQL statement to create table
use ConstructionDB
go
---Query if this table exists in the database, if it exists, delete it
if exists(select * from sysobjects where name = 'T_flow_step_def') 
drop table T_flow_step_def
--- Method Two
IF OBJECT_ID (N'bas_CardType') IS NULL
BEGIN --If the table does not exist, create it
--drop table com_CodeRecord
--Process Step Definition Table 
create table T_flow_step_def(
Step_no int not null, --Process Step ID 
Step_name varchar(30) not null, --Process Step Name 
Step_des varchar(64) not null, --Process Step Description
Limit_time int not null, --Time Limit
URL varchar(64) not null, --Secondary Menu Link 
remark varchar(256) not null, 
)
---Flow Category Table
create table T_flow_type(
Flow_type_id char(3) not null, --Process category number 
Flow_type_name varchar(64) not null, --Process category name 
In_method_id char(3) not null, --Bidding method code 
In_choice_id char(3) not null, --Project option code 
remark varchar(256) not null, 
)
---Contract section situation table
create table T_sub_project(
Project_id varchar(32) not null, ---Project number 
Sub_pro_id char(2) not null, -- Contract section number 
Flow_type_id char(3) not null, --Process category number 
Sub_pro_name varchar(64) not null,--Contract section name (bidding project name) 
Usb_no varchar(64) not null, --Password lock number
In_method_id char(3) not null, --Bidding method code 
In_scope_id char(3) not null, --Bidding scope code 
In_choice_id char(3) not null, --Project option code 
Proj_type_id char(3) not null, --Project nature code 
Engi_type_id char(1) not null, --Engineering nature code
Pack_type char(1) not null, ---Dispatching method 
Grade_type_idv char(1) not null,--Evaluation category number
Flag_done char(1) not null,--Completion flag 
Flag_forcebreak char(1) not null,--Force interruption flag 
remark varchar(256) not null,
)
--Create a database named 'sql_test'
create database sql_test
go 
--Open the database sql_test
use sql_test
go
--Establish the student table
create table student
(student number char(4) primary key, student name varchar(50)not null)
go
--modify the student table
alter table student 
add class number char(4) null --add class number field
-- (Note: If the added field is not empty, it cannot be added)
go
--Establish the class table
create table class
(class number char(4) primary key, class name varchar(50)not null)
go
--Establish the course table
create table course
(course number char(4) primary key, course name varchar(50) not null, course start date datetime )
go
--modify the course table
alter table course
add course code varchar(10) null --add course code field
go
alter table course
drop column course start date --delete the course start date field
go
alter table course
alter column course name varchar(20) not null --Modify the course name field
go
--Establish a product_test_one table, similar to the next table, but there is a comma before the constraint which does not affect execution
create table product_test_one
(
id char(10) not null, name varchar(20) null, price money default 20.5,quantity smallint null, constraint pk_id primary key clustered (id)
)
go
--Establish a product_test_two table
create table product_test_two
(
id char(10) not null, name varchar(20) null, price money default 20.5,quantity smallint null constraint pk_id2 primary key clustered (id)
)
go
--Delete the table pruduct_test_one
drop table product_test_one
go
--Establish a student table so that the name field within it has uniqueness
create table student 
(
id char(8), name char(10) --table field
constraint pk_id primary key (id), --Add a primary key constraint 
constraint uk_name unique (name) --Add a uniqueness constraint
)
go
--Establish a student4table, as above (Note: There must be a comma between constraints, otherwise an error will occur!)
create table student4 
(
id char(8), name char(10) --table field
constraint pk_id4 primary key (id), constraint uk_name4 unique (name)
)
go
-- Delete the table student4
drop table student4
go
--Establish a student3table, as above
create table student3
(
id char(8), name char(10), --table field
constraint pk_id3 primary key (id), constraint uk_name3 unique (name)
)
go
--Delete the table student3
drop table student3
go
--constraint constraint_name check(logical condition expression)
--Create a 'employee' table so that the sex field (sex) can only accept 'm' or 'f', and cannot accept other data
--and create a check constraint for the phone field, restricting input to similar values like 0108564712such data, rather than inputting other data arbitrarily
create table 员工
(
id char(5),name char(20),sex char(2),phone int
constraint pk_zid primary key (id), --There must be a ‘comma’ separated , defining the primary key constraint
constraint chk_sex check (sex in (‘f‘,‘m‘) ),
constraint chk_phone check (phone like ‘(010) [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]‘)
)
go
--constraint constraint_name default constraint_expression [for field_name]
-- Create a table named ‘default constraint’ and create a default constraint for the field sex
create table default constraint
(
id char(5) primary key ,sex varchar(2) constraint con_sex default ‘m‘ 
)
go
--Modify the ‘default constraint’ table
alter table default constraint
add name varchar(10)null constraint con_name default ‘Hello Baby‘ --Add a field named ‘name’ with a default value of ‘Hello Baby'
go
--Add to the class table8record
insert into class values(‘bj01‘,‘一班‘)
insert into class values(‘bj02‘,‘二班‘)
insert into class values(‘bj03‘,‘三班‘)
insert into class values(‘bj04‘,‘四班‘)
insert into class values(‘bj05‘,‘五班‘)
insert into class values(‘bj06‘,‘六班‘)
insert into class values(‘bj07‘,‘七班‘)
insert into class values(‘bj08‘,‘八班‘)
go
--Display all records of classes
select * from class
go
--Delete records from the class table where class_id is greater than bj06records
delete from class where class_id > ‘bj06"
go
--Display all records of classes
select * from class
go
--Add records to the student table
insert into student values(‘xs01‘,‘one‘,‘bj01)
insert into student values(‘xs02‘,‘two‘,‘bj01)
insert into student values(‘xs03‘,‘three‘,‘bj01)
insert into student values(‘xs04‘,‘four‘,‘bj02)
insert into student values(‘xs05‘,‘five‘,‘bj03)
insert into student values(‘xs06‘,‘six‘,‘bj02)
insert into student values(‘xs07‘,‘seven‘,‘bj04)
insert into student values(‘xs08‘,‘eight‘,‘bj03)
insert into student values(‘xs09‘,‘nine‘,‘bj04)
insert into student values(‘xs10‘,‘ten‘,‘bj05)
insert into student values(‘xs11‘,‘eleven‘,‘bj06)
insert into student values(‘xs12‘,‘twleve‘,‘bj06)
go
--Display all records of students
select * from student
go
--Connection query
select * from student, class where student.class_id = class.class_id
go
--The effect is the same as the previous one
--Selected connection query
select student.student_id, class.class_id, student.student_name, class.class_name from student, class where student.class_id = class.class_id
go
--The effect is the same as the previous one
--Query the students in class one
select* from student where class number in (select class number from class where class number='bj01)
go
--The same function as the above query statement
select a.student number, a.student name, a.class number from student as a, class as b where a.class number=b.class number and b.class number='bj01"
go
--Count the number of students in class one
select count(student number) as student statistics from student 
where class number in (select class number from class where class number='bj01)
go
--Usage of group and count() function
--Count the number of students in class one and display the student's name and class
select count(student number) as student statistics, student name, class number from student 
where class number in (select class number from class where class number='bj01)
group by class number, student name
go

The above is the operation method of creating a database table in SqlServer introduced by the editor (creating a database, creating a table, modifying statements), hoping it will be helpful to everyone. If you have any questions, please leave a message, and the editor will reply to everyone in time. We are also very grateful for everyone's support for the Yana Tutorial website!

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#w3Please report via email to codebox.com (replace # with @) with relevant evidence. Once verified, the website will immediately delete the infringing content.