Detailed explanation of SQL Server database table operation method

Creation of tables

Code Operation

-- StudentTwo Library name
use StudentTwo
go

-- table surface-- database database 存放surface-- 先判断surface是否存在，If there is delete first and then create
-- sysobjects surface-- 判断所有系统surface有没有一个surface名字为number，If deleted
if exists(select * from sysobjects where name = 'nunber')
drop table number --Delete
go

create table XueShengTable(
	-- studentID Who is plastic surgery identity Identifier function from1000start Increase each time1 ，以后添addstudent信息时候不要标识列添add
	StudentId int identity(1000,1),

	-- Name
	StudentName varchar(20) not null,

	-- gender
	Gender char(2) not null,
)
Notice: No added after the column not null，addnullYes, the current column can be null，addnot nullThe current column cannot be null

Interface operation

Click the database --> Click the data used to view --> Right-click the table --> Create a new table

Add, delete, modify and check

increase

explain:

-- Insert and add students

-- insert into table name (column name) values ​​(value)

-- What is the corresponding value of which column is inserted into which table

-- Note: Column names are separated by commas, the values ​​and column names are one by one, and the types must also match.

Writing method 1:

insert into XueShengTable(StudentName,Gender,Binrthday,Age,Phone,StudentAddress,ClassId,StudentCard) values('Tiga','male','2000-09-18',14,'15377300008','Dengzhou City, Nanyang City, Henan Province',2341,12345678910987)

Writing 2:

-- You can also omit the column name

insert into XueShengTable values('Convex Man','male','2000-09-18',12345678910987,23,'15377300008','Dengzhou City, Nanyang City, Henan Province',1)

delete

-- grammar

delete from table name where conditions

Method 1delete:

-- Delete the student number 1001 data

delete from XueShengTable where StudentId = 1001

-- The second deletion scheme truncate:

truncate table XueShengTable -- Delete the entire table

-- Tip: Delete deletes faster than truncate. -- Delete deletes this record not allowed to be referenced by foreign keys. You can first delete the relationship of the foreign key and then delete this data. -- truncate requires that the deleted table cannot have foreign key constraints.

change

-- grammar:

update table name set column name = value, column name = value where condition

--Change the student number to 1,000 students' name to change to Li Bai, date of birth

update XueShengTable set StudentName = 'Li Bai',Binrthday='1975-01-01' where StudentId=1000

check

-- Query all information similar to array traversal

select * from XueShengTable

-- Query the data of the specific column and query the name of this column. Select column name, from table name select StudentName,Gender from XueShengTable

-- Conditional query: Query the select column name with age equal to 23, column name from table name where Age = 23

select StudentName from XueShengTable where Age = 23

-- Query all columns that meet the criteria

select * from XueShengTable where Age <= 23

--Logical operator symbol and and relationship is equivalent to &&, or or condition is equivalent to ||;

select * from XueShengTable where Age>14 and Age<23

Summarize

This is the end of this article about the operation method of SQL Server database tables. For more detailed explanations of SQL Server tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!