MySQL学习【2】

【1】子查询（sub query）

举例如下：
先来摆出我们的两张表（一张员工表emp，一张部门表dept）

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.08 sec)

情况1：把子查询当做一个值：分解问题

求员工表内的最高工资的员工信息

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.23 sec)

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 找的其实是一个值。之后由最大值5000来找的对应的员工
用一句sql 语句来写就是：

mysql> select * from emp where sal=(select max(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.04 sec)

情况2：把子查询当做一张表

求每个部门的最高工资的员工

//首先求出的是每个部门的最高工资

mysql> select max(sal) from emp group by deptno;
+----------+
| max(sal) |
+----------+
|  5000.00 |
|  3000.00 |
|  2850.00 |
+----------+
3 rows in set (0.07 sec)

//按照内连接的方法，将emp表和生成的新表进行连接，根据条件获取对应的值

mysql> select * from emp e inner join (select max(sal) msal,deptno from emp group by deptno) a on e.deptno=a.deptno and e.sal=a.msal;
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno | msal    | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 | 2850.00 |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000.00 | NULL |     20 | 3000.00 |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 | 5000.00 |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 | 3000.00 |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
4 rows in set (0.06 sec)

【2】case when

可以配合select 使用，把一列的取值根据不同的条件进行翻译，类似于java 中的if else if
== 语法 ：
case
when 条件1 then 结果1
when 条件2 then 结果2
…
else 结果n
end
==
举例如下：

mysql> select sc.sid,sc.cid,sc.score,
    -> (case
    -> when sc.score<50 then '不及格'
    -> when sc.score>50 and sc.score<80 then '良好'
    -> else '优秀'
    -> end
    -> )等级
    -> from sc;
+------+-----+-------+--------+
| sid  | cid | score | 等级   |
+------+-----+-------+--------+
| 1001 |   1 |    50 | 优秀   |
| 1002 |   1 |    90 | 优秀   |
| 1003 |   1 |    59 | 良好   |
| 1004 |   1 |    80 | 优秀   |
| 1005 |   1 |    50 | 优秀   |
| 1006 |   1 |    60 | 良好   |
| 1007 |   1 |   100 | 优秀   |
| 1001 |   2 |    70 | 良好   |
| 1003 |   2 |    70 | 良好   |
| 1004 |   2 |    70 | 良好   |
| 1005 |   2 |    50 | 优秀   |
| 1001 |   3 |    80 | 优秀   |
| 1005 |   3 |   100 | 优秀   |
| 1001 |   4 |    90 | 优秀   |
| 1002 |   4 |    55 | 良好   |
| 1005 |   4 |    30 | 不及格 |
| 1007 |   4 |    58 | 良好   |
| 1001 |   5 |    80 | 优秀   |
| 1001 |   6 |    80 | 优秀   |
| 1002 |   6 |    90 | 优秀   |
| 1007 |   6 |    90 | 优秀   |
| 1001 |   7 |   100 | 优秀   |
| 1002 |   7 |    80 | 优秀   |
| 1007 |   7 |    80 | 优秀   |
+------+-----+-------+--------+
24 rows in set (0.07 sec)

【3】DCL

grant授权

语法：
创建一个新用户：
create user 用户名 identified by ‘密码’

mysql> create user 'user1' identified by 'user1';
Query OK, 0 rows affected (0.27 sec)

此时这个用户就可以使用了，只不过他没有操作我们数据库的权限：
这个账号和密码是，可以登录的：

C:\Windows\system32>mysql -uuser1 -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

我们来试着查询一下：可以看到除了一个mysql自带的数据库其他都不显示

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

授权举例如下：比如将查询student库中的查询权限授权给新用户：

 grant select on school.* to user1;
Query OK, 0 rows affected (0.00 sec)

意思就是说，将student表下所有数据的查询权限都授予user1表
我们这个时候再来登录user1的账号来查看一下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| school             |
+--------------------+
2 rows in set (0.00 sec)
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| dept             |
| emp              |
| fs               |
| sc               |
| student          |
| teacher          |
| test             |
| test2            |
| user             |
+------------------+
10 rows in set (0.00 sec)

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

但是如果没有给user1插入权限的话，执行insert into的话就会报错

mysql> insert into student(sid,sname,birthday,sex) values(1,'孙梅','1999-9-9','女');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'student'

revoke回收权限

语法
revoke 权限 from 用户名

mysql> revoke select on school.* from user1;
Query OK, 0 rows affected (0.00 sec)

然后我们在user1里面再次查看

C:\Windows\system32>mysql -u user1 -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

最终发现已经没有查询的权限了

【4】事务和锁（TCL事物控制语言）

事务

首先我们来了解一下事务和锁的引入
transaction事务：把多余sql语句视为一个整体执行
举例：两个人同时在一个表中操作同一条数据
mysql的事务控制：默认情况下是一条语句一个事务
如果要多条语句一个事务,需要begin commit rollback进行控制事务
举例如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student(sid,sname,birthday,sex) values(1,'筱筱','1999-09-08','女');
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
9 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from student;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
9 rows in set (0.00 sec)

大家发现了什么？？？？？？？？为什么在没有commit之前表里都已经有了我们插入的数据？？？？？？？难道commit没什么用？？？？？？？当然有用，大家请看下面

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

mysql> insert into student(sid,sname,birthday,sex) values(2,'刘允','1999-09-18','男');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
|    2 | 刘允  | 1999-09-18 | 男   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
10 rows in set (0.00 sec)

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

mysql> select * from student;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
9 rows in set (0.00 sec)

这下清楚了吧，只要我们没有commit，之前的所有数据都是可以通过rollback反悔的

锁

分类：
【1】InnoDB行级锁：以行为单位的锁，只要两个客户端更新的是不同行的数据，则互不干扰
【2】MyISAM表锁：锁住的是整个表
这里我们要了解的是mysql对查询有特别的优化：不用锁也能实现并发访问，即多版本并发访问
这里举加共享锁的例子：
首先加的是一个共享锁，，别人也能查，能加共享锁。但是不能再加排他锁

mysql> select * from student lock in share mode;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
9 rows in set (0.00 sec)

接下来举一个加排它锁的例子
添加排他锁，别人就不能加共享锁和排他锁了

mysql> select * from student for update;
+------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
|    1 | 筱筱  | 1999-09-08 | 女   |
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
| 1010 | 夏木  | 1998-08-08 | 女   |
+------+-------+------------+------+
9 rows in set (0.00 sec)

【5】事务隔离性

不同的事物有不同的隔离级别：
隔离级别越低，并发性越好，但数据的一致性就差
隔离级别越高，并发性越差，但数据的一致性就高
有四种级别：读未提交<读提交<可重复读<序列化读
错误级别（由高到低）：脏读，不可重复读，幻读

【6】其他sql总结

查看建表语句：show create table 表名;
把表行转列：\G结束可以取代“；”
查看系统变量的值：举例：select @@port;查看的是端口号