2012 -02-26 SQL数据库
顺序---> 建库-建表-创建约束-插入-修改-删除-查询
数据库DataBase,不同的数据应该放到不同的数据库中
原因:1.便于对各个数据类别进行个性化管理(分布式部署)
2.避免命名冲突
3.安全性更高
1.数据库要做的工作:
a) 针对数据的修改维护方便(硬盘里)
b) 使用快捷的搜索算法,获取数据
c) 数据格式变化
d) 并发处理, 安全处理 数据完整性
2.DBMS(数据库管理系统)
a) 数据库文件--核心数据库服务--客户端软件(SQLServer)
3.SQL语句查询过程:
a) 客户端软件 先输入SQL语句
b) SQL服务器将SQL语句 交给查询分析器,分析SQL语句语法,,检查通过的语句交给查询优化器,进行优化代码,匹配已经写好的优化规则 集合, 生成查询树后,交给查询执行器到缓存管理器中查到有没有已经存在的数据,有的话直接返回,没有的话 直接到mdf文件中查找
4.数据库 表中的主外键:
手写SQL语句 ------定义主键用( primary key(主键名))
关联外键:----Foreign key 外键字段名 references 目标表名(被关联的字段名)
a) 主键:数据行的唯一标识, 不会出现重复数据的列才能当做主键.表都必须设置主键.
b) 业务主键 与 逻辑主键 . (建议使用逻辑主键)
i. 逻辑主键:使用没有任何意义的字段做主键,
ii. 业务主键:使用有业务意义的字段做主键, 比如:身份证号.
5.主外键的作用;
a) 解决并减少数据冗余问题
b) 方便维护-----> 便于修改数据信息.
c) 可以优化查询效率.(面试放在最后讲,举例)
6.主外键的关系:
两张表存在依赖数据时,就可以使用主外键来解决.其中将依赖列作为主键的就叫做:主键表. 另一个就叫做外键表. 外键表的外键列数据取自主键表的主键
7.主外键约束:
为了避免两张表的主外键 数据 出现不一致情况,需要建立主外键 约束关系.
作用: 当两张表数据修改时出现 主外键数据不一致,则报错,拒绝修改
8.分离/附加 数据库的方法
a) 关闭SQL服务器,然后拷贝
b) 分离数据库后---拷贝---附加数据库,找到mdf文件
c) 脱机/联机 数据库
9.char类型:当储存的数据 小于 长度时,会自动用空格来补充, 固定长度的非Unicode字符数据,最大为8000字符.
10.Nchar类型: 使用Unicode编码,任意字符都占两个字节.固定长度的Unicode数据
11.Varchar类型: 当储存的数据 小于长度时,不会自动用空格来补充.理论上可以节约空间.可变长度的非Unicode数据
12.Text类型: 存储 长文本信息(数据指针,指向所保存的数据, 2G).一般博客内容可以使用Text
13.Varchar(max) 会导致数据的保存和加载速度比较慢
14.Datetime类型: 储存日期时间数据,如果要 用代码类 标识一个具体时间要加单引号" --->'2012-02-02'.
15.数字类型 int smallint,float, real
16.货币数据类型: Money(C#中的double)
17.Bit 数据类型: 是/否 1:代表true 0:代表false
18.给数据库设计表结构时, 为每个表的列中的说明 进行备注(该列的含义)
19.注意:开启数据库服务器的方法:
Cmd-----开启 Sqlserver-----net start/stop mssqlserver
20.SQL语句中 字符串用 单引号,单等号处理.
21.SQL语句 除关键字与字符串值外, 大小写不敏感
22.DDL数据库定义语言 DML数据操作语言 DCL数据库控制语言
23.手动创建数据库
CREATE DATABASE 数据库名
ON [PRIMARY]
(
<数据文件参数> [，…n]
)
[LOG ON]
(
<日志文件参数> [，…n]
)
a) use 数据库名(帮我们选择所使用的数据库)
b) 创建数据库 create database HeiMaBlog
Create database TestSchool
on primary --默认就属于primary主文件组,可省略
(
name='TestSchool',--主数据文件的逻辑名
filename='F:\SQL数据库mdf文件夹\TestSchool_data.mdf',--文件路径
size=3mb,--主数据文件初始大小
maxsize=10mb,--主数据文件最大的值
filegrowth=1mb--主数据文件的增长率
)
log on--日志文件
(
name='TestSchool_log',
filename='F:\SQL数据库mdf文件夹\TestSchool_log.mdf',
size=3mb,
maxsize=15mb,
filegrowth=1mb
)
goc) Go关键字并不是SQL语法的规范,而是我们MSSQLServer软件的关键字(go是批处理语句,当客户端遇到go语句时,将go之前的语句当做单独的批次,发到服务器运行,然后再运行go之后的SQL语句.)
d) 自增 Identtity(1,1)
1.数据插入
1--Insert into 表名(列名1,列名2) values(值1,值2)
不写列名的前提: 当要向表中新增的数据中,包含所有的 (没有默认值或自动增长的列除外) 的值
2--自动编号列不需要手动插入。当表id设置为自动增长列时 又想手动设置id的值,要在插入语句前 将手动设置打开【SET IDENTITY_INSERT 表名 ON 】,on的时候必须手动给值. On开启代表自定义标识
3--N前缀: N'字符串' ,在服务器上执行的代码中(例如存储过程和触发器中)显示的Unicode字符串常量 必须以大写字母N 为前缀. 即使所用用的列已定义为Unicode类型,也应如此,否则可能导致不识别某些字符
2数据修改 update 优先级：Not > and > or
3.数据删除:
delete [from] 表名 where.... Delete只删除表数据,表还在 与drop 不同
a) 普通删除的时候,自增列的值不会清空.
4.Truncate + table +表名 删除表数据 表名---清空表里所有的行,同时重置自增列的值(从种子标量1开始计算)
a) Truncate语句非常高效.由于truncate操作采用按最小方式来记录日志,所以效率非常高,对于数百万条数据 使用truncate删除只要几秒,而使用delete则可能耗费几小时
b) 会把表中的自动编号重置为默认值
c) Truncate语句不触发delete触发器
5.Drop table 删除整个表结构
3.用户创建数据库及数据表的时候,其实就是把设置的数据(列的类型,列名等)都存入了系统表, 比如:我们select * from 表名 那么数据库会帮我们去系统表 , 根据表名查询出所有的列名,然后再查出对应的数据,效率比直接搜索 列名 要低
比如: 新增: insert into Users values()-->数据库会先帮我们去系统表查询该表的 自增字段的值(上一次新增生成的值),在此基础上+ 种子增量,求的当前要新增行的自增字段的值
约束: 保证数据的完整性(正确性)
数据库约束 是为了保证数据的完整性(正确性)而实现的一套机制.
0.非空约束,手动设置
1.主键约束(PK), primary key constraint 唯一 且 不为空
Alter table 表名 add constraint PK_表名 primary key(主键字段)
2.唯一约束(UQ),unique constraint 唯一,允许为空,但只能出现一次(鼠标右击--索引/键--添加--选择唯一键)
Alter table 表名 add constraint UQ_表名_字段名 unique(字段名)
3.默认约束(DF),设置列的默认值,就是默认约束(列属性里 对默认值进行设置)--在手写SQL语句字段后+ default '默认值'
Add constraint DF_表名_字段名 default('默认值') for 字段名
4.检查约束(CK), check constraint 范围以及格式限制 (设置限制表达式)
Add constraint CK_表名_字段名 check(约束范围)
5.外键约束(FK), foreign key constraint 表关系:保证外键值来源于主键
增加外键约束时, 设置级联更新, 级联删除
手写外键约束: Alter table 表名 add constraint FK_表名_字段名 foreign key(外键名) references 表名(主键名)
数据检索(查询)
1.select * from 表名 查询时,先到数据库系统表中, 查出表的所有列名,然后再根据列名查数据----限制结果集的 列
2. As 为 结果集的 列 取别名
3.Where 条件 根据条件查询,限制结果集的行.
----------Top语句--搜索结果集里的前N条 数据
1. 搜索普通的整数行: Select top 3 * from 表名
2. 搜索百分数: Select top 20 percent * from 表名 (只要出现小数就自动加一)--天花板
---------distinct关键字--针对查出的整个结果集 , 去除重复值
Select distinct age from 表名 求出不重复的age
Select distinct age,salary from 表名 求出age,salary组合不重复的(相当于将两个列的值 加在一起 不重复)
----------聚合函数(结果只有一个单元格)--------
1.聚合函数的结果集是单个值,没法与多个值的结果集结合
2.聚合函数可以和其他聚合函数的结果组合
3.Min(),max(),avg(),sum(),count()
4.Count()--结果集行数, 统计某一列的时候 会忽略表中的null值
5.avg(age) age列设置为int时,如果值是小数,也直接当整数算,不会自动加1(向下取整)
------between and 求两个值的区间,推荐使用----
Where between 18 and 20 删除 age为18 19 20 的列
-----in 关键字--------
1.Where id(1,4,6,7) 相当于 where id=1 or id=4 or id=6 or id=7
2.可以应用于 子查询, 当使用子查询配合in 关键字时,子查询的结果集必须只有一个列,而且 列的类型 必须和 条件列类型一致
--------模糊查询 like , 通配符 _ % [] ^----
--------not like--取like的反值
1._ : 单个任意字符
2.%: 任意长度,任意字符 where name like '%杨%'
3.[ ]: 代表取值范围中的一个字符 [a-z]:指所有的英文字母
4.^: 取非符 必须和[ ] 一起连用,一定要放到[ ]中 代表取非 [^a-z] .
5.要通配 _ ,%,[,^---->这些字符,[_],[%],[[],[^],
-------空值处理- is null 和 is not null------
1.数据库中null 表示 "不知道",与任何相关的都是不知道
2. Is null : 判断查询列中的值是不是null时,用is关键字---> name is null
3.Is not null : 查询非空值
4.Isnull(被判断的值,替换值)-->如果第一个参数为null,则使用第二个参数作为返回值,否则返回第一个参数.
--------排序order by 列名-------------
1.order by 列名 默认为升序(asc), 降序为desc
2.Order by age desc, id asc 多条件排序,当age中有相等的值,那这些行在按升序排列
3.Order by 语句必须放到 where语句之后,一般放在所有语句后,就是先让其他语句进行筛选,最后在进行下排序.
-------分组-group by 查询的是组的信息,不牵扯 个人信息---------------
1.根据年龄统计,各个年龄段的人数
Select count(*), age from Teacher group by age2.强调---分组的结果是组的信息,与表中的单行信息无关
3.group by 分组后, 查询语句中 只能出现聚合函数 或者 分组的列
4.多条件分组: group by age, gender. 分组条件是age 和 gender 列的组合,只有当age和gender一样的值时,才分为一组.
-------having 语句 跟在group by语句之后---------
1.对分组后的信息 进行筛选的. 使用几乎与 where一样,也可以用 in....
--------类型转换函数---------------
1.cast(data as type): 将某个值 转成某个类型
Cast('123' as int ) --select cast(cast as int )将工资转成整型
Convert(int,'123') 类型转换(更强大)
------right() , left() 用来切割字符串
Right('abdfg',2) 从右边截取长度为2的字符串
Left('abcdef',3) 从左边截取长度为3的字符串
--------联合结果集 union(集合运算符)-------
1.合并结果集union用法:()
a) 要union的两个结果集的列数必须一致
b) 要链接的两个对应列的类型必须一致
c) 将多个结果集合并成一个结果集,去除重复值,,如果不想去除重复的话,用union all 链接
Select id,name,age from teacher
Union
Select id,name,age from student2. 一次插入多行数据:
将T表中的数据 赋值到已经存在的T1表中
Insert into T1(id,name,gender,age)
Select *from T把现有表的数据 插入到 新表(表不存在) ,将 oldTable表中的数据插入到新表newTable中
select * into newTable from oldTable
-------------字符串函数--------------------
1. len() : 计算字符串长度 len('aaaaa')
2. Datalength() : 获得字节的长度,Unicode代表2个字节
3. Lower() 转小写 upper() 转大写
4. Ltrim() : 去掉字符串左边的空格
5. Rtrim() : 去掉字符串右边的空格
6. Substring(原字符串, 从哪里开始截取, 所截取长度) ,索引从1开始计
----------------日期函数----------------
1.getdate() 获得当前日期时间
2.Dateadd(day, 10, getdate()) 修改日期(修改的部分-day,month,year, 增量,要新增的时间)
3.Datediff(day, '2011-02-26','2011-03-26') 求两个日期的差值
4.Datepart(day, getdate()) 获得日期的某个部分
a) Year() ,hour() ,day(),求时间的某个部分
开窗函数(应用于数据库分页技术): 只能出现在 select 或order by 子句中
1.row_number() over(order by 排序的列名)
2.Select row_number() over(order by ... desc) as rownum,+所要查询的列名From 表名 ---查询出 排序后的结果集
3. 返回结果集中的 第三行到第五行
select * from
(
Select row_number() over(order by ... desc) as rownum,+所要查询的列名
) as table
Where table.rownum>=3 and table.rownum<=5
