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(
190107119,
'name',
100
);

3, Using insert statement classes

//Insert a statement that must correspond to a field before it can be used
insert into xs values(
190107119,
'name',
100
);

4, Set foreign key class

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

① Add foreign keys when modifying

Syntax:
# 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
)charset='utf8mb4';

-- 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
smallint
longint
char class
char(2)
vchar(2)

② 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);