SQLServer2008基本数据库 *** 作

SQLServer2008基本数据库 *** 作,第1张

概述一、最简单的SQL *** 作(建) 1) 首先创建数据库: CREATE DATABASE DATABASENAME(DATABASENAME_DL) ; 例子: CREATE DATABASE ZHANG_DL(创建了一个名为ZHANG_DL的数据库); 2)删除数据库: DROP DATABASE DATABASENAME 例子: DROP DATABASE BBS_DL(删除一个名为BBS_DL的

一、最简单的sql *** 作(建)

1) 首先创建数据库:

CREATE DATABASE DATABASEname(DATABASEname_DL) ;

例子:

CREATE DATABASE ZHANG_DL(创建了一个名为ZHANG_DL的数据库);

2)删除数据库:

DROP DATABASE DATABASEname

例子:

DROP DATABASE BBS_DL(删除一个名为BBS_DL的数据库);

3)创建表格:

CREATE table tablename(ColUMN1 TYPE,ColUMN2 TYPE,--------)

例子:

CREATE table T_EMPLOYEE

(

EMPLOYEE_ID INT IDENTITY(1,1) PRIMARY KEY,

EMPLOYEE_name NVARCHAR(20),

EMPLOYEE_AGE INT,

EMPLOYEE_BRITH_DAY DATE

)

4)删除表格:

DROP table tablename

例子:

DROP table T_EMPLOYEE

5)给创建的表增加列

ALTER table tablename ADD ColUMNname ColUMN_TYPE;

例子:

ALTER table T_EMPLOYEE ADD EMPLOYEE_SALARY MONEY

删除创建表的某列

6) ALTER table tablename DROP ColUMN ColUMNname

例子:

ALTER table T_EMPLOYEE DROP ColUMN EMPLOYEE_SALARY

二、表格内部的 *** 作(增、删、改、查)

1、查询类型

1)全部选择:SELECT * FROM tablename;(所有带有 * 的都可以用具体的列名来代替,如2)

EXAMPLE: SELECT * FROM T_EMPLOYEE

2)选择显示部分:SELECT table_ColUMN_name,---- FROM tablename

EXAMPLE: SELECT EMPLOYEE_ID AS 用户号,EMPLOYEE_name 用户名 FROM T_EMPLOYEE

3) 条件查询:SELECT * FROM tablename WHERE 条件

EXAMPLE: SELECT * FROM T_EMPLOYEE WHERE EMPLOYEE_SALARY>4000

4)查询中可以包含函数: SELECT COUNT(*) FROM tablename WHERE 条件

EXAMPLE: SELECT COUNT(*) FROM tablename WHERE EMPLOYEE_SALARY=2000

5) 查询嵌套: SELECT * FROM tablename1 WHERE tablename1.ColUMN_name =

(SELECT tablename2_ColUMN_name FROM tablename2 WHERE 条件)
6)分组查询:SELECT COUNT(*) FROM tablename GROUP BY 条件;

EXAMPLE: SELECT COUNT(*) FROM T_EMPLOYEE GROUP BY EMPLOYEE_SALARY

7)排序:SELECT * FROM tablename ORDER BY tableColUMN [ASC(升序,默认),DESC(降序)]

EXAMPLE: SELECT * FROM T_EMPLOYEE ORDER BY EMPLOYEE_SALARY ASC

8)组合: SELECT * FROM tablename WHERE 条件 GROUP BY 条件 ORDER BY 条件 [ASC,DESC]

EXAMPLE: SELECT COUNT(* ),EMPLOYEE_SALARY FROM T_EMPLOYE WHERE EMPLOYEE_SALARY

>2000 GROUP BY EMPLOYEE_SALARY ORDER BY COUNT(*)

(解释:首先是根据where条件将工资大于2000的选出来,然后按工资进行分组,相同的在一组,统

计各个工资的人数,然后根据人数的多少按照升序排列)

9)HAVING的用法:SELECT * FROM tablename GROUP BY 条件 HAVING 条件

2、增加数据

