数据分析中的SQL整理

1、数据分析人员应该掌握的一些sql语句

(1)基础

SQL SELECT Column_ FROM MytableSQL DISTINCT(放在SELECT后使用)SQL WHERE(设置条件)SQL AND & OR(逻辑与&逻辑或)SQL ORDER BY(排序操作，BY跟排序字段)SQL INSERT INTO VALUES(插入数据)SQL UPDATE SET(更新数据)SQL DELETE FROM(删除数据)

(2)高级

</pre><blockquote><pre name="code" class="sql">SQL LIMIT(取第N到第M条记录)SQL IN(用于子查询)SQL BETWEEN AND(设置区间)SQL LIKE(匹配通配符)SQL GROUP BY(按组查询)SQL HAVING(跟在“GROUP BY”语句后面的设置条件语句)SQL ALIAS(AS)(可以为表或列取别名)SQL LEFT JOIN/RIGHT/FULL JOIN(左连接/右连接/全连接)SQL OUT/INNER JOIN(内连接/外连接)SQL UNION/UNION ALL(并集，后者不去重)SQL INTERSECT(交集)SQL EXCEPT(差集)SQL SELECT INTO(查询结果赋给变量或表)SQL CREATE TABLE(创建表)SQL CREATE VIEW AS(创建视图)SQL CREATE INDEX(创建索引)SQL CREATE PROCEDURE BEGIN END(创建存储过程)SQL CREATE TRIGGER T_name BEFORE/AFTER INSERT/UPDATE/DELETE ON MyTable FOR (创建触发器)SQL ALTER TABLE ADD/MODIFY COLUMN/DROP(修改表:增加字段/修改字段属性/删除字段)SQL UNIQUE(字段、索引的唯一性约束)SQL NOT NULL(定义字段值非空)SQL AUTO_INCREMENT(字段定义为自动添加类型)SQL PRIMARY KEY(字段定义为主键)SQL FOREIGN KEY(创建外键约束)SQL CHECK(限制字段值的范围)SQL DROP TABLE/INDEX/VIEW/PROCEDURE/TRIGGER (删除表/索引/视图/存储过程/触发器)SQL TRUNCATE TABLE(删除表数据，不删表结构)

(3)函数

常用的文本处理函数

SQL Length(str)(返回字符串str长度)SQL Locate(substr,str)(返回子串substr在字符串str第一次出现的位置)SQL LTrim(str)(移除字符串str左边的空格)SQL RTrim(str)(移除字符串str右边的空格)SQL Trim(str)(移除字符串str左右两边的空格)SQL Left(str,n)(返回字符串str最左边的n个字符)SQL Right(str,n)(返回字符串str最右边的n个字符)SQL Soundex()SQL SubString(str,pos,len)/Substr()(从pos位置开始截取str字符串中长度为的字符串)SQL Upper(str)/Ucase(str)(小写转化为大写)SQL Lower(str)/Lcase(str)(大写转化为小写)

常用的日期与时间处理函数

SQL AddDate()(增加一个日期，天、周等)SQL AddTime()(增加一个时间，天、周等)SQL CurDate()(返回当前日期)SQL CurTime()(返回当前时间)SQL Date()(返回日期时间的日期部分)SQL DateDiff()(计算两个日期之差)SQL Date_Add()(高度灵活的日期运算函数)SQL Date_Format()(返回一个格式化的日期或时间串)SQL Day()(返回一个日期的天数部分)SQL DayOfWeek()(返回一个日期对应的星期几)SQL Hour()(返回一个时间的小时部分)SQL Minute()(返回一个时间的分钟部分)SQL Month()(返回一个日期的月份部分)SQL Now()(返回当前日期和时间)SQL Second()(返回一个时间的秒部分)SQL Time()(返回一个日期时间的时间部分)SQL Year()(返回一个日期的年份部分)

常用的数值处理函数

