SQL必知

一、了解SQL

1.数据库：一个以某种有组织的方式存储的数据集合
2.主键：其值能够唯一区分表中的每一行
表的任何列都可以作为主键，只要它满足以下条件：
* 任意两行都不具有相同的主键值。
* 每一个行都必须具有一个主键值（主键值的列不允许NULL值）
3.sql:结构化查询语言

二、检索数据

1.检索不同的值：distinct
select distinct vend_id from products

2.返回前几行：
select vend_id from products LIMIT 5
返回表中key的以num1为起点后num2行:
select key FROM tablename LIMIT num1,num2
select key from 表 LIMIT num1 OFFSET num2 从num2起的num1行数据

3.注释
/* */多行注释

三、排序

1.选取列
SELECT prod_name from products order by prod_name;
order by 子句应该保证是select语句中的最后一条子句
order by支持按照相对列的位置进行排序

2.降序DESC关键字
只应用到直接位于其前面的列名

四、过滤数据

匹配性检查：where
范围值检查：where between and ;
空值检查： where IS NULL;

五、高级过滤数据

1.组合where子句，用and/or连接 AND等级高于OR
2.where vend_id IN (‘1001’,’1005’) 等价于or
3.NOT 否定其后跟的任何条件 等价于<>

六、通配符

通配符只能用于文本字段（字符串）,非文本数据类型不可使用通配符进行搜索。
1.%通配符 （匹配多个字符）表示任何字符出现任何次数(包括0个) where prod_name like ‘Jet%’

2.（_）下划线通配符 （ 匹配单个字符）

3.[ ]通配符指定一个字符集 必须匹配指定位置的一个字符，匹配字符集中的一个字符
where cust_contact like ‘[JM]%’ 以J或者M开头的联系人
可以使用前缀符 ^ 脱字号来否定，包含在【】内部。
where cust_contact LIKE ’ [^JM]%’ 不以J或者M开头
==where not cust_contact like [JM]%

七、创建字段和使用函数

1.字段连接：concat
select concat(vend_name,’ (‘,vend_country,’ )’) from vendors
order by vend_name
2.去掉空格trim()
3.别名：AS
4.文本处理函数
upper:大写，lower()小写；left：返回字符串左边的字符；trim()去除空格；length:返回字符串长度
soundex()返回发音差不多的
select cust_name,cust_contact from customers where soundex(cust_name)=soundex(‘Mause House’)
5.日期处理函数year(date)=2012
6.数值处理函数 abs绝对值，exp指数值，sqrt平方根

聚集函数(avg,count,min,max,sum)
1.avg:均值函数，只作用于单列；
2.count:确定表中行的数目count(*)/count(列名)

聚集不同值(all/distinct)
对所有的行执行运算，指定ALL或者不指定参数
只包含不同的值，指定distinct参数
select avg(distinct prod_price)as avg_price from products where

八、分组数据

定义：将数据分为多个逻辑组，对每个组进行聚集运算。
group by 子句必须出现在where子句之后，order by子句之前
where过滤行，having过滤分组
select vend_id,count(*) as count from products
where prod_price>=4
group by vend_id

九、子查询

