-- 查询指定列
select StudentName, Age from Students;

-- 查询所有列（正式代码中建议明确列出列名）
select * from Students;

-- as 为列指定别名（as 可省略）
select StudentName as 姓名, Age as 年龄 from Students;

-- distinct 去除重复行
select distinct ClassId from Students;

-- top N 只返回前 N 行
select top 2 StudentName from Students;

-- 查询 20 岁及以上的学员
select StudentName, Age from Students where Age >= 20;

-- 多条件组合
where Age >= 20 and Gender = N'男'
where ClassId = 1 or ClassId = 2

-- between 闭区间，包含两端，等价于 Age >= 20 and Age <= 25
where Age between 20 and 25

-- in 匹配列举的任一值，等价于 ClassId = 1 or ClassId = 2
where ClassId in (1, 2)

where StudentName like N'张%'       -- 以"张"开头：张三、张三丰
where StudentName like N'%三'       -- 以"三"结尾
where StudentName like N'%三%'      -- 包含"三"
where StudentName like N'张_'       -- 姓张且姓名共两个字

where ClassId is null              -- 查询尚未分班的学员
where ClassId is not null          -- 查询已分班的学员
where ClassId = null               -- 错误写法，永远匹配不到任何行

-- 按年龄降序
select StudentName, Age from Students order by Age desc;

-- 先按班级升序，同班再按年龄降序
order by ClassId asc, Age desc;

-- 跳过 10 行，取 5 行，即每页 5 条时的第 3 页
select StudentName, Age from Students
order by StudentId
offset 10 rows fetch next 5 rows only;

select count(*) as 总人数, avg(Age) as 平均年龄, max(Age) as 最大年龄
from Students;

-- 统计每个班级的学员人数
select ClassId, count(*) as 人数
from Students
group by ClassId;

-- 查询人数超过 1 人的班级
select ClassId, count(*) as 人数
from Students
group by ClassId
having count(*) > 1;

select s.StudentName, s.Age, c.ClassName
from Students s
inner join StudentClass c on s.ClassId = c.ClassId;

select s.StudentName, c.ClassName
from Students s
left join StudentClass c on s.ClassId = c.ClassId;

-- 查询年龄大于平均年龄的学员
select StudentName, Age from Students
where Age > (select avg(Age) from Students);

-- 1. where 中配合 in：查询"软件1班"的所有学员
select StudentName from Students
where ClassId in (select ClassId from StudentClass where ClassName = N'软件1班');

-- 2. from 中作为派生表（必须指定别名）
select t.ClassId, t.人数
from (select ClassId, count(*) as 人数 from Students group by ClassId) t
where t.人数 > 1;

-- 3. where 中配合 exists 做存在性判断
select StudentName from Students s
where exists (select 1 from Scores sc where sc.StudentId = s.StudentId);

select StudentName, Age from Students where ClassId = 1
union
select StudentName, Age from Students where ClassId = 2;

-- 单行插入：列名与值一一对应
insert into Students (StudentName, Gender, Age, ClassId)
values (N'孙七', N'女', 21, 2);

-- 多行插入
insert into Students (StudentName, Gender, Age)
values (N'钱八', N'男', 23), (N'周九', N'女', 22);

-- 将查询结果插入另一张表（常用于数据备份或迁移）
insert into StudentsBackup (StudentName, Age)
select StudentName, Age from Students where Age > 20;

update Students
set Age = 26, ClassId = 2
where StudentId = 1001;

delete from Students where StudentId = 1001;

truncate table Students;   -- 清空整表

create database StudentManageDB;    -- 创建数据库
use StudentManageDB;                -- 切换到指定数据库
drop database StudentManageDB;      -- 删除数据库

create table Students
(
    StudentId   int identity(1000,1) primary key,         -- 自增主键：从 1000 开始，每次加 1
    StudentName nvarchar(20) not null,
    Gender      nchar(1) not null,
    StudentIdNo varchar(18) not null unique,
    Age         int check(Age between 0 and 150),
    PhoneNumber varchar(11),
    StudentAddress nvarchar(200) default N'地址不详',
    ClassId     int foreign key references StudentClass(ClassId)
);

-- 修改表结构
alter table Students add Email varchar(50);            -- 添加列
alter table Students alter column Email varchar(100);  -- 修改列类型
alter table Students drop column Email;                -- 删除列

drop table Students;                                   -- 删除表

cast('123' as int)                        -- 字符串转数值
cast(Age as varchar(10)) + N'岁'          -- 数值转字符串后拼接
convert(varchar(10), getdate(), 120)      -- 日期转字符串，120 对应格式 yyyy-mm-dd hh:mi:ss

-- isnull(列, 替代值)：列为 NULL 时返回替代值
select StudentName, isnull(PhoneNumber, '未填写') from Students;

-- coalesce(值1, 值2, ...)：返回第一个非 NULL 的值
select coalesce(PhoneNumber, Email, '无任何联系方式') from Students;

where StudentName = N'张三'    -- 正确：按 Unicode 处理，中文不乱码
where StudentName = '张三'     -- 可能乱码或匹配失败

StudentId int identity(1000, 1) primary key   -- 种子为 1000，增量为 1

begin try
    begin transaction;
    update Accounts set Balance = Balance - 100 where AccountId = 1;
    update Accounts set Balance = Balance + 100 where AccountId = 2;
    commit;        -- 全部成功：提交，修改永久生效
end try
begin catch
    rollback;      -- 任一步骤失败：回滚，撤销所有修改
end catch

from → join/on → where → group by → having → select → distinct → order by → top/offset

select AdminName from Admins where LoginId = {0} and LoginPwd = '{1}'

SqlCommand cmd = new SqlCommand("select AdminName from Admins where LoginId=@id and LoginPwd=@pwd", conn);
cmd.Parameters.AddWithValue("@id", objAdmin.LoginId);
cmd.Parameters.AddWithValue("@pwd", objAdmin.LoginPwd);