sql必知必会的简单总结

看了sql必知必会，简单总结一下

一基本概念

数据库database：保存有组织数据的一组文件或一个文件
数据库管理系统dbms:有mysql,sql server,access等
表Table:同一类型数据的结构化清单
模式：描述数据在表中如何存储，包含怎样的信息等内容
列：表的一个字段，表由一个和多个字段组成。列都有自己的数据类型，定义了该列可以存储哪些数据种类。
行：表中数据是按行存储的，每一行是一个记录
主键：每一行都应该有一列可以唯一的标识自己。任意两行的主键值不相同，每一行至少有一个主键。主键的值不允许修改更新。
注释语句:

--：--之后的文本就是注释，可以嵌在语句内
#:#之后的一行都是注释
/注释/：多行注释

二检索数据

检索单个列：select 关键字 from 表名；
检索多个列：select 关键字，关键字，关键字 from 表名；
检索所有列：select * from 表名；
检索不同的值：select distinct 关键字 from 表名；只返回有不同值的列
限制结果：

（1）sql server和access数据库：select top 数字关键字 from 表名；返回前数字行数据
（2）mysql和sqlite数据库：
select 关键字 from 表名 limit 数字；返回不超过数字行数据
select 关键字 from 表名 limit 数字1 offset 数字2；返回从数字2开始的后数字1行数据
select 关键字 from 表名 limit 数字2，数字1；返回从数字2开始的后数字1行数据

三排序检索数据

按单个列排序：select 关键字 from 表名 order by 列名；
按多个列排序：
（1）select 关键字，关键字，关键字 from 表名 order by 列名1，列名2；即检索的数据先按列名1排序，拥有相同列名1的值的，再按列名2的值排序
（2）select 关键字，关键字，关键字 from 表名 oeder by 2,3;这里即检索的数据先按第二个关键字的列排序，再按第三个关键字的列排序
指定排序方式：默认以升序排列，使用desc指定该列降序排序，如想多个列都进行降序，每个列名后都应加上该关键字。

四过滤数据

1、简单过滤

使用：例如：select name,price from product where price=2;即从prduct中选出price为2的数据
注意：如果有order by，where应该放在其前面
操作符：除了常用的<,>,!=等还用is null(为空值)between 数字 and 数字(在指定两个值之间)

2、高级过滤

（1）可以使and,or进行连接，但and的优先级更高，可以通过()对and,or进行优先分组，如：select name,price from product where price=2 and name<>'aa';即从prduct中选出price为2且name不为aa的数据
（2）in:可以用来指定条件范围，如select name,price from product where name in ('aa','bb');即选出name为aa或bb的数据
（3）not:否定后面的语句，如select name,price from product where not price=3;即选出price不等于3的数据

3、通配符进行过滤

通配符搜索只能用于文本字段,通配符是用来匹配值的一部分特殊字符,这种过滤需要使用like
（1）%：任何字符出现任何次数(在access中使用*)，如：select name,price from product where name like 'aa%'；即检索product中name是以aa开头的任何字符串的数据。
注意：%可以匹配0个字符，但不匹配Null。另外如abc空格是不能通过a%c匹配的，因为有空格，所以应用a%c%匹配
（2）_（在access中使用？）：与%相似，只匹配一个字符
（3）[]（只有access和sqlserver支持）：指定字符集，指定匹配位置的匹配的一个字符。如：select name,price from product where name like '[as]dd';即选出name值是以a或s开头的以dd结尾的数据。另外可以用^进行否定（access用！），如：select name,price from product where name like '[^as]dd';即选出name值不是以a或s开头的，但是以dd结尾的数据

五、计算字段

用来对检索出的数据计算，转换，格式化，虽然客户端也可以处理，但数据库处理更快

1、格式化

（1）拼接字段||（oracle和sqlite等使用）或+（sql server和access使用）：如：select name+'('+price+')' from product;输出形如： apple (3.2)
（2）mysql使用函数concat，如select concat(name,'(',price,')' )from product;
（3）别名：as
上述处理过的数据只是显示出来，不能用于客户端程序中，用as 关键字，处理的结果保存在以该关键字命名的列中，方便应用程序利用
如select name+'('+price+')' as name_price from product;

2、算术计算：

支持加减乘除，且可以用括号表示优先级
如：select name，price，num，price*num as sum from product;返回形如，

name	price	num	sum
apple	3.5	4	14

六使用函数处理字段

文本处理函数：

不同的dbms的函数不尽相同

length()返回字符串长度
rtrim()去掉字符串右边空格
ltrim()去掉字符串左边空格
lower()字符串转换为小写，access为lcase()
upper()字符串转换为大写，access为ucase()
left()返回字符串左边的字符
right()返回字符串右边的字符

日期和时间处理函数：

各dbms不同

数值处理函数：

差不多一致

七汇总数据

avg():返回某列的平均值
count()：返回某列的行数
count（*）对表的行数进行技术，不管是否是NULL,count(列名)对特定列的行数进行统计，忽略NULL
max():返回列中最大值
min()：返回列中最小值
sum()：返回指定列值的和

