mysql的详细使用

 平日里用DBMS用多了，操作都没使用Sql语句了。谁知一场笔试居然考这个，哎，重新再总结下。
先创建数据库


create database mical 
on 
primary (name=miacal_data1, 
filename='E:\SQL Server 200\MSSQL$MICAL_BO\SQL server 2000个人版运行\MSSQL\Data\mical_data1.mdf', 
size=2MB, 
maxsize=10, 
filegrowth=1), 
(name=mical_data2, 
filename='E:\SQL Server 2000\MSSQL$MICAL_BO\SQL server 2000个人版运行\MSSQL\Data\mical_data2.mdf', 
size=2MB, 
maxsize=10, 
filegrowth=1) 
log on 
(name=mical_log1, 
filename='E:\SQL Server 2000\MSSQL$MICAL_BO\SQL server 2000个人版运行\MSSQL\Data\mical_log1.ldf' ,
size=2MB, 
maxsize=8, 
filegrowth=1), 
(name=mical_log2, 
filename='E:\SQL Server 2000\MSSQL$MICAL_BO\SQL server 2000个人版运行\MSSQL\ Data\mical_log2.ldf' ,
size=2MB, 
maxsize=8, 
filegrowth=1) 
go


 


 


修改数据库的名字  exec sp_renamedb  'mical','ss'


     1、先从建表开始吧：


use ss
create table lxp_table
(
coll1  char(50) not null,
coll2  int,
coll3  int identity(1, 1)  not null    自动增长1
primary key (coll3))                     /*建立主键*/


 


create table lxp_b
(
b1 varchar not null,
b2 varchar not null,
b3 int identity(1,1) not null,
primary key(b3)
)


2、修改表的名字
EXEC   sp_rename   'lxp_table',   'lxp_a' 


 


3、修改列名


Exec sp_rename 'lxp_a.[coll1]','a1'
exec sp_rename 'lxp_a.[coll2]','a2'
exec sp_rename 'lxp_a.[coll3]','a3'


 


4、添加新列


alter table lxp_a
add a_3 varchar


exec sp_rename 'lxp_a.[a_3]','a4'


 


 


5、修改列的类型


alter table lxp_a
alter column a4 char(50)  修改类型时只能向能转换成的数据类型修改（修改类型时系统会自动将此列数据转换若无法转换则无法修改）


 


6、创建表时相应的添加外键
create table a_b
(
a_id int not null 
constraint aa foreign key(a_id) references lxp_a(a3),   创建表时相应的添加外键
b_id int not null)


drop table a_b


 


7、在已经创建好的表中添加外键
alter table a_b
add constraint bb foreign key (b_id) references lxp_b(b3)


 


8、在已经创建好的表中删除外键


alter table a_b
drop bb


 


9、查询出谁连接着数据库
select * from master..sysprocesses where hostname<>''
exec sp_who


 


10、查询指定数据库的相关信息


select * from sysobjects where type = 'U'; 
select name from sysobjects where type = 'F'; 
select name from sysobjects where type = 'P'; 


由于系统表sysobjects保存的都是数据库对象,其中type表示各种对象的类型，具体包括: 
U = 用户表 
S = 系统表 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
TF = 表函数 
TR = 触发器 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程及相关的对象信息。 


PS：打开数据库 
use DNN_LH_493


 


11、查询出所有用户数据库


exec sp_databases


 


12、查询出指定数据库下的所有表


use ss
exec sp_tables




回复 (0) | 67 次阅读 


编辑 | 删除 命令备份SQL 
无敌静 发布于： 2010-11-28 16:57


这种方式感觉实现起来也不错，我是使用这种方式来完成数据库备份还原的功能的。
需要指出下面这些存储过程是在网上搜索到的。谢谢代码的提供者。在此转载


利用T-SQL语句,实现数据库的备份与还原的功能


体现了SQL Server中的四个知识点：


1． 获取SQL Server服务器上的默认目录


2． 备份SQL语句的使用


3． 恢复SQL语句的使用，同时考虑了强制恢复时关闭其他用户进程的处理


4． 作业创建SQL语句的使用






/*1.--得到数据库的文件目录


@dbname 指定要取得目录的数据库名
如果指定的数据不存在,返回安装SQL时设置的默认数据目录
如果指定NULL,则返回默认的SQL备份目录名
*/


