mysql常用基本命令（短期内补全）

最新推荐文章于 2020-10-14 19:56:15 发布
最新推荐文章于 2020-10-14 19:56:15 发布
阅读量333 收藏 3
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/S_body/article/details/103505558
版权

本文sql 脚本   ，需要一个积分  https://download.csdn.net/download/S_body/12028152 

1、查看数据库版本
第一种：进入数据库后查看，用函数
SELECT VERSION()
第二种：shell窗口查看
mysql --version
或者
mysql -v

2、基本操作
查看所有的数据库
show databases;
创建数据库
create database lxj;
create database test;
使用某个数据库
use lxj;
查看当前所在数据库
select database();
查看数据库里面的表
show tables;
查看别的数据库的表
show tables from test;

3、分类
DQL  SELECT 数据查询语言
DML  CREATE DELETE UPDATE 数据操作语言
DDL  CREATE DROP ALTER 数据库定义语言
TCL  COMMIT ROLLBACK 数据库事务

4、执行sql脚本
source lxj.sql

5、数据库表数据操作
=======查看员工表
desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
===薪水等级表
desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |
| LOSAL | int(11) | YES  |     | NULL    |       |
| HISAL | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
===部门表
desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
===查询员工信息
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)
===查询字段可以进行数学计算：查询员工12个月的一共有多少工资
mysql> select ename,sal*12 from emp;
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.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)
===起别名as关键字可以省略不写
mysql> select ename,sal*12 as allSal from emp;
+--------+----------+
| ename  | allSal   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.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)
===条件查询where
支持的运算符
= 等于
<>或者!=  不等于
between .. and ... 两个值之间等同于>= and <=
is null 为Null
and 并且
or 或者
in 包含
not 非，主要用于is 或者in 中
like 模糊查询，支持%或下划线匹配,%匹配任意个字符，下划线只匹配一个字符
查询薪水为5000的员工
mysql> select * from emp where sal = 5000;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
薪水不等于5000
mysql> select * from emp where sal != 5000;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
薪水在1000到5000之间的
mysql> select * from emp where sal between 1000 and 5000;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
12 rows in set (0.00 sec)
查询薪水大于1800并且部门编号为20或者30的员工
mysql> select * from emp where sal >1800 and (deptno =20 or deptno =30);
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)
查询补助为Null的员工
mysql> select * from emp where comm is null;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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 |
|  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 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.00 sec)
===数据的排序desc从大到小 asc 从小到大、默认
按照工资从大到小排序员工
mysql> select * from emp order by sal desc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

6、数据处理函数（单行函数）
===lower 转换小写
select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
14 rows in set (0.00 sec)
===upper 转换大写
select upper(ename) from emp;
+--------------+
| upper(ename) |
+--------------+
| SMITH        |
| ALLEN        |
| WARD         |
| JONES        |
| MARTIN       |
| BLAKE        |
| CLARK        |
| SCOTT        |
| KING         |
| TURNER       |
| ADAMS        |
| JAMES        |
| FORD         |
| MILLER       |
+--------------+
14 rows in set (0.00 sec)
===substr 截取字符串
截取第一个字符串
select substr(ename,1,1) from emp;
+-------------------+
| substr(ename,1,1) |
+-------------------+
| S                 |
| A                 |
| W                 |
| J                 |
| M                 |
| B                 |
| C                 |
| S                 |
| K                 |
| T                 |
| A                 |
| J                 |
| F                 |
| M                 |
+-------------------+
===length 长度
 select length(ename) from emp;
+---------------+
| length(ename) |
+---------------+
|             5 |
|             5 |
|             4 |
|             5 |
|             6 |
|             5 |
|             5 |
|             5 |
|             4 |
|             6 |
|             5 |
|             5 |
|             4 |
|             6 |
+---------------+
14 rows in set (0.00 sec)

===trim 去除字符串前后空格
mysql> select * from emp where ename =trim('kin g');
Empty set (0.00 sec)

