SQL基础(2)：数据操作-增删改

以下只针对SQL Server，其它数据库类型可能不支持某些操作或操作名不同。

create database [IF NOT EXISTS] database_name：创建数据库

show databases：查看数据库

drop database：删除数据库

show tables：查看当前数据库下所有的表

use：使用指定数据库

create database db2_name;
use da2_name;
drop database db2_name;

create/drop table [IF NOT EXISTS]：创建/删除表

desc table_name：查看表结构 

有的数据库系统不支持使用 [ ] 来包围列名。

create table person(
                      [ID]   int primary key,
		              [name] varchar(20),
		              [city] varchar(30)
		           );			
drop table person;

insert into ... values()：插入一行数据，即一条记录

insert person values(1001, 'leon', 'beijing');--插入一整条记录
insert person([ID], [name]) values(1002, 'leon');--插入部分数据，其余字段值设为NULL
insert person([name]) select name from student; --使用子查询的值来插入数据

MySQL还支持一次插入多条记录：

insert into person values(32, 'le'), (90, 'dfss');

delete from ... ：删除记录

insert into author values('A10', 'jack', null, null, null, null, null);
insert into author([au_id], [au_name]) values('A10', 'jack');--其余字段被设置为null

delete from authors where [au_id]='A09';--删除id是A09的一条记录
delete from authors;--删除所有记录

alter table ... add ...：插入一列，即插入一个字段

alter table ... drop column ...：删除一列（MySQL使用alter table ... drop ...）

alter table ... alter column ...：更改列的数据类型（MySQL使用alter table ... modify ...）

alter table old_name rename to new_name; -- 修改表名（MySQL）
alter table table_name change old_field new_field int; -- 修改字段名和类型（MySQL）

alter table authors add [add_com] varchar(10);
alter table authors alter column [add_com] int;
alter table authors drop column [add_com];	

update ... set ...：修改数据库中的值

update  表名 set 字段名 = 某个值  

update authors set [au_fname] = 'sarah'; --将作者名全部改成sarah
update authors set [au_fname] = 'sarah', [au_lname] = 'buchman' where [au_id] = 'A01'; --将ID是A01的作者名字改成sarah， 姓改成buchman

truncate table ...：清空整个表

truncate table authors;	

select ... into ...：复制表中指定数据到另一个表

select * into [db1].[dbo].[authors_backup] from authors;--复制表中所有数据到另一个数据库上的表

select [au_fname], [au_lname] into authors_backup from authors 
	where [state] = 'NY';--复制表中指定数据到另一个表
	
select t1.[au_fname], t2.[royalty_share] into table_name_share
	from authors as t1 join title_authors as t2 on t1.[au_id] = t2.[au_id];--复制两个表中指定数据到另一个表
	

索引

create index ... on ...：创建索引

drop index ...：删除索引

索引唯一的作用就是加快对表的查询，索引的缺点为索引会占用额外的空间, 而且在对表中数据增、删、改时也必须维护和更新索引。

对于经常发生以下动作的列，创建索引是合适的：

查询select

条件判断where

排序order by

分组group by

联结join ... on ...

计算统计max()、min()、......

  主键列上一定要有索引，外键列上可以创建索引，主键约束和值唯一约束字段会自动设置索引，MySQL还会对外键添加索引。

对于下列情况，不应该创建索引：

text、bit、image数据类型的列上不能创建索引

在那些重复值比较多，查询较少的列上不要建立索引

索引的分类：

1，按存储结构可分为：

a、聚集索引：指物理存储顺序与索引顺序完全相同，它由上下两层组成，上层为索引页，下层为数据页，只有一种排序方式，因 此 每个表中只能创建一个聚集索引。

b、非聚集索引：指存储的数据顺序一般和表的物理数据的存储结构不同。通过下表我们可以分析出：（其中在学号上建立非聚集 索 引）

2，根基索引键值是否唯一，可以判定是否为唯一索引，唯一索引的列中不能有重复的数据。

3，基于多个字段的组合创建的索引称为组合索引。

4，根据索引键值的排序方式可以分为升序索引和降序索引：创建列的索引后当对列进行查询时默认会以升序排序方式列出列中键值，即默认为升序索引。

create index pub_id_idx on titles([pub_id]);--在表titles中pub_id列上建立索引,索引名为pub_id_idx

create unique index title_id_idx on titles([title_id]);--建立唯一索引 

create index state_city_idx on authors([state], [city]);--建立组合索引：当按照[state]+[city]进行排序,检索时索引才有效

