Lesson29_MySQL

为什么要学习数据库

• 保存数据的容器
  • 数组、集合：内存，不是永久保存
  • 文件：永久保存，不适合查询，在文件中数据中找到想要的一条数据，不好操作，不方便
• 数据库的好处
  • 持久化数据到本地
  • 可以实现结构化查询，方便管理

数据库相关概念

• DB：数据库（database）：存储数据的仓库。它存储了一系列有组织有规律的数据。每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和赋值所保存的数据。大概分为三类：层次型数据库、网络型数据库和关系型数据库，关系模型必须满足以下完整性约束条件：
  • 域（列）完整性：域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。
  • 实体（行）完整性：实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.
  • 参照完整性：参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性
• DBMS：数据库管理系统（Database Management System）。管理数据库的一套系统，数据库通过DBMS创建和操作容器。常见的数据库管理系统：mysql、Oracle、DB2、SQL server，redis。
  • 基于共享文件系统的DEMS(Access微软的，需要安装两端)
  • 基于客户机–服务器的DBMS，mysql，Oracle
• DBA：数据库管理员
• 表：有行和列组成的数据矩阵
• 行：一组相关的数据
• 列：一组相同属性的值
• 字段：一张表的结构
• 冗余：是可以不必存在，但却真实存在的数据；
• 主键：用来做唯一性表示的，不能重复
• 外键：关联几张表间的关系的。也是表中一列或者多列，外键是可以重复的，有外键的表称为从表，参照的表称为主表；如果需要将两张表建立关联关系，必须依靠外键列；就是在从表中添加一个列，此列称为外键列；建议使用 主表名称或者主表的缩写_名称；
• SQL：结构化查询语句（Structure Query Language），专门用来与数据库通信的语言。有以下优点：
  • 不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持
  • 简单易学
  • 虽然简单但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

数据库存储数据的特点

• 将数据放到表中，表再放到库找中
• 一个数据库中可以有多个表每个表都有一个名字，用来标识自己，表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中类的设计。
• 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java中的属性
• 表中的数据是按行存储的，每一行类似于java中的对象。

初识mysql

• 优点

  • 成本低：开放源码，一般可以免费试用
  • 性能高：执行很快
  • 简单：容易安装使用

• 版本

  • 社区版：免费
  • 企业版：收费

• 卸载

  • 控制面板卸载
  • 删除安装目录，如c:\program File(86)\mysql
  • 删除数据目录,如c:\programdata\mysql
  • 清理注册表，cmd输入regedit

• 下载

  • 下载地址

• 安装

  1. 双击打开.msi文件
     
  2. 勾选同意，点击next进入下一步

  
  Developer Default：开发默认安装，也就是经典安装，会安装默认的客户端和服务端

  Server only：只安装服务端

  Client only：只安装客户端

  full：完全安装，占用空间最大

  Custom：自定义安装（推荐选择）

3. 选择相应的安装模式后点击next进入下一步

   
   MySQL Servers：mysql服务器，选择后点击向右箭头添加，右边表示要安装的produce

   application：应用程序

   ​ workbench：工作台

   ​ for Excel:用于Excel

   ​ for Visual Studio：用于VS

   ​ shell：默认的命令工具，终端

   ​ Router：路由器

   connectors：连接器

   document：文档

4. 选择好要安装的插件后点击next进入下一步

6. 确认要安装的插件，点击Execute，等待安装插件，完成后点击next进入下一步，再点击next进入下一步

表示mysql配置与集群配置，直接点击next进入下一步

7. 一路点击next，直到安装完成即可

• 启动/停止服务

  • 启动：net start mysql
  • 停止：net stop mysql

在Linux上使用mysql

• 安装

  1. 卸载centos中预安装的mysql
  	rpm -qa | grep -i mysql：命令查看预安装mysql服务
  	rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps：命令卸载该服务
  2. 上传下载好的mysql安装包
  	alt+p：按alt加上p键转到Windows系统
  	put e:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar：使用put后面跟上安装包路径上传
  3. 解压mysql的安装包
  	mkdir mysql：创建目录用于解压的目标文件夹
  	tar -xvf MySQL -5.6.22-el6.i686.rpm-bundle.tar -c /root/mysql：-c后面是目标目录
  4. 安装依赖包
  	yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=false
  	yum update libstdc++-4.4.7-4.el6.x86_64
  5. 安装mysql-client
  	rpm -ivh MySQL -client-5.6.22-1.el6.i686.rpm
  6. 安装mysql-server
  	rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm
  

• 启动服务

  # 查看mysql服务状态
  service mysql status
  # 启动mysql服务
  service mysql start
  # 停止mysql服务
  service mysql stop
  

• 登录mysql

  mysql 安装完成之后，会自动生成一个随机密码，并且保存在一个密码文件中，在安装mysql -server时会有提示该文件路径，一般为/root/.mysql_secret
  # 查看生成的随机密码
  cat /root/.mysql_secret
  # 使用随机密码登录mysql服务
  mysl -uroot -p 
  # 登录成功之后，修改密码：
  set password = password('newPass');
  # 如果使用本地win上的第三方软件登录，则需要授权远程访问
  grant all privileges on *.* to 'root' @'%' identified by '密码';
  # 要让刚才授权生效，需要刷新授权
  flush privileges;
  # 然后查看Linux防火墙是否开启
  service iptables status
  # 需要关闭防火墙，否则会拦截3306连接
  service iptables stop
  

常用命令

连接数据库

• mysql -h(ip) -u(user) -p(pass)

• mysql -h(ip) -P (prot) -u (user) -p (pass)

  # 连接本机上的root用户，密码abcd1234的数据库
  mysql -uroot -pabcd1234;
  # 同上
  mysql -h localhost -P 3306 -u root -pabc123
  # 同上
  mysql -uroot -p 回车 abcd1234
  # 连接IP为196.168.1.152远程主机上root用户，密码为abcd1234的数据库（注:u与root可以不用加空格，其它也一样）
  mysql -h196.168.1.152 -uroot -pabcd1234
  

• 退出 exit

查看数据库版本

• mysql登录下查看版本

  select version（）;
  

• 未登录状态下查询版本

  mysql --version
  mysql -V # 同上
  

mysql的语法规范

• 不区分大小写，建议关键字大写，表名，列名小写
• 每条命令最好用封号结尾
• 每条命令根据需要，可以进行缩进或换行
• 注释
  • 单行注释：#注释文字
  • 单行注释：-- 注释文字
  • 多行注释：/* 注释文字 */

DDL（数据定义语言）- MySQL

数据库操作

• 查询显示所有数据库

  show databases;
  

• 创建数据库

  # 语法：create database [if not exists] databaseName;
  create database mydatabase;# 创建mydatabase数据库
  create database if not exists mydatabase charset utf8;# 创建指定字符集的数据库
  

• 数据库名无法修改，表名、列名可以修改

  • 修改数据库字符集

    # 语法：alter database dataname character set utf8;
    alter database mydata character set utf8;# 修改库mydata的字符集为utf8;
    alter database mydata charset gbk;# 修改库mydata的字符集为gbk；
    # character set 是charset的简写。效果相同
    # character encoding是指字符编码，简写为encoding，与字符是两个不同的概念
    

• 删除数据库

  # 语法：drop database [if exists] databaseName;
  drop database if exists mydatabase;# 删除mydatabase数据库
  

• 进入某个数据库

  # 语法：use databaseName;
  use mydatabase;# 进入mydatabase数据库
  

数据表操作

• 查询显示所有数据库表

  show tables;
  

• 查询指定数据库中的表

  # 语法：show tables from 库名
  show tables form mydata;# 在任意库中查看mydata中的表
  

• 查询当前所在数据库

  # 查看当前所处哪个数据库
  select database（）;
  

• 查询表的详细信息

  # 语法：show create table tableName;
  show create table user;# 显示user表的详细信息
  

• 查询表的基本信息

  # 语法：desc tableName等同于description tableName
  desc user;# 显示表user的基本信息
  

• 创建表

  # 语法：create table 【if not exists】 tableName(file1 type 【(size) constraint】)
  create table if not exists user( # 创建user表
  	id int(10) not null primary key auto_increment,# 主键，自增
  	name varchar(10) not null,# varchar类型字符串，非空
  	pass varchar(10) not null,
  	sex char(2) not null default '男',# char类型字符串，默认值‘男’
  	age int(3) not null check(age>0),# 年龄，非空，检查约束，大于0
  	cid int(10),
  	foreign key(cid) references core(id)# 添加外键
  	)
  

• 修改表

  • 修改表名

    # 语法 rename oldername to newName
    rename table user to users;# 修改表user为users
    alter table user rename to users;# 修改表user为users
    

  • 修改字段名

    # 语法：alter table tablename change [column] oldername newname type;
    alter table book change column name names varchar(10);#修改book的name字段为names
    alter table book change name bname varchar(10);# 同上
    

  • 修改字段类型

    # 语法：alter table tablename modify [column] columname newType;
    alter table book modify name char(10);#修改book中的name类型为char类型
    alter table book modify column name char(10);# 同上
    

  • 添加字段

    # 语法：alter table tableName add [column] columnName type constraint;
    alter table user add age int not null default 18;# 给user表添加age字段
    alter table user add column sex char(4) not null default '男';# 给user表添加字段sex字段
    

  • 删除字段

    # 语法：alter table tableName drop column columnName;
    alter table user drop sex;
    alter table user drop column age; 
    

  • 添加约束

    # 语法：alter table tableName add 【constraint con_Name】 con_Type column
    alter table user add constraint PK_user_id primary key (id);
    # 给user表中的id字段添加主键约束
    alter table user add primary key(id);# 同上
    alter table user add constraint FK_user_cid foreign key (cid) references core(id);# 给user表中字段cid添加外键约束，引用core表中的id字段
    alter table user add foreign key(cid) references core(id);# 同上
    alter table user add constraint CK_user_age check (age>0);
    # 给user表中添加字段age字段添加检查约束，使其大于0
    alter table user add check(age>0);# 同上
    

  • 删除约束

    # 语法：alter table tableName drop constrainttype 【con_name】;
    alter table user drop primary key;# 删除表user中的主键
    alter table user drop foreign key FK_user_cid;# 删除表user中的外键约束
    

  • 给列添加自增属性

    # 语法：alter table tableName modify 【column】 file type constraint;
    alter table user modify column id int not null primary key auto_increment;
    # 给user表中的id列添加自增
    alter table core modify id int auto_increment;# 同上
    

  • 给列添加默认值

    # 语法：alter table tableName alter file set default value;
    alter table user alter sex set default '女';# 给user表的sex添加默认值’女‘
    

• 无关联表的删除

  # 语法：drop table [if exists] tableName1,tableName2;
  drop table user,student;# 删除表user，student，不存在报错
  drop table if exists user,student;# 删除表user，student，使用if exists容错率高
  

• 有关联表的删除，先删除约束，也就是关联关系，再进行删除操作

  # 语法1：alter table tableName drop constraint con_Name;先删除关联
  # 语法2：drop table [if exists] tableName1,tableName2；再删除表
  

• 表的复制

  • 只复制表结构

    # 语法：create table tabName like olderName;
    create table copy1 like user;# 复制user表，生成表Copy1
    

  • 复制部分表结构

    # 语法：create table tabName select id,name from orlderName where (恒不成立条件)
    create table copy3 select id,name form user where 1=0;# 只复制id和name结构，不复制数据
    

  • 复制表结构和表数据

    # 语法：create table tabName select * from olderName;
    create table copy2 select * from user;# 复制表user到Copy2
    

  • 复制表结构和部分数据

    # 语法：# 语法：create table tabName select * from olderName where (条件);
    create table copy2 select * from user where id>10;# 复制表user中id>10的数据到Copy2
    

• 清空表

  # 清空user表，相当于废弃原先的表，按照原先表新建一个表
  truncate user;
  # 同上
  truncate table user;
  

DML（数据操作语言）- MySQL

插入：insert

经典插入：

• 语法：insert into 表名 （列名，列名。。。）values （值1，值2，。。。）
  

1. 插入的值的类型要与列的类型一致或兼容

   insert into users (id,name,pass,age,sex,borndate) values (1,'张三','zhangsan',18,'男','2020-06-25');
   

2. 不可以为null的列必须插入值，可以为null的列如果插入

   • 直接赋值null

     insert into users (id,name,pass,age,sex,borndate) valuse (2,'李四','lisi',20,'女',null);
     

   • 不写列，自动为null

     insert into users (id,name,pass,age,sex) valuse (2,'李四','lisi',20,'女');
     

3. 有默认值的列插入

   • 直接赋值，或者写default用默认值

     insert into users(id,name,pass,age,sex,borndate) values (3,'王五','wangwu',default,default,default);
     

   • 不写列，自动用默认值

     insert into users(id,name,pass) values (3,'王五','wangwu');
     

4. 列的顺序是否可以调换？

   # 顺序可以变换，但是值的顺序要跟写的顺序一样
   insert into users(name,pass,id) values ('赵六','zhaoliu',4);
   

5. 列数和值的个数必须一致，就算可以为空，有默认值，只有写了，都要有值

   insert into users(id) values(5);
   

6. 如果插入的值包含所有列，则列名可以省略，此时是表中所有的列，且跟表中顺序必须一致

   insert into users values(6,'孙七','sunqi',23,'女','2020-07-23')；
   

7. 如果是插入一条记录，可以不用values而用value，但是在Oracle中用values，所以记忆values即可

   insert into users value(6,'孙七','sunqi',23,'女','2020-07-23')；
   

其他插入：

• 语法：insert into 表名 set 列1=值1，列2=值2，。。。；
  

• 此插入对于不赋值且可以为空的字段直接不写即可,顺序随意。

  insert into users set id=7,name='朱八',pass='zhuba';
  

两种方式比较：

• 经典方式支持插入多行，用逗号隔开即可，第二种不可以

  insert into users (id,name,pass,age,sex) values
  (8,'tom','tom',35,'男'),
  (9,'send','send',36,'女');
  

• 经典方式支持子查询，第二种不支持

  # 注意，不能写values，子查询查出的结果直接插入
  insert into users (id,name,pass)
  select id+1,name,pass from users where id=9;
  

修改：update

修改单表记录

• 语法

  # 如果没有条件表达式筛选，则修改所有记录对应的列的值
  update 表名 set 列名=值，列名=值。。。where 条件表达式
  

• 案例

  update users set age=25 where name like '张%'；# 修改users中姓张的人的年龄为25
  # 将表中经理的年龄修改为30，工资加100块，修改的字段顺序可以随意，无顺序要求
  update users set age=30,salary=salary+100 where type='经理';
  # 将所有人工资加100块
  update users set salary = salary+100;
  

修改多表记录

• 语法

  • SQL92

    update 表1,表2 set 列名=值，列名=值，。。。where 连接条件 and 筛选条件；
    

  • SQL99

    update 表1，inner|left|right join 表2 on 连接条件 set 列名=值，列名=值，。。。 where 筛选条件；
    

• 案例

  update users u inner join student s on u.sid=s.id set u.age=25,s.age=30 where u.name='张三';
  ######################################
  update users u right join student s on s.id=u.sid set s.salary=500 where s.id is null;
  

删除：delete|truncate

delete

• 语法

  # 单表删除操作
  delete from 表名 where 筛选条件;
  # 多表删除操作
  # sql 92
  delete 表1,表2 from 表1，表2 where 连接条件 and 筛选条件;
  # sql 99
  delete 表1,表2 from 表1 inner|left|right join 表2 on 连接条件 where 筛选条件;
  

• 案例

  # 单表的删除，删除姓张的人
  delete from users where name like '张%';
  # 多表的删除,删除学生张三对应的用户信息以及学生信息。
  delete u,s from user u inner join student s on s.id = u.sid where s.name='张三';
  

• 有关联表中数据的删除

  # 对于有外键关联的两个表中的数据，要删除主表中数据时是不允许的，应先删除从表中的数据（有外键的表为从表）
  # 如果要想直接删除有下面方式
  # 方式一：级联删除（设置外键时设置好需要级联删除即可）
  alter table stuinfo add constraint fk_stu_major from key(majorid) references major(id) on delete cascade; # 添加外键时后面设置on delete cascade，表示级联删除，此时若删除主表中的数据，从表中对应引用的数据会自动删除
  # 上面的方式比较野蛮，所有数据都删除了，很可能需要删除主表中数据但从表数据不需要删除，就需要下面第二种方式
  # 方式二：级联置空，删除主表数据时从表中的引用值置空
  alter table stuinfo add constraint fk_stu_major from key(majorid) references major(id) on delete set null;# 与方式一相似的，需要在添加外键时设置好级联置空。则在删除主表中数据时，从表中对应引用的数据的引用外键被置空，数据不会删除
  

truncate

• 语法

  # 清空，不能加条件，清空会让自增重新开始，是新建表代替原先的表
  truncate table 表名;
  

• 使用delete的方式删除表中所有的数据，且让自增列从1重新自增

  delete from tableName;
  alter table tableName auto_increment=1;
  

两种删除比较

• delete可以加where条件，truncate不能加
• truncate删除效率高一点
• 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后再插入数据，自增长列的值从1开始。
• truncate删除没有返回值，delete删除有返回值
• truncate删除不能回滚，delete删除可以回滚。

DQL（数据查询语言）

基础查询

语法：

select 查询列表 from 表名；

特点：

• 查询列表可以是：表中的字段、常量值、表达式、函数
• 查询的结果是一个虚拟的表格。

使用

• 查询单个字段

  seletc name from users;
  

• 查询多个字段

  # 查询users表中的id，name，age字段的所有值，顺序随意
  select id,name,age from users;
  

• 查询所有字段

  # 查询users中所有字段的所有值，字段顺序随意
  select id,name,pass,age from users;
  # 查询users中的所有字段，只能是表中字段的顺序
  select * from users;
  

• 查询常量值

  # 查询数字100常量值,此常量值不来自哪个表，所以没有from
  select 100;
  # 查询字符，mysql中没有字符串的概念，统一单引号
  select 'Tom';
  

• 查询表达式

  # 查询100乘以98
  select 100*98;
  # 查询100取模10
  select 100%10;
  

• 查询函数

  # 查询version函数
  select version();
  # 查询database函数
  select database();
  

