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,
last_name,
dept_code
--into sec04_sales_staff --创建表
from l_employees
where dept_code = 'SAL';
drop table sec04_sales_staff;
select employee_id,
first_name,
last_name,
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, 206, 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, first_name, last_name, dept_code, 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,
first_name,
last_name,
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,
first_name + ' ' + last_name as full_name,--字符串连接用+
credit_limit + 10 as new_credit_limit
from l_employees
order by employee_id;
select menu_item,
description,
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,
description,
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', 5, 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), GETDATE(), 101)
SELECT 'ANSI Date:' + CONVERT(Varchar(50), GETDATE(), 103)
SELECT 'UK/French Date:' +CONVERT (Varchar(50), GETDATE(), 103)
SELECT 'German Date:' + CONVERT(Varchar(50), GETDATE(), 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,
dept_code,
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,
a.f_num.
b.f_num,
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,
a.f_num.
b.f_num,
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,
a.f_num.
b.f_num,
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,
a.f_num.
b.f_num,
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,
a.f_num.
b.f_num,
b.color
from sec_fruits a,
left outer join sec_colors b
on a.f_num = b.f_num
union --**union用于将两个结果表连接起来
select a.fruit,
a.f_num.
b.f_num,
b.color
from sec_fruits a,
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:
终于看完SQL基础了,,,发表一下总结吧
最新推荐文章于 2024-09-20 08:42:34 发布