sinEagle

bottlecode

0%

SQL Server --Table

发表于 更新于
本文字数： 7k 阅读时长 ≈ 6 分钟

建一个学生表和成绩表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table student (
    stu_name nvarchar(50) NOT NULL,
    [stu_id] [int] NOT NULL, 
    [stu_sex] [nchar](10) NOT NULL, 
    stu_age int NOT NULL, 
    stu_email nvarchar(50) NOT NULL, 
    stu_phone int NOT NULL
)

create table score (
    score_stuid int NOT NULL,
    score_val int NOT NULL
)

在表中添加一列

1
alter table student add stu_number int NOT NULL

在表中删除一列

1
alter table student drop column stu_number

修改表中的一列

1
2
alter table student 
alter column stu_sex nchar(2) NOT NULL

identity

设置主键约束

1
2
alter table student
    add constraint pk_stuid primary key(stu_id)

检查约束(假设stu_number 必须是3位或5位)

1
2
3
alter table student 
add constraint ck_stunumber check(len(stu_number) = 3 or len(stu_number) = 5)

创建表的时候添加主键外键和约束

1
2
3
4
5
6
7
8
9
10
11
12
13
create table dept(
    dept_id int primary key, 
    dept_name nvarchar(100) not null,
    dept_address nvarchar(100)
)

create table emp (
    emp_id int constraint pk_id_emp primary key, 
    emp_name nvarchar(50) NOT NULL,
    emp_sex nchar(1), 
    dept_id int constraint fk_dept_id_ foreign key references dept(dept_id)

)

约束

可以分为主键约束，外键约束，check约束, default约束, unique约束

主键约束

不允许重复元素，避免数据的冗余

外键约束

关联的另一个事务一定存在

check约束

保证事务的取值在合法范围之内

1
2
3
4
5
6
create table student (
    stu_id int primary key,
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000),
)
insert into student	values (1, 1500) --正确
insert into student values (1, 999) --会报错

default约束

保证事物的属性一定有一个值

1
2
3
4
5
6
7
8
9
10

create table student (
    stu_id int primary key,
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000),
    stu_sex nchar(1) default ('男') --()可以省,字符串必须用''括起来
)
--drop table student
insert into student	(stu_id, stu_sal) values (1, 1500)
insert into student (stu_id, stu_sal) values (2, 1299) 
insert into student (stu_id, stu_sal, stu_sex) values(3, 3434, '女')

unique 约束

表示事物的属性的取值不允许重复，但可以为空, 且只允许一列为空

1
2
3
4
5
6
create table student (
    stu_id int primary key,
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000), 
    stu_sex nchar(1) default ('男'), 
    stu_name nvarchar(200) unique 
)

*几种创建表的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--2021-10-15 08:22:37
--第二种写法
create table grade(
    gradeid  int identity(1, 1) primary  key not null,
    gradename nvarchar(50) not null
)

create table stu (
    stuno int identity(1000, 1) constraint pk_student_stmo primary key not null,
    name nvarchar(50) not null,
    id varchar(18) not null constraint ck_student_id check(len(id) <= 15 or len(id) >= 18),
    gender nchar(2) not null constraint ck_student_gender check(gender = '男' or gender = '女'),
    [address] nvarchar(500) constraint df_student_address  default '山东工商学院',
    email varchar(50) constraint ck_studnet_email check(email like '%@%.%'),
    gid int not null constraint fk_students_grade references grade(gradeid), 
)
drop table stu
drop table grade

alter table stu drop constraint df_student_address
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--第三种写法
create table grade(
    gradeid  int identity(1, 1) primary  key not null,
    gradename nvarchar(50) not null
)

create table stu (
    stuno int identity(1000, 1)  not null,
    name nvarchar(50) not null,
    id varchar(18) not null ,
    gender nchar(2) not null,
    [address] nvarchar(500) constraint df_student_address  default '山东工商学院',
    email varchar(50),
    gid int not null , 

    constraint pk_student_stmo primary key(stuno),
    constraint ck_student_id check(len(id) <= 15 or len(id) >= 18),
    constraint ck_student_gender check(gender = '男' or gender = '女'),
    constraint ck_studnet_email check(email like '%@%.%'),
    constraint fk_students_grade foreign key(gid) references grade(gradeid)

)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--第四种写法
create table grade(
    gradeid  int identity(1, 1) primary  key not null,
    gradename nvarchar(50) not null
)

