MS SQLSERVER 各种乱七八糟

select employee_id,dept_code,last_name,manager_id
from l_employees
where last_name like '%e%'--%代表任意字符串
order by dept_code,last_name;


select distinct 选取不同的值
where manager_id is null 
	or manager_id in (203,206,208);
where last_name like '%e%'    --%代表任意字符串

order by manager_id desc;    --默认asc升序排列 加desc降序排列
--order by 2;   --使用列号来排序


--SQL中 or比and高一个运算级



create table SS(
	sno  char(10)  NOT NULL       /*学号字段*/
         CONSTRAINT PK_sno PRIMARY KEY CLUSTERED/*主键约束*/
         CHECK (sno like '31300501[0-9][0-9]')/*检查约束*/,sname      char(8)     NULL,/*姓名字段*/
	sex     char(2)   NULL,/*性别字段*/
	age    int  NULL,/*年龄字段*/
	dept   varchar(20)   NULL/*系别字段*/
)
--***********************************************
drop view tmd; --预防性删除
create view tmd as
select employee_id,first_name,dept_code
--into sec04_sales_staff   --创建表
from l_employees
where dept_code = 'SAL';

drop table sec04_sales_staff;
select employee_id,dept_code
into sec04_sales_staff   --创建表
from l_employees
where dept_code = 'SAL';

create view temd as
select * 
from l_employees
where manager_id in (202,203);
drop view temd;

--****************************************************************

delete from l_employees
where LAST_NAME = 'JACKSON';

update l_employees 
set employee_id = '2001',first_name = 'LAZY',last_name = 'JACKSON'
where employee_id = null;	

insert into l_employees
(employee_id,manager_id)
values ('211','LAZY','JACKSON','XXX','209');


update sec04_sales_staff
set dept_code = 'XOX';


--drop sequence sec_seq_lunch_id;  序列

--***********************************************************************************************
--*********************************************
--创建索引
create index ix_l_employees
on l_employees (first_name,last_name)
--*********************************************

select employee_id,hire_date
from l_employees
where hire_date >= '1999/8/01'
	and hire_date <= '2010/12/31'
order by last_name,hire_date,employee_id

------------------------------------
--check约束条件
------------------------------------
alter table l_employees
drop constraint l_employees_min_employee_id;

alter table l_employees
drop constraint l_employees_min_hire_date;

alter table l_employees
add constraint l_employees_min_employee_id
check (employee_id > '200');

alter table l_employees
add constraint l_employees_min_hire_date
check (hire_date > '1900/01/01');

----------------------------------------
--unique约束条件  唯一
----------------------------------------
alter table l_employees
add constraint unique_employee_id
unique (employee_id);

alter table l_employees
drop constraint unique_employee_id;

----------------------------------------
--not null  非空
----------------------------------------
alter table l_employees
add constraint nn_l_employees_employee_id
check (employee_id is not null);

alter table l_employees
drop constraint nn_l_employees_employee_id;

----------------------------------------
--primary key主键约束
----------------------------------------
alter table l_employees
add constraint pk_l_employees
primary key (employee_id);

alter table l_employees
drop constraint pk_l_employees;

--*********************************************
--**设置RI(参照完整性)
--*********************************************

--**首先设定state_code为查找表(引用表/父表)sec808_tates的主键
alter table sec0808_states
add constraint pk_sec0808_states
primary key (state_code);

--**设定state_code为子表state_外键
alter table sec0808_clients
add constraint RI_sec0808_clients_state_code
foreign key(state_code) references sec0808_states (state_code) on delete set null;
--三种规则set null,cascade(删除外键行)
--**************************************************
--**因为RI错误的修改语句
--**************************************************
insert into sec0808_clients
values (700,'GAIL HAUSER','MA');

update sec0808_clients
set state_code = 'MA'
where client_id = 200;

update sec0808_states
set state_code = 'MA'
where state_code = 'OR';

delete from sec0808_states
where state_code = 'CA';

alter table sec0808_clients
drop constraint RI_sec0808_clients_state_code;


--*****************************************************************************
--**行函数
--*****************************************************************************
select l_foods.*,price+price_increase as new_price
into sec0902_foods
from l_foods;

select l_employees.*,first_name + ' ' + last_name as full_name,--字符串连接用+
	credit_limit + 10 as new_credit_limit
into sec0902_employees
from l_employees;	

select menu_item,description,price + price_increase as new_price
from l_foods
where menu_item < 15
order by menu_item;

