【数据库】SQL Server零基础学起你必会（三）九种简单查询【史上最全】

SQL Server零基础学起你必会（三）九种简单查询【史上最全】

• s c o t t scott scott表介绍
• 查询操作
• • 计算列
  • d i s t i n c t distinct distinct[不允许重复的]
  • b e t w e e n between between[在某个范围]
  • i n in in[属于若干个鼓励的值]
  • t o p top top[最前面的若干记录]
  • • 输出工资在1500~3000的前四个人信息
  • n u l l null null[没有值,空值]
  • • 例题
  • o r d e r    b y order\,\,by orderby[以某个字段排序]
  • 模糊查询
  • • 注意
  • 聚合函数
  • • 函数的分类
    • m a x ( ) , m i n ( ) , a v g ( ) max(),min(),avg() max(),min(),avg()
    • c o u n t count count
• 相关知识链接
• 利用SQL命令建表和约束
• 六大约束
• • 实战
  • • 指令合集
    • 实例流程
• 关系
• • 实战
  • • 指令合集
    • 实例流程
  • 查看数据关系的建立
  • 关系图
• 总结
• • • 主键
    • 外键
    • 先删主键表还是外键表
• 相关知识链接

s c o t t scott scott表介绍

• d e p t dept dept部门表
  
• e m p emp emp员工表
  
• S A L G R A D E SALGRADE SALGRADE工资薪级表
  

查询操作

查询最重要的是顺序

计算列

计算: 年 薪 = 表 中 数 据 的 月 薪 ∗ 12 年薪=表中数据的月薪*12 年薪=表中数据的月薪∗12

select *from emp
--*表示所有的
--from emp表示从emp表中查询
select empno,ename from emp;
select ename,sal from emp;
select ename,sal*12 as "年薪",sal "月薪",job from emp;
--as可以省略,年薪要加双引号
select 5 from emp
--输出的表是emp表的行数,每行只有一个字段,值是5

在 O r a c l e Oracle Oracle中字段的别名不允许用单引号括起来，但是 S q l S e r v e r   2005 SqlServer\,2005 SqlServer2005却允许,因此为了兼容性用双引号

d i s t i n c t distinct distinct[不允许重复的]

• 普通 s e l e c t select select表格多少条记录搜索到多少个
  
• 如果不想重复
  

select distinct deptno from emp;
select distinct comm from emp;
--distinct可以过滤掉重复的null
select distinct comm,deptno from emp;
--把comm和deptno的组合进行过滤
--select deptno distinct comm from emp;--逻辑上有冲突

b e t w e e n between between[在某个范围]

--查找工资在1500—3000之间的所有员工信息
select * from emp
where sal=5000
select * from emp
where sal>=1500 and sal<=3000
select * from emp
where sal between 1500 and 3000

i n in in[属于若干个鼓励的值]

select *from emp where sal in (1500,3000,5000)
select *from emp where sal not in (1500,3000,5000)
--等价于
select *from emp
where sal<>1500 and sal <>3000 and sal<>5000
--数据库不等于有两种表示:<> !=推荐使用第二种
--对或取反是并且 对并且取反是或

t o p top top[最前面的若干记录]

select top 5*from emp
select top 15 percent *from emp--
--输出的是三个不是两个,输出前百分之多少的数据,是小数的话向大取整

输出工资在1500~3000的前四个人信息

--输出工资在1500~3000的前四个人信息--ascending(默认)升序--descending降序
select top 4*
from emp
where sal between 1500 and 3000
order by sal desc--降序

n u l l null null[没有值,空值]

select *from emp
--输出奖金非空的员工信息
select *from emp where comm<>null--输出为空,error
select *from emp where comm!=null--输出为空,error
--总结:null不能参加<>和!=运算
select *from emp where comm=null

• 0 和 n u l l 0和null 0和null是不一样的, n u l l null null表示空值,没有值,0表示一个确定的值
• n u l l null null不能参加如下运算<>和!=和=
• n u l l null null可以参加如下运算:   i s       n o t   i s \,is\,\,\,\,\,not \,is isnotis
• 任何类型的数据都允许为 n u l l null null
  

create table t1(name nvarchar(20),cnt int,riqi datetime);
insert into t1 values(null,null,null)
select *from t1

例题