mysql> select * from emp where ename =trim('king  ');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

===str_to_date 将字符串转换为日期
mysql> select str_to_date("1995-01-12",'%Y-%m-%d');
+--------------------------------------+
| str_to_date("1995-01-12",'%Y-%m-%d') |
+--------------------------------------+
| 1995-01-12                           |
+--------------------------------------+
mysql> select ename,hiredate from emp where hiredate = '1981-12-03';
+-------+------------+
| ename | hiredate   |
+-------+------------+
| JAMES | 1981-12-03 |
| FORD  | 1981-12-03 |
+-------+------------+
2 rows in set (0.00 sec)
mysql> select ename,hiredate from emp where hiredate = '12-03-1981';
Empty set, 2 warnings (0.00 sec)
mysql> select ename,hiredate from emp where hiredate = str_to_date('12-03-1981','%m-
%d-%Y');
+-------+------------+
| ename | hiredate   |
+-------+------------+
| JAMES | 1981-12-03 |
| FORD  | 1981-12-03 |
+-------+------------+
2 rows in set (0.00 sec)

===date_format 格式化日期
mysql> select date_format(hiredate,'%m/%Y/%s') from emp;
+----------------------------------+
| date_format(hiredate,'%m/%Y/%s') |
+----------------------------------+
| 12/1980/00                       |
| 02/1981/00                       |
| 02/1981/00                       |
| 04/1981/00                       |
| 09/1981/00                       |
| 05/1981/00                       |
| 06/1981/00                       |
| 04/1987/00                       |
| 11/1981/00                       |
| 09/1981/00                       |
| 05/1987/00                       |
| 12/1981/00                       |
| 12/1981/00                       |
| 01/1982/00                       |
+----------------------------------+
14 rows in set (0.00 sec)

===round 四舍五入
select round(123.56);
+---------------+
| round(123.56) |
+---------------+
|           124 |
+---------------+
1 row in set (0.00 sec)
select round(123.56,0);
+-----------------+
| round(123.56,0) |
+-----------------+
|             124 |
+-----------------+
1 row in set (0.00 sec)

===rand() 生成随机数
select rand()*122;
+-------------------+
| rand()*122        |
+-------------------+
| 81.17318778408057 |
+-------------------+
1 row in set (0.00 sec)
生成一个0到100的随机数
 select round(rand()*100);
+-------------------+
| round(rand()*100) |
+-------------------+
|                72 |
+-------------------+
1 row in set (0.00 sec)

===ifnull 将一个null转换为一个具体值
mysql> select ename,(sal+comm)*12 as allsal from emp;
+--------+----------+
| ename  | allsal   |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)
上面的结果出现了Null，显然不符合我们想要的结果,使用ifnull，将Null设置为0
mysql> select ename,(sal+ifnull(comm,0))*12 as allsal from emp;
+--------+----------+
| ename  | allsal   |
+--------+----------+
| 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)
===case when then else end
mysql> select job,ename,(case job when job = 'manager' then sal *1.1 when job = 'sal
esman' then sal *1.5 else sal end) as sal from emp;
+-----------+--------+---------+
| job       | ename  | sal     |
+-----------+--------+---------+
| CLERK     | SMITH  |  880.00 |
| SALESMAN  | ALLEN  | 1760.00 |
| SALESMAN  | WARD   | 1375.00 |
| MANAGER   | JONES  | 4462.50 |
| SALESMAN  | MARTIN | 1375.00 |
| MANAGER   | BLAKE  | 4275.00 |
| MANAGER   | CLARK  | 3675.00 |
| ANALYST   | SCOTT  | 3300.00 |
| PRESIDENT | KING   | 5500.00 |
| SALESMAN  | TURNER | 1650.00 |
| CLERK     | ADAMS  | 1210.00 |
| CLERK     | JAMES  | 1045.00 |
| ANALYST   | FORD   | 3300.00 |
| CLERK     | MILLER | 1430.00 |
+-----------+--------+---------+
14 rows in set, 14 warnings (0.00 sec)


7、聚合/分组/多行处理函数
注意：自动忽略空值，不能直接放在where之后
===sum 求和
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

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

===max 最大值
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

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

===count 
mysql> select count(empno) from emp where comm is null;
+--------------+
| count(empno) |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)
===distinct 去重
题目：公司有多少个工作岗位
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.01 sec)

