MySQL概述

MySQL最初是由“MySQL AB”公司开发的一套关系型数据库管理系统（RDBMS-Relational Database Mangerment System）。

发展历史：

• MySQL AB是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson和Michael “Monty” Widenius在瑞典创办的。
• 在2008年初，Sun Microsystems收购了MySQL AB公司。在2009年，Oracle收购了Sun公司，使MySQL并入Oracle的数据库产品线。

MySQL常用命令

MySQL程序选项具有以下两种通用形式：
》长选项，由单词之前加两个减号组成
》短选项，由单个字母之前加一个减号组成

在MySQL程序中，常用的命令如下：

• 终止一条语句

  如果想要终止一条正在编写的语句，可键入\c

• 退出MySQL ：\q、quit、exit

• 执行SQL脚本

  • source 文件路径
  • sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

• 查看mysql版本

  • mysql -V
  • mysql --version

• 创建数据库

  • create database 数据库名称

• 使用数据库

  • use 数据库名称

• 查询当前使用的数据库

  • select database();
  • 查询数据库版本也可以使用：select version();

• 查看现有的数据库

  • show databases

• 查看当前库中的表

  • show tables

• 查看其他库中的表

  • show talbes from 数据库名称

• 查看表的结构

  • desc 表名

• 查看表的创建语句

  • show create table 表名

• 删除数据库

  • drop database 数据库名

MySQL语法问题

大小写问题

MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写，结果再 想要将数据库导出放到linux服务器中使用时就出错了。因为在linux下表名区分大小写而找不到表，查了很多都是说在linux下更改MySQL的设置使其也不区分大小写，但是有没有办法反过来让windows 下大小写敏感呢。其实方法是一样的，相应的更改windows中MySQL的设置就行了。

​ 具体操作：

​ 在MySQL的配置文件my.ini中增加一行：

​ lower_case_table_names = 0

​ 其中 0：区分大小写，1：不区分大小写

​ MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的； MySQL在Windows下都不区分大小写

