SQL学习

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

)

go

c) 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 age

2.强调---分组的结果是组的信息,与表中的单行信息无关

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 student 

 

2. 一次插入多行数据: 

将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

 

 

 

转载于:https://www.cnblogs.com/zxp19880910/archive/2012/07/09/2583668.html