<>SQL server database Table basic operation
* Database operation command system create establish alter modify drop delete insert add to update to update delete delete select
query
*
Create Library
*
create database t223
on primary
(name=‘t223.mdf’,
filename=‘E:\data\t223.mdf’,
size=20mb,
filegrowth=10mb,
maxsize=200mb
)
*
Create table
*
Connect to the database first
use Library name
*
establish
create table Table name （id int,name varchar（5）,sex char（2）,date datetime）
notes ： int 4 Byte storage char: Fixed length non-linear Unicode character , maximum 8000 character , When the data does not meet the defined length , Add space to occupy . Fast reading and writing speed , It takes up a lot of space .
varchar: Non fixed length Unicode character , maximum 8000 character , Do not add space occupying spaces . date ： date , Format year - month - day , time ： time , hour : minute :
second datetime: Date time year - month - day hour : minute : second
*
Write data
*
insert into biao1 values add table 1 Data for （ first line ）
(1,‘zs’,‘ male ’,‘2020-10-19’) surface 1 First line
insert into biao1 values add table 1 Data for （ The second line ）
(2,‘xx’,‘ female ’,‘2021-10-19’) surface 1 The second line
*
Batch write data
*
insert into biao1 (id,name,xb,age,csny,jg,gl,tel)
select 1,‘ Zhang San ’,‘ male ’,18,‘2003-01-01’,‘ Beijing ’,4,111
union select 2,‘ Li San ’,‘ male ’,28,‘2002-01-01’,‘ Shanxi ’,5,222
union select 3,‘ Li Si ’,‘ female ’,38,‘2001-01-01’,‘ Shanghai ’,6,333
*
Add modify field
*
Add field seniority , data type int, Nonempty constraint
alter table biao1 add gl int not null
* Modify field name sex( Gender ) by xb exec sp_rename 'biao1.sex','xb'
* Add to table tel field , Used to enter the employee number alter table biao1 add tel char(20)
* Create a new table , to id Field add foreign key constraint create table biao2 (id int not null , constraint zw_id
foreignkey(id) references biao1(id))
<> Add constraints
1. Add non empty constraint
alter table Table name alter column Field name data type not null
This command can modify the data type of a field individually , When modifying the data type, it should conform to the compatibility of the existing data , If the table is empty, you can modify it at will .
* Add primary key constraint
First add a non NULL constraint to the field , Then add the primary key constraint
alter table Table name add constraint Constraint name primary key( Field name )
3. Add uniqueness constraint
alter table Table name add constraint Constraint name unique( Field name )
4. Add check constraint
alter table Table name add constraint Constraint name check( expression )
5. Add default constraint
alter table Table name add constraint Constraint name default ' Default value ' for Field name
6. Add foreign key constraint
alter table Table name add constraint Constraint name foreign key( Set foreign key fields ) references Reference table name ( Reference field name )
* Modify data
* update update Table name set Field name = Modified value where query criteria
* View table structure exec sp_help Table name
<> Small expansion （ dried food ）
* example create table biao1 (id int primary key, --int： The integer is four bytes Create primary key -- name char(8)
unique, --unique Unique constraint -- age int check(age>=18 and age<=30), --check Check constraints （ Greater than or equal to
18 Less than and equal to 30）-- sex char(2)check(sex=' male 'or sex=' female '), --char Fixed length characters check（ Male or female ）-- jg
varchar(20)not null, --varchar Variable length character not null Nonempty constraint -- gz money, --money Currency character --
zwvarchar(20))
* Write multiple lines of data （union） insert into biao1 select 1,' king gx',20,' male ',' Lu Liang ',100000,' Zhongdan '
union select 2,' Zhang San ',30,' female ',' Shanxi ',1000,' On the list ' union select 3,' Li Si ',19,' male ',' earth ',10000,
' Fight wild '
* query select biao1.name from biao1 where age>18and age<30 -- From table 1 Older than middle age 18 less than 30
Find the name in the parameter of -- select biao1.name,biao1.jg,biao1.gz from biao1 where zw=' On the list ' -- From table 1
Find the name, native place and salary in the parameters of the previous document -- select biao1.name,biao1.zw from biao1 where gz<10000 -- From table 1
The average wage is less than 10000 Find the name and position in the parameter -- select biao1.zw,biao1.name from biao1 where sex=' female 'and
age>18 -- From table 1 The gender is female and the age is older than 18 Find the position name in the parameter of -- select biao1.gz from biao1 where zw=' Fight wild '
-- From table 1 Find the salary in the parameters with the position as field -- select biao1.jg,biao1.gz,biao1.age from biao1 where zw=
' On the list ' -- Find the native place, salary and age from the parameters with the position as the previous document — notes ： The detailed query of database and table will be continuously updated later
Technology