• 起别名

  • 好处：

    1. 便于理解
    2. 多表联查中如果有列表重复，可以使用别名的方式区分

  • AS的方式

    # 查询常量或者函数等时，列表默认就是写的内容，可以起别名
    select 100*98 AS 结果;
    # 查询字段时起别名
    select id AS ID,name AS 姓名,pass AS 密码 from users;
    

  • 空格的方式

    # 查询函数显示列表为函数名，可以起别名
    select version() 版本;
    # 查询字段起别名
    select name 姓名,pass 密码,age 年龄 from users;
    

  • 特殊情况

    # 如果起的别名中有特殊字符，如关键字，#，空格等，用单引号或双引号，建议使用双引号
    select salary "out put" from employees;
    

• 去重(distinct)

  • 特点：mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只 用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只能返回它的目标字段，而无法返回其它字段

  • 使用

    # 查询单个字段的值，并实现去重
    select distinct name from A；
    # 多个字段同时去重，就是name与id同时重复会去掉
    select distinct name, id from A；
    # 表中name去重后的数目， SQL Server支持，而Access不支持
    select count(distinct name) from A;
    # SQL Server和Access都不支持
    select count(distinct name, id) from A;
    # 会提示错误，因为distinct必须放在开头
    select id, distinct name from A;
    

• +的作用

  • java中的+的作用

    1. 做数值运算
    2. 做字符串拼接

  • mysql中+的作用：只能做数值运行

    1. 如果两个都是数值，则正常做数值运算

       # 做90+100的运算
       select 90+100;
       

    2. 如果有一方位字符，则尝试转换，如果转换成功，则做运算

       # 做120+80的运算
       select '120'+80;
       

    3. 如果转换失败，则当做0继续做运算

       # 做0+100的运算
       select 'Tom'+100;
       

    4. 如果有一方位null，则结果都为null

       # 结果直接为null，如果拼接字段时列中的值可能为null，则可以用ifnull函数
       # ifnull（判空表达式，如果为null返回的值）
       select null+50;
       

• concat的使用

  • 由于+在mysql中只能做运算，故如果要做拼接，只能用concat函数

    # 直接拼接常量
    select concat('a','b','c');
    # 拼接查询的结果,会将两列的值做拼接显示
    select concat(name,pass) AS 姓名密码 from users;
    

条件查询

语法

select 查询列表 from 表名 where 筛选条件；

分类

• 按条件表达式

  # 简单条件运算符
  >、<、=、<>(!=)、>=、<=、<=>
  

• 按逻辑表达式

  # 逻辑运算符,主要用于连接条件表达式
  and、or、not（&&、||、!）
  

• 模糊查询

  # 模糊查询关键字
  like、between and、in、is null、rlike、not in、is not null、not between
  

使用

• 按条件表达式筛选

  # 查询工资大于12000的员工信息
  select * from employees where salary>12000;
  # 查询成绩及格的学生姓名和编号
  select id 编号,name 姓名 from student where score>=60;
  # 查询年龄不等于20的用户id
  select id from users where age<>20;
  

• 按逻辑表达式筛选

  # 查询工资在10000到20000之间的员工信息
  select * from employees where salary>=10000 and  salary<=20000;
  # 查询成绩不在60到80之间，或者年龄小于23的学生
  select * from student where score>80 or score<60 or age<23;
  # 同上
  select * from student where not(score>=60 and score<=80) or age<23;
  

• 模糊查询

  • like，通常与通配符一起使用

  • 通配符：

    • %：表示任意个任意字符

      # 查询名字中包含a的用户信息
      select * from users where name like '%a%'
      # 查询姓张的用户的id，年龄
      select id,age from users where name like '张%'
      

    • _(下划线)：表示一个任意字符

      # 匹配名字第三个字符是a，第五个字符是b的用户
      select * from users where name like '__a_b%'
      

  • 如果要匹配的字符中出现特殊字符，如_或者%,需要转义

    • 通过斜杠转义

      # 匹配名字中第二个字符是下划线的用户信息
      select * from users where name like '_\_%'
      

    • 通过escape指定转义

      # 匹配名字中第二个字符是下划线的用户信息
      select * from users where name like '_@_%' escape '@'
      # 此处转义符是自定义，可以是任意字符，后面定义即可使用
      

  • between and在连个值之间

    • 注意：
      1. 左边值到右边值之间，代码更简洁
      2. 两个值是包含的
      3. 两个值顺序不能颠倒，查询的就是大于等于左边值小于等于右边值。

    # 查询成绩在60到80之间的学生姓名
    select name from student where score between 60 and 80;
    

  • in 查询的字段等值in中的某一项

    • 注意：
      1. 使用in提高语句的简洁度
      2. in列表的值类型必须一致或兼容
      3. in列表的值不能使用通配符，类似java中switch，多个值等值判断

    # 查询年龄是23,25,30的学生
    select * from student where age in(23,25,30)
    

  • is null和is not null

    • 注意：
      1. =和<>不能做null的判断
      2. 用is null做等于null，is not null做不能null的判断
      3. is和is not只能做null的判断

    # 判断用户中密码为空的用户名和编号
    select id,name from users where name is null;
    # 判断生日不为空的学生信息
    select * from student where birthd is not null;
    

  • <=> 安全等于

    • 安全等于可以用于判断null值
    • 也可以判断其他数字类型

    # 判断用户中密码为空的用户名和编号
    select id,name from users where name <=> null;
    # 查询工资等于12000的员工信息
    select * from employees where salary<=>12000;
    

  • 正则表达式查询

    • regexp匹配正则表达式
    • 正则表达式与JS类似

    # 匹配以st开头的姓名
    SELECT name FROM person_tbl WHERE name REGEXP '^st'
    # 匹配以ok结尾的姓名
    SELECT name FROM person_tbl WHERE name REGEXP 'ok$'
    # 匹配name中包含mar的
    SELECT name FROM person_tbl WHERE name REGEXP 'mar'
    

排序查询

语法

select 查询列表 from 表名 【where 筛选条件】 order by 排序列表【ASC|DESC】；

特点：

• 排序列表可以是字段，字段别名，表达式，函数
• 排序可以是升序ASC，也可以是降序DESC，不写默认是按照升序ASC排序；
• 排序列表可以是多个参数，比如先根据年龄排序，在根据成绩，中间用逗号隔开。
• order by子句一般是放在查询语句的最后面，limit除外

使用

• 按照单个字段无条件查询排序

  # 查询所有员工信息，按照工资降序排列显示
  select * from employees order by salary desc;
  

• 按照单个字段有条件查询排序

  # 查询年龄不在30到60之间的员工信息，按照工号降序排列
  select * from employees where age not between 30 and 60 order by id desc;
  

• 按照表达式排序

  # 按照年薪升序排序查询所有员工信息
  select *,ifnull(salary,0)*12 年薪 from employees order by ifnull(salary,0)*12 ASc;
  

• 按照别名排序

  # 按照年薪降序排序查询所有员工信息
  select *,ifnull(salary,0)*12 年薪 from employees order by 年薪 desc;
  

• 按照函数排序

  # 查询所有员工信息，按照姓名字节长度降序排序
  select * from employees order by length(name) desc;
  

• 按照多个字段排序

  # 查询年龄大于30的员工信息，先按照年龄升序，再按照工资降序
  select * from employees where age>30 order by age asc,salary desc;
  

常见函数

• 概念：类似于java中的方法，将一组逻辑语句封装在方法中，对外暴露方法名
• 好处：
  • 隐藏了实现细节
  • 提高代码的重用性
• 调用：select 函数名(实参列表) 【from 表】;
• 特点：
  • 叫什么(函数名)
  • 干什么(函数功能)
• 分类：
  • 单行函数，如concat、length、ifnull等
  • 分组函数，做统计使用，又称为统计函数、聚合函数、组函数

单行函数

• 字符函数

  • length(获取参数值的字节个数)

    # 获取纯英文参数字节数、结果为4
    select length('abc')；
    # 获取有中文参数的字节数，根字符集有关、结果为15
    select length('王麻子hahaha');
    # 获取客户端字符集
    show variables like '%char%';
    

  • concat(拼接字符串)

    # 查询users表中的用户名跟密码，并且拼接显示
    select concat(name,pass) from users;
    

  • upper、lower(转大写、小写)

    # 将参数大写
    select upper('tom');
    # 将参数小写
    select lower('TOM');
    # 函数的嵌套使用，如将name大写，pass小写，然后拼接
    select concat(upper(name),lower(pass)) from users;
    

  • substr、substring(写法不同，截取子串)

    注意：索引从1开始

    # 从指定索引开始，截取后面所有字符
    select substr('我的家在东北',4);
    # 从指定索引开始，截取指定长度的字符
    select substr('数据库学习第二天'2,4);
    # 姓名中首字母大写，其他字母小写，用下划线连接显示
    select concat(upper(substr(name,1,1)),'_',lower(substr(name,2))) 姓名 
    from 
    users;
    

  • instr(查索引)

    # 获取某字符串在目标字符串中首次出现的位置，如果没有返回0
    select instr('大哥大嫂过年好','大')；# 结果为1
    

  • trim(去前后空格)

    # 去掉目标字符串前后空格
    select trim(' 李云龙 ');
    # 去掉目标字符串前面的指定子串
    select trim('aa' from 'aaaaaaa王aaaaaa麻子aaaaaaaaa');# 返回a王aaaaaa麻子
    

  • lpad、rpad(左填充、右填充)

    # 在目标左边用指定字符填充到指定长度
    # 如果指定长度小于目标本来的长度，则只显示左边指定长度
    select lpad('我是目标',10,'*')；
    # 在目标右边用指定字符填充至指定长度
    select rpad('我是目标',10,'a');
    

  • replace(替换)

    # 用指定字符串替换目标中的指定字符串
    select replace('我是目标串','需要替换','替换后');
    # 将目标中的我是替换为你是
    select replace('我是精神小伙','我是','你是');
    

• 数学函数

  • round(四舍五入)

    # 只有目标值四舍五入，结果是整数
    selec round(-1.55);#-2
    select round(1.55);# 2
    # 重载的，可以指定保留小数位数
    select round(1.556,2);# 1.56
    

  • ceil(向上取整)

    # 返回大于等于该参数的最小整数
    select ceil(1.02);# 2
    

  • floor(向下取整)

    # 返回天花板，即小于等于该参数的大整数
    select floor(1.02);
    

  • truncate(截取小数位)

    # 截取两位小数，不会四舍五入，直接截取
    select truncate(2.2355);# 2.23
    

  • pow(求幂次方)

    # 求2的平方
    select pow(2,2);# 4
    

  • mod(取模)

    # 取模，就是a%b
    select mod(4,2);# 0
    

• 日期函数

  • now 返回当前系统日期+时间

    select now();
    

  • curdate 返回当前系统日期，不包含时间

    select curdate();
    

  • curtime 返回当前系统时间，包含日期

    select curtime();
    

  • 获取参数中的年、月、日、时、分、秒

    # 获取当前时间的年
    select year(now());
    # 获取自定义参数中的年
    select year('2020-01-15');
    # 查询表中的参数获取年
    select year(birthed) from users;
    # 获取月，中文显示
    select month(now());
    # 获取月，英文显示
    select monthname(now());
    #获取日
    select day(now());
    # 获取时
    select hour(now());
    # 获取分
    select minute(now());
    # 获取秒
    select second(now());
    

  • str_to_date(按格式将字符串转换日期)

    select str_to_date('1998-3-2','%Y-%c-%d');
    # 在查询中使用
    select * from users where birthed=str_to_date('4-3 1995','%c-%d %Y')
    

  • date_format(按格式将日期转换字符串)

    select date_format(now(),'%y年%m月%d日')；
    

  • datediff:返回两个日期相差的天数

  • 格式符号表

    序号	格式符	功能
    1	%Y	四位的年份
    2	%y	两位的年份
    3	%m	月份(01,02,11,12)
    4	%c	月份(1,2,11,12)
    5	%d	日(01,02)
    6	%H	小时(24小时制)
    7	%h	小时(12小时制)
    8	%i	分钟(00,01,58,59)
    9	%s	秒(00,01,58,59)

• 其他函数

  • 当前版本

    select version();
    

  • 当前数据库

    select database();
    

  • 当前用户

    select user();
    

  • 加密

    # 返回字符串加密后的形式
    password('abc');
    # md5的加密方式
    md5('abc');
    

  • null值替换

    ifnull(字段.1);#如果字段中有null值，则替换为1，不会修改表数据，一般做计算使用
    # 查询姓名、邮箱、年薪，年薪根据工资计算，还有奖金，有的员工没有奖金，需要用0替换
    select last_name,email,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees;
    

流程控制函数

• if函数：if。。else的效果。类似三目运算

  select if(10<5,'大','小');
  # 查询用户有么有成年
  select age,if(age>18,'成年人','未成年') from users where age is not null;
  

• case函数（是一种流程控制）

• 类似switch case的效果多个等值判断

1. 语法

 case 要判断的字段或表达式
when 常量1 then 要显示的值1【或语句1；】
when 常量2 then 要显示的值2【或语句2；】
。。。
else 要显示的值n【或语句n；】
end；

2. 使用

# 查询用户姓名，如果是20，显示老油条，如果是30，显示中年，如果是40，显示老年。。。
select age,
case age
when 20 then '老油条'
when 30 then '中年'
when 40 then '老年'
else '没有'
end AS '年龄分类'
from users;

• 类似if。。else if。。else的多重区间判断

1. 语法

 case
 when 条件1 then 值1
 when 条件2 then 值2
 else 其他值
 end

2. 使用

# 查询用户姓名，如果小于20，显示小鲜肉，如果小于30，显示中年，如果小于40，显示老年。。。否则显示老东西
select age,
when age<20 then '小鲜肉'
when age<30 then '中年'
when age<40 then '老年'
else '老东西'
end AS 年龄分类
from users;

聚合函数

• 简单使用

  # 查询年龄的总和
  select sum(age) from users;
  # 查询年龄平均值
  select avg(age) from users;
  # 查询年龄最大值
  select max(age) from users;
  # 查询年龄最小值
  select min(age) from users;
  # 查询年龄非空数
  select count(age) from users;
  # 综合查询
  select sum(age),avg(age),max(age),min(age),count(age)
  from users;
  # 与其他函数搭使用
  select sum(age) 总和,round(avg(age),2) 平均年龄,max(age) 最大年龄,min(age) 最小年龄,count(age) 总数
  from users;
  

• 和distinct搭配使用

  # 查询去重之后的年龄总和
  select sum(distinct age),sum(age) from users;
  # 去重之后返回个数
  select count(distinct age) from users;
  

• count函数详解

  # 统计某一字段个数，只会统计非空
  select count(age) from users;
  # 统计行数,就死统计所有列，对于某一行，只要有一列不为空，就+1
  select count(*) from users;
  # 用常量值，相当于有了虚拟一列，用来统计，效果同count(*)
  select count(1) from users;
  *************************************************
  /*
  效率方面：跟存储引擎有关
  myisam引擎：count(*) 效率最高
  innoDB引擎：count(*)和count(1)差不多，比count(字段)高。
  */
  

• 其他注意事项

  • sum、avg一般用于处理数值型，max、min、count可以处理任何类型

  • sum，avg，max，min，count函数在处理某一字段时都会忽略null值。

  • 和聚合函数一起使用的字段限制，要求是group by后的字段

    # 查询age和的同时查询name，此时5.5数据库不报错，但只显示一行，所以没有意义，在Oracle跟8.0中会直接报错。
    select sum(age),name from users;
    

分组查询

• 语法

  select 聚合函数，列(要求出现在group by的后面) from 表
  【where 筛选条件】
  group by 分组的列表
  having 分组后筛选条件
  【order by 字句】;
  /************************************/
  # 注意：查询列表必须特殊，要求是分组函数和group by后出现的字段
  

• 特点

  1. 分组前筛选条件在表名后跟where筛选条件
  2. 分组后筛选条件在group by后跟having筛选条件
  3. 如果筛选条件既是分组前也是分组后，建议使用分组前
  4. group by和order by后面支持别名，where后面不支持
  5. group by字句支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有先后顺序），表达式或函数
  6. 也可以添加排序，排序放在整个分组之后
  

• 使用

  • 准备工作

    create table student (
        id int primary key auto_increment,
        name varchar(15) not null ,
        pass varchar(15) not null ,
        age tinyint check ( age>0 ),
        cid int ,
        score double,
        foreign key (cid) references clazz(id)
    ) engine innoDB charset utf8;
    create table clazz(
        id int primary key auto_increment,
        tid int,
        des varchar(30) unique ,
        foreign key (tid) references teacher(id)
    ) engine innoDB charset utf8;
    create table teacher(
        id int primary key auto_increment,
        name varchar(15) not null ,
        pass varchar(15) not null ,
        age tinyint
    )engine innoDB charset utf8;
    

  • 简单使用

    # 查询每个班的平均分
    select age(score),clazz from student group by clazz;
    # 查询每个班的学生人数
    select count(*),clazz from student group by clazz;
    

  • 添加分组前筛选条件

    # 查询每个班及格的人数
    select count(*) clazz from student where score>60 group by clazz;
    

  • 添加分组后筛选条件

    # 查询及格人数大于3的班级
    select count(*) clazz from student where score>=60 group by clazz having count(*) >3;
    

  • 使用函数|表达式分组

    # 按照学生姓名长度分组，查询出人数大于2的
    select count(*),length(name) from student group by length(name) having count(*)>2;
    

  • 按多个字段分组

    # 按照班级跟年龄分组查询平均分数
    select avg(score),age,cid from student group by 
    age,cid;
    

  • 添加排序

    # 按照班级跟年龄分组查询平均分数,按照品均分倒序，年龄正序
    select avg(score),age,cid from student group by 
    age,cid order by avg(score) desc,age asc;
    

连接查询

概念：

• 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

• 笛卡尔积现象：表1有n行，表2有m行，最终的结果又m*n行

• 避免出现笛卡尔积现象：添加连接条件

分类

• 按年代分类
  • SQL92标准：一般仅做内连接
  • SQL99标准：推荐使用
• 按功能分类
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

使用

