mysql进阶

 1.select 查询

select 列名... from 表名 where 条件;

where条件：

1.1 比较运算

  表示相等的 =
  大于 >
  大于等于 >=
  小于  <
  小于等于 <=
  不等于 !=

1.2 逻辑运算 and（并且） or（或者） not(取反)

  -- 查询工资大于800 并且 部门等于20
  where sal>800 and deptno=20
  -- 部门=20 或者 等于30的
  where deptno=20 or deptno=30;

1.3 null值

数据库中的null ，比较特殊，不能使用=来比较, 必须用 is null
  例如：查询佣金为null的员工
  select * from emp where comm is null;
  例如：查询佣金不为null的员工
  select * from emp where comm is not null;

1.4 like (模糊查询)

   例如查询以 ‘S’字母打头的员工
   
select * from emp where ename like 'S%';

通配符 % 表示匹配0~多个任意的字符
通配符 _ 表示匹配一个任意字符

1.5 between ... and ... 范围匹配

求工资大于等于1000， 小于等于2000的员工
select * from emp where sal>=1000 and sal<=2000;

select * from emp where sal between 1000 and 2000;
1) 必须按有小到大顺序
2) between 包含等于条件

1.6 in 求这一列是否在一个值列表内

求所有销售人员和经理
select * from emp where job='SALESMAN' or job='MANAGER';

select * from emp where job in ('SALESMAN', 'MANAGER');

1.7 limit (mysql特有的)

用来限制返回的结果数
1) limit n;       n表示最多返回几条记录
2) limit m, n;    m代表偏移量（下标） 注意偏移量从0开始

1.8 order by 用来给结果排序

语法： order by 列名 asc|desc, 列名 asc|desc, ...
例如：按工资升序：
select * from emp order by sal;
select * from emp order by sal asc;
按工资降序：
select * from emp order by sal desc;
其中如果不写asc或desc，默认升序

如果多列排序，如果第一列取值相同，再按照第二列进行排序，例如：
先按工资排序，工资相同的，再按姓名排序：
select * from emp order by sal asc, ename asc; /*如果升序，两个asc都可以省略*/

1.9. select 语句的顺序：

select ... from 表 where 条件 order by 排序 limit 限制

 2. 函数

例如 lower 函数把字符变成小写
     upper 函数把字符变成大写
     
常用的函数 :
1) 时间加减
date_add(原始时间, 时间间隔)
    时间间隔的语法：  interval 数字 时间单位
    数字可以是正数或负数

select date_add(now(), interval 1 day );

2) 提取时间的某个部分
extract(时间部分 from 原始时间)
select extract(year from now());
select extract(year_month from now());
返回的结果是一个数字类型

例：要获取1981年入职的员工

select * from emp where extract(year from hiredate)=1981;

3)类型转换
select cast('11' as signed)+1;
select cast('12.55555' as decimal(5,2));

4)拼接字符串
concat(值1, 值2, ... 值n)
select concat('a', 'b', 'c');
select concat('a', 18, 'c'); /*可以把其它类型当做字符串拼接*/

5) 求长度的函数
char_length 按字符为单位统计长度
length      按字节为单位统计长度
utf8mb4编码下，汉字一个字符占三个字节，英文一个字符占一个字节

6) 标准sql：

case
    when 条件1 then 值1
    when 条件2 then 值2
    ...
    else 值n
end

整个case...end 最后返回一个值

select sal, 
case 
    when sal>2000 then '高工资'
    when sal between 1000 and 2000 then '中等'
    else '低工资'
end 工资水平 from emp;

7) 组函数

最大值 max(列)
最小值 min(列)
和     sum(列)
个数   count(列)  会排除null值
       count(*)   针对这次查询，看看一共有多少行
平均值 avg(列)

8) 去除重复
distinct
select distinct job from emp; /*去除重复后的职位有哪些*/
select count(distinct job) from emp; /*有几种职位*/

9) 查询帮助
? contents   总目录

4. 分组语法

(group by 后的列取值相同的会被分为一组)
select ... from 表 group by 列1,列2... having 分组条件

select deptno,count(*),max(sal) from emp group by deptno;
把部门编号取值相同的分为一组, 配合组函数一起使用
 

5.连接查询

 

