SQLServer数据库操作指南-优快云博客

创建数据库sqlserver_test1

create database python_test1;

使用数据库

use sqlserver_test1;

创建students表

create table students(
id int primary key identity(1,1) not null,
name varchar(30) default ' ',
age tinyint default 0,
height decimal(5,2),
gender varchar(10) check (gender in ('男','女','保密')) default '保密',
cls_id int default 0,
is_delete bit default 0
);

在这里插入图片描述

创建classes表

create table classes(
id int primary key identity(1,1) not null,
name varchar(30) not null
);

在这里插入图片描述

增加数据,注意主键使用自动增加时必须用0代替

insert students values
( '小明', 18, 180.00, '男', 1, 0),
( '小月月', 18, 170.00, '女', 2, 0),
( '彭于晏', 20, 183.50, '男', 1, 0),
( '刘德华', 35, 178.00, '男', 1, 0),
( '黄蓉', 34, 156.00, '女', 1, 0),
( '凤姐', 25, 158.00, '女', 2, 0),
( '王祖贤', 20, 170.00, '女', 1, 0),
( '周杰伦', 26, 175.00, '男', 1, 0),
( '陈坤', 20, 175.00, '男', 2, 0),
( '刘亦菲', 20, 163.00, '女', 1, 0),
( '金星', 26, 164.00, '保密', 1, 0),
( '静香', 24, 165.00, '女', 2, 0),
( '郭靖', 30, 181.00, '男', 1, 0),
( '周杰', 36, 182.00, '男', 2, 0),
( '蔡徐坤', 22, 181.00, '男', 1, 0);

在这里插入图片描述

insert into classes values( '一期'),( '二期'),( '一期');

查询

查询所有字段

--select * from 表名
select * from students;

在这里插入图片描述

select * from classes;

在这里插入图片描述

查询指定字段

--select 列1，列2... from 表名
select name, age from students;

在这里插入图片描述

使用as给字段起别名

--select 字段 as 别名 from 表名
select name as '姓名' from students;

在这里插入图片描述

使用as给表起别名

--select 别名.字段 ... from 表名 as 别名;
select s.name from students as s;

消除重复行

--select distinct 字段 from 表名;
select distinct gender from students;

在这里插入图片描述

比较运算符> < = >= <=

--select ... from 表名 where 条件
--查询年龄大于20的学生信息
select * from students where age>20;

在这里插入图片描述

逻辑运算符 and or not

--查询20-28之间的所有学生信息
select * from students where age>20 and age<28;

在这里插入图片描述

查询指定数量 TOP

--SELECT TOP number|percent column_name(s) FROM table_name;
select TOP 5 * from students where gender='男'

在这里插入图片描述

模糊查询 like rlike

--like   %替换一个或多个    _替换一个
--查询姓名中以'小'开头的同学
select name from students where name like '小%';

在这里插入图片描述

--查询名字为两个字的同学
	select name from students where name like '__';

在这里插入图片描述

范围查询 in , not in , between…and… , not between…and…

--in(3,6,9)表示在一个非连续的范围内
--查询年龄为18,22,26的同学
select * from students where age in(18, 22, 26);

在这里插入图片描述

--between ... and ...表示在一个联系的范围内
	select * from students where age between 25 and 35;

在这里插入图片描述

排序

--order by 字段
--asc升序    desc降序
--select ... from 表名 where 条件 order by 字段 asc/desc(, 字段2 asc/desc);
--若只有一个排序字段，当出现相同大小的数据时，默认按照主键排序
	--查询18-34之间的男性，按照年龄从小到大排序
	select * from students where (age between 18 and 34) and gender='男' order by age asc;

在这里插入图片描述

聚合函数

--计数 count   查询满足条件的个数  
	--查询男性有多少人
	select count(*) as 男性人数 from students where gender='男';

在这里插入图片描述

--最大值  max
	--查询年龄最大的男性
	select max(age) as 男性最大年纪 from students where gender='男';

在这里插入图片描述

