SQLServer2008基本数据库操作

时间:2022-02-09 06:37:21

一、最简单的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_AGE,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_AGE,@EMPLOYEE_NAME,

@EMPLOYEE_BRITH_DAY--传参

WHILE(@@FETCH_STATUS=0)

BEGIN

PRINT @ID,@,@EMPLOYEE_AGE,@EMPLOYEE_NAME,@EMPLOYEE_BRITH_DAY

FETCH NEXT FROM CURSOR_EMPLOYEE INTO @ID,@EMPLOYEE_AGE,@EMPLOYEE_NAME,

@EMPLOYEE_BRITH_DAY

END

CLOSE CURSOR_EMPLOYEE--关闭

DEALLOCATE CURSOR_EMPLOYEE--释放