4>DATENAME(datepart, date) 返回日期中日期部分的字符串形式。
5>DATEPART(datepart, date) 返回日期中指定的日期部分的整数形式。
4.数学函数:
5.字符串函数:
rtrim()
ltrim()
ltrim(rtrim())
space(2)
substring(列名,开始位置,长度)
right(列名,长度)
left(列名,长度)
stuff(列名,开始位置,长度,字符串)
五.表的连接
1.内部连接:
select xsxxb.xh,xm,xscjb.fs from xsxxb inner join xscjb
on xsxxb.xh=xscjb.xh
2.多表连接:
select xsxxb.xh,xm,kmmc,fs from xsxxb inner join xscjb
on xsxxb.xh=xscjb.xh
join kmxxb
on xscjb.kmbh=kmxxb.kmbh
3.左外部连接:
select xsxxb.xh,xm,fs from xsxxb left outer join xscjb
on xsxxb.xh=xscjb.xh
4.右外部连接:
(与左外部连接相反)
5.完全外部连接:
select xsxxb.xh,xm,fs from xsxxb full join xscjb
on xsxxb.xh=xscjb.xh
6.交叉连接:
select xm,fs from xsxxb cross join xscjb
7.自连接:
select A.xh,A.fs,B.xh from xscjb A join xscjb B
on A.kmbh=B.kmbh
where A.xh>B.xh
8.联合运算符(union):
select xh,xm from xsxxb
union
select xh,xm from xsxxb2
六.数据汇总
1.排序: (Asc升,desc降)
select * from xscjb order by fs Asc
2.分组group by all 包含不符合指定的where条件的组,但将它们设为null)
select xh,sum(fs) as tol from xscjb
where xh='004'
group by all xh
3.指定分组后,组的条件(having):
select xh,sum(fs) as tol from xscjb
group by xh
having sum(fs)>80
4.compute汇总:
select xh,fs from xscjb
order by xh
compute sum(fs)
5.compute by汇总:
select xh,fs from xscjb
order by xh
compute sum(fs) by xh
compute sum(fs)
6.rollup和cube函数:
rollup() 对group by子句中的列按不同的等级进行分组.
select xh,sum(fs) as tol from xscjb
group by xh with rollup
cube() 是rollup的扩展.
七.数据的查询
1.SELECT 语句的数据检索形式
1>显示所有列和行:
SELECT * FROM authors
2>显示所有行和特定的列:
SELECT pub_name, city FROM publishers
3>显示限定范围的行:
SELECT stor_id, qty, title_id FROM sales
WHERE qty BETWEEN 20 AND 50
4>显示与值列表相匹配的行:
SELECT * FROM publishers
WHERE state IN ('CA', 'TX', 'NY')
5>根据未知值显示行:
SELECT price, title FROM titles
WHERE price IS NULL
6>显示/隐藏重复的值:
SELECT DISTINCT city FROM authors
7>显示根据多个搜索条件查询的结果:
SELECT title FROM titles
WHERE pub_id = 0736 AND type = 'business'
2.SELECT 语句中使用的关键字:
BETWEEN 测试值是否在两值之间
EXISTS 测试值是否存在
IN 测试在一定范围内的值
LIKE 测试类似这个值的值
IS NULL 测试是否为 null 值
3.查询通配符的使用:
(%,_,[],^)
1> select * from xsxxb where xm like '张%'
2> select * from xsxxb where xm like '_花%'
3> select * from xsxxb where xm like '_[花娇]%'
4> select * from xsxxb where xm like '_[^花娇]%'
4.简单子查询:
1>使用返回单个值的子查询:
select xm,xb,csrq,dh from xsxxb
where xh=(select xh from xscjb where fs=70)
5.相关子查询:
1>使用返回多行单列的子查询:
select xm,xb,csrq,dh from xsxxb
where xh in(select xh from xscjb where fs>70)
2>使用exists关键字验证存在性的子查询:
select xm,xb,csrq,dh from xsxxb
where exists (select xh from xscjb where kmbh='3' and fs>70
and xh=xsxxb.xh )
3>使用not exists关键字验证存在性的子查询:
select xm,xb,csrq,dh from xsxxb
where not exists (select xh from xscjb where kmbh='3' and fs>70
and xh=xsxxb.xh ) 作者: fangzhen 时间: 2008-12-10 15:23
八.流程控制语句
1>声明和使用变量:
declare @i int
set @i=3
select @i=(select fs from xscjb where xh='001')
2>begin...end 语句块:
begin
print'dfdfdfd'
end
3>条件语句:
if (select fs from xscjb where xh='002') >70
begin
print'dfdfedfd'
end
else if (select fs from xscjb where xh='002') <60
begin
print'888888'
end
else
begin
print'99999999'
end
4>分支语句:
select gg=case fs 'gg是别名
when 60 then 'df'
when 70 then 'xdf'
when 80 then 'yb'
when 90 then 'xgf'
else 'mf'
end
from xscjb
5>循环语句:
declare @i int
declare @sum int
set @i=0
set @sum=0
while @i<10
begin
set @sum=@sum+@i
set @i=@i+1
end
print @sum
6>标签:
select * from xsxxb
goto A
select * from kmxxb
A:
select * from xscjb
九.视图的使用
1.创建视图:
1>创建基于表中指定列的视图:
create view GetFs
as
select xh,fs from xscjb
2>创建基于表中指定行的视图:
create view GetFs2
as
select xh,fs from xscjb where fs=80
3>创建基于表中汇总信息的视图:
create view GetFs3
as
select xh,sum(fs) as tol from xscjb
group by xh
4>创建基于多个表的视图:
create view GetFs4
as
select xsxxb.xh,xm,kmxxb.kmmc,xscjb.fs from xsxxb,kmxxb,xscjb
where xsxxb.xh=xscjb.xh and xscjb.kmbh=kmxxb.kmbh
5>创建基于视图的视图:
create view GetFs5
as
select * from GetFs4
where fs>75
2.更改视图:
(把create换为alter)
3.删除视图:
1>删除视图中的数据:
delete GetFs2
2>删除视图:
drop view GetFs2
4.通过视图修改数据:
create view GetFs6
as
select xh,xm from xsxxb
1>插入数据:
insert into GetFs6 values('005','黄三')
2>更新数据:
update GetFs6 set xh='006' where xh='005'
3>删除数据
delete GetFs6 where xh='006'
十.存储过程与触发器
1.创建存储过程与执行存储过程:
1>创建一个不带参数的存储过程:
create proc Display_orders
as
select * from orders
2>创建一个带输入参数的存储过程:
create proc Display_orders
@cusid char(20)
as
select * from orders where customerid=@cusid
3>创建一个带输入，输出参数的存储过程:
create proc Display_Name
@Name char(20) output
as
select @Name=(select xm from xsxxb,kmxxb,xscjb where xsxxb.xh=xscjb.xh and
fs=(select max(fs) from xscjb where kmbh=(select kmbh from kmxxb
where kmmc=@Name))
and kmxxb.kmbh=(select kmbh from kmxxb where kmmc=@Name))
from xsxxb,kmxxb,xscjb
print @Name
2.更改存储过程:
(把create换为alter)
3.删除存储过程:
drop proc Display_Name
4.创建触发器:
1>创建INSERT 触发器:
create trigger checkFs
on xscjb
for insert
as
if(select fs from inserted)<50
begin
print'bu neng cha!'
rollback tran
end
2>创建UPDATE 触发器:
create trigger NoUPdateXm
on xsxxb
for update
as
if update(xm)
begin
print'bu neng geng xing xm!'
rollback tran
end
3>创建DELETE 触发器:
create trigger NoDelete002
on xsxxb
for delete
as
if (select xh from deleted)='002'
begin
print'bu neng shang chu xh wei 002!'
rollback tran
end
5.更改触发器:
(把create换为alter)
6.删除触发器:
drop trigger NoDelete002
7.禁用和启用触发器:
1> 禁用:
alter table xsxxb
disable trigger NoDelete002
2> 启用:
alter table xsxxb
enable trigger NoDelete002 作者: fangzhen 时间: 2008-12-10 15:24
十一.用户自定义函数
1.创建用户自定义函数:
1>创建数量型用户自定义函数：（返回一个简单的数值,如：int,char等）
create function NumAdd
(@num1 int,@num2 int)
returns int
as
begin
return(@num1+@num2)
end
调用：select dbo.NumAdd(4.6)
2>创建表值型用户自定义函数:（返回一个Table型数据）
use northwind
go
create function DisplayXsxx
(@xh char(20))
returns table
as
return(select * from xsxxb where xh=@xh)
调用：select * from DisplayXsxx('002')
2.更改用户自定义函数:
(把create换为alter)
3.删除用户自定义函数:
drop function DisplayXsxx
十二.游标
1.创建游标:
declare Fs_level cursor
static
for select xm,sum(fs) from xsxxb,xscjb where xsxxb.xh=xscjb.xh group by xm
declare
@fs int,
@Name varchar(20)
2.打开游标:
open Fs_level
3.提取游标:
fetch next from Fs_level into @Name,@fs
while(@@Fetch_status=0)
begin
if @fs<150
print'总分太低！'
else
print'高分！'
fetch next from Fs_level into @Name,@fs
end
4.关闭游标:
close Fs_level
5.销毁游标:
deallocate Fs_level
十三.数据完整性
1.缺省
1>创建缺省:
create default dd
as 'MN'
2>绑定缺省:
sp_bindefault dd,'xsxxb.xh'
3>取消缺省:
sp_unbindefault 'xsxxb.xh'
4>删除缺省：
drop default dd
2.规则
1>创建规则:
create rule rr
as @scode in('MN','ND','SD')
2>绑定规则:
sp_binderule rr, 'xsxxb.xh'
3>取消规则:
sp_unbindrule 'xsxxb.xh'
4>删除规则：
drop rule rr
3.约束
1>主键约束:
primary key
2>外键约束:
foreign key(列名) references 表名
3>唯一约束:
unique
4>检查约束:
check(xb='男' or xb='女')
十四.数据库的安全性
1.帐户
1>创建一个帐户:
sp_addlogin 'zj','0822','pubs'
2>查询帐户的相关信息:
select convert(varbinary(32),password) from syslogins where name='zj'
3>更改，删除帐户:
sp_password '0822','888','zj'
2.数据库用户
1>添加数据库用户
use northwind
go
sp_grantdbaccess zj
2>删除数据库用户
use northwind
go
sp_revokedbaccess [zj]
3.角色
1>浏览服务器角色的内容：
sp_helpsrvrole
2>角色分配给帐户：
sp_addsrvrolemember zj,'sysadmin'
4.权限
1>授予权限:
use northwind
go
grant insert,update on xsxxb to zj
2>撤消权限：
revoke create table,create view from zj
3>拒绝访问:
use northwind
go
deny select,insert,update ,delete on xsxxb to zj
十五.事务与锁
1.事务
1>一个标准事务:
begin tran
select * from xsxxb
commit tran
或
begin tran insert xscjb
insert into xscjb values('002','2',70)
commit tran
2>返回几个事务在运行:
begin tran
select * from xsxxb
select * from kmxxb
select @@trancount --执行第一次时返回值为1，每执行一次事务数量就加1。
commit tran
select @@trancount --返回值为0。
3>复杂可回滚事务:
declare @i int
set @i=0
print ltrim(str(@i))
begin tran
print ltrim(str(@i))
select @i=(select count(*) from xsxxb)
if @i>4
begin
rollback tran
return --停止过程中当前批的执行，并在下一批的开始处恢复执行。
end
else
print ltrim(str(@i))
select * from xsxxb
select @@trancount
begin tran --嵌套事务
select * from xscjb
select @@trancount
commit tran
commit tran
4>嵌套事务:
declare @i int
set @i=0
print ltrim(str(@i))
begin tran
print ltrim(str(@i))
select @i=(select count(*) from xsxxb)
if @i>4 --改为3试一试
begin
rollback tran
return --停止过程中当前批的执行，并在下一批的开始处恢复执行。
end
else
print ltrim(str(@i))
select * from xsxxb
select @@trancount
begin tran --嵌套事务
select * from xscjb
select @@trancount
commit tran
commit tran
5>与表相联系的事务:
declare @i int
set @i=0
begin tran
update xscjb set fs=85 where fs=70
set @i=2
if @i>1 --改为3试一试
begin
rollback tran
return
end
else
commit tran
go
select * from xscjb
6>设置保存点:
declare @i int
set @i=0
begin tran
update xscjb set fs=120 where fs=90
save tran s1
set @i=2
if @i>1
begin
rollback tran s1
return
end
else
commit tran
go
select * from xscjb
7>含子查询的事务:
begin tran
declare @fs int
update xscjb set fs=95 where fs=90
select @fs=(select max(fs) from xscjb)
if @fs<100 --改为90试一试
begin
rollback tran
return
end
else
commit tran
go
select * from xscjb
8>隐式事务:
[im'plisit]暗示的
set implicit_transactions on --打开
update xscjb set fs=95 where fs=90
select @@trancount
go
select * from xscjb
2.锁
事务的隔离级别：
1>读提交：
第一个窗口：
begin tran
update xscjb set fs=95 where xh='002'
第二个窗口:
set transaction isolation level read committed
go
select * from xscjb where xh='002'
2>读未提交:
第一个窗口：
begin tran
update xscjb set fs=80 where xh='002'
第二个窗口:
set transaction isolation level read uncommitted
go
select * from xscjb
3>可重复读:
第一个窗口：
set transaction isolation level repeatable read
go
begin tran
select * from xscjb
update xscjb set fs=100 where xh='002'
select * from xscjb
rollback
第二个窗口:
set transaction isolation level read committed
go
begin tran
insert into xscjb values('002','2',120)
select * from xscjb where fs=120
rollback
4>顺序读:
第一个窗口：
set transaction isolation level serializable
go
begin tran
select * from xscjb
update xscjb set fs=100 where xh='002'
select * from xscjb
第二个窗口:
set transaction isolation level read committed
go
begin tran
insert into xscjb values('002','2',120)
select * from xscjb where fs=120
`i` int(11) default NULL,
`j` int(11) NOT NULL, `k` int(11) default '-1'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4. SHOW DATABASES LIKE 'm%';
5.SHOW WARNINGS\G显示错误
复制创建表技巧
6. CREATE TABLE CityCopy1 SELECT * FROM City;复制表
7. CREATE TABLE CityCopy2 SELECT * FROM City where id=5;
按条件复制表:将city 表格的结构复制同时复制id=5的记录
8. CREATE TABLE CityCopy3 SELECT title FROM City where id=5;
title(是字段)指定city表中的title字段复制创建成CityCopy3表
9.重命名
(1) ALTER TABLE t1 RENAME TO t2;
(2)Rename tabae t1 to t2;
(2) RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; 批量命名
10.删表
(1)DROP TABLE IF EXISTS t1;或者DROP TABLE t1
(2)DROP TABLE t1, t2, t3; 批量删表
11.清空表记录
(1) DELETE FROM t;或者TRUNCATE TABLE t;
(2) DELETE FROM t WHERE id=5;指定条件删除
删除表
DELETE FROM table_name;
TRUNCATE TABLE table_name;
12.select 另类用法
(1) mysql> SELECT 1 AS One, 4*3 'Four Times Three';
+-----+------------------+
| One | Four Times Three |
+-----+------------------+
| 1 | 12 |
+-----+------------------+
(2)SELECT last_name, first_name FROM t ORDER BY 1, 2;
排序ORDER BY 1, 2升序;2,1降序
(3) mysql> SELECT description, IF('abc' LIKE pattern,'YES','NO') FROM patlist;
15. INSERT插入值的技术
(1) INSERT INTO people (name,age)VALUES('William',25),('Bart',15),('Mary',12);多重插入
(2)INSERT INTO people VALUES(25,'William');不用中间的字段名字
(3) INSERT INTO t (i) VALUES(1),(2),(3),(4),(5);
16.数据替换
(1) REPLACE INTO people (id,name,age) VALUES(12,'William',25);
(2) 多重替换
REPLACE INTO people (id,name,age)VALUES(12,'William',25),(13,'Bart',15),(14,'Mary',12);
(3)确定条件替换
REPLACE INTO people SET id = 12, name = 'William', age = 25; 作者: 虫虫 时间: 2009-3-6 08:55