• SQL92标准（mysql中仅内联）

  • 等值连接

    • 特点

      1. 多表等值连接的结果为两表的交集部分
      2. n表连接，至少要n-1连接条件
      3. 多表的顺序没有要求
      4. 一般要为表别名
      5. 可以搭配前面的所有字句，如分组，排序，筛选
      

    • 简单使用

    # 查询老师名跟对应的授课名
    select cname,tname from course,teacher where course.tid=teacher.id;
    # 查询员工名和对应的部门名
    select ename,dmane from employees,departments where employees.did = departments.id;
    

    • 为表起别名:

    1. 提高语句的简洁度

    2. 区分多个重名字段

    3. 如果为表起了别名，则查询的字段就不能再用原来的表名

       # 查询员工名和对应的部门名
       select e.ename,d.dmane from employees e,departments d where e.did = d.id;
       

    • 添加筛选条件

      # 查询id在2到4之间的老师和课程名
      select tname,cname from teacher t,course c where c.tid=t.id and t.id between 2 and 4;
      

    • 添加分组

      # 查询每门课程选修的学生人数
      select conut(*),cname from student,course where student.cid=c.id group by cname;
      

    • 搭配排序

      # 查询每门课程选修的学生人数，人数大于2的，按课程名排序
      select conut(*),cname from student,course where student.cid=c.id group by cname having count(*)>2 order by cname desc;
      

  • 非等值连接

    # 查询员工工资和工资级别,显示A级别，按工资逆序显示
    select salary,grade_level from employees e,job_grades g where salary between g.lowest_sal and g.highest_sal and grade_level='A' order by salary;
    

  • 自连接

    # 查询1号学生的所有同学
    select s1.name,s1.cid,s2.name,s2.cid from student s1,student s2 where s2.cid=s1.cid and s1.id=1;
    ################################################
    子连接即将一张表通过起别名的方式当做两张表使用。
    

• SQL99标准

  • 语法

    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on 连接条件
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排序列表】
    

  • 连接类型

    内连接：inner
    外连接：
    	左外连接：left【outer】
    	右外连接：right【outer】
    	全外连接：full【outer】
    交叉连接：cross
    

  • 使用

    • 内连接

      1. 等值连接

         • 等值连接特点

         1. 添加排序，分组，筛选
         2. inner 可以省略
         3. 筛选条件可以放在where后面，连接条件放在on后面，提高可读性
         4. inner join 连接和SQL92中的凳子连接效果一样，都是查询多表的交集
         

         • 等值连接使用

         # 查询员工名，部门名
         select last_name,department_name from departments d
         inner join employees e
         on e.department_id=d.department_id;
         # 添加筛选条件，查询名字中包含e的员工名和工种名
         select last_name,job_title from employees e inner join j on e.job_id=j.job_id where e.last_name like '%e%';
         # 添加分组+筛选，查询部门个数>3的城市名和部门个数
         select city,count(*) 部门个数 from department d inner join locations l on d.location_id=l.location_id group by city having count(*)>3;
         # 排序，查询那个部门的员工个数>3的部门名称和员工个数，并按个数降序
         select count(*),department_id
         from employees e inner join departments d on e.department_id=d.department_id group by department_name having count(*)>3 order by count(*) desc;
         #多表联查，查询员工名，部门名，工种名，按照部门名降序
         select last_name,department_name,job_title from employees e inner join departments d on e.department_id=d.department_id inner join jobs j on e.job_id=j.job_id order by department_name desc;
         

      2. 非等值连接

         # 查询员工的工资级别
         select salary,grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal;
         # 查询工资级别的个数>20的个数，按照工资级别降序
         select count(*),grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal group by grade_level having count(*)>20 order by grade_level desc;
         

      3. 自连接

    • 左外（右外连接）

      1. 特点

         使用场景：用于查询一个表中有，另一个表中没有的记录
         特点：
         1. 外连接的查询结果为主表中的所有记录
         	如果从表中有和它匹配的，则显示匹配的值
         	如果从表中没有和它匹配的，则显示null
         	外连接查询结果=内连接结果+主表中有而从表中没有的记录
         2.  左外连接：left join左边的是主表
          	右外连接：right join右边的是主表
         3. 左外和右外交换两个表的顺序，可以实现同样的效果
         

      2. 使用

         # 查询男朋友不在男神表的女神名
         select b.name from beauty b left outer join boys bo on b.boyfriend_id=bo.id where bo.id is null;
         

    • 全外连接

      特点：全外连接=内连接结果+表1 中有但表2中没有的+表2中有但表1中没有的
      select b.*,bo.* from beauty b full outer join boys bo on b.boyfriend_id=bo.id;
      

    • 交叉连接(SQL99的笛卡尔积)

      select b.*,bo.* from beauty b cross boys bo;
      

子查询

• 概念：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询
• 分类
  • 按子查询出现的位置：
    • select后面:仅仅支持标量子查询
    • from后面:支持表子查询
    • where或having后面:标量、列、一些行子查询
    • exists后面(相关子查询):表子查询
  • 按结果集的行列数不同：
    • 标量子查询(结果集只有一行一列)
    • 列子查询(结果集有一行多列)
    • 行子查询(结果集有一行多列)
    • 表子查询(结果集一般为多行多列)

分页查询

• 当要显示的数据一页显示不全，需要分页向数据库提交SQL请求
• 语法

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组条件
having 分组后条件
order by 排序条件
】
limit offset，size；
offset：要显示条目的起始索引（从0开始）
size：要显示条目个数

• 案例

# 查询前5条员工的信息
select * from employees limit 0,5;
select * from employees limit 5;
# 查询第11到25条员工信息
select * from employees limit 10,15;
# 查询有奖金的员工信息，按照工资较高的前10个显示
select * from employees commission_pct is not null order by salary desc limit 10;	

• 特点：

1. limit语句在SQL的最后，执行也是最后执行
2. 公式：假设要显示第page页，每页显示size条数据
3. select * from 表 limit (page-1)*size,size;

union联合查询

• union 联合 合并：将多条查询语句的结果合并成一个结果
• 语法：

查询语句1
union
查询语句2
union
。。。

• 应用场景：要查询的结果来自多个表，且多个表没有关系，但查询的数据一样时
• 注意：
  1. 多条查询语句的查询列数要一致
  2. 默认结果列名是以第一条查询列为准，多条查询的列要对应
  3. 如果多个表中有重复数据，使用union会自动去重，如果不想去重，则使用union all
• 引入案例：查询部门编号>90或邮箱中包含a的员工

select * from employees where email like '%a%' or department_id>90;
# 使用联合查询
select * from employees where email like '%a%' 
union
select * from employees where department_id>90;

其他

• select column,column,column from table,table,table
  [where conditions][group by group_by_list][having conditions][order by order_list[asc/desc]];//查询语句的基本语法
• 如果在命令窗口查询出现乱码，是由于命令创看看模式是gbk编码，而数据库是utf8的，所以显示有问题，就需要用到set names gbk,让查询到的数据按照gbk显示。为固定写法

约束

概念：

• 一种限制，用于限制表中的数据，为了保证表中的数据的一致性（准确和可靠性）

分类：六大约束

• not null：非空，用于保证该字段的值不能为空
• default：默认值，用于保证该字段有默认值
• primary key：主键，用于保证该字段的值具有唯一性，并且非空
• unique：唯一，用于保证该字段的值具有唯一性，可以为空，但只能有一个
• check：检查约束，【mysql8.0之后开始支持】，用于该字段的值添加条件，如年龄大于0
• foreign key：外键，用于限制两个表的关系。用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键列，用于引用主表中某列的值

添加约束的时机

• 创建表时
• 修改表时

约束的添加分类

• 列级约束：
  • 创建表添加字段时跟在字段后面的约束
  • 六大约束在语法上都支持，但是外键约束没有效果
• 表级约束：
  • 创建表时添加完字段后再进行添加的约束
  • 除了非空、默认，其他约束都支持

创建表时添加列级约束

• 语法：在字段名 字段类型后面跟上约束类型

create table stuinfo(
	id int primary key,# 主键约束
	stuName varchar(20) not null,# 非空约束
	gender char(1) check(gender='男' or gender='女'),# 检查约束，可以使用任何条件，以及模糊查询关键字
	seat int unique,# 唯一约束
	age int defaule 18,# 默认值
	majorId int references major(id)# 外键（列级外键不需要写关键字foreign key，这样写只是不会报错）
);
create table major (
	id int primary key,
	majName varchar(20)
);

创建表时添加表级约束

• 语法：在所有字段的最下面【constraint 约束名】约束类型(字段名)

create table stuinfo (
	id int,
	stuName varchar(20),
	gender char(1),
	seat int,
	age int,
	majorId int,
	constraint pk primary key(id),# 主键，主键设置名字没用，会使用默认名，但是可以自己写
	constraint uq unique(seat),# 唯一键
	constraint ck check(gender='男' or gender='女'),# 检查约束
	constraint fk_stuinfo_major foreign key(majorId) references major(id)# 外键
);

通用的写法

