做网站绿色和什么颜色搭配/制作网站需要什么软件
这些初级题应该算是入了sql查询的门了吧？但是很多性能问题还是不清楚，太多知识有欠缺，一点一点来吧。
第一题
create table student(
Id int(10) not null auto_increment primary key,
Name varchar(20) not null ,
Sex varchar(4),
Birth year,
Department varchar(20) not null,
Address varchar(50)
)engine = InnoDB default charset = utf8
create table score(
Id int(10) not null auto_increment primary key,
Stu_id int(10) not null,
C_name varchar(20),
Grade int(10)
)engine = InnoDB default charset = utf8
INSERT INTO student VALUES( 901,’张老大’, ‘男’,1985,’计算机系’, ‘北京市海淀区’);
INSERT INTO student VALUES( 902,’张老二’, ‘男’,1986,’中文系’, ‘北京市昌平区’);
INSERT INTO student VALUES( 903,’张三’, ‘女’,1990,’中文系’, ‘湖南省永州市’);
INSERT INTO student VALUES( 904,’李四’, ‘男’,1990,’英语系’, ‘辽宁省阜新市’);
INSERT INTO student VALUES( 905,’王五’, ‘女’,1991,’英语系’, ‘福建省厦门市’);
INSERT INTO student VALUES( 906,’王六’, ‘男’,1988,’计算机系’, ‘湖南省衡阳市’);
INSERT INTO score VALUES(NULL,901, ‘计算机’,98);
INSERT INTO score VALUES(NULL,901, ‘英语’, 80);
INSERT INTO score VALUES(NULL,902, ‘计算机’,65);
INSERT INTO score VALUES(NULL,902, ‘中文’,88);
INSERT INTO score VALUES(NULL,903, ‘中文’,95);
INSERT INTO score VALUES(NULL,904, ‘计算机’,70);
INSERT INTO score VALUES(NULL,904, ‘英语’,92);
INSERT INTO score VALUES(NULL,905, ‘英语’,94);
INSERT INTO score VALUES(NULL,906, ‘计算机’,90);
INSERT INTO score VALUES(NULL,906, ‘英语’,85);
1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息
2) 从student表中查询每个院系年龄18~30岁的学生信息
3) 查询计算机成绩低于95的学生信息
4) 查询同时参加计算机和英语考试的学生的信息
#1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息
select * from (select * from student limit 1,3) as temTable where temTable.Department in (“计算机系”,”英语系”);
#2) 从student表中查询每个院系年龄18~30岁的学生信息
select * from student where (year(now()) – Birth) between 18 and 22;
#3) 查询计算机成绩低于95的学生信息
select * from student where Id in (select Stu_id from score where Grade < 95);
#4) 查询同时参加计算机和英语考试的学生的信息
SELECT * FROM student WHERE id in
(SELECT stu_id FROM score WHERE stu_id IN
(SELECT stu_id FROM score WHERE c_name= ‘计算机’)
AND c_name= ‘英语’ );#学着用any和把同一个表查两次！！！
第二题
create table customer(
c_id char(6) not null primary key,
name varchar(30) not null,
location varchar(30),
salary decimal(8,2)
)engine = InnoDB default charset = utf8;
create table bank(
b_id char(5) not null primary key,
bank_name char(30) not null
)engine = InnoDB default charset = utf8;
create table deposite(
d_id int not null auto_increment primary key,
c_id char(6),
b_id char(5),
dep_date date,
dep_type char(1),
amount decimal(8,2),
foreign key(b_id) references bank(b_id),
foreign key(c_id) references customer(c_id)
)engine = InnoDB default charset = utf8;
insert into customer values (101001,”孙杨”,”广州”,1234);
insert into customer values (101002,”郭海”,”南京”,3526);
insert into customer values (101003,”卢江”,”苏州”,6892);
insert into customer values (101004,”郭慧”,”济南”,3492);
insert into customer values (101005,”zain”,”北京”,6324);
insert into bank values (“B0001″,”工商银行”);
insert into bank values (“B0002″,”建设银行”);
insert into bank values (“B0003″,”中国银行”);
insert into bank values (“B0004″,”农业银行”);
insert into deposite values (1,”101001″,”B0001″,”2011-04-05″,”3″,42526);
insert into deposite values (2,”101002″,”B0002″,”2012-07-15″,”5″,66500);
insert into deposite values (3,”101003″,”B0003″,”2010-11-24″,”1″,42366);
insert into deposite values (4,”101004″,”B0004″,”2008-03-31″,”1″,62362);
insert into deposite values (5,”101001″,”B0003″,”2002-02-07″,”3″,56346);
insert into deposite values (6,”101002″,”B0001″,”2004-09-23″,”3″,353626);
insert into deposite values (7,”101003″,”B0004″,”2003-12-14″,”5″,36236);
insert into deposite values (8,”101004″,”B0002″,”2007-04-21″,”5″,26267);
insert into deposite values (9,”101001″,”B0002″,”2011-02-11″,”1″,435456);
insert into deposite values (10,”101002″,”B0004″,”2012-05-13″,”1″,234626);
insert into deposite values (11,”101003″,”B0003″,”2001-01-24″,”5″,26243);
insert into deposite values (12,”101004″,”B0001″,”2009-08-03″,”3″,45671);
1) 对 deposite、customer、bank 进行查询，查询条件为 location 在广州、苏州、济南的客户，存款在300000 至 500000 之间的存款记录，显示客户姓名 name、银行名称 bank_name、存款金额 amount.
2) 对 deposite 表进行统计，按银行统计存款总数，显示为 b_id,total.
#对 deposite、customer、bank 进行查询，查询条件为 location
#在广州、苏州、济南的客户，存款在300000 至 500000 之间的存款记录，
#显示客户姓名 name、银行名称 bank_name、存款金额 amount.
SELECT amount,name,bank_name
FROM customer INNER JOIN deposite ON deposite.c_id = customer.c_id
INNER JOIN bank ON deposite.b_id = bank.b_id
WHERE deposite.amount BETWEEN 300000 AND 500000 AND customer.location IN (“广州”,”苏州”,”济南”)
#2先子查询再连表
select amount,name,bank_name from(
select b_id,c_id,amount from deposite where c_id in
(
select c_id from customer where location in (“广州”,”苏州”,”济南”)
) and d_id in(
select d_id from deposite where amount between 300000 and 500000
)
)as temTable
inner join customer on temTable.c_id = customer.c_id
inner join bank on temTable.b_id = bank.b_id
#对 deposite 表进行统计，按银行统计存款总数，显示为 b_id,total.
select b_id, sum(amount) as total from deposite group by b_id
第三题
create table jifei(
phone char(8),
month char(8),
expenses decimal(8,2)
)engine = InnoDB default charset = utf8;
insert into jifei values (“11111111″,”20100606”,51.2);
insert into jifei values (“11111111″,”20100706”,51.2);
insert into jifei values (“11111111″,”20100806”,51.2);
insert into jifei values (“11111111″,”20100906”,0);
insert into jifei values (“11111111″,”20101006”,0);
insert into jifei values (“22222222″,”20100606”,70.2);
insert into jifei values (“22222222″,”20100706”,70.2);
insert into jifei values (“22222222″,”20100806”,50);
insert into jifei values (“22222222″,”20100906”,0);
insert into jifei values (“22222222″,”20101006”,0);
insert into jifei values (“33333333″,”20100606”,70);
insert into jifei values (“33333333″,”20100706”,70);
insert into jifei values (“33333333″,”20100806”,70);
insert into jifei values (“33333333″,”20100906”,0);
insert into jifei values (“33333333″,”20101006”,70);
insert into jifei values (“44444444″,”20100606”,0);
insert into jifei values (“44444444″,”20100706”,0);
insert into jifei values (“44444444″,”20100806”,0);
insert into jifei values (“44444444″,”20100906”,0);
insert into jifei values (“44444444″,”20101006”,0);
insert into jifei values (“44444444″,”20101006”,0);
1.查找6,7,8月有话费产生且话费为51到100；9,10月份没有话费产生的用户
2删除jifei表中10月份出现的相同记录的多余条数
3查询9,10月份，月均30元以上的用户(结果无重复项)
1.查找6,7,8月有话费产生且话费为51到100；9,10月份没有话费产生的用户
SELECT phone1 FROM(
SELECT phone AS phone1 FROM
(SELECT * FROM jifei
WHERE MONTH(month) IN (6,7,8) AND expenses BETWEEN 51 AND 100) AS a
GROUP BY a.phone
HAVING count(1) = 3
) AS temTableA
WHERE temTableA.phone1 IN(
SELECT phone AS phone2 FROM
(SELECT * FROM jifei
WHERE MONTH(month) IN (9,10) AND expenses = 0) AS b
GROUP BY b.phone
HAVING count(1) = 2
)
2删除jifei表中10月份出现的相同记录的多余条数
我觉得没有不同的id，所有内容都一样时，无法删除只保留一条，有写出答案的希望可以告诉我多谢。所以我只写了一个”查出10月份出现重复记录的用户“如下：
SELECT phone FROM
(SELECT * FROM jifei WHERE MONTH(month) = 10) AS a
GROUP BY a.phone
HAVING count(*) > 1
这个链接里面对处理重复数据写的还比较全面。
3查询9,10月份，月均30元以上的用户(结果无重复项)
SELECT DISTINCT phone FROM jifei
WHERE MONTH(month) IN (9,10)
GROUP BY phone HAVING AVG(expenses) > 30;
第四题
create database xuexiao;
use xuexiao;
create table sc (sno varchar(20), cno varchar(20), grade int)ENGINE = INNODB DEFAULT charset = utf8;
create table course (cno varchar(20), cname varchar(20), hours int)ENGINE = INNODB DEFAULT charset = utf8;
create table student (sno varchar(20), sname varchar(20), ssex char(10), sage int, sdept varchar(20))ENGINE = INNODB DEFAULT charset = utf8;
insert into student values
(“9512101″,”李勇”,”男”,19,”计算机系”),
(“9512102″,”刘晨”,”男”,20,”计算机系”),
(“9512103″,”王敏”,”女”,20,”计算机系”),
(“9512103″,”王敏”,”女”,20,”计算机系”),
(“9521101″,”张立”,”男”,22,”信息系”),
(“9521102″,”吴宾”,”女”,21,”信息系”),
(“9521103″,”张海”,”男”,20,”信息系”),
(“9531101″,”钱小力”,”女”,18,”数学系”),
(“9531102″,”王大力”,”男”,19,”数学系”);
insert into course values
(“c01″,”计算机文化学”,70),(“c02″,”VB”,90),
(“c03″,”计算机网络”,80),(“c04″,”数据库基础”,108),
(“c05″,”高等数学”,180),(“c06″,”数据结构”,72);
insert into sc values
(“9512101″,”c01”,90),
(“9512101″,”c02”,86),
(“9512101″,”c06”,null),
(“9512102″,”c02”,78),
(“9512102″,”c04”,66),
(“9521102″,”c01”,82),
(“9521102″,”c02”,75),
(“9521102″,”c04”,92),
(“9521102″,”c05”,50),
(“9521103″,”c02”,68),
(“9521103″,”c06”,null),
(“9531101″,”c01”,80),
(“9531101″,”c05”,95),
(“9531102″,”c05”,85);
1) 查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
2) 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
a)并要求将这两个查询结果合并成一个结果集,
b)并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
3) 将计算机系成绩高于 80 分的学生的修课情况插入到另一张表中,分两种情况实现：
a) 在插入数据过程中建表。
b)先建一个新表,然后再插入数据
#1) 查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
SELECT * FROM student WHERE sno NOT IN(
SELECT sno FROM student GROUP BY sage HAVING COUNT(*) = 1)
ORDER BY sage;
#2) 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno
LEFT JOIN course ON course.cno = sc.cno WHERE sdept = “信息系”;
SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno
LEFT JOIN course ON course.cno = sc.cno WHERE sdept = “计算机系”;
#a)并要求将这两个查询结果合并成一个结果集,
SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno
LEFT JOIN course ON course.cno = sc.cno WHERE sdept IN (“信息系”,”计算机系”);
#b)并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
SELECT sdept,sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno
LEFT JOIN course ON course.cno = sc.cno WHERE sdept IN (“信息系”,”计算机系”) ORDER BY sdept;
#3) 将计算机系成绩高于 80 分的学生的修课情况插入到另一张表中,分两种情况实现：
INSERT INTO newStudent SELECT * FROM student;
#a) 在插入数据过程中建表。
CREATE TABLE studentInfo1 AS SELECT sname,sage,cname,grade FROM student INNER JOIN sc ON student.sno = sc.sno
INNER JOIN course ON sc.cno = course.cno WHERE sdept = “计算机系” AND grade > 80;
#b) 先建一个新表,然后再插入数据
CREATE TABLE studentInfo (sname VARCHAR(20), sage INT, cname VARCHAR(20),grade INT)ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO studentInfo
SELECT sname,sage,cname,grade FROM student INNER JOIN sc ON student.sno = sc.sno
INNER JOIN course ON sc.cno = course.cno WHERE sdept = “计算机系” AND grade > 80;
能力有限，出现错误希望可以指出
完结