Mysql学习笔记高级篇
一、查询数据-单表
1、单表查询指定字段
select field1,field2,…fieldn from table_name;
2、单表查询所有字段
select * from table_name;
3、避免重复数据查询
select distinct field from table_name;
4、数学四则运算数据查询
+、-、、/、%
依次表示加、减、乘、除、求余数
例如:
select name, sal12 from employee;
(注:employee雇员表中name和sal字段分别为雇员姓名,月薪)
5、带格式化查询
select concat(name, “的年薪为:”, sal12) as “薪资” from employee;
6、条件记录查询
select field from 表名 where 条件
1)条件关键字
a、between and范围条件查询
select name, sal from employee where sal between 1000 and 2000;
b、and/or多条件查询
select name, job from employee where job=“clerk” and sal > 800;
select name, job from employee where job=“clerk” or job = “salesman”;
c、带is null的条件查询
select name, comm from employee where comm is not null;
(注:comm为”奖金"字段)
d、带in/not in关键字条件查询
select name, job from employee where job in (“salesman”, “clerk”);
e、带like/not like关键字条件查询:
select name from employee where name like “%A%”;
7、限制记录查询
select field from 表名 where 条件 limit offset_start, row_count;
offset_start为起始偏移量(即跳过几条结果后显示), row_count表示显示几条结果行数
例如:
select * from employee where comm is null limit 2;
(查询comm为null的记录,只显示前两条数据)
select * from employee where comm is null limit 2, 2;
(查询comm为null的记录,跳过两条记录,显示第3,4条记录)
8、统计函数
**1)count():**统计表中记录条数
count():统计所有记录,包括为null,非