-- 表注释
use mysql;
show tables;
create database cpxl
use cpxl
create table product
{
productnumber char(10) not null primary key,
prname char(10) null,
prprice int(10) null,
inventory char(10) null
};
create table retailer
{
clientnumber char(10) not null primary key,
clientname char(10) null,
region char(10) null,
principal char(10) null,
phonenumber char(10) null
};
create table productsale
{
saledate char(10) not null primary key,
productnumber char(10) null,
clientnumber char(10) null,
prnumber char(10) null,
saleroom char(10) null
};
describ product;
insert into product 2000200');
insert into provalues('001','空调','3000200');
insert into product values('0203','冰箱','duct values('0301','彩电','1000200');
insert into product values('0421','微波炉','2500200');
select * from product;
update product set prprice=prprice+1;
delete from product
where prprice<50;
alter table mytable01 add studentnumber char(6) null first;
create table mytable01_copy1 like mytable01;#复制表结构
create table mytable01_copy2 as (select * from mytable01);#同时复制表结构和内容
insert into mytable01 values('','','') on duplicate key updata prname='';#主键冲突更新
replace into mytable01 values('','','');#先判断后替换，数据量大时效率较低
DepartmentID DepartmentName Note PhoneNumber
EmployeeID
not between
一、基础
1、说明：创建数据库
CREATE DATABASE database name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
数据库备份与恢复
备份:(导出)
use yggl;
select * from employees
into outfile 'G:/data/salary.txt' (mysql中目录向左?)
fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
恢复:(导入,因为是同一个文件所以导入导出的分隔符什么的都要一样)
LOAD DATA INFILE ''
REPLACE TABLE employees
fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
用客户端工具备份数据库mysqldump:
导出的数据可以包含表结构:
mysqldump -h(如果是本地服务器可省略) localhost -u root -pwzl123 bdname tablename1 tablename2 > filename.sql
如果没有表名, 则备份整个数据库
使用客户端程序mysqldump的”--tab=”选项，将数据库YGGL中的所有表的表结构和表内容分开备份
。使用mysql程序恢复表salary的结构，使用mysqlimport恢复表的内容。
在bin窗口中输入
Mysqldump -u root -pwzl123456 yggl --tab=G:/data
Mysqldump -uroot -pwzl123456 --tab=G:/data/mysqldata yggl
Mysql -u root -pwzl123456 yggl<G:/data/mysqldata/salary.sql
Mysqlimport -u root -pwzl123456 yggl G:/data/mysqldata/salary.txt
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
MySQL索引的查看创建和删除
1．索引作用
在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别含有1000行数据组成，指为1～1000的数值，查找对应值相等行的查询如下所示。
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行，每行包含3个相等的值。在无索引的情况下处理此查询，必须寻找3个表所有的组合，以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000（十亿），显然查询将会非常慢。
如果对每个表进行索引，就能极大地加速查询进程。利用索引的查询处理如下。
（1）从表t1中选择第一行，查看此行所包含的数据。
（2）使用表t2上的索引，直接定位t2中与t1的值匹配的行。类似，利用表t3上的索引，直接定位t3中与来自t1的值匹配的行。
（3）扫描表t1的下一行并重复前面的过程，直到遍历t1中所有的行。
在此情形下，仍然对表t1执行了一个完全扫描，但能够在表t2和t3上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。
利用索引，MySQL加速了WHERE子句满足条件行的搜索，而在多表连接查询时，在执行连接时加快了与其他表中的行匹配的速度。
2. 创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1．ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
2．CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
3．索引类型
在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。
ALTER TABLE students ADD PRIMARY KEY (sid)
4. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
5．查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
在复合索引中排序
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
6．什么情况下使用索引
表的主关键字
自动建立唯一索引
如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）
表的字段唯一约束
ORACLE利用索引来保证数据的完整性
如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）
直接条件查询的字段
在SQL中用于条件约束的字段
如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）
select * from zl_yhjbqk where qc_bh=’<????甼曀???>7001’
查询中与其它表关联的字段
字段常常建立了外键关系
如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）
select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引，其中包括qc_bh和cb_sx字段）
查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
什么情况下应不建或少建索引
表记录太少
如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：
select * from zl_sybm where sydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）
经常插入、删除、修改的表
对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。
数据重复且分布平均的表字段
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
经常和主字段一块查询但主字段索引值比较多的表字段
如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。
对千万级MySQL数据库建立索引的事项及提高性能的手段
一、注意事项：
首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。
其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。
二、性能调整方面：
首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上（不考虑阵列的情况）。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。
其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。
再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。
9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。
9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。
最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。
MySql在建立索引优化时需要注意的问题
设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：
1，创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加
索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下
降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2，复合索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,
salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3，索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4，使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5，排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6，like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7，不要在列上进行运算
select * from users where
YEAR(adddate)
8，不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id
视图相关操作:
SHOW TABLES
select * FROM DS_VIEW
select * FROM Employees_view
CREATE VIEW Employees_view（EmployeeID，Name，realIncome,salary）
AS SELECT Employees.EmployeeID,Name,Income-OutCome FROM Employees,salary
WHERE Employees.EmployeeID=salary.EmployeeID
select * FROM Employees_View
CREATE VIEW Employees_View ( EmployeeID,Name,DepartmentName,RealInCome )
AS SELECT Departments.DepartmentID,name,DepartmentName,InCome-OutCome as RealInCome FROM Employees,Departments,Salary
where Departments.DepartmentID =Employees.DepartmentID and Employees.EmployeeID =Salary.EmployeeID
select DepartmentName from DS_view where DepartmentID='3'
select * from Employees_view where Name='王林'
insert into DS_VIEW values('6','广告部','广告业务')
update DS_VIEW set DepartmentName='生产车间' where DepartmentID='5'
update Employees_view set Name='王浩' where EmployeeID='000001'
delete from DS_VIEW where DepartmentID='1'
drop VIEW DS_VIEW
MySQL索引的创建、删除和查看
此文转自http://blogold.chinaunix.net/u3/93470/showart_2001536.html
1．索引作用
在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别含有1000行数据组成，指为1～1000的数值，查找对应值相等行的查询如下所示。
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行，每行包含3个相等的值。在无索引的情况下处理此查询，必须寻找3个表所有的组合，以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000（十亿），显然查询将会非常慢。
如果对每个表进行索引，就能极大地加速查询进程。利用索引的查询处理如下。
（1）从表t1中选择第一行，查看此行所包含的数据。
（2）使用表t2上的索引，直接定位t2中与t1的值匹配的行。类似，利用表t3上的索引，直接定位t3中与来自t1的值匹配的行。
（3）扫描表t1的下一行并重复前面的过程，直到遍历t1中所有的行。
在此情形下，仍然对表t1执行了一个完全扫描，但能够在表t2和t3上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。
利用索引，MySQL加速了WHERE子句满足条件行的搜索，而在多表连接查询时，在执行连接时加快了与其他表中的行匹配的速度。
2. 创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1．ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
2．CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
3．索引类型
在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。
ALTER TABLE students ADD PRIMARY KEY (sid)
4. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
5．查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
9、说明：创建视图：create view viewname as select statement
create OR REPLACE view DS_VIEW
as (select * from departments);
create or replace
view EmployeeID_view(EmployeeID, Name, RealIncome)
as
select Employees.EmployeeID, Name, InCome-OutCome
from Employees, Salary
where Employees.EmployeeID=Salary.EmployeeID;
查询视图:
create or replace
view EmployeeID_Salary_departments_view(EmployeeNM, Name, departmentname,RealIncome)
as
select Employees.EmployeeID, DepartmentName,DepartmentName, InCome-OutCome
from employees, Salary,departments
where employees.EmployeeID=Salary.EmployeeID and Departments.departmentID=Employees.DepartmentID;
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count(field1) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
单表查询
嵌套查询
集合查询
连接查询:
等值连接:
select * from salary,employees,departments
where salary.编号=employees.编号
and employees.部门号=departments.部门号;
sql 内连接、外连接、自然连接等各种连接
1、内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和
自然联接：
select *
from emp natural join dept;
自连接：select a.number,a.calssnumber,b.number,a.grade
from table as a
join table as b on
。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
3、交叉联接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。
例子：
-------------------------------------------------
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
1） 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2）左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3） 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4） 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
--------------------------------------------------------------------------------------------
一、交叉连接（CROSS JOIN）
交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。
例如：下面的语句1和语句2的结果是相同的。
语句1：隐式的交叉连接，没有CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;
语句2：显式的交叉连接，使用CROSS JOIN。
SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;
语句1和语句2的结果是相同的，查询结果如下：
二、内连接（INNER JOIN）
内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。
例如：下面的语句3和语句4的结果是相同的。
语句3：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C,ORDERS O
WHERE C.ID=O.CUSTOMER_ID;
语句4：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;
语句3和语句4的查询结果：
三、外连接（OUTER JOIN）：外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。
下面举例说明：
语句5：左外连接（LEFT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
语句6：右外连接（RIGHT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
注意：WHERE条件放在ON后面查询的结果是不一样的。例如：
语句7：WHERE条件独立。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';
语句8：将语句7中的WHERE条件放到ON后面。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE_ORDER001';
从语句7和语句8查询的结果来看，显然是不相同的，语句8显示的结果是难以理解的。因此，推荐在写连接查询的时候，ON后面只跟连接条件，而对中间表限制的条件都写到WHERE子句中。
语句9：全外连接（FULL OUTER JOIN）。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
注意：MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面SQL在Oracle下执行的结果：
语句10：左外和右外的合集，实际上查询结果和语句9是相同的。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
UNION
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
语句9和语句10的查询结果是相同的，如下：
四、联合连接（UNION JOIN）：这是一种很少见的连接方式。Oracle、MySQL均不支持，其作用是：找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。也可以利用数据库的集合操作来实现此功能。
语句11：联合查询（UNION JOIN）例句，还没有找到能执行的SQL环境。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
语句12：语句11在DB2下的等价实现。还不知道DB2是否支持语句11呢！
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
EXCEPT
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
语句13：语句11在Oracle下的等价实现。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
MINUS
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
查询结果如下：
五、自然连接（NATURAL INNER JOIN）：说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL。下面给出几个例子。
语句14：
SELECT *
FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;
语句15：
SELECT *
FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;
语句16：
SELECT *
FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;
语句17：
SELECT *
FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;
六、SQL查询的基本原理：两种情况介绍。
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。
七、ON后面的条件（ON条件）和WHERE条件的区别：
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。
八、总结
连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：
1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、 求差操作的时候用联合查询。
多个表查询的时候，这些不同的连接类型可以写到一块。例如：
SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;
上面这个SQL查询是多表连接的一个示范。
12、说明：使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
12、分组:Group by:
一张表，一旦分组 完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；
13、对数据库进行操作：
分离数据库： sp_detach_db;附加数据库：sp_attach_db 后接表明，附加需要完整的路径名
14.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1（仅用于SQlServer）
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
子查询的作用：
1、方便理解。
2、实现更复杂的查询。
3、提高查询效率。
扩展知识：
1、当直接从SQL的数据库中查询（列出）某些数据很困难或办不到时，可以通过从“查询结果集”中再次提取数据集来实现复合查询。这个“查询结果集”就被叫做子查询。
可以避免连接操作
2、例，当表1与表2中以ID1关联，表2与表3以ID2关联，要以表1为基准复合查询三表中的数据时，就需要先查出表2与表3的子查询集，然后再与表1进行多表联查。
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现：
关于数据库分页：
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作
3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段
alter table tablename
--添加一个自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户
21、说明：列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')
这句看不懂:和select 姓名 ,
case
when 性别='1' then '男'
when 性别='0' then '女'
else AS SEX
from employees;不一样好像
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
错误！未找到目录项。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
用户权限与维护
授予与回收所有权限
grant all
on
to
REVOKE ALL
ON *
FROM user1@localhost
查看所有mysql用户:
select host,user from mysql.user;
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'
存储过程 PROCEDURE
创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作
delimiter $$
create PROCEDURE A()
begin
update Employees set DepartmentID=
(select DepartmentID from departments where departmentname='经理办公室')
where workyear>6 ;
end $$
delimiter ;
CALL A();
创建一个存储过程,使用游标计算本科及以上学历的员工在总员工数中所占的比例
DELIMITER $$
create PROCEDURE a(out tongji float)
begin
declare zongshu int;
declare number int;
declare found1 boolean;
declare xueli char(4);
declare cur_1 cursor for select Education from employees;
declare continue handler for not found set found1=false ;
set found1=true;
set number=0;
open cur_1;
fetch cur_1 into xueli;
while found1 do
if xueli='本科' or xueli='硕士' then
set number=number+1;
end if;
fetch cur_1 into xueli;
end while;
select count(*) into zongshu from employees;
set tongji=number/zongshu;
SELECT tongji;
END $$
DELIMITER ;
调用:CALL a(@tongji);
SHOW PROCEDUREs;
mysql自定义函数 function
总结存储函数只有输入参数（不用加in关键字），没有输出参数，并且返回值只能有一个，
不能返回结果集，调用时直接使用即可，不需加call,
4. 创建函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回字符串‘NO’。
DELIMITER $$
set global log_bin_trust_function_creators=TRUE;
CREATE FUNCTION jeduc( id VARCHAR(19))
RETURNS VARCHAR(20)
begin
declare bmh VARCHAR (20);
set bmh=(select departmentname from departments d, employees e
where e.employeeID=id limit 1);
if bmh='研发部' then RETURN(select education from employees where employeeID=id);
ELSE RETURN 'no';
END if;
END $$
DELIMITER ;
SELECT jeduc('1')
5. 创建一个函数，将工作时间满4年的员工收入增加500元。
DELIMITER $$
CREATE FUNCTION Upinc()
RETURNS DOUBLE
begin
UPDATE salary set InCome = InCome+500 where employeeID in(SELECT employeeID from employees where WorkYear>4);
return 0.0;
END $$
DELIMITE ;
SELECT Upinc();
SELECT * FROM salary
触发器 trigger
创建update触发器，当Departments表中部门号发生变化时，
Employees表中员工所属的部门号也将改变，并写出激活该触发器的语句。
delimiter $$
create trigger u_ate before update
on employees for each row
begin
if new.workyear-old.workyear=1 then
UPDATE salary
set income=workyear+500
where employeeID=old.employeeID;
end if;
end $$
delimiter ;
update employees
set workyear=workyear+3
where employeeID='1';
select * from employees;
创建update触发器，当Salary表中的InCome值增加500时，OutCome值则增加50
delimiter $$
create trigger u_date before update
on salary for each row
begin
if new.income-old.income=500 then
UPDATE salary
set outcome=outcome+50
where employeeID=old.employeeID;
end if;
end $$
delimiter ;
update salary
set InCome=InCome+1000
where EmployeeID='1';
select *from salary;
事件 event
8创建一个2018年11月25日上午11点执行的事件
create event zd on schedule at '2018-11-25 11:00:00'
do select * from employees;
9创建一个从下个月20日开始到2018年5月20日结束,每个月执行一次的事件
delimiter$$
CREATE EVENT every-MONTH
on schedule every 1 month
STARTS date_add(curdate()-day(curdate())+20,interval 1 month)
ends '2018-5-20'
DO
BEGIN
SELECT * from employees;
END $$
delimiter ;
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
案例：
有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end
数据开发-经典
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同
3.取回表中字段:
declare @list varchar(1000),
@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
案例
例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句， 找出表的第31到第40个记录。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。
select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。
解决方案
1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题
2，在那个子查询中也加条件：select top 30 recid from A where recid>-1
例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'
print @s exec sp_executesql @s
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
两种方式的效果相同
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
15：不同服务器数据库之间的数据操作
--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表
SQL Server基本函数
SQL Server基本函数
1.字符串函数 长度与分析用
1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度
3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类
5,Sp_addtype自定義數據類型
例如：EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。
SET NOCOUNT
为 OFF 时，返回计数
常识
在SQL查询中：from后最多可以跟多少张表或视图：256在SQL语句中出现 Order by,查询时，先排序，后取在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。
SQLServer2000
同步复制技术实现步骤
一、 预备工作
1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户--管理工具--计算机管理--用户和组--右键用户--新建用户--建立一个隶属于administrator组的登陆windows的用户（SynUser）2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
我的电脑--D:\ 新建一个目录,名为: PUB
--右键这个新建的目录--属性--共享--选择"共享该文件夹"--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
--确定3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
开始--程序--管理工具--服务
--右键SQLSERVERAGENT--属性--登陆--选择"此账户"--输入或者选择第一步中创建的windows登录用户名（SynUser）--"密码"中输入该用户的密码4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
企业管理器
--右键SQL实例--属性--安全性--身份验证--选择"SQL Server 和 Windows"--确定5.在发布服务器和订阅服务器上互相注册
企业管理器
--右键SQL Server组--新建SQL Server注册...--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加--下一步--连接使用,选择第二个"SQL Server身份验证"--下一步--输入用户名和密码（SynUser）--下一步--选择SQL Server组,也可以创建一个新组--下一步--完成6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到） (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
开始--程序--Microsoft SQL Server--客户端网络实用工具
--别名--添加--网络库选择"tcp/ip"--服务器别名输入SQL服务器名--连接参数--服务器名称中输入SQL服务器ip地址--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
二、 正式配置
1、配置发布服务器
打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:
(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）(3) [下一步] 设置快照文件夹
采用默认\\servername\Pub
(4) [下一步] 自定义配置
可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置
否,使用下列默认设置（推荐）
(5) [下一步] 设置分发数据库名称和位置 采用默认值(6) [下一步] 启用发布服务器 选择作为发布的服务器(7) [下一步] 选择需要发布的数据库和发布类型(8) [下一步] 选择注册订阅服务器(9) [下一步] 完成配置2、创建出版物
发布服务器B、C、D上
(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令(2)选择要创建出版物的数据库，然后单击[创建发布](3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。
但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表
(6)选择发布名称和描述(7)自定义发布属性 向导提供的选择:
是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性
否 根据指定方式创建发布 （建议采用自定义的方式）
(8)[下一步] 选择筛选发布的方式(9)[下一步] 可以选择是否允许匿名订阅1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器
方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
如果仍然需要匿名订阅则用以下解决办法
[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示(10)[下一步] 设置快照 代理程序调度(11)[下一步] 完成配置
当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库
有数据
srv1.库名..author有字段:id,name,phone, srv2.库名..author有字段:id,name,telphone,adress
要求：
srv1.库名..author增加记录则srv1.库名..author记录增加srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新
--*/
--大致的处理步骤--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip' exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'
go
--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动
。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
go
--然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器
--管理--SQL Server代理--右键作业--新建作业--"常规"项中输入作业名称--"步骤"项--新建--"步骤名"中输入步骤名--"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库--"命令"中输入要执行的语句: exec p_process --确定--"调度"项--新建调度--"名称"中输入调度名称--"调度类型"中选择你的作业执行安排--如果选择"反复出现" --点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行
设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程
create proc p_process
as
--更新修改过的数据
update b set name=i.name,telphone=i.telphone
from srv2.库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)
--插入新增的数据insert srv2.库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.库名.dbo.author where id=i.id)
--删除已经删除的数据(如果需要的话)
delete b
from srv2.库名.dbo.author b
where not exists(
select * from author where id=b.id)
go
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
