MySQL 的 SQL 操作

1.mysql　　2.show　　3.select　　4.数据处理函数　　5.多表-连接查询　　6.子查询　　7.查询分页　　8.约束　　9.事务　　10.视图　　11.存储例程　　12.流程控制　　13.数据类型　　last 　　　　

附表：scott

USE `hope`;

DROP TABLE IF EXISTS `bonus`;

CREATE TABLE `bonus` (
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '雇员工资',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '雇员资金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资表';

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
  `deptno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `dname` varchar(15) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(50) DEFAULT NULL COMMENT '部门所在位置',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COMMENT='部门表';

insert  into `dept`(`deptno`,`dname`,`loc`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (
  `empno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '雇员编号',
  `ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(10) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(10) unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号',
  `hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(10) unsigned DEFAULT NULL COMMENT '所在部门',
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8 COMMENT='雇员表';

insert  into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values (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-07-13','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-07-13','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);

DROP TABLE IF EXISTS `salgrade`;

CREATE TABLE `salgrade` (
  `grade` int(10) unsigned DEFAULT NULL COMMENT '工资等级',
  `losal` int(10) unsigned DEFAULT NULL COMMENT '此等级的最低工资',
  `hisal` int(10) unsigned DEFAULT NULL COMMENT '此等级的最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表';

insert  into `salgrade`(`grade`,`losal`,`hisal`) values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
View Code

1.mysql

  1. 查看 服务的默认选项： 
    ./mysqld --verbose --help
    View Code
  2. 运行时 服务器选项配置： 
    mysqladmin variables
    View Code
  3. 运行的服务器版本信息： 
    mysqladmin -uroot -p variables version
    View Code

2.show

3.select

  1. 服务器版本号： 
    SELECT VERSION();
    View Code
  2. 查看当前数据库名称： 
    SELECT DATABASE();
    View Code
  3. 查询空值NULL： 
    SELECT ename,comm FROM emp WHERE comm IS NOT NULL;
    View Code
  4. 模糊查询：
    like：
    SELECT ename,sal FROM emp WHERE ename LIKE 's%';
SELECT ename,sal FROM emp WHERE ename LIKE '_d%';
    View Code

    between：

    SELECT e.ename,g.grade
FROM emp e
    JOIN salgrade g ON e.`sal` BETWEEN g.`losal` AND g.`hisal`
WHERE ename <> 'king'
ORDER BY g.`grade` DESC;
    View Code

     

4.数据处理函数

4.1.单行函数

  1. 字符函数 
    #字符串长度
mysql> select length('argor');
+-----------------+
| length('argor') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

#长度包含空格
mysql> select length('argor ');
+------------------+
| length('argor ') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

#获取子串
#SQL中下标起点为1，substr（String， StartIndex）
mysql> select substr('123456', 5);
+---------------------+
| substr('123456', 5) |
+---------------------+
| 56                  |
+---------------------+
1 row in set (0.00 sec)

#获取子串，起始下标越界，……
mysql> select substr('123456', 7);
+---------------------+
| substr('123456', 7) |
+---------------------+
|                     |
+---------------------+
1 row in set (0.00 sec)

#获取子串重载方法，substr（String，start，len）
mysql> select substr('123456', 3, 2);
+------------------------+
| substr('123456', 3, 2) |
+------------------------+
| 34                     |
+------------------------+
1 row in set (0.00 sec)

#子串下标
mysql> select instr('123456', '5');
+----------------------+
| instr('123456', '5') |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

mysql> select instr('123456', '45');
+-----------------------+
| instr('123456', '45') |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

#下表越界，
mysql> select instr('123456', '7');
+----------------------+
| instr('123456', '7') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

#字符串拼接，concat（String...）
mysql> select concat("hello", "argor");
+--------------------------+
| concat("hello", "argor") |
+--------------------------+
| helloargor               |
+--------------------------+
1 row in set (0.00 sec)

mysql> select concat("hello", " ", "argor");
+-------------------------------+
| concat("hello", " ", "argor") |
+-------------------------------+
| hello argor                   |
+-------------------------------+
1 row in set (0.00 sec)

#字符串转小写
mysql> select lower("Argor");
+----------------+
| lower("Argor") |
+----------------+
| argor          |
+----------------+
1 row in set (0.00 sec)

#字符串转大写
mysql> select upper("argor");
+----------------+
| upper("argor") |
+----------------+
| ARGOR          |
+----------------+
1 row in set (0.00 sec)

#两端去空格
mysql> select trim('  argor  ');
+-------------------+
| trim('  argor  ') |
+-------------------+
| argor             |
+-------------------+
1 row in set (0.00 sec)

#两端去指定字符
mysql> select trim('a' from 'a  argor  aa');
+-------------------------------+
| trim('a' from 'a  argor  aa') |
+-------------------------------+
|   argor                       |
+-------------------------------+
1 row in set (0.00 sec)

#左填充
mysql> select lpad('argor', 8, '>');
+-----------------------+
| lpad('argor', 8, '>') |
+-----------------------+
| >>>argor              |
+-----------------------+
1 row in set (0.00 sec)

#右填充
mysql> select rpad('argor', 8, '<');
+-----------------------+
| rpad('argor', 8, '<') |
+-----------------------+
| argor<<<              |
+-----------------------+
1 row in set (0.00 sec)

#字符替换
mysql> select replace('a r g o r', ' ', '');
+-------------------------------+
| replace('a r g o r', ' ', '') |
+-------------------------------+
| argor                         |
+-------------------------------+
1 row in set (0.00 sec)
    View Code
  2. 数学函数 
    #取随机数，0 <= Value < 1.0
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8167916432179433 |
+--------------------+
1 row in set (0.00 sec)

#获取区间：i <= Value <= j 的整数，FLOOR(i + RAND()*j)
mysql> select FLOOR(1 + RAND()*3);
+---------------------+
| FLOOR(1 + RAND()*3) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select FLOOR(1 + RAND()*3);
+---------------------+
| FLOOR(1 + RAND()*3) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

mysql> select FLOOR(1 + RAND()*3);
+---------------------+
| FLOOR(1 + RAND()*3) |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

#四舍五入
mysql> select round(3.1415);
+---------------+
| round(3.1415) |
+---------------+
|             3 |
+---------------+
row in set (0.00 sec)

mysql> select round(-3.141);
+---------------+
| round(-3.141) |
+---------------+
|            -3 |
+---------------+
row in set (0.00 sec)

mysql> select round(3.1415, 2);
+------------------+
| round(3.1415, 2) |
+------------------+
|             3.14 |
+------------------+
row in set (0.00 sec)

mysql> select round(-3.141, 2);
+------------------+
| round(-3.141, 2) |
+------------------+
|            -3.14 |
+------------------+
row in set (0.00 sec)

#大于等于的最小
mysql> select ceil(3.1415);
+--------------+
| ceil(3.1415) |
+--------------+
|            4 |
+--------------+
row in set (0.00 sec)

mysql> select ceil(3.0000);
+--------------+
| ceil(3.0000) |
+--------------+
|            3 |
+--------------+
row in set (0.00 sec)

mysql> select ceil(-3.1415);
+---------------+
| ceil(-3.1415) |
+---------------+
|            -3 |
+---------------+
row in set (0.00 sec)

#小于等于的最大
mysql> select floor(3.1415);
+---------------+
| floor(3.1415) |
+---------------+
|             3 |
+---------------+
row in set (0.00 sec)

mysql> select floor(-3.141);
+---------------+
| floor(-3.141) |
+---------------+
|            -4 |
+---------------+
row in set (0.00 sec)

#截取
mysql> select truncate(3.14151, 3);
+----------------------+
| truncate(3.14151, 3) |
+----------------------+
|                3.141 |
+----------------------+
row in set (0.00 sec)

mysql> select truncate(-3.1415, 3);
+----------------------+
| truncate(-3.1415, 3) |
+----------------------+
|               -3.141 |
+----------------------+
row in set (0.00 sec)

#取余，mod（n, m）算法：n-n/m*m
mysql> select mod(13, 3);
+------------+
| mod(13, 3) |
+------------+
|          1 |
+------------+
row in set (0.00 sec)

mysql> select mod(-13, 3);
+-------------+
| mod(-13, 3) |
+-------------+
|          -1 |
+-------------+
row in set (0.00 sec)

mysql> select mod(13, -3);
+-------------+
| mod(13, -3) |
+-------------+
|           1 |
+-------------+
row in set (0.00 sec)

mysql> select mod(-13, -3);
+--------------+
| mod(-13, -3) |
+--------------+
|           -1 |
+--------------+
row in set (0.00 sec)
    View Code
  3. 日期函数 
    #当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-17 05:22:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-07-17 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 05:23:07  |
+-----------+
1 row in set (0.00 sec)

#获取年份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.00 sec)

mysql> select year('1998-7-1');
+------------------+
| year('1998-7-1') |
+------------------+
|             1998 |
+------------------+
1 row in set (0.00 sec)

#获取月份
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

mysql> select month('1998-7-1');
+-------------------+
| month('1998-7-1') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

#字符串转日期
mysql> select ename,hiredate from emp where hiredate = '1981-12-03';
+-------+------------+
| ename | hiredate   |
+-------+------------+
| JAMES | 1981-12-03 |
| FORD  | 1981-12-03 |
+-------+------------+
rows in set (0.00 sec)

mysql> select ename,hiredate from emp where hiredate = str_to_date('1981-12-03', '%Y-%m-%d');
+-------+------------+
| ename | hiredate   |
+-------+------------+
| JAMES | 1981-12-03 |
| FORD  | 1981-12-03 |
+-------+------------+
rows in set (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 |
+-------+------------+
rows in set (0.00 sec)

#日期转字符串
mysql> select ename,date_format(hiredate, '%y%m%d') from emp where ename = 'ford';
+-------+---------------------------------+
| ename | date_format(hiredate, '%y%m%d') |
+-------+---------------------------------+
| FORD  | 811203                          |
+-------+---------------------------------+
row in set (0.01 sec)

mysql> select ename,date_format(hiredate, '%Y/%m/%d') from emp where ename = 'ford';
+-------+-----------------------------------+
| ename | date_format(hiredate, '%Y/%m/%d') |
+-------+-----------------------------------+
| FORD  | 1981/12/03                        |
+-------+-----------------------------------+
row in set (0.00 sec)

mysql> select ename,date_format(hiredate, '%Y/%m/%d %S') from emp where ename = 'ford';
+-------+--------------------------------------+
| ename | date_format(hiredate, '%Y/%m/%d %S') |
+-------+--------------------------------------+
| FORD  | 1981/12/03 00                        |
+-------+--------------------------------------+
row in set (0.00 sec)

mysql> select ename,date_format(hiredate, '%Y/%m/%d %s') from emp where ename = 'ford';
+-------+--------------------------------------+
| ename | date_format(hiredate, '%Y/%m/%d %s') |
+-------+--------------------------------------+
| FORD  | 1981/12/03 00                        |
+-------+--------------------------------------+
row in set (0.00 sec)

mysql> select ename,date_format(hiredate, '%Y/%m/%d %H:%i:%s') from emp where ename = 'ford';
+-------+--------------------------------------------+
| ename | date_format(hiredate, '%Y/%m/%d %H:%i:%s') |
+-------+--------------------------------------------+
| FORD  | 1981/12/03 00:00:00                        |
+-------+--------------------------------------------+
row in set (0.00 sec)
    View Code
  4. 流程控制
    ①IF 语句 
    mysql> SELECT IF(10>5,'大','小');
+--------------------+
| IF(10>5,'大','小')    |
+--------------------+
| 大                   |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT ename,IF(comm IS NULL,0,comm) FROM emp;
+--------+-------------------------+
| ename  | IF(comm IS NULL,0,comm) |
+--------+-------------------------+
| SMITH  |                       0 |
| ALLEN  |                  300.00 |
| WARD   |                  500.00 |
| JONES  |                       0 |
| MARTIN |                 1400.00 |
| BLAKE  |                       0 |
| CLARK  |                       0 |
| SCOTT  |                       0 |
| KING   |                       0 |
| TURNER |                    0.00 |
| ADAMS  |                       0 |
| JAMES  |                       0 |
| FORD   |                       0 |
| MILLER |                       0 |
+--------+-------------------------+
14 rows in set (0.00 sec)

mysql> SELECT IF(en IS NULL, '无', en) FROM ENUM;
    View Code

    ②CASE 语句用法

    SELECT
    ename,job,sal,(
        CASE job
          WHEN 'manager' THEN 500
          WHEN 'salesman' THEN 300
        END
    ) AS new_sal
FROM
    emp
WHERE
    job IN ('salesman', 'manager');

mysql> SELECT
    -> ename,job,sal,(
    ->     CASE job
    ->       WHEN 'manager' THEN 500
    ->       WHEN 'salesman' THEN 300
    ->     END
    ->     ) AS new_sal
    -> FROM
    -> emp
    -> WHERE
    -> job IN ('salesman', 'manager');
+--------+----------+---------+---------+
| ename  | job      | sal     | new_sal |
+--------+----------+---------+---------+
| ALLEN  | SALESMAN | 1600.00 |     300 |
| WARD   | SALESMAN | 1250.00 |     300 |
| JONES  | MANAGER  | 2975.00 |     500 |
| MARTIN | SALESMAN | 1250.00 |     300 |
| BLAKE  | MANAGER  | 2850.00 |     500 |
| CLARK  | MANAGER  | 2450.00 |     500 |
| TURNER | SALESMAN | 1500.00 |     300 |
+--------+----------+---------+---------+
7 rows in set (0.00 sec)

SELECT 
    ename,job,sal,(
        CASE job
        WHEN 'manager' THEN 500
        WHEN 'salesman' THEN 300
        ELSE 0
        END
        ) AS new_sal
FROM
    emp
WHERE
    job IN ('manager','salesman');

mysql> SELECT
    -> ename,job,sal,(
    -> CASE job
    -> WHEN 'manager' THEN 500
    -> WHEN 'salesman' THEN 300
    -> ELSE 0
    -> END
    -> ) AS new_sal
    -> FROM
    -> emp
    -> WHERE
    -> job IN ('manager','salesman');
+--------+----------+---------+---------+
| ename  | job      | sal     | new_sal |
+--------+----------+---------+---------+
| ALLEN  | SALESMAN | 1600.00 |     300 |
| WARD   | SALESMAN | 1250.00 |     300 |
| JONES  | MANAGER  | 2975.00 |     500 |
| MARTIN | SALESMAN | 1250.00 |     300 |
| BLAKE  | MANAGER  | 2850.00 |     500 |
| CLARK  | MANAGER  | 2450.00 |     500 |
| TURNER | SALESMAN | 1500.00 |     300 |
+--------+----------+---------+---------+
7 rows in set (0.00 sec)

SELECT
    ename,job,sal,(
        CASE job
          WHEN 'manager' THEN 500
          WHEN 'salesman' THEN 300
          ELSE NULL
        END
    ) AS new_sal
FROM
    emp
WHERE
    job IN ('manager','salesman');

mysql> SELECT
    -> ename,job,sal,(
    ->     CASE job
    ->       WHEN 'manager' THEN 500
    ->       WHEN 'salesman' THEN 300
    ->       ELSE NULL
    ->     END
    -> ) AS new_sal
    -> FROM
    -> emp
    -> WHERE
    -> job IN ('manager','salesman');
+--------+----------+---------+---------+
| ename  | job      | sal     | new_sal |
+--------+----------+---------+---------+
| ALLEN  | SALESMAN | 1600.00 |     300 |
| WARD   | SALESMAN | 1250.00 |     300 |
| JONES  | MANAGER  | 2975.00 |     500 |
| MARTIN | SALESMAN | 1250.00 |     300 |
| BLAKE  | MANAGER  | 2850.00 |     500 |
| CLARK  | MANAGER  | 2450.00 |     500 |
| TURNER | SALESMAN | 1500.00 |     300 |
+--------+----------+---------+---------+
7 rows in set (0.00 sec)
    View Code
  5. 空值（NULL）运算 
    mysql> select ename,sal,comm,sal+comm from emp;
+--------+---------+---------+----------+
| ename  | sal     | comm    | sal+comm |
+--------+---------+---------+----------+
| SMITH  |  800.00 |    NULL |     NULL |
| ALLEN  | 1600.00 |  300.00 |  1900.00 |
| WARD   | 1250.00 |  500.00 |  1750.00 |
| JONES  | 2975.00 |    NULL |     NULL |
| MARTIN | 1250.00 | 1400.00 |  2650.00 |
| BLAKE  | 2850.00 |    NULL |     NULL |
| CLARK  | 2450.00 |    NULL |     NULL |
| SCOTT  | 3000.00 |    NULL |     NULL |
| KING   | 5000.00 |    NULL |     NULL |
| TURNER | 1500.00 |    0.00 |  1500.00 |
| ADAMS  | 1100.00 |    NULL |     NULL |
| JAMES  |  950.00 |    NULL |     NULL |
| FORD   | 3000.00 |    NULL |     NULL |
| MILLER | 1300.00 |    NULL |     NULL |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)

mysql> select ename,sal+null from emp;
+--------+----------+
| ename  | sal+null |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  |     NULL |
| WARD   |     NULL |
| JONES  |     NULL |
| MARTIN |     NULL |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER |     NULL |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

mysql> select ename,sal,comm,sal + ifnull(comm,0) from emp;
+--------+---------+---------+----------------------+
| ename  | sal     | comm    | sal + ifnull(comm,0) |
+--------+---------+---------+----------------------+
| SMITH  |  800.00 |    NULL |               800.00 |
| ALLEN  | 1600.00 |  300.00 |              1900.00 |
| WARD   | 1250.00 |  500.00 |              1750.00 |
| JONES  | 2975.00 |    NULL |              2975.00 |
| MARTIN | 1250.00 | 1400.00 |              2650.00 |
| BLAKE  | 2850.00 |    NULL |              2850.00 |
| CLARK  | 2450.00 |    NULL |              2450.00 |
| SCOTT  | 3000.00 |    NULL |              3000.00 |
| KING   | 5000.00 |    NULL |              5000.00 |
| TURNER | 1500.00 |    0.00 |              1500.00 |
| ADAMS  | 1100.00 |    NULL |              1100.00 |
| JAMES  |  950.00 |    NULL |               950.00 |
| FORD   | 3000.00 |    NULL |              3000.00 |
| MILLER | 1300.00 |    NULL |              1300.00 |
+--------+---------+---------+----------------------+
14 rows in set (0.01 sec)
    View Code

4.2.分组函数

  1.  求和（自动处理null） 
    mysql> select ename,comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

#自动处理null值
mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)
    View Code
  2. 统计函数，跳过 NULL 值
    mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

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

  3. 平均值（默认跳过 NULL，不在统计之列）

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

mysql> select avg(sal),avg(ifnull(comm,0)) from emp;
+-------------+---------------------+
| avg(sal)    | avg(ifnull(comm,0)) |
+-------------+---------------------+
| 2073.214286 |          157.142857 |
+-------------+---------------------+
1 row in set (0.00 sec)
    View Code
  4. 最大值、最小值（默认跳过 NULL，不在统计之列） 
    mysql> select max(sal),min(sal),max(comm),min(comm) from emp;
+----------+----------+-----------+-----------+
| max(sal) | min(sal) | max(comm) | min(comm) |
+----------+----------+-----------+-----------+
|  5000.00 |   800.00 |   1400.00 |      0.00 |
+----------+----------+-----------+-----------+
1 row in set (0.00 sec)
    View Code
  5. 分组查询，查询部门的人数、部分的岗位数 
    mysql> select distinct deptno,count(job) from emp group by deptno;
+--------+------------+
| deptno | count(job) |
+--------+------------+
|     10 |          3 |
|     20 |          5 |
|     30 |          6 |
+--------+------------+
3 rows in set (0.00 sec)

mysql> select distinct deptno,count(distinct job) from emp group by deptno;
+--------+---------------------+
| deptno | count(distinct job) |
+--------+---------------------+
|     10 |                   3 |
|     20 |                   3 |
|     30 |                   3 |
+--------+---------------------+
3 rows in set (0.00 sec)
    View Code

    部门薪资统计、岗位薪资统计

    mysql> select deptno,max(sal),min(sal),avg(sal) from emp group by deptno order by deptno;
+--------+----------+----------+-------------+
| deptno | max(sal) | min(sal) | avg(sal)    |
+--------+----------+----------+-------------+
|     10 |  5000.00 |  1300.00 | 2916.666667 |
|     20 |  3000.00 |   800.00 | 2175.000000 |
|     30 |  2850.00 |   950.00 | 1566.666667 |
+--------+----------+----------+-------------+
3 rows in set (0.00 sec)

mysql> select job,max(sal),min(sal),avg(sal) from emp group by job order by job;
+-----------+----------+----------+-------------+
| job       | max(sal) | min(sal) | avg(sal)    |
+-----------+----------+----------+-------------+
| ANALYST   |  3000.00 |  3000.00 | 3000.000000 |
| CLERK     |  1300.00 |   800.00 | 1037.500000 |
| MANAGER   |  2975.00 |  2450.00 | 2758.333333 |
| PRESIDENT |  5000.00 |  5000.00 | 5000.000000 |
| SALESMAN  |  1600.00 |  1250.00 | 1400.000000 |
+-----------+----------+----------+-------------+
5 rows in set (0.00 sec)
    View Code

    按照部门、岗位分组，统计薪资

    select
  deptno,job,sal
from
  emp
order by
  deptno,sal desc;

mysql> select deptno,job,sal from emp order by deptno,sal desc;
+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     10 | PRESIDENT | 5000.00 |
|     10 | MANAGER   | 2450.00 |
|     10 | CLERK     | 1300.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | MANAGER   | 2975.00 |
|     20 | CLERK     | 1100.00 |
|     20 | CLERK     |  800.00 |
|     30 | MANAGER   | 2850.00 |
|     30 | SALESMAN  | 1600.00 |
|     30 | SALESMAN  | 1500.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | CLERK     |  950.00 |
+--------+-----------+---------+
rows in set (0.00 sec)

select
  deptno,count(*) person,job,max(sal) max ,min(sal) min,avg(sal) avg
from
  emp
group by
  deptno,job
order by
  deptno,max desc,min desc,avg desc;

mysql> select deptno,count(*) person,job,max(sal) max ,min(sal) min,avg(sal) avg from emp group by deptno,job order by deptno,max desc,min desc,avg desc;
+--------+--------+-----------+---------+---------+-------------+
| deptno | person | job       | max     | min     | avg         |
+--------+--------+-----------+---------+---------+-------------+
|     10 |      1 | PRESIDENT | 5000.00 | 5000.00 | 5000.000000 |
|     10 |      1 | MANAGER   | 2450.00 | 2450.00 | 2450.000000 |
|     10 |      1 | CLERK     | 1300.00 | 1300.00 | 1300.000000 |
|     20 |      2 | ANALYST   | 3000.00 | 3000.00 | 3000.000000 |
|     20 |      1 | MANAGER   | 2975.00 | 2975.00 | 2975.000000 |
|     20 |      2 | CLERK     | 1100.00 |  800.00 |  950.000000 |
|     30 |      1 | MANAGER   | 2850.00 | 2850.00 | 2850.000000 |
|     30 |      4 | SALESMAN  | 1600.00 | 1250.00 | 1400.000000 |
|     30 |      1 | CLERK     |  950.00 |  950.00 |  950.000000 |
+--------+--------+-----------+---------+---------+-------------+
rows in set (0.00 sec)
    View Code

    不包括管理层信息

    select
  deptno,count(*) person,job,max(sal) max ,min(sal) min,round(avg(sal),2) avg
from
  emp
where
  job not in ('manager','president')
group by
  deptno,job
order by
  deptno,max desc,min desc,avg desc;

mysql> select deptno,count(*) person,job,max(sal) max ,min(sal) min,round(avg(sal),2) avg from emp where job not in ('manager','president') group by deptno,job order by deptno,max desc,min desc,avg desc;
+--------+--------+----------+---------+---------+---------+
| deptno | person | job      | max     | min     | avg     |
+--------+--------+----------+---------+---------+---------+
|     10 |      1 | CLERK    | 1300.00 | 1300.00 | 1300.00 |
|     20 |      2 | ANALYST  | 3000.00 | 3000.00 | 3000.00 |
|     20 |      2 | CLERK    | 1100.00 |  800.00 |  950.00 |
|     30 |      4 | SALESMAN | 1600.00 | 1250.00 | 1400.00 |
|     30 |      1 | CLERK    |  950.00 |  950.00 |  950.00 |
+--------+--------+----------+---------+---------+---------+
rows in set (0.00 sec)
    View Code

5.多表-连接查询

  1. 内连接（SQL99），薪水在2000~3000的人员和部门信息 
    SELECT
    ename,dname
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    sal >= 2000 AND sal <= 3000;
    
mysql> select ename,dname from emp e join dept d on e.deptno = d.deptno where sal >= 2000 and sal <= 3000;
+-------+------------+
| ename | dname      |
+-------+------------+
| CLARK | ACCOUNTING |
| JONES | RESEARCH   |
| SCOTT | RESEARCH   |
| FORD  | RESEARCH   |
| BLAKE | SALES      |
+-------+------------+
5 rows in set (0.00 sec)
    View Code

    图解内链接 

  2. 非等值内连接，员工的薪资等级 
    SELECT
    ename,grade
FROM
    emp
JOIN
    salgrade
ON
    emp.sal <= hisal AND emp.sal >= losal;

mysql> SELECT ename,grade FROM emp JOIN salgrade ON emp.sal <= hisal AND emp.sal >= losal;
+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| ALLEN  |     3 |
| WARD   |     2 |
| JONES  |     4 |
| MARTIN |     2 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| KING   |     5 |
| TURNER |     3 |
| ADAMS  |     1 |
| JAMES  |     1 |
| FORD   |     4 |
| MILLER |     2 |
+--------+-------+
14 rows in set (0.00 sec)
    View Code

    芝加哥的员工薪水等级

    mysql> SELECT
    -> ename,loc,grade
    -> FROM
    -> emp JOIN dept JOIN salgrade
    -> ON
    -> emp.sal <= salgrade.hisal AND emp.sal >= salgrade.losal AND emp.deptno = dept.deptno
    -> WHERE
    -> dept.loc = 'CHICAGO';
+--------+---------+-------+
| ename  | loc     | grade |
+--------+---------+-------+
| JAMES  | CHICAGO |     1 |
| WARD   | CHICAGO |     2 |
| MARTIN | CHICAGO |     2 |
| ALLEN  | CHICAGO |     3 |
| TURNER | CHICAGO |     3 |
| BLAKE  | CHICAGO |     4 |
+--------+---------+-------+
6 rows in set (0.00 sec)
    View Code

  3. 自连接，查询员工对应的领导名称

    SELECT
    e.ename,l.ename leader
FROM
    emp e JOIN emp l
ON
    e.mgr = l.empno
WHERE
    e.ename IN ('WARD','SMITH');

mysql> SELECT e.ename,l.ename leader FROM emp e JOIN emp l ON e.mgr = l.empno WHERE e.ename IN ('WARD','SMITH');
+-------+--------+
| ename | leader |
+-------+--------+
| SMITH | FORD   |
| WARD  | BLAKE  |
+-------+--------+
2 rows in set (0.00 sec)
    View Code

  4. 外连接，查询所有的员工名称和部门名称

    SELECT
  e.ename,d.dname
FROM
  emp e JOIN dept d
ON
  e.deptno = d.deptno;

mysql> select e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

SELECT
  e.ename,d.dname
FROM
  emp e RIGHT JOIN dept d
ON
  e.deptno = d.deptno;

mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
    View Code

     图解左外，图解右外

  5. 外连接，查询所有的员工与其领导名称

    SELECT
  e.ename,l.ename leader
FROM
  emp e JOIN emp l 
ON
  e.mgr = l.empno;

mysql> SELECT e.ename,l.ename leader FROM emp e JOIN emp l ON e.mgr = l.empno;
+--------+--------+
| ename  | leader |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)