/*--调用示例
select 数据库文件目录=dbo.f_getdbpath('tempdb')
,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')
,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null)
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdbpath]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdbpath]
GO


create function f_getdbpath(@dbname sysname)
returns nvarchar(260)
as
begin
declare @re nvarchar(260)
if @dbname is null or db_id(@dbname) is null
select @re=rtrim(reverse(filename)) from master..sysdatabases where name='master'
else
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname


if @dbname is null
set @re=reverse(substring(@re,charindex('\',@re)+5,260))+'BACKUP'
else
set @re=reverse(substring(@re,charindex('\',@re),260))
return(@re)
end
go




/*2.--备份数据库


*/


/*--调用示例


--备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_db.bak'


--差异备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'


--备份当前数据库日志
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'


--*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_backupdb]
GO


create proc p_backupdb
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
as
declare @sql varchar(8000)
if isnull(@dbname,'')='' set @dbname=db_name()
if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,'')='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'
set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname)
,'\DATE\',convert(varchar,getdate(),112))
,'\TIME\',replace(convert(varchar,getdate(),108),':',''))
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname
+' to disk='''+@bkpath+@bkfname
+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
+case @appendfile when 1 then 'NOINIT' else 'INIT' end
print @sql
exec(@sql)
go






/*3.--恢复数据库


*/


/*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db'


--差异备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_df.bak',@dbname='db',@retype='DF'


--日志备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'


--*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GO


create proc p_RestoreDb
@bkfile nvarchar(1000), --定义要恢复的备份文件名
@dbname sysname='', --定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar(10)='DB', --恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复
@filenumber int=1, --恢复的文件号
@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000)


--得到恢复后的数据库名
if isnull(@dbname,'')=''
select @sql=reverse(@bkfile)
,@sql=case when charindex('.',@sql)=0 then @sql
else substring(@sql,charindex('.',@sql)+1,1000) end
,@sql=case when charindex('\',@sql)=0 then @sql
else left(@sql,charindex('\',@sql)-1) end
,@dbname=reverse(@sql)


--得到恢复后的数据库存放目录
if isnull(@dbpath,'')='' set @dbpath=dbo.f_getdbpath('')


--生成数据库恢复语句
set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname
+' from disk='''+@bkfile+''''
+' with file='+cast(@filenumber as varchar)
+case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end
+case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end
print @sql
--添加移动逻辑文件的处理
if @retype='DB' or @retype='DBNOR'
begin
--从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int


--创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--从备份文件中获取信息
insert into #tb exec('restore filelistonly from disk='''+@bkfile+'''')
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)
+case @tp when 'D' then '.mdf''' else '.ldf''' end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
end


--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end


--恢复数据库
exec(@sql)


go


/*4.--创建作业


*/


/*--调用示例


--每月执行的作业
exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'


--每周执行的作业
exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'


--每日执行的作业
exec p_createjob @jobname='a',@sql='select * from syscolumns'


--每日执行的作业,每天隔4小时重复的作业
exec p_createjob @jobname='b',@sql='select * from syscolumns',@fsinterval=4


--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_createjob]
GO


create proc p_createjob
@jobname varchar(100), --作业名称
@sql varchar(8000), --要执行的命令
@dbname sysname='', --默认为当前的数据库名
@freqtype varchar(6)='day', --时间周期,month 月,week 周,day 日
@fsinterval int=1, --相对于每日的重复次数
@time int=170000 --开始执行时间,对于重复执行的作业,将从0点到23:59分
as
if isnull(@dbname,'')='' set @dbname=db_name()


--创建作业
exec msdb..sp_add_job @job_name=@jobname


--创建作业步骤
exec msdb..sp_add_jobstep @job_name=@jobname,
@step_name = '数据处理',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔


--创建调度
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when 'day' then 4
when 'week' then 8
when 'month' then 16 end
,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval<>1 set @time=0
set @ffactor=case @freqtype when 'day' then 0 else 1 end


EXEC msdb..sp_add_jobschedule @job_name=@jobname,
@name = '时间安排',
@freq_type=@ftype , --每天,8 每周,16 每月
@freq_interval=1, --重复执行次数
@freq_subday_type=@fstype, --是否重复执行
@freq_subday_interval=@fsinterval, --重复周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time --下午17:00:00分执行


go


/*--应用案例--备份方案:
完整备份（每个星期天一次）+差异备份（每天备份一次）+日志备份（每2小时备份一次）


调用上面的存储过程来实现
--*/


