目录
本文是【SqlServer学习基础】指南
🍉一、数据库的基本使用(菜鸟阶段1--\(˙<>˙)/--)
【1】基本的创建表
CREATE TABLE Student
(
stu_id VARCHAR(3) NOT NULL,
stu_name VARCHAR(4) NOT NULL,
stu_sex VARCHAR(2) NOT NULL,
stu_birthday DATETIME NOT NULL,
stu_class VARCHAR(5)
)
CREATE TABLE Course
(
cou_id VARCHAR(5) NOT NULL,
cou_name VARCHAR(10) NOT NULL,
tea_id VARCHAR(10) NOT NULL
)
CREATE TABLE Score
(
stu_id VARCHAR(3) NOT NULL,
cou_id VARCHAR(5) NOT NULL,
sco_degree NUMERIC(10, 1) NOT NULL
)
【2】基本的插入
--插入student
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (108, '曾华', '男', '1977-09-01', 95033)
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (105, '匡明', '男', '1975-10-02', 95031)
--插入course
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('3-105', '计算机导论', 825)
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('3-245', '操作系统', 804)
--插入score
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (103, '3-245', 86)
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (105, '3-245', 75)
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (109, '3-245', 68)
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (103, '3-105', 92)
【3】基本的查询数据
-- 1、 查询student表的所有记录。
select * from Student
-- 2、 查询student表中的所有记录的stu_name、stu_sex和stu_class列。
select st.stu_name,st.stu_sex,st.stu_class from student st
-- 3、 查询score表中成绩在60到80之间的所有记录。(where)
select *from Score sc where sco_degree>60 and sco_degree<80
-- 4、 以stu_id升序、sco_degree降序查询score表的所有记录。
-- order by 用来升序,后加desc为降序
select * from score order by stu_id,sco_degree desc
-- 5、查询‘3-105’号课程的平均分。
-- avg函数用来计算平均成绩
select avg(sco_degree) from score where cou_id='3-105'
🍍二、多表数据库(菜鸟阶段2--\(˙<>˙)/--)
【1】多表查询
****inner join 多表连接,只取中间交集****
**************left join 多表连接,以左侧表为主**************
**************右侧没有并包含左侧表的数据用NULL补全*****************
**************right join 多表连接,以右侧表为主**************
**************左侧没有的值并包含右侧表的数据NULL补全*****************
--inner join
select * from Student st
inner join Score sc
on sc.stu_id=st.stu_id
--left join
select * from Student st
left join Score sc
on sc.stu_id=st.stu_id
--right join
select * from Student st
right join Score sc
on sc.stu_id=st.stu_id
【2】Group By 分组
Group By + 你想进行分组的列名
select stu_class,count(stu_sex) from Student group by stu_class
group by stu_class
翻译:我想根据stu_class进行分组
select stu_class,count(stu_sex) from Student
翻译:我想让stu_class每组显示其对应的count(stu_sex)【每组的stu_sex个数】
【3】distinct去重
去除重复的行数据
select distinct * from Student