目录
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
主要记录一下上面几个的用法:
简单的表查询
~星号代表全部,或者指定字段查询
select * from t1;
select id,name from t1;
~where条件
select * from t1 where id=3
~concat格式化查询
1. concat 和 python 中的字符串拼接一个道理,并不难理解。
2. as 就是别名,这里是为了看的清楚,可以省略。
select concat('id:',id,' 姓名:',name) as info from t1;
执行结果:
+------------------------+
| info |
+------------------------+
| id:15 姓名:程咬金 |
| id:16 姓名:程咬银 |
| id:17 姓名:程咬铜 |
| id:18 姓名:程咬铁 |
+------------------------+
~去重复distinct
结果就不贴上来了,就是去除重复打印结果的而已。
select distinct name from t1;
~四则运算
不光是where可以使用四则运算,字段这用上,也可以改变打印结果,比如说把表中的月薪变成年薪。
select salary*12 from t1;
where约束
~简单用法:
select * from t1 where salary>3000 and salary<5000
select * from t1 where salary>3000 or salary<5000
select * from t1 where not salary>3000 and salary<5000
下面两条是等同,弄个元组,看他在不在里面
select * from t1 where id=1 or id=5 or id=7;
select * from ti where id in(1,5,7);
判断null记录的时候不能使用‘=’号,用is
select * from t1 where post_comment is null;
~between and
就是取一个区间, 这两条结果是相同的
select * from t1 where salary between 5000 and 8000;
select * from t1 where salary>5000 and salary<8000;
~like 模糊匹配
%代表匹配任意个字符
select * from t1 where name like 'jin%';
~regexp 正则表达式
select * from t1 where regexp '^jin'
_ 代表匹配一个字符
select * from t1 where name like 'jin_';
group by 分组
ONLY_FULL_GROUP_BY
#查看MySQL 5.7默认的sql_mode如下:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#!!!注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。
#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
!!!SQL_MODE设置!!!
需要注意的:
1, 规范如果有where的情况下,要写到where后面。
2. 分组了哪个字段就只能查看那个字段,或者聚合函数,或者concat。
1~简单操作
select post from t1 group by post;
~group_concat 因为group by 只能查看分组的字段,如果想查看对应的其他字段就可以使用group_concat实现
mysql> select post from t1 group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| teacher |
| sale |
| operation |
+-----------------------------------------+
mysql> select post,group_concat(name) from t1 group by post;
+-----------------------------------------+---------------------------------------------------------+
| post | group_concat(name) |
+-----------------------------------------+---------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | |
| sale | 丫丫,格格,星星,丁丁,歪歪 | |
| teacher | 成龙,jinxin | |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)
having 在group by 后的分组信息进行过滤
案例:分组职位,过滤出平均工资大于10000的部门
mysql> select post,avg(salary),group_concat(name) from t1 group by post having avg(salary)>10000;
+-----------+---------------+---------------------------------------------------------+
| post | avg(salary) | group_concat(name) |
+-----------+---------------+---------------------------------------------------------+
| operation | 16800.026000 | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 151842.901429 | 成龙,jinxin,jingliyang |
+-----------+---------------+---------------------------------------------------------+
order by (默认asc升序,desc降序)
案例:指定age字段来定制排序规则
select * from t1 order by age asc;
select * from t1 order by age desc;
案例:首先按照age开始排序,如果其中记录age一样大,就按照id来排序
select * from t1 order by age desc,id desc;
limit 限制条数
案例:借用上面order的,就是结果只打印出前5条。
select * from t1 order by age desc limit 5;
案例:可以选择从某个位置指定打印几条(起始位置,步长) ,并不是区间,不要理解错了。
mysql> select id,name from t1 limit 0,2;
+----+------+
| id | name |
+----+------+
| 1 | egon |
| 2 | alex |
+----+------+
2 rows in set (0.00 sec)
mysql> select id,name from t1 limit 2,2;
+----+---------+
| id | name |
+----+---------+
| 3 | wupeiqi |
| 4 | yuanhao |
+----+---------+
2 rows in set (0.01 sec)
聚合函数
1~count统计指定字段个数总查到多少条
2~sum求和,这个就比较好理解。
3~max和min,分别是打印出最大值和最小值
4~ avg ,平均值
select count(salary) from t1 ;
select sum(salary) from t1;
select max(id) from t1;
select min(id) from t1;
select avg(salary) from t1;