select
{* | <字段列表>}
{
form <表1>,<表2>...
[where <表达式>]
[group by <grop by definition>]
[having <exception> [{<option> <expression>}...]]
[order by <order by definition>]
[limit [<offset>,] <row count>]
}
select [字段1,字段2...] from [表或视图] where [查询条件];
查找’t‘字母开头的员工姓名:
select id,name from tb_emp_2 where name like 't%';
查找包含’t‘的员工姓名:
select id,name from tb_emp_2 where name like '%t%';
查询以’t‘开头,并以’g‘结尾的员工姓名:
select id,name from tb_emp_2 where name like 't%g';
‘_’通配符,一次只能匹配任意一个字符
1
2
查询以’e‘结尾,并且前面包含四个字符的员工姓名:
select id,name from tb_emp_2 where name like '____e';
IS NULL 查询空值
1
select id,name from tb_emp_2 where name is null;
带AND的多条件查询
1
2
3
4
MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回,可以用AND连接多个条件,多个条件见用AND分开
例如:查询名字中包含't'并且年龄等于18岁的员工
select id,name,age from tb_emp_2 where name like '%t%' and age = 18;
带OR的多条件查询
1
2
3
4
在WHERE声明中使用OR操作符,表示只需要满足其中一个条件的记录即可返回。OR可以用来连接多个查询条件。
例如:查询姓名中包含’t‘或'o'且年龄等于18的员工
select id,name,age from tb_emp_2 where name like '%t%' or '%o%' and age = 18;
注意: and的优先级高于OR
DISTINCT查询结果不重复
1
2
如,查询表中所有name不重复的数据:
select id,distinct name from tb_emp_2;
1. 单列排序(默认按字母顺序排列)
select name from tb_emp_2 order by name;
2. 多列排序(哪个字段在前先依照哪个排序)
select age,name from tb_emp_2 order by age,name;
3. 指定排序方向
select name,age from tb_emp_2 order by age desc,name asc;
**注意:MySQL排序的时候默认按照asc升序排序,且第二个字段仅仅是在第一个字段的基础上排序
**如下列情况:
mysql> select age,name from tb_emp_2 order by age,name asc;
+------+--------+
| age | name |
+------+--------+
| 12 | tu |
| 13 | apple |
| 18 | orange |
+------+--------+
mysql> select age,name from tb_emp_2 order by age,name desc;
+------+--------+
| age | name |
+------+--------+
| 12 | tu |
| 13 | apple |
| 18 | orange |
+------+--------+
mysql> select age,name from tb_emp_2 order by age desc,name desc;
+------+--------+
| age | name |
+------+--------+
| 18 | orange |
| 13 | apple |
| 12 | tu |
+------+--------+
前两个结果一样,因为都只是给name字段后加了条件,而不加条件的age字段则默认按圣墟排列,所以无论name字段后加什么排序结果都是一样的。
想要多多列都进行降序排列,要在每个字段后都加desc
GROUP BY 分组查询
1
语法:[group by 字段] [having <条件表达式>]
创建分组
1
2
3
4
5
例如:按照password排序,并记录其相同值的数量
select password count(*) from tb_emp_2 group by password;
例如:查看这些分组的password分别对应哪些name
select password,group_concat(name) from tb_emp_2 group by password;
使用HAVING过滤分组
1
2
3
group by可以和having一起限定表中的数据进行分组,只有满足条件的分组才能被显示出来。
如:按id排序,并查询名字中包含't'的客户
select id,name from tb_emp_2 group by id having name like '%t%';
where和having的区别: having 是在数据分组之后进行过滤来选择分组的,而where是在分组之前用来选择记录的。另外where排除的记录不再包含在分组中
使用group by 进行多字段分组:
如:
mysql> select f_id,f_name from tb_fruits group by f_id,f_name;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | apple |
| 2 | banana |
| 2 | orange |
| 3 | apple |
| 3 | banana |
| 4 | grape |
+------+--------+
但是:
mysql> select * from tb_fruits group by f_id,f_name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1.tb_fruits.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
所以:
使用group by 进行多字段分组,要注意你分组的字段在select后指定,
原因:
SQL的GROUP By的语法:
SELECT 选取分组中的列+聚合函数 from 表名称 GROUP BY 分组的列
所以是先有分组,再有检索的列,检索的列只能在分组中的列中选
综上:
就是:GROUP BY后的字段是先确定的,而SELECT后的字段是从GROUP BY分组中选的。
即:GROUP BY后面有的字段,SELECT后面才允许有;反之没有的SELECT也不能有
mysql> select f_id,f_name from tb_fruits group by f_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1.tb_fruits.f_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select f_id from tb_fruits group by f_id,f_name;
+------+
| f_id |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
+------+
mysql> select f_id,f_name from tb_fruits group by f_id,f_name;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | apple |
| 2 | banana |
| 2 | orange |
| 3 | apple |
| 3 | banana |
| 4 | grape |
+------+--------+
1. 带ANY、SOME的子查询:
select num1 from tb_1 where num1 > any(select num2 from tb_2);
表示如果tb_2中的num2和tb_1中的num1比较,只要大于num2列的任意一个值即可满足
2. 带ALL的子查询
select num1 from tb_1 where num1 > all(select num2 from tb_2);
需要num1大于num2列的所有值才满足条件
3. 带EXISTS的子查询
select * from tb_fruits where exists(select o_num from tb_orders where o_num = 3);
只要tb_orders中存在o_num = 3的数据,子查询就返回true,那么就会进行外层的查询
4. 带IN关键字的查询
select
插入语句
INSERT INTO table_name (column_list) VALUES(value_list)
更新语句
UPDATE table_name SET column_name = value WHERE (condition)