SQL数据库基本操作

1，----修改不同的行的相同列的信息

UPDATE 学生表

    SET 总分数 = CASE 学号

        WHEN 201601 THEN 800

        WHEN 201602 THEN 801

        WHEN 201603 THEN 802

    END

WHERE 学号 IN (201601,201602,201603)

 2.----创建学生表

CREATE TABLE [学生表] (
学号 int NOT NULL PRIMARY KEY,
性别 VARCHAR (2),
姓名 VARCHAR (20),
专业 VARCHAR (20),
入学日期 smalldatetime,
总分数 int,
备注 TEXT
);

 3.--向学生表中插入数据
INSERT INTO [学生表] 
(学号,性别,姓名,专业,入学日期,总分数,备注)
VALUES（值）
(201601,'男','黄一','信息管理','2016-1-1',300,'差'),
(201602,'女','黄二','软件管理','2016-1-2',301,'差'),
(201603,'男','黄三','软件管理','2016-1-3',302,'差'),
(201604,'女','黄四','软件管理','2016-1-4',303,'差'),
(201605,'男','黄五','美术设计','2016-1-5',400,'良好'),
(201606,'女','黄六','美术设计','2016-1-6',401,'良好'),
(201607,'男','李一','美术设计','2016-1-7',402,'良好'),
(201608,'女','李二','计算机应用','2016-1-8',403,'良好'),
(201609,'男','李三','计算机应用','2016-1-9',404,'良好'),
(201610,'女','李四','计算机应用','2016-1-10',450,'优秀'),
(201611,'男','李五','计算机网络','2016-1-11',451,'优秀'),
        (201612,'女','李六','计算机网络','2016-1-12',452,'优秀');

4. ----删除表中某一行数据
DELETE FROM [学生表] where t.姓名=‘xx'

 5.---修改数据                                          
UPDATE 学生表 SET 备注 = '' WHERE（条件）姓名 = '周二' 

6. ---修改数据                                          
UPDATE 学生表SET 备注 = ''WHERE 姓名 in  ('周二' , '黄一'， ‘XX’) ;

查询数据(SELECT)

查询是数据库的核心操作。SQL仅提供了唯一的语句SELECT，其使用方式灵活，功能非常丰富。

格式：SELECT * from  基本表名或视图 简单查询 ：

查询表的所有数据 select * from tbl_user;

有条件地查询 select * from tbl_user where username=‘zhaoying’

       select username from tbl_user;

7.模糊查询：使用LIKE

SELECT * from tbl_user where usrname LIKE ‘%zhao%’

图解：查找学生表中所有姓黄的学生信息

SELECT count  (*) FOME 学生表 WHERE 姓名 LIKE  '%黄%';    其中（*） 表示总数

图解 ：查找学生表中姓黄的学生的个数，不显示具体信息。

 8.--查看表结构
sp_help 学生表;

9.--增加一列
ALTER TABLE 学生表 ADD 备注1 VARCHAR(800);

10.--修改列的属性
ALTER TABLE 学生表 ALTER COLUMN 备注1  TEXT;

11.--修改列的名称
sp_rename '学生表.备注1' ,'备注44' , 'column';

12.---删除一列
ALTER TABLE  学生表 DROP COLUMN 备注44;（列名不要分号）

13.---修改姓名为空值

UPDATE 学生表 set 姓名=NULL where  学号=201601
--非空查询
select * from 学生表 where 姓名 IS NOT NULL;
--涉及空的查询
select * from 学生表 where 姓名 IS  NULL;

  14.查找除了信息管理之 的专业和总平均分数;

SELECT 专业, avg(总分数)  FROM 学生表  

WHERE 专业 not in ('信息管理')  GROUP  by 专业  ORDER BY 专业 ;

15.--分组查询

SELECT 专业,SUM (总分数)   FROM 学生表 
 GROUP BY 专业 HAVING SUM (总分数)<1000;

查出

16.---按照‘入学日期’顺序排序

select * from 学生表  WHERE  专业  IN ('信息管理','计算机应用') ORDER BY 入学日期

17.---按照‘入学日期’倒序排序

select * from 学生表  WHERE  专业  IN ('信息管理','计算机应用') ORDER BY 入学日期 desc;

18.---查出选体育科目的学生的学号、姓名、入学日期

方法如下：

19.--连接查询
select 学生表.学号,学生表.姓名,学生表.入学日期 from 学生表,课程表 
WHERE    学生表.学号 =课程表.学号  AND 课程表.科目='体育';

20--嵌套查询
SELECT  学生表.学号,学生表.姓名,学生表.入学日期
FROM 学生表
WHERE  学生表.学号 IN 
(select 课程表.学号 FROM 课程表 WHERE 课程表.科目='体育' );

21.select * from 课程表,学生表 where 课程表.学号=学生表.学号

同时查两个表的信息对