declare @sql varchar(8000)
--完整备份（每个星期天一次）
set @sql='exec p_backupdb @dbname=''要备份的数据库名'''
exec p_createjob @jobname='每周备份',@sql,@freqtype='week'


--差异备份（每天备份一次）
set @sql='exec p_backupdb @dbname=''要备份的数据库名'',@bktype='DF''
exec p_createjob @jobname='每天差异备份',@sql,@freqtype='day'


--日志备份（每2小时备份一次）
set @sql='exec p_backupdb @dbname=''要备份的数据库名'',@bktype='LOG''
exec p_createjob @jobname='每2小时日志备份',@sql,@freqtype='day',@fsinterval=2


/*--应用案例2


生产数据核心库：PRODUCE


备份方案如下：
1.设置三个作业,分别对PRODUCE库进行每日备份,每周备份,每月备份
2.新建三个新库,分别命名为:每日备份,每周备份,每月备份
3.建立三个作业，分别把三个备份库还原到以上的三个新库。


目的:当用户在produce库中有任何的数据丢失时,均可以从上面的三个备份库中导入相应的TABLE数据。
--*/


declare @sql varchar(8000)


--1.建立每月备份和生成月备份数据库的作业,每月每1天下午16:40分进行:
set @sql='
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=''PRODUCE_''+convert(varchar(10),getdate(),112)+''_m.bak''
set @path=dbo.f_getdbpath(null)+@fname


--备份
exec p_backupdb @dbname=''PRODUCE'',@bkfname=@fname


--根据备份生成每月新库
exec p_RestoreDb @bkfile=@path,@dbname=''PRODUCE_月''


--为周数据库恢复准备基础数据库
exec p_RestoreDb @bkfile=@path,@dbname=''PRODUCE_周'',@retype=''DBNOR''


--为日数据库恢复准备基础数据库
exec p_RestoreDb @bkfile=@path,@dbname=''PRODUCE_日'',@retype=''DBNOR''
'
exec p_createjob @jobname='每月备份',@sql,@freqtype='month',@time=164000


--2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行:
set @sql='
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=''PRODUCE_''+convert(varchar(10),getdate(),112)+''_w.bak''
set @path=dbo.f_getdbpath(null)+@fname


--差异备份
exec p_backupdb @dbname=''PRODUCE'',@bkfname=@fname,@bktype=''DF''


--差异恢复周数据库
exec p_backupdb @bkfile=@path,@dbname=''PRODUCE_周'',@retype=''DF''
'
exec p_createjob @jobname='每周差异备份',@sql,@freqtype='week',@time=170000


--3.建立每日日志备份和生成日备份数据库的作业,每周日下午17:15分进行:
set @sql='
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=''PRODUCE_''+convert(varchar(10),getdate(),112)+''_l.bak''
set @path=dbo.f_getdbpath(null)+@fname


--日志备份
exec p_backupdb @dbname=''PRODUCE'',@bkfname=@fname,@bktype=''LOG''


--日志恢复日数据库
exec p_backupdb @bkfile=@path,@dbname=''PRODUCE_日'',@retype=''LOG''
'
exec p_createjob @jobname='每周差异备份',@sql,@freqtype='day',@time=171500 




回复 (0) | 65 次阅读 


编辑 | 删除 mysql的乱码问题解决 
无敌静 发布于： 2010-11-28 14:56


使用MySQL保存中文数据时，经常会遇到乱码问题。产生乱码的原因很多，在笔者以为对MySQL的字符集处理已经很有了解的时候，使用C#编程时，竟再一次遇到了乱码。联想到前几天一位同事在使用JDBC访问MySQL时遇到的中文乱码问题，决定对MySQL中文乱码问题的产生场景进行一次总结。
 
一、错误读出
现象：一个已经存在数据的MySQL数据库，该数据库的数据用系统中其它软件、网页查看均正常，使用MySQLcc之类的客户端查看也正常，可是在新写的网页中总是显示乱码。
 
