SQL Server 数据库操作完整指南

1. 数据库与表操作 (DDL)

1.1 数据库操作

-- 创建数据库createdatabasehm0707;-- 删除数据库dropdatabasehm0707;

1.2 表操作

-- 创建表-- create table 表名(字段名 数据类型,字段名 数据类型，...字段名不限)-- int整数 decimal小数 char字符-- varchar字符串 datetime时间日历createtablestudent(idint,namevarchar(50),salarydecimal(5,2),-- 代表数字可以写5位 最多有2位是小数lasttimedatetime);-- 删除表droptablestudent;-- 修改表名execsp_rename'student','stu';-- 修改表中的字段名execsp_rename'student.lasttime','time','column';-- 删除表中的字段altertablestudentdropcolumntime;-- 向表中添加字段信息（只能添加到最后）altertablestudentaddtimedatetime;

2. 数据增删改查 (DML)

2.1 插入数据

-- 全表添加insertintostudentvalues(1,'abc',23.45,'2026-07-07 15:20:34');-- 指定字段添加insertintostudent(name,salary)values('aa',56);

2.2 查询数据

-- 全表查询select*fromstudent;-- 查询指定字段selectname,salaryfromstudent;-- WHERE条件查询select*fromstudentwhereid=1;

2.3 更新数据

-- 修改语句updatestudentsetsalary=60whereid=1;

2.4 删除数据

-- 删除语句deletefromstudentwherename='aa';

3. 高级查询与聚合

3.1 逻辑运算符

-- AND同时满足select*fromempwheredeptno=10andsal>1000;-- OR满足其中之一selectename,deptno,salfromempwheredeptno=10orsal>2000;-- 区间查询selectename,salfromempwheresal>1300andsal<3000;-- BETWEEN AND区间查询selectename,sal,deptnofromempwheresalbetween1300and3000anddeptno=10;

3.2 NULL值处理

-- 查询没有提成的员工selectename,commfromempwherecommisnotnull;

3.3 IN操作符

-- 查询编号为7369，7499，7521selectename,empnofromempwhereempnoin(7369,7499,7521);

3.4 模糊查询

-- LIKE模糊查询-- % 代表0个或者多个字符 _下划线代表一个字符-- 查询名字中包含a的员工selectempno,enamefromempwhereenamelike'%a%';-- 查询名字中第二个字符是A的员工selectempno,enamefromempwhereenamelike'_a%';

3.5 别名与去重

-- 别名selectempnoas'员工编号'fromempwhereempno=7900;-- 去重selectdistinct(job)fromemp;

3.6 排序

-- 排序（默认升序，降序加DESC）selectempno,salfromemporderbysaldesc;-- 多字段排序selectempno,sal,deptnofromemporderbydeptno,saldesc;-- 条件排序selectempno,sal,deptnofromempwheredeptno=20andsal>2000orderbysaldesc;

4. 子查询与视图

4.1 子查询

-- 1. 查询拿最高工资的员工姓名selectename,salfromempwheresal=(selectmax(sal)fromemp);-- 2. 查询工资高于平均工资的员工selectename,salfromempwheresal>(selectavg(sal)fromemp);-- 3. 查询工资高于20号部门最高工资的所有员工select*fromempwheresal>(selectmax(sal)fromempwheredeptno=20);-- 4. 查询和jones相同职位的员工信息select*fromempwherejob=(selectjobfromempwhereename='jones');-- 5. 复杂子查询：求emp表中除了名字为king的人中年薪最高的员工select*from(select*,12*(sal+ISNULL(comm,0))asnxfromempwhereename!='king')s1wheres1.nx=(selectmax(s.nx)from(select*,12*(sal+ISNULL(comm,0))asnxfromempwhereename!='king')s);

4.2 视图

-- 创建视图createviewtas(select*,12*(sal+ISNULL(comm,0))asnxfromempwhereename!='king');-- 使用视图查询selectmax(nx)fromt;select*fromtwherenx=(selectmax(nx)fromt);

5. 连接查询

5.1 内连接（等值连接）

-- 查询所有的员工信息和对应的部门地点selecte.*,d.*fromemp e,dept dwheree.job='salesman'ande.deptno=d.deptno;

5.2 外连接

-- 右外连接selecte.*,d.*fromemp erightjoindept done.deptno=d.deptno;

5.3 自连接

-- 一张表的连接selecte.ename,e.empno,e.mgr,d.ename,d.empnofromemp e,emp dwheree.mgr=d.empno;

6. 聚合函数与分组

6.1 聚合函数

-- 聚合函数：max最大、min最小、avg平均值、sum求和、count统计-- 查询emp表中工资最高的工资selectmax(sal)as'最高工资'fromempwherejob='salesman';-- 查询emp表中日期最小的值selectmin(hiredate)as'最小年龄'fromemp;-- 查询emp表中的平均工资selectavg(sal)as'平均工资'fromemp;-- 查询emp表中工资的总和selectsum(sal)as'工资的总和'fromemp;-- 查询员工的数量selectcount(ename)as'员工的数量'fromemp;