22.--UNION关联查询

UNION 指令的目的是将两个 SQL 语句的结果合并起来,UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。

例子：SELECT USERNAME FROM tbl_user UNION

  SELECT USERNAME FROM  tbl_user2 

23.LEFT JOIN 关联查询

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行

语法：

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name 

 --左关联，学生表是左表
SELECT * FROM  学生表  LEFT JOIN  课程表 ON 学生表.学号=课程表.学号;

--左关联，课程表是左表
SELECT * FROM  课程表  LEFT JOIN  学生表 ON 学生表.学号=课程表.学号;

24.RIGHT JOIN 关键字会右表;

面试的笔试题目

SELECT  depart_name,AVG(salary) 

FROM Empioyee WHERE depart_name NOT IN ('human resource')

GROUP BY depart_name ORDER BY depart_name;

GROUP BY 根据一个或多个列对结果集进行分组。

ORDER BY，基本排序

not in ('xxx') 不在

解1.select  depart_name, avg (salary)  from Employee
 group by   depart_name 
 HAVING depart_name  not  in ('human resource') 
order by depart_name;

    例子--select 专业,avg(总分数) from 学生表 group by 专业 HAVING 专业

 NOT IN ('软件管理') ORDER BY 专业;

解2.select  depart_name, avg (salary)  from Employee

where  depart_name  not  in ('human resource')   group by   depart_name  order by depart_name;

同理：

 例子--SELECT 专业, avg(总分数)   FROM 学生表  
WHERE 专业 not in ('信息管理')  GROUP  by 专业  ORDER BY 专业 ;

25.---查询去重

SELECT  DISTINCT 专业 FROM 学生表  

；

解：SELECT Tab1.身高  ,  Tab2.姓名，Tab3.体重

 FROM Tab1 , Tab2 , Tab3 
WHERE Tab1.ID=Tab2.ID
and  Tab2.编号=Tab3.编号 
AND Tab2.姓名='李亮';

26. 多表查询建议使用连接查询方法：

 SELECT  tab1.身高, tab2.姓名,tab3.体重 FROM tab1  LEFT JOIN tab2 ON tab1.ID=tab2.ID
LEFT JOIN  tab3 ON   tab2.编号=tab3.编号  AND tab2.姓名='李亮' ;

27.查计算机应用专业的总分数，两种方法:

28.---只显示前5行信息

SELECT  TOP 5 *  FROM Tbl_AlarmConnector 

.---显示后5行信息
SELECT  TOP 5 *  FROM Tbl_AlarmConnector  order by uidroleid desc;

29.--查询“李”字开头的信息

SELECT    *  FROM Tbl_AlarmConnector  WHERE struserdes  LIKE '李%'

--查询‘’伟‘’字结束的信息
SELECT    *  FROM Tbl_AlarmConnector  WHERE struserdes  LIKE '%伟'

 --查询带有‘’伟‘’字的信息
SELECT    *  FROM Tbl_AlarmConnector  WHERE struserdes  LIKE '%伟%'

30.----创建视图

CREATE VIEW  视图_学生表  AS  SELECT  学号,姓名 FROM 学生表;

SELECT * FROM 视图_学生表（查找学生表的视图）

DROP VIEW 视图_学生表（删去学生表的视图）

31--创建索引
CREATE UNIQUE INDEX  INDEX_姓名 ON  学生表(姓名);

DROP INDEX 学生表.INDEX_姓名（删去学生表里面姓名的索引）

sp_help 学生表

A)select s#,grade from sc where sc  c#='c2';

B)SELECT * FROM s  WHERE sname LIKE 'd%';

C)SELECT s.s#,s.sname,c,cname from s,sc,c where s.s#=sc.s# and c.c#=sc.c# and c.cname='maths';

A

SELECT S.S#,SC.GRADE,C.C2 FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.C#='C2';

SELECT SC.S#,SC.GRADE FROM SC WHERE SC.C#='C2';
B
SELECT * FROM S WHERE SNAME LIKE 'D%';


C
SELECT S.S#,S.SNAME,C.CNAME FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.CNAME='Maths';

SELECT S.S#,S.SNAME FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND SC.C#   IN 
(SELECT C.C# FROM C WHERE C.CNAME='Maths');
D
SELECT s.s#,C.C# FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND (C.C#='C2' OR C.C#='C4');这里用OR的时候要用括号括起来.

SELECT SC.S#,SC.c# from SC where sc.c#='c2' OR sc.c#='c4'

SELECT S.S# FROM S,SC WHERE S.S#=SC.S# AND SC.C# IN
(SELECT C.C# FROM C WHERE C.C#='C2' OR C.C#='C4');

SELECT S.S# FROM S,SC WHERE S.S#=SC.S# AND SC.C# IN
(SELECT C.C# FROM C WHERE C.C# IN ('C2','C4'));