SQL复习框架
数据库系统
- 数据:date描述事物的符号 ,可以出现多种载体,比如:声音、图片、文字等
- 数据库:database(简写db)数据的仓库 在计算机内可以长期存储,并且可以共享的数据的集合
- 数据库管理系统:(DBMS)管理数据库的一些软件 sql 2008 mysql oracle DB2
- 数据库系统:包含数据库管理系统和管理员 (DBS)
(第一部分):数据库
1:系统数据库
n Master:记录系统信息sys开头
n Msdb:代理数据库发出作业警报
n Tempdb:临时数据库
n Mode模型数据库l
n 注意事项:
n 数据库管理系统以数据模型为核心:网状模型 层次模型 关系模型
2.用户自定义数据库
创建数据库 |
查看数据库 |
修改数据库 |
删除数据库 |
Create database dbname |
Exec sp_helpdb(查看所有数据库) Exec sp_helpdb dbname(查看具体的某一个数据库) |
Exec sp_renamedb ‘oldname’ ‘newname’(修改名称) Alter database dbname(修改数据库) |
Drop database dbname |
注意事项:数据文件(首要:mdf次要:ndf)和事务日志文件(ldf) |
|||
实例: |
//(1)创建学生数据库student Create database student //(2)使用当前数据库student Use student //(3)查看学生数据库student的信息 Exec sp_helpdb student //(4)修改学生数据库student名称为stu Exec sp_renamedb ’student’,’ stu’ //(5)删除学生数据库stu Drop database stu
|
(第二部分):表
- 表结构
数据类型
|
(1) 整型tinyint int smallint bigint (2) 实型float real numeric decimal (3) 字符型char(固定字节) varchar(可变字节) text (1) 日期与时间类型datetime |
||
创建表 |
查看表结构 |
修改表结构 |
删除表结构 |
Create table studentinfor( Son int, Ssname varchar(20), Ssex varchar(20), Ssage int ) |
Exec sp_help studentinfor |
Alter tablename columnname 增加字段 Add 字段名 type 删除字段 Drop column 字段名 修改列名 Exec rename ‘stuno’,’snum’ 修改表名 Exec sp_rename 修改属性 Alter column sno varchar(20) |
Drop studentinfor |
实例 |
//(1)创建学生信息表studentinfor (包含学号sno 姓名sname 性别ssex 年龄sage) //(2)查看表结构 //(3)修改表 n 增加字段籍贯snative 学院scollege n 删除学段年龄 n 修改字段年龄sage为stuage n 修改字段性性学号sno 整型为字符型 //(4)删除表结构
|
- 表约束
主键:一个表中只能有一个,字段可以有多个,主键值不能为空 (primary key) |
外键:一个表中可以有多个,参考的那个字段必须是主键 外键所在的表叫做外键表(从表) (foreign key) |
唯一:只可以出现一次空 (unique) |
默认:可以有多个 (default) |
空/非空 (null/not null |
检查 (check) 一个表可以有多个 |
||||||||||||||||||||||
标识列 identity |
只适用于数值类型,默认起始值是1,增量也是1 |
||||||||||||||||||||||||||
|
创建学生信息表stuinfor
创建学生课程表cinfor
创建学生成绩表scinfor
|
||||||||||||||||||||||||||
实例 (创建表 后添加)
|
Create table stuinfor (sno int, Sname varchar(20), Ssex varchar(20), Sage int, Sphone varchar(20), Scollege varchar(20)) Alter table stuinfor Add constraint zj_1 primary key(sno) Alter column sname int not null Add constraint jc_1 check(ssex=’男’ or ssex=’女’) Add constraint jc_2 check(sage between 18 and 50) Add constraint wy_1 unique(sphone) Add constraint df_1 default ‘软工’ for scollege |
||||||||||||||||||||||||||
实例 (创建表 同时添加) |
Create table stuinfor( Sno int primary key, Sname varchar(20) not null, Ssex varcar(20) check(ssex='男' or ssex=''女) default '女', Sage varchar(20) check (sage between 20 and 30), Sphone varchar(20) unique, Scollege varchar(20) default '软工' ) Create table cinfor( Cno int primary key, Cname varchar(20) not null ) Create table scinfor( Sno int foreign key (sno) references primary key stuinfor(sno), Cno int foreign key(cno) references primary key cinfor(cno), Scroe int check(check between 0 and 100) ) |
- 表操作
添加insert into |
n 完全添加 insert into 表名 values(值,值,....) n 部分添加 insert into 表名(字段,字段,...) values(值,值,...) n 一次添加多条 insert into 表名(字段,字段,...) values(值,值,...),(值,值,..) n 简写格式 insert 表 values(值,值,..) //(1)将1,张三,男,22,13245673452,软工添加到stuinfor insert into stuinfor values(1,'张三','男',22,'13245673452',default) //(2)将2,李四,男添加至stuinfor Insert inton stuinfor(sno,sname,ssex) values(2,'李四','男') //(3)将1,HTML 2.JAVA 3JAVASCRIP添到cinfor Insert into cinfor values(1,'HTML'),(2,'java'),(3,'javascript') //(4)将1,1,68添加至scinfor(简写格式) Insert scinfor values(1,1,68)
|
更改 updae |
n 表中全部记录的一个或多个字段更改 update 表 set 字段=值,字段=值 update stuinfor set sage=sage+1 n 表中满足条件记录的一个或多字段更改 update 表 set 字段=值,字段=值 where 条件(and or...) n 以空为条件的更改 update 表 set 字段=值 where 字段 is null //(1)将stunfor表中全部学生的学院改为环境 Update stuinfor set scolege='环境' //(2)将性别为男的学生的学院改为软工,年龄增加一岁 Update stuinfor set scolege='软工' ,sage=sage+1 where ssex='男' //(3)将所有学院为空的学生记录的年龄改为20 Update stuinfor set sage=20 where scollege is null |
删除 delete |
n 删除满足条件的记录 delete from stuinfor where 条件 n 以空为条件的删除 delete from 表 where 字段 is null n 删除简写格式 delete 表 n 删除表中全部记录 delete from 表 //(1)将所有广告学院女同学的信息删除 Delete from stuinfor where scollege='广告' and SSEX='女' //(2)将所有电话为空的记录删除 delete from stuinfor where sphone is null //(3)删除表中的全部记录 delete from stuinfor |
查询 select |
(一) 单表查询 n 查询表中全部记录 select * from 表 n 查询表中指定字段记录 select 字段,字段,...from 表 n 查询前n条记录 select top n percent * from 表 n 带条件的查询 select * from 表 where 条件 n 查询去掉重复项 select distinct 字段 from 表 n 查询使用聚合函数(并起别名) max() sum() min() avg() count() select A=B from 表 select B a from 表 select B as a from 表 n 模糊查询 like _单字符 %一个或多个字符 [] 范围内 [^ ]不在范围 n 查询时对字段排序
n 合并查询结果 union n 查询创建新表 select 字段,字段 into 新表名 from 表 n 查询使用分组命令. select * from stuinfor --统计女学生的平均年龄 select AVG(stuage )from stuinfor where ssex='女' --统计男学生的平均年龄 select AVG(stuage )from stuinfor where ssex='男' --分别统计男女学生的平均年龄 select stusex,AVG(stuage) from stuinfor group by stusex
(二) 多表查询 n 交叉连接 select * from stuinfor,cinfor,scinfor n 内连接(正确) select 字段,字段 from 表,表 where 表.字段=表.字段 select 字段,字段 from 表 inner join 表 on 表.字段=表.字段 and ... n 外连接outer join l 左外连接 (LEFT join) 左表全显右表满足显 不满足以null显示 l 右外连接 (right join)右表全显左表满足显 不满足以null显示 l 完全连接 (full join)左+右 //(1)查询学生信息表中的全部记录 select * from stuinfor //(2)查询学生信息表中学生的学号,姓名,学院 Select sno,sname,scollege from stuinfor //(3)查询学生信息表中软工或动画学院学生信息 Select * from stuinfor where scollege='软工' or scollege='动画' select * from stuinfor where scollege in('软工','动画') //(4)查询学生信息表中年龄在23—27之间学生信息 Select * from stuinfor where sage between 23 and 27 select * from stuinfor where sage>=23 and sage<=27 //(5)查询学生信息表中年龄>28并且是软工的学生信息 Select * from stuinfor where sage<28 and scollege='软工' //(6)查询学生信息表中前五条记录的学号,姓名,性别 Select top 5 sno,sname,ssex from stuinfor //(7)查询学生信息表中学生来自哪些学院(几个) Select scollege from stuinfor (select count(distinct scollege )from stuinfor) //(8)查询学生信息表中学生的最大年龄及最小年龄 Select max(sage) 最大值 ,min(sage) 最小值 from stuinfor //(9)查询成绩表中选修1号课程的总分及平均分 Select sum(score) ,avg(score) from scinfor where cno=1 //(10)查询成绩表中成绩合格的个数 Select count(score) from scinfor where score>=60 //(11)按由高到低的顺序查询学生成绩表中的学生信息 Select * from scinfor order by score desc //(12)查询姓李的学生信息 Select * from stuinfor where sname like '%李' //(13)分别统计每个学院的平均年龄 Select avg(sage) from stuinfor group by scollege //(14)查询学生的学号,姓名,学院创建一个新表n1 Select sno,sname,scollege into n1 from stuinfor //(15)查询所有选修了Java课程的学生的学号,姓名,性别,学院及成绩 select stuinfor.sno,sname,ssex,scollege,scroe from stuinfor,cinfor,scinfor where scinfor.sno=stuinfor.sno and scinfor.cno=cinfor.cno and cname='java'
select stuinfor.sno,sname,ssex,scollege,scroe from stuinfor inner join scinfor on stuinfor.sno =scinfor.sno inner join cinfor on scinfor.cno=cinfor.cno and cname='java'
//(16)查询所有学生的选课情况 select * from stuinfor left join scinfor on stuinfor.sno=scinfor.sno
|
(第三部分)T-SQL
- 基本概念
n T-SQL
DDL DCL DML 流程控制语句
n 变量的声明,赋值,计算,输出
n 函数
分类 |
函数名 |
作用 |
聚合函数 |
|
|
数学函数 |
|
|
字符串函数 |
|
|
日期与时间函数 |
|
|
转换函数 |
|
|
- 流程控制语句
n If..else
n Case
n While
n Begin…end
n Return
n Goto
n waitfor
(第四部分)视图
创建视图 |
查看视图 |
修改视图 |
删除视图 |
create view view_1 as select * from
select * from sysobjects where type='v'
|
|
|
|
(第五部分)数据库的安全性
n 分离与附加加
n 备份与还原
n 修改身份验证模式