===group by 分组函数
题目：按照工作岗位分组，并取出工资最高的记录
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| MANAGER   |  2975.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)
题目：每个工作岗位的最高薪水，除了manager之外
mysql> select job,max(sal) from emp where job != 'manager' group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+
4 rows in set (0.00 sec)

===having 用到group by之后，对分组数据进行筛选
题目：每个工作岗位的最高薪水，显示薪水大于2000的
mysql> select job,avg(sal) as avgsal from emp group by job having avgsal >2000;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.00 sec)


7、DQL总结
select xxx
from XXXX
where xxxx
group by xxx
having xxxx
order by xxxx
limit 0,1;

8、多表查询
SQL92
SQL99
题目：查询员工薪水对应的薪水等级
mysql> select e.sal , s.grade
    -> from emp e
    -> left join salgrade s
    -> on e.sal between s.losal and s.hisal;
+---------+-------+
| sal     | grade |
+---------+-------+
|  800.00 |     1 |
| 1100.00 |     1 |
|  950.00 |     1 |
| 1250.00 |     2 |
| 1250.00 |     2 |
| 1300.00 |     2 |
| 1600.00 |     3 |
| 1500.00 |     3 |
| 2975.00 |     4 |
| 2850.00 |     4 |
| 2450.00 |     4 |
| 3000.00 |     4 |
| 3000.00 |     4 |
| 5000.00 |     5 |
+---------+-------+
14 rows in set (0.00 sec)
查询出员工对应的领导名称以及员工自己的名称
mysql> select e.ename,l.ename as leadername from emp e left join emp l on e.mgr = l.
empno;
+--------+------------+
| ename  | leadername |
+--------+------------+
| SMITH  | FORD       |
| ALLEN  | BLAKE      |
| WARD   | BLAKE      |
| JONES  | KING       |
| MARTIN | BLAKE      |
| BLAKE  | KING       |
| CLARK  | KING       |
| SCOTT  | JONES      |
| KING   | NULL       |
| TURNER | BLAKE      |
| ADAMS  | SCOTT      |
| JAMES  | BLAKE      |
| FORD   | JONES      |
| MILLER | CLARK      |
+--------+------------+
14 rows in set (0.00 sec)

查询员工的部门名称，员工的领导名称和员工薪水等级

mysql> select e.ename,d.dname,l.ename as leadername,s.grade
    -> from emp e
    -> left join dept d on e.deptno = d.deptno
    -> left join emp l on e.mgr = l.empno
    -> left join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+------------+-------+
| ename  | dname      | leadername | grade |
+--------+------------+------------+-------+
| SMITH  | RESEARCH   | FORD       |     1 |
| ADAMS  | RESEARCH   | SCOTT      |     1 |
| JAMES  | SALES      | BLAKE      |     1 |
| MILLER | ACCOUNTING | CLARK      |     2 |
| WARD   | SALES      | BLAKE      |     2 |
| MARTIN | SALES      | BLAKE      |     2 |
| ALLEN  | SALES      | BLAKE      |     3 |
| TURNER | SALES      | BLAKE      |     3 |
| CLARK  | ACCOUNTING | KING       |     4 |
| JONES  | RESEARCH   | KING       |     4 |
| SCOTT  | RESEARCH   | JONES      |     4 |
| FORD   | RESEARCH   | JONES      |     4 |
| BLAKE  | SALES      | KING       |     4 |
| KING   | ACCOUNTING | NULL       |     5 |
+--------+------------+------------+-------+
14 rows in set (0.00 sec)


