sql练习
1.写出创建上面这两张表的sql
CREATE TABLE score(
id int(10) NOT NULL AUTO_INCREMENT,
stuId int(10) NOT NULL,
grade int(10) DEFAULT NULL,
c_name varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
CREATE TABLE students (
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
age int(10) DEFAULT NULL,
sex varchar(4) DEFAULT NULL,
class VARCHAR(20) NOT NULL,
address varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
2. 使用while循环和repeat循环写各两个存储过程，传入一个行数，控制插入多少条数据，往第一题创建的两个表中各插入500条数据
delimiter $$;
CREATE PROCEDURE myproc()
BEGIN
Declare i int;
Set i =1;
While i<=500 do
insert into students(name,sex,age,class,address) values('lgqest', '男','21', '路飞', '广东省');
set i=i+1;
end while;
End
$$;
delimiter;
Call myproc();
createprocedurescorepc()
begin
declarei int;
seti = 1;
repeat
insert into score(stuid,c_name,grade) values(i,'计算机','98');
seti = i + 1;
until i >500
endrepeat;
end;
call scorepc()
3.写insert语句插入两个表的数据
（1）
insert into students(id,name,sex,age,class,address) values(801,'张三', '男','21', '乔巴', '北京市海淀区');
insert into students(id,name,sex,age,class,address) values(802,'周星星', '男','18','乔巴', '北京市昌平区');
insert into students(id,name,sex,age,class,address) values(803,'味道', '男','26','路飞', '湖南省永州市');
insert into students(id,name,sex,age,class,address) values(804,'孙杨', '女','21','乔巴', '辽宁省阜新市');
insert into students(id,name,sex,age,class,address) values(805,'李佳家', '女','22','超人', '福建省厦门市');
insert into students(id,name,sex,age,class,address) values(806,'保总才', '女','30','乔巴', '湖南省衡阳市');
insert into students(id,name,sex,age,class,address) values(1001,'徐振宁', '男','21','索隆', '辽宁省阜新市');
insert into students(id,name,sex,age,class,address) values(1002,'李小强', '男', '18','索隆', '福建省厦门市');
insert into students(id,name,sex,age,class,address) values(1003,'狄任杰', '男','26','蜘蛛侠', '湖南省衡阳市');
insert into students(id,name,sex,age,class,address) values(1004,'女屌丝', '女', '21','蜘蛛侠', '北京市海淀区');
insert into students(id,name,sex,age,class,address) values(1005, '郁金香', '女','22','索隆', '北京市昌平区');
insert into students(id,name,sex,age,class,address) values(1006,'裴飞菲', '女', '30','索隆', '辽宁省阜新市');
insert into students(id,name,sex,age,class,address) values(1007,'李小龙', '男','50','索隆', '福建省厦门市');
（2）insert into score(stuid,c_name,grade) values(801,'计算机','98');
insert into score(stuid,c_name,grade) values(801,'英语','80');
insert into score(stuid,c_name,grade) values(802,'计算机','65');
insert into score(stuid,c_name,grade) values(802,'中文','88');
insert into score(stuid,c_name,grade) values(803,'中文','95');
insert into score(stuid,c_name,grade) values(804,'计算机','70');
insert into score(stuid,c_name,grade) values(804,'英语','92');
insert into score(stuid,c_name,grade) values(805,'英语','94');
insert into score(stuid,c_name,grade) values(806,'计算机','57');
insert into score(stuid,c_name,grade) values(806,'英语','45');
insert into score(stuid,c_name,grade) values(1001,'计算机','98');
insert into score(stuid,c_name,grade) values(1007,'英语','80');
insert into score(stuid,c_name,grade) values(1002,'计算机','65');
insert into score(stuid,c_name,grade) values(1002,'中文','88');
insert into score(stuid,c_name,grade) values(1003,'中文','95');
insert into score(stuid,c_name,grade) values(1004,'计算机','70');
insert into score(stuid,c_name,grade) values(1004,'英语','92');
insert into score(stuid,c_name,grade) values(1005,'计算机英语','94');
insert into score(stuid,c_name,grade) values(1006,'英语','57');
insert into score(stuid,c_name,grade) values(1006,'英语','45');
4.查询students表的所有记录
select * from students;
5.查询students表的第2条到4条记录
select * from students order by id asc limit 4;
6.从students表查询所有学生的学号（id）、姓名（name）和班级（class）的信息
Select id,name,class from students;
7.从students表中查询乔巴和索隆的学生的信息
Select * from students where class='乔巴' or class ='索隆';
8.从students表中查询年龄18~25岁的学生信息
Select * from students where age between 18 and 25;
9.从students表中查询每个班有多少人
Select class,count(id) from students group by class;
10.从score表中查询每个科目的最高分
Select c_name,max(grade) from score group by c_name;
11.查询女屌丝的考试科目（c_name）和考试成绩（grade）
Select c_name,grade from score where stuid = (select id from student where name='女屌丝');
12.用4种多表连接的方式查询所有学生的信息和考试信息（左连接、右连接、内连接、=号连接）
(1)Select * from students left join score on students.id=score.stuid;
(2)Select * from students right join score on students.id=score.stuid;
(3) Select * from students inner joinscore on students.id=score.stuid;
Select * from students a,score b where a.id=b.stuid;
13.计算每个学生的总成绩
Select students.name,sum(grade) from students inner join score on students.id=score.stuid GROUP BY students.id;
14.计算每个考试科目的平均成绩
select c_name,avg(grade) from score GROUP BY c_name;
15.查询计算机成绩低于95的学生信息
Select * from students inner join score on students.id=score.stuid where score.c_name='计算机' and score.grade<95;
16.查询同时参加计算机和英语考试的学生的信息
Select * from students inner join score on students.id=score.stuid where c_name='计算机' and stuid in (select stuid from score where c_name='英语');
17.将计算机考试成绩按从高到低进行排序
Select * from students inner join score on students.id=score.stuid where c_name='计算机' ORDER BY grade DESC;
18.从student表和score表中查询出学生的学号，然后合并查询结果
select id FROM students UNION ALL select stuid from score;
select students.id,score.stuId from students inner JOIN score on students.id=score.stuid;
19.查询索隆班姓李的男同学的成绩和学生信息
select * from students inner JOIN score on students.id=score.stuid where students.name like '李%' and sex='男' AND students.class='索隆';
20.查询都是湖南的学生的姓名、年龄、班级和考试科目及成绩
select students.name,students.age,students.class,score.c_name,score.grade from students inner JOIN score on students.id=score.stuid where students.address like '%湖南%';
21.把总成绩小于100的学生名称修改为白胡子
select * from score GROUP BY stuid HAVING sum(grade)<100;
update students set students.name='天才' where students.id in (select score.stuId from score GROUP BY stuid HAVING sum(grade)<100);
22.查询只学过一门课的学生信息
select * from students where students.id in(select stuId from score GROUP BY stuid HAVING count(stuid)<2);
23.查出有多少个年龄一样的学生、把已有年龄全部分组 然后统计
select age,count(age) from students GROUP BY age;
24.索引是什么，如何创建索引，为什么要使用索引？写自己的理解
(1)索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
(2)
|
–直接创建索引 |
|
|
|
|
CREATEINDEXindex_name ONtable(column(length)) |
|
|
|
–修改表结构的方式添加索引 |
|
|
|
ALTERTABLEtable_name ADDINDEXindex_name ON(column(length)) |
|
|
|
–创建表的时候同时创建索引 |
|
|
|
CREATETABLE`table` ( |
|
|
|
`id` int(11) NOTNULLAUTO_INCREMENT , |
|
|
|
`title` char(255) CHARACTERSETutf8 COLLATEutf8_general_ci NOTNULL, |
|
|
|
`content` text CHARACTERSETutf8 COLLATEutf8_general_ci NULL, |
|
|
|
`time` int(10) NULLDEFAULTNULL, |
|
|
|
PRIMARYKEY(`id`), |
|
|
|
INDEXindex_name (title(length)) |
|
|
|
) |
|
|
|
–删除索引 |
|
|
|
DROPINDEXindex_name ONtable |
(3) 如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢
25.创建一个视图，要求显示总成绩大于160分的的学生的班级、课程名称、分数、学号、学生姓名、学生性别。
select sum(grade),students.class,students.id,students.name,students.sex,score.c_name from score INNER JOIN students ON score.stuid=students.id GROUP BY stuid HAVING sum(grade)<160;