6.2 分组查询

-- GROUP BY分组（一般和聚合函数一起使用）-- 求每个部门的平均工资selectdeptno,avg(sal)asafromempgroupbydeptno;-- 求每个工作岗位的人数selectjob,count(job)ascfromempgroupbyjob;-- 求每个部门工资大于1500的平均工资selectdeptno,avg(sal)asafromempwheresal>1500groupbydeptno;-- 求每个部门工资大于1500的平均工资的升序selectdeptno,avg(sal)asafromempwheresal>1500groupbydeptnoorderbyaasc;-- 多字段分组排序selectdeptno,avg(sal)asa,count(sal)ascfromempwheresal>1500groupbydeptnoorderbycdesc,aasc;

6.3 HAVING子句

-- 查询每个部门的平均工资，只查询工资大于1800的员工并且查询平均工资大于2000的selectdeptno,avg(sal)asafromempwheresal>1800groupbydeptnohavingavg(sal)>2000orderbya;-- 查询每个部门名字里包含a的员工的平均工资，只显示平均工资高于2000的selectdeptno,avg(sal)asafromempwhereenamelike'%a%'groupbydeptnohavingavg(sal)>2000;-- 复杂分组查询selectdeptno,avg(sal)asa,count(*)ascfromempgroupbydeptnohavingavg(sal)>2000orderbya;

6.4 复杂分组示例

-- 查询emp表，名字不是以k开头的信息-- 每个部门的员工最低工资高于1000的部门编号，-- 工资总和，平均工资以及最低工资 根据平均工资排序selectdeptno,avg(sal)asa,sum(sal)ass,min(sal)asmfromempwhereenamenotlike'k%'groupbydeptnohavingmin(sal)>1000orderbya;-- 查询部门编号是10，30的部门员工中每个职业中-- 最高工资低于5000的职业名称，人数，-- 平均工资，最高工资，根据人数排序-- 如果人数一致根据最高工资降序排序selectjob,count(*)asc,avg(sal)asa,max(sal)asmfromempwheredeptno=10ordeptno=30groupbyjobhavingmax(sal)<5000orderbyc,mdesc;-- 查询emp表中，工资在1000-3000的员工信息，-- 每个部门的编号，工资总和，平均工资，-- 过滤掉平均工资低于2000的部门，平均工资升序排序-- 如果一致则按照工资总和排序selectdeptno,sum(sal)ass,avg(sal)asafromempwheresalbetween1000and3000groupbydeptnohavingavg(sal)>=2000orderbya,sdesc;

7. 约束与索引

7.1 约束类型

-- 主键约束(包含非空和唯一) 非空 唯一 检查 外键-- 主键自增 IDENTITY(i,j) i初始值 j步长-- not null非空 identity(1,1)主键自增 primary key主键-- unique 唯一 check检查约束createtablehh(idintidentity(1,1)primarykey,namevarchar(20)unique,sexintcheck(sex=0orsex=1));droptablehh;insertintohhvalues(null,1);select*fromhh;

7.2 外键约束

-- 外键约束insertintoemp(deptno)values(50);select*fromemp;-- 对deptno设置外键约束：只能是有的部门你才能添加-- 主表：主表里面一定有一个主键createtablezhu(idintprimarykey);-- constraint 约束名 foreign key (本表的字段) references 主表(字段)createtablefu(idint,kidint,constraintyu1foreignkey(kid)referenceszhu(id));insertintozhuvalues(1);deletefromzhuwhereid=1;insertintofuvalues(1,1);droptablefu;

7.3 索引与查询优化

-- 索引：提高查询效率，一般是给字段设置索引-- 二分查找优点：查询效率快-- 缺点：占数据库内存-- 如何优化查询速度：使用WHERE条件，不要全表扫描

8. 存储过程与事务

8.1 存储过程

-- 存储过程：类似方法CREATEPROCEDUREAddMillionRowsASBEGINSETNOCOUNTON;DECLARE@iINT=0;WHILE@i<10000BEGININSERTINTOzhuVALUES(@i)SET@i=@i+1;ENDEND-- 执行存储过程EXECAddMillionRowsselectcount(*)fromzhu

8.2 事务控制

-- TCL事务控制语言commit;-- 提交事务rollback;-- 回滚事务

9. 分页与日期函数

9.1 分页查询

-- 分页查询select*fromemporderbysaloffset0rowsfetchnext5rowsonly;-- TOP简单分页（只能查询前面的几条数据）selecttop2*fromemporderbysaldesc;

9.2 日期函数

-- 日期函数：YEAR、MONTH、DAYselectmonth(hiredate),hiredatefromemp;

10. Visual Studio 连接实战

10.1 连接SQL Server

10.2 创建SQL中的表

10.3 插入数据

10.4 查询数据

10.5 修改数据

10.6 删除数据