SQl基础——附带实例

这是在工作开始学习SQL基础时记下的一点笔记，内容很基础。算是对学习的个人记录，适合初学者和学生来看。学的时候用的Typora来写的其中的注释有的用的–有的用的/**/.文章可能还有些许错误，请大家指教。

【】：表示其中的内容可以写也可以不写，按需而定

SQL基础

SQL

struct query language 结构化查询语言

一、 T-SQL分类

数据定义语言 DDL

用来管理数据库对象，包括：数据库、数据表、索引、游标、视图、存储过程等

创建 ：create

删除： drop

修改： alter

drop database Stu  --删除数据库
go    --每一句写完，在最后加上批处理go，作为该句的结束

/*
创建学生表  S 字段如下
Sno 学号 int  主键
Sname 姓名 varchar(50)
sSex 性别 varchar(2) check约束
sage 年纪 int check约束
stel  电话 varchar（13）
splace varchar(60)
*/

--drop table S
create table S    --创建表
(
  sNo      int               primary key ,-- identity(101,1)   primary key主键约束。identity(101,1）自增，让s_id的值从101开始每一次增加1
  sName    varchar(50),
  sSex     varchar(20)       check(ssex in ('男','女')),--约束条件，ssex只能为男或者女
  sAge     int               check(sage>=0 and sage<=60),--约束条件，sage只能在18到60之间
  sTel     varchar(13),
  sPlace   varchar(60),
)
go--每一句写完，在最后加上批处理go，作为该句的结束

数据操纵语言 DML

用来管理数据

增加： insert

删除： delete

修改： update

查询： select

ALTER TABLE S  ADD cId int --在S表中新增一个cId列
go

update S set cId = 1301 where sNo=6;  --增加列后，更新数据表
update S set cId = 1302 where sNo=7;
update S set cId = 1303 where sNo=8;
update S set cId = 1304 where sNo=9;
update S set cId = 1301 where sNo=10;
update S set cId = 1302 where sNo=11;
update S set cId = 1303 where sNo=12
update S set cId = 1304 where sNo=13;
update S set cId = 1301 where sNo=14;

select * from S;

insert S values(6,'喜羊羊','男',16,'666666','羊村',1301);
insert S values(7,'美羊羊','女',17,'666666','羊村',1302);
insert S values(8,'懒羊羊','男',18,'666666','羊村',1303);
insert S values(9,'沸羊羊','男',19,'666666','羊村',1304);
insert S values(10,'暖洋洋','女',16,'666666','羊村',1301);
insert S values(11,'慢羊羊','男',21,'666666','羊村',1302);
insert S values(12,'红太狼','女',22,'666666','狼堡',1303);
insert S values(13,'灰太狼','男',20,'666666','狼堡',1301);
insert S values(14,'小灰灰','男',4,'666666','狼堡',1304);
insert S values(15,'小红红','女',6,'666666','狼堡',null);

数据控制语言 DCL

数据库权限控制

授权：grant

取消授权：revoke

流程控制语言 FCL

流程控制符：if while case

二、运算符

1. 算术运算符：+ - * /
2. 比较运算符： 大于> 、小于 <、大于等于>= 、小于等于 <= 、等于 =、不等于<> !=
3. 逻辑运算符：and or not

三、数据库脚本

创建数据库

create database 数据库名

切换数据库

use 数据库名

创建表

create table 表名（

列名1 数据类型 【约束】，

列名2 数据类型 【约束】，

列名3 数据类型 【约束】，

列名N 数据类型 【约束】

)

判断数据表或列是否在

select  count(*)  from  dbo.sysobjects where name=  'table_name';--判断表存在，则在输出框显示1，不存在显示0
select  count(*)  from syscolumns where id=object_id(‘table_name’)  and name=  'column_name';--判断列是否存在

增加数据

insert [into] 表名 【(列名1,列名2…)】 value(值1,值2…)；

在表中添加新的列并插入相应数据

ALTER TABLE S  ADD cId int
go