分析：其它系统都可以正常查看数据，说明数据本身是没有问题的。在网页中显示乱码，一定是网页的编码字符集和获取到的数据的编码字符集不一至。比如数据库的字符集是UTF8的，而网页的字符集是gb2312的，那么网页就会把UTF8编码的字体串当作gb2312的来处理，结果产生乱码。
 
解决办法：在连接数据库时，设定连接字符集，使连接字符集和当前网页或客户端程序使用的字符集一致。可以使用MySQL的Set Names指令设定连接字符集。假设网页的字体集为gb2312。在连接MySQL后，在连接上执行如下SQL语句：
Set Names ‘gb2312’
在以后所有这个连接上的查询，MySQL都会自动把数据库中的数据转换成gb2312编码格式传过来。
mysqli_query($db,"SET NAMES 'gb2312'")
 
二、错误写入
现象：一个网页或程序向一个MySQL数据库中写数据，写完后，这个网页或程序自己可以正常读取数据，而从其它客户端或网页中读取数据都是乱码。
 
分析：其它的正常的客户端出现乱码，说明数据库中的字符编码不对。写数据的那个网页能正常读取，是因为写和读都用了错误的编码格式，将错就错，反而能读出正确的数据了。比如数据库中设定的字符集为UTF8，而网页使用gb2312编码执行了插入数据的SQL，那么MySQL就会把这些gb2312的编码当成是UTF8的编码写进数据库。当其它客户端访问数据时，会按系统的设定，以UTF8格式读取数据，而数据其实是使用gb2312编码的，结果就出现了乱码。只有写数据的那个网页会把这些数据当成gb2312的，也只有那个网页能正常显示数据。
 
解决办法：同第一条，即：使用Set Names指令设定连接字符集。
在设定了连接字符体的连接上执行数据操作，所有的数据都将被MySQL自动、正确地转换为数据库中设定的编码格式保存。
 
通过以上两点，我们可以看到，只要在连接MySQL时，正确地设定了字符集，无论数据库本身是使用什么格式编码的，都能得到正确的结果。也许有人会以为写数据时设定的字符集必需和读数据时一致，事实上完全没有必要。程序所要做的只是告诉MySQL，目前操作MySQL使用的是什么字符集即可。因为MySQL会自动完成如下的转换工作：
写数据库时用的字符集-->存诸数据的字符集-->读取数据的字符集。
 
笔者以为MySQL对多语言字符集的处理是非常优秀的，并且每次建立到MySQL的连接都会立刻使用Set Names设定字符集，然而最近还是出现了一回乱码，如下面所述。
 
三、无知的程序包
现象：使用C#编程，使用MySQL提供的连接程序库包访问数据库，使用MySqlConnection类连接数据库，连接之后立刻调用Set Names设定连接字符串，然后使用MySqlCommand类执行SQL，并使用MySqlDataReader读取数查询结果。然而，当我调用MySqlDataReader的成员方法GetString获取数据的时候，发现得到的全是乱码。百思不得其解。
 
分析：经仔细检查，确信问题没有出在MySQL连接上面，这时我想到了C#中对string类型的处理。在C#中字符串和C/C++中有很大不同。在C/C++中一个字符就是一个字节，而在C#中，按不同的编码格式，一个字符也可以是多个字节的。比如”啊”就是一个字符，如果一个字符串s=”啊”; 那么s的Length属性为1，而不是C/C++中的2。我想MySQL程序包也许并不知道连接上传过来的字符是什么编码的，它因为无知，所以只是按单字节字符把这些数据组织成一个string，这个生成的string就是我得到的乱码。事实上也的确是这样。
 
解决办法：把这些数据重新组织起来，然后使用正确的编码方法重新生成string。C#中System.Text包内的Encoding类提供了字符集的编/解码方法。
1）首先还是设定连接字符集，以确认收到的字符的编码方式。
2）把GetString得到的字符串转换到byte数组中。
3）使用Systec.Text.Encoding包中相应字符集的解码方法GetString得到新的字符串。
为了通用性，我们使用System.Text.Encoding的默认字符集。连接数据库时，设置数据库连接字符集使用的SQL指令strSetCharset为如下值：
string strSetCharset = “Set Names ” + System. Text. Encoding .Default. HeaderName;
在获取数据时，使用下面的函数得到真正的字符串：
private string DBStringToNormal(string dbStr)
        {
            byte[] str = new byte[dbStr.Length];
            for (int i = 0; i < dbStr.Length; ++i)
                str[i] = (byte)(dbStr[i]);
     return System.Text.Encoding.Default.GetString(str, 0, dbStr.Length);
 }
