MySql中一些常见的查询语句

一、单表查询

1. 简单单表查询

   select *from member; //全部查询

   select user_name from member; //查询某一列

   select user_name,integral from member; //查询某两列

   select user_name.integral from member where integral>200;//查询integral中大于200的数据

   select user_name.integral from member where integral>=200;//查询integral中大于等于200的数据

   select user_name,integral from member where integral>=200 and integral <=1000000;//查询integral这一列中integral大于200小于100000的数据

   select user_name,integral from member WHERE integral BETWEEN 200 and 1000000;//查询integral这一列中integral大于200小于100000的数据

   注意：
   SELECT name,description,s_id from category where description>200 and s_id = 2 or s_id = 3;
   这条语句的执行顺序其实是
   SELECT name,description,s_id from category where (description>200 and s_id = 2) or s_id = 3;

   SELECT name,description,s_id from category where description>200 and( s_id = 2 or s_id = 3);
   在这里插入图片描述
   
   
   SELECT name,description,s_id from category where description is NULL; //查询description为null的数据，这里不能用SELECT name,description,s_id from category where description = NULL; 因为null不是指具体的数字。

   SELECT name,description,s_id from category where description is not NULL ; //查询description不为null的数据

2. 模糊查询

   attention：%代表通配符 ，_代表占位符
   SELECT name,description,s_id from category where name like ‘%w%’;// 查询名字中含有w的数据，

   SELECT name,description,s_id from category where name like ‘w%’; //查寻名字中首字母为w的数据

   SELECT name,description,s_id from category where name like ‘_w%’; //查询第二个字母为w的数据

   SELECT name,description,s_id from category where name like ‘%w_’;//查询倒数第二个字母为w的数据

3. 排序

   SELECT name,description,s_id from category ORDER BY name; //将查询到的数据进行排序

4. 多表查询
   多表查询中的笛卡尔积现象：若两张表或多张表连接查询的时候没有条件限制，最终查询结果是两张表或多张表查询的乘积，这种现象叫做笛卡尔积现象。

   select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;//查寻salgrade和emp表中的名字，薪水，薪水等级。

   内连接把一张表看做两张表用
   select a.ename,b.ename as leadername from emp a join emp b on a.mgr = b.empno; //一个表用了两次，且b.ename as leadername的意思是将ename的名字改为leadername。

   多表查询
   select d.dname , e.ename , b.ename as leadername , s.grade from emp e join dept d ON e.DEPTNO = d.deptno join emp b on e.MGR = b.empno join salgrade s on e.sal BETWEEN s.LOSAL and s.HISAL ORDER BY dname desc;