create index price_idx on titles([price] desc);--建立降序索引

drop index titles.price_idx;--删除索引

MySQL删除索引：

drop index price_idx on titles;

以上索引内容部分转自http://www.jb51.net/article/30950.htm

视图

create view ... as ...：创建视图

drop view ...：删除视图

视图就是基于SQL语句结果集的一个虚拟表，它是表中数据的逻辑显示。同普通的表一样，可以对视图进行查询等操作， 但我们一般使用视图都是对他进行只读操作：

create view au_name_view as select [au_id], [au_fname], [au_lname] from authors;--创建视图au_name_view
select * from au_name;--列出视图au_name_view中所有数据

drop view au_name;--删除视图

MySql中还可以通过create or replace来指定如果视图已经存在的话进行替换：

create or replace view view_person as select * from person;

存储过程、存储函数、游标

存储过程是预编译并存储在数据库上的一条或多条SQL语句，其优点有：

 ①、执行速度快：存储过程只在创建的时候进行分析和编译。

 ②、减少网络开销：程序调用的时候只是使用存储过程名称和参数。

 ③、方便代码移植：存储过程在数据库上创建和修改，对应用程序的代码无影响。

 ④、安全性高：可以设置存储过程的权限，网络传输中只是存储过程的调用。可以设置存储过程使用的权限为当前调用用户或其它，默认执行存储过程使用的权限是定义存储过程的用户的权限

 存储过程中也可以调用其他存储过程，创建存储过程：

drop procedure if exists proc; -- 如果已存在存储过程proc则删除
 
/****添加存储过程SQL语句，使用delimiter声明//为分隔符来代替SQL中以;为分隔符****/
delimiter //
create procedure proc(in num1 int, in num2 int, out sum int, inout param int) -- 存储过程函数声明, in表示传入参数, out表示传出参数
begin -- 函数定义开始
 
-- 定义变量
declare temp int;
declare num int;
-- 变量赋值
set temp = 2;
select count(id) into num from newtable;
 
-- SQL语句
select * from orders where ID_O > temp;
 
-- if语句
set sum = num1 + num2 + temp;
if sum = 110 then set sum = 100;
end if;
 
-- case 语句
case num1
when 0 then
	set num1 = 1;
when 1 then
	set num1 = 0;
else
	set num1 = 1;
end case;
 
-- while语句
while sum < 0 do
set sum = sum + 1;
end while;
 
end
// 
delimiter ; -- 存储过程结束
 
 
/****执行存储过程SQL语句****/
set @num = 90;
set @param = 0;
call proc(10, @num, @s, @param);
select @num;
select @s;

  创建存储函数使用CREATE FUNCTION，存储函数必须有返回值而存储过程没有，存储函数的参数类型默认而且只能是in类型：

delimiter //
create function fun1(s char(20)) returns char(50)
begin
 return concat('hello', s, '!!');
end
//
delimiter ;

select fun1('刘德华') as '打招呼';


delimiter //
create function fun2() returns char(50)
-- begin
 return (select city from newtable where id = 100);
-- end
//
delimiter ;

select fun2();

  游标主要用在Transact_SQL 脚本、存储过程和触发器中，它相当于是指向查询结果集记录的指针，使用它可以对当前的数据进行读写：

-- 示例1
delimiter //
create procedure pro1()
begin

-- 定义两个变量用来保存游标指向的记录数据
declare _id int;
declare _city char(20);
 

-- 使用“declare 游标名 cursor for select查询” 来创建游标
declare cur_student cursor for select id, city from newtable where id = 102;

open cur_student; -- 使用游标前需要先打开
fetch cur_student into _id, _city; -- "fetch 游标名 into 变量名"：将游标指向的数据保存到指定变量
select _id, _city;
CLOSE cur_student; -- 关闭游标

end
//
delimiter ;

-- 执行存储过程
call pro1122();


-- 示例2
delimiter //
create procedure pro2()
begin

-- 定义两个变量用来保存游标指向的记录数据
declare _id int;
declare _city char(20);
declare state int default false; -- state为跳出循环的标志 


-- 使用“declare 游标名 cursor for select查询” 来创建游标
declare cur_student cursor for select id, city from newtable;
declare continue HANDLER for not found set state = true; -- 设置游标超出结果集末尾后state变量设为true

open cur_student; -- 使用游标前需要先打开

cur_loop:loop -- 开始循环
fetch cur_student into _id, _city; -- "fetch 游标名 into 变量名"：将游标指向的数据保存到指定变量，执行结束后游标自动指向下一条记录
select _id, _city;
if state then
	leave cur_loop; -- leave为跳出循环