INSERT INTO tablename(ColUMN1,ColUMN2,----) VALUES (VALUE1,VALUE2,---)

例子:

INSERT INTO T_EMPLOYEE(EMPLOYEE_name,EMPLOYEE_AGE,EMPLOYEE_BRITH_DAY) VALUES

('ZHANG_SOR',25,GETDATE())

3、修改原有

1) 改变全部列

UPDATE tablename SET 要改属性=要改的属性值

例子:

UPDATE T_EMPLOYEE SET EMPLOYEE_DATE = GETDATE()

2)带有条件的改

UPDATE tablename SET 要改属性=要改的属性值 WHERE 条件

例子:

UPDATE T_EMPLOYEE SET EMPLOYEE_DATE = GETDATE() WHERE EMPLOYEE_ID =1

4、删除数据

DELETE FROM tablename [WHERE 条件]

例子:

DELETE FROM T_EMPLOYEE WHERE EMPLOYEE_name='ZHANG'删除表中名字叫ZHANG 的数据

DELETE FROM T_EMPLOYEE 删除表中所有的数据

三、条件

在条件是单方向的,如果是两个以上就不能使用连续的,可以使用AND,OR连接 IN,IS,liKE,NOT IN,

IS NOT,NOT liKE,BETWEEN AND

在里可以执行if条件,case when then else,while

四、创建存储过程

CREATE PROC[PROCEDURE] PRORCname(参数) AS 处理过程

例子:

CREATE PROC NET_INTSET_EMPLOYEE

(

@ EMPLOYEE_name NVARCHAR(20),

@EMPLOYEE_AGE INT,

@EMPLOYEE_BRITH_DAY DATE

)

AS

INSERT INTO T_EMPLOYEE(EMPLOYEE_name,EMPLOYEE_BRITH_DAY) VALUES (

@EMPLOYEE_name,@EMPLOYEE_AGE,@EMPLOYEE_BRITH_DAY)

EXEC NET_INSERT_EMPLOYEE 'SUN',24,GETDATE()

五、创建视图,索引

1)创建视图:

CREATE VIEW Vname AS SELECT 语句

例子:

CREATE VIEW V_SHOW_EMPLOYEE

AS

SELECT * FROM T_EMPLOYEE

使用:

SELECT * FROM V_SHOW_EMPLOYEE(跟表格的使用一样)

删除视图:

DROP VIEW VIEAname

2) CREATE INDEX INDEXname ON table(ColUMNname)

例子:

CREATE INDEX IDX_EMPLOYEE_ID ON T_EMPLOYEE(EMPLOYEE_name)

删除索引:

DRO INDEX INDEX_name

六、创建游标过程:

CREATE PROC NET_CURSOR_EMPLOYEE

(

@EMPLOYEE_ID INT

)

AS

DECLARE CURSON_EMPLOYEE CURSOR FOR SELECT * FROM T_EMPLOYEE WHERE --声明

EMPLOYEE_ID = @EMPLOYEE_ID

OPEN CURSON_EMPLOYEE;

DECLARE @ID INT--定义变量

DECLARE @EMPLOYEE_AGE INT

DECLARE @EMPLOYEE_name NVARCHAR(20)

DECLARE @EMPLOYEE_BRITH_DAY DATE

FETCH NEXT FROM CURSOR_EMPLOYEE INTO @ID,@EMPLOYEE_name,

@EMPLOYEE_BRITH_DAY--传参

WHILE(@@FETCH_STATUS=0)

BEGIN

PRINT @ID,@,@EMPLOYEE_BRITH_DAY

FETCH NEXT FROM CURSOR_EMPLOYEE INTO @ID,

@EMPLOYEE_BRITH_DAY

END

CLOSE CURSOR_EMPLOYEE--关闭

DEALLOCATE CURSOR_EMPLOYEE--释放

总结

以上是内存溢出为你收集整理的SQLServer2008基本数据库 *** 作全部内容,希望文章能够帮你解决SQLServer2008基本数据库 *** 作所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1173962.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-02
下一篇 2022-06-02

发表评论

登录后才能评论

评论列表(0条)

保存