update S set cId = 1301 where sNo=6;
update S set cId = 1302 where sNo=7;
update S set cId = 1303 where sNo=8;
update S set cId = 1304 where sNo=9;
update S set cId = 1301 where sNo=10;
update S set cId = 1302 where sNo=11;
update S set cId = 1303 where sNo=12
update S set cId = 1304 where sNo=13;
update S set cId = 1301 where sNo=14;

修改数据

update 表名 set 列=值 【where】

删除数据

delete 【from】 表名 【where 条件】

truncate table 表名

其中：

delete 可以删除带有条件的数据，在下方提示栏提示一行受影响，一行一行的按行删除。删除后自增长不会变会顺延

truncate不能按条件删除，在下方提示栏提示命令已完成，针对于表全部删除。删除后自增长会从头开始

四、查询

select * from 表名 【where】

select 列名1，列名2 from 表名 【where】

--1、查询所有记录 
--查询S表中的所有记录
select * from S

--2、查询部分列
--查询S表中所有学生的姓名和年龄
select sName ,sAge from S

--3、去除重复的记录 distinct
--查询SC表中的课程号(无重复项)
select distinct  cNo from C;

单表查询

select * /列1 列2 dinstinct top N top N percent
from 表名
where 条件
=> <=。。。。
between and
not between and
in
not in
Like
null
not null
and or
…

order by desc（降序）/asc(升序，默认为升序)

--4、取前...条数据 top n
--查询S表中前5条记录
select   top 5 * from S
select   top 5 sName  from S 
--前5条姓名
select   top 5 sName from S
--查询S表中前50%条记录
select   top 50percent * from S


--5、为列显示别名
--查询S表中学号姓名年龄(字段名用汉语显示,容易读)
select  学号 = sNo ,  姓名 = sName, 年龄 = sAge   from S;
select  sNo as 学号,sName as 姓名,sAge as 年龄  from S;


--6、对列进行计算
--查询学生的名字，并在每个名字前加上‘姓名：’
select  '姓名：'+sName  from S;

--查询学生的姓名和年龄，要求年龄小5岁显示
select sName ,sAge  from S  where sAge<5
--select sAge  sAge-5 from S 

--7、条件查询
--查询S表中的所有男生
select *  from S  where sSex='男'

--查询S表中所有年龄小于20岁的记录
select *  from S  where sAge<20

--查询S表中所有的年龄小于20岁或者是女生的学生信息
select *  from S  where sAge<20 or sSex='女'
--查询S表中所有的年龄小于20岁且是女生的学生信息
select *  from S  where sAge<20 and sSex='女'

--查询出年龄在18到20岁之间的学生的学号,姓名,年龄,和电话号码
select sNo,sName ,sAge,sTel  from S  where sAge between 18 and  20
--查询年龄不在20--25岁的学生的信息
select *  from S  where sAge not between 20 and  25
--查询年龄为17，19，21的学生的信息
select * from S  where sAge=17 or sAge=19 or sAge=21
select * from S  where sAge in (17,19,21)
--查询年龄是17和19的人
select * from S  where sAge=17 or sAge=19
--查询年龄是17或19的人
select * from S  where sAge=17 or sAge=19
--查询懒羊羊和灰太狼的信息
select * from S  where sName='懒羊羊' or sName='灰太狼'
--查询年龄不为17，19，21的学生的信息
select * from S  where sAge!=17 and sAge!=19 and sAge!=21
select * from S  where sAge<>17 and sAge<>19 and sAge<>21

模糊查询

like ‘一’
匹配一个字 _
匹配多个字 % 其中：“%字%”表示在内容中含有该字，“字%”表示必须是在该句的开头是该字
任意一个字 []
不是其中任意一个字 [^]

--查询电话为空的学生
select * from S  where sTel  is null

--查询电话不为空的学生
select * from S  where sTel is not null

--模糊查询  匹配符/通配符  like 
/*
_  ：匹配一个字符,用来代替一个字符的位置
%  ：匹配多个字符
[]: 匹配括号中的任意一个字符
[^]:不匹配括号中的任意一个字符  
*/
--查询s表中姓李的学生信息   李%
select * from S where sName like '懒%'
--查询姓李的，名字是2个字的人
select * from S where sName like '懒_'--这里_就代替了一个字符的位置