问题解决。在使用JDBC访问MySQL时，也会有这样的问题，可以用类似的方法解决。
 
 
作者：苏林






回复 (0) | 28 次阅读 


编辑 | 删除 Mysql学习基础 
无敌静 发布于： 2010-11-25 16:41
本文讲述MySql5.x中视图的使用，包括创建视图、修改视图和删除视图的相关知识，包括命令的语法、使用举例以及注意事项。


一．             视图概述


       视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。


对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。


二．             创建视图——CREATE VIEW


1.       语法


CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW [db_name.]view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION] 
通过该语句可以创建视图，若给定了[OR REPLACE]，则表示当已具有同名的视图时，将覆盖原视图。select_statement是一个查询语句，这个查询语句可从表或其它的视图中查询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。


表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。


2.       使用举例


Eg. 本例创建一个产品表（product）和一个购买记录表（purchase），再通过视图purchase_detail查询出购买的详细信息。


CREATE TABLE product


(


        product_id INT NOT NULL,


    name VARCHAR(50) NOT NULL,


    price DOUBLE NOT NULL


);


INSERT INTO product VALUES(1, 'apple ', 5.5);


CREATE TABLE purchase


(


        id INT NOT NULL,


    product_id INT NOT NULL,


    qty INT NOT NULL DEFAULT 0,


    gen_time DATETIME NOT NULL


);


INSERT INTO purchase VALUES(1, 1, 10, NOW());


CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;


创建成功后，输入：SELECT * FROM purchase_detail;


运行效果如下：


+-------+-------+-----+-------------+


| name | price | qty | total_value |


+-------+-------+-----+-------------+


| apple |   5.5 | 10 |          55 |


+-------+-------+-----+-------------+


1 row in set (0.01 sec)


3.       注意事项


创建视图存在如下注意事项：


（1）       运行创建视图的语句需要用户具有创建视图（CRATE VIEW）的权限，若加了[OR REPLACE]时，还需要用户具有删除视图（DROP VIEW）的权限；


（2）       SELECT语句不能包含FROM子句中的子查询；


（3）       SELECT语句不能引用系统或用户变量；


（4）       SELECT语句不能引用预处理语句参数；


（5）       在存储子程序内，定义不能引用子程序参数或局部变量；


（6）       在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句；


（7）       在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图；


（8）       在视图定义中命名的表必须已存在；


（9）       不能将触发程序与视图关联在一起；


（10）   在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。


三．             修改视图——ALTER VIEW


1.       语法


ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION] 
该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。


2.       使用举例


Eg. 将上一小节中中创建的视purchase_detail进行修改，去掉qty列，语句如下：


ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;


此时通过语句：select * from purchase_detail;对视图进行查询时，结果如下：


+-------+-------+-------------+


| name | price | total_value |


+-------+-------+-------------+


| apple |   5.5 |          55 |


+-------+-------+-------------+


3.       注意事项


修改视图的注意事项除了第一条外跟创建视图的注意事项是一样的。第（1）条应改为：


该语句需要具有针对视图的CREATE VIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。


四．             删除视图——DROP VIEW


1.       语法


DROP VIEW [IF EXISTS]    view_name [, view_name] ...    [RESTRICT | CASCADE] 
该语句用户删除视图，可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。若没有该子句，当指定的视图不存在时，将发生错误。


2.       使用举例


Eg1. 删除在前面的小节中创建的视图purchase_detail：DROP VIEW purchase_detail;


Eg2. 删除一个未知的视图：DROP VIEW IF EXISTS test_view;


Eg3. 删除多个视图：DROP VIEW IF EXISTS test_view1, test_view2;


3.       注意事项


必须对要删除的一个或多个视图拥有DROP VIEW的权限。




回复 (0) | 11 次阅读 


编辑 | 删除 Mysql常用函数 
无敌静 发布于： 2010-11-25 16:36
本文讲述MySql5.x中最常用的一些函数，包括控制流程函数、字符串函数、数值函数、日期和时间函数，对不常用的函数略去不讲，以便开发人员查阅。


一．             控制流程函数