SQL Avg()(求均值)SQL Max()(求最大值)SQL Min()(求最小值)SQL Sum()(求和)SQL Count()(统计个数)SQL Abs()(求绝对值)SQL Cos()(求一个角度的余弦值)SQL Exp(n)(求e^n)SQL Mod()(求余)SQL Pi()(求圆周率)SQL Rand()(返回一个随机数)SQL Sin()(求一个角度的正弦值)SQL Sqrt()(求一个数的开方)SQL Tan()(求一个角度的正切值)SQL Mid(ColumnName,Start,[,length])(得到字符串的一部分)SQL Round(n,m)(以m位小数来对n四舍五入)SQL Convert(xxx,TYPE)/Cast(xxx AS TYPE) (把xxx转为TYPE类型的数据)SQL Format() (用来格式化数值)SQL First(ColumnName)(返回指定字段中第一条记录)SQL Last(ColumnName)(返回指定字段中最后一条记录)

2、操作练习

(1)工程项目-供应商-零件数据库

创建供应商表

CREATE TABLE S(  sno VARCHAR(2)  PRIMARY KEY,  sname VARCHAR(6),  status INT,  city VARCHAR(8));

插入供应商数据

INSERT INTO S VALUES('S1','精益',20,'天津');INSERT INTO S VALUES('S2','盛锡',10,'北京');INSERT INTO S VALUES('S3','东方红',30,'北京');INSERT INTO S VALUES('S4','丰泰盛',20,'天津');INSERT INTO S VALUES('S5','为民',30,'上海');SELECT * FROM S;

创建零件表

CREATE TABLE P(  pno VARCHAR(2) PRIMARY KEY,  pname VARCHAR(6),  color VARCHAR(2),  weight INT);

插入零件数据

INSERT INTO P VALUES('P1','螺母','红',12);INSERT INTO P VALUES('P2','螺栓','绿',17);INSERT INTO P VALUES('P3','螺丝刀','蓝',14);INSERT INTO P VALUES('P4','螺丝刀','红',14);INSERT INTO P VALUES('P5','凸轮','蓝',40);INSERT INTO P VALUES('P6','齿轮','红',30);SELECT * FROM P;

创建工程项目表

CREATE TABLE J(  jno VARCHAR(2) PRIMARY KEY,  jname VARCHAR(8),  city VARCHAR(8));

插入工程项目数据

INSERT INTO J VALUES('J1','三建','北京');INSERT INTO J VALUES('J2','一汽','长春');INSERT INTO J VALUES('J3','弹簧厂','天津');INSERT INTO J VALUES('J4','造船厂','天津');INSERT INTO J VALUES('J5','机车厂','唐山');INSERT INTO J VALUES('J6','无线电厂','常州');INSERT INTO J VALUES('J7','半导体厂','南京');SELECT * FROM J;

创建供应情况表

