MySQL----DQL

SELECT 要查询的列名称

FROM 表名

[WHERE 满足的行条件]

[GROUP BY 分组的依据]

[ORDER BY 排序的依据]

[LIMIT 限定输出结果];

 

-- 查询所有数据

select * from students;

 

-- 查询指定列

select sname,age,gender from students;

 

-- 查询一个或多个表中的数据

select students.sname,students.age,teachers.tname,teachers.age,teachers.tsubject from students,teachers;

 

 

 

 

 

-- in

select * from students where age in (18,19,20); #in 表示在集合中，not in 表示不在集合中

select * from students where sname not in ('张三','李四');

 

 

-- between and

select * from students where age between 12 and 18;

select * from students where age not between 12 and 18;

 

-- like

select * from students where sname like '小%';

select * from students where hobby like '羽_球';

 

-- is null

select * from students where phone is null;

select sname from students where hobby is not null;

 

-- and

select * from students where age>15 and address='China';

select * from students where sid>1 and sid=4;

 

-- or

select * from students where age>15 or address='China';

select * from students where sid=1 or sid=4;

 

-- distinct 去除查询结果中的重复行

select distinct * from students;

select distinct address from students;

 

-- order by 对查询结果排序

select * from students order by age asc; #默认按升序排列,asc 可省略

select * from students order by age desc; #加 desc 按降序排列

 

-- group by 查询结果只显示每组的一条记录

select sid,sname,age,phone from students group by age; #数据中两个年龄16岁的只显示了一个

select sid,sname,age,phone,group_concat(age) from students group by age; #加上group_concat()函数，可以显示所有数据记录

select sid,sname,age,address,group_concat(age,address) from students group by age,address;

 

-- limit 限定输出行数

select * from students order by age;

select * from students order by age limit 3; #取前三条数据

select * from students order by age limit 0,3; #从第1（编号从0开始）条数据开始查询3条

select * from students order by age limit 2,2; #从第3条数据开始查询2条

 

 

-- regexp 正则表达式

-- ^ 匹配以特定字符或字符串开头的记录

select * from students where sname regexp '^小';

-- $ 匹配以特定字符或字符串结尾的记录

select * from students where address regexp 'na$';

-- . 匹配字符串的任意一个字符，包括回车和换行符

select * from students where phone regexp '1.'; #当.在给定的字符后面，那么查询的结果不能以该字符结尾且包含该字符

select * from students where phone regexp '.1'; #当.在给定的字符前面，那么查询的结果不能以该字符开头且包含该字符

select * from students where phone regexp '.2345.'; #包含指定字符，不能以该字符开头或结尾

select * from students where phone regexp '345'; #包含指定字符

-- [字符集合]

select * from students where gender  regexp '[男]'; #匹配“字符集合”中的任意一个字符

select * from students where gender regexp '[^男]'; #匹配除“字符集合”以外的任意一个字符

-- S1|S2|S3 匹配S1 S2 S3中的任意一个字符串

select * from students where address regexp 'China|北京|西安';

-- * 匹配多个该符号之前的字符，包括0和1个

select * from students where phone regexp '6*';

select * from students where phone regexp '3*567';

select * from students where phone regexp '1*01';

-- + 匹配多个该符号之前的字符，最少一个，且必须紧挨着该字符

select * from students where phone regexp '2+34';

select * from students where phone regexp '234'; #用法和上一样

-- 字符串{N} 匹配字符串连续出现N次

select * from students where phone regexp '3{2}'; #注意：必须是连续出现

-- 字符串{M,N} 匹配字符串出现至少M次，最多N次

select * from students where phone regexp '1{1,4}';

 

 

 

 

 

 

-- 聚合函数查询

-- count()

select count(sname) from students; #不带*，返回非null值行的数目

select count(phone) from students;

select count(age),count(*) from students; #带*，返回所有行的数目

select age,count(*),avg(age) from students group by age; #统计各个年龄的人数

 

 

-- sum() 函数可以求出表中某个字段取值的总和

select sum(age) from students;

select sum(sid) from students;

select s.sname , c.english+c.chinese+c.math as total from student s,score c where s.sid = c.sid order by total desc; #求学生总成绩

 

-- avg() 函数可以求出表中某个字段取值的平均值

select avg(age) from students;

 

-- max() 函数可以求出表中某个字段取值的最大值

select max(age) from students;

 

-- min() 函数可以求出表中某个字段取值的最小值

select min(age) from students;

 

-- round() 保留几位小数

select round(avg(age),1) from students;

 

 

 

 

-- 内连接查询,

select s.sname,s.age,s.grade from students s inner join teachers t where s.grade  = t.grade;

select s.sname,s.age,s.grade from students s, teachers t where s.grade  = t.grade;

# 内连接inner join 可以省略，用逗号代替;

# 内连接关键字 where 可以用 on 替代，外连接只能用on

# 内外连接的条件语句可以加括号

 

 

-- 左外连接查询

select s.sname,t.tname,s.grade from students s left join teachers t on (s.grade = t.grade);

 

-- 右外连接查询

select s.sname,t.tname,t.grade from students s right join teachers t on s.grade = t.grade;

 

-- 复合条件连接查询

select s.sname,t.tname,s.grade from students s,teachers t where s.grade = t.grade and s.grade > 2;

 

 

 

 

-- 子查询

 

-- 带关键字in的子查询

select * from students where sid in(select tid from teachers order by tid);

select tid from teachers order by tid;

 

-- 带比较运算符的子查询

select * from students where grade = (select grade from teachers where tname = '潘老师');

select grade from teachers where tname = '潘老师';

 

-- 带关键字exists的子查询

select * from students where exists (select * from teachers where tid = 1);

select * from students where sid = 2 and exists (select * from teachers where tid = 1);

 

-- 带关键字any的子查询

select * from students where sid > any (select tid from teachers);

select tid from teachers;

 

-- 带关键字all的子查询

select * from students where sid > all (select tid from teachers where tid >3);

select tid from teachers where tid >3;

 

 

-- 合并查询结果

-- union 将所有的查询结果合并到一起，并且去除相同记录

select address from students union select address from teachers;

 

-- union all 只是简单地将结果合并到一起

select address from students union all select address from teachers;

 

 

-- 定义表和字段的别名

select * from students s where s.sid = 2;

select sname s,grade g from students;