MySQL单表查询

格式：
SELECT col_name1, col_name2, .... FROM tb_name [select_statement]

简单查询
[root@client ~]# mysql -uroot -p < jiaowu.sql
mysql> select * from tutors;
mysql> select Tname,Age from tutors;
mysql> select Tname as 教师,Age as 年龄 from tutors; //给字段设置别名
mysql> select sql_no_cache * from new1; //不使用cache直接取数据
mysql> reset query cache; //清空query cache后取数据

避免重复查询
mysql> select distinct * from tutors; //去掉完全重复的行
mysql> select distinct Age from tutors; //查询有哪些年龄

通过条件查询
where condition
数字操作符：
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于

大于
= 大于等于
between 5 and 10 在两个值之间
逻辑操作符：
and
or
and和or并存时and的优先级高
包含和不包含：
in
not in
空值或者非空值：
is null 空值
is not null 非空值

示例：
mysql> SELECT * FROM tutors WHERE Age > 80;
mysql> SELECT * FROM tutors WHERE Age >= 70 AND Age <= 80;
mysql> SELECT * FROM tutors WHERE Age BETWEEN 70 AND 80;
mysql> SELECT * FROM tutors WHERE Tname = "OuYangfeng" OR Tname = "YiDeng" OR Tname = "HuYidao";

使用in查询的优点：
1.in的语法更加直观
2.in的计算次序更容易管理(操作符少)
3.in 一般比or执行的更快
4.in的最大优点可以包含其他子句 or不行
mysql> select * from tutors where Tname in ("OuYangfeng","YiDeng","HuYidao");
mysql> select * from tutors where Tname not in ("OuYangfeng","YiDeng","HuYidao");

mysql> select * from tutors where TID=6 or TID=7 and age > 60;
mysql> select * from tutors where ( TID=6 or TID=7 ) and age > 60;

mysql> insert into tutors values(10,'Alice','F',null);
mysql> select * from tutors where Age is null;
mysql> select * from tutors where Age is not null;

模糊查询
1、LIKE "通配符表达式"
通配符’%’匹配多个字符
通配符’_’匹配一个字符

使用通配符的原则:
尽量少使用通配符,如果其他操作符能做到就不要使用通配符
在确实需要通配符时,尽量不要使用%erry 这种用法,搜索起来会更慢 ，可以将%放到后面
至于使用位置,使用错了得不到想要的结果

mysql> SELECT * FROM tutors WHERE Tname LIKE "%ang%";
mysql> SELECT * FROM tutors WHERE Tname LIKE "Y%" OR Tname LIKE "H%";
mysql> SELECT * FROM tutors WHERE Tname LIKE "Y%" OR Tname LIKE "H_____";

2、rlike正则表达式
mysql> SELECT * FROM tutors WHERE Tname RLIKE "^[HY]";
mysql> SELECT * FROM tutors WHERE Tname rlike "ao$";

按指定的字段内容排序
ORDER BY <col_name> [ASC|DESC] DESC|ASC：降序|升序，默认

按单列排序
mysql> SELECT * FROM tutors ORDER BY Age;
mysql> SELECT * FROM tutors ORDER BY Age DESC;

按多列排序
按照年龄排序降序，年龄相同按照TID降序
mysql> select * from new1 order by age desc,TID desc;

限制查询的记录数
LIMIT [n,]m 忽略前n行，共显示m行

mysql> SELECT * FROM tutors LIMIT 3;
mysql> SELECT * FROM tutors LIMIT 2,3; //从第三个开始取，取三个
mysql> SELECT * FROM tutors ORDER BY Age LIMIT 1;
mysql> SELECT * FROM tutors WHERE Gender="M" ORDER BY Age DESC LIMIT 1;
//性别为男，年龄最大的

最大年龄有多个的情况
mysql> insert into tutors values(10,'LingLing','F',93);
mysql> SELECT * FROM tutors ORDER BY Age desc LIMIT 1;
mysql> select * from tutors where Age=(SELECT Age FROM tutors ORDER BY Age desc LIMIT 1);

年龄最大的前三个，显示用户的详细信息
mysql> select * from tutors where Age in (select t . Age from (select * from tutors order by Age desc limit 3 ) as t) order by Age desc;

使用集合函数查询
函数：可以实现某些特定功能的代码

concat连接字符串
mysql> create table mail(name char(10),domain varchar(20));
mysql> insert into mail values('alice','uplooking.com');
mysql> insert into mail values('tom','uplooking.com');

mysql> select concat(name,'@',domain) from mail;
mysql> select concat(name,'@',domain) as email from mail;

upper、lower大小写转换
mysql> select upper(name) as new_name from mail;
mysql> select lower(upper(name)) from mail;

sum求和、avg求平均数、max求最大值、min求最小值、count统计总数
mysql> create table emp(id int,name char(10),salary float(10,2),did int);
mysql> insert into emp values(1,'alice',5000,100),(2,'jerry',6000,100);
mysql> insert into emp values(3,'tom',10000,200),(4,'john',20000,200);
mysql> insert into emp values(5,'robin',3000,300),(6,'rose',2000,300);
mysql> insert into emp values(7,'jean',8000,300),(8,'uuu',30000,300);

mysql> select sum(salary) from emp;
mysql> select avg(salary) from emp;
mysql> select max(salary) from emp;
mysql> select min(salary) from emp;
mysql> select count() as count from emp;
mysql> select count() from emp where did=100;

通过四则运算查询 （不支持+=）
mysql> select name,salary12 from emp;
mysql> select name,salary12 as annual_salary from emp;
mysql> select name,(salary+1000)*12 as annual_salary from emp where name='rose';

year、month、day、hour、minute、second
mysql> create table time(t_timestamp timestamp);
mysql> insert into time values(null);

mysql> select year(t_timestamp) from time;
mysql> select month(t_timestamp) from time;
mysql> select day(t_timestamp) from time;
mysql> select hour(t_timestamp) from time;
mysql> select minute(t_timestamp) from time;
mysql> select second(t_timestamp) from time;

group by 分组函数
注：group by必须在where之后，分组前过滤；having的用法和where相同，但可以分组后过滤，一般不使用
mysql> select count() from emp group by did; //显示每个部门有多少人
mysql> select count() as num from emp group by did having num >3; //取出部门人数超过三个的

mysql> select count(*) as num from emp where salary < 10000 group by did having num >2;

GROUP BY关键字和连接字符串的GROUP_CONCAT()函数一起使用，查看每个部门的员工
mysql> select did,group_concat(name) as emp_members from emp group by did；
GROUP BY与COUNT() 集合函数一起使用，统计每个部门有多少个人
mysql> select did,count(id) as number from emp group by did;

========================================================
练习：
1、求scores表中所有人的成绩的总和
2、求scores表中SID相同的平均值
3、求scores表中SID相同的平均值，且只显示平均值大于68的