SQL基础知识

数据库中常用的概念

l         Sql本身是一个服务器，没有界面

l         Management Studio  只是一个SQL Server管理工具而已，不是服务器。

l         Sql server 在管理工具下面的服务SQL Server (MSSQLSERVER) 

l          

l         主键：就是唯一定位的一条数据的列。不会有重复的列才能当主键， 一个表可以没有主键，但是会非常难以处理，因此建议都要设主键。

两种主键:

u       业务主键：使用有业务意义的字段做主键，如身份证号、职工编号。

u       逻辑主键：使用任何没有意义的字段做主键，完全给程序看的。推荐用逻辑主键

常识：

1、  sql语句中字符串是用单引号

2、  sql语句大小写不敏感的。

分类	备注和说明	类型	说明
二进制数据类型	存储非子符和文本的数据	Image	可用来存储图像
文本数据类型	字符数据包括任意字母、符号或数字字符的组合	Char	固定长度的非 Unicode 字符数据
		Varchar	可变长度非 Unicode 数据
		Nchar	固定长度的 Unicode 数据
		Nvarchar	可变长度 Unicode 数据
		Text	存储长文本信息(指针,2G) 保存的是字节数据
		Ntext	存储可变长度的长文本
日期和时间	日期和时间在单引号内输入	Datetime	日期和时间
数字数据	该数据仅包含数字，包括正数、负数以及分数	int smallint	整数
		float real	数字
货币数据类型	用于十进制货币值	Money
Bit数据类型	表示是/否的数据	Bit	存储布尔数据类型

Guid 算法是一种可以产生唯一标识的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生成的GUID永远不会重复，无论是同一个计算机上还是不同的计算机。/

 数据库的类型uniqueidentifier

 SQLServer中生成GUID的函数newid()，.Net中生成Guid的方法：Guid.NewGuid()

use 数据库名，打开该数据库，有时候在新建查询时没选择对应的数据库，所以用这条语句。或者手动选择该数据库

l         Insert语句可以省略表名后的列名，但是不推荐

l         如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。

l         当不提供字段名时，插入值需要加入默认值defaulf 也就默认值用defaulf表示

插入数据  insert  into 表名 (字段名) values  (值)

例：insert into student ([name],age,sex,birthday) values ('张三',21,1,1995-02-26)

字段与值一一对应

 

1. 新建一个表

    语法：create table 表名 (字段1 类型1，字段2 类型2，……)

         添加主键 primart key (字段)

例：

    CREATE TABLE T_Employee     --创建一个表表名是T_Employee

    (FNumber VARCHAR(20),   --字段 类型

    FName VARCHAR(20),

    FAge INT,

    FSalary NUMERIC(10,2),

    PRIMARY KEY (FNumber)); -- 设置主键

 

2. 删除整个表：Drop Table 表名

 

更新

语法：update Table set col=value,col2=value2 where ...

          update 表名 set 字段 = 值，字段2 = 值2 where 条件

l       更新一个列:UPDATE T_Person Set Age=30

l       更新多个列:UPDATE T_Person Set Age=30,Name=‘tom’

l       更新一部分数据： UPDATE T_Person Set Age=30 where Name=‘tom’，用where语句表示只更新Name是’tom’的行，注意SQL中等于判断用单个=，而不是==

l       Where中还可以使用复杂的逻辑判断UPDATE T_Person Set Age=30 where Name=‘tom’ or Age<25，or相当于C#中的||（或者）

l       update Person1 set NickName=N'二十岁'  在字符前面加上N代表使用Unicode编号

l       where (Age>20 and Age<30) or(Age=80)

l       Where中可以使用的其他逻辑运算符：or(或)、and(与)、not(非)、<、>、>=、<=、!=（或<>）等

3. 修改表

       添加字段

语法:alter  table 表名 add 字段 类型

例：ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);

4. 删除

语法：delete [from] Table where col=data

         delete [from] 表名 价目where 条件

l       删除表中全部数据：delete from 表名

l       Delete只是删除数据，表还在，和Drop Table不同。

l       Delete 也可以带where子句来删除一部分数据：DELETE FROM T_Person WHERE FAge > 20

 删除表中全部的数据

1、  Delete 表名  删除之后的id继续上一次的值后。

2、runcate Table 表名  同时还原标识 也就是说设置默认的id从1开始

                    

5. 检索

语法： select col,col2[,*] from Table where ...

            Select 字段 from 表名 where 条件

l       简单的数据检索 ：SELECT * FROM表名

l       只检索需要的列 ：SELECT 字段 表名

l       列别名：SELECT FNumber AS 编号,FName AS 姓名,FAge AS Age111 FROM T_Employee

l       使用where检索符合条件的数据：SELECT 字段 FROM 表名 WHERE 条件。故事：新员工的数据检索噩梦。

l       还可以检索不与任何表关联的数据：select 1+1;select newid();select getdate();

6. 函数

1、   聚合函数

l         最大值 MAX（字段）

l         最小值 MIN（字段）

l         平均值 AVG （字段）

l         求和   SUM （字段）

l         数量  COUNT（*）

2、   数字函数

l       执行备注中的代码

l   ABS() ：求绝对值。

l       CEILING()：舍入到最大整数 。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。 Ceiling→天花板

l       FLOOR()：舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。 Floor→地板。

l       ROUND()：四舍五入。舍入到“离我半径最近的数” 。Round→“半径”。例：Round(3.1425,2)。

解释：3.1425是在四舍五入的值，2是精确的位数

3、   字符串函数

l LEN() ：计算字符串长度   求字符

l DATALENGTH(): 计算字节长度  求字节  一个汉字两个字节

                     例：

                     select len('123是数字') --字符

