目的:
学习总是需要记忆遗忘再记忆反反复复才能印入脑海。IT技术尤其如此。最好解决遗忘的方式,就是多多反复,然而捧起一本大部头从新看起显然效率低下。对于数据库入门级别的知识,看过一两遍,也就能理解主要机理了,欠缺在于代码练习。因此最近在重刷MySQL入门的同时,总结起来练习所使用的代码。用于以后的温习。某一天发现自己生疏了,花一点时间敲上一通,记忆就回来了。
说明:
1 大部分的语句都非常简单,进行了少量的注释;
2 代码稍有一些冗余,是为了熟练操作;
3 有意设置了一些错误,注意数据库的返回的信息;
4 所有测试在mysql上进行,但如果有错误,系手误或理解错误,望担待;
5 本文覆盖基本常用的查询;
——2017/7/31
select * from tt2; //tt2为表名
select all name from tt2; //all查询所有
select distinct name from tt2; //distinct去除重复
select count(*) from tt2; //统计表的行数
select count(name) from tt2; //统计name不为null的行数
select count(distinct name) from tt2; //返回不同的非null值的name数目
select * from tt2 limit 3; //取前3条记录 mysql中没有top关键字
select * from tt2 limit 2,4; //从第2条开始取4条
select id as 编号, name as 姓名 from tt2; //查询结果中列重命名
select id 编号 from tt2; // 查询结果中列重命名
select (score + singin) as col from tt2; //列运算
//where条件
select * from tt2 where id = 2;
select * from tt2 where id > 2;
select * from tt2 where id < 2;
select * from tt2 where id <>2; //不等于
select * from tt2 where id != 2; //不等于
select * from tt2 where id >= 2;
select * from tt2 where id <= 2;
select * from tt2 where id > 2 and id < 5; //and并且
select * from tt2 where id > 5 or sex = 1; //or或者
select * from tt2 where id in(1,3,5); //判断某个字段的值是否在指定的集合中
select * from tt2 where id not in(1,3,5); //不在指定的集合中
select * from tt2 where id between 2 and 5; //判断某个字段的值是否在指定的范围内,相当于并且
select * from tt2 where id not between 2 and 5; //不在指定范围之内
//like模糊匹配
select * from tt2 where name like '小_'; //下划线_代表一个未指定的字符
select * from tt2 where name not like '%明'; //百分号%代表一个或多个未指定的字符 %注意中英文不同,必须英文
//正则表达式
select * from tt2 where name regexp '^ma'; //查找name字段中以ma开头的所有数据
select * from tt2 where name regexp 'eo$'; //查找name字段中以eo结尾的所有数据
select * from tt2 where name regexp '^ma|eo$'; //查找name字段中以ma开头的所有数据开头或者以eo结尾的所有数据
select * from tt2 where name regexp 'le'; //查找name字段中包含le的所有数据
select * from tt2 where name regexp '^[aei]|ok$'; //查找name字段中以a或者e或者i开头的所有数据开头或者以eo结尾的所有数据
select * from tt2 where score is null;
select * from tt2 where score is not null;
select * from tt2 order by name; //order by排序
select * from tt2 order by name desc; //降序
select * from tt2 order by name asc; //升序
select count(age), age from tt2 group by age; //按照年龄进行分组统计
select count(*), sex from tt2 group by sex; //按照性别进行分组统计
select count(*), sex, age from tt2 group by sex, age order by age; //按照性别和年龄组合进行分组统计,最后的结果再按照年龄排序
select count(*), sex from tt2 where age > 16 group by sex; //在年龄大于16的记录中 按照性别进行分组统计 (where在分组之前先过滤数据)
select avg(score), age from tt2 group by age having age >= 18; //(having在分组之后过滤)
select count(*), age from tt2 group by age having count(age) >= 2; //按照年龄分组,过滤条件是分组后的记录条数大于等于2
//嵌套子查询
select *
from (
select id, name from tt1 where sex = 1
) tb
where tb.id > 2; //查询性别为1的人中id>2的人的信息
select *
from student
where cid in(
select id from classes where id > 2 and id < 4
); //查询班级id大于2小于4的这些班级的学生信息
select *, (select count(*) from student where cid = classes.id) as num
from classes
order by num; //查询班级信息,统计班级学生人数
select *
from tt1
where birth <= all(select birth from tt1); //查询出生最早的人的详细信息
//上句等价于:
select *
from tt1
where birth <= (select min(birth) from tt1);
//上句等价于:
select * from tt1 order by birth limit 1;
select *
from tt2
where id = any(select id from tt2 where sex = 1); //=any和in等效
//使用子查询进行多表查询
select name
from emp
where empNumber in
(select distinct empNumber
from assignment
where hoursworked > 50); // 查找在某个任务assignment中工作时间超过50个小时的所有雇员的姓名
//使用连接进行多表查询
select name, hoursworked
from emp join assignment
on emp.empNumber = assignment.empNumber
order by emp.empNumber;
待续