mysql 常用的sql语句随笔

本文主要是介绍mysql 常用的sql语句随笔，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

1. mysql> select ename,job from emp where job = 'salesman' or job = 'clerk';
   +--------+----------+
   | ename | job |
   +--------+----------+
   | SMITH | CLERK |
   | ALLEN | SALESMAN |
   | WARD | SALESMAN |
   | MARTIN | SALESMAN |
   | TURNER | SALESMAN |
   | ADAMS | CLERK |
   | JAMES | CLERK |
   | MILLER | CLERK |
   +--------+----------+
   8 rows in set (0.00 sec)

   mysql> select ename,job from emp where job in('salesman','clerk');
   +--------+----------+
   | ename | job |
   +--------+----------+
   | SMITH | CLERK |
   | ALLEN | SALESMAN |
   | WARD | SALESMAN |
   | MARTIN | SALESMAN |
   | TURNER | SALESMAN |
   | ADAMS | CLERK |
   | JAMES | CLERK |
   | MILLER | CLERK |
   +--------+----------+
   8 rows in set (0.01 sec)

   job = 'salesman' or job = 'clerk';
   job in('salesman','clerk');
   in和or的效果相等。in后面不要当做区间。是一个具体的值。

2. mysql> select ename,job from emp where job not in('salesman','clerk');
   +-------+-----------+
   | ename | job |
   +-------+-----------+
   | JONES | MANAGER |
   | BLAKE | MANAGER |
   | CLARK | MANAGER |
   | SCOTT | ANALYST |
   | KING | PRESIDENT |
   | FORD | ANALYST |
   +-------+-----------+
   6 rows in set (0.00 sec)

3. mysql> select ename from emp where ename like '%o%';
   +-------+
   | ename | 表示查询名字里面含有o的,%表示任意多个字符。
   +-------+
   | JONES |
   | SCOTT |
   | FORD |
   +-------+
   3 rows in set (0.00 sec)

4. mysql> select ename from emp where ename like '_a%';
   +--------+
   | ename | 表示第二个字母为a的名字，_表示任意一个字符
   +--------+ 想要让下划线表示普通的意思，使用转义字符。_表示一个普通的下划线。
   | WARD |
   | MARTIN |
   | JAMES |
   +--------+
   3 rows in set (0.00 sec)

5. mysql> select ename from emp where ename like '%_%';
   Empty set (0.00 sec)
   查询名字中有下划线的。

6. mysql> select ename from emp where ename like '%T';
   +-------+
   | ename | 找出最后一个字母是T的。
   +-------+
   | SCOTT |
   +-------+
   1 row in set (0.00 sec)

7. desc emp;
   查看表的结构。

8. mysql> select ename,sal from emp order by sal;
   +--------+---------+
   | ename | sal | 排序默认按照升序排序。
   +--------+---------+ asc 或者不写是升序
   | SMITH | 800.00 | desc是降序排序
   | JAMES | 950.00 |
   | ADAMS | 1100.00 |
   | WARD | 1250.00 |
   | MARTIN | 1250.00 |
   | MILLER | 1300.00 |
   | TURNER | 1500.00 |
   | ALLEN | 1600.00 |
   | CLARK | 2450.00 |
   | BLAKE | 2850.00 |
   | JONES | 2975.00 |
   | FORD | 3000.00 |
   | SCOTT | 3000.00 |
   | KING | 5000.00 |
   +--------+---------+
   14 rows in set (0.01 sec)

9. mysql> select ename,sal from emp order by sal desc;
   +--------+---------+
   | ename | sal |
   +--------+---------+
   | KING | 5000.00 |
   | SCOTT | 3000.00 |
   | FORD | 3000.00 |
   | JONES | 2975.00 |
   | BLAKE | 2850.00 |
   | CLARK | 2450.00 |
   | ALLEN | 1600.00 |
   | TURNER | 1500.00 |
   | MILLER | 1300.00 |
   | MARTIN | 1250.00 |
   | WARD | 1250.00 |
   | ADAMS | 1100.00 |
   | JAMES | 950.00 |
   | SMITH | 800.00 |
   +--------+---------+
   14 rows in set (0.00 sec)

10. select ename,sal from emp order by sal desc,ename asc;
    +--------+---------+
    | ename | sal | 这个表示先按照工资降序排列，然后按照名字的升序排列
    +--------+---------+ ，order后面想要指定的规则用逗号分开，但是越往后执行
    | KING | 5000.00 | 效果越低。有些时候后面的字段不会使用。
    | FORD | 3000.00 |
    | SCOTT | 3000.00 |
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    | ALLEN | 1600.00 |
    | TURNER | 1500.00 |
    | MILLER | 1300.00 |
    | MARTIN | 1250.00 |
    | WARD | 1250.00 |
    | ADAMS | 1100.00 |
    | JAMES | 950.00 |
    | SMITH | 800.00 |
    +--------+---------+
    14 rows in set (0.00 sec)

11. select ename,sal from emp order by 1; 后面的1,2指的是字段，按照字段升序排序。
    +--------+---------+ 这样写不健壮，建议写死。
    | ename | sal |
    +--------+---------+
    | ADAMS | 1100.00 |
    | ALLEN | 1600.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    | FORD | 3000.00 |
    | JAMES | 950.00 |
    | JONES | 2975.00 |
    | KING | 5000.00 |
    | MARTIN | 1250.00 |
    | MILLER | 1300.00 |
    | SCOTT | 3000.00 |
    | SMITH | 800.00 |
    | TURNER | 1500.00 |
    | WARD | 1250.00 |
    +--------+---------+
    14 rows in set (0.00 sec)

