一、最简单的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基本数据库 *** 作所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)