1．CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END


该语句用于在不同情况时，执行不同的语句，实例如下：


Eg1. SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;


输出：true


Eg2. SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;


       输出：NULL


2．IF(expr1,expr2,expr3) 


       如果expr1返回true，则该表达式返回expr2，否则返回expr3。


Eg1. SELECT IF(1<2,'yes ','no');


       输出：'yes '


Eg2. SELECT IF(STRCMP('test','test1'),'no','yes');


       上例中STRCMP(expr1, expr2)这个函数在expr1小于expr2时，返回-1，相等时返回0，其余的情况返回1，在上例中返回-1，因此上例的输出为：'yes'


3．IFNULL(expr1,expr2) 


       该函数在expr1为NULL时，返回expr2，否则，返回expr1。


Eg1. SELECT IFNULL(1,0);


       输出：1


Eg2. SELECT IFNULL(NULL,10);


       输出：10


4．NULLIF(expr1,expr2) 


       如果expr1=expr2，则返回NULL，否则，返回expr1。


Eg1. SELECT NULLIF(1,1);


       输出：NULL


Eg2. SELECT NULLIF(3,4);


       输出：3


二．             字符串函数


1.       ASCII(str)


返回值为字符串str 的最左字符的数值。假如str为空字符串，则返回值为 0 。假如str 为NULL，则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。


Eg1. SELECT ASCII('2');


       输出：50


Eg2. SELECT ASCII('de');


       输出：100


2.       CONCAT(str1,str2,...) 


返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。


Eg1. SELECT CONCAT('Hello', ',', '阿蜜果');


       输出：'Hello, 阿蜜果'


Eg2. SELECT CONCAT('Hello', NULL, '阿蜜果');


       输出：NULL


3.       CONCAT_WS(separator,str1,str2,...)


CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。   第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的NULL值。


Eg1. SELECT CONCAT_WS(',', 'Hello', '阿蜜果');


       输出：'Hello, 阿蜜果'


Eg. SELECT CONCAT_WS(',', 'Hello', NULL, '阿蜜果');


       输出：'Hello, 阿蜜果'


4.       INSERT(str,pos,len,newstr)


返回字符串 str, 其子字符串起始于 pos位置和长期被字符串 newstr取代的len字符。 如果pos超过字符串长度，则返回值为原始字符串。 假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。


Eg1. SELECT INSERT('hello,Amigo', 7, 5, 'Locus');


       输出：hello,Locus


5.       INTER(str, substr)


返回字符串 str中子字符串的第一个出现位置。


Eg1. SELECT INSTR('hello,Amigo', 'Amigo');


       输出：7


6.       LENGTH(str)


返回值为字符串str的长度，单位为字节。一个多字节字符算作多字节。


Eg1. SELECT LENGTH('Amigo');


       输出：5


7.       LOWER(str)


返回字符串 str以及所有根据最新的字符集映射表变为小写字母的字符。


Eg1. SELECT LOWER('AMIGO');


       UPPER(str)函数的用法与此类似，在此不再赘述。不过它是将字符串转换为大写的。


       输出：amigo


8.       LPAD(str, len, padstr) 


返回字符串 str, 其左边由字符串padstr 填补到len字符长度。假如str 的长度大于len, 则返回值被缩短至len字符。


Eg1. SELECT LPAD('hi',4,'!!');


       输出：'!!hi'                       


Eg2. SELECT LPAD('hi',1,'!!');


       输出：'h'


       RPAD(…)函数用法与此类似，在此不再赘述。


9.       REPEAT(str,count)


返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count。 若 count <= 0,则返回一个空字符串。若str或count 为 NULL，则返回NULL。


Eg1. SELECT REPEAT('Amigo', 2);


       输出：'AmigoAmigo'


10.   SUBSTRING(str,pos), SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 


       不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。


Eg1. SELECT SUBSTRING('hello,Amigo',6);        


       输出：Amigo


Eg2. SELECT SUBSTRING('hello,Amigo', 6, 4);


       输出：Amig


三．             数值函数


1.       ABS(X)


返回X的绝对值。


Eg1. SELECT ABS(-5);


       输出：5


2.       FLOOR(X)


返回不大于X的最大整数值 。


Eg1. SELECT FLOOR(2.56);


       输出：2


