MySql教程
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)
-
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后面不要当做区间。是一个具体的值。 -
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) -
mysql> select ename from emp where ename like '%o%';
+-------+
| ename | 表示查询名字里面含有o的,%表示任意多个字符。
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec) -
mysql> select ename from emp where ename like '_a%';
+--------+
| ename | 表示第二个字母为a的名字，_表示任意一个字符
+--------+ 想要让下划线表示普通的意思，使用转义字符。_表示一个普通的下划线。
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec) -
mysql> select ename from emp where ename like '%_%';
Empty set (0.00 sec)
查询名字中有下划线的。 -
mysql> select ename from emp where ename like '%T';
+-------+
| ename | 找出最后一个字母是T的。
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec) -
desc emp;
查看表的结构。 -
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) -
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) -
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) -
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)
-
找出工作岗位是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) -
select (3) from (1) where (2) order by (4);执行顺序
-
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) -
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) -
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) -
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) -
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);
-
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集群入门：新手必读指南11-20
-
部署MySQL集群教程：初学者指南11-20
-
部署MySQL集群项目实战：新手教程11-20
-
部署MySQL集群资料：新手入门教程11-20
-
MySQL集群部署教程：入门级详解11-20
-
MySQL集群教程：入门与实践指南11-20
-
部署MySQL集群教程：新手入门指南11-20
-
MySQL读写分离教程：轻松入门11-20
-
部署MySQL集群入门：一步一步搭建你的数据库集群11-20
-
部署MySQL集群学习：入门教程11-19
-
如何部署MySQL集群：新手入门教程11-19
-
Mysql安装教程：新手必看的详细安装指南11-19
-
Mysql安装入门：新手必读指南11-18
-
MySQL事务MVCC原理入门详解11-18
-
MySQL资料：新手入门教程11-16