CREATE TABLE SPJ(  sno VARCHAR(2),  pno VARCHAR(2),  jno VARCHAR(2),  qty INT,  PRIMARY KEY(sno,pno,jno),  #建立表级完整性约束条件  FOREIGN KEY(sno) REFERENCES S(sno),  FOREIGN KEY(jno) REFERENCES J(jno),  FOREIGN KEY(pno) REFERENCES P(pno));

插入供应情况数据

INSERT INTO SPJ VALUES('S1','P1','J1',200);INSERT INTO SPJ VALUES('S1','P1','J3',100);INSERT INTO SPJ VALUES('S1','P1','J4',700);INSERT INTO SPJ VALUES('S1','P2','J2',100);INSERT INTO SPJ VALUES('S2','P3','J1',400);INSERT INTO SPJ VALUES('S2','P3','J2',200);INSERT INTO SPJ VALUES('S2','P3','J4',500);INSERT INTO SPJ VALUES('S2','P3','J5',400);INSERT INTO SPJ VALUES('S2','P5','J1',400);INSERT INTO SPJ VALUES('S2','P5','J2',100);INSERT INTO SPJ VALUES('S3','P1','J1',200);INSERT INTO SPJ VALUES('S3','P3','J1',200);INSERT INTO SPJ VALUES('S4','P5','J1',100);INSERT INTO SPJ VALUES('S4','P6','J3',300);INSERT INTO SPJ VALUES('S4','P6','J4',200);INSERT INTO SPJ VALUES('S5','P2','J4',100);INSERT INTO SPJ VALUES('S5','P3','J1',200);INSERT INTO SPJ VALUES('S5','P6','J2',200);INSERT INTO SPJ VALUES('S5','P6','J4',500);SELECT * FROM SPJ;SELECT FOUND_ROWS();

查询练习
供应工程J1零件的供应商号码SNO

SELECT DISTINCT sno FROM SPJ WHERE jno='J1';

供应工程J1零件P1的供应商号码

SELECT DISTINCT sno FROM SPJ WHERE jno='J1' AND pno='P1';

供应工程J1零件为红色的供应商号码

SELECT sno FROM SPJ,P WHERE jno='J1' AND color='红' AND SPJ.pno=P.pno;

没有使用天津供应商生产的红色零件的工程号

SELECT jno FROM SPJ WHERE sno IN  (SELECT sno FROM S WHERE city<>'天津')AND pno IN (SELECT pno FROM P WHERE color<>'红');

至少使用了供应商S1所供应的全部零件的工程

Unkonw

(2)学生-课程数据库

创建学生信息表

CREATE TABLE Student(  Sno CHAR(9) PRIMARY KEY,  Sname CHAR(20) UNIQUE,  Ssex char(2),  Sage SMALLINT,  Sdept CHAR(20));

插入学生信息数据

INSERT INTO Student1 VALUES('200215121','李勇','男',20,'CS'),('200215122','刘晨','女',29,'CS'),('200215123','王敏','女',18,'MA'),('200215125','张立','男',19,'IS');

创建课程表

CREATE TABLE Course(  Cno CHAR(4) PRIMARY KEY,  Cname VARCHAR(40),  Cpno CHAR(4),  Ccredit SMALLINT);

插入课程数据

INSERT INTO Course1 VALUES('1','数据库','5',4),('2','数学',NULL,2),('3','信息系统','1',4),('4','操作系统','6',3),('5','数据结构','7',4),('6','数据处理',NULL,2),('7','PASCAL语言','6',4);

创建学生课程考试信息表

CREATE TABLE SC1(  Sno CHAR(9),  Cno CHAR(4),  Grade SMALLINT,  PRIMARY KEY(Sno,Cno),  FOREIGN KEY(Sno) REFERENCES Student1(Sno),  FOREIGN KEY(Cno) REFERENCES Course1(Cno));

插入学生考试成绩数据

INSERT INTO SC1 VALUES('200215121','1',92),('200215121','2',85),('200215121','3',88),('200215122','2',90),('200215122','3',80);

建立索引，在建立唯一索引时确认索引列是具有唯一值的（按学号升序在学生表建立唯一索引）

CREATE UNIQUE INDEX Stuno ON Student(Sno);

#按课程号升序在课表上建立唯一索引

CREATE UNIQUE INDEX Coucno ON course(Cno);

查看索引

SHOW INDEX FROM Student;

删除索引

DROP INDEX Stuno ON Student;

查询全体学生的姓名和出生年份

SELECT Sname,2014-Sage Birth FROM Student;

消除重复的行

SELECT DISTINCT Sno FROM SC;

查询计算机科学系全体学生的名单

SELECT * FROM Student WHERE Sdept='CS';

查询所有年龄在20岁以下的学生姓名和年龄

SELECT Sname,Sage FROM Student WHERE Sage<20;查询年龄不在20~23岁之间的学生的姓名、系别和年龄

SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

查询计算机科学性（CS）、数学系（MA）、和信息系(IS)学生的姓名和性别

SELECT Sname,Ssex FROM Student WHERE Sdept IN('CS','MA','IS');

查询学号为200215121学生的详细情况

SELECT * FROM Student WHERE Sno='200215121';

查询所有姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';

查询姓欧阳且全名为3个汉字的学生的姓名

SELECT Sname FROM Student WHERE Sname LIKE '欧阳__';

查询所有不姓刘的学生姓名

SELECT Sname FROM Student WHERE Sname NOT LIKE '刘%'; 

查询DB_Design课程的课程号和学分

SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design';

查询缺少成绩的学生的学号和相应的课程号

SELECT Sno,Cno FROM SC WHERE Grade IS NULL;

查询所有成绩非空学生的学生学号和课程号

SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

查询计算机科学系年龄在20岁以下的学生姓名

SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20;

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排序

SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

查询学生的总人数

SELECT COUNT(*) FROM Student;

查询选修了课程的学生人数   

SELECT COUNT(*) FROM Student;

计算1号课程的学生的平均分数

SELECT Avg(Grade) FROM SC WHERE Cno='1';

查询选修1号课程的学生最高分数

SELECT Max(Grade) FROM SC WHERE Cno='1';

查询学生200215122选修课程的总学分数

SELECT Sum(Ccredit) FROM SC,Course WHERE Sno='200215122' AND SC.Cno=Course.Cno;

求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;

查询选修了1门以上课程的学生学号

SELECT Sno,COUNT(Cno) FROM SC GROUP BY Cno HAVING COUNT(Cno)>1; 

查询每个学生及其选修课程情况

SELECT * FROM Student,SC1 where Student.Sno=SC.Sno;

左外连接

SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno;

右外连接

SELECT * FROM Student RIGHT JOIN SC ON Student.Sno=SC.Sno;

查询选修2号课程且成绩在90分以上的所有学生

SELECT * FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='2' AND Grade>90;

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT * FROM Student1,Course,SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;

查询与‘刘晨’在同一个系学习的学生

SELECT * FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname='刘晨');

