SQLSERVER数据库、表的创建及SQL语句命令
|
SQLSERVER数据库，安装、备份、还原等问题： 一、存在已安装了sql server 2000，或2005等数据库，再次安装2008，会出现的问题 1、卸载原来的sql server 2000、2005，然后再安装sqlserver 2008，否则经常sql server服务启动不了 2、sql server服务启动失败，解决方法： ???????? 进入sql serverconfigure manager，点开 Sql server 网络配置（非sql native client 配置），点sqlzhh（我sqlserver的名字）协议，将VIA协议禁用。再启动Sql Server服务，成功 如图:
二、在第一次安装SQLSERVER2008结束后，查看安装过程明细，描述中有较多项插件或程度，显示安装失败。 解决方法： 1、重新启动安装程度setup.exe，选择进行修复安装，至完成即可。 三、先创建数据库XXX，再进行还原数据库时，选择好备份文件XXX.bak，确定后进行还原，会报如下图的错误。
解决方法： 选择好备份数据库文件后，再进入“选项”中，勾选“覆盖现在数据库”即可。
四、查看数据库版本的命令：select @@version 在数据库中，点击“新建查询”，然后输入命令，执行结果如下
五、数据库定义及操作命令： 按照数据结构来组织、存储和管理数据的仓库。由表、关系以及操作对象组成，把数据存放在数据表中。 1、修改数据库密码的命令： EXECsp_password?NULL,?'你的新密码',?'sa' sp_passwordNull,'sa','sa'
2、操作SQL2008数据库的命令 备份SQL数据库： backup?database?[3D-DATA]/*原数据库*/?to??disk='D:Program?FilesMicrosoftSQL?ServerMSSQL10.MSSQLSERVERMSSQLbackup3D-DATA_backup_20131017.bak/*数据库备份文件*/' with?name?=?'3D-DATA_backup_20131017'/*数据库备份文件*/
查看备份数据库的逻辑文件： restorefilelistonly from?disk= 'd:Program?FilesMicrosoftSQL?Server MSSQL10.MSSQLSERVERMSSQLbackup3D-DATA_backup_20131017.bak'
强制还原SQL数据库： [其中，Test-3d是已存在的数据库，3D-FEP_data和3D-FEP_log为备份数据库的两个逻辑文件，] restore?database?[Test-3d] ???from?disk?=?'d:Program?FilesMicrosoft?SQLServerMSSQL10.MSSQLSERVERMSSQLBackup3D-DATA_backup_20131017.bak'? with move?'3D-FEP_data'?to?'D:ProgramFilesMicrosoft?SQLServerMSSQL10.MSSQLSERVERMSSQLDATATest-3d.mdf',move?'3D-FEP_log'?to?'D:ProgramFilesMicrosoft?SQL?ServerMSSQL10.MSSQLSERVERMSSQLDATATest-3d_log.ldf',
??stats = 10/*?表示每完成10%显示一条记录*/ ,replace/*表示强制，放置在with后，加英文逗号也可*/ ? 以下为另一种方式： -备份数据DB 到.bak文件。然后利用此bak文件恢复一个新的数据库DBTest。 USE?master;backup?database?DB?to?disk?=?'g:DBBack0930.bak'?;restore?filelistonly?form?disk?=?'g:DBBack0930.bak'?; restore?database?DBTest（此数据库，已创建）?FROM?DISK?=?'g:DBBack0930.bak'?WITH?MOVE?'DBTest'?TO?'E:Program?FilesMicrosoft?SQLServer2005DataDBTest.mdf',MOVE?'DBTest_log'?TO?'E:Program?FilesMicrosoft?SQLServer2005DataDBTest_log.ldf'?GO 六、 SQL Server数据库文件恢复技术yzhshi(yzhshi@etang.com) SQL Server数据库备份有两种方式： 1、使用命令：BACKUPDATABASE，将数据库文件备份出去； 2、直接拷贝数据库文件mdf和日志文件ldf的方式。 ???????? 下面将主要讨论一下后者的备份与恢复。本文假定您能熟练使用SQL Server Enterprise Manager (SQL Server企业管理器)和SQL ServerQuwey Analyser(SQL Server查询分析器) 正常的备份、恢复方式正常方式下，我们要备份一个数据库，首先要先将该数据库从运行的数据服务器中断开，或者停掉整个数据库服务器，然后复制文件。 卸下数据库的命令：Sp_detach_db 数据库名连接数据库的命令：Sp_attach_db或者sp_attach_single_file_db s_attach_db [@dbname =] ′dbname′,[@filename1 =] ′filename_n′ [,...16] sp_attach_single_file_db [@dbname =]′dbname′,[@physname =] ′physical_name′ 使用此方法可以正确恢复SQLSever7.0和SQL Server 2000的数据库文件，要点是备份的时候一定要将 mdf和ldf两个文件都备份下来，mdf文件是数据库数据文件，ldf是数据库日志文件。 ? 七、SQL命令创建数据库及数据表 use?master
if?db_id('Test-a01')is?not?null--判断数据库，是否已存在
drop?database?[Test-a01]--删除数据库
go
?
create?database?[Test-a01]?on??primary?
(???
????name?='Test-a01_data',--数据文件的逻辑名称
????filename?='D:Program?FilesMicrosoft?SQL?ServerMSSQL10.MSSQLSERVERMSSQLDATATest-a01_data.ndf',?--数据文件的路径和文件名
????size?=3mb?,--数据文件的初始容量
????maxsize?=10mB--数据文件的最大容量
)
log?on?
(
????name?='Test-a01_log',?--事务日志文件的逻辑名称
????filename?='D:Program?FilesMicrosoft?SQL?ServerMSSQL10.MSSQLSERVERMSSQLDATATest-a01_log.ndf',?--事务日志文件的路径和文件名
????size?=3mb?,?--事务日志文件的初始容量
????maxsize?=10mB--事务日志文件的最大容量
)
Go
use?[xxxxx]/*指定创建表的数据库*/
go
create?table?student
(
????stuID?char(5)?primary?key,--主键
????stuname?char(20)not?null,????stusex?char(2)?check(stusex?in('男','女'))?default('女'),--设定范围及默认值
????stuage?char(6),????stubirth?datecheck(stubirth?between?'1985-01-01'?and?'1995-12-31'),--设置日期范围
????stuaddress?varchar(100),????stunum?char(12),????stuemail?char(40)check(stuemail?like?'%@%')
)
go
use?[xxxxx]
go
create?table?teacher
(
????TeacherID?char(5),????Teaname?char(20)not?null,????Teasex?char(2)?check(Teasex?in('男',????Teaage?char(6),????Entrydate?date?check(Entrydate?between?'1950-01-01'?and?'2099-12-31'),????Teaphone?varchar(100),????Teanum?char(12),????Teaaddress?char(40)
)
1、删除新表： drop table table_name; ? 2、创建视图： create viewviewname as select statement; 例1： create view view_name01(stuid,stuname,stusex,stuage) as select stuid,stuage fromstudent where stuage>=25; 其中，红色部分的字段列，必须相同。 如下例2，则不用考虑（比较常用）. create view view_name02 as select stuid,stuagefrom student where stuage>=25; ? 查看视图：select * from view_name; ? 3、删除视图： drop?view?viewname; ? 4、创建索引： create?[unique]?index?idx_name?ontable_name(字段1…); create?index?idx_name?on?table_name(COLUMN_NAME); ?
drop?index?idxname;
八、常见创建数据表的限制，有以下几种： NOT NULL UNIQUE CHECK 主键(Primary Key) 外来键(Foreign Key) ? 1、NOT NULL 表示某字段的值，不允许出现空值。 例如： CREATE?TABLECustomer? ( ????SID?int?NOT?NULL,????Last_Name?varchar?(30)?NOT?NULL,? ????First_Name?varchar(30) ); 2、unique 表示某字段的值，不允许重复 例如： CREATE?TABLECustomer? ( ????SID?int?Unique,? ????First_Name?varchar(30) ); 3、check 检查某字段的值，必须符合某些条件 CREATE?TABLE?Customer? ( ????SIDint??check(sid>0?),? ????Last_Namevarchar?(30)?NOT?NULL,? ????First_Namevarchar(30) ); 4、主 键 主键 (Primary Key) 中的每一笔资料都是表格中的唯一值。 添加主键SQL命令（注意：选择作主键的列，必须有数值）： Alter?tableteacher?add?primary?key(TeacherID); Alter?table?teacher?add?primary?key(TeacherID); ? 创建数据表时，加主键的不同方式： MySQL: CREATE?TABLE?Customer? (SID?integer,? Last_Name?varchar(30),? First_Name?varchar(30),? PRIMARY?KEY?(SID)); ? Oracle: CREATE?TABLE?Customer? (SID?integer?PRIMARY?KEY,? First_Name?varchar(30)); ? SQL Server: CREATE?TABLE?Customer? (SID?integer?PRIMARY?KEY,? First_Name?varchar(30)); ? 删除主键的SQL语句： 语法：alter table 表名 drop constraint 主键约束名(非字段名称) 如： alter?table?Stu_PkFk_S?drop?constraintPK_S;
5、外键 外来键是一个(或数个)指向另外一个数据表主键的字段。外键作用是：确定资料限制输入的参考完整性(referential integrity)。换言之，只有被准许的字段值才会被存入数据库内。
创建外键的命令操作：如下， alter?table?[Store_Information]? add?foreign?key?(Reg_ID)?references?[Geography](regionID); ? alter table 需要建立外键的表 addconstraint 外键名字 foreign key references 外键表(外键字段) ; ? 以下列出几个在建置 ORDERS 表格时指定外键的方式： 外键Reg_ID，指向主键regionID。 MySQL: CREATE?TABLE?ORDERS? (Order_ID?integer,? Order_Date?date,? Customer_SID?integer,? Amount?double,? Primary?Key?(Order_ID),? Foreign?Key?(Customer_SID)?referencesCUSTOMER(SID)); ? Oracle: CREATE?TABLE?ORDERS? (Order_ID?integer?primary?key,? Customer_SID?integer?referencesCUSTOMER(SID),? Amount?double); ? SQL Server: CREATE?TABLE?ORDERS? (Order_ID?integer?primary?key,? Order_Date?datetime,? Amount?double); ? 以下的例子则是改变表格架构来指定外键。这里假设 ORDERS表格已经被建立，而外键尚未被指定： ? MySQL: ALTER?TABLE?ORDERS? ADD?FOREIGN?KEY?(customer_sid)REFERENCES?CUSTOMER(sid); ? Oracle: ALTER?TABLE?ORDERS? ADD?(CONSTRAINT?fk_orders1)?FOREIGN?KEY(customer_sid)?REFERENCES?CUSTOMER(sid); ? SQL Server: ALTER?TABLE?ORDERS? ADD?FOREIGN?KEY?(customer_sid)REFERENCES?CUSTOMER(sid); ? 删除外键的SQL命令： alter table 表名 drop constraint 外键约束名（非字段名）; 如： alter?table?Stu_PkFk_Sc?drop?constraintFK_s; ? 6、Alter Table 改变表结构的方式： 1、加一个字段 Alter?table?table_name?add?字段1?int,字段2?char(10),…;?加多个字段 Alter?table?table_name?add?字段1?int、或字段2?char(10)、…;加一个字段 ? 2、删去一个字段 Alter?table? table_namedrop? column 字段 1;-- （ SQLserver 数据库，字段前要加“ column ”） ? 3、改变字段名称 （此命令SQLserver数据库，不可用） ALTER?table?table_name ?change 字段 1? 新字段 1 ?char(50); ? 4、改变字段的类型（此命令SQLserver数据库，不可用） ALTER?table?table_name? modify ? 字段 1?char( 20 );
SQLSERVER 2008不能修改表的解决方法（增加字段、修改字段名）新装的SQL SERVER 2008，打开原来SQL SERVER 2008中建立的表，现在想增加一些字段，在保存时提示错误，无法保存。 原以为是SQL SERVER 2008与2005的不兼容，后来发现在SQL SERVER 2008中新建的表也存在无法修改表结构，也就是无法修改字段名和增加字段名。 提示是：不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的标进行了更改或者启用了“阻止保存要求重新创建表的更改”选项。 截图如下：
解决办法： 启动MSSQL SERVER 2008，选择菜单工具-〉选项-〉左侧有个设计器-〉表设计器和数据库设计器。然后去掉“ 阻止保存要求重新创建表的更改”前面的勾。重新启动MSSQL SERVER 2008即可。如下图所示：
九、SQL数据库字段类型详解
SQL语句： 一、DML―数据操纵语言SQL命令，分为：select查询、insert into插入、delete from删除、update set修改.
最复杂、存在非常多的使用方法 1、查询表中所有的数据 Select?*? from? table_name； ? 2、普通条件（where、and、or）查询 Select *或者字段1,字段2,… fromtable_name where 字段1＝值1或字段2＝值2…; 如查询一个范围的薪资 SELECT store_name FROM Store_InformationWHERE salary > 1000 OR (salary < 500 AND salary > 275); ? 3、模糊条件（like）查询 Select *或者字段1,… from table_name where字段1 like %A%;包含A的字符 Select *或者字段1,… from table_name where字段1 like A%;以A起头的字符 Select *或者字段1,… from table_name where字段1 like %A;以A结尾的字符 Select *或者字段1,… fromtable_name where字段1 like '_汉字_';一个下划线，表示一个字符，共计四个字符 Select *或者字段1,… from table_name where字段1 like '%汉字%'; %表示无限个字符 ? 4、空值条件（null）查询 Select *或者字段1,… from table_name where 字段1 is not null; ? 5、范围查询一（in） Select *或者字段1,… from table_name where 字段1 in (1,2,3,…)或者（'质量','数量',…）…;-- 字段值可为数值、或字符类型 ? 6、范围查询二（between …and…） Select *或者字段1,… fromtable_name where 字段1 between 10 and 100; Select *或者字段1,… from table_name where 字段1 between '1990-01-01' and '2012-12-31'; ? 7、排序查询（order by…asc/desc一般都是组合使用） SELECT?*?FROM?table_name??order?by?字段1?asc; SELECT?*?FROM?table_name??order?by?字段2?desc; SELECT?*?FROM?table_name??order?by?字段1,?字段2,…??desc或asc; SELECT?*?FROM?table_name?where?字段1=数值1?order?by?字段1?desc,?字段2?asc,…; ? 8、Count 统计记录数查询 SELECT ?Count（字段1，或字段2，…）[别名a…] FROM table_name; 【一般不与其它字段一起组合，否则会报错，可在后面加别名】 【加条件“is not null”,可过滤掉为空值数据】 SELECT??Count（字段1，或字段2，…）[别名a…]?FROM?table_name?where(字段1，或字段2，…)?is?not?null;? SELECT??Count（字段1，或字段2，…）,字段A?FROM?table_name?group?by?字段A; SELECT??Count（字段1，或字段2，…）,字段A?FROM?table_name?where?字段B=数值?group?by?字段A; 【与其它字段一起查询显示，要与group by组合使用，且要显示的"字段A"，一定要与group by匹配的"字段A"相同，与字段B不能相同，所以只能是一个集合函数，或者与group by配置的字段名相同】 ? ? 9、sum 求和 SELECT ??sum （字段 1 ，或字段 2 ， … ） FROM?table_name; sum匹配的字段，一定为数值类型。 SELECT??sum（字段1，或字段2，…）,字段A?FROM?table_name?group?by?字段A; SELECT??sum（字段1，或字段2，…）,字段A?FROM?table_name?where?字段B=数值?group?by?字段A; 【与其它字段一起查询显示，要与group by组合使用，且要显示的"字段A"，一定要与group by匹配的"字段A"相同，与字段B不能相同，所以只能是一个集合函数，或者与group by配置的字段名相同】 ? 10、Max 求和 SELECT ??Max （字段 1 ，或字段 2 ， … ） FROM?table_name; sum匹配的字段，一定为数值类型。 SELECT??Max（字段1，或字段2，…）,字段A?FROM?table_namegroup?by?字段A; SELECT??Max（字段1，或字段2，…）,字段A?FROM?table_namewhere?字段B=数值?group?by?字段A; 【与其它字段一起查询显示，要与group by组合使用，且要显示的"字段A"，一定要与group by匹配的"字段A"相同，与字段B不能相同，所以只能是一个集合函数，或者与groupby配置的字段名相同】 ? 11、Min 求和 SELECT ??Min （字段 1 ，或字段 2 ， … ） FROM?table_name; sum匹配的字段，一定为数值类型。 SELECT??Min（字段1，或字段2，…）,字段A?FROM?table_namegroup?by?字段A; SELECT??Min（字段1，或字段2，…）,字段A?FROM?table_namewhere?字段B=数值?group?by?字段A; 【与其它字段一起查询显示，要与group by组合使用，且要显示的"字段A"，一定要与group by匹配的"字段A"相同，与字段B不能相同，所以只能是一个集合函数，或者与groupby配置的字段名相同】 ? 12、AVG 求平均值 SELECT ?avg（字段1，或字段2，…）FROM table_name; sum匹配的字段，一定为数值类型。 SELECT?avg（字段1，或字段2，…）,字段A?FROM?table_namegroup?by?字段A; SELECT?avg（字段1，或字段2，…）,字段A?FROM?table_namewhere?字段B=数值?group?by?字段A; 【与其它字段一起查询显示，要与group by组合使用，且要显示的"字段A"，一定要与group by匹配的"字段A"相同，与字段B不能相同，所以只能是一个集合函数，或者与groupby配置的字段名相同】 ? 13、having筛选组查询 那我们如何对函数产生的值来设定条件呢？举例来说，我们可能只需要知道哪些店的营业额有超过 $1,500。在这个情况下，我们不能使用 WHERE 的指令。那要怎么办呢？很幸运地，SQL 有提供一个 HAVING 的指令，而我们就可以用这个指令来达到这个目标。 HAVING 子句通常是在一个 SQL 句子的最后。 一个含有HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。 HAVING 的语法如下： SELECT?"栏位1",?SUM("栏位2")?
FROM?"表格名"?
GROUP?BY?"栏位1"?
HAVING?(函数条件)?;
? select （字段1，或字段2，…）from table_name group by <字段1，或字段2，…> having <分组条件>; 例如：查询出本次考试男女生的总成绩大于等于250分的信息 select?sex,sum(score) ?as?' 总成绩 '?from?student?group?by?sex??having?sum(score)>=250 ; ? 12、表格连接
SELECT?A1.region_name?REGION,?SUM(A2.Sales)SALES? FROM?Geography?A1,?Store_Information?A2?--A1,A2使用到别名，更助于理解 WHERE?A1.store_name?=?A2.store_name??--关键处，存在相同的字段列 GROUP?BY?A1.region_name?--按区域名称，进行分类 结果: REGION?SALES East?????$700 West?????$2050 外部连接 Select?a2.?region_name,?sum(a1.Sales?)?from?Store_Information?a1?,?Geography?a2 Where?a1.?store_name=a2.?store_name+???--"+"表示列表中的所有资料，都查取出来 Group?by?a2.region_name; ? 13、CONCATENATE字段列表内容间的串连 MySQL: CONCAT() Oracle: CONCAT(),|| SQL Server: + CONCAT() 的语法如下： CONCAT(字符串1,字符串2,字符串3,...): 将字符串1、字符串2、字符串3，等字符串连在一起。请注意，Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字符串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字符串 SELECT CONCAT(region_name,store_name)FROM Geography WHERE store_name = 'Boston'; SQL Server使用 +，如：加个空格+' '+ SELECTregion_name?+?'?'?+?store_name?FROM?Geography? WHEREstore_name?=?'Boston'; ? 14、SUBSTRING SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的数据库中不完全一样： MySQL: SUBSTR(),SUBSTRING() Oracle: SUBSTR() SQL Server: SUBSTRING() 此函数不可用 最常用到的方式如下 (在这里我们用SUBSTR()为例)： SUBSTR(str,pos): 由<str>中，选出所有从第<pos>位置开始的字符。请注意，这个语法不适用于SQL Server上。 SUBSTR(str,pos,len): 由<str>中的第<pos>位置开始，选出接下去的<len>个字符 假设我们有以下的表格：
例1: SELECTSUBSTR(store_name,?3)? FROM?Geography WHEREstore_name?=?'Los?Angeles'; ? 结果: 's Angeles' 例2: SELECT?SUBSTR(store_name,4)? FROM?Geography? WHERE?store_name?=?'San?Diego'; 结果: 'an D' ? 15、top查询 随机取出10条数据 ? ?
1、普通常用插入 Insert into table_name (字段1,字段3,…)values (数值1,数值2,数值3,…); 2、插入子查询结果 Insert into table_name [(字段1,…)] SELECT 语句; --后面的select语句中，还可加相应的限制条件(如：where) 例如： insert?into?[XXXXX].[dbo].[Geography_001](regionID,region_name,store_name) select?[regionID],[region_name],[store_name]?from?[XXXXX].[dbo].[Geography] where?regionID>9002; ?
1、删除表中的符合某个条件的所有数据信息： Delete from table_namewhere 字段1=数值1，或字段2=数值2,…; ? 2、删除表中所有内容，表结构不删除，以下两种效果一样： Delete table_name; truncate table table_name; ? 3、完全删除表: Drop table table_name; ? ?
update table_name set 字段1=数值1 where 字段1=数值1,字段2=数值2,…; 例如： UPDATE?Store_Information?SET?Sales?=500 WHERE?store_name?=?"Los?Angeles"? AND?Date?=?"Jan-08-1999"; ? SQL查询的拓展 l?UNION 或UNION? ALL 作用是将两个 SQL 语句的结果合并起来（相当于取两个SQL 语句的并集） UNION 的语法如下： [SQL 语句 1] UNION [SQL 语句 2] 限制是两个 SQL 语句查询的字段值（可以为多个字段）内容，必须是同类型的数据。 SELECT?store_name FROM?[XXXXX].[dbo].[Geography]? union SELECT Store_Information FROM?[XXXXX].[dbo].[Store_Information]; 结果：
如果SQL语句中为union all，则全部将两张表的字段值内容，查询出且包括重复的。 ?
查询重复出现的字段值，只显示一次。 select distinct 字段名1，字段名2 from 表格 order by 字段名1 例如：
执行语句： select?distinct[store_name]?from?[XXXXX].[dbo].[Store_Information]; 结果：
作用是将字段值都存在两个 SQL 语句中时，才查询出结果。（相当于取两个SQL 语句的交集） 语法： [SQL 语句 1]?INTERSECT? [SQL 语句 2] 限制是两个 SQL语句查询的字段值（可以为多个字段）内容，必须是同类型的数据。 SELECT?store_name FROM?[XXXXX].[dbo].[Geography]? intersect?? SELECT Store_Information FROM?[XXXXX].[dbo].[Store_Information]; 结果：
只运用在两个SQL 语句上。执行第一个SQL 语句所产生的结果，如果存在第二个 SQL 语句的结果中，则会被去除，不显示最后结果中。执行第二个 SQL 语句所产生的结果，如果不存在第一个 SQL 语句所产生的结果内，则会被去除，不显示最后结果中
而我们要知道有哪几天是有店面营业额而没有网络营业额的。要达到这个目的，我们用以下的 SQL 语句： ? SELECT?Date?FROM?Store_Information MINUS SELECT?Date?FROM?Internet_Sales ? 结果: ? Date Jan-05-1999 Jan-08-1999 ? ?
在一个 SQL 语句中放入另一个 SQL 语句。在 WHERE 子句或 HAVING 子句中插入另一个 SQL 语句时，就有一个子查询 (subquery) 的架构。 子查询的作用是：1、用来连接表格。2、唯一能够连接两个表格的方式。 ? 子查询的语法如下： SELECT "栏位1" FROM "表格" WHERE "栏位2" [比较运算素] (SELECT "栏位1" FROM "表格" WHERE [条件]); 其中，[比较运算素]可以为“=,>,<,>=,<=.”，也可以为“like”，及“and”、“or”，红色部分表示内查询。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
