sqlserver基础

数据库事务？
为数据库操作序列提供了一个从失败中到恢复正常状态的方法，还有就是如果真的出现了异常仍然能够保证一致性的方法

当有多个应用程序访问同一数据库时能够为这些操作做好隔离，防止多个应用程序互相干扰
数据库事务拥有四个特性
原子性：事务作为一个整体被执行，要么都执行要么都不执行
一致性：事务执行完必须是由一个状态转变成另外一个状态，一致性的含义是数据库中的数据要满足完整性约束
隔离性：多个事务并发执行，事务之间互不影响
持久性：事务执行完毕就是永久性的，也修改的数据永久地保存在数据库中

Select语句：

1. 查询所有记录

Select *from 表名;

*代表所有字段也可写成

Select 学号,姓名,性别,成绩 from student;

1. 查询满足一定条件的记录（查询满足成绩在[60,70]），并列出他们的姓名,成绩信息

Select 姓名,成绩 from student

Where 成绩>=60 and 成绩<=70;

1. 对查询结果进行排序

查询成绩合格的学生，并按成绩降序显示查询结果

Select * from student

Where 成绩>=60

Order by 成绩 DESC;

说明：DESC 降序

  ASC  升序（默认好像是升序的）

1. 模糊查询

需要通过通配符来实现，通配符 “%” 可以匹配任意的字符串

例：查询所有姓王的学生

Select * from student

Where 姓名 like ‘王%’;

统配符”_”则只能匹配一个字符

例：查询姓王并且姓名仅由两个字构成的学生

Select *from student

Where 姓名 like ‘王_’;

SQL windows 身份验证

SQLserver 身份（在其他地方也可以登录）

创建用户：

普通用户不可以再创建用户

（强制密码过期去掉）

数据库是如何处理存储问题的？

把能够唯一标识事物的属性称为主键（例如学号）

软件最赚垄断部门（煤炭石油）

设计任何一个事物都需要主键

唯一的标识符

数据库研究的就是事物要怎么存，关系怎么存

个体与个体之间的关系

关系型数据库

Java 面向对象的

关系是通过外键（连接两个表的字段）来表示的

多对一：将外键多的一方

写在例如

员工表

员工姓名	员工工资	员工性别	员工编号（主键）	部门编号（外键）
张三	1000	男	10001	1
李四	2000	男	10002	2

 

部门表

部门名称	部门人数	部门地址	部门编号（主键）
市场部	1000	西安	1
研发部	50	西安	2

 

设置成外键的字段不能随便更改

列就称为字段或者属性

行称为记录也称为元组

字段：一个事物的某一个（静态的）特征。

记录：字段的组合，表示的是一个具体的事物。

表：记录的组合，表示的是同一类事物的集合。

表和字段记录的关系：

字段是事物的属性

记录是事物本身

表是事物的集合

建表两种：①鼠标点②写命令

nvarchar（） 表示是可变的字符类型

表 dbo. 系统的身份权限最大当前用户本身

给表起名不能是user（因为user为系统用户名，是一个关键字）

 

Update 表 set 属性 = xxx

约束有：

非空约束

主键约束

外键约束

Check约束（设置范围约束）

为什么设置主键？？

防止事物冗余（有重复）

外键实际上就是关系

外有外键字段的表叫做外键表

设置完外键之后外记得保存

在SQLserver中注释是二杠 “--”

主流编程都源自于C语言，但是数据库与C无关系

使用命令来建表：

Create table emp

(

emp_id int primary key,   --主键

Emp_name nvarchar(20) not null, --非空约束（存汉字必须把n添上）

emp_sex nchar(1)

)

写好表之后先点√号分析一下，然后再执行

dept_id int constraint fk_dept_id_height foreign key references dept(dept_id)

如果没有给键起名，那么系统会自动产生一个

什么是约束？？

定义：对一个表中的属性操作的限制叫做约束

分类：

主键约束

外键约束

非空约束

Check约束

emp_sal int check(emp_sal>=1000 and emp_sal <=8000)

 

插入数据：

Insert into 表名 values()

Default 约束

stu_sex nchar(2) default(‘男’) -- 括号可以不写

在数据库中字符串用单引号’’

名字（也就是字段名）使用双引号””

唯一约束与主键有什么区别？

主键不能为NULL 而unique约束可以为NULL

如何合理利用主键和唯一键建表？

用有实际意义的键当主键不好

一般都是使用编号来作为主键

有实际意义的而且还不允许重复的键当unique（唯一）键