利用自身连接解决上面的问题

SELECT b.* FROM Student a,Student b WHERE b.Sdept=a.Sdept AND a.Sname='刘晨';

查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Student.Sno,Sname FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Cname='信息系统';

不使用连接

SELECT Student.Sno,Sname FROM Student WHERE Student.Sno IN(SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统'));

找出每个学生超过他选修课程平均成绩的课程号

SELECT SC.Sno,Cno,Grade,Avgrade FROM SC,(SELECT Sno,Avg(Grade) Avgrade FROM SC GROUP BY Sno) aWHERE SC.Sno=a.Sno AND Grade>Avgrade;

相关子查询

SELECT Sno,Cno FROM SC x WHEREGrade >= (SELECT Avg(Grade) FROM SC y WHERE x.Sno=y.Sno);

查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage FROM Student WHERESage<ANY(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';

SELECT Sname,Sage FROM StudentWHERE Sage<(SELECT Max(Sage) FROM Student WHERE Sdept='CS') AND Sdept<>'CS';

查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sname,Sage FROM Student WHERE Sage<(SELECT Min(Sage) FROM Student WHERE Sdept='CS');SELECT Sname,Sage FROM Student WHERE Sage<ALL(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';

使用EXISTS
查询所有选修了1号课程的学生姓名

SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

查询没有选修1号课程的学生姓名

SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

查询与“刘晨”同一个系学习的学生

SELECT * FROM Student a WHERE EXISTS (SELECT * FROM Student WHERE a.Sdept=Sdept AND Sname='刘晨');

查询这样一个学生，没有一门课程是他不选修的(查询选修了全部课程的学生姓名)

SELECT Sname FROM Student WHERE NOT EXISTS (  SELECT * FROM Course WHERE NOT EXISTS (    SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));

查询至少选修了学生200215122选修的全部课程的学生学号

SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS(SELECT * FROM SC SCY WHERE SCY.Sno='200215122' AND  NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno));

查询选修了课程1或者选修了课程2的学生

SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2';

查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage>19;

查询选修了课程1又选修了课程2的学生

SELECT Sno FROM SC a WHERE  Cno='1' AND Sno IN (SELECT Sno FROM SC WHERE Sno=a.Sno AND Cno='2');

查询计算机科学系的学生与年龄不大于19岁的学生的差集

SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage <= 19;

SELECT * FROM Student WHERE Sdept='CS' AND Sage>19;

将一个新学生元祖插入到Student1表中，注意字段顺序换了

INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES('200215128','陈冬','男','IS',18);

插入一条选课记录

INSERT INTO SC VALUES('200215128','1');

插入子查询结果
建立一个新表

CREATE TABLE Dept_age (  Sdept CHAR(15),  Avg_age SMALLINT);

插入系名与系内学生的平均年龄

INSERT INTO Dept_age(Sdept,Avg_age) SELECT Sdept,Avg(Sage) Avg_age FROM Student GROUP BY Sdept;

修改数据

UPDATE Student SET Sage = 22 WHERE Sno = '200215121';UPDATE Student SET Sage = Sage+1;

删除数据

DELETE FROM Student WHERE Sno='200215128';

建立视图

CREATE VIEW CS_Student AS SELECT Sno,Sname,Sdept FROM Student WHERE Sdept='CS';

建立视图，并要求进行修改和插入操作时仍需要保证该视图只有信息系的学生

CREATE VIEW IS_Student2 AS SELECT Sno,Sname,Sdept FROM Student WHERE Sdept='CS' WITH CHECK OPTION;

建立信息系选修了1号课程的学生的视图  

CREATE VIEW S1 AS SELECT Student.* FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='1'; 

建立信息系选修了1号课程且成绩在90分以上的学生的视图

CREATE VIEW S2 AS SELECT SC.Sno,Sname,Sdept FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='1' AND Grade>90;

建立一个反映学生出生年份的视图

CREATE VIEW S3 AS SELECT Sno,2014-Sage Birth FROM Student;

删除视图

DROP VIEW S1;

删除视图和由它到处的其他视图

DROP VIEW S1 CASCADE;

3、面试题整理

(1)有两个表, t1, t2,
Table t1:
SELLER | NON_SELLER
----- -----
A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C

Table t2:

SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80

要求用SELECT 语句列出如下结果:如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和......

且用的方法不要增加数据库负担,如用临时表等.

NON-SELLER| COUPON | SUM(BAL)
A 9 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80

创建表t1,t2

CREATE TABLE t1 (  Seller CHAR(1),  Nonseller CHAR(1));CREATE TABLE t2 (  Seller CHAR(1),  Coupon DECIMAL(7,3),  Bal INT);

插入数据

INSERT INTO t1 VALUES('A','B'),('A','C'),('A','D'),('B','A'),('B','C'),('B','D'),('C','A'),('C','B'),('C','D'),('D','A'),('D','B'),('D','C');INSERT INTO t2 VALUES('A',9,100),('B',9,200),('C',9,300),('D',9,400),('A',9.5,100),('B',9.5,20),('A',10,80);

查询

SELECT Nonseller,Coupon,Sum(Bal) FROM(SELECT DISTINCT Nonseller,t2.seller,Coupon,Bal FROM t1,t2 WHERE t1.seller=t2.Seller ORDER BY Coupon,Nonseller) aGROUP BY Nonseller,Coupon ORDER BY Coupon,Nonseller;

(2)获取某个时间段内的数据记录

#TO_DAYS函数：返回一个天数SELECT TO_DAYS('2015-3-11');#NOW()函数返回系统时间SELECT NOW();#查询当前日期前一个月内的记录SELECT * FROM TABAL WHERE TO_DAYS(NOW())-TO_DAYS(Outdate) <=30 ORDER BY Outdate;

(3)删除重复记录

删除a,b,c相同的记录,数据里100万以内使用下面的语句，效率还可以

DELETE FROM TABLE_A WHERE id NOT IN (SELECT Max(id) FROM TABLE_A GROUP BY a,b,c);orDELETE FROM TABLE_A WHERE id NOT IN (SELECT Min(id) FROM TABLE_A GROUP BY a,b,c);

效率较高的语句，利用临时表

#新建临时表CREATE TEMPORARY TABLE TT(  Mid  CHAR(2));#将id装入临时表中INSERT INTO TT SELECT Min(id) mid FROM Score GROUP BY Stu_id,C_name,Grade;#查询SELECT Stu_id,C_name,Grade FROM ScoreWHERE Id IN (SELECT * FROM TT);

获取重复记录

SELECT Stu_id,C_name,Grade FROM ScoreWHERE Id NOT IN (SELECT * FROM TT);

(4)将表的某一字段复制到另一表的某个字段

UPDATE TABLE1 INNER JOIN TABLE2 ON TABLE1.id=TABLE2.idSET TABLE1.value=TABLE2.value; 

(5)CONVERT函数与CAST函数区别

#CAST(XXX AS TYPE),CONVERT(XXX,TYPE)#TYPE包括BINARY、CHAR()、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNEDSELECT CONVERT('100',SIGNED);SELECT CAST('100' AS SIGNED);

(6)查询每个分组的前n条记录

例子：查询每个班总分排名TOP2

#建表并插入数据create table test(id int unsigned not null auto_increment primary key,name varchar(10),class varchar(20),score varchar(20));insert into test(name, class, score) values ('gonn', '6(1)', '299');insert into test(name, class, score) values ('yyun', '6(1)', '259');insert into test(name, class, score) values ('lin', '6(1)', '289');insert into test(name, class, score) values ('mei', '6(1)', '277');insert into test(name, class, score) values ('xj', '6(2)', '287');insert into test(name, class, score) values ('zhl', '6(2)', '277');insert into test(name, class, score) values ('lwjs', '6(2)', '257');insert into test(name, class, score) values ('lulu', '6(2)', '265');#查询SELECT Id,Class,Score FROM(SELECT a.Id,a.Name,a.Class,a.Score,b.Score Scoreb FROM Test a,Test b WHERE a.Class=b.Class AND a.Score>b.Score) TGROUP BY Id,Class HAVING Count(Id)>=2;

(7)主键与索引的区别

#1、主键是唯一性索引，唯一性索引不一定是主键#2、一个表中可以有多个唯一性索引，但是只有一个主键#3、主键列不允许有空值，而唯一性索引列允许控制#4/索引可以提高查询速度#5、两者都是键，但是主键是逻辑键，索引是物理键

(8)列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

#新建表,插入数据create table employee921(id int primary key auto_increment,name varchar(50),salary bigint,deptid int);insert into employee921 values(null,'zs',1000,1),(null,'ls',1100,1),(null,'ww',1100,1),(null,'zl',900,1) ,(null,'zl',1000,2),(null,'zl',900,2) ,(null,'zl',1000,2) , (null,'zl',1100,2);

SELECT COUNT(deptid),deptid FROM(SELECT a.name ename,a.salary,a.deptid FROM employee921 a,(SELECT deptid,AVG(salary) avgsala FROM employee921 GROUP BY deptid) bWHERE a.deptid=b.deptid AND a.salary>avgsala)  cGROUP BY deptidORDER BY deptid DESC;

(9)连接多个变量

SELECT Concat('123',Cast(456 AS CHAR));SELECT Concat('123',Cast(456 AS CHAR),'789');

(10)找出TABLE1中处在ID字段中1-200条记录中Name字段包含w的所有记录

SELECT * FROM TABLE1 WHERE ID BETWEEN 1 AND 200 AND Name LIKE '%w%';

(11)找出拥有超过10名客户的地区的列表

SELECT Zore FROM Customers GROUP BY Zore HAVING Count(Customerid)>10;

(12)取出每个部门工资最高TOP3

SELECT id FROM(SELECT * FROM Employee a WHERE Employee.Employeeid=a.Employeeid AND Employee.Salary>a.Salary) tGROUP BY id HAVING Count(id)>(SELECT COUNT(id)-3 FROM Employee);

(13)两个结构相同的表a,b，主键为index，把a表中存在但b表中不存在的记录插入b中

INSERT INTO b SELECT * FROM a WHERE NOT EXISTS(SELECT * FROM b WHERE a.index=index);

(14)从一个数据库中的多个数据表提取相关变量

SELECT Table1.Var1,Table2.Var2,Table3.Var3 FROM Table1 INNER JOIN Table2 ONTable1.Var1=Table2.Var1 INNER JOIN Table3 ON Table1.Var2=Table3.Var2;

(15)一个叫department的表，里面只有一个字段name,一共有4条记录，分别是a,b,c,d,对应四个球队。现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合

SELECT * FROM Department a WHERE a.Name>Department.Name;

(16)删除除了自动编号不同,其他都相同的学生冗余信息

#假设有字段：Id、Sno、Sname、SsexDELETE FROM Student WHERE Id NOT IN (SELECT Min(Id) FROM Student GROUP BY Sno,Sname,Ssex)

(17)用一条SQL语句 查询出每门课都大于80分的学生学号

#利用得分最低的课SELECT Sno,Min(Grade) Min_grade FROM SC GROUP BY Sno HAVING Min_grade>80;

(18)设计一个四表连接查询语句

SELECT a.column,b.column,c.column,d.column FROM a INNER JOIN b ON a.a=b.b INNER JOIN c a.a=c.c INNER JOIN d a.a=d.d WHERE 条件;

(19)在两张关联表A,B中，删除副表B中不存在，而主表A中存在的记录

DELETE FROM A WHERE NOT EXISTS (SEELCT * FROM B WHERE A.Id=B.Id);

(20)请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。

 (SELECT * FROM TestDB a, (SELECT 月份 b月份,Max(发生额) t FROM TestDB WHERE 科目号='101' GROUP BYb月份) b WHERE a.月份=b月份 AND a.发生额>=t) c GROUP BY 科目号 HAVING Count(科目号)=12;

(21)查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。

SELECT 编号,姓名,部分,If(出生日期 IS NULL,'日期不详',Date(出生日期)) FROM 员工信息表 ORDER BY 部门;

(22)查找与喻自强在同一个单位的员工姓名、性别、部门和职称

SELECT 姓名,性别,部门,职称 FROM 员工信息表 WHERE 工作单位=(SELECT 工作单位 FROM 员工信息表 WHERE 姓名='喻自强');

(23)按部门进行汇总，统计每个部门的总工资

SELECT Department,Count(Salary) FROM Employee GROUP BY Department;

(24)查找商品名称为“14寸显示器”商品的销售情况，显示该商品的编号、销售数量、单价和金额

SELECT 编号,销售数量,单价,金额 FROM 销售明细表 WHERE  商品名称='14寸显示器';

(25)在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额

SELECT 编号,Count(销售数量),Count(金额) FROM 销售明细表 GROUP BY 编号;

(26)按客户编号统计每个客户1996年的订单总金额

SELECT 客户编号,Count(金额) FROM 订单表 WHERE Year(时间)='1996' GROUP BY 客户编号;

(27)查找有销售记录的客户编号、名称和订单总额

SELECT 客户编号,名称,订单总额 FROM 销售明细表 WHERE 订单总额 IS NOT NULL;

(28)查找在1997年中有销售记录的客户编号、名称和订单总额

SELECT 客户编号,名称,订单总额 FROM 销售明细表 WHERE Year(时间)='1997' AND 订单总额 IS NOT NULL;

(29)查找至少有3次销售的业务员名单和销售日期

SELECT 业务员名单,销售日期 FROM 销售明细表 WHERE 业务员名单 IN(SELECT 业务员名单 FROM 销售明细表 GROUP BY 业务员名单 HAVING(业务员名单)>=3);

(30)用存在量词查找没有订货记录的客户名称

SELECT 客户名称 FROM 客户信息表 WHERE NOT EXISTS (SELECT * 订货信息表 WHERE 客户信息表.客户名称=客户名称);

(31)使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额

SELECT 客户编号,名称,订货日期,订单金额 FROM 客户信息表 LEFT JOIN 订货信息表 ON 订货信息表.客户编号=客户信息表.客户编号;

(32)查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用男、女表示

SELECT 姓名,If(性别='M','男','女') 性别,销售日期,销售数量,金额 FROM 销售明细表 WHERE 商品名称='16M DRAM';

(33)查找销售总额少于1000元的销售员编号、姓名和销售额

SELECT 销售员编号,姓名,销售额 FROM 销售明细表 WHERE 销售员编号 IN(SELECT 销售员编号 FROM 销售明细表 GROUP BY 销售员编号 HAVING Conut(销售额)<1000);

(34)查找至少购买了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额

SELECT 客户编号,客户名称,商品编号,商品名称,数量和金额 FROM 订货信息表 WHERE 客户编号 IN(SELECT 客户编号 FROM 订货信息表 GROUP BY 客户编号  HAVING Count(客户编号)>=3 );

(35)怎么把这样一个表儿
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

SELECT Year,(SELECT Amount FROM Mytable a WHERE Month='1' AND a.Year=Mytable.Year) m1,(SELECT Amount FROM Mytable a WHERE Month='2' AND a.Year=Mytable.Year) m2,(SELECT Amount FROM Mytable a WHERE Month='3' AND a.Year=Mytable.Year) m3,(SELECT Amount FROM Mytable a WHERE Month='4' AND a.Year=Mytable.Year) m4FROM Mytable GROUP BY Year;

(36)拷贝表

INSERT INTO Table1(a,b,c) SELECT a1,b1,c1 FROM Table2;

(37)有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value替换为A中对应的value

UPDATE B SET Value=(SELECT A.Value FROM A,B WHERE A.key=B.key) WHERE B.Key IN (SELECT Key FROM A,B WHERE A.Key=B.Key);

(38)原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------

SELECT Datatable.*,If(Sign(Score-80)='1','PASS','FAIL') Mark FROM Datatable;

(39)原表:
id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表:
id pro1 pro2
1 M F
2 N G
3 B A

SELECT Id,(SELECT Proname FROM Datatable a WHERE Proid='1' AND a.Id=Datatable.Id) prod1,(SELECT Proname FROM Datatable a WHERE Proid='2' AND a.Id=Datatable.Id) prod2FROM Datatable GROUP BY Id;

(40)写出查询语句
表a
列 a1 a2
记录
1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗？
1 ab
2 xyz;
创建表并插入数据

CREATE TABLE a (  a1 CHAR(2),  a2 CHAR(2));INSERT INTO a VALUES('1','a'),('1','b'),('2','x'),('2','y'),('2','z');SELECT a1,(SELECT a2 FROM a b WHERE a.a1=b.a1) mark FROM a GROUP BY a1;SELECT * FROM a b,a c WHERE b.a1=c.a1; 

TableX有三个字段Code、 Name、 Age、 其中Code为主键；
TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下：
Code Name Age Code Class Score
97001 张三 22 97001 数学 80
97002 赵四 21 97002 计算机 59
97003 张飞 20 97003 计算机 60
97004 李五 22 97004 数学 55
(41)请写出SQL，找出所有姓张的学生，并按年龄从小到大排列；

SELECT * FROM TableX WHERE Name LIKE '张%' ORDER BY Age;

(42)请写出SQL，取出计算机科考成绩不及格的学生；

SELECT Code,Name FROM TableX,TableY WHERE Name='计算机' AND Score<60 AND TableX.Code=TableY.Code;

(43)通过等值联接，取出Name、Class、Score，请写出SQL即输出结果

SELECT Name,Class,Score FROM TableX,TableY WHERE Student.Sno=SC.Sno; 

(44)通过外联接，取出每个学生的Name、Class、Score、请写SQL输出结果

SELECT Nmae,Class,Score FROM TableX LEFT JOIN TableY ON TableX.Code=TableY.Code;

(45)请写SQL，在TableX 表中增加一条学生记录(学号：97005 姓名：赵六 年龄：20)；

INSERT INTO TableX(Code,Name,Age) VALUES('97005','赵六',20); 

(46)李五的年龄记录错了，应该是21，请写SQL，根据主键进行更新；

UPDATE TableX SET Age=21 WHERE Name='李五';

(47)请写SQL，删除TableX中没有考试成绩的学生记录，请使用not in条件；

DELETE FROM TableX WHERE Code NOT IN (SELECT DISTINCT Code FROM TableY);

有两个表定义如下：
create table individual (
firstname   varchar2(20) not null
lastname    vatchar2(20) not null
birthdate   date
gender      varchar2(1)
initial     number(2)
farorite    varchar2(6)
type        varchar2(8)
);

create table chile_detail(
firstname   varchar2(20)
lastname    varchar2(20)
cname       varchar2(8)
coment      varchar2(2)
type        varchar2(8)
);
(48)在表individual中建唯一索引 firstname + lastname

CREATE UNIQUE INDEX index_fl ON individual(firstname,lastname);

(49)写一个简单的SQL语句实现：删除表individual中一条出生日期（brithdate）为 1990年10月2日 出生的人的记录

DELETE FROM Individual WHERE Birthdate='1990-10-2';

(50)写一修改语句实现： 将表child_detail 中的type 为 “kkd” 的记录的Cname 值为“declear”，coment的值为“02”

UPDATE Child_detail SET Cname='declear',Coment='02' WHERE Type='kkd' ;