select employee_id,--字符串连接用+
	credit_limit + 10 as new_credit_limit
from l_employees
order by employee_id;

select menu_item,price + price_increase as new_price
from l_foods
where (price + price_increase) > 2
order by (price + price_increase);

create view sec0905_step1_view as
select menu_item,price + price_increase as new_price
from l_foods;
--***************************
select 10.0 / 3;--自动的数据类型转换,与C相似

select sqrt(3); --测试行函数
--**************************************************************
--**常用行函数
--***************************
--**power(10,3)  指数函数
--**sqrt(9,2)  平方根
--**sign(-9)  符号函数
--**abs(-9)  绝对值
--**floor(-9.5)  小于等于此数的最大值
--**round(9.213423423,2)  四舍五入到一个精度
--***************************************************************
select n,n % 3  --求余运算
from sec0908_test_number
order by n;

--**字符转换函数
select ASCII('A');  --字符转换ASCII码
select char(97);  --ASCLL码转字符
select lower('ASDASDasdsa');  --全部转换为小写
select upper('ASDasdasdASDASDasdasd');  --全部转换为大写
select str(12341231231256.78902323,20,3);  --将数值型转化为字符型

--**去空格函数
select LTRIM('   ASDASD    ');  --去除左端空格
select RTRIM('   ASDASD    ');  --去除右端空格

--**取字串函数
select left('ABCDEFGHIJK',100);  --从字符串左端截取长度为i的字符串
select right('abcdefghijk',3);  --从右端
select substring('ABCDEFGHIJK',5,3);  --返回从左端第i号开始的长度为j的字符串

--**字符串比较函数
select charindex('CE','ABCDEFG');  --查询给定的字符串的位置，，没有返回0，'ABCDEFG'处可以是列函数
select patindex('%A_D%','ADSFASDFASSDAFDFSAASD'); --可以使用通配符的查询函数，此函数可用于CHAR、 VARCHAR 和TEXT 数据类型,下划线的位置代表不必匹配

--**字符串操作函数
select quotename('ASD','<>'); --返回被特定括号(),<>,{},[]括起来的字符串，默认为[]
select replace('ABCDEFGHI','ABC','12345');  --替换字符串
select reverse('ASDASD');  --将给定的字符串颠倒顺序
select replicate('ASD',3);  --返回重复多次的给定字符串
select space(5);  --返回一定长度的空格
select stuff('ABCDEFGHI',10,'ASD');  --用另一子串替换字符串指定位置、长度的子串。

--**数据类型转换函数
select cast(109.88 as int);
select convert(int,109.88);  
--****************************************************
-- convert 还可以用于改变日期的格式
--****************************************************
SELECT 'Default Date:' + CONVERT(Varchar(50),GETDATE(),100) 

SELECT 'US Date:' + CONVERT(Varchar(50),101) 

SELECT 'ANSI Date:' + CONVERT(Varchar(50),103) 

SELECT 'UK/French Date:' +CONVERT (Varchar(50),103) 

SELECT 'German Date:' + CONVERT(Varchar(50),104) 
--****************************************************

select len('ADSASD');  --字符串长度
select cast(1009.89 as decimal(10,3));  --decimal(i,j)用于规定显示数字的总长度和小数点后的精度
select convert(decimal(10,3),1009.89);

--**日期函数

select day(hire_date) as 日,month(hire_date) as 月,year(hire_date) as 年
from l_employees;

select getdate();  --给出当前的日期
select dateadd(year,-1,getdate());  --指定的日期部分变化相应值之后的日期
select dateadd(day,3,'1990-08-24');  --日期必须用''字符串表示
select datediff(month,'1990-12-10','1990-09-10'); --两个日期在指定的日期部分下的差值，有正负
select datename(month,'1990-08-24');  --以字符串形式返回指定部分的日期值
select datepart(day,'1990-08-24');  --以数值形式返回指定部分的日期
--**yy相当于year，mm相当于month，dd相当于day

--*******************************************
--**其他类型行函数
--*******************************************
select user;  --显示当前的用户名


--**********************************************************************************************
--**列函数(聚合函数)
--**********************************************************************************************
declare @MyNum int --用户变量必须以@开头 用set或者select语句可以给变量赋值，但是select赋值语句不能和检索操作混用
set @MyNum = 144
select sqrt(@MyNum);