create table stu (
    stuno int identity(1000, 1)  not null,
    name nvarchar(50) not null,
    id varchar(18) not null ,
    gender nchar(2) not null,
    [address] nvarchar(500) constraint df_student_address  default '山东工商学院',
    email varchar(50),
    gid int not null , 
)
drop table stu
drop table grade

alter table stu add	constraint pk_student_stmo primary key(stuno)
alter table stu add	constraint ck_student_id check(len(id) <= 15 or len(id) >= 18)
alter table stu add	constraint ck_student_gender check(gender = '男' or gender = '女')
alter table stu add	constraint ck_studnet_email check(email like '%@%.%')
alter table stu add	constraint fk_students_grade foreign key(gid) references grade(gradeid)

*课程作业创建一个学生表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
create table grade (
    grade_id int not null,
    grade_name varchar(100) 
)
alter table grade add constraint pk_grade_stmo primary key(grade_id)

create table subjectx(
    subject_id int not null,
    subject_name varchar(100),
    subject_hour int,
    sub_grade_id int 
) 
alter table subjectx add constraint fk_subjectx_grade foreign key(sub_grade_id) references grade(grade_id)
alter table subjectx add constraint pk_subjectx_stmo primary key(subject_id)

create table student (
    student_id varchar(20) not null, 
    stu_password varchar(10), 
    stu_name nvarchar(10),
    stu_identity varchar(18),
    stu_gender nvarchar(2),
    birthday datetime,
    phone varchar(20),
    email varchar(50), 
    stu_address varchar(50), 
    stu_grade_id int
)
--drop table student 
alter table student add constraint pk_student_stmo primary key(student_id)
alter table student add constraint fk_student_grade foreign key(stu_grade_id) references grade(grade_id)

create table score (
    score_id varchar(20) not null, 
    student_id varchar(20), 
    subject_id int, 
    score int,
    test_time datetime 
)
--drop table score
alter table score add constraint pk_score_id_stmo primary key(score_id)
alter table score add constraint fk_score_student foreign key(student_id) references student(student_id) 
alter table score add constraint fk_score_subject foreign key(subject_id) references subjectx(subject_id)
drop table score 
drop table subjectx
drop table student 
drop table grade

*几种插入操作

1
2
3
4
5
6
insert into grade(gradename) values('sdfsd')
insert into grade(gradename) 
select '一年级' union all
select '二年级' union all
select '三年级 ' union all
select '四年级 ' 
1
insert into student (stuno, stu_name, gender, [address], grade_id) values('1001', '张三', '男', '烟台', 1)

显示表中的数据

1
select * from table_name

*2021-10-22 09:29:56 乱七八糟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
update grade set gradename = '啊啊啊' where gradeid = 1
select * from grade
insert grade values('五年级')
delete from grade where gradename = '五年级'

create table stu1(
    stuid varchar(50), 
    stuName nvarchar(50)
)
drop table stu1
select * from sheet1$
select * from stu1
insert into stu1 (stuid, stuName) select 上映年份,导演 from sheet1$
select 上映年份 stuid,导演 stuName into stu2 from sheet1$
select * from stu2

多对多关系及其实现

多对多必须通过单独的一张表实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table banji (
    banji_id int primary key, 
    banji_num int not null,
    banji_name nvarchar(100)
)

create table jiaoshi (
    jiaoshi_id int primary key,
    jiaoshi_name nvarchar(200)
)

create table banji_jiaoshi_mapping (
    banji_id int constraint fk_banji_id foreign key references banji(banji_id),
    jiaoshi_id int foreign key references jiaoshi(jiaoshi_id), 
    kecheng nvarchar(20),
    constraint pk_banji_id_jiaoshi_id primary key (banji_id, jiaoshi_id, kecheng)
)
drop table banji_jiaoshi_mapping

*查询

1
2
3
4
5
6
7
select banji_num, banji_id, banji_name from banji
select * from banji
select banji_id from banji

select * from jiaoshi 
select jiaoshi_id+10 from jiaoshi

*第五章作业

5-2:

1
2
3
4
5
6
7
8
9
10
11
create table  bank (
    bankID int primary key,
    bank_name nvarchar(50),
    bank_address nvarchar(50),
    bank_phone varchar(50) 
)
--drop table bank

insert bank values ('1','模拟银行重庆分行','重庆市重庆路123号', '023-76543210')
insert bank values ('2','模拟银行四川分行','四川市成都路123号', '023-12345678')
insert bank values ('3','模拟银行天津分行','天津市天津路654号', '022-67890123')
求大佬赏个饭