SELECT
  e.ename,l.ename leader
FROM
  emp e LEFT JOIN emp l 
ON
  e.mgr = l.empno;

mysql> SELECT e.ename,l.ename leader FROM emp e LEFT JOIN emp l ON e.mgr = l.empno;
+--------+--------+
| ename  | leader |
+--------+--------+
| 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)
    View Code
  6. 其他类型
    纯左 
    select <>
from a
    left join b
    on a.key = b.key
where b.key is null;
    View Code
    完全 
    select <>
from a
    full outer b on a.key = b.key;
    View Code
    中空 
    select <>
from a
    full outer join b on a.key = b.key
where a.key is null or b.key is null;
    View Code

  7. 三表查询格式；

    查询出：某个部门所有员工相应的领导名称（左外、右外在这里是一样的效果，左在含义上是正确的）、以及部门地址、薪资水准，以薪资降序排列

    SELECT
  e.ename,l.ename,d.loc,sg.grade
FROM
  emp e
    INNER JOIN dept d ON e.deptno = d.deptno
    INNER JOIN  salgrade sg ON e.sal BETWEEN sg.losal AND sg.hisal
    LEFT JOIN  emp l ON e.mgr = l.empno
WHERE
  d.dname = 'SALES'
ORDER BY
  sg.grade DESC;