输出每个员工的姓名,年薪(包括奖金)     c o m m \,\,\,comm comm假设是一年的奖金

select empno,ename,sal*12+comm "年薪" from emp

本程序证明了: n u l l null null不能参与任何数据运算，否则结果永远为空

• 改进：

select empno,ename,sal*12+isnull(comm,0) "年薪" from emp
--isnull(common),如果comm是null返回0,否则返回comm的值

o r d e r    b y order\,\,by orderby[以某个字段排序]

指令	作用
o r d e r   b y   a , b order\, by\, a,b orderbya,b	a , b a,b a,b都是升序
o r d e r   b y   a , b   d e s c order\, by\, a,b\,desc orderbya,bdesc	a a a升序, b b b降序
o r d e r   b y   a   d e s c , b order\,by\, a \,desc,b orderbyadesc,b	a a a降序 b b b升序

文字描述:如果不指定排序的标准，则默认是升序，升序用 a s c asc asc表示,默认可以不写,为一个字段指定的排序标准并不会对另一个字段造成影响,强烈建议为每一个字段都制定排序的标准

--asc升序排序,默认可以不写--desc降序
select *from emp order by sal--默认按照升序排序
select *from emp order by deptno,sal
--先按照deptno升序排序,如果相同则按照sal升序排序
select *from emp order by deptno desc,sal
--order by a desc,b,c,d只对a产生影响

模糊查询

格式	s e l e c t   select\, select字段的集合   f r o m   \,from\, from 表名   w h e r e   \,where\, where 某个字段的名字   l i k e   \,like\, like 匹配的条件

匹配的条件通常具有通配符,通配符明细如下:

• %:表示任意零个或多个字符

select *
from emp
where ename like '%A%'--ename只要含有字母A就输出

select *
from emp
where ename like 'A%'--ename只要首字母是A就输出

select *
from emp
where ename like '%A'--ename只要尾字母是A就输出

• _:表示任意单个字符
  

select *from emp where ename like '_A%' --ename只要第二个字母是A就输出

• ‘ [ a − f ] [a-f] [a−f]’: a a a到 f f f中的任意单个字符,只能是 a , b , c , d , e , f a,b,c,d,e,f a,b,c,d,e,f中的任意一个

select *from emp where ename like '_[A-F]%' 
--ename中第二个字母是A或B或C或D或E或F中的任意一个记录输出

• ‘ [ a , f ] [a,f] [a,f]’: a a a或 f f f
• ‘_ [ ∧ a − c ] [\wedge a-c] [∧a−c]’:把 e n a m e ename ename中不是 a a a也不是 b b b也不是 c c c的任意单个字符记录输出

select *from emp where ename like '_[^A-F]%' 
--ename中第二个字母是A或B或C或D或E或F中的任意一个记录输出

注意

• 匹配的条件必须用单引号括起来,不能省略,也不能改用双引号
• 通配符作为不同字符使用的问题,如%已经作为特殊字符,而要查%怎么办，解决方法如下:
  e s c a p e   ′ ∖ ′ escape\,'\setminus' escape′∖′ 是将 ′ ∖ ′ '\setminus' ′∖′作为转义字符
  

create table student
(
	name varchar(20) null
	,age int
);

insert into student values ('张三', 88);
insert into student values ('Tom', 66);
insert into student values ('a_b', 22);
insert into student values ('c%d', 44);
insert into student values ('abc_fe', 56);
insert into student values ('haobin', 25);
insert into student values ('HaoBin', 88);
insert into student values ('c%', 66)
insert into student values ('long''s', 100)
select * from student;

select * from student where name like '%\%%' escape '\'  --把name中包含有%的输出 
select * from student where name like '%\_%' escape '\' --把name中包含有_的输出

聚合函数

函数的分类

分类	作用
单行函数	每一行返回一个值
多行函数	多行返回一个值，聚合函数是多行函数

select LOWER(ename) from emp;--最终返回的是行lower
select max(sal) from emp;--返回max是多行函数

m a x ( ) , m i n ( ) , a v g ( ) max(),min(),avg() max(),min(),avg()

c o u n t count count

分类	作用
c o u n t ( ∗ ) count(*) count(∗)	返回表中所有的记录个数
c o u n t ( 字 段 名 ) count(字段名) count(字段名)	返回字段值非空的记录个数,重复记录的也为有效记录,空记录为无效记录