Eg2. SELECT FLOOR(-2.56)


       输出：-3


3.       MOD(N,M) , N % M N MOD M


模操作。返回N 被 M除后的余数。


Eg1. SELECT MOD(237, 10);


       输出：7


Eg2. SELECT 237 % 10;


       输出：7


Eg3. SELECT 237 MOD 10;


       输出：7


4.       POW(X, Y)，POWER(X, Y)


返回X的Y乘方的结果值。


Eg1. SELECT POW(2, 3); 


       输出：8


Eg2. SELECT POWER(2, 4)


       输出：16


5.       RAND()，RAND(N)


返回一个随机浮点值 v，范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N，则它被用作种子值，用来产生重复序列。


Eg1.  SELECT RAND(20);


       输出：0.15888261251047


Eg2. SELECT RAND();


       输出：0.9233482386203


若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：


SELECT FLOOR(7 + (RAND() * 6));


6.       ROUND(X)，ROUND(X,D)


返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位，可将 D 设为负值。


Eg1. SELECT ROUND(-1.23);


       输出：-1


Eg2. SELECT ROUND(2.56);


       输出：3


7.       SQRT(X)


      返回非负数X的二次方根，如X为NULL时，返回NULL。


Eg1. SELECT SQRT(4);


      输出：2


Eg2. SELECT SQRT(-4);


      输出：NULL


四．             日期和时间函数


1. ADDDATE(date,INTERVAL expr type)，ADDDATE(expr,days) 


       当被第二个参数的INTERVAL格式激活后， ADDDATE()就是DATE_ADD()的同义词。相关函数SUBDATE()则是DATE_SUB()的同义词。


Eg1. SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);


       输出：'1998-02-02'


Eg2. SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);


       输出：'1998-02-02'


2. CURDATE() 


       将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。


Eg1. SELECT CURDATE();


       输出：'2008-01-01'


Eg2. SELECT CURDATE() + 0;


       输出：20080101


3. CURTIME() 


       将当前时间以'HH:MM:SS'或 HHMMSS 的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。 


Eg1. SELECT CURTIME();


       输出：'15:15:26'


4. DATE(expr)


       提取日期或时间日期表达式expr中的日期部分。


Eg1. SELECT DATE('2007-12-31 23:59:59');


       输出：'2007-12-31'


5. DATEDIFF(expr, expr2)


       DATEDIFF() 返回起始时间 expr和结束时间expr2之间的天数。Expr和expr2为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。


Eg1. SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');


       输出：1


Eg2. SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');


       输出：-31


6. DAYOFMONTH(date)


       返回date 对应的该月日期，范围是从 1到31。


Eg1. SELECT DAYOFMONTH('2008-01-05');


       输出：5


7. MONTH (time)


       返回date对应的月份，范围时从 1 到 12。


Eg1. SELECT MONTH('2007-01-05');


输出：1


8. HOUR(time)


       返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23 。


Eg1. SELECT HOUR('11:47:1');


9. MINUTE(time)


       返回 time 对应的分钟数,范围是从 0 到 59。


Eg1. SELECT MINUTE('2007-01-05 11:51:23);


       输出：51


10. SECOND(time)


       返回time对应的秒数, 范围是从 0到59。


Eg1. SELECT SECOND('11:56:43);


       输出：43


11. LAST_DAY(date)


       获取一个日期或日期时间值，返回该月最后一天对应的值。若参数无效，则返回NULL。


Eg1. SELECT LAST_DAY('2008-02-01');


       输出：29


12. NOW()


       返回当前日期和时间值，其格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS ， 具体格式取决于该函数是否用在字符串中或数字语境中。


Eg1. SELECT NOW();


       输出：'2007-01-05 11:54:30'


13. SUBDATE(date,INTERVAL expr type) ，SUBDATE(expr,days) 


       当被第二个参数的 INTERVAL型式调用时, SUBDATE()和DATE_SUB()的意义相同。对于有关INTERVAL参数的信息， 见有关 DATE_ADD()的讨论。


Eg1. SELECT SUBDATE('2008-01-05', INTERVAL 31 DAY);


       输出：2007-12-05


14. SYSDATE()


       返回当前日期和时间值，格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS， 具体格式根据函数是否用在字符串或数字语境而定。


在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同。


Eg1. SELECT SYSDATE();