mysql> SELECT
    ->   e.ename,l.ename,d.loc,sg.grade
    -> FROM
    ->   emp e JOIN dept d
    -> ON
    ->   e.deptno = d.deptno JOIN  salgrade sg
    -> ON
    ->   e.sal BETWEEN sg.losal AND sg.hisal LEFT JOIN  emp l
    -> ON
    ->   e.mgr = l.empno
    -> WHERE
    ->   d.dname = 'SALES'
    -> ORDER BY
    ->   sg.grade DESC;
+--------+-------+---------+-------+
| ename  | ename | loc     | grade |
+--------+-------+---------+-------+
| BLAKE  | KING  | CHICAGO |     4 |
| ALLEN  | BLAKE | CHICAGO |     3 |
| TURNER | BLAKE | CHICAGO |     3 |
| WARD   | BLAKE | CHICAGO |     2 |
| MARTIN | BLAKE | CHICAGO |     2 |
| JAMES  | BLAKE | CHICAGO |     1 |
+--------+-------+---------+-------+
ROWS IN SET (0.00 sec)
    View Code

6.子查询

使用：常常是把分组结果作为条件的应用，以及中间表与实体表的再查询；

  1. 薪资高于平均薪资的员工名称，员工岗位（where子句的子查询） 
    mysql> select ename,job from emp where sal > (select avg(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| JONES | MANAGER   |
| BLAKE | MANAGER   |
| CLARK | MANAGER   |
| SCOTT | ANALYST   |
| KING  | PRESIDENT |
| FORD  | ANALYST   |
+-------+-----------+
6 rows in set (0.00 sec)
    View Code
  2. 部门的薪资等级查询（from子句的子查询） 
    SELECT
  t.dname,sg.grade
FROM
  salgrade sg JOIN (
            SELECT
              d.dname,AVG(e.sal) avg_sal
            FROM
              emp e JOIN dept d
            ON
              e.deptno = d.deptno
            GROUP BY
              d.deptno
        ) t
ON
  t.avg_sal BETWEEN sg.losal AND sg.hisal
ORDER BY
  grade;

mysql> SELECT
    ->   t.dname,sg.grade
    -> FROM
    ->   salgrade sg JOIN (
    -> SELECT
    ->   d.dname,AVG(e.sal) avg_sal
    -> FROM
    ->   emp e JOIN dept d
    -> ON
    ->   e.deptno = d.deptno
    -> GROUP BY
    ->   d.deptno
    -> ) t
    -> ON
    ->   t.avg_sal BETWEEN sg.losal AND sg.hisal
    -> ORDER BY
    ->   grade;
+------------+-------+
| dname      | grade |
+------------+-------+
| SALES      |     3 |
| ACCOUNTING |     4 |
| RESEARCH   |     4 |
+------------+-------+
3 rows in set (0.00 sec)
    View Code

7.查询分页

  1. 语法：LIMIT [ start_num, ] length

    语法测试

    　　查询起始 n 行：SELECT * FROM emp LIMIT n 
    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-07-13 | 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-07-13 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
rows in set (0.00 sec)

mysql> SELECT * FROM emp LIMIT 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| 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 |
+-------+-------+----------+------+------------+---------+--------+--------+
rows in set (0.00 sec)

mysql> SELECT * FROM emp LIMIT 3,2;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
rows in set (0.00 sec)
    View Code

    　　开始默认为0

  2. 分页查询，每页查询出 3 行数据 
    mysql> SELECT * FROM emp LIMIT 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| 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 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM emp LIMIT 3,3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM emp LIMIT 6,3;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
    View Code

    LIMIT （页数-1）*长度，长度

8.约束

  1. 约束类型（表级、列级）
    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
  2. 水水

9.事务

  1.  事务，数据库操作逻辑单元，由一组SQL语句组成，逻辑单元的执行结果没有中间态。事务满足ACID属性。
  2. 原子性：逻辑单元不可再分
  3. 一致性：数据库状态
  4. 隔离性：数据库操作的各个逻辑单元在执行时互不干扰
  5. 持久性：逻辑单元执行完成后，执行结果完整保留

  6. 示例
    事务的结束，要么提交成功、要么回滚 
    #关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

#开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)

mysql> update emp3 set sal = sal + 500 where ename = 'ford';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 3500.00 |
+---------+
1 row in set (0.00 sec)

mysql> update emp3 set sal = sal + 500 where ename = 'ford';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 4000.00 |
+---------+
1 row in set (0.00 sec)

mysql> update emp3 set sal = sal + 500 where ename = 'ford';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 4500.00 |
+---------+
1 row in set (0.00 sec)

#回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)
    View Code

    修改事务的隔离级别（会话级环境变量）

    mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> set tx_isolation=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%iso%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
row in set (0.00 sec)

mysql> set tx_isolation=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
row in set (0.00 sec)

mysql> set tx_isolation=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> set tx_isolation=3;
Query OK, 0 rows affected (0.00 sec)

#修改结果
mysql> show variables like '%iso%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
row in set (0.00 sec)

mysql> set tx_isolation=4;
ERROR 1231 (42000): Variable 'tx_isolation' can't be set to the value of '4'

mysql> set tx_isolation=4;
ERROR 1231 (42000): Variable 'tx_isolation' can't be set to the value of '4'

#当前显示的环境变量为 session 级别变量
mysql> show session variables like '%iso%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

#全局不影响
mysql> show global variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
    View Code

    savepoint

    mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 3500.00 |
+---------+
1 row in set (0.00 sec)

#p3500
mysql> savepoint p3500;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp3 set sal = 4700 where ename = 'ford';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#p4700
mysql> savepoint p4700;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp3 set sal = 5300 where ename = 'ford';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#p5300
mysql> savepoint p5300;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp3 set sal = 6100 where ename = 'ford';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#p6100
mysql> savepoint p6100;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 6100.00 |
+---------+
1 row in set (0.00 sec)

#返回
mysql> rollback to p5300;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 5300.00 |
+---------+
1 row in set (0.00 sec)

#不能向前
mysql> rollback to p6100;
ERROR 1305 (42000): SAVEPOINT p6100 does not exist
mysql> rollback to p4700;
Query OK, 0 rows affected (0.00 sec)

mysql> select sal from emp3 where ename = 'ford';
+---------+
| sal     |
+---------+
| 4700.00 |
+---------+
1 row in set (0.00 sec)

mysql>
    View Code

10.视图

  1. 创建、修改、删除
    创建、修改视图 
    mysql> create view v_emp2
    -> as
    -> select ename,job,sal,comm from emp2;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view v_emp2G
*************************** 1. row ***************************
                View: v_emp2
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_emp2` AS select `emp2`.`ename` AS `ename`,`emp2`.`job` AS `job`,`emp2`.`sal` AS `sal`,`emp2`.`comm` AS `comm` from `emp2`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> desc v_emp2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| ename | varchar(15)  | YES  |     | NULL    |       |
| job   | varchar(10)  | YES  |     | NULL    |       |
| sal   | decimal(7,2) | YES  |     | NULL    |       |
| comm  | decimal(7,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from v_emp2;
Empty set (0.00 sec)

mysql> show index from emp2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp2  |          0 | PRIMARY  |            1 | empno       | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| emp2  |          1 | nhs      |            1 | ename       | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
| emp2  |          1 | nhs      |            2 | hiredate    | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
| emp2  |          1 | nhs      |            3 | sal         | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
rows in set (0.00 sec)
    View Code

    删除、查看视图

    mysql> show tables;
+----------------+
| Tables_in_hope |
+----------------+
| bonus          |
| dept           |
| dept2          |
| dept3          |
| emp            |
| emp2           |
| emp3           |
| salgrade       |
| salgrade2      |
| v_emp2         |
| v_emp3         |
+----------------+
11 rows in set (0.00 sec)

#删除视图
mysql> drop view v_emp2,v_emp3;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_hope |
+----------------+
| bonus          |
| dept           |
| dept2          |
| dept3          |
| emp            |
| emp2           |
| emp3           |
| salgrade       |
| salgrade2      |
+----------------+
9 rows in set (0.00 sec)
    View Code
  2. 视图限制
    ①、不能from自居包含子查询
    ②、不能引用系统变量或用户变量
    ③、在程序中，不能引用程序参数或局部变量
    ④、必须存在定义中引用的任何表或视图
    ⑤、定义不能引用 临时表
    ⑥、不能与触发器关联
    ⑦、别名最大长度比表名短（64，not 256）

  3. 视图数据修改
    数据更新、插入、删除 
    #数据更新
mysql> update v_emp2 set sal = 1800 where ename = 'smith' and job = 'clerk' and comm is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp2 where ename = 'smith';
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 1800.00 | NULL |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

mysql> select * from v_emp2 where ename = 'smith';
+-------+-------+---------+------+
| ename | job   | sal     | comm |
+-------+-------+---------+------+
| SMITH | CLERK | 1800.00 | NULL |
+-------+-------+---------+------+
1 row in set (0.00 sec)

#数据插入
mysql> insert into v_emp2 values ('argor','java','2200',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from v_emp2 where ename = 'argor';
+-------+------+---------+------+
| ename | job  | sal     | comm |
+-------+------+---------+------+
| argor | java | 2200.00 | NULL |
+-------+------+---------+------+
1 row in set (0.00 sec)

mysql> select * from emp2 where ename = 'argor';
+-------+-------+------+------+----------+---------+------+--------+
| empno | ename | job  | mgr  | hiredate | sal     | comm | deptno |
+-------+-------+------+------+----------+---------+------+--------+
|  7935 | argor | java | NULL | NULL     | 2200.00 | NULL |   NULL |
+-------+-------+------+------+----------+---------+------+--------+
1 row in set (0.00 sec)

#数据删除
mysql> delete from v_emp2 where ename = 'argor';
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp2 where ename = 'argor';
Empty set (0.00 sec)

mysql> select * from v_emp2 where ename = 'argor';
Empty set (0.00 sec)
    View Code

11.存储例程

  1. 语法格式： 
    ##### Syntax: #####
CREATE
    PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body

CREATE
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

routine_body:
    Valid SQL routine statement
    
##### Demo 1：存储过程 #####
mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
row in set (0.00 sec)

##### Demo 2：存储函数 #####
mysql> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
row in set (0.00 sec)
    View Code
  2. 存储过程
    创建 空参存储过程（仅仅把SQL语句放进去），创建表并且插入数据 
    DELIMITER //
CREATE PROCEDURE demo ()
    BEGIN
    DROP TABLE IF EXISTS `bonus`;

    CREATE TABLE `bonus` (
      `ename` VARCHAR(10) DEFAULT NULL COMMENT '雇员姓名',
      `job` VARCHAR(9) DEFAULT NULL COMMENT '雇员职位',
      `sal` DECIMAL(7,2) DEFAULT NULL COMMENT '雇员工资',
      `comm` DECIMAL(7,2) DEFAULT NULL COMMENT '雇员资金'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='工资表';

    DROP TABLE IF EXISTS `dept`;

    CREATE TABLE `dept` (
      `deptno` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门编号',
      `dname` VARCHAR(15) DEFAULT NULL COMMENT '部门名称',
      `loc` VARCHAR(50) DEFAULT NULL COMMENT '部门所在位置',
      PRIMARY KEY (`deptno`)
    ) ENGINE=INNODB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COMMENT='部门表';

    INSERT  INTO `dept`(`deptno`,`dname`,`loc`) VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

    DROP TABLE IF EXISTS `emp`;

    CREATE TABLE `emp` (
      `empno` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '雇员编号',
      `ename` VARCHAR(15) DEFAULT NULL COMMENT '雇员姓名',
      `job` VARCHAR(10) DEFAULT NULL COMMENT '雇员职位',
      `mgr` INT(10) UNSIGNED DEFAULT NULL COMMENT '雇员对应的领导的编号',
      `hiredate` DATE DEFAULT NULL COMMENT '雇员的雇佣日期',
      `sal` DECIMAL(7,2) DEFAULT NULL COMMENT '雇员的基本工资',
      `comm` DECIMAL(7,2) DEFAULT NULL COMMENT '奖金',
      `deptno` INT(10) UNSIGNED DEFAULT NULL COMMENT '所在部门',
      PRIMARY KEY (`empno`),
      KEY `deptno` (`deptno`),
      CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
    ) ENGINE=INNODB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8 COMMENT='雇员表';

    INSERT  INTO `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) VALUES (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-07-13','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-07-13','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);

    DROP TABLE IF EXISTS `salgrade`;

    CREATE TABLE `salgrade` (
      `grade` INT(10) UNSIGNED DEFAULT NULL COMMENT '工资等级',
      `losal` INT(10) UNSIGNED DEFAULT NULL COMMENT '此等级的最低工资',
      `hisal` INT(10) UNSIGNED DEFAULT NULL COMMENT '此等级的最高工资'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='工资等级表';

    INSERT  INTO `salgrade`(`grade`,`losal`,`hisal`) VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

    END//
    View Code

    创建带参存储过程（IN）

    DELIMITER //
CREATE PROCEDURE getSal (IN NAME VARCHAR(15))
    BEGIN
    SELECT sal FROM emp WHERE ename = NAME;
    END//    
DELIMITER ;

DELIMITER //
CREATE PROCEDURE getSalary (IN NAME VARCHAR(15))
    BEGIN
    SELECT sal + IF(comm IS NULL,0,comm) salary FROM emp WHERE ename = NAME;
    END//    
DELIMITER ;
    
CALL getSal('ward');
CALL getSal('ford');
CALL getSalary('ward');
CALL getSalary('ford');
    View Code

    创建带参存储过程（OUT）

    DELIMITER //
CREATE PROCEDURE getSalary (IN NAME VARCHAR(15), OUT salary DECIMAL(7,2))
    BEGIN
    SELECT sal + IF(comm IS NULL,0,comm) INTO salary FROM emp WHERE ename = NAME;
    END//    
DELIMITER ;
    

CALL getSalary('ward',@salary);
SELECT @salary;
    View Code

    查看、删除存储过程

    mysql> show procedure status like 'getSal';
+------+--------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name   | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| demo | getSal | PROCEDURE | root@%  | 2019-07-15 20:23:34 | 2019-07-15 20:23:34 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+--------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show procedure status like 'getSalary';
+------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name      | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| demo | getSalary | PROCEDURE | root@%  | 2019-07-15 20:27:09 | 2019-07-15 20:27:09 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

#查看 demo 库中所有存储过程
mysql> show procedure status where db = 'demo';
+------+------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name       | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| demo | getSal     | PROCEDURE | root@%  | 2019-07-15 20:23:34 | 2019-07-15 20:23:34 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| demo | getSalary  | PROCEDURE | root@%  | 2019-07-15 20:27:09 | 2019-07-15 20:27:09 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| demo | simpleproc | PROCEDURE | root@%  | 2019-07-15 19:21:37 | 2019-07-15 19:21:37 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> drop procedure getSal;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure getSalary;
Query OK, 0 rows affected (0.00 sec)
    View Code
  3. 存储函数
    空参，返回随机字母 
    DELIMITER //
CREATE FUNCTION getChar()
  RETURNS VARCHAR(1)
  BEGIN
    DECLARE chs TEXT DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE result VARCHAR(1) DEFAULT '';
    SET result = SUBSTR(chs,(ROUND(RAND()*25)+1),1);
    RETURN result;
  END//
    View Code

    查看、删除存储函数

    mysql> show function status where db = 'demo';
+------+---------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| demo | getChar | FUNCTION | root@%  | 2019-07-16 19:59:32 | 2019-07-16 19:59:32 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+---------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> drop function getchar;
Query OK, 0 rows affected (0.00 sec)
    View Code

12.流程控制

  1. 变量
    ①系统变量（要求super权限）
    MySQL 中系统变量划分：全局系统变量（GLOBAL），会话系统变量（SESSION *）
    show 命令，查看系统变量：（local是session的别名） 
    # 默认为session
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
row in set (0.00 sec)

mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show session variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
row in set (0.00 sec)

mysql> show local variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
row in set (0.00 sec)
    View Code

    select 命令，查看系统变量

    mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@local.tx_isolation;
+----------------------+
| @@local.tx_isolation |
+----------------------+
| READ-COMMITTED       |
+----------------------+
1 row in set (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.00 sec)
    View Code

    修改系统变量的值

    mysql> set tx_isolation=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
    View Code

     
    ②自定义变量

    mysql> set @myvar='argor';
Query OK, 0 rows affected (0.00 sec)

mysql> select @myvar;
+--------+
| @myvar |
+--------+
| argor  |
+--------+
1 row in set (0.00 sec)

mysql> set @mysalary = 1500;
Query OK, 0 rows affected (0.00 sec)

mysql> select @mysalary;
+-----------+
| @mysalary |
+-----------+
|      1500 |
+-----------+
1 row in set (0.00 sec)
    View Code

    局部变量（放在SQL代码块中）

    DELIMITER //
CREATE PROCEDURE setVar (IN NAME VARCHAR(15))
    BEGIN
    DECLARE nickname VARCHAR(15) DEFAULT NAME;
    SELECT nickname;
    END//    
DELIMITER ;

CALL setVar('argor');
    View Code

     分支结构

  2. if 函数 
    mysql> SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes                |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no                                    |
+---------------------------------------+
1 row in set (0.00 sec)
    View Code

    if 语句

    IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
    View Code

     
    case 操作

    mysql> SELECT CASE 1 
    -> WHEN 1 THEN 'one'
    -> WHEN 2 THEN 'two'
    -> ELSE 'more'
    -> END C1;
+-----+
| C1  |
+-----+
| one |
+-----+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT CASE 
    -> WHEN 1>0 THEN 'true' 
    -> ELSE 'false'
    -> END C2;
+------+
| C2   |
+------+
| true |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT CASE BINARY 'B'
    -> WHEN 'a' THEN 1
    -> WHEN 'b' THEN 2
    -> END C3;
+------+
| C3   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
    View Code

    case 语句

    DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |
    View Code

  3. 循环结构
    while循环：简单循环10次

    DELIMITER //
CREATE PROCEDURE doWhile()
    BEGIN
    DECLARE v INT DEFAULT 1;
    
    WHILE v < 10 DO
        SELECT v;
        SET v = v + 1;
    END WHILE;
    END//
DELIMITER ;
    View Code

    写个存储过程，使用while循环，测试存储函数的执行

      DELIMITER //
  CREATE PROCEDURE testFun()
    BEGIN
      DECLARE i INT DEFAULT 0;
      DECLARE v VARCHAR(3) DEFAULT '';
      
      WHILE i < 200 DO
        SELECT getChar();
        SET i = i + 1;
      END WHILE;
    END//
  DELIMITER ;
    View Code

    repeat 循环

    mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT
    ->     SET @x = @x + 1;
    ->   UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
    View Code

    loop 循环

    CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END;
    View Code

  4. 流程控制小实例 
    编写存储函数，根据参数返回字符串

    # 参数是返回字符串的长度
DELIMITER //
CREATE FUNCTION getStr(lengths INT) RETURNS TEXT
  BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE len INT DEFAULT LENGTH(result);
    DECLARE chs TEXT DEFAULT 'abcdefghijklmnopqrstuvwxyz1234567890';
    DECLARE randCh VARCHAR(1) DEFAULT '';
    
    IF lengths < 0 THEN
      SET lengths=0;
    END IF;
    WHILE len < lengths DO
      SET randCh=SUBSTR(chs, (FLOOR(1+RAND()*LENGTH(chs))),1);
      SET result=CONCAT(result, randCh);
      SET len=len+1;
    END WHILE;
    
    RETURN result;    
  END//
DELIMITER ;

#参数返回字符串的长度，返回字符串的字符集合
DELIMITER //
CREATE FUNCTION getStr(lengths INT, chs TEXT) RETURNS TEXT
  BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE len INT DEFAULT LENGTH(result);
    DECLARE randCh VARCHAR(1) DEFAULT '';
    
    IF LENGTH(chs) < 1 then
      return '';
    END IF;
    
    IF lengths < 0 THEN
      SET lengths=0;
    END IF;
    WHILE len < lengths DO
      SET randCh=SUBSTR(chs, (FLOOR(1+RAND()*LENGTH(chs))),1);
      SET result=CONCAT(result, randCh);
      SET len=len+1;
    END WHILE;
    
    RETURN result;    
  END//
DELIMITER ;
    View Code

    创建简单表，使用存储过程插入数据

    # Create table bigvarchar
CREATE TABLE bigvarchar(
NAME VARCHAR,
nick VARCHAR
);

# Create Big Table By Varchar.
DELIMITER //
CREATE PROCEDURE cBTV(IN len INT)
  BEGIN
    DECLARE tmp TEXT DEFAULT '';
    DECLARE i INT DEFAULT 0;
    
    WHILE i < len DO
      INSERT INTO bigvarchar VALUES(getStr(8), getStr(8));
      SET i=i+1;
    END WHILE;
    
  END//
DELIMITER ;
    View Code

13.数据类型

    1. 数值型
      整型数
      整型数
      类型 字节 范围
      tinyint 1

      有符号：-27~27-1

      无符号：0~28-1
      smallint 2

      有符号：

      无符号：0~216-1
      mediumint 3

      有符号：

      无符号：
      int 4

      有符号：

      无符号：0~232-1
      bigint 8

      有符号：

      无符号：0~264-1

      定点数
      定点数
      类型 字节 范围
      decimal(M, D) M+2

      浮点数
      浮点数
      类型 字节 范围
      float(M, D) 4  
      double(M, D) 8  
  2. 字符型
    char
    varchar
    text
    blob

  3. 日期类型
    日期类型
    类型 字节 最小值 最大值
    date 4 1000-01-01

    9999-12-31
    datetime 8 1000-01-01 00:00:00

    9999-12-31 23:59:59
    timestamp 4 1970

    2038
    time 3 -838:59:59

    838:59:59
    year 1 1901

    2155

MySQL .

一切代码都是为了生活，一切生活都是调剂
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/argor/p/9019650.html