--查询出名字中的第二个字为”太”的所有学生的详细资料  _学%
select * from S where sName like '_太%'--这里用一个_来代替第一个字，然后第二个字是太，之后字符不限数量用%表示

--查询姓小，名字是三个字的学生信息   
select * from S where sName like '小__'

--查询姓小，姓张或姓都的学生信息
select * from S where sName like '小%' or sName like '张%' 
select * from S where sName like '[小，张]%'
--查询不姓小，不姓张还不姓都的学生信息
select * from S where sName not like '[小，张]%'

--排序  order by 列名  [asc|desc]  （永远写在最后）
--asc升序（默认）  desc 降序
--查询S表中年龄从小到大的信息 
select * from S order by sAge

--查询S表中年龄从大到小的信息
select * from S order by sAge  desc

--查询S表中男生年龄从大到小的信息
select * from S where sSex='男' order by sAge desc 

--查询S表中年纪最大的5个学生
select * from S where sSex='男' order by sAge desc 

--查询S表中年纪最小的5个学生
select * from S order by sAge desc 

五、约束

主键：

primary key

自增长：

identity （初始值，增长值）

check：

check（sage>18 and sage<60）

默认值：

default（‘男’）

外键：

references 主键表名（对应的列名）

例子

/*创建数据库 stu*/
create database Stu    --创建数据库
go    --每一句写完，在最后加上批处理go，作为该句的结束

use Stu    --切换到数据库
--drop database Stu   --删除数据库
go    --每一句写完，在最后加上批处理go，作为该句的结束