MySQL中标识符（数据库名、表名、字段名等）都可以加符号：` 。最好别用，因为不通用。

SQL的常见语法

• 字符串

  • 使用 ''，MySQL是支持" "的，但其他软件不支持。

• null

  • 表示空，即没有值，任何数据类型与null做运算，结果为null。
  • ifnull(A, B) ：空处理函数
    • 当A为null时，将B当作A

SQL的分类

1. 数据查询语言(DQL-Data Query Language)

   • 查询语句

   • 代表关键字:select

2. 数据操纵语言(DML-Data Manipulation Language)

   • 对表当中的数据进行增删改
   • 代表关键字:insert,delete,update

3. 数据定义语言(DDL-Data Definition Language)

   • 对表结构的增删改
   • 代表关键字:create ,drop,alter,

4. 事务控制语言(TCL-Transactional Control Language)

   • 操作事务
   • 代表关键字:commit ,rollback;

5. 数据控制语言(DCL-Data Control Language)

   • grant授权、revoke撤销权限等。
   • 代表关键字:grant,revoke.

表

• 表(table)是一种结构化的文件，可以用来存储特定类型的数据，是数据库的基本组成单元。
• 如：学生信息，课程信息，都可以放到表中。另外表都有特定的名称，而且不能重复。
• 使用目的：可读性强。
• 表中具有几个概念：列、行、主键。 列叫做字段(Column)，行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度

如：一个学生信息表：

创建表

• 语法格式

  create table 表名 (
  	字段名 字段数据类型(字段长度限制) 字段约束,
      字段名 字段数据类型(字段长度限制) 字段约束,
      ...
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  /*
  字段约束可以有多个。
  ENGINE=InnoDB DEFAULT CHARSET=utf8:这两个可以不写，有默认值。
  ENGINE：设置存储引擎，默认为InnoDB
  DEFAULT CHARSET：设置字符集，默认为utf8
  */
  

  创建表的时候，表中有字段，在()里，每一个字段有：字段名、字段数据类型、字段长度限制、字段约束

• 示例：

  建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

  create table t_student(
  	student_id int(10),/*可以在()里指定字段长度*/
  	student_name varchar(20),
  	sex char(2),
  	birthday date,
  	email varchar(30),
  	classes_id int(3)	
  )
  

MySql常用数据类型

char和varchar怎么选择？固定长度用char，不固定的用varchar

添加约束

(constraint)

添加约束目的：保证数据的完整性、有效性、合法性。

• 常见的约束

  1. 非空约束，not null
  2. 唯一约束，unique
  3. 主键约束，primary key
  4. 外键约束，foreign key
  5. 自定义检查约束，check（不建议使用）(在mysql中现在还不支持)

• 非空约束

  非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

    drop table if exists t_student; 
    create table t_student(
    	student_id  	int(10),
    	student_name 	varchar(20) not null,
    	sex		char(2)  default  'm',
    	birthday	date, 
    	email		varchar(30),
    	classes_id	int(3)	
    )
    
    insert into t_student(student_id, birthday, email, classes_id) 
    values
    (1002, '1988-01-01', 'qqq@163.com', 10)
    /*会报错，姓名不能为空，就得给上*/
  

• 唯一约束

  • 唯一性约束，它可以使某个字段的值不能重复，但可以为NULL(多个NULL是可以的)。

  • 给一列添加unique，如下：email不能重复

    drop table if exists t_student; 
    create table t_student(
    	student_id  	int(10),
    	student_name 	varchar(20) not null,
    	sex		char(2)  default  'm',
    	birthday	date, 
    	email		varchar(30)  unique,
    	classes_id	int(3)	
    )
    insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
    values
    (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
    

  • 给两列或者多列添加unique。

    /* 方式一 */
    drop table if exists t_user; 
    create table t_user(
        id int,
        usercode varchar(255),
        username varchar(255),
        unique(usercode, username)
    );
    
    /*方式二*/
    drop table if exists t_user; 
    create table t_user(
        id int,
        usercode varchar(255) unique,
        username varchar(255) unique
    );
    
    /**
    方式一和方式二的区别？
    方式一：usercode、username两个字段联合起来不能重复
    方式二：usercode、username两个字段分别不能重复
    
    例如：
    insert into t_user values(1, '111', 'zs');
    insert into t_user values(2, '111', 'ls');
    insert into t_user values(3, '222', 'zs');
    以上可插入方式一创建的表，但不可插入方式二创建的表。
    方式一判断重复的标准是，两字段都重复才算重复，方式二是有一个字段重复就算重复
    */
    

  • 列级约束与表级约束

    • 列级约束：修饰一列，如上面的方式二
    • 表级约束：修饰多列，如上面的方式一
      • not null不是表级约束！

• 可以查看表的约束情况

  • 关于约束名称可以到table_constraints中查询

  • 例如：

    use information_schema;

    select * from table_constraints where table_name = 't_student';
    

• 表级约束的名称可以自定义：constraint

  drop table if exists t_student; 
  create table t_student(
  	student_id  	int(10),
  	student_name 	varchar(20) not null,
  	sex		char(2)  default  'm',
  	birthday	date, 
  	email		varchar(30)  ,
  	classes_id	int(3)	,
  	constraint email_unique unique(email)/*表级约束*/
  )
  

• 主键约束，primary key

  • 主键的分类：

    • 根据字段数量分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的，单一主键推荐使用。
    • 根据主键性质分为：自然主键、业务主键
      • 自然主键：没有任何业务关系的自然数。（推荐用）
      • 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号作主键，用身份证号作主键。（不推荐用）

  • 主键修饰的字段不为空，也不能重复。

  • 一张表的主键只能有一个。

  • 主键的作用？

    1. 设计规范
    2. 主键值是这条记录在这张表中的唯一标识。

  • drop table if exists t_student; 
    create table t_student()
    	student_id int(10)  primary key,/*列级约束*/
    	student_name varchar(20) not null,
    	sex char(2) default  'm',
    	birthday date, 
    	email varchar(30),
    	classes_id int(3)	
    )
    
    /*采用表级约束方式创建*/
    drop table if exists t_student; 
    create table t_student()
    	student_id  	int(10),
    student_name 	varchar(20) not null,
    	sex		char(2)  default  'm',
    	birthday	date, 
    	email		varchar(30)  ,
    	classes_id	int(3),
    	primary key(id)
    )
    

  • 我们也可以通过表级约束为约束起个名称：

    drop table if exists t_student; 
    create table t_student(
    	student_id  	int(10),
    	student_name 	varchar(20) not null,
    	sex		char(2)  default  'm',
    	birthday	date, 
    	email		varchar(30)  ,
    	classes_id	int(3),
        CONSTRAINT p_id PRIMARY key (student_id)
    )
    insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
    values
    (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
    

  • MySQL提供主键值自增：auto_increment

    • 自动维护一个自增的数字，从1开始。

    drop table if exists t_user;
    create table t_user(
    	id int primary key auto_increment,
        username varchar(255)
    );
    insert into t_user(username) values('a');
    insert into t_user(username) values('a');
    insert into t_user(username) values('a');
    insert into t_user(username) values('a');
    
    /*
    结果：
    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | a        |
    |  2 | a        |
    |  3 | a        |
    |  4 | a        |
    +----+----------+
    4 rows in set (0.00 sec)
    */
    
    /* 当中间删除了某条数据时 
    mysql> delete from t_user where id = 2;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | a        |
    |  3 | a        |
    |  4 | a        |
    +----+----------+
    3 rows in set (0.00 sec)
    
    可见，主键并不会继续维持连续的状态。
    */
    

    Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

• 外键约束，foreign key

  • 外键主要是维护表之间的关系的

    • 有了外键约束，可以保证表中该字段的值对应地一定存在另一张表中的某行数据。

  • 使用外键的表称为子表，被引用的表称为父表。子表依赖父表，所以修改父表数据的时候应考虑子表。

    顺序：

    删除数据：先删子表，再删父表

    添加数据：先加父表，再加子表

    创建表：先创建父表，再创建子表

    删除表：先删子表，再删父表

  • 外键值，可以为NULL。

  • 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

    • 答：被引用的字段不一定是主键，但至少具有unqiue约束。开发中常用主键，也建议用。

  • 示例：建立学生和班级表之间的连接

    1. 首先建立班级表t_classes

       drop table if exists t_classes;
       create table t_classes(
       	classes_id int(3),
       	classes_name varchar(40),
       	constraint pk_classes_id primary key(classes_id)
       )
       

    2. 在t_student中加入外键约束

       • t_student中的classes_id字段引用t_class表中的classes_id字段，此时t_student表叫做子表。t_classes表叫做父表。

       drop table if exists t_student;
       create table t_student(
       	student_id  	int(10),
       	student_name 	varchar(20),
       	sex		char(2),
       	birthday	date,
       	email		varchar(30),
       	classes_id	int(3), 
       	constraint   student_id_pk primary key(student_id),
       constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	   
       )
       /*外键也可以设置为列级约束的形式*/
       

    3. 向t_student中加入数据

       insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
       

       

       出现错误，因为在班级表中不存在班级编号为10班级，外键约束起到了作用

       存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表
       

       以上成功的插入了学生信息，当时classes_id没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

       drop table if exists t_student;
       create table t_student(
       	student_id  	int(10),
       	student_name 	varchar(20),
       	sex		char(2),
       	birthday	date,
       	email		varchar(30),
       	classes_id	int (3) not null,
       	constraint      student_id_pk primary key(student_id),
       	constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	     
       )
       insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);
       
       

增删改表的结构

(Java开发中极少改表结构，遇到时再用工具就好了)

采用alter table来增加/删除/修改表结构，不影响表中的数据

添加字段

如：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)

alter table t_student add contact_tel varchar(40);

修改字段

如：student_name无法满足需求，长度需要更改为100

alter table t_student modify student_name varchar(100) ;

删除字段

如：删除联系电话字段

alter table t_student drop contact_tel;

增删改表中数据

添加、修改和删出都属于DML，主要包含的语句：insert、update、delete

• 添加（insert）

  • 语法格式

    insert into 表名(字段，。。。。) values(值,………..)

    insert into 表名(字段，。。。。) 
    values(值,………..),
    values(值,………..),
    ...
    /* 可以一次插入多条values，中间用逗号隔开 */
    

  • 要求：字段的数量是可选的，但字段的数量和值的数量必须相同，并且数据类型要对应相同。

  • 若对应的字段没有对应的value的话，如果该字段可以为null，系统将自动赋上null值。

  • 字段可以省略，字段省略时，后面的values就要对应全部字段的值。不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句

    insert into 表名 values(...)

  • 执行insert，必然会插入至少一条数据。

  • 如何插入日期？

    • 第一种方法，插入的日期格式和显示的日期格式一致

      insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);

    • 第二种方法，采用函数：str_to_date()

      insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

    • 第三种方法，函数：添加系统日期（now()）

      insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);

  • 表复制

    • create table emp_bak as select empno,ename,sal from emp;

      以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

    • 如何将查询的数据直接放到已经存在的表中，如下：

      insert into emp_bak select * from emp where sal=3000;

• 修改（update）

  可以修改数据，可以根据条件修改数据

  • 语法格式：

    update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where …….

    • 没有条件时，整张表数据全部更新

  • 示例：

    将job为manager的员工的工资上涨10%

    update emp set sal=sal+sal*0.1 where job='MANAGER';

• 删除（delete）

  可以删除数据，可以根据条件删除数据

  • 语法格式：

    delete from表名 where 。。。。。

    • 没有加条件时全部删除

  • 示例

    1. 删除津贴为500的员工

       delete from emp where comm=500;

    2. 删除津贴为null的员工

       delete from emp where comm is null;

  • 截断操作：truncate。—— 删除大表的数据，效率会快些。

    如：truncate table emp1;会将emp1表头部位置截断，即后面的数据全被删除，即该表数据全部清空。

    • 表被截断，不可回滚。——永久丢失。

  • 删除表？

    • drop table 表名：这个通用
    • drop table if exists 表名：Oracle不支持这种写法。

SQL查询语句

简单查询

• 查询字段

  • select 字段名 from 表名
    • 字段名可以有多个，中间用,隔开
  • 如：
    

• 查询全部字段

  • select * from 表名
    

  • 建议查询全部字段将相关字段写到select语句的后面,在以后java连接数据库的时候,是需要在java程序中编写SQL语句的,这个时候编写的SQL语句不建议使用select * 这种形式,建议写明字段,这样可读性强

• 在select语句中可以使用运算符

  • 如：select empno,ename,sal*12 from emp;
    

• 可以给查询出来的字段起别名

  • 如：select empno as ‘员工编号’,ename as ‘员工姓名’,sal*12 as ‘年薪’ from emp
    

  • as关键字可以省略，用空格代替
    

条件查询

条件查询需要用到where语句，where必须放到from语句的后面

支持如下运算符:

使用示例：

=

1. 查询薪水为5000的员工

   select empno,ename,sal from emp where sal=5000;
   

2. 查询job为MANAGER的员工

   select empno,ename from emp where job="manager";

   select empno,ename from emp where job='MANAGER';
   

3. 查询薪水不等于5000的员工

   select empno, ename, sal from emp where sal <> 5000;
   

   • 以下写法等同于以上写法，建议使用第一种写法

     select empno, ename, sal from emp where sal != 5000;

   • 数值也可以采用单引号引起来，如一下语句是正确的(不建议这么写)

     select empno, ename, sal from emp where sal <> '5000';

> < between…and…

1. 查询薪水为1600到3000的员工(第一种方式，采用>=和<=)

   select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;
   

2. 查询薪水为1600到3000的员工(第一种方式，采用between … and …)

   select empno, ename, sal from emp where sal between 1600 and 3000;
   

   关于between … and …，它是包含最大值和最小值的

3. between … and … 还可以用于字符串，这时它只包含最小值。（极少用）

   • select ename from emp
     

   • select ename from emp where ename between 'A' and 'C'
     

     如图所示，结果是不包含最大值的。

is null

1. 查询津贴为空的员工

   null表示为空，但不是空串，为null则代表这个字段不填值，如果查询为null的字段，采用is null

   • select * from emp where comm=null;
     

     以上也无法查询出符合条件的数据，因为null类型比较特殊，必须使用 is来比较

   • select * from emp where comm is null;
     

and

1. 查询工作岗位为MANAGER,薪水大于2500的员工

   and表示并且的含义，表示所有的条件必须满足

   select * from emp where job='MANAGER' and sal > 2500;
   

or

1. 查询出job为manager或者job为salesman的员工

   or，只要满足条件即可,相当于包含

   select * from emp where job='MANAGER' or job='SALESMAN';
   

运算符的优先级

1. 查询薪水大于1800，并且部门代码为20或30的员工

   1. 错误写法

   select * from emp where sal > 1800 and deptno = 20 or deptno = 30;
   

   以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的

   2. 正确的写法

   select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
   

   关于运算符的问题：不用记，没有把握尽量采用括号

in

in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些，注意 in后的()里表示的不是一个区间，而是一个个具体的值。

1. 查询出job为manager或者job为salesman的员工

   select * from emp where job in ('manager','salesman');
   

not

1. 查询出薪水不包含1600和薪水不包含3000的员工

   • 第一种写法

     select * from emp where sal <> 1600 and sal <> 3000;
     

   • 第二种写法

     select * from emp where not (sal = 1600 or sal = 3000);
     

   • 第三种写法

     select * from emp where sal not in (1600, 3000);
     

2. 查询出津贴不为null的所有员工

   select * from emp where comm is not null;
   

like

• Like可以实现模糊查询，like支持%和下划线匹配

• Like中%和下划线的差别？

  • %匹配任意字符出现的个数
  • 下划线只匹配一个字符

• Like 中的表达式必须放到单引号中|双引号中，以下写法是错误的：

  select * from emp where ename like _A%

1. 查询姓名以M开头所有的员工

   select * from emp where ename like 'M%';
   

2. 查询姓名以N结尾的所有的员工

   select * from emp where ename like '%N';
   

3. 查询姓名中包含O的所有的员工

   select * from emp where ename like '%O%';
   

4. 查询姓名中第二个字符为A的所有员工

   select * from emp where ename like '_A%';
   

5. 查询姓名中第二个字符为_的员工

   sql语句支持转义字符\

   select * from emp where ename like '_\_%';

排序查询

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面

使用示例：

单一字段排序

1. 按照薪水由小到大排序(系统默认由小到大)

   select * from emp order by sal;
   

2. 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)

   select * from emp where job='MANAGER' order by sal;
   

   • 如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面

     • 以下写法是错误的：

     select * from emp order by sal where job='MANAGER';

3. 按照多个字段排序，如：首先按照job排序，再按照sal排序

   select * from emp order by job,sal;
   

手动指定排序顺序

1. 手动指定按照薪水由小到大排序 (asc)

   select * from emp order by sal asc;
   

2. 手动指定按照薪水由大到小排序 （desc）

   select * from emp order by sal desc;
   

多个字段排序

1. 按照job和薪水倒序

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

   如果采用多个字段排序，如果根据第一个字段排序，当遇到重复了，才会根据第二个字段排序

使用字段的位置来排序

1. 按照薪水升序

   select * from emp order by 6;
   

   不建议使用此种方式，采用数字含义不明确，程序不健壮

分组函数

或者叫聚合函数或者叫多行处理函数

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

所有分组函数都是对“某一组”数据进行操作的。

例：

select count(*) from emp where xxx; 符合条件的所有记录总数。

select count(comm) from emp; comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。

使用示例：

count

1. 取得所有的员工数，是包含null的。

   select count(*) from emp;
   

2. 取得津贴不为null员工数

   select count(comm) from emp;
   

   采用字段名称，不会取得为null的记录

3. 取得工作岗位的个数

   select count(distinct job ) from emp;
   

sum

sum可以取得某一个列的和，null会被忽略

1. 取得薪水的合计

   select sum(sal) from emp;
   

2. 取得津贴的合计

   select sum(comm) from emp;
   

   • null会被忽略

3. 得薪水的合计（sal+comm）

   select sum(sal+comm) from emp;
   

   • 从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

   select sum(sal+IFNULL(comm, 0)) from emp;
   

avg

取得某一列的平均值

1. 取得平均薪水

   select avg(sal) from emp;
   

max

取得某个一列的最大值

1. 取得最高薪水

   select max(sal) from emp;
   

2. 取得最晚入职得员工

   select max(str_to_date (hiredate, '%Y-%m-%d')) from emp;
   

min

取得某个一列的最小值

1. 取得最低薪水

   select min(sal) from emp;
   

2. 取得最早入职得员工（可以不使用str_to_date转换）

   select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;
   

组合聚合函数

可以将这些聚合函数都放到select中一起使用

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

分组查询

分组查询主要涉及到两个子句，分别是：group by和having

• group by：按照某个字段或者某些字段进行分组。
• having：是对分组之后的数据进行再次过滤。

分组查询必须在where语句之后。

使用示例：

group by

分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。并且任何一个分组函数都是再group by语句执行结束之后才会执行的。这也是分组函数不能在where语句使用的原因。

1. 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计。

   需求是先分组，再合计

   select job, sum(sal) from emp group by job;
   

• 如果使用了order by，order by必须放到group by后面

2. 按照工作岗位和部门编码分组，取得的工资合计

   1. 原始数据
      

   2. 分组语句

      select job,deptno,sum(sal) from emp group by job,deptno;
      

• 注意点：

  • select empno,deptno,avg(sal) from emp group by deptno;
    

    以上SQL语句在Oracle数据库中无法执行，执行报错。

    以上SQL语句在Mysql数据库中可以执行，但是执行结果矛盾。

    在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。

having

如果想对分组数据再进行过滤需要使用having子句，having语句可以用分组函数。

having必须在group by之后，没有group by不能用having。

1. 取得每个岗位的平均工资大于2000

   select job, avg(sal) from emp group by job having avg(sal) >2000;
   

   分组函数的执行顺序：

   1. 根据条件查询数据
   2. 分组
   3. 采用having过滤，取得正确的数据

select语句总结

一个完整的DQL语句格式如下

select 字段
from 表名
where ……
group by ……
having ……(就是为了过滤分组后的数据而存在的——不可以单独的出现)
order by ……

以上语句的执行顺序
1.	首先执行where语句过滤原始数据
2.	执行group by进行分组
3.	执行having对分组数据进行操作
4.	执行select选出数据
5.	执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

去重

关于查询结果集的去重：distinct 关键字：去除重复记录

select distinct job from emp;

distinct只能在所有字段的最前面，

如：select deptno, distinct job from emp;，语法错误。

表示对后面所有字段联合起来去重。

如：select distinct deptno,job from emp;

连接查询

连接查询：也可以叫跨表查询，需要关联多个表进行查询

SQL92语法

select ... from 表.. where 连接条件

使用示例：

1. 显示每个员工信息，并显示所属的部门名称

   select ename, dname from emp, dept;
   

   以上输出，不正确，输出了56条数据，其实就是两个表记录的乘积。

   这种情况我们称为：“笛卡儿乘积”，出现错误的原因是：没有指定连接条件

   指定连接条件

   select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;

   也可以使用别名（效率高些，可读性好些，对于连接的表字段重名的情况来说）

   select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
   

   以上结果输出正确，因为加入了正确的连接条件

   以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）

   避免了笛卡尔积现象，会减少记录的匹配次数吗？

   答：不会，只不过显示的是有效记录。只是避免显示无效记录。

2. 取得员工和所属的领导的姓名

   select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
   

   以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表emp e代码了员工表，emp m代表了领导表，相当于员工表和部门表一样

SQL99语法

select ... from 表1 join 表2 on 连接条件 where 过滤条件

...
A
join
B
on
...
join
C
on
...
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

优点：

• 更清晰。92语法连接条件和过滤条件会写到一起，都在where后就容易混。

使用示例：

1. （内连接）显示薪水大于2000的员工信息，并显示所属的部门名称

   1. 采用SQL92语法：

      select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000;

   2. 采用SQL99语法：

      select e.ename, e.sal, d.dname from emp e join dept done.deptno=d.deptno where e.sal>2000;

      或

      select e.ename, e.sal, d.dname from emp einnerjoin dept done.deptno=d.deptno where e.sal>2000;

      在实际中一般不加inner关键字

      Sql92语法和sql99语法的区别：

      99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰

2. （外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来

   1. 右连接：

      select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;

   2. 左连接：

      select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno=d.deptno;

   以上两个查询效果相同

3. 三张表怎么连接查询？

   案例：找出每一个员工的部门名称以及工资等级。

   select
   	e.ename, d.dname, s.grade
   from
   	emp e
   join
   	dept d
   on
   	e.deptno = d.deptno
   join
   	salgrade s
   on
   	e.sal between s.losal and s.hisal;
   

   案例2：找出每一个员工的部门名称以及工资等级，以及上级领导。

   select
   	e.ename, d.dname, s.grade, e1.ename '领导'
   from
   	emp e
   join
   	dept d
   on
   	e.deptno = d.deptno
   join
   	salgrade s
   on
   	e.sal between s.losal and s.hisal;
   left join /*此时的主表是emp e，这一系列的连接都是作用在emp e上的。*/
   	emp e1
   on
   	e.mgr = e1.empno;
   

连接分类：

1. 内连接：（非等值连接：最大的特点是：连接条件中的关系是非等量关系。）

   1. 表1 inner join 表2 on 关联条件
   2. 做连接查询的时候一定要写上关联条件
   3. inner 可以省略

2. 外连接（使用多）

   1. 左外连接

      1. 表1 left outer join 表2 on 关联条件

      连接查询的时候一定要写上关联条件

   2. 右外连接

      1. 表1 right outer join 表2 on 关联条件

      做连接查询的时候一定要写上关联条件

   3. outer 可以省略

   4. 全连接：左边的表和右边的表都是主表。

   5. 左外连接（左连接）和右外连接（右连接）的区别：

      1. 左外连接：表示左边的表是主表。
      2. 右外连接：表示右边的表是主表。
      3. 左、右外连接都有相对应的写法。

3. 内连接和外连接的区别？

   1. 内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。A、B两张表没有主副之分，两张表是平等的。
   2. 外连接：假设A和B表进行连接，使用外连接的话，A、B两张表中有一张表是主表，一张表是副表，主要查询主表中的数据（外连接主要特点：主表中的数据都会显示出来），捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

​

​

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

可以出现在：select、from、where后面。

• 在where语句中使用子查询，也就是在where语句中加入select语句

  1. 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

     1. 实现思路：

        1. 首先取得管理者的编号，去除重复的

           select distinct mgr from emp where mgr is not null;

           distinct 去除重复行

        2. 查询员工编号包含管理者编号的

           select empno, ename from emp where empno in(select distinct mgr from emp where mgr is not null);

  2. 查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水

     1. 实现思路:

        1. 取得平均薪水
        2. 取得大于平均薪水的员工

        select empno, ename, sal from emp where sal > (select avg(sal) from emp);

• 在from语句中使用子查询，可以将该子查询看做一张表

  1. 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名首先取得管理者的编号，去除重复的

     select distinct mgr from emp where mgr is not null;

     将以上查询作为一张表，放到from语句的后面

     1. 使用92语法：

        select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;

     2. 使用99语法：

        select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

  2. 查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

     1. 实现思路

        1. 首先取得各个部门的平均薪水

           select deptno, avg(sal) avg_sal from emp group by deptno;

        2. 将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

           select deptno,avg(sal) avg_sal from emp group by deptno;

           select * from salgrade;

        3. 组合

           select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

• 在select语句中使用子查询

  1. 查询员工信息，并显示出员工所属的部门名称第一种做法，将员工表和部门表连接

     select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

  2. 第二种做法，在select语句中再次嵌套select语句完成部分名称的查询

     select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

union

union可以合并查询结果集（相加）

使用示例：

1. 查询job包含MANAGER和包含SALESMAN的员工

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

2. 采用union来合并

   select * from emp where job='MANAGER'
   union
   select * from emp where job='SALESMAN'
   

   

合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。

如：

select ename from emp
union
select dname from dept;

limit 的使用

mySql提供了limit ，主要用于提取前几条或者中间某几行数据

limit是MySQL特有的，其他数据库没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

select * from tablename limit m,n
 其中m是指记录开始的index，从0开始，表示第一条记录；n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
 即取出第3条至第6条，4条记录
select * from tablename limit 5;
 即取前5条数据

limit语句是SQL语句中最后执行的一个环节。

通用的标准分页sql？

每页显示pageSize条记录
第pageNo页：(pageNo - 1) * pageSize, pageSize

存储引擎

什么是存储引擎？

答：存储引擎这个名字只有再MySQL中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）

MySQL支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

存储引擎的使用

• 数据库中的各表均被（在创建表时）指定的存储引擎来处理。

• 服务器可用的引擎依赖于以下因素：

  • MySQL的版本
  • 服务器在开发时如何被配置
  • 启动选项

• 为了解当前服务器中有哪些存储引擎可用，可使用SHOW ENGINES语句：mysql> SHOW ENGINES\G（这里为MySQL8版本）

  mysql> SHOW ENGINES\G
  *************************** 1. row ***************************
        Engine: MEMORY
       Support: YES
       Comment: Hash based, stored in memory, useful for temporary tables
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 2. row ***************************
        Engine: MRG_MYISAM
       Support: YES
       Comment: Collection of identical MyISAM tables
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 3. row ***************************
        Engine: CSV
       Support: YES
       Comment: CSV storage engine
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 4. row ***************************
        Engine: FEDERATED
       Support: NO
       Comment: Federated MySQL storage engine
  Transactions: NULL
            XA: NULL
    Savepoints: NULL
  *************************** 5. row ***************************
        Engine: PERFORMANCE_SCHEMA
       Support: YES
       Comment: Performance Schema
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 6. row ***************************
        Engine: MyISAM
       Support: YES
       Comment: MyISAM storage engine
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 7. row ***************************
        Engine: InnoDB
       Support: DEFAULT
       Comment: Supports transactions, row-level locking, and foreign keys
  Transactions: YES
            XA: YES
    Savepoints: YES
  *************************** 8. row ***************************
        Engine: BLACKHOLE
       Support: YES
       Comment: /dev/null storage engine (anything you write to it disappears)
  Transactions: NO
            XA: NO
    Savepoints: NO
  *************************** 9. row ***************************
        Engine: ARCHIVE
       Support: YES
       Comment: Archive storage engine
  Transactions: NO
            XA: NO
    Savepoints: NO
  9 rows in set (0.01 sec)
  

• 在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。

  CREATE TABLE TABLENAME (
      ...
  ) ENGINE = MyISAM;
  

• 如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎

• 默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。

• 现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;

• 为确定某表所使用的存储引擎，可以使用SHOW CREATE TABLE或SHOW TABLE STATUS语句：

  mysql> SHOW CREATE TABLE emp\G
  *************************** 1. row ***************************
         Table: emp
  Create Table: CREATE TABLE `emp` (
    `EMPNO` int(4) NOT NULL,
    `ENAME` varchar(10) DEFAULT NULL,
    `JOB` varchar(9) DEFAULT NULL,
    `MGR` int(4) DEFAULT NULL,
    `HIREDATE` date DEFAULT NULL,
    `SAL` double(7,2) DEFAULT NULL,
    `COMM` double(7,2) DEFAULT NULL,
    `DEPTNO` int(2) DEFAULT NULL,
    PRIMARY KEY (`EMPNO`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)
  

  mysql> SHOW TABLE STATUS LIKE 'emp' \G
  *************************** 1. row ***************************
             Name: emp
           Engine: InnoDB
          Version: 10
       Row_format: Dynamic
             Rows: 14
   Avg_row_length: 1170
      Data_length: 16384
  Max_data_length: 0
     Index_length: 0
        Data_free: 0
   Auto_increment: NULL
      Create_time: 2021-05-03 14:33:52
      Update_time: 2021-05-03 14:33:53
       Check_time: NULL
        Collation: utf8mb4_0900_ai_ci
         Checksum: NULL
   Create_options:
          Comment:
  1 row in set (0.05 sec)
  

常用的存储引擎

MyISAM存储引擎

• MyISAM存储引擎是MySQL最常用的引擎。

• 它管理的表具有以下特征：

  1. 使用三个文件表示每个表：

     1. 格式文件 — 存储表结构的定义（mytable.frm）

     2. 数据文件 — 存储表行的内容（mytable.MYD）

     3. 索引文件 — 存储表上索引（mytable.MYI）

  2. 灵活的AUTO_INCREMENT字段处理

  3. 可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

• 缺点：

  1. 不支持事务

InnoDB存储引擎

• InnoDB存储引擎是MySQL的缺省引擎。

•     Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
  Transactions: YES
            XA: YES
    Savepoints: YES
  

• 优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

• 它管理的表具有下列主要特征：

  1. 每个InnoDB表在数据库目录中以.frm格式文件表示

  2. InnoDB表空间tablespace被用于存储表的内容。—— 内容没办法被压缩、无法被转换成只读！

1. tablespace：是一个逻辑概念，不是具体的文件。

2. 提供一组用来记录事务性活动的日志文件

3. 支持事务处理

4. 提供全ACID兼容

5. 在MySQL服务器崩溃后提供自动恢复

6. 多版本（MVCC）和行级锁定

7. 支持外键及引用的完整性，支持级联删除/更新

   级联删除、级联更新：就是有外键关联的几个表，在数据修改时，几个表中的该数据一并修改。

MEMORY存储引擎

• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。

• MEMORY存储引擎管理的表具有下列特征：

  1. 在数据库目录内，每个表均以.frm格式的文件表示。

  2. 表数据及索引被存储在内存中。（不存在硬盘中——断电即没）

  3. 表级锁机制。

  4. 不能包含TEXT或BLOB字段。

• MEMORY存储引擎以前被称为HEAP引擎。

• 优点：查询速度最快。

• 缺点：不支持事务。数据容易丢失，因为所有数据和索引都是存储在内存当中的。

选择合适的存储引擎

• MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。

• 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。（一般用InnoDB即可）

• 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

事务

背景：

• 什么是事务？

  • 一个事务是一个完整的业务逻辑单元，不可再分。
  • 事务只作用到DML语句。
    • 因为DML（insert/delete/update）语句是和数据库表当中的“数据”相关的。事务的存在——目的：是为了保证数据的完整性，安全性。

• 作用：

• 事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。

• 事务具有四个特征ACID：

  1. 原子性（Atomicity）

     • 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。事务是最小的工作单元，不可再分。

  2. 一致性（Consistency）

     • 在事务开始之前与结束之后，数据库都保持一致状态。保证多条DML语句同时成功或者同时失败。

  3. 隔离性(Isolation)

     • 一个事务不会影响其他事务的运行。

  4. 持久性(Durability)

     • 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

• 事务中存在一些概念：

  1. 事务（transaction）：一批操作（一组DML）

  2. 开启事务（Start Transaction）

  3. 回滚事务（rollback）

  4. 提交事务（commit）

  5. SET AUTOCOMMIT：禁用或启用事务的自动提交模式

  6. savepoint：保存点

• 关于事务的回滚需要注意：

  1. 只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.
  2. 事务只对DML有效果。
  3. 注意：rollback，或者commit后该事务就结束了。

• **事务是怎么保证数据完整性、安全性的呢？**执行逻辑：
  

事务的提交与回滚演示

1. 创建表

   create table user(
   id int (11) primary key not null auto_increment ,
        username varchar(30),
   password varchar(30)
   )  ENGINE=InnoDB DEFAULT CHARSET=utf8
   

2. 查询表中数据
   

3. 开启事务START TRANSACTION;

4. 插入数据：insert into user (username,password) values ('zhangsan','123');
   

5. 查看数据
   

6. 修改数据
   

7. 查看数据
   

8. 回滚事务
   

9. 查看数据
   

自动提交模式

• MySQL事务默认情况下是自动提交的。

• 自动提交模式用于决定新事务如何及何时启动。

• 启用自动提交模式：

  1. 如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。

  2. 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。

  3. 如果语句执行失败，事务将自动回滚，并取消该语句的结果。

  4. 在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。

• 禁用自动提交模式：

  1. 如果禁用自动提交，事务可以跨越多条语句。

  2. 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。

• 自动提交模式可以通过服务器变量AUTOCOMMIT来控制。

• 例如：

  mysql> SET AUTOCOMMIT = OFF； 
  
  mysql> SET AUTOCOMMIT = ON； 
  
  或
  
  mysql> SET SESSION AUTOCOMMIT = OFF； 
  
  mysql> SET SESSION AUTOCOMMIT = ON； 
  
  show variables like '%auto%'; -- 查看变量状态
  

• 使用命令START TRANSACTION;关闭自动提交模式（开启一个事务）

事务中的保存点

• 设置保存点：

  命令：savepoint 保持点名字

• 回滚到保存点：

  命令：rollback 保存点名字

事务的隔离级别

一致性问题

• 事务的隔离级别决定了事务之间可见的级别。

• 当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

  1. 脏读取（Dirty Read）

     • 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取：读取到对方未提交的数据。

  2. 不可重复读（Non-repeatable Read）

     • 在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。

  3. 幻像读（Phantom Read）

     • 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。读的数据是备份，即其他事务提交的数据修改没有显示出来。

四个隔离级别

• InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

  1. 读未提交（READ UMCOMMITTED）

     • 允许一个事务可以看到其他事务未提交的修改。有脏读问题。

  2. 读已提交（READ COMMITTED）

     • 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。解决了脏读问题，但有不可重复读问题。

  3. 可重复读（REPEATABLE READ）

     • 确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。此隔离级别为InnoDB的缺省设置。解决了不可重复读问题。

  4. 串行化（SERIALIZABLE） 【序列化】

     • 将一个事务与其他事务完全地隔离。
     • 例:A可以开启事物,B也可以开启事物，A在事物中执行DML语句时,未提交，B不以执行DML,DQL语句

Oracle数据库的默认的隔离级别是：读已提交。

MySQL数据库默认的隔离级别是：可重复读。

隔离级别与一致性问题的关系

设置服务器缺省隔离级别

• 通过修改配置文件设置

  • 可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

  • 该选项值可以是：

    1. READ-UNCOMMITTED
    2. READ-COMMITTED
    3. REPEATABLE-READ
    4. SERIALIZABLE

  • 例如：

  [mysqld]
  transaction-isolation = READ-COMMITTED
  

• 通过命令动态设置隔离级别

  • 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。

  • 其语法模式为：

  ​ SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>

  ​ 其中的<isolation-level>可以是：

  1. READ UNCOMMITTED

  2. READ COMMITTED

  3. REPEATABLE READ

  4. SERIALIZABLE

  • 例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别的作用范围

• 事务隔离级别的作用范围分为两种：

  1. 全局级：对所有的会话有效

  2. 会话级：只对当前的会话有效

• 例如，设置会话级隔离级别为READ COMMITTED ：

  mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
  
  或：
  
  mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
  

  设置全局级隔离级别为READ COMMITTED ：

  mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；
  

查看隔离级别

• 服务器变量tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。

• 为了查看当前隔离级别，可访问tx_isolation变量：

• 查看会话级的当前隔离级别：

  mysql> SELECT @@tx_isolation;
  
  或：
  
  mysql> SELECT @@session.tx_isolation;
  

• 查看全局级的当前隔离级别：

  mysql> SELECT @@global.tx_isolation;
  

索引

• 什么是索引？

  • 索引等同于一本书的目录，通过目录可以快速地找到对应的资源。
  • 在数据库方面，查询一张表的时候有两种检索方式：
    1. 全表扫描
    2. 根据索引检索（效率高）

• 优点：提高检索效率。

• 索引原理

  • 最根本的是：缩小了扫描的范围。
  • 索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。MYISAM和INNODB都是用B+Tree作为索引结构
  • 索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

（主键，unique 都会默认的添加索引，所以尽量根据主键查询效率最高（1条就定位到）。）

• 索引的分类：
  1. 单一索引：给单个字段添加索引
  2. 复合索引：给多个字段联合起来添加1个索引
  3. 唯一索引：有unique约束的字段上会自动添加索引
  4. 主键索引：主键上会自动添加索引
  5. …

索引的应用

索引是添加在字段上的。

例如：select ename from emp where sal = 6000;当sal字段上没有添加索引的时候，该SQL语句会进行全表扫描，扫描sal字段中所有的值。当sal字段上有索引的时候，该SQL语句会根据索引扫描，快速定位。

什么时候需要给字段添加索引：

• 表中该字段中的数据量庞大

• 经常被检索——经常出现在where子句中的字段

• 经常被DML操作的字段不建议添加索引。(因为假如字段进行修改，索引也需要相应的维护)

添加索引

如经常根据某字段进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对该字段建立索引，

建立索引命令如下：

• create index 索引名 on 表名(列名);
• alter table 表名 add index 索引名 (列名);

查看索引

show index from 表名

使用索引

添加索引后，执行查询语句按条件查询时，会自动根据字段上的索引查询。

索引什么时候失效？

如：select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

删除索引

drop index 索引名称 on 表名

ALTER TABLE 表名 DROP INDEX 索引名称

ALTER TABLE 表名 DROP PRIMARY KEY

检查执行计划（Mysql才有的）

使用explain可以查看语句的执行情况，如下：可以看出用和没用索引的区别

实现原理

如：查询 select ename from emp where ename='SMITH';

当ename字段上没有索引的时候，会进行全表扫描，效率较低。

给ename字段添加索引：create index emp_ename_index on emp(ename);，此时会在硬盘或内存中（存储引擎决定索引的存储位置）创建索引对象，该字段值在索引对象中会有对应的索引，且表中该字段数据的物理地址也会传到索引对象对应的索引中，索引对象将内部的索引自动排序且分区。

索引底层采用的数据结构是：B + Tree

当执行查询语句：select ename from emp where ename='SMITH';首先条件是ename字段，所以会查看ename字段有没有对应的索引，现在有，结果对应的索引对象是：emp_ename_index，然后通过索引检索。会从左到右根据’SMITH’一步步追溯定位（比如先定位到S区再定位到M区…），因为索引已经被排序分区（可以用某种算法），所以缩短了扫描的次数，会很快定位到’SMITH’。

此时，根据条件查询到对应的索引，拿到了索引上存储的物理地址，该物理地址即为表中该字段数据的物理地址，就可以根据物理地址取得需要的数据显示出来。不需要再从表中查询，此查询语句执行结束。

总的来说：

通过B Tree缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';通过索引转换为：select ename from emp where 物理地址 = ...;

视图

什么是视图

• 视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。

• 视图有时也被成为“虚拟表”。

• 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。

• 相对于从基表中直接获取数据，视图有以下好处：

  • 访问数据变得简单

  • 可被用来对不同用户显示不同的表的内容

  • 用来协助适配表的结构以适应前端现有的应用程序

• 视图作用：

  • 提高检索效率

  • 隐藏表的实现细节【面向视图检索】

​

视图的使用

示例数据的准备：

创建视图

• 为什么要使用视图？

  • 因为需求决定某查询结果需要在多个地方使用，如果频繁的拷贝代码，会给维护带来成本，视图可以解决这个问题

• 示例：创建视图

  create view myview as select empno,ename,sal from emp_bak;
  

• 只有select语句可以创建视图对象。

• 注意mysql不支持子查询创建视图

修改视图

• 对视图进行增删改，会影响到原表数据。

• 示例：

  update myview set ename='hehe',sal=1 where empno=7369;通过视图修改原表数据。

删除视图

drop view if exists v_dept_emp;

使用视图

视图的作用？

视图并不会提高检索效率，视图可以隐藏表的实现细节。保密级别较高的系统中，数据库可以只对外提供相关的视图，Java程序员只对视图对象进行CRUD。

DBA命令

新建用户

CREATE USER username IDENTIFIED BY 'password';

说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

例如：

create user p361 identified by '123'; ：可以登录但是只可以看见一个库 information_schema

授权

命令详解

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

1. dbname=*表示所有数据库

2. tbname=*表示所有表

3. login ip=%表示任何ip

4. password为空，表示不需要密码即可登录

5. with grant option; 表示该用户还可以授权给其他用户

• 细粒度授权

首先以root用户进入mysql，然后键入命令：grant select,insert,update,delete on *.* to p361 @localhost Identified by "123";

如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 “%” 。

• 粗粒度授权

我们测试用户一般使用该命令授权，

GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;

privileges包括：

1. alter：修改数据库的表

2. create：创建新的数据库或表

3. delete：删除表数据

4. drop：删除数据库/表

5. index：创建/删除索引

6. insert：添加表数据

7. select：查询表数据

8. update：更新表数据

9. all：允许任何操作

10. usage：只允许登录

回收权限

命令详解

revoke privileges on dbname[.tbname] from username;

revoke all privileges on *.* from p361;

刷新权限

flush privileges

修改密码

update user set password = password('qwe') where user = 'p646';

导入导出

• 导出

  • 导出整个数据库

    在windows的dos命令窗口中执行：mysqldump 数据库名>导出路径 登录用户 密码

    如：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

  • 导出指定库下的指定表

    在windows的dos命令窗口中执行：mysqldump 数据库名 表名>导出路径 登录用户 密码

    如：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

• 导入

  • 登录MYSQL数据库管理系统之后执行：source 文件路径

数据库设计的三范式

目的：按照这个三范式设计的表不会出现数据冗余。

第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分

不符合第一范式的示例

存在问题：

n 最后一条记录和第一条重复（不唯一，没有主键）

n 联系方式字段可以再分，不是原子性的

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖

示例：

确定主键：

以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

解决方案如下：

学生信息表

教师信息表

教师和学生的关系表

如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系

以上是一种典型的“多对多”的设计

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：

学生信息表

班级信息表

以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键

一对一怎么设计？

三范式总结

第一范式：有主键，具有原子性，字段不可分割

第二范式：完全依赖，没有部分依赖

第三范式：没有传递依赖

数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

一对一设计，有两种设计方案：

• 第一种设计方案：主键共享

• 第二种设计方案：外键唯一