create database python_test1;
use sqlserver_test1;
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
);
create table classes(
id int primary key identity(1,1) not null,
name varchar(30) not null
);
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;
--select 字段 as 别名 from 表名
select name as '姓名' from students;
--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;
--查询20-28之间的所有学生信息
select * from students where age>20 and age<28;
--SELECT TOP number|percent column_name(s) FROM table_name;
select TOP 5 * from students where gender='男'
--like %替换一个或多个 _替换一个
--查询姓名中以'小'开头的同学
select name from students where name like '小%';
--查询名字为两个字的同学
select name from students where name like '__';
--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 姓名='李三' --改