首页 > 代码库 > SQL复习框架

SQL复习框架

数据库系统

  1. 数据:date描述事物的符号 ,可以出现多种载体,比如:声音、图片、文字等
  2. 数据库:database(简写db)数据的仓库    在计算机内可以长期存储,并且可以共享的数据的集合
  3. 数据库管理系统:(DBMS)管理数据库的一些软件 sql 2008   mysql  oracle  DB2
  4. 数据库系统:包含数据库管理系统和管理员 (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. 表结构

 

   数据类型

 

 

(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)删除表结构

 

 

  1. 表约束

主键:一个表中只能有一个,字段可以有多个,主键值不能为空

(primary key)

外键:一个表中可以有多个,参考的那个字段必须是主键  外键所在的表叫做外键表(从表)

(foreign key)

唯一:只可以出现一次空

(unique)

默认:可以有多个

(default)

空/非空

(null/not null

检查

(check)

一个表可以有多个

标识列

identity

只适用于数值类型,默认起始值是1,增量也是1

 

 

 

 

 

 

创建学生信息表stuinfor

                                                           
   

学号

   
   

姓名

   
   

性别

   
   

年龄

   
   

电话

   
   

学院

   
   

sno

   
   

sname

   
   

ssex

   
   

sage

   
   

sphone

   
   

scollege

   

 

创建学生课程表cinfor

                           
   

课程号

   
   

课程名

   
   

cno

   
   

cname

   

 

创建学生成绩表scinfor

                                   
   

学号

   
   

课程号

   
   

成绩

   
   

sno

   
   

cno

   
   

scroe

   

 

 

 

实例

(创建表

后添加)

 

 

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)

)

 

  1. 表操作

添加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  查询时对字段排序

  • order by (asc 升 desc 降)

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

  1. 基本概念

n  T-SQL

DDL  DCL   DML   流程控制语句

n  变量的声明,赋值,计算,输出

n  函数

分类

    函数名

作用

聚合函数

 

 

 

数学函数

 

 

 

字符串函数

 

 

 

日期与时间函数

 

 

 

转换函数

 

 

 

 

  1. 流程控制语句

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  修改身份验证模式

SQL复习框架