数据库教程:MySQL查询——select

select col1, col2,...          # 业务查询的字段  from table_name            # 选取的哪张表  [where single_conditions]           # single_conditions条件表达式，个体约束（条件）  [[group by column_name1]      # column_name1以哪个字段名分组  [having group_conditions]]     # group_conditionds条件表达式，分组约束  [order by column_name2]      # column_name2以哪个字段进行排序  [limit n,m]                         # 执行完之后，跳过n条记录，选取m条记录

# 查看员工的职位  select title  from s_emp;    select distinct title   from s_emp;    # 每个部门下有哪些职位  select dept_id,title   from s_emp;    select distinct dept_id,title   from s_emp;# 联合唯一

# 查看员工年薪  select salary*12   from s_emp;  select id,last_name,salary*12 as year_salary   from s_emp;    # 查看员工当前年薪以及月薪提高100美元后的年薪  select id,last_name,salary*12 old_year_salary,(salary+100)*12 as new_year_salary  from s_emp;

# 查看员工基本信息（编号，全名，工资）  select id,concat(first_name,' ',last_name) as name,salary   from s_emp;

# 查看员工工资（没有工资的显示为0）  select id,last_name,ifnull(salary,0.00) as salary   from s_emp;

# 查看拥有白领工资的员工有哪些（1200，2000）  select id,last_name,salary   from s_emp   where salary>1200 and salary<2000;    # 查看有工资的员工信息  select id,last_name,salary   from s_emp   where salary is not null;    select id,last_name,salary   from s_emp  where salary <> null;# erro    # 查看没有工资的员工的信息  select id,last_name,salary   from s_emp   where salary is null;    select id,last_name,salary   from s_emp   where salary <=> null;    select id,last_name,salary   from s_emp   where salary != 1200;# null数据未取到

查看41号部门的员工信息并且工资大于1200或者43号部门工资小于2000的员工信息  select id,last_name,salary  from s_emp  where (dept_id=41 and salary>1200) or (dept_id=43 and salary<2000);

查看拥有白领工资的员工有哪些（1200，2000）  select id,last_name,salary from s_emp where salary>1200 and salary<2000;  # 下面是闭区间  select id,last_name,salary   from s_emp  where salary between 1199 and 2001;        #[1199,2001]    # 不在区间内  select id,last_name,salary   from s_emp   where salary not between 1200 and 2000;    #（-&，1200）or（2000，+&）

# 查看41，42，43号部门的员工有哪些  select id,last_name,salary   from s_emp  where dept_id=41 and dept_id=42 and dept_id=43;  # 和上面的等价  select id,last_name,salary   from s_emp  where dept_id in(41,42,43);    # 查看不是41，42，43号部门的员工有哪些  select id,last_name,salary   from s_emp  where dept_id!=41 and dept_id!=42 and dept_id!=43;  # 和上面的等价  select id,last_name,salary   from s_emp  where dept_id not in(41,42,43)

# 查看职位以vp开头的员工有哪些  select id,last_name,salary   from s_emp  where title like 'vp%';    select id,last_name,title   from s_emp  where title not like 'vp%';    # 查看员工信息，免费精选名字大全以c开头，并且字符数不小于5个字符  select id,last_name  from s_emp  where last_name like 'c____%';  #查看客户信息，客户名称中包含单引号的客户  select id,name from s_cutomer  where name like "%'%";

# 查看员工总数  select count(*) as count_num   from s_emp;   # 默认分组（以表格为单元）  select count(id) as count_num   from s_emp;    # 统计有工资的员工个数  select count(salary) as count_num   from s_emp;  # 查看每个部门的员工个数  select dept_id,count(*) as nums   from s_emp  group by dept_id;    # 进行分组后，select的结果只能是组的概念，不允许出现个体概念(last_name)  select dept_id,count(*) as nums,last_name   from s_emp  group by dept_id;   # errro    # 默认以逗号拼接 group_concat(),这个函数很重要  select dept_id,count(*) as nums,group_concat(last_name)   from s_emp  group by dept_id;    # 查看每个部门薪资大于1200的员工总数（信息）  select dept_id,count(*) nums,group_concat(last_name),group_concat(salary)  from s_emp  where salary > 1200  group by dept_id;    # 查看部门平均薪资  select avg(salary)   from s_emp   group by dept_id;    # 查看部门平均薪资>2000员工总数  select dept_id,count(*) nums,avg(salary)  from s_emp  group by dept_id  having avg(salary)>2000;    # 查看每个部门员工总数，部门平均薪资大于1000，并且每个员工的薪资>900  select dept_id,count(*),avg(salary)  from s_emp  where salary >900  group by dept_id  having avg(salary)>1000

# 查看员工的员工id，免费精选名字大全，月薪，部门id，部门id进行升序排序，相同部门的员工在一起按照薪资从高到低排序    select id,last_name,dept_id,salary  from s_emp  order by dept_id asc,salary desc;

# 跳过3条记录，查询5条记录，这一般用于分页比较合理  # 擦昏地当前页数和记录数  select id,last_name,dept_id,salary  from s_emp  order by dept_id asc,salary desc  limit 3, 5