create table S    --创建表
(
  sNo   int   primary key ,-- identity(101,1)   primary key主键约束。identity(101,1）自增，让s_id的值从101开始每一次增加1
  sName    varchar(50),
  sSex     varchar(20)       check(ssex in ('男','女')),--约束条件，ssex只能为男或者女
  sAge     int               check(sage>=0 and sage<=60),--约束条件，sage只能在18到60之间
  sTel     varchar(13),
  sPlace   varchar(60)
)
go--每一句写完，在最后加上批处理go，作为该句的结束

insert S values(6,'喜羊羊','男',16,'666666','羊村');
insert S values(7,'美羊羊','女',17,'666666','羊村');

create table C(
  cNo       int             primary key ,
  cName     varchar(50)     not null
)
go

insert C values  (001,'语文');
insert C values  (002,'数学');
insert C values  (003,'物理');
create table SC(
   sNo       int         references  S (sNo),--外键
   cNo       int         references  C (cNo),
   Point     float,
   primary key (sNo,cNo) 
)
go

insert SC (Point )  values (11);
insert SC (Point )  values (12);
insert SC (Point )  values (13);

六、聚合函数

求和 sum

求平均 avg

最大值 max

最小值 min

个数 count

-求所有学生人数
select count(*) as '学生总人数' from S
--性别不为空的学生人数
select count(*)  as '学生总人数' from S  where sSex is not null

--查询学生年龄总和，取别名为‘年龄总和’
select  sum( sAge ) as '年龄总和' from S
--查询学生平均年龄，取别名为‘平均年龄’
select  Avg( sAge ) as '平均年龄' from S

select  min( sAge ) as '最小年龄' from S

--查询出有多少个人的年龄是20岁
select count(*) from S  where sAge=20

七、分组表达式

group by --》group by 列名

注意点
1、select 列名，聚合函数 》》 group by （列名）
2、聚合函数 根据 列名 分组的
3、group by 多个列，根据多个列分组

having==》where 条件

having 必须和 group by 一起使用
having 只能接在聚合函数之后

--求男生总人数和女生总人数
select sSex, count(*)  from S  group by sSex

--统计每个班的学生人数
select  count(*) from SC  where cNo=001
  alter table S add 班级编号 int ;

--查询SC表中所有学生的学号和平均成绩.
select sNo, avg(Point) as 平均成绩  from SC  group by sNo

--查询SC表中所有学生的学号和总分.
select sNo, sum(Point) as 总成绩  from SC  group by sNo

--查询SC表中所有学号大于等于2的学生的学号和总分.
select sNo,sum(Point) from SC  where sNo>=2 group by sNo
select sNo,sum(Point) from SC  group by sNo having sNo>=2

--查询SC表中选修超过3门课程的学生的学号和选修的课程的门数
select sNo , count(cNo) as '选课人数' from SC
group by sNo  having count(cNo)>3 

--查询SC表中各个课程号及相应的选课人数.
select cNo,count(cNo) from SC group by cNo

八、联表查询

内联接

inner join on
select 。。。 from 表1 inner join 表2 on 表1 和表2条件
inner join 表3 on 表3和表1/表2 条件
表1 表2…没有主从关系

外部联接

左联接 表1 left join 表2 on 主表：表1
右联接 表1 right join 表2 …on 主表：表2

全外联接 没有主从之分
表1 full outer join 表2 on 主表： 表1 表2

交叉查询

cross join

--查询每个学生的成绩：学号、姓名、课程号 、分数
select S.sNo, sName,cName,Point from S right join SC on S.sNo=SC.sNo  right join C on SC.cNo=C.cNo;

--查询每个学生的成绩：学号、姓名  s(sname)、课程名  c(cname) 、分数  sc(point)
select S.sNo, sName,cName,Point from S right join SC on S.sNo=SC.sNo  right join C on SC.cNo=C.cNo;

--查询年龄大于20岁的学生的成绩：学号、姓名、课程名 、分数
select S.sNo, sName,cName,Point from S right join SC on S.sNo=SC.sNo  right join C on SC.cNo=C.cNo where S.sAge>20;

--多表连接查询：内连接，外连接  ,交叉连接

--查询所有的学生对应的班级，如果没有分班，则班级设置为null
select S.sName , class.cName
from S right join class 
on S.cId = class.cId

--查询所有的学生对应的班级，如果有的班没有学生，则学生设置为null

--查询所有的学生对应的班级
select sName,cName from S
right join class 
on S.cId = class.cId

九、子查询

select语句嵌套select语句

功能

子查询作为查询条件来使用，查询结果是一列

子查询可以作为一个临时表，应该为临时表增加表名

select * from (–子查询) where

子查询作为列，应该为列增加列

create database lianxi
go

use lianxi
go

--创建部门表
create table bumen
(
 　　bmcode int primary key,  --部门编号（主键）
 　　bmname varchar(20),  --部门名字
 　　bmceo varchar(20),  --部门主管
　　 bmtel varchar(20),  --部门电话
)
go
--先插入部门的数据
　　insert into bumen values(1,'财务部','张三','1111111')
　　insert into bumen values(2,'人事部','李四','2222222')
　　insert into bumen values(3,'技术部','王五','3333333')
　　insert into bumen values(4,'销售部','赵六','4444444')

--创建人员表
create table renyuan
(
 　　code int primary key identity(1001,1),  --员工编号（自增长主键）
 　　name varchar (20),  --员工名字
 　　sex varchar (10),  --员工性别
 　　age int,  --员工年龄
 　　ygbm int,  --员工部门（部门表外键）
)
go
--插入人员表的信息

　　insert into renyuan values('张三','男',22,1)
　　insert into renyuan values('李四','女',32,2)
　　insert into renyuan values('王五','男',42,3)
　　insert into renyuan values('赵六','女',52,4)

　　insert into renyuan values('一','男',28,1)
　　insert into renyuan values('二','女',38,2)
　　insert into renyuan values('三','男',48,3)
　　insert into renyuan values('四','女',58,4)

　　insert into renyuan values('五','男',25,1)
　　insert into renyuan values('六','女',35,2)
　　insert into renyuan values('七','男',45,3)
　　insert into renyuan values('八','女',55,4)

--设置好外键关系，之后插入数据

--1、查询年纪最大的人的部门名称
select max(age) from renyuan
select  ygbm from renyuan  where age=( select max(age) from renyuan)
select bmname from  bumen  where bmcode=(select  ygbm from renyuan  where age=( select max(age) from renyuan))

--2、按照年龄排序后的前三个人的所有信息

select   top 3 * from  renyuan order by age desc

--3、按照年龄排序后的6/7/8名人员的所有信息
select   top 8 * from  renyuan order by age desc
select   top 3 * from  (select   top 8 * from  renyuan order by age )ry order by age  desc

select top 3 * from renyuan where code not in(
select top 5 code   from  renyuan order by age
)  order by age

--4、分页查询,要求 一页给显示5条数据

select  top 5 * from renyuan
select  top 5 * from renyuan where code not in (select  top 0 code from renyuan)
select  top 5 * from renyuan where code not in (select  top 5 code from renyuan)
select  top 5 * from renyuan where code not in (select  top 10 code from renyuan)

--5、查询销售部里的年龄大于35岁的人的所有信息
select bmcode from bumen where bmname='销售部'
select * from renyuan   where ygbm=(select bmcode from bumen where bmname='销售部')

select * from (select * from renyuan   where ygbm=(select bmcode from bumen where bmname='销售部'))yy where age>35
--6、查看所有人员信息，将部门编号替代为部门名称
select code ,name,sex,age,(select bmname from bumen where bumen.bmcode=renyuan.ygbm)as '部门名称' from renyuan  

十、视图

创建视图

create View 视图名 as select 语句（一个结果）

查询视图

select 列名 from 视图名

视图的列重命名

create View 视图名(新列名1，列名2…)
as select 语句
select 查询的时候，列名用新列名

修改视图

alter view 视图名…(table 一样)

删除视图

drop view 视图名

create view  cie as 
select code ,name,sex,age,(select bmname from bumen where bumen.bmcode=renyuan.ygbm)as '部门名称' from renyuan  
go

select * from cie

--创建视图为列重命名
create view  cie2 (编号,姓名,性别,年龄,部门名称)--视图重命名汉字名称不需要加单引号
as 
select code ,name,sex,age,(select bmname from bumen where bumen.bmcode=renyuan.ygbm)as '部门名称' from renyuan  
go
select * from cie2

--查询列
select 姓名 from cie2 where 编号=1001;--查询时列名按照重命名的名字

视图数据更新

​ create view cie4 as select * from bumen ;
​ select * from cie4;
​ update cie4 set bmtel = 5555555 where bmcode=4;–对单表修改有效
​

 create view  cie2 (编号,姓名,性别,年龄,部门名称)--视图重命名汉字名称不需要加单引号
    as 
 select code ,name,sex,age,(select bmname from bumen where bumen.bmcode=renyuan.ygbm)as '部门名称' from renyuan  
    go
 select * from cie2;
 update cie2 set 部门名称 = '人事部' where  编号=1005;--对多表修改数据无效	

十一、排序函数

对列排序方法row_number(), rank(), dense_rank()
语法：select 排序函数 over (order by 列名) from 表名

select *,ROW_NUMBER () over (order by age)  from renyuan;--不论是否有相同的数值，排序号连贯，不会跳号
	select *,rank () over (order by age)  from renyuan;--在出现相同数值时，排序号不连贯，会跳号
	select *,dense_rank () over (order by age)  from renyuan;--在出现相同数值时，排序号连贯，会重复

函数	排序效果
row_number()	不管是否相同，即1，2，3，4，5
rank()	如果遇到相等值，则排名相同，并且排名为非连续自然数，即：1，1，3，3，5
dense_rank()	如果遇到相等值，则排名相同，但是排名为连续自然数,即：1，1，2，2，3

十二、索引

作用：提高查询的速度，降低查询的时间

聚集索引

类似于字典的页码，在一个表里只能有一个

主键默认有一个聚集索引

非聚集索引

类似于字典的目录，在一个表里可以有多个

创建索引

语法：create 【关键字】 index 索引名 on 表（列）

聚集索引关键字：clustered

非聚集索引关键字：nonclustered（默认创建非聚集索引）

删除索引

drop index 索引名

使用索引

select * from 表名 with （index=索引名）

   create  nonclustered  index  in1  on renyuan (ygbm);--创建非聚集索引
   drop  index  in1--删除索引
   select   *   from  renyuan  with  (index=in1)--使用索引
   ------聚集索引测试表
	use lianxi 
	go
	create table  ceshi (
	csName    varchar(20),
	csId       int
	)
	go
	insert into ceshi  values ('一',11);
	insert into ceshi  values ('二',12);
	insert into ceshi  values ('三',13);
	insert into ceshi  values ('四',14);
	insert into ceshi  values ('五',15);
	insert into ceshi  values ('六',16);

	select * from ceshi;
	create  clustered  index  in5  on ceshi (csId);
	create  index  in6  on ceshi (csId);

十三、事务

概念

不可分割的工作逻辑单元

属性

原子性：不可分割

一致性：事务完成的时候，数据保持一致的状态

隔离性：A对B转账和B对A转账，以及A对C，相互不干扰

分类

显式事务

隐式事务

自动提交事务：自动执行，自动回滚

创建事务 transaction

开始事务 begin transaction

提交事务 commit transaction

回滚事务 rollback transaction

当提交/回滚事务即表示事物结束

公式表达式

概念：把一个查询结果当做临时表来处理

语法：with 临时表名 （列名）

as

​ select 语法

十四、存储过程

概念

一组已经定义好功能的SQL语句。在数据库后台调用。类似于函数，创建一个函数然后调用，函数中可以有变量，调用时可以为变量赋值。

分类

系统存储过程

sp_开头的数据库系统存储过程

xp_开头的操作系统存储过程

自定义存储过程

无参数

有输入参数

输出参数

创建存储过程

语法：

create  procedure/proc  存储过程名 
as 

    sql 语句（删除、查询、判断等都可以写在SQL之中）
    
go

删除存储过程

drop procedure/proc 存储过程名

修改存储过程

alter （类似创建）

加密存储过程

十五、触发器

概念

触发器是一种特殊类型的存储过程，通过事件进行触发被自动调用执行的。使用触发器时，当执行指定的语句后，会触发响应，在提示栏显示指定的语句或动作，（原来是‘ 行受影响‘，添加变成’操作成功‘）

创建触发器

语法：
create trigger trigger_name  /*关键字trigger创建触发器*/
　　on  table_name/view_name/*对表或者视图创建*/
　　{for | After | Instead of } insert, update,delete /*在出现insert、update、delete时触发*/
　　as
　　sql_statement
　实例：
　go
create trigger tr1
on renyuan
for insert,delete
as
  begin
  print('操作成功')
  end
  go
  delete  from renyuan  where name='张六';/*测试数据*/
  insert into renyuan values ('张六','女',66,6);

修改触发器

语法类似于创建

go
  alter trigger tr1 
  on  renyuan  /*修改时不能更改面向的表名或者视图*/
  for insert  /*可以修改动作*/
  as
    begin
	   print ('添加成功')  /*可以修改SQL语句*/
	end
	go
  insert into renyuan values ('张七','女',77,6);

删除触发器

语法:drop trigger trigger_name;
实例：drop trigger tr1;

十六、游标

作用

在结果集中读取数据，显示到结果栏

创建游标

语法： declare 游标名 cursor  scroll  for 结果集SQL
实例： declare youbiao cursor  scroll  for select * from renyuan;

开启游标

语法：open 游标名    --游标需要打开才能使用
实例： open youbiao;

抓取数据

语法： fetch 【first/next/last/absolute N/relative N】 from 游标名
实例： fetch relative 4 from youbiao;

关闭游标

语法：close 游标名   --关闭后游标还存在，但无法使用，如果关闭后抓取数据会提示游标未打开
  实例：close youbiao;

释放游标（删除游标）

 语法： deallocate 游标名
 实例：deallocate youbiao;

十七、数据库设计

设计数据库需要：
E-R图
实体：表。用方框表示
属性：数据列。用椭圆表示
关系：菱形表示

设计步骤

1. 需求分析；（根据需求，确定实体、属性、关系）
2. 概要设计；（绘制R-R图）
3. 详细设计；（将修改好的E-R图转换成表）

数据库规范

第一范式：原子性
第二范式：列与主键有相关关系
第三范式：列和主键之间直接关联（不一定满足）