stu_id int primary key identity    --自动增长不需要设置值了

stu_name nvarchar(20) unique not null

删除表：

drop table 表名

 

master 库

Insert 数据没有指定输入几个属性，不管是否有default 默认约束必须给每个属性都赋值

数据库是通过表来解决事物的存储问题的

 

 

一对一：  （假设是A表和B表）

既可以把A表的主键充当B表的外键也可以反过来，都行

一对多：

把表A的主键当做表B的外键，将外键设置在多的一方

多对多：

例 班级与老师的关系

不可以在班级表或老师表中添加外键，必须要再创建一个表来表示多对多这种关系

 

班级表

Id	人数
1	20
2	30
3	40
4	50

老师表

Id	姓名
1001	A
1002	B
1003	C
1004	D

新建表

班级	老师	课程
1	1001	C
1	1002	PHP
2	1001	Java
3	1001	SQL

任何一个表一定有主键，但是不一定有外键

数据库最终讨论的就是数据的存储问题，如何用表来描述事物之间的关系

 

表名一般写单数

Mapping 映射

不允许一个表有两个属性为外键

Create table BanJi_jiaoshi_mapping

(

Banji_id int constraint fk_banji_id foreign key references banji(banji_id),

Jioashi_id int foreign key references jiaoshi(jiaoshi_id),

Kecheng nchar(20),

Constraint pk_banji_id_jiaoshi_id primary key (banji_id,jiaoshi_id),

)

设置banji_id,jiaoshi_id 整体为主键

从数据库中删除关系就是删除的约束条件，并不删除字段

查询：

1. 计算列
2. Distinct
3. Between
4. In
5. Top
6. Null
7. Order by
8. 模糊查询
9. 聚合函数
10. Group by
11. Having
12. 连接查询

嵌套查询：

Select ename,sal*12 as “年薪” from emp;

as 可以省略，最好写成”” 双引号

Select deptno from emp;

Select 1000 from emp;

先找到emp表，找第一套记录，找deptno 值，找到就输出，如果有14行记录，那么就输出14行1000

Select distinct deptno from emp;

Distinct 也可以过滤掉空，有多个空则只输出一个空

Select distinct comm,deptno from emp;

这样是对comm,deptno 整体进行过滤掉，使comm deptno两个值不相同

Select deptno distinct comm from emp;

这样不可以执行，逻辑上有错误

 

Select *from emp

Where sal between 1500 and 5000; --可以不写分号

 

Where sal in(1500,3000,5000)

查询sal值为1500,3000,5000的记录

或者这样写

Where sal =1500 or sal =3000 or sal=5000

 

Select top 2 *from emp --查询前两条记录

Select top 15 percent* from emp --查询整个表中的15%的记录，如果不是整数，则取整

 

6、null 不能参与 < 、>、 !=、 = 运算

可以参与 is isnot

Select *from emp

Where comm is null

datatime 也是个数据类型

Null 不能参与参与任何运算，否则结果都是null

任何数据类型都可为null

1. order by deptno sal;

先将deptnno 升序排列，如果 deptno相同，再将sal升序排列

Order by deptno asc,sal;

先将deptno 降序排列，如果deptno 相同再将相同的记录给sal进行默认升序排列

Order by deptno ,sal desc;

1. select * from emp

Where ename like ‘%A%’ --含有A的

格式：

Select 字段的集合from 表名

Where 某个字段的名字like 匹配的条件

 

通配符：

①%：表示任意0个或者多个只读

Where ename like ‘%A’; ename 尾字母为A的记录

②_（下划线）表示任意单个字符

Select *from emp

Where ename like ‘_A%’   --第二个字符为A的记录

③[A-F]

Select * from emp

Where ename like ‘_[A-F]%’  --ename 中第二个字符是A~F之间的记录

④[a-f]

⑤[^a-c] ^表示取反，不是a~c之间的字符

注意匹配的条件必须用单引号括起来

 

escape ‘\’ 表示\符号被当做特殊字符

 

1. Select lower(ename) from emp; --转成小写
2. Select max(sal) from emp; --sal最大的记录

聚合函数：

max()

Sum()

Min()

Arg() --平均值

Count() --求个数

例：

Select count(*) from 表名; --返回表共有多少记录

Count()函数重复的记录也会被计算，空值不会被重复计算

Count(distinct 字段名)

返回字段不重复且非空的记录个数

Select max(sal) lower (ename) from 表名

这样使用会出错，单行函数和多行函数通用会出错

