MySQL 使用DQL命令查询数据的实现方法

SELECT [ALL | DISTINCT]
{ * | table.* | [table.field1 [as alias1][,table.field2] [as alias2][,...]]}
FROM table_name [as table_alias]
[left | out | inner join table_name2] #联合查询
[WHERE ...]   #指定结果需要满足的条件
[GROUP BY ...]   #指定结果按照哪几个字段来分组
[HAVING ...]   #过滤分组的记录必须满足的次要条件
[ODER BY ...]   #指定查询记录按一个或者多个条件排序
[LIMIT [偏移量，返回结果记录数]; #指定查询的记录从哪条至哪条

select * from student;

select StudentName,Phone from student;

select StudentName as '学生姓名' from student;

select stu.address from student as stu;

SELECT Phone +1 AS Tel FROM student;

SELECT DISTINCT 字段名1，字段名2，... from 表名

SELECT DISTINCT StudentNo,address from student;

SELECT DISTINCT address from student;

SELECT VERSION(),100*3 as 乘积; #返回Mysql 版本和计算结果
 
SELECT NOW() '当前时间'; #返回当前时间

SELECT VERSION() as MySQL_V,12.3*100 as expression;
#返回结果不会与后台开发程序发生混淆

SELECT CONCAT(studentName,'@.com') as Email from student;

select StudentNo+100 from student;

#where 条件语句
select * from student where address='四川';
 
#查询学生表中电话号码不为空的学员姓名
select StudentName from student where Phone is not null;
 
#查询学生表中电话号码为空的学员姓名
select StudentName from student where Phone is null;
 
#查询刚删掉的数据——空值
select StudentName from student where Phone = '';
 
# BETWEEN AND 适用于时间范围

#查询学生表中姓张*的学生姓名
SELECT StudentName from student where studentname like '张_';
 
SELECT StudentName from student where studentname like '%丽%';
# IN
SELECT * from student where Address in ('四川','上海');

SELECT * from subject ORDER BY classhour; #升序
SELECT * from subject ORDER BY classhour desc; #降序

SELECT * from subject ORDER BY classHour,GradeID;

SELECT * FROM grade LIMIT 3; #返回前3条记录
SELECT * FROM grade LIMIT 1,3; #返回2~4条记录

SELECT COUNT(subjectNO) '总数据' from subject;

SELECT subjectName from subject
where gradeID IN
(SELECT GradeID from grade);

SELECT COUNT(StudentNo) '总数据' FROM student;
SELECT sum(classHour) '总学时'from subject;
SELECT AVG(classHour) '平均学时' FROM subject;
SELECT MAX(classHour) '最长学时' FROM subject;
SELECT MIN(classHour) '最短学时' FROM subject;

SELECT Address,COUNT(Address) FROM student GROUP BY Address;

SELECT GROUP_CONCAT(StudentName),COUNT(Address) FROM student GROUP BY Address HAVING COUNT(Address)=1;

SELECT * FROM subject UNION SELECT * FROM student;  
SELECT * FROM subject UNION ALL SELECT * FROM student;

SELECT subjectName,GradeID FROM subject
WHERE EXISTS 
(SELECT * FROM grade WHERE GradeID=1);

SELECT subjectName,GradeID FROM subject
WHERE NOT EXISTS 
(SELECT * FROM grade WHERE GradeID=999);

SELECT subjectName,GradeID FROM subject
WHERE EXISTS 
(SELECT * FROM grade WHERE GradeID=999);

SELECT subjectName,GradeID FROM subject
WHERE NOT EXISTS 
(SELECT * FROM grade WHERE GradeID=1);

SELECT subjectname,gradeid FROM subject 
WHERE GradeID >=ANY
(SELECT GradeID FROM grade);

SELECT subjectname,gradeid FROM subject 
WHERE GradeID >=ALL
(SELECT GradeID FROM grade);