end if; 
end loop;  -- 结束循环

CLOSE cur_student; -- 关闭游标

end
//
delimiter ;

-- 执行存储过程
call pro2();

触发器

触发器是一种特殊的存储过程，不同之处在于触发器主要是事件（对表进行插入、更新、删除）触发的时候被自动调用执行的，存储过程是通过EXECUTE语句主动调用的。触发器可以有事件之前触发、事件之后触发等类型。

创建触发器格式：create trigger + 触发器名 + 触发时机（before|after）+ 触发事件（insert|delete|update）+ on 表名 + for each row + 触发器程序体（一条SQL语句或用begin和end包含的多条SQL语句）。

使用NEW和OLD：

对于INSERT语句, 只有NEW是合法的；对于DELETE语句，只有OLD才合法；对于UPDATE语句，NEW、OLD可以同时使用。

mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突，为解决此问题可用delimiter，如：delimiter ||，可以将结束符号变成||，当触发器创建完成后，可以用delimiter ; 来将结束符号变成;。

delimiter ||

create trigger newtable_trigger after insert on newtable for each row
 begin
	  if (NEW.id >= 100) then 
			insert into test values(NEW.id, NEW.city);
	  end if;
 end||

delimiter ;

事务

事务是一种机制，它将多条SQL语句组合成一个执行单元，这些SQL语句要么全部执行，要么都不执行。可以说事务是一个原子性的完整操作。

MySql中使用begin或start transaction语句来开启事务，使用commit来显示提交本次事务，或者执行一条DCL、DDL语句来隐式提交本次事务，当事务中语句执行失败应该使用rollback回滚事务。事务具有隔离性，即未提交的事务对于其它连接不可见。当在MySQl的命令行窗口输入set autocommit=0表示自动开启了事务。还可以使用savepoint point_name来设置中间点，使用rollback to point_name来回滚到指定中间点。

MySql中事务示例：

begin; -- 开启事务

insert into newtable values(104, '广州');-- 插入两条记录
insert into newtable values(105, '深圳');

select * from newtable; -- 可以看到上面插入的两条记录

rollback; -- 回滚事务，本次事务中操作无效

select * from newtable; -- 上面插入的两条记录没有了

SQL Server 数据类型

Character 字符串：

数据类型	描述	存储
char(n)	固定长度的字符串。最多 8,000 个字符。	n
varchar(n)	可变长度的字符串。最多 8,000 个字符。
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。
text	可变长度的字符串。最多 2GB 字符数据。

Unicode 字符串：

数据类型	描述	存储
nchar(n)	固定长度的 Unicode 数据。最多 4,000 个字符。
nvarchar(n)	可变长度的 Unicode 数据。最多 4,000 个字符。
nvarchar(max)	可变长度的 Unicode 数据。最多 536,870,912 个字符。
ntext	可变长度的 Unicode 数据。最多 2GB 字符数据。

Binary 类型：

数据类型	描述	存储
bit	允许 0、1 或 NULL
binary(n)	固定长度的二进制数据。最多 8,000 字节。
varbinary(n)	可变长度的二进制数据。最多 8,000 字节。
varbinary(max)	可变长度的二进制数据。最多 2GB 字节。
image	可变长度的二进制数据。最多 2GB。

Number 类型：

数据类型	描述	存储
tinyint	允许从 0 到 255 的所有数字。	1 字节
smallint	允许从 -32,768 到 32,767 的所有数字。	2 字节
int	允许从 -2,147,483,648 到 2,147,483,647 的所有数字。	4 字节
bigint	允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。	8 字节
decimal(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
numeric(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
smallmoney	介于 -214,748.3648 和 214,748.3647 之间的货币数据。	4 字节
money	介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。	8 字节
float(n)	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。	4 或 8 字节
real	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。	4 字节

Date 类型：

数据类型	描述	存储
datetime	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。	8 bytes
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 bytes
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 bytes
date	仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。	3 bytes
time	仅存储时间。精度为 100 纳秒。	3-5 bytes
datetimeoffset	与 datetime2 相同，外加时区偏移。	8-10 bytes
timestamp	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型：

数据类型	描述
sql_variant	存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。
uniqueidentifier	存储全局标识符 (GUID)。
xml	存储 XML 格式化数据。最多 2GB。
cursor	存储对用于数据库操作的指针的引用。
table	存储结果集，供稍后处理。

MySQL数据类型：

   sql Server数据类型转自SQL 数据类型