mysql>  select ename,sal from emp order by 2;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

12. 找出工作岗位是salesman并且按照工资的降序排列。
    mysql> select ename,job,sal from emp where job = 'salesman' order by sal desc;
    +--------+----------+---------+
    | ename | job | sal |
    +--------+----------+---------+
    | ALLEN | SALESMAN | 1600.00 |
    | TURNER | SALESMAN | 1500.00 |
    | WARD | SALESMAN | 1250.00 |
    | MARTIN | SALESMAN | 1250.00 |
    +--------+----------+---------+
    4 rows in set (0.00 sec)

13. select (3) from (1) where (2) order by (4);执行顺序

14. mysql> select sum(sal) from emp;
    +----------+
    | sum(sal) | sum是分组函数对一组数据进行操作。
    +----------+ 分组函数有5个
    | 29025.00 | 分组函数自动忽略null，如果计算过程有null参与，结果必定是null。
    +----------+ 多行处理函数，运算多行，结果一行。
    1 row in set (0.00 sec)
    mysql> select sum(sal) from emp;
    +----------+
    | sum(sal) |
    +----------+
    | 29025.00 |
    +----------+
    1 row in set (0.00 sec)

    mysql> select max(sal) from emp;
    +----------+
    | max(sal) |
    +----------+
    | 5000.00 |
    +----------+
    1 row in set (0.01 sec)

    mysql> select min(sal) from emp;
    +----------+
    | min(sal) |
    +----------+
    | 800.00 |
    +----------+
    1 row in set (0.00 sec)

    mysql> select count(ename) from emp;
    +--------------+
    | count(ename) |
    +--------------+
    | 14 |
    +--------------+
    1 row in set (0.00 sec)

    mysql> select count(sal) from emp;
    +------------+
    | count(sal) |
    +------------+
    | 14 |
    +------------+
    1 row in set (0.00 sec)

    mysql> select avg(sal) from emp;
    +-------------+
    | avg(sal) |
    +-------------+
    | 2073.214286 |
    +-------------+
    1 row in set (0.00 sec)

15. mysql> select ename,ifnull(comm,0) from emp;
    +--------+----------------+
    | ename | ifnull(comm,0) | ifnull(comm,0)；如果comm是null转换为0；
    +--------+----------------+
    | SMITH | 0.00 |
    | ALLEN | 300.00 |
    | WARD | 500.00 |
    | JONES | 0.00 |
    | MARTIN | 1400.00 |
    | BLAKE | 0.00 |
    | CLARK | 0.00 |
    | SCOTT | 0.00 |
    | KING | 0.00 |
    | TURNER | 0.00 |
    | ADAMS | 0.00 |
    | JAMES | 0.00 |
    | FORD | 0.00 |
    | MILLER | 0.00 |
    +--------+----------------+
    14 rows in set (0.01 sec)

16. mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
    +--------+----------+
    | ename | yearsal | 求员工的年薪。工资+补贴
    +--------+----------+
    | SMITH | 9600.00 |
    | ALLEN | 22800.00 |
    | WARD | 21000.00 |
    | JONES | 35700.00 |
    | MARTIN | 31800.00 |
    | BLAKE | 34200.00 |
    | CLARK | 29400.00 |
    | SCOTT | 36000.00 |
    | KING | 60000.00 |
    | TURNER | 18000.00 |
    | ADAMS | 13200.00 |
    | JAMES | 11400.00 |
    | FORD | 36000.00 |
    | MILLER | 15600.00 |
    +--------+----------+
    14 rows in set (0.00 sec)

17. mysql> select count() from emp;
    +----------+ count()表示计算所有的条数
    | count(*) |
    +----------+
    | 14 |
    +----------+
    1 row in set (0.00 sec)

    mysql> select count(comm) from emp;
    +-------------+
    | count(comm) | count(comm)表示计算comm有多少条不为null
    +-------------+
    | 4 |
    +-------------+
    1 row in set (0.00 sec)

18. mysql> select job, max(sal) from emp group by job;
    +-----------+----------+
    | job | max(sal) | 分组函数一般与group by 联合使用。
    +-----------+----------+ 先分组后执行分组函数，如果没有分组，
    | ANALYST | 3000.00 | 整张表默认一个组。
    | CLERK | 1300.00 | group by是在where后面执行的。所以不能在where
    | MANAGER | 2975.00 | 后面执行分组函数。
    | PRESIDENT | 5000.00 | 当一个语句使用group by的时候，select 后面只能
    | SALESMAN | 1600.00 | 跟参加分组的字段，和分组函数。不然查询出来的数据
    +-----------+----------+ 是无意义的。
    5 rows in set (0.00 sec)

    select (5) from(1) where(2) group by (3) having(4)order by(6);

19. mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
    +-------+---------+
    | ename | sal | 查询工资大于平均工资的是那些。可以使用分步的方法。
    +-------+---------+ 先求平均，在求大于的是那些。
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    | SCOTT | 3000.00 |
    | KING | 5000.00 |
    | FORD | 3000.00 |
    +-------+---------+
    6 rows in set (0.00 sec)

这篇关于mysql 常用的sql语句随笔的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！