mysql常用基本命令

1、查看数据库版本
第一种:进入数据库后查看,用函数
SELECT VERSION()
第二种:shell窗口查看
mysql --version
或者
mysql -v
2、基本操作
查看表占用的空间大小
USE information_schema;
SELECT TABLE_SCHEMA, SUM(DATA_LENGTH)/1024 FROM TABLES GROUP BY TABLE_SCHEMA;
查看所有的数据库
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
 
 
 
# mysql 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×