利用子查询进行过滤
select cust_id,cust_name,cust_contact from customers where cust_id in
(select cust_id from orders where order_num in
ww(select order_num from oderItems where prod_id =‘01’)

等价于内连接查询(给表取别名)
select cust_id,cust_name from customers as C,orders as O,orderItems as OI where
C.cust_id=O.cust_id
and OI.order_num=O.order_num
and prod_id=‘01’

作为计算字段使用子查询
select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as count from customers
order by cust_name;

十、连接表和组合查询

等值联结
select vend_name,prod_name,prod_price from vendors,products
涉及到两个表
where vendors.vend_id=products.vend_id;#给出匹配条件的列

等价于内连接（INNER JOIN ON）：
select vend_name,prod_name,prod_price from vendors INNER JOIN products
ON venders.vend_id=products.vend_id;

没有where则返回笛卡尔积（叉联结）

自连接（相同表取两个别名）
select c1.cust_id,c1.cust_name,c1.cust_contact from customers as c1,customers as c2
where c1.cust_name=c2.cust_name and c2.cust_contact=‘jajjs’

自然连接
对一个表使用select(*)，对其他表的列使用明确的子集
select c.*,O.order_date, from…

外连接（包含在相关表中没有关联的行 left/right/full outer join）

组合查询：
union连接select语句
注意点：select列必须相同；默认返回不重复行（若不去重可以选择union all）;
使用union查询时，只能使用一条order by子句，它只能位于最后一条select 语句之后

十一、数据插入更新与删除

插入：
插入有几种方式：
插入完整的行：insert into 表名（列名） values( )
插入行的一部分： insert语句中省略NULL列
插入某些查询结果：insert into 表名（列名） select 列名 from 表2；
复制表 create table 新表 as select * from 旧表

更新：
update 表名
set 列名=新值，列名=新值，列名=新值（要删除某列的值，set 列名=NULL）
where 过滤条件

删除：
update:删除整列
delete from 表名 where:删除整行

十二、创建表和操作表

create table 表名（
列名 数据类型（mysql中 varchar 替换为text） null值 default指定默认值
）

更新表：alter 表名 add/drop 列名

删除表 drop table

视图：虚拟的表，只包含使用动态检索数据的查询（不包含任何列或者数据，只包含select查询）
视图的优点：
简化复杂的sql操作，在编写查询后，可以方便的重用，而不必知道基本查询细节；
使用表的一部分而不是整个表；
保护数据，可以授予用户访问表的特定部分的权限，而不是整个表的访问权限；
更改数据格式和表示

创建/删除视图 create view 视图名 AS select语句 /drop view

十三、存储过程、事务处理、游标
存储过程
存储过程定义：为以后使用而保存的一条或多条sql语句.可将其视为批文件
优点：简单：把处理封装在一个易用的单元中，可以简化复杂的操作，

存储过程
安全：简化对变动的管理。
高性能：DBMS处理命令所需的工作量少，提高了性能。
执行存储过程：EXECUTE 存储过程名（列参数）
创建存储过程：create procedure 存储过程名 as
declare 变量名 integer
select;
return;

事物处理：
定义：是一种机制，用来管理必须成批执行的sql操作，保证数据库不包含不完整的操作结果。利用事物处理，可以保证一组操作不会中途停止，要么完全执行，要么完全不执行，如果发生错误，则回退，将数据库恢复到某个已知且安全的状态。
术语：
事务:一组sql语句；
回退：撤销指定sql语句的过程；（insert,update,delete语句，不能回退select语句，不能回退create/drop操作）
提交：将未存储的sql语句写入数据库表
保留点：指事务处理中设置的临时占位符，可以对它发布回退。

事务的开始和结束：start transaction
事务的撤销：delete from orders;rollback;(撤销删除操作)
事务的提交：conmmit transaction
使用保留点：savepoint delete1
rollback to delete1

游标
定义：存储在DBMS服务器山的数据库查询，不是一条select语句，而是被该语句检索出来的结果集。存储游标后，可以根据需要滚动或浏览其中的数据。
1.声明/创建游标(declare)：
declare 游标名 CURSOR
FOR
SELECT语句
2.使用游标（open/fetch）：
打开游标：open cursor 游标名 (执行查询，存储检索出的数据以供浏览和滚动)
访问数据：fetch next from 游标名 into 将游标数据存到变量

3.关闭游标（close）

十四、高级SQL特性

约束：主键、外键、唯一约束、检查约束
定义主键（primary key）
create语句中：primary key指定
alter语句中
alter table 表名
add CONSTRAUNT PRIMARY JEY(列名)

外键(foreign key)
外键是表中一列 ，其值必须在另一个表的主键中
create语句中指定：references关键字；
alter语句中：
alter table 表名
add constraint foreign key(cust_id) references customers(cust_id)

唯一约束
unique与primary key的区别
unique以有null值，可修改或者更新，唯一约束不能用来定义外键

检查约束（check）
（用来保证一列或一组列中的数据满足一组指定的条件）
add constraint check(gender like ‘[MF]’) 检查genter列只能是M或者F

索引
create index 索引名 on 表名（列名）
触发器trigger