select datalength('123是数字') --字节

       注意：text类型保存的是字节数据，所以不能用len()

l LOWER():  转小写

l UPPER () ：转大写

l LTRIM()：  字符串左侧的空格去掉

l RTRIM () ：字符串右侧的空格去掉

               例：LTRIM(RTRIM('         bb        '))

l SUBSTRING(string,start_position,length)

          参数：string为主字符串，

start_position为子字符串在主字符串中的起始位置，从1开始

length为子字符串的最大长度。

         例：SELECT  SUBSTRING('abcdef111',2,3)

4、   日期函数

l         GETDATE() ：取得当前日期时间

l         DATEADD (datepart , number, date )，计算当前的时间前或后

参数：datepart 计量单位  值：year、month、day

number 增量 正数是后，负数是前

date 日期

                            例：--获得当前日期前年的时间

select dateadd(month,1,getdate())

l         DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 参数：datepart  计量单位

  startdate  起始日期

 enddate  结束日期

例：统计不同工龄的员工的个数：

select DateDiff(year,FInDate,getdate()),count(*) from T_Employee group by DateDiff(year,FInDate,getdate())

l         DATEPART (datepart,date)：返回一个日期的特定部分

参数：datepart  计量单位

         date   日期

l         统计员工的入职年份个数：

l         select DatePart(year,FInDate),count(*) from T_Employee

l         group by DatePart(year,FInDate)

5、   类型转换函数

l       CAST ( expression AS data_type)

参数：expression 要转换的值

 data_type  要转换的类型

                例：select cast(right('abc2011-02-24',10) as datetime)

l       CONVERT ( data_type, expression)

参数：expression 要转换的值

 data_type  要转换的类型

例：select convert(datetime,right('2012-11-24',10))

6、   空值处理函数

l       ISNULL(expression,value) ：

参数：如果expression值为null，刚返回value的值

7、   CASE函数

单值判断，相当于switch case

例：select fsalary, case 

       when fsalary < 4500 then '初级'

       when fsalary < 8000 then '中级'

       when fsalary > 8000 then '高级'

       end as '等级'

from t_employee

注：then 后面除了给常量之外，还可以给列

7. 数据排序

l       ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，

升序  ASC（从小到大排列）默认

降序  DESC（从大到小排列）。

l       按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序 ：SELECT * FROM  T_Employee ORDER BY FAge DESC,FSalary DESC（多个排序条件）

l       ORDER BY子句要放到WHERE子句之后

例：       SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC

8. 通配符过滤

l       通配符过滤关键字使用LIKE 。

l       单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。

            例：以任意字符开头，剩余部分为“erry”

              SELECT * FROM T_Employee WHERE FName LIKE '_erry'

l       多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。 “k%”匹配以“k”开头、任意长度的字符串

             例：检索姓名中包含字母“n”的员工信息

              SELECT * FROM T_Employee WHERE FName LIKE '%n%'

通配符	解释	示例
‘_’	一个字符	A Like 'C_'
%	任意长度的字符串	B Like 'CO_%'
[ ]	括号中所指定范围内的一个字符	C Like '9W0[1-2]'
[^]	不在括号中所指定范围内的一个字符	D Like ‘%[A-D][^1-2]'

-                                        任意一个字符

%                  任意多个字符

'[^a]%a%'  非a开头，中间含有a的任意多个字符

'[a-d]'    匹配a到d之间任意一个字符

9. 空值处理

l       数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null不一样，数据库中的null表示“不知道”，而不是表示没有

l       因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。

l       SQL中使用is null、is not null来进行空值判断：

例：

1. SELECT * FROM T_Employee WHERE FNAME is null ；FNAME为空的值
2. SELECT * FROM T_Employee WHERE FNAME is not null ；fname不是空值

1. 10.         多值匹配  使用关键字

l       要删除多条记录怎么办？

例：Delete T_Employee where FId in (21,22)

l       BETWEEN  包括该值

例：SELECT * FROM T_Employee WHERE FAGE BETWEEN 23 AND 27

等价于：SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27

  

1. 11.         数据分组

l       按照年龄进行分组统计各个年龄段的人数：

SELECT FAge,Count(*) FROM T_Employee GROUP BY Fage

l       GROUP BY子句必须放到WHERE语句的之后

l       没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)

多条件查询：多条件分组时，将条件相加，看是否相等，如果相等，就为一组

例：select fage,ftest,count(*) from t_employee group by fage,ftest

Having语句(分组后查询条件)

l       分组统计之后查询条件不能用where 而要用having语句，Having要位于Group By之后

例：--进年龄分组统计，同时ftese数量等于的

select fage,count(fage) from t_employee group by fage having count(ftest) = 2

1. 12.         限制结果集行数  top

--查询前行记录

select top 3 * from T_Employee order by fsalary desc

--分页查询

select top 3 * from t_Employee where fnumber not in (

select top 3 fnumber  from T_Employee order by fsalary desc

)order by fsalary desc

1. 13.         去掉数据重复  distinct

例：select distinct fdepartment from t_employee

--多个列时和group by 一样，按照多个列组合值判断是否重复

select distinct fdepartment,fsubcompany from t_employee

1. 14.         联合结果集 UNION 

 基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。

1. UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条

2. Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行

那么就用UNION ALL

l         Union 和Union All 的区别

Union         合并重复项，并且去掉重复项

Union All 合并重复项，不去掉重复项

问题：

l         在sql server 2008 中出现了“阻止保存要求重新创建表的更改”

解决办法：工具->选项->-Designers>取消“阻止保存要求重新创建表的更改”

l         行号和自动换行

工具—>选项—>文本编辑器—>纯文本 右边就有行号和文本编辑器