八分组数据

group by：例如select name,count(*) as number from product group by name;即先对数据按name分组，再计算改组有多少行。
大多数sql实现不允许group by列带有长度可变得数据类型
所有NULL被分成一组
group by出现在where之后，order by之前
having:与where相似，不过是对分组进行过滤

九子查询

1、利用子查询过滤

例如，product表有生产人员id列，name列，price列，people表有生产人员id列，姓名people_name列。查找某产品(apple)的生产人员的姓名：select people_name from people where id in(select id from product where name='apple');

2、作为计算字段使用子查询

如：select name, (select count(*) from orders where orders.id=customer.id)as orders from customers;

十联结表

用一条语句来查询多个表的数据

1、内联结，

如：select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;
其中vend_name是vendors表的列，后两个是products的列，两个表通过vend_id联系
相同的：select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;

2、高级联结

（1）使用别名：如：select vend_name,prod_name,prod_price from vendors as v,products as p where v.vend_id=p.vend_id;
（2）自联结：如：select name,price from products as p1,products as p2 where p1.price=p2.price and p2.name='apple';即找出和apple价格相同的数据，查询使用了两个相同的表
（3）外联结：联结将一个表的行与另一个表的行相关联，但有时需要包含没有关联行的那些行。左关联：left,右关联：right,全关联：full。如：elect vend_name,prod_name,prod_price from vendors left inner join products on vendors.vend_id=products.vend_id;

十一组合查询

union：在多条语句间使用union。union会自动剔除重复行，union连接的各语句必须是查询相同的列

十二插入数据

1、insert into values(各列值)。其中into可省，但是插入数据时最好给出列名。如向表product插入数据：insert into product(id,name,price) values('1','apple','3.5');
2、插入检索出的数据：insert into product(id,name,price) select id,name,price from product2;即将从product2检索出的数据插入product中
3、复制表:select * into product from product2;将product2的内容复制到product中，如果只想复制几列，给出列名即可。mysql等的语法稍有不同，create table product as select * from product2;

十三

更新数据:update product set price='4' where name='apple';即将apple的价格更新为4
删除数据:delete from product where name='apple';即删除名字为apple的这一行

十四创建和操纵表

1、创建表：

列名 数据类型 Notnull或默认的null default 值，
)
default 值（用来指定默认值，可选）,不同dbms创建表不一样```
2、删除表：drop table 表名
3、更新表：alter

## 十五视图
视图是虚拟的表，只包含动态检索数据的查询，access不支持视图
1、使用视图简化复杂联结
如；```create view 视图名 as select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;```
使用视图：``` select vend_name,prod_name from 视图名 where vend_id='aaa';```
2、重新格式化检索出的数据
如：```select name+'('+price+')' from product;```这样的数据可以保存到视图中
```create view 视图名 as select name+'('+price+')' from product;```只要输入select * from 视图名；即输出上句的效果
3、与上同理可以使用视图过滤不想要的数据，使用视图与计算字段

## 十六存储过程
存储过程好像一个函数，包含多条语句，有参数，返回值等。dbms不同创建存储过程不同。创建时一般都有create peocedure 名字，调用时一般使用到execute

## 十七控制事务处理
事务处理能确保一组sql操作要么完全被执行，要么完全不被执行，确保数据库的完整性。事务处理关键在于将sql分解为块，并明确规定数据何时回退，何时不回退。
（1）事务处理块的标识
```sql server:begin transaction ...commit transaction
mysql:start transaction
oracle:set transaction```
（2）回退语句
rollback
（3）保留点
保留点取能够唯一标识的名字，在回退时，dbms知道回退到何处。
创建保留点： ```Mysql和oracle等 ：savepoint 名字;sql server:save transaction 名字；```
回退保留点：```mysql等：rollback to 名字；sql server: rollback transaction 名字；```

## 十八
* 主键：可以在创建表时，使用primary key标识主键，也可以通过alter table 表名 add constraint primary key (列名)设置主键
* 外键：为了保证引用完整性，外键是表中的一列，其值必须是另一表的主键值，可以在创建表时，使用references 另一表名（列名）标识，也可以通过```alter table 表名 add constraint foreign key (列名) references 另一表名（列名）```
* 检查约束：用来保证某一列中的数据满足指定的条件
如：```crreate table 表名（
number integer not null check(number >4)
）```即插入的Number值必须满足大于4

## 十九数据类型

### 1、串数据类型：
* char ：1~255个字符的定长字符串，长度必须在创建时规定
* ncgar：char的特殊形式 ，支持多字节或unicode字节
* nvarchar:text的特殊形式，支持多字节或unicode字节
* text:变长文本

### 2、数值数据类型
* bit:单个二进制，0或1
* deciml:定点
* float:浮点
* int:四字节整数
* smallint:二字节整数
* tinyint:一字节整数

### 3、日期时间
* date:日期值
* time:时间值