一、理解数据库

1、关系型数据库

SQL-Server（）(中小型数据存储）
MySQL(中小型数据存储）
Oracle （海量数据存储） DB2 （海量数据存储）

image.png

2、数据库文件

.mdf 数据文件 .ldf日志文件

image.png

二、安装

三、数据库的创建

1、创建数据库和表

create database emp        //创建数据库
on primary                 //指明数据文件
(
name='emp_data',            //数据库的逻辑文件名
filename='e:\emp_data.mdf', //物理数据文件名
size=1MB,                   //初始大小
maxsize=5MB,                //最大
filegrowth=1MB              //增长率
)
log on                      //指明日志文件
(
name='emp_log',
filename='e:\emp_data.ldf',
size=1MB,                   //初始大小
maxsize=5MB,                //最大
filegrowth=1%               //增长率
)
go
/*drop database emp;*/      //删除数据库

Create table emp            //创建表
(
empid int,
empname varchar(20),
sal numeric(10,2),
age int
)
sp_help emp                //查看表结构信息)

2、举例

·创建一个员工管理系统的数据库(名字自拟)
.在该数据库中建三张表，自行考虑取名和数据类型:
.人员信息(人员编号、姓名、年龄、工资(小数点后保留2位))
.部门信息(部门编号、部门名称)

Create database ms
Use ms
Create table ms1
(
No int,
Name varchar(10),
Age int,
Sal numeric(10,2)
);
Create table ms2
(
No int,
No_part int,
Name_part varchar(10)
);

四、数据库中数据的增删查改（DML,数据操作语言）

1、插入数据

  Insert  [INTO]  <表名> (字段列表） values(值列表）；
  Insert  [INTO]  <表名>   [列名]    values <值列表>；

image.png

2、查询数据

  select  字段列表 from 表名；

image.png

3、修改数据

Update  表名  set  字段名1=值1，字段名2=值2；
Update  表名  set  字段名1=值1，字段名2=值2 [where 条件子句]；
条件子句：字段名（表达式）运算符 值

4、删除数据

Delete  [from] 表名 [while 字段名 （表达式） 运算符 值；
Truncate table 表名：<=> delete  [from] 表名；（事务commit/rollback)//永久删除

image.png

几种删除的不同点

truncate和delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(rigger)索引(index);依赖于该表的存储过程函数将保留，但是变为invalid状态.2. delete语句是dml,这个操作会放到ollback segement中，事务提交之后才生效;如果有相应的trigger执行的时候将被触发.

truncate,drop是ddl,操作立即生效原数据不放到ollback segment中，不能回滚.操作不触发trigger.

3.delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不动

显然drop语句将表所占用的空间全部释放

truncate语句缺省情祝下见空间释放到minextents个extent 除非使用reuse storage; truncate会 将高水线复位(回到最开始).

4.速度,一般来说: drop>, truncate >; delete

5.安全性小心使用drop和truncate,尤其没有备份的时候:否则哭都来不及使用上，想删除部分数据行用delete ;注意带上where子句. 回滚段要足够大.

5、命名前端展示的字段名

select 字段名（显示字段名）
Select 字段名 as （显示字段名）

image.png

6、增加和删除表中字段

alter table Students drop column Age;//表中删除Age字段
alter table Students add bir datetime;//表中增加bir字段

7、例子

.给人员表造如下数据:
人员编号 姓名
1 张三
1 张三丰
2 李四
3 李四囍
4 王五
4 王小五
5 王二小
6 王三小
7 张一
7 阿卜杜勒
1 张三
12 张一
12 刘加一
.查询人员表中编号为7或1或4的人员信息
.将编号为12且名字为张一的人员编号更新为13,姓名更新为张一一
.查询人员表中编号为1且名字为张三的人员信息
.思考：表中一模一样的数据如何区分开,如何只删除以上数据编号为1名字为张三的其中一条数据

select * from ms1;
insert into ms1 (No,Name) values (1,'张三');
insert into ms1 (No,Name) values (1,'张三丰');
insert into ms1 (No,Name) values (2,'李四');
insert into ms1 (No,Name) values (3,'李四囍');
insert into ms1 (No,Name) values (4,'王五');
insert into ms1 (No,Name) values (4,'王小五');
insert into ms1 (No,Name) values (5,'王二小');
insert into ms1 (No,Name) values (6,'王三小');
insert into ms1 (No,Name) values (7,'张一');
insert into ms1 (No,Name) values (7,'阿卜杜勒');
insert into ms1 (No,Name) values (1,'张三');
insert into ms1 (No,Name) values (12,'张一');
insert into ms1 (No,Name) values (1,'刘加一');
select * from ms1;
select * from ms1 where No=1 or No=4 or No=7;
update ms1 set No=13 ,Name='张一一' where No=12 and Name='张一';
select * from ms1;
select *from ms1 where No=1 and Name='张三';

五、数据唯一性

1、distinct （提取唯一性数据）

Select distinct 字段列表 from 源表
Select distinct id，pername from emp order by id，pername   
//表名字段合在一起，一模一样的记录，将只提取一个

2、identity（建表的时候）

这种编号的好处是一定不会重覆, 而且一定是唯一的, 这对table中的唯一值特性很重要, 通常用来做客户编号, 订单编号等功能。
限制：建立整形的字段；一张表中只能有一个字段，可以自动增长；不是独立对象，不允许空

 Create table t2(id int identity(10,2), tname varchar(20)); //id的初始值为10，每次增加2
 Insert into t2(tname) values ('b');
 select * from t2;

3、constraint （添加约束语法）

image.png

Alter table 表名 add constraint 约束名 约束类型（字段列表）
//唯一性约束可以建多个 可以为空值，但只能出现一次
    create table t3 (id char(10) unique,tname varchar(20));
    insert into t3(id,tname) values (1,'b');
    alter table t3 add constraint uq_t3 unique(tname);

4、Primary key主键

在某个表的某个字段上建立主键，则那么字段不能重复
主键字段不能为空 一张表中只能出现一个主键 数据唯一

create table t4 (id char(10) primary key,tname varchar(20));
    //联合/复合主键
    create table t4
    (
    id char(10),
    tname varchar(20),
    primary key(id,tname)--联合/复合主键
);
//事后打补丁(做数据保持唯一性  字段不能为空)再打补丁
alter table emp add constraint pk_emp primary key(empid);

六、查询操作

1、条件查询 where

select  字段列表 from 表名 while [条件子句]
例：select * from ms1 where No=1 or No=4 or No=7;

2、模糊查询 Between and

（把某一字段中内容在特定范围内的字段查询出来，包括上点值）

Select 字段名 from 表格 where 字段 between 值 and 值
例：Select Score,StudentID from Score where Score between 60 and 80

3、模糊查询 in （把某一字段的内容与所列出的查询内容匹配的记录查询出来）

Select 字段名 from 表格 where 字段 in（‘值’,’值’,’值’）;
例：Select SName as 学生姓名，SAddress as 学生地址 from Students where SAddress in ("北京","上海","深圳"）

4、模糊查询 like

select * from Students where SAddress like '北%'  //从学生表中查找找居住地址以”北“开头的
select *from Students where SName like '%楠%' //从学生表中查找姓名中包含“楠”的学生
select * from Students where SId not like '%45%' //从学生表中查找学号不包含45的学生

select * from Students where SName like ‘_小_’;  //选取学生表中名字是X小X的
select *from Students where SId like '211421[1-5]' 
//选取学生表中学号是2114211，224212，2114213，2114214，2114215的学生信息
select * from 表 where 字段名  like ‘%[a-z]%’;   //包含字母的值 

5、查询空值

Select * from  Where 字段 is null
 例：select *from emp where sal is null;--为空
     select *from emp where sal is not null;--不为空

6、获取当前服务器时间 select getdate();

7、计算

   select 1+2;  --显示1+2 的结果：3
   select '收费的';  --显示汉字 收费的
   Select 1*3-2+null  --显示结果为空，空值与任何值相加为空

七、插入操作

1、插入数据

Insert  [INTO]  <表名> (字段列表） values(值列表）;
insert  [INTO]  <表名>   [列名]  values <值列表>;
例：--插入数据的三种方法
insert into ms1(No,Name,Age,Sal) values(1,'楠',22,1000);--第一种
insert into ms1(No,Name) values(2,'xu');--第二种（有些字段可以为空）
insert into ms1 values(3,'wan',21,3000);--第三种（必须严格按照字段顺序，并且不能不填）

2、插入多行数据

Insert into <表名> (列名)  select <原列名> from (源表名）;

insert into  TongXueLu（姓名，地址，手机号）
select  SName,SAddress,SPhoneNumber
from Students

八、修改操作

1、修改数据

Update  表名  set  字段名1=值1，字段名2=值2；
Update  表名  set  字段名1=值1，字段名2=值2 [where 条件子句]；
条件子句：字段名（表达式）运算符 值
例：update ms1 set No=13 ,Name='张一一' where No=12 and Name='张一'

2、修改表结构

  alter table 表名
  alter column 字段名 数据类型

  alter table emp alter column empid int not null; //修改emp表里面的empid 不能为空
  alter table emp drop column Age;//人员表销毁年龄字段
  alter table emp add bir datetime;//人员表增加出生日期字段

九、排序操作

Order by 字段 (ASC)升序 ； order by 字段 desc 降序

    select *from emp order by empid； //升序 asc 不写默认升序
    select *from emp order by empid desc;  
    select *from emp where empid in(7,4,1)  order by empid desc;  
    select *from person order by no asc, name desc;  
//按人员编号升序排序，如果编号相同再按姓名降序排序（asc 可默认不写）

十、其他操作

1、不允许为空not null

  Create table t1(id int not null, tname varchar(20));

  Select *from emp where 1=2; //恒为假 只显示字段名

  消息515报错 ，不允许为空的报错

情况一：创建表时，约束字段内容不能为空

Create table t1（id int not null,name varchar(20) not null）

情况二：表已建立，再修改表结构，约束字段内容不能为空

alter table 表名 alter column 字段名 数据类型 not null //alter table 用来修改表结构

注意：若当前表中某字段有null值，则无法用情况二修改。

      否则就将null值全都修改掉，再去修改表结构

2、限制行数

 ·限定固定行数 top 5

select top 5 sname,saddress from students where ssex=0;

3、返回百分之多少行

select top 20 percent sname,saddress from students where ssex=0;

4、去重【distinct】操作

①提取唯一性数据
②提取唯一性数据备份到备份表
③删除源表数据
④备份表数据写回源

1、提取唯一性数据Select distinct 字段列表 from 源表
Select distinct id，pername from emp order by id，pername 
  //表名字段合在一起，一模一样的记录，将只提取一个

2、提取唯一性数据备份到备份表 select 字段列表 into 新表名 from 源表 [where]
Select distinct * into emp_bak from emp   
//into 后的表格不能存在

3、删除源表数据
Truncate table emp
------drop table emp

备份表数据写回源表
Select *into emp from emp_bak   
//into 后的表格不能存在                
Insert into 表名（字段列表） values（值列表）
Insert into 表名（字段列表） select 字段列表 from 表名2 
 //将某一个表的内容插入另一个表        
Insert into emp select *from emp_bak

数据库函数

1、日期函数

select name as 姓名,datepart(year,getdate())-datepart(year,bir) as 年龄 from Students  ;
select name as 姓名,datediff(yy,bir,getdate()) as 年龄 from Students ;
select name as 姓名,year(getdate())-(year(bir)) as 年龄 from Students;
select day(getdate());
select month(getdate());
select year(getdate());

//1、
select getdate();
select datename(dw,getdate());//星期几
select datename(wk,getdate());//本年的第几周
select datename(dy,getdate());//本年的第几天
select datename(qq,getdate());//本年的第几个季度
//2查询人员表所有人的生日是星期几
select *,datename(dw,bir) from emp;

//3查询当前系统时间向后推分钟，向前推小时的数据
select dateadd(mi,-150,getdate());
select dateadd(hh,2,getdate());

//4日期型数据直接加上(+)一个整数是什么数据
select getdate()+1; --往前（后）推几天

//5如何计算两个日期之间相差的年份(小数)
select datediff(yy,getdate(),'1990-05-01');
select datediff(day,getdate(),'1990-05-01')*1.0/365;

//6查询人员表所有人的年龄(小数)
select *,datediff(mm,bir,getdate())*1.0/12 from emp;

2、数学函数

3、字符串函数

4、系统函数

5、数据类型转换函数

1、转换函数 cast
2、转换函数convert
--数据类型转换
select 101-'232'; --结果-131
select 101+'232';--结果
select '101'+'232';--结果
--转换函数cast（不能定义格式）
select cast(101 as char(3));
select cast(getdate() as char(24));
--转换函数convert
update chinfo set sdate='2017-04-07 9:30:00.0' where no=1;
update chinfo set sdate='2017-04-07 8:30:00.0' where no=3;
update chinfo set sdate='2017-04-07 10:30:00.0' where no=2;
select * from chinfo;

select substring(convert(char(24),sdate,121),12,5)--判断是否迟到
from chinfo
where substring(convert(char(24),sdate,121),12,5)>'08:30'

6、排名函数

--排名函数
select row_number() over(order by 字段)
select row_number() over(order by No) as x,No,Name from emp;

update emp set No=6 where name='王小五';
select * from emp;
--排名函数
select row_number() over(order by 字段)
select row_number() over(order by No) as x,No,Name from emp;

update emp set No=6 where name='王小五';

select top 100 row_number() over(order by id) ,
dateadd (ss,row_number() over(order by id),getdate()) 
from master.dbo.sysobjects;

5、聚合函数

特点①自动绕过null值②取出来的值是单一值③用于检测/对比数据的
④注意函数适用的数据类型sum()和avg()只针对数值型数据

select max(sal),min(sal),avg(sal),count(no) from emp;
select count(sal) from emp;--要拿非空字段统计数量
select count(no) from emp;--拿主键字段统计是最合理的
select count(*) from emp;--数量量巨大的时候不适用
select 1636+null;--为空
select sum(sal) from emp;

select max(sal) ,name from emp;--单值数据与多值数据不能同时查找出来
--所以有了分组
--分组group by
select max(sal),name from emp group by name;--所有叫一个名字的里面工资最多的，是多少
select max(sal),count(*),left(name,1) from 
emp group by left(name,1);--按照姓氏统计

6、约束

Check 约束：约束用于限制列中的值的范围。
Default 约束：如果没有规定其他的值，那么会将默认值添加到所有的新记录。

创建表时添加
create table t5
(
id int check(id between 10 and 100),
name varchar(10)
)
修改表时添加
create table t6
(
id int ,
name varchar(10)
)
alter table t6 add constraint ck_t6_name

建表时
create table t5
(
id int,
name varchar(10) default ’中国人’
)
修改表时添加
alter table t5 add constraint df_t5_name
default('中国人') for name; --添加default 约束，不影响之前的数据
select *from t5;
insert into t5 (id) values(11);

7、多表关联查询

以下新建一个dept的部门表，包括deptid和deptname两个字段
在之前的人员表info中新增一个字段，并添加字段的值
查询人员信息和部门信息（多张表查询）
期间出现：迪卡尔现象 用where条件语句解决
create table dept
(
deptid int,
deptname varchar(20)
)
insert into dept values(1,'测试部');
insert into dept values(2,'开发部');
insert into dept values(3,'市场部');
alter table info add deptid int;
update info set deptid=1 where id between 1 and 4;
update info set deptid=2 where id between 5 and 8;
update info set deptid=3 where id between 9 and 10;
select * from info;

select id,name,deptname--查询两张表的数据
from info,dept ;

select id,name,deptname,dept.deptid,--两张表通用一个字段名，前面加表名. 
from info,dept ;
--出现笛卡尔积现象（上面语句查询，出现10*3=30条数据）
--加上where条件避免出现笛卡尔积
select id,name,deptname,dept.deptid--两张表通用一个字段名，前面加表名. 
from info,dept 
where info.deptid=dept.deptid;

select id,name,deptname,b.deptid
from info as a,dept as b--可以将复杂的表名暂时使用简名代替
where a.deptid=b.deptid;


--【练习】
--按部门名称统计人员数量：部门名称，人数
select  deptname 部门名称,count(*) 人员数量
from info,dept
where info.deptid=dept.deptid
group by deptname

--按部门统计各姓氏人数：部门名称，姓氏，人数
select deptname 部门名称,left(name,1) 姓氏,count(*) 人员数量
from info,dept
where info.deptid=dept.deptid
group by deptname ,left(name,1)
order by deptname


select  deptname 部门名称,count(*) 人员数量--三表之间关联查询，找一个中间表
from info,ckinfo,dept
where info.deptid=dept.deptid and  info.id=ckinfo.id
group by deptname


【问题】已经出现了的解决办法→学习外连接
insert into info(id,name,deptid) values(11,'a',4);--丢人了 解决方法：建立外连接使丢失的数据也显示出来
insert into dept(deptid,deptname) values(5,'X部');--丢部门了
--按部门名称统计人员数量：部门名称，人数
select  deptname 部门名称,count(*) 人员数量
from info,dept            --自然连接
where info.deptid=dept.deptid  
group by deptname


--加入外连接  left (outer)join  ...     on
select  deptname 部门名称,count(*) 人员数量
from info left join  dept            
on info.deptid=dept.deptid  
group by deptname
--加入右外连接 right(outer)join ...  on
select  deptname 部门名称,count(*) 人员数量
from info right join  dept            
on info.deptid=dept.deptid  
group by deptname
--加入全外连接 full(outer) join  ... on
select  deptname 部门名称,count(*) 人员数量
from info full join  dept            
on info.deptid=dept.deptid  
group by deptname



以上问题从源头解决的方法 →学习外键约束
外键约束
--从源头解决 以下x1相当于部门表，x2相当于人员表
--外键约束:由从表（非主键字段）向主表（主键字段）发起建立外键
--主从表建立外键的字段名字可以不同，但数据类型和长度必须一致
--先写主表数据,再写从表数据
--当从表当中有主表的数据时,主表数据不能删除
create table x1(id int primary key,tname char(10));
create table x2
(x2id int primary key,
id int,
constraint fk_x2_x1 foreign key(id) references x1(id) --由从表x2向主表x1发起建立外键
);
insert into x2(x2id,id) values(1,1);--写不进去
insert into x1(id) values(1);--主表添加数据后
insert into x2(x2id,id) values(1,1);--可以写入

delete from x1;--删除不了,当从表当中有主表的数据时,主表数据不能删除
--特殊情况//从表设置为非空来解决（也就是人员表x2的部门id不能为空）
insert into x2(x2id) values (3)

--【任务11】
--1对部门表设置部门编号为主键,并写入一些部门数据
alter table dept alter column deptid int not null;
alter table dept add constraint pk_dept_deptid
primary key (deptid) ;

--2在人员表中增加部门编号字段
alter table info add deptid int;

--3在人员表造一些有部门编号的数据(部分数据在部门表中不存在)
select *from dept;
insert into info values(12,'王新',5000,'1794-02-03',null ,4);

--4查询如下信息:人员编号、名字、部门名称(要确保人员的数据不丢失)
select id,name,deptname
from info full join dept
on info.deptid=dept.deptid

--5在人员表和部门表中设计外键,写出相应步骤及sql
--①确定主从表(主表:dept;从表：info 字段为deptid);
--②查看从表中字段值是否存在主表不存在的值 (存在部门编号为4的值）
--③主表中‘部门编号’字段插入‘4’
insert into dept(deptid,deptname) values(4,'后勤');
--④创建info表（deptid)字段向dept(deptid)字段的外键请求
alter table info add constraint fk_info_dept foreign key (deptid) references dept(deptid);

--6描述建立外键的条件、作用
--答:保持主从表数据的一致性 ，完整性
    --主表中主键不存在的数据在从表的外键字段无法写入（null除外）
    --主表中主键不能删除在从表的外键字段上拥有的数据
    --由从表（非主键字段）向主表（主键字段）发起建立外键
    
--7由考勤表向人员表建立外键
create table ckinfo
(
id int not null,
sdate datetime,
esdate datetime
)
alter table ckinfo drop column esdate;--去掉字段名
alter table ckinfo add edate datetime;--添加字段名
select * from ckinfo;
insert into ckinfo(id,sdate,edate) --单个语句造数据
select id ,dateadd (hh,-3,getdate()) ,dateadd(mi,420,getdate())
from info;
alter table ckinfo add constraint fk_ckinfo_info foreign key (id) references info (id) ;--外键

--8统计各部门各月份上班迟到的次数：部门名称、年月、迟到次数
select deptname 部门名称,count(*) 迟到次数, substring(convert(char(24),sdate,121),1,7) 年月
from  dept, info, ckinfo
where dept.deptid=info.deptid and info.id=ckinfo.id 
and  substring(convert(char(24),sdate,121),12,5)>'08:30' 
group by deptname,month(sdate),substring(convert(char(24),sdate,121),1,7)
select * from ckinfo;
--9统计各部门上班迟到的人数：部门名称、迟到人数(*注意迟到人数与次数的区别)
insert into ckinfo(id,sdate,edate) --单个语句造数据
select id ,dateadd (hh,-10,getdate()) ,dateadd(mi,420,getdate())
from info;
insert into ckinfo(id,sdate,edate) --单个语句造数据
select id ,dateadd (hh,-12,getdate()) ,dateadd(mi,420,getdate())
from info;
insert into ckinfo(id,sdate,edate) --单个语句造数据
select id ,dateadd (hh,5,getdate()) ,dateadd(mi,420,getdate())
from info;
insert into ckinfo(id,sdate,edate) --单个语句造数据
select id ,dateadd (hh,24,getdate()) ,dateadd(mi,420,getdate())
from info;
select * from ckinfo

select deptname 部门名称,count(*) 迟到人数
from  dept, info, ckinfo
where info.id=ckinfo.id and dept.deptid=info.deptid  
and  substring(convert(char(24),sdate,121),12,5)>'08:30' 
group by deptname
order by deptname


select  deptname,count(distinct name)
from   ckinfo,info,dept
where  ckinfo.id=info.id 
and dept.deptid=info.deptid
and substring(convert(char(24),sdate,121),12,5)>'08:30' 
group by deptname

--10统计上班迟到的次数3次以上的人员姓名、迟到次数，并按迟到数从低到高排序

select name 姓名,info.id 编号,count(*) 迟到次数
from   info, ckinfo
where info.id=ckinfo.id and  substring(convert(char(24),sdate,121),12,5)>'08:30' 
group by name,info.id
having count(*)>2

select *from ckinfo where id in(1,5,9) and  substring(convert(char(24),sdate,121),12,5)>'08:30' order by id