语法：
                                  内    连接
    select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)
                                    左    外  
    select ... from 表1 left [outer] join 表2 on 连接条件 (重点)
                                   右    外
    select ... from 表1 right [outer] join 表2 on 连接条件
                                 全
    select ... from 表1 full join 表2 on 连接条件 (mysql不支持全连接

 

内连接 ：
    例：连接部门表和员工表
    select * from emp a inner join dept b on a.deptno=b.deptno;
    注意：
    1) 内连接是将两张表中所有符合连接条件的数据列入结果，不符合连接条件的结果中没有，例如40号部门
    2) 如果连接的两表中有同名的列，列前面要加表名(或表别名)来区分（否则会报歧义错误）
    3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法：        
    select ... from 表1, 表2 where 连接条件;        
   例： 还是连接部门表和员工表 select * from emp a, dept b where a.deptno=b.deptno; 
        

左外连接：
    例子：采用左外连接员工表和部门表
    select * from emp a left join dept b on a.deptno = b.deptno;
    例子：采用左外连接部门表和员工表
    select * from dept b left join emp a on a.deptno = b.deptno;
    左外连接，首先将符合连接条件的记录连在一起，作为结果，其次左边表中不符合连接条件的记录也会出现在结果中，只不过它对应的右边的列都是NULL
    
右外连接：

例子：
    select * from emp a right join dept b on a.deptno = b.deptno;
    
    注意：左外和右外与表的先后次序有关，而内连接与表的先后次序无关

 5.2多表连接

    select * from 表1 
        inner join 表2 on 连接条件
        inner join 表3 on 连接条件
        ... 
    例子
    select * from student a inner join sc b on a.sid = b.sid
                             inner join course c on b.cid = c.cid
                             inner join teacher d on c.tid = d.tid
                             order by a.sid, b.cid;
    等价写法：
    select * from student a, sc b, course c, teacher d
                 where a.sid=b.sid and b.cid=c.cid and c.tid=d.tid;
    
    左外多表连接：
    select * from student a left join sc b on a.sid = b.sid
                             left join course c on b.cid = c.cid
                             left join teacher d on c.tid = d.tid
                             order by a.sid, b.cid;
    注意：左外多表连接要全部使用left join，不能再出现inner join
    
    性能上：连接的表越多，性能越低, 可以把连接查询变成分多次查询

5.3 自连接

    一个表自己和自己连接
    找到员工的姓名和上级的姓名
    select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
     from emp a left join emp b on a.mgr=b.empno;
    
    以后经常用于树状结构的数据表示
    陕西省
        西安
            雁塔区
            高新区
        咸阳
        宝鸡

可以利用parent_id建立联系，避免自连接查询
    id    name     parent_id
    1     陕西省   null
    2     西安     1
    3     雁塔区   2

5.4 子查询

把某个select结果当做一个值，或一张表做进一步的查询

情况1：找具有最高工资的员工信息（子查询作为值）

select max(sal) from emp; // 5000

select * from emp where sal = (select max(sal) from emp);

把select max(sal) from emp当成了一个值，代入到主查询当中，代入时需要在子查询的两边加()

情况2： 获取每个部门的平均工资和部门的名称（子查询作为表）
先查询平均工资
(select deptno,avg(sal) from emp group by deptno) a

再把子查询看做临时表，与其它表做表连接
select * from (子查询)a inner join dept b on a.deptno=b.deptno;

练习：
1) 查询所有同学的学号、姓名、选课数、总成绩

   (select sid, count(*),sum(score) from sc group by sid) a
   先从sc表中将学号，选课数和总成绩查出来，将其结果单做一张表a
   select * from (select sid, count(*),sum(score) from sc group by sid) a 
    inner join student b on a.sid=b.sid;
    
   select a.*,b.sname from (select sid, count(*) 选课数,sum(score) 总成绩 from sc group by sid) a 
    inner join student b on a.sid=b.sid;内连接a表和student表按学号查出学生姓名

2) 查询学生平均成绩大于80的所有学生的学号、姓名和平均成绩 
   (select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80)) a
   先从sc表中找出学生id，学号和平均成绩，结果作为表a
   select a.*, b.sname from (select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80))a 
    inner join student b on a.sid=b.sid; 内连接a表和student表，找到学生姓名

3) 查询课程相同且成绩相同的的学生的学号、课程号、学生成绩
    (select cid,score from sc group by cid,score having count(*) > 1) a
    要在同一张表中找成绩和课程相等的学生，自连接会有很多重复记录，我们可以按课程号和成绩分组，记录数大于1说明有多个学生有相同的课程和成绩，结果取个别名a
    select * from (select cid,score from sc group by cid,score having count(*) > 1)a 
        inner join sc b on a.cid=b.cid and a.score=b.score;
   有了相同的课程号和成绩，只要找出课程和成绩与此相等的学生即可
    