--最小值   min
--平均值   avg
--求和    sum
--四舍五入    round( ,n)
	--计算所有人年龄的平均值，并取2位小数
	select round(sum(age)/count(*), 2) as '平均年龄' from students;

在这里插入图片描述

分组

--分组一般和聚合函数联合使用

--group by
	--计算每种性别的人数
	select count(*) as '性别分类' from students group by gender;

在这里插入图片描述

--查询年龄大于30的人数和小于30的人数各多少
	select age as '年龄',count(*) as '总数' from students  group by age;

在这里插入图片描述

链接查询

--inner join ...on
--select * from 表1 inner join 表2 on 条件
	--查询 有能够对应班级的同学及班级信息
	select * from students inner join classes on students.cls_id=classes.id;

在这里插入图片描述

--通过给表起别名方便查询,查询各个班里的同学姓名
	select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;

在这里插入图片描述

--查询对应班级的同学信息，按照班级排序，同一个班级按照ID排序
select c.name ,s.* from classes as c inner join students as s on c.id=s.cls_id order by c.name desc, s.id asc

在这里插入图片描述

--左连接  表1 left join 表2 on 条件
	--查询每位同学的班级信息,先将周杰和静香的班级改为4
	update students set cls_id=4 where name='周杰'or name='静香';
	select * from students left join classes on students.cls_id=classes.id;

在这里插入图片描述

 
--如果使用inner join 则看不到null值的数据
	select * from students inner join classes on students.cls_id=classes.id;

在这里插入图片描述

--使用right join则以右边的表为主，左边没有对应数据的显示null
	select * from students right join classes on students.cls_id=classes.id;

在这里插入图片描述

视图

--创建视图,查询每个学生的班级信息并保存在视图中
create view cls_message
as
select s.*,c.name as '班级' from students as s right join classes as c on s.cls_id=c.id;

在这里插入图片描述

--通过视图cls_message查询每位同学的班级信息
go
select name,班级 from cls_message;

在这里插入图片描述

--删除视图
Drop view cls_message

游标

游标的作用是完成一个事务，一个事物中的所有操作要么都完成，要不都不进行，确保数据的一致性。

--创建游标
declare cur_Student Cursor
for
select id,name,age from students;
--打开游标
open cur_Student;

--读取游标
fetch next from cur_Student
while @@FETCH_STATUS=0
begin
fetch next from cur_Student
end
--关闭游标
close cur_Student
deallocate cur_Student

在这里插入图片描述

存储过程

--存储过程：
--存储过程的优点：1.编译后在服务器端 2.一次编译多次调用 3.具有一定的数据安全性
--创建存储过程--
create proc pro @xuehao varchar(10)=null   --创建存储过程pro 一个参数@xuehao 后面是它的类型
as                                         --这个as超重要，不能漏写
if @xuehao=null                            --如果是null,返回10
    return 10
if not exists(select * from 学生 where 学号=@xuehao)    --如果找不到就返回-10
    return -10
select 姓名 from 学生 where 学号=@xuehao               --找到这个人，返回0（不知道你们有没有注意到，这些是按照顺序排列的，不能变顺序）
    return 0

--执行存储过程--
declare @num int                      --定义一个变量来接收结果
exec @num=pro 'S0101'                 --调用存储过程
if @num=10                            --如果是10，那就说明你输入的不对
 select '输入错误'
else 
    if @num=-10                        --10就是没有这个人，0就是有这个人
      select '没有这个人'
    if @num=0
      select '找到了'

触发器

--触发器
--创建一个触发器之后，只要被触发就会执行相应的操作，就是这么简单，而触发条件也很简单，无非就是三种，增删改create trigger tr_test --创建触发器
create trigger chufaqi
on 学生 for insert,update,delete           --触发条件，增删改
as
select * from inserted                   --inserted和deleted是数据库系统在触发的时候自动创建的两个表，inserted表存放了插入和更新数据时的变化
select * from deleted                    --deleted存放了删除的时候的信息


insert into 学生(学号,姓名,性别)             --插入数据，增
values('S1112','李三','男')

delete from 学生                           --删
where 姓名 = '李三'
update 学生  set 性别='女' where 姓名='李三'      --改