9、子查询
找出薪水比公司平均薪水高的员工，要求显示员工名称和员工薪水
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> select t.avgsal,s.grade from (select job,avg(sal) as avgsal from emp group by
 job ) t left join salgrade s on t.avgsal between s.losal and s.hisal;
+-------------+-------+
| avgsal      | grade |
+-------------+-------+
| 1037.500000 |     1 |
| 1400.000000 |     2 |
| 3000.000000 |     4 |
| 2758.333333 |     4 |
| 5000.000000 |     5 |
+-------------+-------+
5 rows in set (0.00 sec)



10、分页
公式：pageNo=页码 pageSize=每页显示的条数
(pageNo-1)*pageSize

11、表操作
===创建表
create table t_表名(){
    字段名 字段类型 字段长度 字段约束,
    字段名 字段类型 字段长度 字段约束,
    字段名 字段类型 字段长度 字段约束
};
===常用的数据类型
cahr 定长字符串，使用char(2)来表示类型或者状态
varchar 变长字符串
int 
bigint
float
double
date
blob 二进制大对象
clob 字符大对象
题目：创建一张学生表
create table t_student(
    no int(4) primary key,
    name varchar(32),
    gender char(1) default '1',
    birth date,
    email varchar(128)
);
===查看建表语句
show create table t_student;
===删除表
drop table t_student;
drop table if exists t_student;//推荐用这种
===快速复制一张表
mysql> create table emp_bak as select * from emp;
Query OK, 14 rows affected (0.02 sec)
Records: 14  Duplicates: 0  Warnings: 0


12、DML语句，数据操作语言
===插入数据insert
mysql> insert into t_student (no,name,gender,birth,email) values (1,'lxj','1',str_to
_date('1995-01-15','%Y-%m-%d'),'888888@qq.com');
Query OK, 1 row affected (0.01 sec)

===更新数据update
mysql> update t_student set email = '8129374@126.com' where no =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

===删除数据delete
mysql> delete from t_student where no =1;
Query OK, 1 row affected (0.01 sec)

===快速插入一批数据
mysql> insert into emp_bak select * from emp where job = 'manager';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

