sql server basic command statement

1, Create library and table classes

create database a;//Create database
//Create table
create table name(
 Field name char(10),
 Field name 2  char(10)
drop Command is also the method of use

2, Modify table data

alter sentence
//Delete column
alter table student drop column Field name;
//Add column
alter table student add column Field name;

If used

use a;
create table xs(
xh char(10),
mz char(10),
cj char(10)
insert into xs values(

3, Using insert statement classes

//Insert a statement that must correspond to a field before it can be used
insert into xs values(

4, Set foreign key class

Conditions: ① – > with quoted content
② The referenced content is unique

① Add foreign keys when modifying

# Add foreign key: the 'SubjectId' field in the 'Score' table is associated with the 'SubjectId' field in the 'Subject' table
alter table `Score` add constraint score_subject 
foreign key(`SubjectId`)
references `Subject`(`SubjectId`);

② Create foreign key

-- Parent table
create table if not exists `father_table`(
    `test_field1` int(4) primary key

-- Sub table
create table if not exists `child_table`(
    `test_field1` int(4),
    foreign key(`test_field1`) references `father_table`(`test_field1`)
)charset='utf8mb4';//That is to create two fields for association

5, Set cascade operation

For example, there are two tables a and B

#Create a table with
create table a
id  varchar(20) primary key,
password varchar(20) not null

#Create b table with
create table b
id int identity(1,1)  primary key,#identity(1,1) means to add one from one
name varchar(50) not null,
userId varchar(20),
foreign key (userId) references a(id) on delete cascade
#That is, if the id data in table a is deleted, the useid 11 in table b will also be deleted
#Such as executing statements
insert a values ('11','aaa')
insert a values('23','aaa')
insert b values('da','11')
insert b values('das','11')
insert b values('ww','23')
delete a where id='11'#That is, while deleting the data with id a, the data with b data '11' will also be deleted

Basic knowledge

① Data type

int class
char class

② Control domain command
1. Not null (indicates a statement that is neither – > nor empty)
2.check statement – > check statement

checki(Ssex='male'or Ssex='female')

3. Default – > Default
Existing default parameters

Ssex char(2) default 'male'

4. Method of setting primary key in a table – > setting method of primary attribute

primary key (sno)

5. Wildcard
*Number -- > is used for wildcards on database segments
%And -- > Query methods that can only be used for fields

③ Error type

① Message 4145, level 15, state 1, line 5 specifies a non Boolean expression in the context in which the condition should be used (near ').
Typical reasons – > Chinese symbols may be used in both sentences
② The new relationship must have at least one pair of related columns – > when the table internal relationship class is set, that is, uniqueness is not set
Solution – > set uniqueness in index creation to solve the problem

Message 4104, level 16, status 1, line 4
Cannot bind multi part identifier 'FIRST.cpno'.
Message 4104, level 16, status 1, line 2
Cannot bind multi part identifier 'FIRST.Cno'.
Cause: – > character class error

④ Several query type classes
1. Equivalent connection query
(that is, all are queried)

select * from Studnet ,SC where Studnet.Sno=SC.sno;
#That is, the contents of the two tables that meet this condition
# That is, use commas to separate connection queries

2. Natural connection query
(i.e. connect after the attribute is determined)

select Student.Sno,Sname,Ssex,Ssage,Sdept,Cno,Grade  from Student,SC where Student.Sno=SC.Sno;

3. Self connection query
Pre courses before querying pre courses – > that is, the content of the query is queried through its own connection

select FIRST.Cno,SECOND.cpno from Course FIRST,Course SECOND where FIRST.cpno=SECOND.Cno;

4. Nested connection

#nice for control range
#Such as code 1
select sno from Course where sno in (select sno from Student );
#Code 2
select name from person 
where countryid in 
select countryid from country
where countryname = 'Wei '

Job code class

create database jxgl;
use jxgl;
create table Student
Sno char(5),
Sname varchar(20),
Ssex char(2),
Sage int,
Sdept char(2)
create table Course
Cno char(2),
Cname varchar(20),
Cpno char(2),
Ccredit int

create table SC
Sno char(5),
Cno char(2),
Grade int 
 insert into Student values('98001','Qian Heng','male',18,'CS');
 insert into Student values('98001','Zhao San','female',20,'IS');
 insert into Student values('98002','Xiaobai','male',19,'CS');
 insert into Course values('1','database system','5',4);
 insert into Course values('2','network security','3',2);
 insert into SC values('98001','1',87); 
 insert into SC values('98002','2',95);

Keywords: Database SQL Server SQL

Added by aeafisme23 on Fri, 04 Feb 2022 14:23:43 +0200