进行 min() 操作是会忽略空值

Having 短语一般与group by 混用

 

向已经建好的表中添加新的列或添加约束

Alter table 表名

Alter column 字段名 类型

Add 字段名 类型

Drop column 字段名

添加索引

Create index 字段名 on 表名

 

1. 聚合函数

函数的分类

①单行函数： 每一行返回一个值

②多行函数：多行返回一个值

例：

Select max(sal) from emp;

聚合函数是多行函数

聚合函数分类：

Max()

Min()

Arg()

Count()

Select count(*) from emp; --返回emp表记录的个数

Select count(deptno) from emp;

Select count(distinct deptno) from emp; --重复的记录不在进行计算

Select count(comm) from emp;

 

Count(*) 返回表中所有记录的个数

Count(字段名) 返回字段非空的记录个数，重复的记录也会被重复计算

Count(distinct 字段名)

注意的问题：

Select max(sal , lower(ename)) from emp;

出错，单行函数不能与多行函数混用

 

1. group by

Having 对分组后的数据进行过滤

例：

输出每个部门编号和平均工资

Select deptno,arg(sal) as “部门平均工资” from emp

Group by deptno;

按deptno 分组 ，分完组输出deptno arg(sal)

分组后统计的是组的信息

Select deptno,ename from emp

Group by deptno

出错！不能写deptno内部的信息

总结：使用了group by 之后，select 只能出现分组后的整体信息，不能出现组内的信息

 

求部门相同，并且工作相同的平均工资：

Select deptno ,job,arg(sal) ,count(*),sum(sal),min(sal) from emp

Group by deptno,job; --先按照部门分组，部门如果一样，在按照工作分组

 

Group by

格式：group by 字段的集合

功能：把表中的记录按照字段分成不同的组

 

Group by a,b,b

最终统计的是最小分组的信息

1. having 对分组之后的信息进行过滤

例：

输出部门平均工资>2500的部门便你好，部门的平均工资

Select deptno ,arg(sal) from emp

Group by deptno

Having arg(sal)>2500

Having 和where 的区别？

例：

把姓名不包括A的所有员工按部门分组，输出部门工资>2000的部门编号，平均工资

Select deptno,arg(sal),count(*),max(sal) from emp

Where sal > 2000

Group by deptno

Having arg(sal) > 3000

Where 对原始记录过滤

Having 对分组后的记录进行过滤

顺序不能改变

1. 连接查询

定义：将两个表或者两个以上的表以一定的连接条件连接起来，从而检索出满足条件的数据

分类：

内连接

外连接

①select * from A表 , B表产生的是笛卡尔集

产生的共有行数=A表行数*B表行数

列数=A列+B列

没有什么连接条件的话，那么就是A表的每一条记录与B表的每一条记录都有连接

②select *from A , B

Where A.emmpno=1000    --根据条件进行连接

③select ……from A join B on ……用法

Join 是表与表进行连接 on后面的是连接条件

④select ……from A , B where……

这样写和③中的写法结果都是一样的

On 指定连接条件

Where 对连接表进行过滤

左连接与右连接

例：

A表

Aid	Adata
1	a1
2	a2
3	a3

B表

Bid	Bdata
1	b1
2	b2
4	b4

 

左连接

Select * from a left join b

Where a.aid=b.bid;

执行过程是先取出a表中的所有数据在取出b中的满足条件的数据，然后合成一个表结果为：

 

1	a1	b1
2	a2	b2
3	a3	null

 

右连接

Select* from a right join b

Where a.aid=b.bid  结果为：

1	b1	a1
2	b2	a2
4	B4	null

 

• 查询选修了课程名为数据库的学生的姓名和年龄

Select sname , sage from student , sc , course

Where student.sno=sc.scno and sc.cno=course.cno and cname=’数据库’

• 查询选修了2课程与3课程的学生的姓名和学号

Select sname,student.sno from student ,sc

Where sno in (select sno from sc where cno=2) and sno in (select sno from sc.where cno =3);

③查询选修了全部课程的学生姓名

Select sname from student

Where sno in(select sno from sc  

group by sno

Having count(cno)=(select count(*) from course) )

该问题是先在sc表中找满足全选课程的学号，然后在查看该学号在student中的学生姓名

 

 

将查询到的数据添加到另一个表中去：

Insert into 要添加数据表名(字段列表)

Select 字段列表 from 原始表

注意的是：insert into 目标表(字段列表)

不是insert into 目标表 values(字段)