www.渣男.cn
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL基本命令、常用命令总结
09-10
主要介绍了MySQL基本命令、常用命令总结,需要的朋友可以参考下
mysql sql语句 引号吗_MySQL数据库:盘点SQL语句中不可省去的引号
weixin_29890919的博客
01-19 227
本文主要向大家介绍了MySQL数据库:盘点SQL语句中不可省去的引号，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。有一次在代码中发现了一个BUG，在排查过程中，差点晕翻;作一个DBA也碰到这种低级错误;大为自嘲。其实这个问题在官方手册里就写得很明白;(可见官方文档的重要性)mysql>createtableh1(idint,col1char(1));Qu...
25，MySQL 8.0参考手册 5.1.10.2时区闰秒支持
dandan520520的博客
03-31 471
闰秒值以时间部分返回，以时间部分结束 :59:59。这意味着NOW()在闰秒期间，一个函数 可以返回相同的值两到三秒。时间部分以时间部分结尾:59:60或被 :59:61认为是无效的字面时间值仍然是事实。如果有必要TIMESTAMP在闰秒前一秒搜索 值，则可以通过与'YYYY-MM-DD hh:mm:ss' 值进行比较来获得异常结果。以下示例演示了这一点。它将本地时区更改为UTC，因此内部值（以U...
2017元旦,你被闰秒吓到了吗? - 闰秒背景与数据库处理
weixin_34050519的博客
01-02 315
标签 PostgreSQL , Linux , POSIX , UT1 , UTC , leap second , 闰秒 , epoch , solar time , 太阳时 , unix time , Universal Time , Coordinated Universal Time , International Atomic Time 背景...
MySQL DECIMAL数据类型
热门推荐
刀刀的专栏
10-22 5万+
同事问MySQL数据类型DECIMAL(N,M)中N和M分别表示什么含义，M不用说，显然是小数点后的小数位数，但这个N究竟是小数点之前的最大位数，还是加上小数部分后的最大位数？这个还真记不清了。于是乎，创建测试表验证了一番，结果如下：测试表，seller_cost字段定义为decimal(14,2)CREATE TABLE `test_decimal` ( `id` int(11) NOT NU
mysql 查询时报 Empty set (0.00 sec)
weixin_44191019的博客
10-14 1万+
其实就是提示你实体表为空，说的是你的数据库或表里数据为空，或者查找的信息为空。可以直接在表里看一下数据，并核对一下查询语句。
MySQL常用命令大全.pdf
07-28
MySQL常用命令大全
mysql常用命令大全 mysql常用命令总结
09-10
本文介绍下，mysql中常用的一些命令，包括创建与修改数据库、数据库中的表，mysql的权限管理命令grant、revoke等的用法。有需要的朋友参考下
mysql常用命令总结适用于mysql8.0
12-22
mysql常用命令总结适用于mysql8.0
Empty set (0.00 sec)
huiqinbo的专栏
04-14 2万+
估计很多人在mysql  Command Line Client  doc端都遇到过这样的抛出：Empty set (0.00 sec) 其实就是提示你实体表为空，说的是你的数据库或表里数据为空，你添加上数据就可以了 更多交流，更多了解：QQ 444084929 ...
Mysql学习1
ChengZi~
08-18 228
Mysql学习11 SQL基础1.1 SQL的分类1.2 导入数据1.3 表的复制1.4 查看建表语句2 条件查询2.1 and2.2 or2.3 and和or的优先级2.4 in3 测试表的创表语句    1 SQL基础 1.1 SQL的分类 语言种类 代表关键字 缩写 数据查询语言(Data Query Language) select DQL 数据操纵语言(Data Ma...
MYSQL：如何使用MYSQL命令行的自动补全功能
12-20 1778
使用MYSQL -I命令，查看MYSQL命令的参数，其中对--auto-rehash参数的说明如下： --auto-rehash     Enable automatic rehashing. One doesn't need to use                   'rehash' to get table and field completion, but startup
oracle动态update语句
渣男的博客
05-23 4133
1、今天公司项目提交更新表单出错，这个bug交给了我。 2、OK，debugger一下，发现提交的时候后台打印出错，提示大概就是这样 SQLException '17004' 其实很简单，这种错误一般都是说某一列传进来的值为null，因此报错，所以追踪下去，发现是update语句没有做动态传值处理，也就是说需要写成动态sql语句 3、那么Mybatis的动态sql语句是怎么样的呢？ u...
PLSQL Developer连接远程数据库
渣男的博客
12-31 380
自己笔记本电脑需要连接公司的ORCLE数据库，我折腾了好几天了才全部理解搞定清楚！！网上很多复制黏贴很不友好！ 分为两种情况：你可以尽量都试下，肯定有一种能成功 一：安装64位的instantclient （1）下载解压，64位的instantclient （2）解压路劲D:\instantclient_12_2 （3）在解压的路径下依次新建NETWORK和ADMIN文件夹 （4）在此路径下
orcacle数据库的安装和解除scott用户锁
渣男的博客
11-05 344
orcacle数据库的安装和解除scott用户锁安装的话大家可以看这篇百度的文章教程，我也是按着来的，很简单 https://jingyan.baidu.com/article/e75057f2e7efcbebc91a89f4.html
2021数学建模美赛C题代码.zip
最新发布
04-24
最全的数学建模美赛C题和代码、大量刷题题库、逻辑清晰易于学习
mysql常用命令速查表pdf
06-19
其中，mysql常用命令速查表pdf就是一份非常实用的参考资料，里面介绍了MySQL的常用命令及其参数，涵盖了MySQL的基本操作、数据查询、表管理、用户权限、备份恢复等方面。 使用mysql常用命令速查表pdf，我们可以快速...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值