4) 查询每个部门有最高工资的员工的（所有）信息
(select deptno,max(sal) msal from emp group by deptno) a

查出每个部门的部门编号和最高工资

select b.* from (select deptno,max(sal) msal from emp group by deptno)a 
    inner join emp b on a.deptno=b.deptno and a.msal=b.sal;
    再从职员表中查出拥有最高工资的员工
    
情况3： 将子查询当做一个函数 (了解)

(select max(sal) from emp where deptno =?)

m(deptno)   返回结果是这个deptno下的最大工资

部门编号是一个入参, 最大工资是返回结果

select * from emp e where sal = m(e.deptno);  // 伪代码

select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno); 

6. 事务

DDL： create alter drop truncate
DML： insert update delete select 
TCL： start transaction, commit, rollback

## 6.1 start transaction 开始事务 (begin)
## 6.2 commit 提交事务 
## 6.3 rollback 回滚事务

账户表account
ID      balance(余额)
1       50000.0       
2       0.0           

create table account (
 id int primary key,
 balance decimal(12,2) not null
); 

insert into account(id,balance)values(1,50000.0),(2,0.0);

以下两条sql必须作为一个整体执行, 要么都成功，其中有一条失败，前面成功的也得撤销
update account set balance=balance+10000.0 where id=2; /*2号账户转入10000元*/ 成功
update account set balance=balance-10000.0 where id=1; /*1号账户转出10000元*/

所谓的事务，就是指一个事务内，多条sql语句是作为一个整体执行的。
一个事务内的多条sql是作为一个原子操作，不可以被分割。要么都成功，要么都不成功。

start TRANSACTION;
UPDATE
UPDATE
INSERT
DELETE
如果这个事务内多条sql全部成功 COMMIT(让更改都生效)
如果这个事务内有sql失败了，Rollback（让更改都撤销）

事务内所有更改，在结束之前，对于其它用户来讲都是不可见的。
事务commit提交时，这些更改才会真正生效，其它用户才能看到你的更改。
事务执行中如果出现意外情况，这时候可以执行rollback，可以撤销事务内所有更改，恢复到事务开始的时刻
commit 和rollback都意味着事务结束

事务有四大特性
ACID
A 原子性, 指事务内多条sql是作为一个整体执行
C 一致性, 事务开始前后，整个数据的状态应当一致
 I 隔离性, 指事务的隔离级别(未提交读，提交读，可重复读，序列化读)
    1) 脏读(读取到了未提交的数据)
    客户1                         客户2
    1 号账户余额 10000.0
    begin;
    update 1 号账户余额50000.0
                                  select 1 号账户余额 50000.0 脏读
    rollback;                                 
                                    select 1 号账户余额 50000.0
    
    
    2)避免脏读现象, 将隔离级别升级为提交读
    查询到的肯定是别人提交后的结果，提交读下不会有脏读,    但会有不可重复读现象:
    客户1 更新                   客户2查询
    1 号账户余额 10000.0
                                  begin;
                                  select ... 10000.0
    begin;
    update 1 号账户余额50000.0;
    commit;
                                  select ... 50000.0
                                  commit;
    
    3) 为了避免不可重复和脏读的现象，可以将隔离级别升级可重复读（mysql默认隔离级别）    
    4) 幻读 (可以将隔离级别提高为序列化读，即可避免幻读现象)     
    
    客户1 新增                    客户2查询
    原始记录是10条
    begin                          begin;
                                    查询个数  10
    insert 1
    commit;    
                                    查询个数  11
                                    
                                    commit;
    
    5) mysql的【可重复读】隔离级别三种现象都可以避免
    
    
D 持久性, 事务中做的更改必须在事务结束后永久生效

7. DCL 数据控制语言 (了解)

grant 授权
revoke 回收权限

create user 'user1'@'localhost' identified by 'user1';
登录之后执行use test3;
会报告  Access denied for user 'user1' 含义是用户无权访问.

使用root 给user1授权
grant all on test3.* to 'user1'@'localhost'; 
all 是代表所有权限：select,insert,update,delete...
test3.* 是权限的范围：test3库中所有对象
to 后面跟的是用户

使用root回收权限
revoke all on test3.* from 'user1'@'localhost';

更细的权限分配
grant select on test3.student to 'user1'@'localhost';  /*只让test1用户能够查询test3.student表*/