mysql查姓名中既有a也有e的姓_mysql查询初级练习题（已更完）
這些初級題應(yīng)該算是入了sql查詢的門了吧？但是很多性能問題還是不清楚，太多知識有欠缺，一點(diǎn)一點(diǎn)來吧。
第一題
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,’計(jì)算機(jī)系’, ‘北京市海淀區(qū)’);
INSERT INTO student VALUES( 902,’張老二’, ‘男’,1986,’中文系’, ‘北京市昌平區(qū)’);
INSERT INTO student VALUES( 903,’張三’, ‘女’,1990,’中文系’, ‘湖南省永州市’);
INSERT INTO student VALUES( 904,’李四’, ‘男’,1990,’英語系’, ‘遼寧省阜新市’);
INSERT INTO student VALUES( 905,’王五’, ‘女’,1991,’英語系’, ‘福建省廈門市’);
INSERT INTO student VALUES( 906,’王六’, ‘男’,1988,’計(jì)算機(jī)系’, ‘湖南省衡陽市’);
INSERT INTO score VALUES(NULL,901, ‘計(jì)算機(jī)’,98);
INSERT INTO score VALUES(NULL,901, ‘英語’, 80);
INSERT INTO score VALUES(NULL,902, ‘計(jì)算機(jī)’,65);
INSERT INTO score VALUES(NULL,902, ‘中文’,88);
INSERT INTO score VALUES(NULL,903, ‘中文’,95);
INSERT INTO score VALUES(NULL,904, ‘計(jì)算機(jī)’,70);
INSERT INTO score VALUES(NULL,904, ‘英語’,92);
INSERT INTO score VALUES(NULL,905, ‘英語’,94);
INSERT INTO score VALUES(NULL,906, ‘計(jì)算機(jī)’,90);
INSERT INTO score VALUES(NULL,906, ‘英語’,85);
1) 查詢student表的第2條到4條記錄中計(jì)算機(jī)系和英語系的學(xué)生的信息
2) 從student表中查詢每個(gè)院系年齡18~30歲的學(xué)生信息
3) 查詢計(jì)算機(jī)成績低于95的學(xué)生信息
4) 查詢同時(shí)參加計(jì)算機(jī)和英語考試的學(xué)生的信息
#1) 查詢student表的第2條到4條記錄中計(jì)算機(jī)系和英語系的學(xué)生的信息
select * from (select * from student limit 1,3) as temTable where temTable.Department in (“計(jì)算機(jī)系”,”英語系”);
#2) 從student表中查詢每個(gè)院系年齡18~30歲的學(xué)生信息
select * from student where (year(now()) – Birth) between 18 and 22;
#3) 查詢計(jì)算機(jī)成績低于95的學(xué)生信息
select * from student where Id in (select Stu_id from score where Grade < 95);
#4) 查詢同時(shí)參加計(jì)算機(jī)和英語考試的學(xué)生的信息
SELECT * FROM student WHERE id in
(SELECT stu_id FROM score WHERE stu_id IN
(SELECT stu_id FROM score WHERE c_name= ‘計(jì)算機(jī)’)
AND c_name= ‘英語’ );#學(xué)著用any和把同一個(gè)表查兩次！！！
第二題
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,”郭慧”,”濟(jì)南”,3492);
insert into customer values (101005,”zain”,”北京”,6324);
insert into bank values (“B0001″,”工商銀行”);
insert into bank values (“B0002″,”建設(shè)銀行”);
insert into bank values (“B0003″,”中國銀行”);
insert into bank values (“B0004″,”農(nóng)業(yè)銀行”);
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 進(jìn)行查詢，查詢條件為 location 在廣州、蘇州、濟(jì)南的客戶，存款在300000 至 500000 之間的存款記錄，顯示客戶姓名 name、銀行名稱 bank_name、存款金額 amount.
2) 對 deposite 表進(jìn)行統(tǒng)計(jì)，按銀行統(tǒng)計(jì)存款總數(shù)，顯示為 b_id,total.
#對 deposite、customer、bank 進(jìn)行查詢，查詢條件為 location
#在廣州、蘇州、濟(jì)南的客戶，存款在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 (“廣州”,”蘇州”,”濟(jì)南”)
#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 (“廣州”,”蘇州”,”濟(jì)南”)
) 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 表進(jìn)行統(tǒng)計(jì)，按銀行統(tǒng)計(jì)存款總數(shù)，顯示為 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月有話費(fèi)產(chǎn)生且話費(fèi)為51到100；9,10月份沒有話費(fèi)產(chǎn)生的用戶
2刪除jifei表中10月份出現(xiàn)的相同記錄的多余條數(shù)
3查詢9,10月份，月均30元以上的用戶(結(jié)果無重復(fù)項(xiàng))
1.查找6,7,8月有話費(fèi)產(chǎn)生且話費(fèi)為51到100；9,10月份沒有話費(fèi)產(chǎn)生的用戶
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月份出現(xiàn)的相同記錄的多余條數(shù)
我覺得沒有不同的id，所有內(nèi)容都一樣時(shí)，無法刪除只保留一條，有寫出答案的希望可以告訴我多謝。所以我只寫了一個(gè)”查出10月份出現(xiàn)重復(fù)記錄的用戶“如下：
SELECT phone FROM
(SELECT * FROM jifei WHERE MONTH(month) = 10) AS a
GROUP BY a.phone
HAVING count(*) > 1
這個(gè)鏈接里面對處理重復(fù)數(shù)據(jù)寫的還比較全面。
3查詢9,10月份，月均30元以上的用戶(結(jié)果無重復(fù)項(xiàng))
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,”計(jì)算機(jī)系”),
(“9512102″,”劉晨”,”男”,20,”計(jì)算機(jī)系”),
(“9512103″,”王敏”,”女”,20,”計(jì)算機(jī)系”),
(“9512103″,”王敏”,”女”,20,”計(jì)算機(jī)系”),
(“9521101″,”張立”,”男”,22,”信息系”),
(“9521102″,”吳賓”,”女”,21,”信息系”),
(“9521103″,”張海”,”男”,20,”信息系”),
(“9531101″,”錢小力”,”女”,18,”數(shù)學(xué)系”),
(“9531102″,”王大力”,”男”,19,”數(shù)學(xué)系”);
insert into course values
(“c01″,”計(jì)算機(jī)文化學(xué)”,70),(“c02″,”VB”,90),
(“c03″,”計(jì)算機(jī)網(wǎng)絡(luò)”,80),(“c04″,”數(shù)據(jù)庫基礎(chǔ)”,108),
(“c05″,”高等數(shù)學(xué)”,180),(“c06″,”數(shù)據(jù)結(jié)構(gòu)”,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) 查詢哪些學(xué)生的年齡相同,要求列出年齡相同的學(xué)生的姓名和年齡。
2) 分別查詢信息系和計(jì)算機(jī)系的學(xué)生的姓名、性別、修課名稱、修課成績,
a)并要求將這兩個(gè)查詢結(jié)果合并成一個(gè)結(jié)果集,
b)并以系名、姓名、性別、修課名稱、修課成績的順序顯示各列。
3) 將計(jì)算機(jī)系成績高于 80 分的學(xué)生的修課情況插入到另一張表中,分兩種情況實(shí)現(xiàn)：
a) 在插入數(shù)據(jù)過程中建表。
b)先建一個(gè)新表,然后再插入數(shù)據(jù)
#1) 查詢哪些學(xué)生的年齡相同,要求列出年齡相同的學(xué)生的姓名和年齡。
SELECT * FROM student WHERE sno NOT IN(
SELECT sno FROM student GROUP BY sage HAVING COUNT(*) = 1)
ORDER BY sage;
#2) 分別查詢信息系和計(jì)算機(jī)系的學(xué)生的姓名、性別、修課名稱、修課成績,
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 = “計(jì)算機(jī)系”;
#a)并要求將這兩個(gè)查詢結(jié)果合并成一個(gè)結(jié)果集,
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 (“信息系”,”計(jì)算機(jī)系”);
#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 (“信息系”,”計(jì)算機(jī)系”) ORDER BY sdept;
#3) 將計(jì)算機(jī)系成績高于 80 分的學(xué)生的修課情況插入到另一張表中,分兩種情況實(shí)現(xiàn)：
INSERT INTO newStudent SELECT * FROM student;
#a) 在插入數(shù)據(jù)過程中建表。
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 = “計(jì)算機(jī)系” AND grade > 80;
#b) 先建一個(gè)新表,然后再插入數(shù)據(jù)
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 = “計(jì)算機(jī)系” AND grade > 80;
能力有限，出現(xiàn)錯(cuò)誤希望可以指出
完結(jié)
總結(jié)
以上是生活随笔為你收集整理的mysql查姓名中既有a也有e的姓_mysql查询初级练习题（已更完）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 牛客错题集5
- 下一篇： 支配树与Lengauer-Tarjan算