SQL练习

创建数据库：create database haha character set uutf8 collate utf8_general_ci;

对数据表的操作：

1. 创建表结构（框架）并设置唯一主键，主键按照升序进行，设置不可为空，年龄采用默认格式为utf8：create table if not exists students(sid int primary key auto_increment,name varchar(20) not null,age int default 20,sex varchar(4) default '男')charset=utf8;
2. 主键的设置方式：三种，一种如上，一种 单独列出来primary key（sid），第三种alter table 表名primary key（sid）
3. 创建简单无约束的 表：create table score (name  text ,obj text,score float)charset=utf8;
4. 向表里添加信息填充：insert into students(name,age,sex)values('魔天',200,'男'),('败天',1000,' 男'),
5. 查看表的信息：select * from students;
6. 增加一个列，并设置默认信息的数据为20：alter table chinese add age integer default 20;
7. 删除表中的信息：delete from students where name='大魔';
8. 查看表中年龄大于200的信息，：select *from student where age>200;
9. 查看表中年龄不等于18 岁的：select * from students where age <> 18;
10. 查询年龄的一个连续区间（200-1000）：select * from students where age between 200 and 1000;
11. 查询年龄不在这个连续区间的用户信息（200-1000）：select * from students where age  not between 200 and 1000;
12. 查询年龄在这个集合的用户信息：select * from students where age in (200,1000,2000);
13. 查询按年龄降序排列的用户信息：select * from students order by age;
14. 查询按年龄升序排列的用户信息：select * from students order by age desc;
15. 查询年龄在区间之中且这个区间按照顺序排列：select * from students where sid between 5 and 10 order by age（desc）;
16. 复制表以及信息，然信息约束取消：create table student2 select * from students;
17. 复制表框架以及信息，信息的约束依旧在: create table math like chinese;
18. 查询姓名中含有一个确定字符的用户信息：select * from student2 where name like '%魔%';
19. 翻页设置：select * from student2 limit 0,3;0代表默认开始条数第0条开始，3，表示从第0条往下数3条信息；
20. 查询表中所有的年龄的平均值：select avg(age)（as 显示数据类型的信息） from student2;
21. 查询表中年龄最大的年龄单属性：select max(age) from student2;
22. 查询表中年龄最大的用户全部信息：select * from student2 where age=(select max(age) from student2);
23. 查询表中年龄小于平均值的用户信息：select * from student2 where age<(select avg(age) from student2);
24. 查询表现有的行数或者记录条数：select count(*)as '记录条数' from student2;
25. 将现查询的表的名字后面拼接内容select concat(name,1801) as '新名字'from student2;
26. 查询建表时间，现在时间，md5 加密：select curdate() ;   select curtime();   select md5('123');
27. 根据表中sid的标识符修改表中相对应的年龄的数据：update students set age=118 where sid=12;
28. 查询分组之后的分数：select avg(score) as '平均成绩' from score group by name;
29. 查询分组之后的分数并显示学科名称：select name,avg(score) as '平均成绩' from score group by name;
30. 查询平均分数以及总成绩：select name,avg(score) as '平均成绩' ,sum(score) as '总成绩' from score group by name;
31. 按照姓名进行分组查询平均分数以及平均分数大于70分的学生：select name, avg(score) from score group by name having avg (score)>70;
32. 按照姓名进行分组查询平均分数以及平均分数大于70分的学生或者总分在180分以上的学生姓名：select name, avg(score) ,sum(score) from score group by name having avg (score)>70 or sum(score)>180;
33. 查询学生的分数并按照顺序进行排列：select name,sum(score) from score group by name order by sum(score)（desc）;
34. 查询语文大于70 分的学生的所有信息：select *from score where name in (select name  from score where obj='语文'and score>70);先求出大于70的语文成绩的学生姓名集合求出来， 然后搜索这个集合匹配信息
35. 查询表中语文大于70数学大于70英语大于70的人的所有信息：select * from score where name in(select name from score where score > 70 and obj='语文') and name in (select name from score where score > 70 and obj='数学');
36. 查询语文成绩大于70分的学生的：select * from score where name in(select name from score where score > (select avg(score) from score where obj='语文') and obj='语文');
37. 查询语文成绩大于平均分的学生的信息select * from score where name in(select name from score where score > (select avg(score) from score where obj='语文') and obj='语文')and name in(select name from score where score > (select avg(score) from score where obj='数学') and obj='数学')and name in(select name from score where score > (select avg(score) from score where obj='英语') and obj='英语');
38. 查询两个表中的交集信息（两个表）：select * from chinese left join math on chinese and math;以左边为模板，右边如果没有，平行位置为null；
39. 查询两个表中的交集信息（一个表）：（语文表中姓名，语文表中学科，分数数学表中学科，分数）select chinese.name,chinese.obj,chinese.score,math.obj,math.score from chinese left join math on chinese.name=math.name;（以左边为模板，输出两个表中name相同的数据并显示在同一行）
40. 查询两个表中学生交集之外名称不相同的学生信息：(select * from chinese left join math on chinese.name = math.name where math.name is null) union (select * from chinese right join math on chinese.name = math.name where chinese.name is null);
41. 外键：有外键的即为从表，一个表一个主键n多个外键，外键作用，保证数据的完整性，练习另一个表，外键也一定是主表的主键 ，目前只有mysql引擎为InnoDB时才支持外键，外键的约束条目：district：当从表中有外键数据和主表关联主表中该条记录就不能删除或者更新；cascade：级联操作，当主表中删除或者更新了某条记录从表中与该条记录有关的记录也发生相应的更改；set null：当主表记录被删除或者更改，从表相关记录的外键设置为null；no action 和district功能一样；外键的约束模式：主表记录的删除和更新
42. 索引：当我们表中某个字段经常作为查询的条件（where后面），并且表中有大量的数据，该表经常进行查询操作，这时我们就可以将该字段设置为索引，提高查询效率。设置索引之后会降低，增删改的效率，索引一般有普通索引，唯一索引，联合索引，方式 btree、fulltext（mysql有些版本不支持）