MY-SQL-查询

MY-SQL-查询

查询

– 1、查询tb_emp表的name,entry达特字段

select name,entrydate from tb_emp;

– 2、查询返回所有字段

– 性能高

select id,username,password,name,gender,image,job,

entrydate,create_time,update_time from tb_emp;

– 性能低，不直观

– select * from tb_emp;

– 3、查询所有员工的name，entrydate，并起别名（姓名，入职日期

– select name 姓名,entrydate 入职日期 from tb_emp;

– 4、查询已有的员工关联了哪几种职位（不要重复

– select distinct job from tb_emp;

– 1、查询姓名为杨逍的员工

– select * from tb_emp where name=‘杨逍’;

– 2、查询id小等于5的员工信息

– select * from tb_emp where id<=5;

– 3、查询没有分配职位的员工信息

– select * from tb_emp where job is null;

– 4、查询有职位的员工信息

– select * from tb_emp where job is not null;

– 5、查询密码不等于’123456’的员工信息

– select * from tb_emp where password != ‘123456’;

---

– 6、查询入职日期在’2000-01-01’包含到’2010-01-01’(包含）之间的员工信息

– select * from tb_emp where entrydate >= ‘2000-01-01’ and entrydate <= ‘2010-01-01’

---

– select * from tb_emp where entrydate between ‘2000-01-01’ and ‘2010-01-01’;

---

– 7、查询入职时间在’2000-01-01’（包含）到’2010-01-01’(包含)之间且性别为女的员工

– select * from tb_emp where entrydate between ‘2000-01-01’ and ‘2010-01-01’ and gender=2;

---

– 8、查询职位是2（讲师），3（学工主管），4（教研主管）的员工信息 or:或者

– select * from tb_emp where job=2 or job=3 or job=4;

---

– in(…):在in之后的列表中的值，多选一

– select * from tb_emp where job in (2,3,4);

---

– 9、查询姓名为两个字的员工信息 _:匹配单个字符

– select * from tb_emp where name like’__';

---

– 10、查询姓张的员工信息 %:匹配任意字符

– select * from tb_emp where name like ‘张%’;