SQL Server 学习

SQL Server 是微软公司提供的一款关系型数据库。由于C#也是微软官方提供的语言所以我们C#可以直接连接SQL Server使用而不需要其他导入文件。一. SQL 概述1.1 语法关键字不区分大小写。多行或单行书写用结尾。可使用空格缩进增强可读性。1.2 分类DDLData Definition Language数据定义语言用来定义数据库对象库、表、列等DMLData Manipulation Language数据操作语言用来定义数据库记录数据DCLData Control Language数据控制语言用来定义访问权限和安全级别DQLData Query Language数据查询语言用来查询记录数据。二. DDL1. 创建数据库//创建数据库语法CREATE DATABASE database_name[ ON[ PRIMARY ] [ filespec [ ,...n ][ , filegroup [ ,...n ] ]][ LOG ON { filespec [ ,...n ] } ][ COLLATE collation_name ][ WITH external_access_option ]]-- 创建最简单的数据库 CREATE DATABASE MyDatabase; -- 完整示例指定文件参数 CREATE DATABASE MyDatabase ON PRIMARY ( NAME MyDatabase_Data, FILENAME C:\soft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf, SIZE 10MB, MAXSIZE 50MB, FILEGROWTH 5MB ) LOG ON ( NAME MyDatabase_Log, FILENAME C:\soft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase.ldf, SIZE 5MB, MAXSIZE 25MB, FILEGROWTH 5MB );2. 数据库基本操作-- 查看所有数据库 SELECT name FROM sys.databases; -- 查看当前连接的数据库 SELECT DB_NAME(); --切换数据库 use testdb; -- 删除数据库 DROP DATABASE [IF EXISTS] 数据库名称 //直接删除数据库 DROP DATABASE MyDatabase //判断删除数据库是否存在 drop database if EXISTS MyDatabase;3. 数据类型数值类型bit1位存储0、1或NULLtinyint1bytes0到255smallint2bytes-32,768到32,767int4bytes(-2147483648 2147483647)bigint8bytes(-2^632^63-1)float(n)浮点数n为精度(1-53)real4bytes4字节浮点数相当于float(24)decimal(p,s)固定精度和小数位数p(1-38)是总位数s是小数位数money8字节默认4位小数字符串类型类型大小描述char(n)1-8,000字符固定长字符串(需要指定长度),非Unicodevarchar(n)1-8,000字符变长字符串(需要指定长度),非Unicodevarchar(max)最大2^31-1字节变长字符串(需要指定长度),非Unicodenchar(n)1-4,000字符固定长字符串(需要指定长度),Unicodenvarchar(n)1-4,000字符变长字符串(需要指定长度),Unicodenvarchar(max)最大2^30-1字符可变长度Unicodebinary(n)1-8,000字节固定长度二进制varbinary(n)1-8,000字节可变长度二进制varbinary(max)最大2^31-1字节可变长度二进制日期类型类型范围格式描述date日期0001-01-01到9999-12-31yyyy-MM-dd日期值time(n)n为小数秒精度(0-7)hh:mm:ss时间值datetime1753-01-01到9999-12-31精度3.33ms 8字节yyyy-MM-dd hh:mm:ss日期时间值datetime2(n)6-8字节0001-01-01到9999-12-31精度100nsn为小数秒精度(0-7)yyyy-MM-dd hh:mm:ss日期加时间smalldatetime4字节1900-01-01到2079-06-06精度1分钟yyyy-MM-dd hh:mm:ss日期加时间4. 数据表操作----创建stu表 CREATE TABLE stu ( sid NCHAR(6), sname NVARCHAR (20), age INT, gender NVARCHAR (10) ) ; -- 查看当前数据库中所有表名称 SELECT name AS TableName FROM sys.tables -- 查看表结构 EXEC sp_help emp; -- 查看emp表结构 -- 删除表 DROP TABLE emp; --删除emp表 -- 修改表 -- 1. 修改之添加列给stu表添加classname列 ALTER TABLE stu ADD classname NVARCHAR(50); -- 2. 修改之修改列类型修改stu表的gender列类型为CHAR(2) ALTER TABLE stu ALTER COLUMN gender CHAR(2); -- 3. 修改之修改列名修改stu表的gender列名为sex EXEC sp_rename stu.gender, sex, COLUMN; -- 4. 修改之删除列删除stu表的classname列 ALTER TABLE stu DROP COLUMN classname; -- 5. 修改之修改表名称修改stu表名称为student EXEC sp_rename stu, student, OBJECT;三. DMLSql语句的增删改操作Insert Delect Update----插入数据向stu表指定的四个列插入记录 INSERT INTO stu (sid, sname, age, gender) VALUES (s_1001, zhangSan, 23, male); ---往stu表的所有列插入记录:按创建表时列的顺序插入所有列的值 INSERT INTO stu VALUES(s_1001, zhangSan, 23, male); ---插入多条记录 INSERT INTO stu VALUES(s_1001, zhangSan, 23, male) (s_1001, zhangSan, 23, male), (s_1001, zhangSan, 23, male);UPDATE stu SET snamezhangSanSan, age32, genderfemale WHERE sids_1001; UPDATE stu SET ageage1 WHERE snamezhaoLiu;DELETE FROM stu WHERE sids_1001; DELETE FROM stu WHERE snamechenQi OR age 30; -- 备份表 SELECT * INTO stu_backup FROM stu; --删除所有 慎用 DELETE FROM stu;四. DQL语法SELECT [TOP n /*结果限定*/election_list /*要查询的列名称*/FROM table_list /*要查询的表名称*/WHERE condition /*行条件*/GROUP BY grouping_columns /*对结果分组*/HAVING condition /*分组后的行条件*/ORDER BY sorting_columns /*对结果排序*/DISTINCT 去重关键字DQL语句执行顺序from -- where -- group by -- having -- select -- order by1. 基础查询----查询所有列SELECT * FROM stu;-----查询指定列SELECT sid, sname, age FROM stu;-----去重查询SELECT DISTINCT job FROM emp;2. 条件查询1. 关键字与逻辑运算符、!、(不等于)、、、、BETWEEN…AND 相当于 num1xnum2IN(set) 在这个集合中的数据 IN (num1,num2)IS NULL 空AND 并且 (多个条件同时成立)OR 或者 (多个条件任意一个成立)NOT 非 , 不是2. 优先级NOT AND OR----查询性别为女并且年龄50的记录 SELECT * FROM stu WHERE gender female AND age 50 ; ---查询学号为S_1001或者姓名为liSi的记录 SELECT * FROM stu WHERE sid S_1001 OR snameliSi; ---查询学号为S_1001S_1002S_1003的记录 SELECT * FROM stu WHERE sid IN (S_1001,S_1002,S_1003); ---查询学号不是S_1001S_1002S_1003的记录 SELECT * FROM stu WHERE sid NOT IN (S_1001,S_1002,S_1003); ---查询年龄为null的记录 SELECT * FROM stu WHERE age IS NULL; ---查询年龄在20到40之间的学生记录 SELECT * FROM stu WHERE age20 AND age40; -- 或者 SELECT * FROM stu WHERE age BETWEEN 20 AND 40; ----查询性别非男的学生记录 SELECT * FROM stu WHERE gender!male; -- 或者 SELECT * FROM stu WHERE gendermale; -- 或者 SELECT * FROM stu WHERE NOT gendermale; ----查询姓名不为null的学生记录 SELECT * FROM stu WHERE NOT sname IS NULL; -- 或者 SELECT * FROM stu WHERE sname IS NOT NULL;3.模糊查询当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。_匹配任意一个字母。“%”匹配0~n个任何字母。----查询姓名由5个字母构成并且第5个字母为“i”的学生记录 SELECT * FROM stu WHERE sname LIKE ____i; -----查询姓名中包含“a”字母的学生记录 SELECT * FROM stu WHERE sname LIKE %a%;4. 排序1.语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ,...;2. 排序方式DESC 降序ASC 默认升序3. 规则如果是多字段排序先根据字段1排序当字段1的值相同时再根据字段2排序。----查询所有雇员按月薪降序排序如果月薪相同时按编号升序排序 SELECT * FROM emp ORDER BY sal DESC,empno ASC;5. 分组分组查询时需要使用GROUP BY子句支持多字段查询。分组之后查询字段一般为聚合函数或分组字段一般字段不能查询。语法SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];having与where的区别执行时机不同where是分组之前进行过滤不满足where条件不参与分组而having是分组之后对结果进行过滤。执行顺序where聚合函数having判断条件不同where不能对聚合函数进行判断而having可以。----查询每个部门的部门编号以及每个部门工资大于1500的人数 SELECT deptno,COUNT(*) FROM emp WHERE sal1500 GROUP BY deptno; ----查询工资总和大于9000的部门编号以及工资和 SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) 9000;6. 分页查询SQL Server2012及以上版本推荐使用OFFSET-FETCH 子句。1. OFFSET-FETCH语法SELECT 列名FROM 表名ORDER BY 排序列OFFSET (页码-1)*每页行数 ROWSFETCH NEXT 每页行数 ROWS ONLY;-- 示例查询第2页每页5条 SELECT * FROM emp ORDER BY sal OFFSET 5 ROWS -- 跳过前5条(第1页) FETCH NEXT 5 ROWS ONLY; -- 取第6-10条(第2页)2. Top简单分页2.1 返回前number条记录查询语法SELECT TOP (number|percent) [WITH TIES] 列名FROM 表名[WHERE 条件][ORDER BY 排序列];-- 返回前10条产品记录 SELECT TOP 10 * FROM emp;2.2 Top子句分页查询分页语法SELECT TOP (页码-1)*每页行数 *FROM (SELECT TOP 页码*每页行数 *FROM 表名ORDER BY 列名) AS 别名ORDER BY 列名 desc;-- 示例查询第2页每页5条 SELECT TOP 5 * FROM ( SELECT TOP 10 * -- 前2页的总行数 FROM emp ORDER BY sal ) AS Temp ORDER BY sal desc;7. 多表联查7.1 联合查询要求查询的两个表的列数列类型必须相同。7.1.1 并集UNION去除重复记录。UNION ALL不去除重复记录。----将薪资低于 2000 的员工 , 和 10号部门的员工全部查询出来. select * from emp where sal 2000 union select * from emp where deptno 10;7.1.2 交集INTERSECT 是 SQL Server 提供的标准交集运算符返回两个查询结果中都存在的。INTERSECT 会自动去除重复行。结果集的列名取自第一个查询。select * from emp_10 intersect select * from emp_clerk;7.1.3 差集EXCEPT 是 SQL Server 的标准差集运算符返回第一个查询结果中存在但第二个查询结果中不存在的行。 EXCEPT 会自动去除重复行。select * from emp_10 except select * from emp_clerk;7.2 连接查询7.2.1 内连接查询结果必须满足条件SELECT * FROM emp e INNER JOIN dept d ON e.deptnod.deptno;7.2.2 外连接查询出的结果存在不满足条件的可能。1. 左外连接先查询出左表即以左表为主然后查询右表右表中满足条件的显示出来不满足条件的显示NULL。SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptnod.deptno;查询emp表存在dept表不存在的记录2. 右外连接先把右表以右表为主中所有记录都查询出来然后左表满足条件的显示不满足显示NULL。SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptnod.deptno;查询emp表不存在dept表存在的记录7.2.3 全外连接返回左表和右表中的所有记录当没有匹配时用NULL填充缺失的一侧。select * from emp e full join dept don e.deptno d.deptno7.3 子查询子查询就是嵌套查询即SELECT中包含SELECT如果一条语句中存在两个或两个以上SELECT那么就是子查询语句了。1. 分类按位置where后作为条件的一部分from后作为被查询的一条表SELECT之后 ,作为被查询的一列;按查询结果标量子查询子查询结果为单个值列子查询(子查询结果为一列)表子查询(子查询结果为多行多列)2. 子查询关键字ANY 任意ALL 全部//标量子查询 ----查询工资高于ALLEN的员工。 SELECT * FROM emp WHERE sal (SELECT sal FROM emp WHERE enameALLEN) //列子查询 ----工资高于30部门所有人的员工信息 SELECT * FROM emp WHERE sal ALL (SELECT sal FROM emp WHERE deptno30) ----查询比SALES部其中任意一人工资高的员工信息 select * from emp where sal any ( select sal from emp where deptno (select deptno from dept where dname SALES) ); //表子查询 ----查询员工编号为7369的员工名称、员工工资、部门名称、部门地址 SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptnod.deptno AND e.empno7369五. 约束关键字PRIMARY KEY 主键约束 唯一标识符 不能为null 一张表只能有一个主键FOREIGN KEY 外键约束 建立两张表的联系UNIQUE 唯一约束 数据唯一不重复NOT NULL 非空约束 字段不能为nullDEFAULT 默认约束 未指定字段数据的默认值CHECK 检查约束 保证字段满足条件-----为表设置主键 CREATE TABLE stu ( //第一种方式 sid CHAR(6) PRIMARY KEY, sname VARCHAR (20) NOT NULL, //非空约束 age INT UNIQUE check(age 15 and age 30), //唯一约束检查约束 gender VARCHAR (10) , //第二种方式 primary key(sid) //组合主键 primary key(sid, age) ) ;外键约束1. 在创建表时添加外键CREATE TABLE 表名 (字段名 数据类型,...[ CONSTRAINT ] [ 外键名称 ] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名));2. 创建表后添加外键ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;----第一种方式 CREATE TABLE dept ( id INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR (50) NOT NULL sid INT, FOREIGN KEY(sid) REFERENCES Emp (sid) ) ; ---第二种方式 alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);六. 题目分享接下来我分享两个我的做题思路1.查询学员信息1因为学员信息与考试科目成绩这些数据是不在同一个表中的但成绩表与学员表中的StuID有联系所以我们可以根据这个StuID字段来查询相关的学员信息。2这里我采用的方法是子查询方式通过先把成绩表中我们所需的列查询出来再作为一个表来连接查询。3为什么不使用连接查询连接查询需要把两个数据表中所有记录汇总成笛卡儿积这对于数据大的表来说效率低所以我们先查询再连接。2. 按班级名称分组查询每个班级的平均成绩1这里需要3个表连接查询因为要查询学员信息和成绩所以我们需要连接学员表与成绩表来查询出相关学员的成绩信息。2但因为要查询班级平均成绩所以我们还需要对班级编号sClassID进行分组操作查询出每个班级的平均成绩。3把查询的平均成绩与班级编号作为副表进行表连接查询根据班级编号查询出班级名称与班级平均成绩。---1) 查询学员信息要求显示姓名学号考试科目考试成绩并按照考试成绩降序和学号升序排序。 ---(提示每个学员的信息都要求显示不论是否有参加考试) SELECT StuCard,StuName,e.ExamSubject,e.ExamResult FROM StudentInfo s,(SELECT EStuID,ExamSubject,ExamResult FROM StudentExam ) e WHERE s.StuIDe.EStuID; ---2) 按班级分组查询各个班级学员的平均成绩。 SELECT c.ClassNumber, AVG(se.ExamResult) AS 平均成绩 FROM ClassInfo c INNER JOIN StudentInfo s ON c.ClassID s.SClassID INNER JOIN StudentExam se ON s.StuID se.EStuID GROUP BY c.ClassNumber; ---3) 按班级名称分组查询每个班级的平均成绩。(提示3 表连接) SELECT c.ClassNumber,sub.平均成绩 FROM ClassInfo c,( SELECT s.SClassID,AVG(e.ExamResult) AS 平均成绩 FROM StudentInfo s JOIN StudentExam e ON e.EStuIDs.StuID GROUP BY s.SClassID ) sub WHERE c.ClassIDsub.SClassID; ---4) 查询所有 3 月份入学男学员按所在班级名称分组要求显示每个班级的考试平均分并按从高到低排列。 ----(提示3 表连接) SELECT c.ClassID,sub.平均成绩 FROM ClassInfo c,( SELECT s.SClassID,AVG(e.ExamResult) AS 平均成绩 FROM StudentInfo s JOIN StudentExam e ON s.StuIDe.EStuID WHERE MONTH(s.StuJoinTime)3 AND s.StuSex 男 GROUP BY s.SClassID ) sub WHERE sub.SClassIDc.ClassID ORDER BY sub.平均成绩 DESC; ---5) 查询每个班主任所带的学员信息要求显示班主任姓名班主任连系电话班级名称学员姓名学员学号。 ----(提示3 表连接) SELECT t.TeacherName,t.TeacherTel,s.ClassNumber,s.StuName,s.StuCard FROM TeacherInfo t, ( SELECT s.StuName,s.StuCard,c.ClassNumber,c.CTeacherID FROM StudentInfo s JOIN ClassInfo c ON c.ClassIDs.SClassID ) s WHERE t.TeacherID s.CTeacherID; ---6) 按班主任姓名分组查所带班级的总成绩(假定每个班主任只带一个班级)。(提示4 表连接) SELECT te.TeacherName, st.TotalScore AS 总成绩 FROM ( SELECT t.TeacherName, c.ClassID FROM TeacherInfo t, ClassInfo c WHERE t.TeacherID c.CTeacherID ) te, ( SELECT s.SClassID, SUM(se.ExamResult) AS TotalScore FROM StudentInfo s, StudentExam se WHERE s.StuID se.EStuID GROUP BY s.SClassID ) st WHERE te.ClassID st.SClassID;七. 总结在Sql Server学习过程中最重要的一点就是多去写sql语句毕竟语法与关键字就那么多其中查询操作比较复杂一些。查询操作中多表联查是一个难点我们前面查询只是单表查询而在多表查询中可能涉及到2表3表甚至是多表查询这就需要你融会贯通前面所学的知识了对于子查询来说我觉得可以把一个多表查询拆分成几个单表查询或者2表查询这样会更容易看懂。八. 面试DQL语句执行顺序where与having的区别nvarchar与nchar有什么区别有几种约束