mysql 常用的sql语句随笔

2022-04-05 15:31:38

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)

  1. 找出工作岗位是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)

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

  3. 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)

  4. 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)

  5. 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)

  6. 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)

  7. 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);

  8. 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学习笔记B站


下一篇：oracle总结