select * 
from sys.messages;  --sys.messages是系统视图，出现标准错误时，错误是由数据库引擎引发的。所有的标准错误代码与消息都保存在sys.messages系统视图中

select 5/0;
select * from master.dbo.sysmessages where error = @@error ;  -- @@error是配置变量,用于记录最后一次发生的错误

SELECT alias,name,msglangid  --msglangid 被非正式的定义为Microsoft Global Language Identifier,可以检索出系统已经安装、支持的语言
FROM sys.syslanguages;

--******************************************************************************
create table sec11(
	col_1 int NULL,col_2 int NULL,)
go
insert into sec11 values(1,4)
insert into sec11 values(NULL,5)
insert into sec11 values(2,6)
insert into sec11 values(3,NULL)

--************************************************************************
--**一个简单的事务，将null设定为0再做运算
--************************************************************************
begin transaction

update sec11
set col_1 = 0
where col_1 is NULL;

update sec11
set col_2 = 0
where col_2 is NULL;

select sum(col_1)+sum(col_2) as columns_add_first,sum(col_1 + col_2) as rows_add_first
from sec11;

rollback transaction;
--************************************************************************
--汇总
--************************************************************************
select manager_id,count(employee_id) as number_of_employees,min(credit_limit) as minimunm_credit,max(credit_limit) as maximum_credit
from l_employees
where not (employee_id = 202)
--having not(employee_id = 202)  having子句与where子句类似，都是限制条件的，，但是having子句可以使用列函数而且可以对已经汇总的数据进行处理.
group by manager_id,dept_code
order by manager_id,dept_code;


--*************************************************************************
--内连接(inner join)
--*************************************************************************
select a.fruit,a.f_num.
	   b.f_num,b.color
from sec_fruits a,sec_colors b
where a.f_num = b.f_num
order by a.fruit;

select a.fruit,b.color
from sec_fruits a
	 inner join sec_colors b
	 on a.f_num = b.f_num
order by a.fruit;

--********************************************************************
--外连接(outer join)
--********************************************************************
--**左外连接
select a.fruit,b.color
from sec_fruits a
	 left outer join sec_colors b
	 on a.f_num = b.f_num
order by a.fruit;
--**右外连接
select a.fruit,b.color
from sec_fruits a
	 right outer join sec_colors b
	 on a.f_num = b.f_num
order by a.fruit;
--**全外连接
select a.fruit,b.color
from sec_fruits a
	 full outer join sec_colors b
	 on a.f_num = b.f_num
order by a.fruit;
--**全外连接的另一种实现方法
select a.fruit,left outer join sec_colors b
	 on a.f_num = b.f_num
union  --**union用于将两个结果表连接起来
select a.fruit,right outer join sec_colors b
	 on a.f_num = b.f_num

--******************
--**union all与union类似，不过union all不负责数据的自动排序和去重
--**union语句中selec语句使用新列名必须在最开头
--**union语句中使用order by必须写在最后
--**union可以实现自动的数据类型转换
--**可以通过添加null列和使用类型转换函数实现两个表的union连接

--******************
--union应用
--******************
--1.利用union自动排序和去重判断两张表是否完全相同
--2.使用加一列的直接量来确定数据的来源
--3.给异常、警告和错误的标志附加信息
--4.将数据从一个列中分到两个不同的列中
--5.将两个函数应用的数据的不同部分
--******************************************************************

--交集操作 intersect  --交集 取出两个表中相同的数据 与where a=b and c=d 等效
--差集操作 可以通过先外连接再选取null行的操作来实现

--*********************************************************************************
--**交叉操作
--*********************************************************************************
--交叉操作就是 叉积(笛卡尔积)
--结果表行数 = 初始表行数的总和
--结果表列数 = 初始表列数的乘积

select a.*,b.*
from sec_fruit a,sec_color b;
--内连接源于交叉连接   交叉连接可以列出所有可能的组合


--**************************************************
--**if-then-else
--**************************************************
--Oracle中使用decode 和 case 语句进行判断
--Access中使用iif进行判断
--SQL sever使用case

select description,case when price > 2.00 then 'EXPENSIVE ITEM'
			else ' '
	   end as mess  --case必须有end结尾
from l_foods
order by description;

--*********************************
--**子查询
--*********************************
--****in与exists的区别

--　　in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。
--　　绝对的认为exists比in效率高的说法是不准确的。这要看关联表的数据量大小.
--　　如果查询的两个表大小相当，那么用in和exists差别不大。
--　　如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!