MySQL习题 求数据库高手解答

create table stu1 (

Id int auto_increment primary KEY,

Name VARCHAR(20) not null ,

Sex varchar(4) ,

Brith Date ,

department VARCHAR(20)not null,

address VARCHAR(50)

)

create table score(

Id INT auto_increment PRIMARY KEY,

stu_id int,

FOREIGN KEY(stu_id) REFERENCES stu1(Id) on DELETE CASCADE,

C_name VARCHAR(20),

grade int

)

SELECT *from stu1

INSERT INTO stu1 VALUES( 901,'张老大', '男','1985-06-05','计算机系', '北京市海淀区')

INSERT INTO stu1 VALUES( 902,'张老二', '男','1986-06-05','中文系', '北京市昌平区')

INSERT INTO stu1 VALUES( 903,'张三', '女','1990-06-05','中文系', '湖南省永州市')

INSERT INTO stu1 VALUES( 904,'李四', '男','1990-06-05','英语系', '辽宁省阜新市')

INSERT INTO stu1 VALUES( 905,'王五', '女','1991-06-05','英语系', '福建省厦门市')

INSERT INTO stu1 VALUES( 906,'王六', '男','1988-06-05','计算机系', '湖南省衡阳市')

SELECT *from Score

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '计算机',98)

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '英语', 80)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '计算机',65)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '中文',88)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '中文',95)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '计算机',70)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '英语',92)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '英语',94)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '计算机',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '英语',85)

/*查询 student 表的第 2 条到 4 条记录*/

SELECT * from stu1 LIMIT 2,2

/*从 student 表中查询计算机系和英语系的学生的信息*/

SELECT *from stu1 WHERE department in('计算机系','英语系')

/*从 student 表中查询年龄 18~22 岁的学生信息*/

SELECT YEAR('1990-09-09')

SELECT YEAR(NOW())-YEAR('1990-09-09')

SELECT s.Name,s.age,s.Brith from (SELECT *,YEAR(NOW())-YEAR(Brith)as age FROM stu1)as s WHERE s.age>23 and s.age<28

/*8.从 student 表中查询每个院系有多少人*/

SELECT department,COUNT(id) from stu1 GROUP BY(department)

/*从 score 表中查询每个科目的最高分*/

(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

SELECT id,h.C_name,h.scote_m FROM score,(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

WHERE h.C_name=Score.C_name and h.scote_m=Score.grade

/*10.查询李四的考试科目（c_name）和考试成绩（grade）*/

SELECT name,C_name,grade from stu1,score where name='李四'and stu1.id=score.stu_id

SELECT stu_id,C_name,grade FROM score WHERE stu_id in(SELECT id FROM stu1 where name='李四')

/*12.计算每个学生的总成绩*/

SELECT grade_sum_t.grade_sum,grade_sum_t.stu_id,NAME,department FROM stu1,

(SELECT sum(grade)as grade_sum,stu_id from score GROUP BY stu_id )as grade_sum_t

where grade_sum_t.stu_id=stu1.id

/*13.计算每个考试科目的平均成绩*/

SELECT C_name,AVG(grade) from score GROUP BY C_name

/*14.查询计算机成绩低于 95 的学生信息*/

SELECT * FROM stu1 WHERE id IN(SELECT stu_id from score WHERE C_name='计算机' AND grade<95)

/*15.查询同时参加计算机和英语考试的学生的信息*/

SELECT * FROM stu1 WHERE id IN(

SELECT stu_id FROM score WHERE C_name='英语' AND stu_id in(

SELECT stu_id FROM score WHERE C_name='计算机'))

/*16.将计算机考试成绩按从高到低进行排序*/

/*18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩*/

SELECT Name,department,C_name,grade FROM stu1,score WHERE stu1.id=score.stu_id AND (Name LIKE '王%' OR Name LIKE '张%')

SELECT *from Score

/*19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩*/

SELECT YEAR(NOW())-YEAR(Brith) FROM stu1

SELECT Name,YEAR(NOW())-YEAR(Brith) as age,department,C_name,grade FROM stu1,score WHERE stu1.Id=score.stu_id AND address LIKE '湖南%'

/*20-*/

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '物理',80)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '化学',53)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '生物',59)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '物理',55)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '化学',56)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '生物',50)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '物理',100)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '化学',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '生物',54)

SELECT stu_id,AVG(grade) FROM score WHERE stu_id IN(SELECT stu_id FROM score1 WHERE grade<60 GROUP BY stu_id HAVING COUNT(stu_id)>=2)GROUP BY stu_id

SELECT *FROM score1

A.修改mysql默认端口

B.linux下可以通过iptables来限制访问mysql端口的IP地址

C.对所有用户设置较复杂密码并严格指定对应账号的访问IP（可在mysql库中user表中指定用户的访问可访问IP地址）

D.root特权账号的处理（建议给root账号设置强密码，并指定只允许本地登录）

E.开启二进制查询日志和慢查询日志

F.mysql安装目录及数据存储目录权限控制：给mysql安装目录读取权限，给mysql日志和数据所在目录读取和写入权限

G.删除无用mysql账号和删除无用的数据库（安装好的mysql默认会有个test库，可将其删除）如果有什么不懂的话可以去看看《Linux就该这么学》这本书，非常适合新手学习Linux。

USE test

DROP TABLE if EXISTS grade

    create table `grade` (  

       `id` int PRIMARY KEY,        

       `name` varchar (300),           

       `score` double                  

    )   

    

    insert into `grade` (`id`, `name`, `score`) values('1','n1','59') 

    insert into `grade` (`id`, `name`, `score`) values('2','n2','66') 

    insert into `grade` (`id`, `name`, `score`) values('3','n3','78') 

    insert into `grade` (`id`, `name`, `score`) values('4','n1','48') 

    insert into `grade` (`id`, `name`, `score`) values('5','n3','85') 

    insert into `grade` (`id`, `name`, `score`) values('6','n5','51') 

    insert into `grade` (`id`, `name`, `score`) values('7','n4','98') 

    insert into `grade` (`id`, `name`, `score`) values('8','n5','53') 

    insert into `grade` (`id`, `name`, `score`) values('9','n2','67') 

    insert into `grade` (`id`, `name`, `score`) values('10','n4','88')

首先将上面内容保存在一个名字叫grade.sql 的文件里面，上面我私自修改了id的类型，实在看不下去了。然后登陆数据库使用mysql>source C:/grade.sql；

select name,max(score) from grade

select name,sum(score)  from grade group by name order by score desc limit 1,5

select name,sum(score) as tot from grade  group by name having tot<150

select name,avg(score) as scavg from grade group by name having scavg <80 and scavg >60

select name from grade group by name having sum(score) >150 and avg(score) <90这个应该是查询人吧，6才是查询人数吧

亲测有效

---