create table if not exists stuinfo (
	id int primary key,
	stuName varchar(20) not null,
	gender char(1) check(gender='男' of gender='女',
	age int default 18,
	seat int unique,
	majorId int,
	constraint fk_stuinfo_major foreign key(majorId) references major(id)
);

主键和唯一对比

约束	唯一性	非空性	表中个数	是否允许组合
主键	是	是	至多一个	是
唯一	是	否	可以多个	是

外键

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容。名称无要求
3. 主表的关联列必须是一个key（一般是主键或唯一）
4. 插入数据时，先插入主表，再插入从表
5. 删除数据时，先删除从表，再删除主表

修改表时添加约束

• 添加非空约束

  alter table stuinfo modify column stuName varchar(20) not null;
  

• 添加默认约束

  alter table stuinfo modify column age int default 18;
  

• 添加主键约束

1. 列级约束

   alter table stuinfo modify column id int primary key;
   

2. 表级约束

   alter table stuinfo add primary key(id);
   

• 添加唯一约束

3. 列级约束

   alter table stuinfo modify column seat int unique;
   

4. 表级约束

   alter table stuinfo add unique(seat);
   

• 添加外键约束

  alter table stuinfo add 【constraint fk_stuinfo_major】 foreign key(majorId) references major(id);
  

• 语法总结
  • 添加列级约束

    alter table 表名 modify column 字段名 字段类型 约束类型
    

  • 添加表级约束

    alter table 表名 add 【constraint 约束名】约束类型(字段名)
    

修改表时删除约束

• 删除非空约束

  alter table stuinfo modify column stuname varchar(20) 【null】;
  

• 删除默认约束

  alter table stuinfo modify column age int;
  

• 删除主键约束

  alter table stuinfo drop primary key; #表只有一个外键，不需要名字
  

• 删除唯一约束

  alter table stuinfo drop index seat;# index后面跟的是约束名
  

• 删除外键约束

  alter table stuinfo drop foreign key fk_stuinfo_major;# 后面是约束名
  

• 可以通过下面语句查看约束（主键、外键、唯一）

  show index from 表名;
  

标识列

概念

• 又称为自增长列
• 含义：可以不用手动插入值，系统提供默认的序列值。

创建表时设置标识列

create table tab_identity (
	id int primary key auto_increment,# 主键、自增列
	name varchar(20),
	seat int unique;
);

特点

1. 标识列必须和主键搭配吗？不一定，但是要求该列是一个key
2. 一个表可以有几个标识列？至多一个
3. 标识列的类型只能是数值型（int、double、float）
4. 标识列默认的步长是1，初始值是1,
5. 标识列可以通过set auto_increment_increment=值;设置步长，设置后本次连接下所有的标识列都共用此设置
6. mysql不支持设置初始值，但可以通过插入第一条数据时给定标识列的值的方式，达到设置初始值的方式
7. 可以通过show variables like ‘%auto_increment%’;语句查看系统中标识列相关的属性，可以看到步长和初始值

修改表时设置标识列

alter table tab_identity modify column id int primary key auto_increment;

修改表时删除标识列

alter table tab_identity modify column id int;

DCL(数据控制语言)

• 修改密码

  • 格式：mysqladmin -u用户名 -p旧密码 password 新密码
    • 例1：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令：mysqladmin -uroot -password ab12 注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
    • 例2：再将root的密码改为djg345。mysqladmin -uroot -pab12 password djg345

• 增加新用户。

  • 注意：和上面不同，下面的因为是MySQL环境中的命令，所以后面都带一个分号作为命令结束符

  • 格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

    • 例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：

      grant select,insert,update,delete on . to test2@localhost identified by “abc”;

    • 如果你不想test2有密码，可以再打一个命令将密码消掉。

      grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

TCL(事务控制语言)

• 概念：Transaction Control Language 事务控制语言

事务事务

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个mysql语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺利执行。

mysql中的存储引擎【了解】

• 概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
• 通过show engines；来查看mysql支持的存储引擎
• 在mysql中用的最多的存储引擎有：innodb，myisam，memory等。其中innodb支持事务，而myisam、memory等不支持事务

事务的ACID属性

1. 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态切换到另一个一致性状态。
3. 隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建

• 事务的分类

1. 隐式事务：事务没有明显的开启和结束标记，比如insert、update、delete语句
2. 显示事务：事务具有明显的开启和结束的标记

• 显示事务前提：必须先设置自动提交功能为禁用，set autocommit=0；
• 显示事物创建步骤：

3. 设置自动提交禁用：set autocommit=0；
4. 开启事物：start transaction；可选的，自动提交禁用默认会开启事务
5. 编写事务中的SQL语句（select、insert、update、delete），ddl语句没有事务
6. 结束事物：a.commit；提交 b.rollback；回滚
7. savepoint 节点名：设置保存点
8. rollback to 节点名：回滚到某个保存点

数据库的隔离级别

• 概念：对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

1. **脏读：**对于两个事务T1、T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
2. **不可重复读：**对于两个事务T1、T2，T1读取了一个字段，然后T2 更新了该字段之后，T1再次读取同一个字段，值就不同了。
3. **幻读：**对于两个事务T1、T2，T1读取了一个字段，然后T2再该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行。（与脏读类似，只是脏读针对一个字段，幻读针对一条数据，也就是脏读针对update语句，幻读针对delete和insert语句）

• 数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会互相影响，避免各种并发问题。
• 一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。
• 数据库提供的4钟事务隔离级别：

隔离级别	描述
READ UNCOMMITTED（读未提交）	允许事务读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现
READ COMMITTED（读已提交）	只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题任然可能出现
REPEATABLE READ（可重复读）	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更改，可以避免脏读和不可重复读，但幻读的问题任然存在
SERIALIZABLE（串行化）	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有的并发问题都可以避免，但性能十分低下

• Oracle支持2钟事务隔离级别：READ COMMITED，SERIALIZABLE。Oracle默认的事务隔离级别为READ COMMITED
• mysql支持4中事务隔离级别。mysql默认的事务隔离级别为REPEATABLE READ。

隔离级别示例

• 查看当前事务隔离级别

  # 5.0数据库版本下查看
  select @@tx_isolation;
  # 8.0数据库版本下查看
  select @@transaction_isolation;
  

• 修改当前事务隔离级别

  # 设置当前mysql连接事务隔离级别为读未提交,session可以不写，默认为当前会话的
  set 【session】 transaction isolation level read uncommitted;
  # 设置当前mysql连接事务隔离级别为读以提交，如果不加session，可能效果不会马上显示
  set s【session】 transaction isolation level read committed;
  # 设置数据库系统的全局隔离级别，所有连接有效，可能需要重启才会有效
  set global transaction isolation level read committed;
  

• 使用两个命令行窗口模拟两个事务进行操作即可
• 每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@@tx_isolation，表示当前的事物隔离级别。
• savepoint使用

set autocommit=0;
start transaction;
delete from account where id = 25;
savepoint a;
delete from account where id = 28;
rollback to a;
select * from account;#25号被删除，28号没有删除

delete和truncate语句在事务中的使用

• 演示delete

  set autocommit=0; # 设置取消自动提交
  start transaction; # 打开一个事务
  delete from account; # 运行delete语句
  rollback; # 事务回滚，数据没有被删掉
  

• 演示truncate

  set autocommit=0; # 设置取消自动提交
  start transaction; # 打开一个事务
  truncate table account; # 运行delete语句
  rollback; # 事务回滚，表已被清空
  

数据类型

整数

整数类型	关键字	字节	范围
微整形	Tinyint	1	-128~127（无符号范围：0 ~ 255）
小整形	Smallint	2	-32768~32767（无符号范围：0 ~ 65535）
中间	mediumint	3	-8388608~8388607（无符号范围：0 ~ 1677215）
整形	Int、integer	4	2147483648~2147483647（无符号范围：0 ~ .。。）
大整形	Bigint	8	-9223372036854775808~9223372036754775807

• 如何设置无符号，如果不设置则默认是有符号

  # 通过关键字unsigned设置为无符号
  create table tab_int(
  	t1 int unsigned;
  )
  #此时只能是大于0的值，如果插入负数，则有警告，插入的是临界值0
  

• 长度，如果不设置有默认长度。对于整形来讲，数据范围是由类型觉得的，长度只是显示的位数。不足部分可以用0填充，但是要用关键字zerofill

  # 设置int长度为7，如果不做任何操作，插入数据是123，则显示123
  # 如果设置int长度7，用关键字zerofill，则显示0000123
  # 设置了用0填充后默认的类型都是无符号类型。再设置无符号没有意义，不设置也是无符号的
  create table tab_int(
  	it int(7) zerofill;# 不足用0填充
  )
  

小数

浮点型	字节	范围
float	4	与java类似
double	8	于java类似
定点数	字节	范围
DECIMAL（M，D）简写：DEC（M，D）	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

• M和D解释
  • M表示整数部位+小数部位的总长度
  • D表示小数部位长度
  • 如果超过范围，则插入临界值
• M和D都可以省略
  • 如果是float和double类似，则是根据插入的值自动确认的，根据值变化
  • 如果是decimal，则默认是(10,0);也就是只要有小数，就超出范围了
  • 顶点类型的精确度较高，如果是要求插入数值的精度较高，如货币运算等则考虑使用。

字符

字符串类型	最多字符数	描述及存储需求	效率
char(M)	M	M为0~255之间的整数	较高
varchar(M)	M	M为0~65535之间的整数	较低

• char为定长字符串，M可以省略，默认是1，如char(10),插入两个字符，占用依然是10字符

• varchar为变长字符串，M不可以省略，如varchar(10),插入几个字符就是几个字符。

• 其他字符类型

  • binary：类似于char，不同的是包含二进制字符串而不包含非二进制字符串，用于保存较短的二进制

  • varbinary：类似于varchar，不同的是包含二进制字符串而不包含非二进制字符串，用于保存较短的二进制

  • Enum类型：又称为枚举类型，要求插入的值必须属于列表中指定的值之一。如果列表成员为1 ~ 255，则需要1个字节存储，如果列表成员为255 ~ 65535，则需要2个字节存储，最多需要65535个成员！

    # c1类型为enum类型，只能插入a，b，c中间的一个值，不区分大小写
    create table tab_char(
    	c1 enum('a','b','c')
    )
    # 插入一个值
    insert into tab_char values('a');
    

  • Set类型：和Enum类型类似，里面可以保存0 ~ 64个成员，和Enum类型最大的区别是Set类型一次可以选取多个成员，而Enum只能选取一个，根据成员个数不同，存储所占字节不同，每8个成员占一个字节，比如8个成员占1字节，64成员占8字节。

    # c1类型为set类型，只能插入a，b，c中间的一个值,不区分大小写
    create table tab_char(
    	c1 set('a','b','c')
    )
    # 插入值
    insert into tab_char values('a,b,c');
    # 插入值，不区分大小写
    insert into tab_char values('A,c');
    

    类型	大小	用途
    TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
    TINYTEXT	0-255 bytes	短文本字符串
    BLOB	0-65 535 bytes	二进制形式的长文本数据
    TEXT	0-65 535 bytes	长文本数据
    MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
    MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
    LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
    LONGTEXT	0-4 294 967 295 bytes	极大文本数据

日期

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• datetime和timestamp的区别
  • Timestamp支持的时间范围较小，取值范围：19700101080001–2038年的某个时刻，datetime的取值范围：1000-1-1–999-12-31
  • timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
  • timestamp的属性受mysql版本和SQLMode的影响很大。
• 分类总结
  • date：只保存日期
  • time：只保存时间
  • year：只保存年
  • datetime：保存日期+时间
  • timestamp：保存日期+时间

数据类型选取原则

• 所选择的类型越简单越好，能保存数值的类型越小越好。

表间关系

• 一对一
  • 一个学生对应一个校园卡
• 一对多，多对一
  • 一个用户对应多个收货地址
• 多对多
  • 一个学生选修了多门课程，每门课程有多个学生选修

视图

概念：

• 是一个虚拟表，和普通表一样具备行列，使用也是一样的
• 是mysql5.0.1版本出现的新特性，是通过数据库表动态生成的数据集，也就是说临时存在的
• 通俗的讲就是讲比较复杂的查询语句的结果封装为视图，后续再次使用时不需要去查询，直接使用视图即可。
• 比如普通班和舞蹈班。如同普通表和视图。舞蹈班是在活动期间临时组成，任何活动都可以出现，所以对应的视图有如下特性：
  • 临时性
  • 复用性
• 总结：mysql从5.0.1版本开始提供视图功能，一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了SQL逻辑，不保存查询结果
• 应用场景：
  • 多个地方用到同样的查询结果
  • 该查询结果使用的SQL语句比较复杂
• 示例：

  create view my_vi as select studentname,majorname from student s inner join major m on s.majorid=m.majorid where s.majorid=1;
  

简单使用

• 查询姓张的学生和对应的专业名

  select stuname,majorname from stuinfo s inner join major m on s.majorid=m.id where stuname like '张%';
  

• 使用视图实现上面效果

  # 先创建一个视图，查询姓名和专业名,视图名v1
  create view v1 as select stuname,majorname
  from stuinfo s
  inner join major m on s.majorid=m.id;
  # 使用视图完成需求
  select * from v1 where stuname like '张%';
  # 注意：此处的v1像一张表一样。这就是视图的特性，但是不是真的保存了虚拟表，而是SQL逻辑，是在SQL运行中动态生成的虚拟表
  

• 语法

  create view 视图名
  as
  查询语句;
  # 一般是较为复杂的查询语句，如果是简单的SQL语句，没有必要使用视图
  

• 在employees数据库中完成下面查询：

  # 查询姓名中包含a字符的员工名、部门名和工种信息
  create view myv1 as select last_name,department_name,job_title
  from employees e
  join departments d on e.department_id = d.department_id
  join jobs j on j.job_id = e.job_id;
  
  select * from myv1 where last_name like '%a%';
  
  # 查询各部门的平均工资级别
  #1. 创建视图查看各部门的平均工资
  create view myv2 as select avg(salary) ag,department_id from employees group by department_id;
  #2. 使用视图查看对应工资级别
  select myv2.ag,g.grade_level from myv2 join job_grades g on myv2.ag between g.lowest_sal and g.highest_sal;
  # 查询平均工资最低的部门信息(使用上面的视图)
  select * from myv2 order by ag limit 1;
  # 查询平均工资最低的部门名和工资(可以在一个视图的基础上创建另一个视图)
  create view myv3 as select * from myv2 order by ag limit 1;
  select d.*,m.ag from myv3 m join departments d on m.department_id=d.department_id;
  

通过上面的实例会发现视图有如下好处

• 重用SQL语句
• 简化复杂的SQL操作，不必知道它的查询细节
• 保护数据，提高安全性

视图的修改

• 方式一：如果该视图不存在则创建，如果存在则修改

  create or repalce view 视图名
  as
  查询语句;
  

• 方式二：直接修改视图，与修改表语句类似，用alter关键字

  alter view 视图名
  as
  查询语句;
  

视图的删除查询

• 删除视图：使用drop关键字，可以一次删除多个视图

  drop view 视图名1、视图名2、视图名。。。
  

• 查询视图：与查询表一样，可以使用desc或者show语句

  desc 视图名; #查看视图简单结构
  show create view 视图名; # 查看视图的详细创建过程
  

视图的更新（视图中数据的更新，不是视图结构的修改）

• 视图本身就是虚拟的表格，可以按照正常表格进行数据插入、修改删除等

  # 创建简单视图供使用
  create view myv1 as select last_name,email from employees;
  

• 插入数据（原表中也会插入数据，原表中有视图中没有的数据null填充）

  # 给视图中传入一条数据，原表也会插入，视图中没有的列以null填充
  insert into myv1 values('张三','zs@qq.com');
  

• 修改数据（原表中数据也会修改）

  # 修改视图中的一条数据，原表中数据也会修改
  update myv1 set last_name = '李四' where last_name='张三';
  

• 删除数据

  # 删除一条数据，原表数据也会被删除
  delete from myv1 where last_name='李四';
  

• 注意：

视图中的数据虽然可以被更新，但是这样对原表的数据不安全，所以一般会设置视图权限为只读，或者设置用户对视图的修改权限等
视图的可更新和视图中查询的定义有关系，以下类型的视图是不能更新的：

包含以下关键字的SQL语句：分组函数、distinct、group by、having、union或者union all
常量视图
select中包含子查询
join
from一个不能更显的视图，也就是该视图创建于其他视图，其他视图不能被更新
where字句的子查询引用了from字句中的表

视图和表对比

结构	关键字	占内存情况	操作内容
表	create table name	保存逻辑和数据	增、删、改、查
视图	creaate view name	只保存SQL逻辑	增、删、改（基本不使用）、查（主要使用）

变量

概念及分类

概念：

与java中一样 ，一个可以改变的量。

分类

系统变量：

• 全局变量：
• 会话变量

自定义变量：

• 用户变量
• 局部变量

具体介绍

系统变量

说明：变量由系统提供，不是用户定义，属于服务器层面

使用语法：

• 查看所有系统变量

  # 查看全局变量
  show global variables;
  # 查看会话变量,session关键字可不写，默认就是查看会话变量
  show session variables;
  show variables;
  

• 查看满足条件的部分系统变量(类似模糊查询，like后跟值，通配符正常使用)

  # 查看全局变量
  show global variables like '%char%';
  # 查看会话变量
  show session variables like '%char%';
  show variables like '%increment%';
  

• 查看指定的某个系统变量的值

  # 查看全局变量的值,后面是变量名
  select @@global.character_sets_dir;
  # 查看会话变量的值,后面是变量名
  select @@session.auto_increment_increment;
  select @@auto_increment_increment;
  

• 为某个具体的系统变量赋值

  方式一：set global | 【session】系统变量名=值
  方式二：set @@global | 【session】。系统变量名=值

  set global auto_increment_increment=2;
  set session auto_increment_increment=1;
  set auto_increment_increment=3;
  set @@global.autocommit=0;
  set @@session.autocommit=0;
  set @@autocommit=0;
  

• 注意：

  如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session级别
  全局变量的作用域是作用于所有会话（连接），每次数据库服务重启以后都会为所有变量赋予初始值，用户设置变量全局变量值以后作用所有连接，但是不会垮重启，如果想每次重启依然奏效，必须修改配置文件
  会话变量的作用域是一次连接，只对当前连接有效，每次服务器重启，都会对当前会话的所有变量赋予初始值

自定义变量

说明：变量是由用户自定义，不再由系统提供

使用步骤（与java中类似）：

1. 声明
2. 赋值
3. 使用

具体说明

• 用户变量

  作用域：针对于当前会话（连接）有效，同于系统变量中的会话变量的作用域
  使用位置：可以在任意位置。可以是在begin end中，也可以在begin end外面使用
  注意：使用时不需要指定变量类型，类型根据值自动确定，在声明时赋值初始化
  使用步骤如下：
  声明并初始化：三种方式

  set @用户变量名=值；
  set @用户变量名:=值；
  select @用户变量名:=值；

  赋值(更新用户变量的值):

  方式一：通过set或者select（同初始化）

  1. set @用户变量名=值；
  2. set @用户变量名:=值；
  3. select @用户变量名:=值；

  方式二：通过select into

  1. 语法：select 字段 into @用户变量名 from 表名；
  2. 注意：通过select查表中字段结果只能是一个值，字段可以是函数等
  3. 示例：select count(*) into @count from employees;

  使用（查询）

  语法：select @用户变量名；
  示例：select @count;

• 局部变量

  作用域：仅仅定义在begin end中有效
  使用位置：应用在begin end中，且必须是begin end中第一句
  注意：可以只声明不赋值初始化，必须指定类型
  使用步骤如下：
  声明（声明并初始化）

  只进行声明：declare 变量名 类型；不需要@符号
  声明初始化：declare 变量名 类型 default 值；值的类型与前面给定类型一样或兼容

  赋值（与用户变量赋值类似）

  方式一：通过set或者select（同初始化）

  1. set 用户变量名=值；与用户变量不同，不需要加@符号
  2. set 用户变量名:=值；与用户变量不同，不需要加@符号
  3. select @用户变量名:=值；与用户变量一样

  方式二：通过select into

  1. 语法：select 字段 into 变量名 from 表名；与用户变量不同，不需要加@符号
  2. 注意：通过select查表中字段结果只能是一个值，字段可以是函数等
  3. 示例：select count(*) into count from employees;

  使用（查询）

  语法：select 局部变量名；不需要加@符号

• 用户变量与局部变量对比

  变量类型	作用域	定义和使用位置	语法
  用户变量	当前会话	当前会话任何地方	必须加@符号，不用限定类型
  局部变量	begin end中	只能在begin end中且第一句话	一般不用加@符号，需要限定类型

• 案例：定义两个变量，求和，并打印结果

存储过程和函数

基础概念

存储过程和函数

理解：类似于java中的方法

好处：

• 提高代码的重用性
• 简化操作

存储过程(多用于增删改，如批量添加)

含义

一组预先编译好的SQL语句的集合，可以理解成批处理语句

好处

• 提高代码的重用性
• 简化操作
• 减少了代码的编译次数并且减少了和数据库服务器的连接次数，提高了效率（首次运行会编译，之后运行会看没没有被编译，如果有，则不需要编译了）

语法

创建语法

• 基本语法

  create procedure 存储过程名(参数列表) 
  begin
  	存储过程体（一组合法的SQL语句）
  end
  

• 注意点：

  参数列表包含三部分：

  参数模式：in out inout，不写默认是in
  参数名：自定义，形参名
  参数类型：数据库存在的类型

  参数模式有三种：

  in:该参数可以作为输入，也就是该参数需要调用者传入值
  out:该参数可以作为输出，也就是该参数可以作为返回值
  inout:该参数既可以作为输入又可以作为输出，也就是既需要传入值又可以返回值

  存储过程注意：

  如果存储过程体仅仅只有一句话，begin end可以被省略
  存储过程体中的每条SQL语句的结尾要求必须加封号

  结尾部分

  存储过程接我不再使用封号，需要特殊语法
  存储过程结尾可以使用delimiter重新设置,以这个自定义符号作为结束符
  语法：delimiter 结束标记；（结束标记自定义）
  示例：delimiter $，定义好以后其他语句都使用该标记作为结束标记。除了存储过程体中的SQL依然使用封号

调用语法

• 基本语法

  call 存储过程名(实参列表) 定义的结束标记
  

案例

• 空参数列表

  # 存入5条数据进表
  delimiter $ #定义结束标记
  create procedure myp1()
  begin
  insert into admin(username,password) values ('john','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
  end $ #创建存储过程，使用定义的结束标记
  call myp1()$ # 调用存储过程，使用定义的结束标记
  select * from admin$ # 查询表中数据已经存入，依然使用定义的结束标记
  

• 带in模式参数的存储过程

  # 案例1： 创建存储过程实现根据女神名，查询对应男神信息（一个参数）
  delimiter $
  create procedure myp2(in beautyName varchar(20))
  begin
  	select bo.*
  	from boys bo
  	right join beauty b on bo.id = b.boyfriend_id
  	where b.name=beautyName; # 跟java中方法一样，直接使用参数
  end $
  call myp2('柳岩') $
  *************************************************
  # 案例2： 创建存储过程实现用户是否登录成功（两个参数）
  delimiter $
  create procedure myp3 (in username varchar(20),in pasword varchar(20))
  begin
  	declare result varchar(20) default'';# 声明初始化一个变量
  	select count(*) into result # 将查询结果赋值给变量
  	from admin
  	where admin.username = username # 当参数与字段重名，则使用表名点区分
  	and admin.password = password;
  	select result; # 查询/打印 变量的值得到结果
  end $
  # 调用
  call myp3('张飞','8888') $
  *******************************************************
  # 对于刚才的案例，可以使用if函数，对查询结果进行处理
  delimiter $
  create procedure myp4 (in username varchar(20),in pasword varchar(20))
  begin
  	declare result varchar(20) default'';# 声明初始化一个变量
  	select count(*) into result # 将查询结果赋值给变量
  	from admin
  	where admin.username = username # 当参数与字段重名，则使用表名点区分
  	and admin.password = password;
  	select if(result>0,'成功','失败'); # 查询/打印 根据变量值转换结果
  end $
  # 调用
  call myp4('张飞','8888') $
  

• 带out模式的存储过程

  # 案例1： 根据女神名返回对应的男神名（一个out参数）
  delimiter $ # 创建结束符号
  create procedure myp5(in beautyName varchar(20),out boyName varchar(20))
  begin
  	select bo.boyName into boyname # 将查询结果赋值给返回值参数
  	from boys bo
  	inner join beauty b on bo.id=b.boyfriend_id
  	where b.name=beautyName;
  end $
  # 调用
  set @bName $ # 可以先创建一个参数用于接收，相当于实参，也可以不写这一句
  call myp5('小昭',@bName)$ # 如果上面没有创建变量，直接写相当于创建了，后面正常使用
  select @bName$ #显示结果，上面的参数可以不用创建，直接写
  ***********************************************************
  # 案例2：根据女神名，返回对应的男神名和男神魅力值（两个out参数）
  delimiter $
  create procedure myp6(in beautyName varchar(20),out boyName varchar(20),out userCP int)
  begin
  	select bo.boyName,bo.userCP into boyName,userCP # 先查所有字段，再依次赋值
  	from boys bo
  	inner join beauty b on bo.id = b.boyfriend_id
  	where b.name=beautyName;
  end $
  # 调用
  call myp6('小昭',@bName,@userCP)$ # 直接给值，不需要创建变量
  # 打印结果
  select @bName 姓名,@userCP 魅力值$    
  

• 带inout模式的存储过程

  # 传入a和b两个值，翻倍后返回
  create procedure myp7(inout a int,inout b int)
  begin
  	set a=a*2; # 变量翻倍并返回，就是变量重新赋值，局部变量赋值方式
  	set b=b*2; # 因为是相当于变量重新赋值，所以不需要特殊的返回方法
  end $
  # 调用
  set @m=10$ # 必须先创建变量。这样才可以
  set @n=20$ # 如果没有变量，直接调用写的值，则返回值无法获取
  call myp7(@m,@n)$
  # 打印结果
  select @m,@n$
  

删除存储过程

• 语法

  drop procedure 存储过程名；

• 注意：与删库或删表不同，存储过程每次只能删除一个，不能使用逗号删除多个
• 示例：

  drop procedure myp6;# 正常删除
  drop procedure myp7,myp8; # 报错
  

查看存储过程

• desc关键字可以查看表和视图，但是无法查看存储过程
• 可以使用show create procedure 存储过程名 查看对应的存储过程
• 示例

  # 查询某个存储过程的定义
  show create procedure myp8;
  # 查询存储过程的状态信息
  show procedure status;
  # 查询db_name数据库中的所有存储过程
  select name from mysql.proc where db=db_name;
  

函数

含义

一组预先编译好的SQL语句的集合，理解成批处理语句（与存储过程一样）

好处

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

语法

创建语法

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

• 注意点

  参数列表包含两部分：参数名 参数类型
  函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不会报错，但不建议，一般逻辑处理完成后加 return 值；
  函数体中仅有一句话，则可以省略begin end
  使用delimiter语句设置结束标记

调用语法

select 函数名(参数列表)

函数与存储过程的区别

类型	返回值个数	应用场景
存储过程	可以有0个返回，也可以有多个返回	适合做批量插入、批量更新
函数	有且仅有一个返回	适合做批处理数据后返回一个结果

示例

• 无参有返回

  # 返回公司的员工个数
  delimiter $ #设置结束标记
  create function myf1() returns int #创建函数，设置返回值类型
  begin
  	declare c int default 0;#  定义变量，保存值
  	select count(*) into c # 查询结果赋值给变量
  	from employees;
  	return c; # 返回结果
  end $
  select myf1() $ # 调用函数
  

• 有参有返回

  # 根据员工名返回工资
  create function myf2(empName varchar(20)) returns double
  begin
  	set @sal=0; # 定义一个用户变量，接受返回值
  	select salary into @sal # 将查询结果赋值给变量
  	from employees
  	where last_name = empName;
  	return @sal; 返回变量
  end $
  # 调用
  select myf2('k_ing') $
  **********************************************************
  # 根据部门名返回平均工资
  create function myf3(deptName varchar(20)) returns double
  begin
  	declare sal double;
  	select avg(salary) into sal
  	from employees e
  	join departments d on e.department_id = d.department_id
  	where d.department_name = deptName;
  	return sal;
  end $
  # 调用
  select myf3('IT') $
  

查看函数

show create function 函数名;

• 无论是存储过程还是函数，当创建好以后，都是保存在系统库中的，也就是mysql这个数据库中的proc表中的。所有创建的函数或者存储过程都可以看到

删除函数

drop function 函数名;

流程控制结构

顺序结构：程序/代码从上往下依次执行
分支结构：程序/代码从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

分支结构

if函数

• 功能：实现简单的双分支，类似于java中if。else或者三目运算
• 语法

  if (表达式1，表达式2，表达式3）；
  # 如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
  /*
  表达式1：一般就是boolean类型的表达式，分支根据其值进行
  表达式2/3：结果是值，if函数需要返回值，根据表达式1的结果进行返回
  */
  if(age>18,'成年人','未成年');
  select name,if(age>18,'成年人','未成年') from users;
  

• 应用：任何地方

case结构

情况1：

• 类似于java中的switch语句，一般用于实现等值判断
• 语法：

  case 变量|表达式|字段
  when 要判断的值1 then 返回的值1（或语句1；）
  when 要判断的值2 then 返回的值2（或语句2；）
  。。。
  【else 要返回的值n（或语句n；）】
  end （case；）
  

• 解释

  语法含义：就是拿着case后面的值与when后面的值挨个比较，找到匹配的然后返回对应then后面的值或者是在begin end中时执行后面的语句
  上面的结构的使用一般是配合select或update等语句一起出现，此时then后面都是固定的返回值，也不需要封号，最后直接end结束即可
  上面的结构也可以单个的作为语句出现，这种情况下只能出现在begin end中，此时then后面就不再是值，而是语句，那就要加上封号且最后是end case封号结尾
  总结：可以作为表达式，嵌套在其他语句中，可以放在任何地方，begin end外或里面，可以作为独立的语句使用，只能放在begin end中

• 示例

  select age, case age when 20 then '20岁' when 25 then '25岁' when 28 then '28岁' else '没有年龄' end as '年龄' 
  from users;
  

情况2：

• 类似于java中的多重IF语句，一般用于实现区间判断
• 语法：

  case 
  when 要判断是条件1 then 返回的值1（或语句1；）
  when 要判断的条件2 then 返回的值2（或语句2；）
  。。。
  【else 要返回的值n（或语句n；）】
  end （case；）
  

• 解释

  语法含义：与情况1不同，case后面没有任何东西，直接判断when后面的条件，一旦成立，直接返回对应then后面的值或者执行后面的语句
  上面的结构的使用一般是配合select或update等语句使用，此时then后面都是固定的值，不需要封号，以end结尾即可
  上面的结构也可以单出作为语句出现，这种情况下只能出现在begin end中，此时then后面就是语句，所以需要封号结尾，且最后需要在end后使用case；结尾
  总结：可以作为表达式，嵌套在其他语句中，可以放在任何地方，begin end外或里面，可以作为独立的语句使用，只能放在begin end中

• 示例

  # 创建存储过程，根据传入的成绩，来显示等级
  create procedure test_case (in score int)
  begin
  	case
  	when score>=90 and score<=100 then select 'A'; #作为单独语句，then后面是语句，封号结尾
  	when score>=80 then select 'B';
  	when score>=60 then select 'C';
  	else select 'D';
  	end case; # 结尾部分需要加end case；
  end $
  

if结构

• 功能：实现多重分支
• 语法

  if 条件1 then 语句1;
  elseif 条件2 then 语句2;
  。。。
  【else 语句n;】
  end if;
  

• 应用：只能放在begin end中
• 示例

  # 根据传入的成绩，返回等级
  create function test_if(score int) returns char
  begin
  	if score>=90 and score<=100 then return 'A';
  	elseif score>=80 then return 'B';
  	elseif score>=60 then return 'C';
  	else return 'D';
  	end if;
  end $
  

循环结构（必须在begin end里面）

分类

• while循环
• loop循环
• repeat循环

循环控制语句

• iterate：类似于java中的continue，指继续，也就是结束本次循环，继续下一次
• leave：类似于break，跳出，结束当前所在的循环
• 必须配合循环标签一起使用

详解

while

• 语法：

  【标签：】while 循环条件 do
  	循环体;
  end while 【标签】;
  # 标签相当于给这个循环起了名字，可以不需要写，就是一个正常的循环
  

• 示例

  # 批量插入，根据传入的次数插入admin表多条记录
  create procedure test_while (in insertcount int)
  begin
  	declare i int default 1; 创建变量i控制循环
  	while i<=insertcount do
  		insert into admin (username,password) values (concat('rose',i),'123');
  		set i=i+1; # 变量i自增
  	end while;
  end $
  # 批量添加，如果次数大于20，则退出，配合leave使用
  create procedure test_while(in insertcount int)
  begin
  	declare i int default 1; #创建变量，控制循环
  	a:while i<=insertcount do # 创建循环
  		insert into admin(username,password) values(concat('xiaohua',i),'0000'); # 插入数据
  		if i>=20 then leave a; # if结构判断次数是否到了20，大于等于20次则退出循环
  		end if; # 结束if结构
  		set i = i+1; # 修改变量
  	end while a; # 结束循环
  end $ # 结束存储过程
  # 调用存储结构
  call test_while(100)$
  # 批量插入，只插入偶数次，配合iterate使用
  create procedure test_while (in int insertcount)
  begin
  	declare i int default 0;
  	a:while i<=insertcount do
  		set i=i+1;
  		if mod(i,2)!=0 then iterate a; # mod求摸函数
  		end if;
  		insert into admin(username,password) values(concat('xiaohua',i),'0000');
  	end while a;
  end $
  

loop

• 语法

  【标签：】loop
  	循环体;
  end loop 【标签】;
  # 没有循环条件，相当于死循环，可以搭配控制语句leave和iterate使用
  

• 示例

  # 计算1到n的和
  create procedure pro_test10(n int)
  begin
  	declare total int default 0;
  	c:loop
  		set total=total+n;
  		set n=n-1;
  		if n<=0 then
  			leave c;
  		end if;
  	end loop c;
  	select total;
  end$
  

repeat

• 语法

  【标签：】repeat
  	循环体;
  until 结束循环的条件
  end repeat【标签】;
  # until是直到的意思
  # until后面的条件是结束循环的条件，意思是一直执行，直到后面条件成立才结束,条件后面不能写封号，会报错
  # 类似于do。。while，先执行一次循环体
  

• 示例

  # 计算1累加到n的和
  create procedure pro_test9(n int)
  begin
  	declare total int default 0;
  	repeat
  		set total=total+n;
  		set n=n-1;
  		until n=0 # 此处不写结束符，会报错
  	end repeat;
  	selest total;
  end $
  

循环结构综合示例

• 创建表stringcontent，字段有id自增，content varchar（20）
• 向表中按照给定数值插入对应条数的记录，且要求插入的字符串随机

  create table strigcontent(
  	id int primary key auto_increment,
  	content varchar(20)
  );
  delimiter $
  create procedure test_randstr_insert(in insertCount int)
  begin
  	declare i int default 1; # 定义一个循环变量i，表示插入的次数
  	declare str varchar(26) default 'abcdefghijklmnopqrstuvwxyz'; # 定义字符串，用于截取随机字符串
  	declare startIndex int default 1; # 定义变量，表示截取字符串的起始下标，从1开始
  	declare len int default 1; # 定义变量，表示截取的字符串的长度，最小为1
  	while i<= insertCount do
  		set startIndex = floor(rand()*26+1); # floor是向下取整函数，rand函数生成随机数为0到1之间，再乘以26表示0到25之间，再加1就是向下取整表示1到26
  		set len = floor(rand()*(20-startIndex+1)+1); # 产生一个随机数，长度只能是字符串长度26减去起始下标再加1，但是表中字段长度为20，所以为20减去起始下标
  		insert into stringcontent(content) values(substr(str,startIndex,len)); # 插入数据，substr函数用于截取字符串
  		set i = i+1; # 循环变量更新
  end $
  # 调用
  call test_randstr_insert(20) $
  # 查询结果是否正确
  select * from stringcontent$
  

索引

索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引优势劣势

优势

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势

• 实际上索引也有一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引也是要占用内存空间的。
• 虽然所以大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT 、UPDATE、DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引的结构

概念

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4钟索引：

• BTREE索引：最常见的索引类型，大部分索引都支持B树索引。
• HASH索引：只有memory引擎支持，使用场景简单。
• R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text索引（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始支持全文索引。

不同引擎对索引的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本后支持	支持	不支持

• 我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉树）结构组织的索引，其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称索引。

BTREE结构

BTREE又叫多路平衡搜索时，一棵m叉的Btree特性如下：

• 树中每个节点最多包含m个孩子
• 除根节点与子节点外，每个节点至少有[ceil(m/2)]个孩子。ceil天花板函数
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1
  
• 插入过程如下：
  
• 因此，该BTREE就已经构建完成，BTREE和二叉树相比，查询数据的效率更高，因此对于相同的数据量来说，BTREE的层次结构比二叉树小，因此搜索速度快。

B+Tree结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多包含n个可以，而BTree最多含有n-1个key
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
• 所有的非叶子节点都可以看作是key的索引部分。
  

mysql中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成带有顺序指针的B+Tree，提供区间访问的性能。

• MySQL中的B+Tree索引结构示意图
  

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个列

索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

准备环境

• 创建表

  create table city(
     city_id int not null auto_increment,
     city_name varchar(50) not null,
     country_id int not null,
     primary key(city_id)
  ) engine=InnoDB default charset=utf8;
  create table country(
  	country_id int not null auto_increment,
  	country_name varchar(100) not null,
  	primary key (country_id)
  )engine=InnoDB default charset=utf8;
  insert into country (country_id,country_name) values (1,'China');
  insert into country (country_id,country_name) values (2,'America');
  insert into country (country_id,country_name) values (3,'Japan');
  insert into country (country_id,country_name) values (4,'UK');
  insert into city (city_id,city_name,country_id) values (1,'西安',1);
  insert into city (city_id,city_name,country_id) values (2,'NewYork',2);
  insert into city (city_id,city_name,country_id) values (3,'东京',3);
  insert into city (city_id,city_name,country_id) values (4,'伦敦',4);
  

创建索引

• 语法

  create 【unique|fulltext|spatial】 index index_name
  【using index_type】
  on table_name (index_column,...);
  

• 说明

  unique|fulltext|spatial：索引类型，可以不写，唯一索引，全文索引等
  index_name：索引名
  using index_type：指定索引数据结构，可不写，默认B+Tree结构
  table_name：表名，指定要为那张表创建索引
  index_column：【(length)】【ASC|DESC】

• 示例

  # 为city表中的city_name创建索引
  create index idx_city_name on city (city_name);
  

查看索引

• 语法

  # 查询某表中的索引
  show index from table_name;
  # 查询结果可以格式化显示
  show index from table_name\G;
  

• 示例

  show index from city;
  show index from city\G;
  

删除索引

• 语法

  # 删除某表中的某索引
  drop index index_name on table_name;
  

• 示例

  drop index idx_city_name on city;
  

alter命令

• 语法

  alter table table_name add primary key(column_list);
  # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
  alter table table_name add unique index_name (column_list);
  # 该语句创建索引的值必须是唯一的，唯一索引（除了null外，null可能出现多次）
  alter table table_name add index index_name(colunm_list);
  # 添加普通索引，索引值可以出现多次
  alter table table_name add fulltext index_name(solunm_list);
  # 该语句指定了索引为FULLTEXT，用于全文索引
  

• 示例

  alter table city add primary key(city_id);
  alter table city add unique index_name(city_name);
  alter table city add index idx_city_name(city_name);
  alter table city add fulltext idx_fu_name(city_name);
  

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引

• 对查询频率较高，且数据量比较大的表建立索引
• 索引字段的选择，最佳候选列应当从where字句的条件中提取，如果where字句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难症，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
• 创建复合索引

  create index index_name on table_name(name,email,status);
  # 相当于
  对name创建了索引
  对name,email创建了索引
  对name,email,status创建了索引
  

索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

验证索引提升查询效率

在我们准备的表中，存储了300万条数据

• 根据ID查询

  select * from tb_item where id = 1999\G;
  

• 查询速度很快，接近0s，主要的原因是因为id是主键，有索引；
• 根据title字段进行精确查询

  select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;
  

• 消耗时间非常长，查看SQL执行计划：

  explain select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;
  

• 处理方案，针对title字段，创建索引：

  # 该过程会持续1分钟左右，在于表中数据量较大，创建索引耗时较长
  create index idx_item_title on tb_item(title);
  

• 索引创建完成后，再次进行查询

  # 对比刚才的5秒，此时只需要0.01秒时间
  select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;
  

• 通过对比前后SQL耗时情况，可以得出索引对于我们查询效率的提升

索引的使用

环境准备

create table `tb_seller` (
	`sellerid` varchar(100),
	`name` varchar(100),
	`nickname` varchar(50),
	`password` varchar(60),
	`status` varchar(1),
	`address` varchar(100),
	`createtime` datetime,
	primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;

insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('iotek','海同科技有限公司','海同科技','e10adc3949ba59abbe56e057f20f883e','1','上海市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('haitong','海同优才','海同优才','e10adc3949ba59abbe56e057f20f883e','0','上海市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values ('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
# 创建了一个复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效

1. 全值匹配，对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。

   # 查出使用了索引，且条件中的三个字段符合复合索引的字段
   explain select * from tb_seller where name='小米' and status='1' and adress='西安市';
   

2. 最左前缀法则，如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列，也就是必须从name开始，且不能跳过status直接到address。但是跟where后面书写顺序无关

   # 符合最左前缀法则，用了到索引
   explain select * from tb_seller where name='小米科技';
   # 符合最左前缀法则，用到了索引
   explain select * from tb_seller where name='小米科技' and status='1';
   # 符合最左前缀法则，用到了索引
   explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
   # 符合最左前缀法则，与书写顺序无关，用到了索引
   explain select * from tb_seller where status='1' and address='北京市' and name='小米科技';
   # 用到了索引，但只是name一个用了，因为跳过了status，所以后面的address没有用索引，与单独的name查询时索引一致
   explain select * from tb_seller where name='小米科技' and address='北京市';
   # 不符合最左前缀法则，没有用到索引
   explain select * from tb_seller where status='1' and address='北京市';
   # 不符合最左前缀法则，没有用到索引
   explain select * from tb_seller where address='北京市';
   

3. 范围查询右边的列，不能使用索引

   # 因为status使用了范围查询，索引只有前面的name和status使用了索引，status条件右边的address则没有使用索引
   explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';
   

4. 不要在索引列上进行运算操作，索引将失效

   # 因为对name进行了运行，导致该语句没有使用到索引
   explain select * from tb_seller where substring(name,3,2) = '科技';
   

5. 字符串不加单引号，索引失效

   # status因为没有加单引号，导致该语句中只有name使用了索引，而对于后面的status则没有使用索引，根据索引长度可以得出结论
   explain select * from tb_seller where name='科技' and status=0;
   # 原因是底层SQL优化器发现status是varcahr类型后，会对没有加单引号的0进行隐式类型转换，也就相当于进行了运算，导致索引失效
   

6. 尽量使用覆盖索引，避免select*，尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select*。如果查询列超出索引列也会降低性能

   # 得到的结果是使用了索引，但是描述信息也就是Extra中提示Using index condition，该描述表示虽然查询时用到了索引，但是返回数据时，使用了索引回表查询。也就是先根据索引name查出了数据，但是返回所有列，所以回表获取所有列的数据。
   explain select * from tb_seller where name='小米科技';
   # 得到的结果使用了索引，而且需要的数据都在索引列中能找到，不需要回表查询，所以在Extra中提示信息为using index;using where,无论查询的列是name或者是name和status或者是name和address或者是status和address都是一样的
   explain select name,status,address from tb_seller where name='小米科技';
   # 由于查询列中的password不属于索引列，所以返回数据时依然需要回表查询，Extra显示信息依然为using index dondition,所以对于这种查询列超出索引列的情况，也会降低性能
   explain select name,status,address,password from tb_seller where name='小米科技';
   

   TIP:

   using index：使用覆盖索引的时候会出现
   using where：在查找使用索引的情况下，需要回表去查询需要的数据
   using index condition：查找使用了索引，但是需要回表查询数据
   using index;using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7. in走索引，not in索引失效

   # 用主键索引时，in是走索引的
   explain select * from tb_seller where sellerid in ('oppo','xiaomi');
   # 同样的搜索语句，如果使用notin则不走索引，原因与第10点类似
   explain select * from tb_seller where sellerid not in ('oppo','xioami');
   # 可以看到使用了索引，且扫描的行数rows为2行
   explain select name from tb_seller where name in ('小米科技','宜家家居');
   # 可以看到在not in情况下也使用了索引，但是扫描行rows达到了11行
   explain select name from tb_seller where name not in ('小米科技','宜家家居');
   # 结果也是扫描行数为2行
   explain select name from tb_seller where name in ('abc','bcd');
   # not in时扫描行数达到了12行，虽然显示使用了索引，但是显然已经失效了
   explain select name from tb_seller where name not in ('abc','bcd');
   

8. 用or分割开的条件，如果or前面的条件中的列有索引，而后面的列中没有索引，那么涉及的索引列都不会被用到

   # 表中name属于索引列，而createtime不属于索引列，如果用and连接条件，会发现第一个条件name使用了索引
   explain select * from tb_seller where name='小米科技' and createtime='2088-01-01 12:00:00';
   # 同样的条件如果用or连接，则完全没有用到索引
   explain select * from tb_seller where name='小米科技' or createtime='2088-01-01 12:00:00';
   

9. 以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊查询，索引不会失效，如果是头部模糊查询，索引会失效

   # 使用like模糊查询，%因为是放在后面的，所以依然使用了索引
   explain select * from tb_seller where name like '科技%';
   # 如果将%放置在前，会发现根本就没有使用索引
   explain select * from tb_seller where name like '%科技';
   # 前后都有%的情况也是一样的，不会使用索引
   explain select * from tb_seller where name like '%科技%';
   

   由于在开发中，对于前面使用%或者是前后都有%的情况使用较多，所以针对上面不使用所以的解决方案就是使用覆盖索引，如下：

   # 查询条件依然是前后都有%，但查询列使用了索引列，会发现索引生效
   explain select name from tb_seller where name like '%科技%';
   # 任意列，只要包含在所有列中，都会使得索引列生效
   explain select name,status from tb_seller where name like '%科技%';
   explain select status,address from tb_seller where name like '%科技%';
   # 查询列中包含sellerid也会使用索引，因为sellerid是主键，自己拥有主键索引，依然符合覆盖索引情况，所有在模糊查询时索引依然生效
   explain select sellerid,name from tb_seller where name like '%科技%';
   # 下列SQL情况与上面一样，都会使得所有生效，因为sellerid是索引列，属于主键索引
   explain select sellerid from tb_seller where name like '%科技%';
   

10. 如果MySQL评估使用索引比全表更慢，则不适用索引。

    # 首先使用address查询会发现没有使用索引，虽然address属于复合索引一部分，但是不符合最左前缀法则，所以没有使用索引
    explain select * from tb_seller where address = '北京市';
    # 为验证该结论，给address列创建独立索引
    create index idx_seller_address on tb_seller(address);
    # 此时表中有12条数据，其中9条数据的address是北京市，两条上海市，一条西安市，再使用北京市查询，会发现使用了索引，因为address有独立索引，没有问题
    explain select * from tb_seller where address = '北京市';
    # 将后面的值换掉为西安市，会发现也没有问题，索引正常使用
    explain select * from tb_seller where address = '西安市';
    # 此时，如果将表中的上海市数据改为北京市，那结果就是表中共12条数据，其中北京市10条，上海市和西安市各一条，再次使用北京市查询，会发现没有使用索引
    explain select * from tb_seller where address = '北京市';
    # 而如果是将值换成上海市或者西安市，索引依然有效，原因是当mysql优化器在优化SQL时发现表中数据，北京市几乎占了全部，评估结果就是直接扫描全表，而不再使用索引
    explain select * from tb_seller where address = '西安市';
    

11. is null，is not null。有时索引失效。

    # 会走索引，原因是address字段中全部都是不为空的值，要判断的为空的值是少数，所以走索引
    explain select * from tb_seller where address is null;
    # 不走索引，因为值都是不为空的，或者大部分都是不为空的，mysql判断走索引的效率不如全表扫描，所以不走索引
    explain select * from tb_seller where address is not null;
    # 原理是第10点一样，根据值所占的比例决定是否走索引
    

12. 单列索引和复合索引

    尽量使用复合索引，而少使用单列索引

    create index idx_name_sta_address on tb_seller(name,status,address);
    # 就相当于创建了三个索引：
    #1.name
    #2.name+status
    #3.name+status+address
    # 如果是创建了单列索引
    create index idx_seller_name on tb_seller(name);
    create index idx_seller_status on tb_seller(status);
    create index idx_seller_adress on tb_seller(adress);
    # 在使用name、status和address三列进行查询，mysql并不会使用所有的索引，而是会选择最优的（辨识度最高的）索引使用，最优就是看那个索引对应的字段数据比例低，就用那个，法则类似第10点
    

查看索引使用情况

# 仅查看当前会话中索引的使用情况
show status like 'Handler_read%';
# 查看全局索引的使用情况
show global status like 'Handler_read%';

查询结果解释说明

Handler_read_first：索引中第一条被读的次数，如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）
Handler_read_next：按照顺序读下一行的请求数，如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照顺序读前一行的请求数，该读方法主要用于优化Order by。。。DES
Handler_read_rnd：根据固定位置读一行的请求数，如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键，这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中，读下一行的请求数，如果你正进行大量的表扫描，该值较高，通常说明你的表索引不正确或写入的查询没有利用索引

游标/光标

概念

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理，光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下

基本语法

声明光标

declare cursor_name cursor for select_statement;
# cursor 游标名
# select_statement 查询语句
# 指声明一个光标对后面的查询结果集

OPEN光标

open cursor_name;
# cursor_name 光标名
# 指打开某光标（类似于有个指针一样的东西）

FETCH光标(fetch一次，游标获取一行，类似于迭代器的next）

fetch cursor_name into var_name【,var_name】...
# cursor_name 光标名
# var_name 列名
# fetch一次，指针移动一次，相当于抓取一行，然后用into赋值给变量

CLOSE光标

close corsor_name;
# corsor_name 光标名
# 指关闭某光标

示例

环境准备

create table emp(
	id int(11) not null auto_increment,
	name varchar(50) not null comment '姓名',
	age int(11) comment'年龄',
	salary int(11) comment '薪水',
	primary key(id)
)engine=innodb default charset=utf8;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

使用光标逐行获取数据

create procedure pro_test11()
begin 
	declare e_id int(11); # 声明变量用于接收结果中的id
	declare e_name varchar(50); # 声明变量用于接收结果中的name
	declare e_age int(11); # 声明变量用于接收结果中的age
	declare e_salary int(11); # 声明变量用于接收结果中的salary
	declare emp_result cursor for select * from emp; # 声明一个光标
	open emp_result; # 打开光标
	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示
	close emp_result; # 关闭光标
end$
####################################################
# 通过多次fetch取出结果集
create procedure pro_test11()
begin 
	declare e_id int(11); # 声明变量用于接收结果中的id
	declare e_name varchar(50); # 声明变量用于接收结果中的name
	declare e_age int(11); # 声明变量用于接收结果中的age
	declare e_salary int(11); # 声明变量用于接收结果中的salary
	declare emp_result cursor for select * from emp; # 声明一个光标
	open emp_result; # 打开光标
	
	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示
	
	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示

	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示

	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示

	fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示
	close emp_result; # 关闭光标
end$
# 上面语句中通过fetch取值5次，然后结果只有4行，此时前面4行结果正常显示，最后一次会报如下错误：
No date - zero rows fetched,selected,or processed

通过循环，获取光标中的数据

# 使用循环进行获取光标中数据，依然存在退出的问题，此时可以通过多种方式解决
# 要解决退出的问题，可以在查询语句中查询count，然后通过变量控制，取到最后一行数据以后退出
# 常用的方式是mysql提供的一种句柄机制，使用变量与边界量来控制退出，一般推荐使用
create procedure pro_test11()
begin 
	declare e_id int(11); # 声明变量用于接收结果中的id
	declare e_name varchar(50); # 声明变量用于接收结果中的name
	declare e_age int(11); # 声明变量用于接收结果中的age
	declare e_salary int(11); # 声明变量用于接收结果中的salary
	declare has_data int default 1; # 声明变量，用于控制循环，初始为1，当值为0则表示光标到最后一行了，可以退出循环
	declare emp_result cursor for select * from emp; # 声明一个光标
	declare exit handler for not found set has_data=0; # mysql提供的句柄机制，表示没有数据时触发，此时修改变量的值为0，后面根据此变量的值退出循环且关闭光标
	open emp_result; # 打开光标
	repeat # 使用循环取值
		fetch emp_result into e_id,e_name,e_age,e_salary; # 移动光标到第一行，然后利用前面声明好的变量进行取值
		select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary); # 对取到的值进行显示
		until has_data = 0 # 循环的退出条件，就是变量为0
	end repeat;
	close emp_result; # 关闭光标
end$

注意

declare exit handler for not found set has_data=0;
此语句为声明退出的条件，为固定写法
此语句只能与声明光标的语句放在一起，否则会报错

触发器

概念

触发器是与表相关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句结合

好处

• 可以协助应用在数据库端确保数据的完整性
• 进行日志记录
• 进行数据的校检

其他

注意

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
Oracle数据库支持行级触发器或语句级触发器
OLD和NEW表示一行数据，可以通过点访问法访问到字段

分类

触发器类型	NEW和OLD的使用
INSERT型触发器	NEW表示将要或已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据

语法

创建触发器

• 语法

  create trigger trigger_name # 创建触发器，给定名字
  before/after insert/update/delete # 指定是操作前/后触发，属于什么类型的触发器
  on tab_name # 作用在哪张表上
  【for each row】 # 加上表示是行级触发器，mysql只支持行级触发器，所以要加上
  begin
  	trigger_stme; # 触发器的具体逻辑语句
  end;
  

• 示例

  # 1. 准备环境，给emp表创建一张日志表
  create table emp_logs(
  	id int(11) not null auto_increment,
  	operation varchar(20) not null comment '操作类型,insert/update/delete',
  	operate_time datetime not null comment '操作时间',
  	operate_id int(11) not null comment '操作表的ID',
  	operate_params varchar(500) comment '操作参数',
  	primary key(id)
  )engine=innodb default charset=utf8;
  # 2. 通过触发器记录emp表的数据变更日志emp_logs，包含增加、删除、修改
  # 2.1 创建对插入的触发器
  create trigger emp_insert_trigger # 创建触发器
   after insert # 设定在之后执行，类型为插入，也就是数据插入成功后触发
   on emp # 为表emp创建触发器
   for each row # 声明为行级触发器
   begin
   # 就是在对应的日志表中插入一条记录，日志中需要的插入后的数据均从new对象中获取
   insert into emp_logs values (
   null,'insert',now(),new.id,concat('插入后(id:',new.id,',name:',new.name,',age:',new.age,',salary:',new.salary,')'));
   end $
   # 2.2 创建对删除的触发器
   create trigger emp_delete_trigger
   after delete
   on emp
   for each row
   begin
   	insert into emp_logs values(
   	null,'delete',now(),old.id,concat(
   	'删除前(id:',old.id,',name:',old.name,',age:',old.age,',salary:',old.salary,')'
   	)
   	);
   end$
   # 2.3 创建对修改的触发器
   create trigger emp_update_trigger
   after update
   on emp
   for each row
   begin
   	insert into emp_logs values(
   	null,'update',now(),new.id,concat(
   	'修改前(id:',old.id,',name:',old.name,',age:',old.age,',salary:',old.salary,') 修改后(id:',new.id,',name:',new.name,',age:',new.age,',salary:',new.salary,')'
   	)
   	);
   end$
  

删除触发器

drop trigger 【schema_name.】trigger_name;
# 删除指定数据库的触发器
# schema_name是指数据库名
# trigger_name是指触发器名
# 如果没有指定schema_name则默认是当前数据库

查看触发器

# 查看所有触发器
show triggers;

表锁

该部分内容待完成！！！！！！

表分区

该部分内容待完成！！！！！！

mysql备份还原

该部分内容待完成！！！！！！

mysql结构体系

mysql体系结构概述

体系结构详解

• Connectors指的是不同语言中与SQL的交互
• Management Serveices & Utilities： 系统管理和控制工具
• Connection Pool: 连接池。管理缓冲用户连接，线程处理等需要缓存的需求
• SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
• Parser: 解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。主要功能：
  　　a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
  　　b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
• Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name from user where gender = 1;这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤将这两个查询条件联接起来生成最终查询结果
• Cache和Buffer： 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
• Engine ：存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎），现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB，默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。nnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

体系结构分层

1. 连接层：最上层是一些客户和链接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2. 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有垮存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提示系统的性能。
3. 引擎层：存储引擎层，存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信，不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
4. 存储层：数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

• 和其他数据库相比 ，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎

存储引擎概述

• 和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。
• 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可以被称为表类型。
• Oracle，SQLserver等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应的引擎，或者编写存储引擎。
• MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。
• 可以通过指令show engines，来查询当前数据库支持的存储引擎
• 创建表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB
• 查看MySQL数据库默认的存储引擎，指令为 show variables like’%storage_engine%';

各存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁（适合高并发）	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持（5.6版本之后）	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持
下面我们将重点介绍最常使用的两种引擎：InnoDB、MyISAM，另外两种MEMORY、MERGE，了解即可。

存储引擎详解

InnoDB

• InnoDB存储引擎是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留 数据和索引。
• 事务控制

  # 创建表
  create table goods_innodb(
  	id int not null auto_increment,
  	name varchar(20) no null,
  	primary key(id)
  ) engine=innodb default charset=utf8;
  

  # 开启事务后插入数据查看是否支持事务
  start transaction;
  insert into goods_innodb(id,name) values (null,'Meta20');
  commit;
  

• 在事务提交之前另外开启客户端查看数据是没有的，只有提交之后才可以查看，可以确定InnoDB中是存在事务的。
• 外键约束：MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动创建对应的索引。
• 下面两张表中，country_innodb是父表，country_id为主键索引，city_innodb为子表，country_id字段为外键，对应country_innodb表的主键country_id。

  create table country_innodb(
  	country_id int not null auto_increment,
  	country_name varchar(100) not null,
  	primary key(country_id)
  )engine=innodb default charset=utf8;
  create table city_innodb(
  	city_id int not null auto_increment,
  	city_name varchar(50) not null,
  	country_id int not null,
  	primary key(city_id),
  	key idx_fk_country_id(country_id),
  	constaint 'fk_city_country' foreign key(country_id) references country_innodb(country_id) on delete restrict on update cascade
  )engine=innodb default charset=utf8;
  insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
  insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
  

• 在创建索引时，可以指定删除、更新父表时，对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。
• RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新。
• CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；
• SET NULL则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。
• 针对上面创建的两个表，子表的外键指定是on delete RESTRICT on update CASCADE方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新
• 存储方式：InnoDB存储表和索引有以下两种方式

1. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_date_home_dir和innodb_date_file_path定义的表空间中，可以是多个文件
2. 使用多表空间存储，这种方式创建的表的表结构仍然存在.frm文件中，但每个表的数据和索引单独保存在.ibd中。

• 在Linux系统中，可以进入到var/lib/mysql/进行查看表结构和数据的存储

MyISAM

• MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者select、insert为主的应用基本上都可以使用这个引擎创建表。有以下两个比较重要的特点
• 不支持事务

  # 创建表
  create table goods_myisam(
  	id int not null auto_increment,
  	name varchar(20) not null,
  	primary key(id)
  )engine=myyisam default charset=utf8;
  # 开启事务
  start transaction;
  # 插入数据
  insert into goods values(null,'电脑3');
  # 事务回滚
  rollback;
  

• 文件存储方式：每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是

1. .frm 存储表定义
2. .MYD MYData，存储数据
3. .MYI MYIndex，存储索引

Memory

• Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY类型的表访问非常快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。

MERGE

• MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。
• 对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有三个不同的值，使用FIRST或LAST值使得插入操作被相应的作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作 。
• 可以对MERGE表 进行DROP操作，但是这个操作只是删除MERGE表定义，对内部的表是没有任何影响的。
• 下面示例演示创建merge引擎表

# 创建1990订单表，引擎是myisam
create table order_1990(
	order_id int,
	order_money double(10,2),
	order_address varchar(50),
	primary key(order_id)
)engine=myisam default charset=utf8;
# 创建1991订单表，引擎是myisam
create table order_1991(
	order_id int,
	order_money double(10,2),
	order_addrress varchar(50),
	primary key(order_id)
)engine=myisam default charset=utf8;
# 创建订单表，引擎是merge，联合了前面两个订单表，且定义插入的是1991订单表
create tanble order_all(
	order_id int,
	order_money double(10,2),
	order_adress varchar(50),
	primary key (order_id)
)engine=merge union = (order_1990,order_1991) insert_method=last default charset=utf8;
# 分别往1990和1991表中插入两条数据，查询all表得到4条数据

存储引擎的选择

• 在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常见的存储引擎的使用环境。
• InnoDB：是MySQL的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对于数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有的数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY的缺点就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以及快速得到访问结果。
• MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓库等VLDB(大规模数据库)环境十分合适。

优化SQL步骤

• 在应用的开发过程中，由于初期数据量小，开发人员写SQL语句时更注重功能上的实现，但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对他们进行优化，本章将详细介绍在MySQL中优化SQL语句的方法。
• 当面对一个有SQL性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题SQL并尽快解决问题。

查看SQL执行频率

• MySQL客户端连接成功后，通过show【session|global】status命令可以提供服务器状态信息。show【session|global】status可以根据需要加上参数session或者global来显示session级（当前连接）的结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是session。
• 下面的命令显示了当前session中所有的统计参数的值：

  # 大写的Com，后面是七个下划线
  show status like 'Com_______';
  

• 下面命令查看innoDB引擎下增删改查影响的行数

  show status like 'Innodb_rows_%';
  

• Com_xxx表示每个xxx语句执行的次数，我们通常比较关心的是以下几个统计参数。

  参数	含义
  Com_select	执行select操作的次数，一次查询只累加1
  Com_insert	执行insert操作的次数，对于批量插入的insert操作，一次只累加1
  Com_update	执行update操作的次数
  Com_delete	执行delete操作的次数
  Innodb_rows_read	select查询返回的行数
  Innodb_rows_inserted	执行insert操作插入的行数
  Innodb_rows_updated	执行update操作更新的行数
  Innodb_rows_deleted	执行delete操作删除的行数
  Connections	视图连接MySQL服务的次数
  Uptime	服务器工作时间
  Slow_queries	慢查询的次数

• Com_***：这些参数对所有引擎的表操作都会进行累计。
• Innodb_***：这几个参数只是针对InnoDB存储引擎的，累加的算法也略有不同。

定位低效率执行SQL

• 可以通过以下两种方式定位执行效率较低的SQL语句：

慢查询日志

通过慢查询日志定位那些执行效率较低的SQL语句，用–log-slow-queries【=file_name】选项启动时，mysql写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

show processlist

• 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程状态、是否锁表等，可以实时查看SQL的执行情况，同时对一些锁表操作进行优化。
• 查询结果列分析

  id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看。
  user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的SQL语句
  host列，显示这个语句是从哪个IP的哪个端口上发的，可以用来跟踪出现问题语句的用户
  db列，显示这个进程目前连接的是哪个数据库
  command列，显示当前连接的执行的命令，一般取值为休眠(sleep)，查询(query)，连接(connect)等
  time列，显示这个状态持续的时间，单位是秒
  state列，显示使用当前连接的SQL语句的状态，很重要的列。state描述的是语句执行中的某一个状态，一个SQL语句，以查询为例，可能需要经过copying to tmp table、sortine result、sending data等状态才可以完成
  info列，显示这个SQL语句，是判断问题语句的一个重要依据

explain分析执行计划

• 通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN或者DESC命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
• 查询SQL语句的执行计划

  explain select * from tb_item where id = 1;
  explain select * from tb_item where title = '阿尔卡特（OT-979）冰川白 联通3G手机3';
  # 查询结果显示都是一张固定列的表
  

• 查询结果列分析

  字段	含义
  id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
  select_type	表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等
  table	输出结果集的表
  type	表示的连接类型，性能由好到差的连接类型为system–>const–>eq_ref–>ref–>ref_or_null–>index_merge–>index_subquery–>range–>index–>all
  possible_keys	表示查询时，可能使用的索引
  key	表示查询时实际使用的索引
  key_len	索引字段的长度
  rows	扫描行的数量
  extra	执行情况的说明和描述

环境准备

# 角色表
create table t_role(
	id varchar(32) nor null,
	role_name varchar(255) default null,
	role_code varchar(255) default null,
	description varchar(255) default null,
	primary key(id),
	unique key unique_role_name (role_name)
)engine=InnoDB default charset=utf8;
# 用户表
create table t_user (
	id varchar(32) not null,
	username varchar(45) not null,
	password varchar(96) not null,
	name varchar(45) not null,
	primary key(id),
	unique key unique_user_username(username)
engine=InnoDB default charset=utf8;
# 用户角色关系表
create table user_role(
	id int(11) not null auto_increment,
	user_id varchar(32) default null,
	role_id varchar(32) default null,
	primary key(id),
	key fk_ur_user_id (user_id),
	key fk_ur_role_id (role_id),
	constraint fk_ur_role_id foreign key (role_id) references t_role (id) on delete no action on update no action,
	constraint fk_ur_user_id foreign key (user_id) references t_user (id) on delete no action on update no action
)engine=InnoDB default charset=utf8;
insert into t_user (id,username,password,name) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into t_user (id,username,password,name) values ('3','itcast','$2a$10$8qmaHgUFUAmPR5p0uWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into t_user (id,username,password,name) values ('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into t_user (id,username,password,name) values ('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into t_user (id,username,password,name) values ('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70u3CC33CeVncD3SLmyMXMknstqKRe','老师1');
insert into t_role(id,role_name,role_code,description) values ('5','学生','student','学生');
insert into t_role(id,role_name,role_code,description) values ('7','老师','teacher','老师');
insert into t_role(id,role_name,role_code,description) values ('8','教学管理员','teachermanger','教学管理员');
insert into t_role(id,role_name,role_code,description) values ('9','管理员','admin','管理员');
insert into t_role(id,role_name,role_code,description) values ('10','超级管理员','super','超级管理员');
insert into user_role(id,user_id,role_id) values (null,'1','5'),(null,'1','7'),(null,'2','8'),(null,'3','9'),(null,'4','8'),(null,'5','10');

explain之id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。也就是SQL语句中所涉及表的执行顺序，id情况有三种：

1. id相同表示加载顺序是从上到下

   explain select * from t_role r,t_user u,user_role ur where r.id = ur.role_id and u.id = ur.user_id;
   

2. id不同，id值越大，优先级越高，越先被执行

   explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'stu1'));
   

3. id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

   explain select * from t_role r,(select * from user_role ur where ur.user_id = '2' ) a where r.id = a.role_id;
   

explain之select_type

表示select的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不含子查询或者union
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在select或者where列表中包含的子查询
DERIVED	在from列表中包含的子查询，被标记为derived(衍生)MySQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个select出现在union之后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
UNION RESULT	表示union表获取结果的select,指union之后的联合结果集

# 演示simple
explain select * from t_user;
# 演示primary和subquery
explain select * from t_user where id = (select id from user_role where role_id = '9');
# 演示derived
explain select a.* from (select * from t_user where id in ('1','2')) a;
# 演示union和union result
explain selet * from t_user where id = '1' union select * from t_user where id = '2';

explain之table

展示这一行的数据是关于哪一张表的

• 展示的就是SQL中给定的表名
• 子查询展示的是衍生表，类似derived2，表示来自于id为2的查询的衍生表
• union中，union result类型的查询，table会显示类似于union1,2这种，表示union后结果集，来自于id为1和2两个查询的联合

explain之type

type显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。const将主键或唯一索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回行，使用一个索引来选择行。where之后出现between，<,>,in等操作。
index	index与all的区别为index类型只是遍历了索引树，通常比all快，all是遍历数据文件
all	将遍历全表以找到匹配的行

• 结果从上到下依次为从好到坏
• 一般来说，我们需要保证查询至少达到range级别，最好达到ref。

explain之key

• possible_keys：显示可能应用在这张表的索引，一个或多个
• key：实际使用的索引，如果为null，则没有使用索引
• key_len：表示索引中使用的字节数，改值为索引字段最大可能长度，并非实际长度，在不损失精确度的前提下，长度越短越好

explain之rows

表示扫描行的数量

explain之extra

其他的额外的执行计划信息，在该列展示。

extra	含义
using filesort	说明mysql会对数据库使用一个外部的索引排序，而不是按照表内的顺序进行读取，称为文件排序。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于order by和group by。
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据化，效率不错。

• 需要优化前面两个，保持第三个。

show profile分析SQL

• MySQL从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
• 通过过have_profiling参数，能够看到当前MySQL是否支持profile：

  select @@have_profiling;
  

• 默认profiling是关闭的

  select @@profiling;
  

• 可以通过set语句在session级别开启profiling

  set profiling = 1;
  

• 通过profiling，我们能够更清楚的了解SQL执行过程
• 首先，我们可以执行一系列的操作：

  show databases;
  use db01;
  show tables;
  select * from tb_item where id < 5;
  select count(*) from tb_item;
  

• 执行完上述指令之后，再执行show profiles指令，来查看SQL语句执行的耗时：

  show profiles;
  

• 通过show profile for query query_id语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

  show profile for query 6;
  

• 通过查询得知一条查询SQL的时间主要耗费在sending data阶段

  sending data状态表示MySQL线程开始访问数据并把结果返回给客户端，而不仅仅是返回客户端。
  由于在sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

• 在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型查看MySQL在使用什么资源上消耗了过高的时间。例如选择查看CPU的耗费时间：

  show profile cpu for query 6;
  

trace分析优化器执行计划

• MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是B计划。
• 打开trace，设置格式为json，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

  set oprimizer_trace = "enabled = on",end_markers_in_json = on;
  set optimizer_trace_max_mem_size = 1000000;
  

• 执行SQL语句：

  select * from tb_item where id < 4;
  

• 最后检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

  select * from information_schema.optimizer_trace\G;
  

SQL优化操作

• 准备环境，创建表结构

  # 创建用户表1
  create table `tb_user_1` (
  	`id` int(11) not null auto_increment,
  	`username` varchar(45) not null,
  	`password` varchar(96) not null,
  	`name` varchar(45) not null,
  	`birthday` datetime default null,
  	`sex` char(1) default null,
  	`email` varchar(45) default null,
  	`phone` varchar(45) default null,
  	`qq` varchar(32) default null,
  	`status` varchar(32) not null comment '用户状态',
  	`create_time` datetime not null,
  	`update_time` datetime default null,
  	primary key(`id`),
  	unique key `unique_user_username` (`username`)
  )engine=InnoDB default charset=utf8;
  # 创建用户表2
  create table `tb_user_2` (
  	`id` int(11) not null auto_increment,
  	`username` varchar(45) not null,
  	`password` varchar(96) not null,
  	`name` varchar(45) not null,
  	`birthday` datetime default null,
  	`sex` char(1) default null,
  	`email` varchar(45) default null,
  	`phone` varchar(45) default null,
  	`qq` varchar(32) default null,
  	`status` varchar(32) not null comment '用户状态',
  	`create_time` datetime not null,
  	`update_time` datetime default null,
  	primary key(`id`),
  	unique key `unique_user_username` (`username`)
  )engine=InnoDB default charset=utf8;
  

SQL优化-大批量插入数据

• 使用load指令导入数据的时候，适当的设置可以提高导入的效率。
• 对于InnoDB类型的表，有一下几种方式可以提高导入效率

1. 主键顺序插入

   因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率

   # 加载数据本地文件系统中的sql1.log中中的数据到表tb_user_1中，列分隔是逗号，行分隔是换行符
   load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
   # '/root/sql1.log' 表示文件地址
   # fields terminated by ','表示数据中每一列数据以逗号分隔
   # lines terminated by '\n' 表示数据中每一行数据以分隔符分隔
   

   sql1.log中的数据格式，可以看出字段之间用逗号隔开，行之间是换行符

   1,"username1","ZPAIFXVC","name1","1957-01-23 20:22:02","1","0sq1rgktly@0355.net","13301787682","SJVGNDTDWE","0","2019-04-20 22:37:15","2019-04-20 22:37:15"
   2,"username2","ZPAIFXVC","name1","1957-01-23 20:22:02","1","0sq1rgktly@0355.net","13301787682","SJVGNDTDWE","0","2019-04-20 22:37:15","2019-04-20 22:37:15"
   

   准备两个文件，一个文件中数据是按照id主键排序好的，另一个是无序的，分别倒入用户表1和用户表2中，因为两个表结构完全相同，可以对比出时间差距，插入一百万调数据时，相差好几倍的时间

2. 关闭唯一性校检

   # 对于设置了唯一约束的字段，在每次插入数据时，都会进行唯一性校检，降低了效率
   # 在导入数据前执行set unique_checks=0，关闭唯一性校检
   set unique_checks = 0;
   
   load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
   
   # 在导入数据后执行set unique_checks=1，恢复唯一性校检,可以提高效率
   set unique_checks = 1;
   

3. 手动提交事务
   如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

   set autocommit = 0;
   load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
   set autocommit = 1;
   

SQL优化-insert优化

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
  示例，原始方式为：

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  

  优化后的方案为：

  insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  

• 在事务中进行数据插入

  start trasaction;
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  commit;
  

• 数据有序插入

  insert to tb_test values(4,'Tim');
  insert to tb_test values(1,'Tom');
  insert to tb_test values(3,'Jerry');
  insert to tb_test values(5,'Rose');
  insert to tb_test values(2,'Cat');
  

  优化后

  insert to tb_test values(1,'Tom');
  insert to tb_test values(2,'Cat');
  insert to tb_test values(3,'Jerry');
  insert to tb_test values(4,'Tim');
  insert to tb_test values(5,'Rose');
  

SQL优化-order by优化

环境准备

create table `emp` (
	`id` int(11) not null auto_increment,
	`name` varchar(100) not null,
	`age` int(3) not null,
	`salary` int(11) default null,
	primary key(`id`)
)engine=InnoDB default charset=utf8mb4;
insert into `emp` (`id`,`name`,`age`,`salary`) values('1','Tom','25','2300');
insert into `emp` (`id`,`name`,`age`,`salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`,`name`,`age`,`salary`) values('3','Luci','25','2800');
insert into `emp` (`id`,`name`,`age`,`salary`) values('4','Jay','36','3500');
insert into `emp` (`id`,`name`,`age`,`salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`,`name`,`age`,`salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`,`name`,`age`,`salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`,`name`,`age`,`salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`,`name`,`age`,`salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`,`name`,`age`,`salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`,`name`,`age`,`salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`,`name`,`age`,`salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);

两种排序方式

1. 第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

   # 虽然排序的age字段有索引，但是查询的字段是所有字段，所以为filesort
   explain select * from emp order by age desc;
   # age字段顺序排序，结果一样，因为查询的字段有写是没有索引的
   explain select * from emp order by age asc;
   

2. 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

   # 结果显示为using index
   explain select id from emp order age asc;
   # 结果与上面相同
   explain select id,age from emp order by age asc;
   # 结果与上面相同
   explain select id,age,salary from emp order by age asc;
   

   多字段排序问题

   # 两个字段的前后顺序与索引顺序一致，都是倒叙，查询字段都有索引，所以是using index
   explain select id,age,salary from emp order by age desc,salary desc;
   # 结果为using index，using filesort，因为排序的两个字段的顺序与索引的顺序不符合
   explain select id,age,salary from emp order by salary desc,age desc;
   # 结果为using index，using filesort，因为一个是顺序，一个是倒叙
   explain select id,age,salary from emp order by age desc,salary asc;
   

   了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。否则肯定需要额外操作，这样就会出现filesort

Filesort的优化

通过创建合适的索引，能够减少filesort的出现，但是在某些情况下，条件限制不能让filesort消失，那就需要加快filesort的排序操作。对于filesort，MySQL有两种排序算法：

1. 两次扫描算法：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作
2. 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法高。
   MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小，来判定使用哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
   可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率。

   show variables like 'max_length_for_sort_data';
   show variables like 'sort_buffer_size';
   

SQL优化-group by 优化

由于group by实际上也同样会进行排序操作（8.0之前是自动升序，可以在group by直接跟上desc为降序，8.0之后没有默认排序，后面写desc也会报错），而且与order by相比，group by主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by的实现过程中，与order by一样也可以利用到索引。
如果查询包含group by但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序。如下：

# 删除索引
drop index idx_emp_age_salary on emp;
# 进行分组查询，8.0之前的版本
explain select age,count(*) from emp group by age;
# 可以看到extra信息显示using temporary；using filesort

优化后

# 可以看到extra部分信息显示为using temporary
explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个语句需要filesort，第二个语句不需要filesort，而上面提到过，filesort往往非常耗费时间，可以为分组字段创建索引

SQL优化-子查询优化

MySQL4.1版本之后，开始支持SQL的子查询。这个技术可以使用select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时可以避免事务或者表锁死，并且写起来很容易。但是，有些情况下，子查询是可以被更高效的连接（join）替代。
示例，查找有角色的所有用户信息：

explain select * from t_user where id in(select user_id from user_role);

优化后

explain select * from t_user u,user_role ur where u.id = ur.user_id;

连接从查询之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询操作。

SQL优化-or优化

对于包含or的查询子句，如果要利用索引，则or之间的每个条件都必须用到索引，而且不能使用复合索引；如果没有索引，或者其中有至少一个条件没有索引，则整个语句不会使用到索引，就应该考虑增加索引。
获取emp表中的所有索引：

# 查询得出有主键索引以及age和salary的复合索引
show index from emp;

示例：

# 会发现没有用到任何索引，因为or条件中name是没有索引的
explain select * from emp where id = 1 or name = 'Tom'\G;
# 会发现也是完全没有用到索引的，虽然有复合索引，但是不会用到
explain select * from emp where age = 20 or salary = 3500;
# 如果是复合索引覆盖条件。则会用到索引
explain select * from emp where id = 1 or age = 30\G;

一般是推荐使用union替换or，替换之后主要差别在于type和ref两项，type显示的访问类型，是一个比较重要的指标，结果值从好到坏依次是：

const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all

union语句的type值为ref，or语句的type值为range，可以看到这是一个很明显的差距，union语句的ref值为const，or语句的ref值为null，const表示常量值引用，非常快，这两项的差距足以说明union要优于or

explain select * from emp where id = 1 union select * from emp where age = 25;
explain select * from emp where id = 1 union select * from emp where id = 10;

SQL优化-limit优化

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头疼的问题就是limit 200000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

explain select * from tb_item limit 2000000,10;

优化思路一：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

explain select * from tb_item t,(select id from tb_item order by id limit 2000000,10) a where t.id = a.id;

优化思路二：

该方案适用于主键自增的表，且自增主键列不能出现断层，可以把Limit转换为某个位置的查询

explain select * from tb_item where id > 1000000 limit 10;

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句中表名的后面，添加use index来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

# 给表tb_seller中的name字段创建索引
create index idx_seller_name on tb_seller(name);
# 根据name查询，查看执行计划
explain select * from tb_sweller where name = '小米科技';
# 同样的SQL语句，添加了use index之后再查看执行计划
explain select * from tb_seller use index(idx_seller_name)  where = '小米科技';

IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或多个索引，则可以使用ignore index作为hint

# 执行时就会忽略idx_seller_name索引
explain select * frm tb_seller ignore index(idx_seller_name) where name = '小米科技';

FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用force index作为hint

# 此时会发现，虽然address字段有索引，但是因为数据占比的原因，mysql认为全表扫描效率高于索引，导致所有失效，不会走索引
explain select * from tb_seller where address='北京市';
# 此时如果使用use index指定索引，会发现没有用，依然不会走索引
explain select * from tb_seller use index(idx_seller_address) where address='北京市';
# 针对上面这种情况，就可以使用force index强制使用索引
explain select * from tb_seller force index(idx_seller_address) where address='北京市';

JDBC

概念：

• Java Database Connectivity：Java数据库连接
• sun公司推出的一套java程序访问数据库的规范
• 规范：抽象类、接口

简单实现

步骤：

1. 导入驱动jar包
2. 注册驱动
3. 获取数据库连接对象Connection
4. 定义SQL语句
5. 获取执行SQL语句的对象 statement
6. 执行SQL，接收返回结果
7. 处理结果
8. 释放资源

对象详解

• DriverManager：驱动管理器，驱动管理对象

  • 注册驱动：告诉程序该使用哪一个数据库驱动jar

    mysql5之后的驱动jar包可以省略注册驱动的步骤

  • 获取数据库连接

    • getConnecton(String url，String user，String passWord)

    • 参数：

      1. url：指定连接的路径

         jdbc:mysql://localhost:3306/test

         jdbc:mysql:///test:连接本地3306mysql服务器

      2. user：用户名

      3. password：密码

• Connection：

  • 获取执行SQL的对象
    • Statement：createStatement()
    • PreparedStatement：preparedStatement(String sql)
  • 管理事物
    • 开启事物：setAutoCommet(boolean autocommit),设置false
    • 提交事物：commit()
    • 回滚事物：rollback()

• Statement

  • 执行静态SQL
  • execute(String sql)可以执行任意的sql
  • int executeUpdate()，执行DML语句，DDL语句，返回受影响的行
  • ResultSet executeQuery(String sql)执行DQL语句

• ResultSet

• PreparedStatement：

/**
 * @Author: 邪灵
 * @Date: 2020/7/24 13:43
 * @Description: jdbc工具类
 * @version: 1.0
 */
public class DBUtils {
    private static String url;
    private static String user;
    private static String pass;
    private static Connection connection;
    private static PreparedStatement ps;
    private static ResultSet rs;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Properties prop = new Properties();
            prop.load(DBUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            pass = prop.getProperty("pass");
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,pass);
    }

    public static boolean update(String sql,Object...objects) {
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            if (objects!=null||objects.length!=0) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject(i+1,objects[i]);
                }
            }
            int i = ps.executeUpdate();
            return i!=0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResouce(connection,ps);
        }
        return false;
    }

    public static void closeResouce(Connection connection,PreparedStatement ps) {
        if (ps!=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeResouce(Connection connection,PreparedStatement ps,ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        closeResouce(connection,ps);
    }
}

测试数据库

drop table employees,admin,beauty,boys,job_grades,jobs,locations,departments;
CREATE TABLE `job_grades`  (
                               `grade_level` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                               `lowest_sal` int(11) NULL DEFAULT NULL,
                               `highest_sal` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8;
INSERT INTO `job_grades` VALUES ('A', 1000, 2999);
INSERT INTO `job_grades` VALUES ('B', 3000, 5999);
INSERT INTO `job_grades` VALUES ('C', 6000, 9999);
INSERT INTO `job_grades` VALUES ('D', 10000, 14999);
INSERT INTO `job_grades` VALUES ('E', 15000, 24999);
INSERT INTO `job_grades` VALUES ('F', 25000, 40000);
CREATE TABLE `beauty`  (
                           `id` int(11) NOT NULL AUTO_INCREMENT,
                           `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                           `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '?',
                           `borndate` datetime NULL DEFAULT '1987-01-01 00:00:00',
                           `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                           `photo` blob NULL,
                           `boyfriend_id` int(11) NULL DEFAULT NULL,
                           PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8;
INSERT INTO `beauty` VALUES (1, '柳岩', '女', '1988-02-03 00:00:00', '18209876577', NULL, 8);
INSERT INTO `beauty` VALUES (2, '苍老师', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `beauty` VALUES (3, 'Angelababy', '女', '1989-02-03 00:00:00', '18209876567', NULL, 3);
INSERT INTO `beauty` VALUES (4, '热巴', '女', '1993-02-03 00:00:00', '18209876579', NULL, 2);
INSERT INTO `beauty` VALUES (5, '周冬雨', '女', '1992-02-03 00:00:00', '18209179577', NULL, 9);
INSERT INTO `beauty` VALUES (6, '周芷若', '女', '1988-02-03 00:00:00', '18209876577', NULL, 1);
INSERT INTO `beauty` VALUES (7, '岳灵珊', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `beauty` VALUES (8, '小昭', '女', '1989-02-03 00:00:00', '18209876567', NULL, 1);
INSERT INTO `beauty` VALUES (9, '双儿', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `beauty` VALUES (10, '王语嫣', '女', '1992-02-03 00:00:00', '18209179577', NULL, 4);
INSERT INTO `beauty` VALUES (11, '夏雪', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `beauty` VALUES (12, '赵敏', '女', '1992-02-03 00:00:00', '18209179577', NULL, 1);
CREATE TABLE `boys`  (
                         `id` int(11) NOT NULL AUTO_INCREMENT,
                         `boyName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                         `userCP` int(11) NULL DEFAULT NULL,
                         PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;
INSERT INTO `boys` VALUES (1, '张无忌', 100);
INSERT INTO `boys` VALUES (2, '鹿晗', 800);
INSERT INTO `boys` VALUES (3, '黄晓', 50);
INSERT INTO `boys` VALUES (4, '段誉', 300);
CREATE TABLE `admin`  (
                          `id` int(11) NOT NULL AUTO_INCREMENT,
                          `username` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                          `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                          PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;
INSERT INTO `admin` VALUES (1, 'john', '8888');
INSERT INTO `admin` VALUES (2, 'lyt', '6666');
CREATE TABLE `locations`  (
                              `location_id` int(11) NOT NULL AUTO_INCREMENT,
                              `street_address` varchar(40) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `postal_code` varchar(12) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `city` varchar(30) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `state_province` varchar(25) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `country_id` varchar(2) CHARACTER SET utf8  NULL DEFAULT NULL,
                              PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB charset utf8;
INSERT INTO `locations` VALUES (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT');
INSERT INTO `locations` VALUES (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT');
INSERT INTO `locations` VALUES (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO `locations` VALUES (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP');
INSERT INTO `locations` VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO `locations` VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO `locations` VALUES (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO `locations` VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO `locations` VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO `locations` VALUES (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO `locations` VALUES (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN');
INSERT INTO `locations` VALUES (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO `locations` VALUES (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO `locations` VALUES (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG');
INSERT INTO `locations` VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO `locations` VALUES (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO `locations` VALUES (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO `locations` VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO `locations` VALUES (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO `locations` VALUES (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO `locations` VALUES (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO `locations` VALUES (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO `locations` VALUES (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
CREATE TABLE `jobs`  (
                         `job_id` varchar(10) CHARACTER SET utf8  NOT NULL,
                         `job_title` varchar(35) CHARACTER SET utf8  NULL DEFAULT NULL,
                         `min_salary` int(6) NULL DEFAULT NULL,
                         `max_salary` int(6) NULL DEFAULT NULL,
                         PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;
INSERT INTO `jobs` VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO `jobs` VALUES ('AD_PRES', 'President', 20000, 40000);
INSERT INTO `jobs` VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO `jobs` VALUES ('FI_ACCOUNT', 'Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('FI_MGR', 'Finance Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('HR_REP', 'Human Resources Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO `jobs` VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO `jobs` VALUES ('MK_REP', 'Marketing Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('PR_REP', 'Public Relations Representative', 4500, 10500);
INSERT INTO `jobs` VALUES ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('PU_MAN', 'Purchasing Manager', 8000, 15000);
INSERT INTO `jobs` VALUES ('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO `jobs` VALUES ('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO `jobs` VALUES ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO `jobs` VALUES ('ST_MAN', 'Stock Manager', 5500, 8500);
CREATE TABLE `departments`  (
                                `department_id` int(4) NOT NULL AUTO_INCREMENT,
                                `department_name` varchar(3) CHARACTER SET utf8  NULL DEFAULT NULL,
                                `manager_id` int(6) NULL DEFAULT NULL,
                                `location_id` int(4) NULL DEFAULT NULL,
                                PRIMARY KEY (`department_id`) USING BTREE,
                                INDEX `loc_id_fk`(`location_id`) USING BTREE,
                                CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE innoDB charset utf8;
INSERT INTO `departments` VALUES (10, 'Adm', 200, 1700);
INSERT INTO `departments` VALUES (20, 'Mar', 201, 1800);
INSERT INTO `departments` VALUES (30, 'Pur', 114, 1700);
INSERT INTO `departments` VALUES (40, 'Hum', 203, 2400);
INSERT INTO `departments` VALUES (50, 'Shi', 121, 1500);
INSERT INTO `departments` VALUES (60, 'IT', 103, 1400);
INSERT INTO `departments` VALUES (70, 'Pub', 204, 2700);
INSERT INTO `departments` VALUES (80, 'Sal', 145, 2500);
INSERT INTO `departments` VALUES (90, 'Exe', 100, 1700);
INSERT INTO `departments` VALUES (100, 'Fin', 108, 1700);
INSERT INTO `departments` VALUES (110, 'Acc', 205, 1700);
INSERT INTO `departments` VALUES (120, 'Tre', NULL, 1700);
INSERT INTO `departments` VALUES (130, 'Cor', NULL, 1700);
INSERT INTO `departments` VALUES (140, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (150, 'Sha', NULL, 1700);
INSERT INTO `departments` VALUES (160, 'Ben', NULL, 1700);
INSERT INTO `departments` VALUES (170, 'Man', NULL, 1700);
INSERT INTO `departments` VALUES (180, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (190, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (200, 'Ope', NULL, 1700);
INSERT INTO `departments` VALUES (210, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (220, 'NOC', NULL, 1700);
INSERT INTO `departments` VALUES (230, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (240, 'Gov', NULL, 1700);
INSERT INTO `departments` VALUES (250, 'Ret', NULL, 1700);
INSERT INTO `departments` VALUES (260, 'Rec', NULL, 1700);
INSERT INTO `departments` VALUES (270, 'Pay', NULL, 1700);
CREATE TABLE `employees`  (
                              `employee_id` int(6) NOT NULL AUTO_INCREMENT,
                              `first_name` varchar(20) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `last_name` varchar(25) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `email` varchar(25) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `phone_number` varchar(20) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `job_id` varchar(10) CHARACTER SET utf8  NULL DEFAULT NULL,
                              `salary` double(10, 2) NULL DEFAULT NULL,
                              `commission_pct` double(4, 2) NULL DEFAULT NULL,
                              `manager_id` int(6) NULL DEFAULT NULL,
                              `department_id` int(4) NULL DEFAULT NULL,
                              `hiredate` datetime NULL DEFAULT NULL,
                              PRIMARY KEY (`employee_id`) USING BTREE,
                              FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ,
                              FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE = InnoDB charset utf8;
INSERT INTO `employees` VALUES (100, 'Steven', 'K_ing', 'SKING', '515.123.4567', 'AD_PRES', 24000.00, NULL, NULL, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', 'IT_PROG', 9000.00, NULL, 102, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', 'IT_PROG', 6000.00, NULL, 103, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', 'IT_PROG', 4200.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', 'FI_MGR', 12000.00, NULL, 101, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', 'FI_ACCOUNT', 9000.00, NULL, 108, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (110, 'John', 'Chen', 'JCHEN', '515.124.4269', 'FI_ACCOUNT', 8200.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', 'FI_ACCOUNT', 7700.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', 'FI_ACCOUNT', 7800.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', 'FI_ACCOUNT', 6900.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', 'PU_MAN', 11000.00, NULL, 100, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', 'PU_CLERK', 3100.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', 'PU_CLERK', 2900.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', 'PU_CLERK', 2800.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', 'PU_CLERK', 2600.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', 'PU_CLERK', 2500.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', 'ST_MAN', 8000.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', 'ST_MAN', 8200.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', 'ST_MAN', 7900.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', 'ST_MAN', 6500.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', 'ST_MAN', 5800.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', 'ST_CLERK', 3200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', 'ST_CLERK', 2700.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', 'ST_CLERK', 2400.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', 'ST_CLERK', 2200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', 'ST_CLERK', 3300.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', 'ST_CLERK', 2800.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', 'ST_CLERK', 2500.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', 'ST_CLERK', 2100.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', 'ST_CLERK', 3300.00, NULL, 122, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', 'ST_CLERK', 2900.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', 'ST_CLERK', 2400.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', 'ST_CLERK', 2200.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', 'ST_CLERK', 3600.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', 'ST_CLERK', 3200.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (139, 'John', 'Seo', 'JSEO', '650.121.2019', 'ST_CLERK', 2700.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', 'ST_CLERK', 2500.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', 'ST_CLERK', 3500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', 'ST_CLERK', 3100.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', 'ST_CLERK', 2600.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', 'ST_CLERK', 2500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', 'SA_MAN', 14000.00, 0.40, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', 'SA_MAN', 13500.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', 'SA_MAN', 12000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', 'SA_MAN', 11000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', 'SA_MAN', 10500.00, 0.20, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', 'SA_REP', 10000.00, 0.30, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', 'SA_REP', 9500.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', 'SA_REP', 9000.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', 'SA_REP', 8000.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', 'SA_REP', 7500.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', 'SA_REP', 7000.00, 0.15, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (156, 'Janette', 'K_ing', 'JKING', '011.44.1345.429268', 'SA_REP', 10000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', 'SA_REP', 9500.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', 'SA_REP', 9000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', 'SA_REP', 8000.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', 'SA_REP', 7500.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', 'SA_REP', 7000.00, 0.25, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', 'SA_REP', 10500.00, 0.25, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', 'SA_REP', 9500.00, 0.15, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', 'SA_REP', 7200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', 'SA_REP', 6800.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', 'SA_REP', 6400.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', 'SA_REP', 6200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', 'SA_REP', 11500.00, 0.25, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', 'SA_REP', 10000.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', 'SA_REP', 9600.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', 'SA_REP', 7400.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', 'SA_REP', 7300.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', 'SA_REP', 6100.00, 0.10, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', 'SA_REP', 11000.00, 0.30, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', 'SA_REP', 8800.00, 0.25, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', 'SA_REP', 8600.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', 'SA_REP', 8400.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', 'SA_REP', 7000.00, 0.15, 149, NULL, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', 'SA_REP', 6200.00, 0.10, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', 'SH_CLERK', 3200.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', 'SH_CLERK', 3100.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', 'SH_CLERK', 2500.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', 'SH_CLERK', 2800.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', 'SH_CLERK', 4200.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', 'SH_CLERK', 4100.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', 'SH_CLERK', 3400.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', 'SH_CLERK', 3000.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', 'SH_CLERK', 3800.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', 'SH_CLERK', 3600.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', 'SH_CLERK', 2900.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', 'SH_CLERK', 2500.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', 'SH_CLERK', 4000.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', 'SH_CLERK', 3900.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', 'SH_CLERK', 3200.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', 'SH_CLERK', 2800.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', 'SH_CLERK', 3100.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', 'SH_CLERK', 3000.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', 'AD_ASST', 4400.00, NULL, 101, 10, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 'MK_MAN', 13000.00, NULL, 100, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', 'MK_REP', 6000.00, NULL, 201, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 'HR_REP', 6500.00, NULL, 101, 40, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', 'PR_REP', 10000.00, NULL, 101, 70, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 'AC_MGR', 12000.00, NULL, 101, 110, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', 'AC_ACCOUNT', 8300.00, NULL, 205, 110, '2016-03-03 00:00:00');

练习题目

基础SQL语句

1. 下面的语句是否可以执行成功：select last_name,job_id,salary as sal from employees;
2. 下面的语句是否可以执行成功：select * from employees;
3. 找出下面语句中的错误：select employee_id,last_name,salary*12 “ANNUAL SALARY” from employees;
4. 显示表departments的结构，并查询其中的全部数据
5. 显示出表employees中的全部job_id(不能重复)
6. 显示employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

条件查询

1. 查询没有奖金，且工资小于18000的salary，last_name
2. 查询employees表中，job_id不为‘IT’或者工资为12000的员工信息
3. 查看部门department表的结构
4. 查询部门department表中涉及到了哪些位置编号
5. 试问select * from employees和select * from employees where commission_pct like ‘%%’ and last_name like '%%'结果是否一样，并说明原因

排序查询

1. 查询员工的姓名和部门号和年薪，按照年薪降序，按照姓名升序
2. 选择工资不在8000到17000的员工的姓名和工资，按工资降序
3. 查询邮箱中包含e的员工信息，并按照邮箱的字节数降序，再按部门号升序

常用函数

1. 显示系统时间（注：日期+时间）
2. 查询员工号，姓名，工资以及工资提高百分之20后的结果（new salary）
3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
4. 做一个查询，产生下面的结果<last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns 24000 monthly but wants 72000
5. 使用CASE-WHEN，按照下面的条件：job grade,AD_PRES A,ST_MAN B,IT_PROG C;

分组查询

1. 查询公司员工工资的最大值，最小值，平均值，总和
2. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）
3. 查询部门编号为90的员工个数
4. 查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
5. 查询员工最高工资和最低工资的差距（DIFFERENCE）
6. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
7. 查询所有部门编号，员工数量和工资平均值，并按平均工资降序
8. 选择具有各个job_id的员工人数

综合练习

1. 显示员工表的最大工资，工资平均值
2. 查询员工表的employee_id,job_id,last_name，按department_id降序，salary升序
3. 查询员工表的job_id中包含a和e的，并且a在e的前面
4. 显示当前日期，以及去前后空格，截取子字符串的函数
5. 显示所有员工的姓名，部门号和部门名称
6. 查询90号部门员工的job_id和90好部门的location_id
7. 选择所有有奖金的员工的last_name,job_id,department_id,department_name
8. 查询每个工种，每个部门的部门名，工种名和最低工资
9. 查询每个国家下的部门个数大于2的国家编号
10. 查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
11. 查询哪个城市没有部门
12. 查询部门名为SAL或者IT的员工信息

子查询练习

1. 查询工资最低的员工信息last_name，salary
2. 查询平均工资最低的部门信息
3. 查询平均工资最低的部门信息和该部门的平均工资
4. 查询平均工资最高的job信息
5. 查询平均工资高于公司平均工资的部门有哪些
6. 查询出公司中所有manager 的详细信息
7. 各个部门中，最低工资中最低的那个部门的最低工资是多少
8. 查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salasy
9. 查询和zlotkey相同部门的员工姓名和工资
10. 查询工资比公司平均工资高的员工的员工号，姓名和工资
11. 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
12. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
13. 查询在部门的location_id为1700的部门工作的员工的员工号
14. 查询管理者是King的员工姓名和工资
15. 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓，名

事务、视图练习

1. 创建表Book表，字段如下：bid 整形，要求主键；bname 字符型，要求设置唯一键，并非空；price 浮点型，要求默认值10；btypeId 类型编号，要求引用bookType表的id字段
2. 已知bookType表（不用创建），字段如下：id，name
3. 开启事物，向表中插入1行数据，并结束
4. 创建视图，实现查询价格大于100的书名和类型名
5. 修改视图，实现查询价格在90-120之间的书名和价格
6. 删除刚才创建的视图
7. 创建视图emp_v1，要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
8. 创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

存储过程练习

1. 创建存储过程实现传入用户名和密码，插入到admin表中
2. 创建存储过程实现传入女神编号，返回女神名称和女神电话
3. 创建存储过程实现传入两个女神生日，返回大小
4. 创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回
5. 创建存储过程实现传入女神名称，返回女神 and 男神格式的字符串
6. 创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录

其他练习

• 已知表stuinfo：id 学号，email 邮箱 john@126.com，gradeID 年级编号，sex 性别 男 女，age 年龄
• 已知表grade：id 年级编号，gradeName 年级名称

1. 查询所有学员的邮箱的用户名（注：邮箱中@前面的字节）
2. 查询男生和女生的个数
3. 查询年龄>18岁的所有学生和姓名和年级名称
4. 查询哪个年级的学生最小年龄>20岁
5. 试说出查询语句中涉及到的所有的关键字，以及执行先后顺序
6. 运行以下脚本创建my_employees和users表

create table my_empployees(
id int(10),
first_name varchar(10),
last_name varchar(10),
userid varchar(10),
salary double(10,2)
);
create table users(
id int,
userid varchar(10),
department_id int
);

7. 显示表my_employees的结构
8. 向表my_employees表中插入下列数据

id	first_name	last_name	userid	salary
1	patel	ralph	rpatel	895
2	dance	betty	bdancs	860
3	biri	ben	bbiri	1100
4	newman	chad	cnewman	750
5	ropeburn	audrey	aropebur	1550

9. 向表users表中插入数据

1	rpatel	10
2	bdancs	10
3	bbiri	20
4	cnewman	30
5	aropebur	40

10. 将3号员工的last_name修改为drelxer
11. 将所有工资少于900的员工工资修改为1000
12. 将userid为bbiri的user表和my_employees表的记录全部删除
13. 删除所有数据
14. 检查所有的修正
15. 清空表my_employees