select COUNT (*)from emp--返回emp所有记录的个数
select COUNT (deptno)from emp--返回值是14,deptno中重复的记录也被当为有效的记录
select COUNT (distinct deptno) from emp--返回值是3,统计deptno 中不重复的个数
select COUNT (comm) from emp--返回值是4,说明count不计空的记录,为空的记录为无效记录

单行函数和多行函数不能混用

相关知识链接

SQL Server零基础学起你必会（二）表和约束概念详解,用SQL命令建表和约束

• s c o t t scott scott表介绍
• 查询操作
• • 计算列
  • d i s t i n c t distinct distinct[不允许重复的]
  • b e t w e e n between between[在某个范围]
  • i n in in[属于若干个鼓励的值]
  • t o p top top[最前面的若干记录]
  • • 输出工资在1500~3000的前四个人信息
  • n u l l null null[没有值,空值]
  • • 例题
  • o r d e r    b y order\,\,by orderby[以某个字段排序]
  • 模糊查询
  • • 注意
  • 聚合函数
  • • 函数的分类
    • m a x ( ) , m i n ( ) , a v g ( ) max(),min(),avg() max(),min(),avg()
    • c o u n t count count
• 相关知识链接
• 利用SQL命令建表和约束
• 六大约束
• • 实战
  • • 指令合集
    • 实例流程
• 关系
• • 实战
  • • 指令合集
    • 实例流程
  • 查看数据关系的建立
  • 关系图
• 总结
• • • 主键
    • 外键
    • 先删主键表还是外键表
• 相关知识链接

利用SQL命令建表和约束

六大约束

约束:对一个表中属性操作的限制

项目	Value
主键 ( P K ) (PK) (PK)约束	不允许重复元素，避免数据冗余
外键 ( F K ) (FK) (FK)约束	从语法上保证了本事物所关联的其他事物一定存在
c h e c k check check约束	保证事物的属性在合法的范围内
d e f a u l t default default约束	保证事物的属性一定会有一个值
u n i q u e unique unique唯一性约束	不允许重复,但允许为空, S Q L S e r v e r SQLServer SQLServer不允许多列为空

• 事物和事物之间的关联由外键体现
• 外键不能来自另一个表的普通键，如果是普通键则可以一致，如果有重复的就无法判断来自于哪一个。
• 不要用业务逻辑当主键,比如用户名,因为用户名可以改,加一些额外的操作,比如重新编个号。
• 外键可以有多个,主键只可以有一个

实战

下面建一个简单的学生表，快跟着敲一遍：

指令合集

功能	操作	图例
建表	c r e a t e    t a b l e + 表 名 create\,\, table+表名 createtable+表名
主键	p r i m a r y    k e y primary\,\,key primarykey
约束条件	c h e c k ( . . .   a n d   . . . ) check(... \,and\, ...) check(...and...)
n o t    n u l l not \,\,null notnull	非空
n v a r c h a r ( 100 ) nvarchar(100) nvarchar(100)	n n n表示国际化支持汉字, v a r y vary vary变量, c h a r char char表示字符, ( 100 ) (100) (100)表示最大容量
d e f a u l t ( 默 认 值 ) default(默认值) default(默认值)	默认为 . . . ... ...
i n s e r t insert insert插入	i n s e r t    i n t o + insert\,\, into+ insertinto+表名 + + +(需添加的列) v a l u e s + values+ values+(添加的信息)

数据库中单引号里面是字符串,双引号里是数据的名字

实例流程

我们会遇到这样的问题，老代码运行完后新代码加进去会报错，显示已存在

• 如果是单独的指令可以
• 如果是表内进行了新的修改，需添加：

IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student

运行新表时带着该代码一起运行即可

• n o t    n u l l not\,\, null notnull:默认 n u l l null null,用户不给该字段赋值,则字段默认值为 n u l l null null

create table dept2
(
dept2_id int primary key,
dept2_name nvarchar(100) not null,
dept2_address nvarchar(100)
)
create table emp2
(--不能写成{
emp2_id int constraint pk_id_hahaha primary key,--定义主键同时定义了名字
emp2_name nvarchar(20) not null ,
--n表示国际化支持汉字,vary变量,char表示字符,(20)最大20个
emp2_sex nchar(1),
dept2_id int constraint fk_dept_id_heihei foreign key references dept2(dept2_id),--列名,类型,约束,约束的名字,外键约束,外键是哪个表的
)
IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
create table student
(
student_id int primary key,
student_sal int check (student_sal>1000 and student_sal<8000),
student_sex nvarchar(2) default('男'),
student_name nvarchar(200) unique 
)
insert into student(student_id,student_sal,student_name) values (1,2000,'王五');
insert into student values (2,4000,'女','张三')
insert into student values (3,6000,'男','李四');

和约束的关系
黄金三句：

• 数据库通过表来解决事物的存储问题
• 数据库通过约束来解决事物取值的有效性和合法性问题
• 建表的过程就是制定事物属性及其事物属性各种约束的过程

关系

• 定义:表和表之间的联系
• 实现方式:通过不同形式的外键来表示表和表的不同关系
• 假设是表A和表B

分类	Value
一对一	即可以把表A的主键充当B的外键,也可以把表B的主键充当表A的外键
一对多	把表A的主键添加到表B来充当外键,或者说在多的一方添加外键
多对多	必须通过单独的一张表来表示

实战

建立班级,学生,课程的具有多对多关系的表:
班级一张表,教师一张表,班级和教师的关系也是一张表。

指令合集

功能	操作	图例
约束及约束的命名	c o n s t r a i n t    + 命 名 constraint\,\, +命名 constraint+命名
外键及来源表格	f o r e i g n    k e y    r e f e r e n c e s foreign\,\, key\,\, references foreignkeyreferences

实例流程

--班级表
create table class
(
class_id int primary key,
class_num int not null,
class_name nvarchar(100)
)
--教师
create table teacher
(
teacher_id int primary key,
teacher_name nvarchar(200)
)
drop table class_teacher_mapping--没写完可以加这句话
--第三张表格,来模拟班级和教师的关系
create table class_teacher_mapping
(class_id int constraint fk_class_id foreign key references class(class_id),
teacher_id int foreign key references teacher(teacher_id),
course nvarchar(20),
constraint pk_class_id_teacher_id primary key (class_id,teacher_id,course)
)

查看数据关系的建立

关系图

总结

• 数据库研究的是外存
• 图灵证明了用顺序,选择,循环可以解决一切问题, S Q L S e r v e r SQLServer SQLServer用的是纯命令,没有流程,把顺序,选择和循环给删掉了,可以用可流程控制的 T L   S Q L ( 无 法 跨 平 台 ) TL\,SQL(无法跨平台) TLSQL(无法跨平台)解决或者用高级语言对数据进行处理,再写入数据库
• 数据库的所有操作都可以简化成增删改查的操作
• 一列(字段)表示一个特征,一行(记录)表示这个事物,表表示的是同一个事物的集合

主键

• 主键:能唯一标识事物的一个字段,主键不允许修改,修改可能会出现重复。
• 主键通常是整数,不建议用字符串(如果主键是用于集群式服务,才可以用字符串)
• 主键通常 不允许修改,除非被本记录删除
• 主键不要定义成 i d id id,而要定义成表名 I d Id Id或者表名_id
• 要用代理主键,不要用业务主键,通常都是单独添加一个整型的编号充当主键字段

外键

• 外键:如果一个表中若干字段来自另外若干表的主键或唯一键
• 外键通常来 自另外表的主键 而不是唯一键,因为唯一键可能为 n u l l null null
• 外键不一定来自另外的表,也可能来自本表的主键,如下图员工上司表,每个员工的上司也属于员工
• 含有外键的表叫外键表,外键字段来自的那一张表叫做主键表
  

先删主键表还是外键表

• 先删外键表,否则会报错

相关知识链接

SQL Server零基础学起你必会(一)图形化界面建表
SQL Server零基础学起你必会（二）表和约束概念详解,用SQL命令建表和约束
SQL Server零基础学起你必会（三）九种简单查询【史上最全】
数据库入门（一）范式理解：1NF,2NF,3NF,BCNF,4NF详析
数据库 SQLServer 视频教程全集（99P）| 22 小时从入门到精通
数据库知识点
一个小时上手SQL
sql语句，你肯定会听懂的
(重要顺序从上到下)