栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL数据库

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL数据库

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档


提示:以下是本篇文章正文内容,下面案例可供参考

目录

一、安装MySQL

一、环境变量配置

二、启动MySQL

三、登录MySQL

四、MySQL常用命令

注意:不见;不执行,其表示结束

五、SQL语句分类

六、导入数据

七、查看表结构

八、查询语句

1.简单查询(1)查询一个字段

2.条件查询

九、数据处理函数(单行处理函数)

1.lower(转换小写)

2.upper(转换大写)

3.substr(取子串)

4.length(取长度)

5.trim(去空格)

6.string_to_date(将字符串转换成日期)

7..date_format(格式化千分位)

8.format(设置千分位)

9.concat(字符串拼接)

十、分组函数(多行处理函数)

1.max()

2.min()

3.avg()

4.count()

5.sum()

十一、分组查询(*****)

1.group by

2.distinct(去重)

十二、连接查询

案例(SQL92语法)

笛卡尔积现象

1.内连接

2.外连接

两种可以进行相互转换

案例

案例

十三、子查询

1.子查询可以出现在哪里?

2.where字句中的子查询

案例

3.from子句中的子查询

案例

4.select后面出现的子查询(了解)

案例

十四、union合并查询结果集

案例

十五、limit

案例

分页

十六、表的操作

1.创建表

2.删除表及删改数据

 3.insert插入

4.string_to_date

5.date_format

       格式化千分位,date_format(日期类型数据,'日期格式') 

6.date和datetime

7.快速创建表

8.快速删除表中的数据

9.约束(constraint)

1)not null

2)unique

3)primary key(主键约束)一般为数字,定长

4)foreign

9.脚本文件

十七、存储引擎

1.查看MySQL支持哪些引擎:

2.常用存储引擎

(1) MyISAM

(2) InnoDB

(3)MEMORY

十八、事务

1.什么是一个完整的业务逻辑

2.只有DML语句才有事务

3.事务如何实现

(1)提交事务?会自动提交

(2)回滚事务

(3)怎么将MySQL的自动提交机制关闭呢?

4.事务的特性

(1)A:原子性

(2)C:一致性

(3)I:隔离性

(4)D:持久性

十九、索引(index)

1.添加索引的条件

2.创建和删除

3.查看是否使用索引进行检索

4.索引失效

5.索引分类

 二十、视图

1.创建及删除视图对象

2.视图做什么


一、安装MySQL

二、启动MySQL

三、登录MySQL

四、MySQL常用命令

注意:不见;不执行,其表示结束

五、SQL语句分类

六、导入数据

七、查看表结构

八、查询语句

1.简单查询(1)查询一个字段

2.条件查询

九、数据处理函数(单行处理函数)

1.lower(转换小写)

2.upper(转换大写)

3.substr(取子串)

4.length(取长度)

5.trim(去空格)

6.string_to_date(将字符串转换成日期)

7..date_format(格式化千分位)

8.format(设置千分位)

9.concat(字符串拼接)

十、分组函数(多行处理函数)

1.max()

2.min()

3.avg()

4.count()

5.sum()

十一、分组查询(*****)

1.group by

2.distinct(去重)

十二、连接查询

案例(SQL92语法)

笛卡尔积现象

1.内连接

2.外连接

两种可以进行相互转换

案例

案例

十三、子查询

1.子查询可以出现在哪里?

2.where字句中的子查询

案例

3.from子句中的子查询

案例

4.select后面出现的子查询(了解)

案例

十四、union合并查询结果集

案例

十五、limit

案例

分页

十六、表的操作

1.创建表

2.删除表及删改数据

 3.insert插入

4.string_to_date

5.date_format

       格式化千分位,date_format(日期类型数据,'日期格式') 

6.date和datetime

7.快速创建表

8.快速删除表中的数据

9.约束(constraint)

1)not null

2)unique

3)primary key(主键约束)一般为数字,定长

4)foreign

9.脚本文件

十七、存储引擎

1.查看MySQL支持哪些引擎:

2.常用存储引擎

(1) MyISAM

(2) InnoDB

(3)MEMORY

十八、事务

1.什么是一个完整的业务逻辑

2.只有DML语句才有事务

3.事务如何实现

(1)提交事务?会自动提交

(2)回滚事务

(3)怎么将MySQL的自动提交机制关闭呢?

4.事务的特性

(1)A:原子性

(2)C:一致性

(3)I:隔离性

(4)D:持久性

十九、索引(index)

1.添加索引的条件

2.创建和删除

3.查看是否使用索引进行检索

4.索引失效

5.索引分类

 二十、视图

1.创建及删除视图对象

2.视图做什么


一、环境变量配置

path

二、启动MySQL
net start mysql
net stop mysql

三、登录MySQL

四、MySQL常用命令
查看数据库
show database;
创建数据库
create database student;
选择使用哪个数据库
use mysql;
查看mysql数据库版本号
select version();
查看当前使用的数据库
select database();

注意:不见;不执行,其表示结束

五、SQL语句分类
  • DQL:数据库查询语言 select关键字
  • DML:数据库操作语言 (对表中的数据进行修改)
  1. 增:insert
  2. 删:delete
  3. 改:update
  • DDL:数据库定义语言(主要是对表的结构进行操作)
  1. 新建:等同于增
  2. 删除:drop
  3. 修改:alter
  • TCL:事务控制语言
  1. 事务提交:commit
  2. 事务回滚:rollback
  • DCL:数据控制语言
  1. 授权:grant
    desc emp;
    

  2. 撤销权限:revoke

六、导入数据
1.创建数据库
    create database 数据库名;
2.导入
    source  数据库保存的地址(不能有中文);
3.查看数据表
    show tables;

七、查看表结构
desc 表名;

八、查询语句

1.简单查询
(1)查询一个字段
select 字段名 from 表名;

(2)查询两个/多个字段,用逗号隔开

 select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

(3)查询所有字段

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
这种方式的缺点:
1.可读性差
2.效率低
在实际开发中不建议使用

(4)给查询列起别名(别名是中文,用单引号括起来)

mysql> select deptno,dname as deptname from dept;
+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
使用as关键字起别名,只改变显示,不改变原表

(5)列参与数学运算

mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
+--------+---------+
14 rows in set (0.00 sec)

mysql> select ename,sal*12 from emp; //字段可以使用数学表达式!
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
+--------+----------+

2.条件查询
select字段.... from 表名 where 条件;

(1)=

mysql> select empno,ename from emp where sal=800;
+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
+-------+-------+

(2)不等于

select empno,ename from emp where sal !=800;
select empno,ename from emp where sal <>800;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
+-------+--------+

(3)<

(4)<=

(5)>

(6)>=

(7)between...and

mysql> select empno,ename,sal from emp where sal >=2450 and sal <= 3000;
       select empno,ename,sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+

(8)查询是否为空

mysql> select empno,ename,sal,comm from emp where comm is not null;
+-------+--------+---------+---------+
| empno | ename  | sal     | comm    |
+-------+--------+---------+---------+
|  7499 | ALLEN  | 1600.00 |  300.00 |
|  7521 | WARD   | 1250.00 |  500.00 |
|  7654 | MARTIN | 1250.00 | 1400.00 |
|  7844 | TURNER | 1500.00 |    0.00 |
+-------+--------+---------+---------+
select empno,ename,sal,comm from emp where comm is null;

(9)and并且

        查询工作岗位时manager并且工资大于2500的员工信息

mysql> select empno,ename,job,sal from emp where job='manager' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+

(10)or或者

        查询工作岗位时manager和salesman的员工

mysql> select empno,ename,job from emp where job='manager' or job='salesman';
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

(11)and和or同时出现时,有优先级问题吗?

and 优先级比or高,()优先级最高

(12)in包含,相当于多个or(not in不在这个范围中)

mysql> select ename,sal from emp where sal in (800,5000);
+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| KING  | 5000.00 |
+-------+---------+
注:in后面的括号相当于集合
    

(13)not表示不在这几个值当中

(14)like

        称为模糊查询,支持%或下划线匹配,%:任意多个字符,下划线:任意一个字符

找出名字含有o的
mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+

(15)排序(order by)

      ·  默认升序,指定降序desc,指定升序asc

mysql> select ename,sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
+--------+---------+

        ·多个字段排序

mysql> select ename,sal from emp order by sal desc,ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 ||
+--------+---------+

        ·根据字段的位置排序(了解)

按照查询结果第二列进行排序
mysql> select ename,sal from emp order by 2;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 ||
+--------+---------+

(16)综合一点的案例

注:关键字顺序不能变
mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+

九、数据处理函数(单行处理函数)

单行处理函数特点:一个输入对应一个输出

1.lower(转换小写)
mysql> select lower(ename) as ename from emp;
+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
+--------+

2.upper(转换大写)

3.substr(取子串)
第一种方式:substr函数 emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
第二种方式:模糊查询
mysql> select ename from emp where ename like 'A%';

4.length(取长度)
mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
+-------------+

5.trim(去空格)
mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim('   KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

6.string_to_date(将字符串转换成日期)

7..date_format(格式化千分位)

8.format(设置千分位)

9.concat(字符串拼接)
mysql>  select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
+---------------------+

十、分组函数(多行处理函数)

        多行处理函数特点:输入多行,最终输出一行

注意:分组函数在使用时必须先分组,后使用

1.max()
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

2.min()
mysql>  select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

3.avg()
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

4.count()
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+

5.sum()
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

6.分组函数的注意点

(1)分组函数在使用的时候需要注意哪些?

        分组函数会自动忽略null,不需要提前对null进行处理

(2)分组函数中count(*)和count(具体字段)有什么区别

count(具体字段):表示统计该字段下所有不为NULL的元素的总数

count(*):统计表当中的总行数

(3)分组函数不能够直接使用where子句中

(4)所有分组函数可以组合起来用

mysql> select sum(sal),min(sal),max(sal),count(*),avg(sal) from emp;
+----------+----------+----------+----------+-------------+
| sum(sal) | min(sal) | max(sal) | count(*) | avg(sal)    |
+----------+----------+----------+----------+-------------+
| 29025.00 |   800.00 |  5000.00 |       14 | 2073.214286 |
+----------+----------+----------+----------+-------------+

十一、分组查询(*****)

先进行分组,然后进行操作

以下关键字的顺序不能颠倒,需要记忆

from、where、group by、select、order by

select
    ...
from 
    ...
group by
    ...
order by
    ...

1.group by

eg:

(1)按照工作岗位对工资求和

mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

(2)找出每个部门的做高薪资

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+

(3)找出每个部门,不同工作岗位的最高薪资

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     20 | CLERK     |  1100.00 |
|     30 | SALESMAN  |  1600.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | MANAGER   |  2850.00 |
|     10 | MANAGER   |  2450.00 |
|     20 | ANALYST   |  3000.00 |
|     10 | PRESIDENT |  5000.00 |
|     30 | CLERK     |   950.00 |
|     10 | CLERK     |  1300.00 |
+--------+-----------+----------+

(4)找出每个部门的最高薪资,要求娴熟最高薪资大于3000

where与having优先选择where

     1)having字句可以对分组之后 的数据进行过滤

mysql> select 
            deptno,max(sal) 
       from 
            emp 
       group by 
            deptno 
       having 
            max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

     2)可以先找出薪资大于3000的,然后进行分组

mysql> select 
            deptno,max(sal) 
       from 
            emp 
       where
            sal > 3000
       group by 
            deptno ;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

2.distinct(去重)

(1)岗位去重

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

(2)job和deptno两个字段同时去重

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

十二、连接查询

        内连接:等值连接、非等值连接、自连接

        外连接:左外连接、右外连接

        全连接(不讲)

案例(SQL92语法)

查询每个员工所在的部门名称

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

两张表没有条件限制:

mysql> select ename,dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
| ALLEN  | SALES      |
| ALLEN  | RESEARCH   |
| ALLEN  | ACCOUNTING |
|.....................|
56 rows in set (0.37 sec)

笛卡尔积现象

       当两张表连接查询,两张表没有条件限制时,最终查询结果条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象。

连接时加条件,满足这个条件的记录被筛选出来!

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

以下为SQL99语法

1.内连接

        特点:完全能够匹配上这个条件的数据查询出来,没有主次关系

(1)等值连接

        案例:查询每个员工所在部门名称,显示员工名和部门名

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

(2)非等值连接(条件不是一个等值关系)

        案例:找出每个员工的薪资等级,要求显示员工名、薪资等级?

mysql> select 
            e.ename,s.grade 
       from 
            emp e 
       inner join
            salgrade s 
       on 
            e.sal 
       between s.losal and s.hisal;
+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| ALLEN  |     3 |
| WARD   |     2 |
| JONES  |     4 |
| MARTIN |     2 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| KING   |     5 |
| TURNER |     3 |
| ADAMS  |     1 |
| JAMES  |     1 |
| FORD   |     4 |
| MILLER |     2 |
+--------+-------+
14 rows in set (0.36 sec)

(3)自连接

        案例:查询员工的上级领导,要求显示员工名和对应的领导名?

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec)

技巧:一张表看成两张表

emp a:员工表             emp b:领导表

mysql> select a.ename as 'employee',b.ename as 'leader'
    -> from
    ->     emp a
    -> join
    ->     emp b
    -> on
    ->     a.mgr=b.empno;
+----------+--------+
| employee | leader |
+----------+--------+
| SMITH    | FORD   |
| ALLEN    | BLAKE  |
| WARD     | BLAKE  |
| JONES    | KING   |
| MARTIN   | BLAKE  |
| BLAKE    | KING   |
| CLARK    | KING   |
| SCOTT    | JONES  |
| TURNER   | BLAKE  |
| ADAMS    | SCOTT  |
| JAMES    | BLAKE  |
| FORD     | JONES  |
| MILLER   | CLARK  |
+----------+--------+
13 rows in set (0.00 sec)

2.外连接

两种可以进行相互转换

(1)右外连接

        right表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部显示出来,捎带关联查询right左边这张表。

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.41 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

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

内连接只显示了有员工的部门10、20、30,没有员工的部门40没有显示出来,若想要显示,就需要使用外连接。

mysql>  select
    ->         e.ename,d.dname
    ->  from
    ->         emp e
    ->  right  join
    ->         dept d
    ->  on
    ->         e.deptno=d.deptno;
mysql>  select
    ->         e.ename,d.dname
    ->  from
    ->         dept d
    ->  left   join
    ->         emp e
    ->  on
    ->         e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

(2)左外连接

案例

查询每个员工的上级领导,要求显示所有的员工名和领导名

mysql> select 
            a.ename,b.ename 
       from 
            emp a 
       left join 
            emp b    
       on 
            a.mgr=b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

(3)三张表连接

mysql> select
        ...    
       from  
            a
       join 
            b
       on 
            a和b连接的条件
       join 
            c
       on 
            a和c连接的条件
       right join 
            d
       on
            a和d连接的条件
案例

找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?

mysql> select
    ->       e.ename,e.sal,d.dname,s.grade
    -> from
    ->       emp e
    -> join
    ->       dept d
    -> on
    ->       e.deptno=d.deptno
    -> join
    ->       salgrade s
    -> on
    ->      e.sal between s.losal and s.hisal;
+--------+---------+------------+-------+
| ename  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+------

十三、子查询

        select语句中嵌套select语句,被嵌套的select语句称为子查询

1.子查询可以出现在哪里?
select 
    ...[select]...
from 
    ...[select]...
where
    ...[select]...

2.where字句中的子查询

where子句中不能使用分组函数

案例

找出比最低工资高的员工姓名、薪资

mysql> select  
            ename,sal 
       from 
            emp 
       where 
            sal> (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

3.from子句中的子查询

from后面的子查询,可以将子查询的查询结果当作一张临时表

案例

找出每个岗位的平均工资的薪资等级

第一步:找出每个岗位的平均工资(按照岗位分组求平均值)

mysql> select 
            job,avg(sal) 
       from 
            emp 
       group by 
            job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

第二步:克服心理障碍,把以上的查询结果就当作一张真是存在的表t

mysql> select 
                job,avg(sal)
       as 
                avgsal 
       from 
                emp 
       group by 
                job;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

mysql> select
    ->          t.*,s.grade
    -> from
    ->          (select job,avg(sal)as avgsal from emp group by job) t
    -> join
    ->          salgrade s
    -> on
    ->          t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+

4.select后面出现的子查询(了解) 案例

找出每个员工的部门名称,要求显示员工名、部门名?

mysql> select 
            e.ename,(select d.dname from dept d where e.deptno=d.deptno)
       as     
            dname 
       from 
            emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

十四、union合并查询结果集 案例

查询工作岗位是manager和salesman的员工

mysql> select ename,job from emp where job='manager' or job='salesman';
       select ename,job from emp where job in('manager'.'salesman');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
mysql>  select ename,job from emp where job='manager'
    ->  union
    ->  select ename,job from emp where job='salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

union效率更高一些,匹配次数会减少

十五、limit

        limit是将查询结果集的一部分取出来,通常使用在分页查询中

案例

按照薪资降序,取出排名在前5名的员工

mysql> select ename,sal from emp order by sal desc limit 5;
       select ename,sal from emp order by sal desc limit (0,5);
       //    5代表长度,下标从0开始
       //取出3-5名
       select ename,sal from emp order by sal desc limit (2,3);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

分页

每页显示三条记录

第一页:limit 0,3        第二页:limit 3,3        第三页:limit 6,3

每页显示pagesize条记录

第pageno页:limit (pageno-1)*pagesize,pagesize

十六、表的操作

1.创建表
create table 表名(
    
        id int unsigned primary key auto_increment,

        name varchar(5),

        age int unsigned
);

2.删除表及删改数据

表存在删除后创建:
               drop table  if exists students
查看数据表结构:
               desc students
删除数据表:
            drop table students       //只删除所有数据,不重置主键字段的计数
            truncate table students   //删除所有数据,并重置主键字段的计数
            delete from students      //删掉整张表

修改数据:
            update students set age 40 where id=2;
删除数据:
            delete from students where id=2;

 3.insert插入

        数字格式化:format(数字,'格式')

mysql> select ename,format(sal,'$999,999') as sal from emp;
+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
| BLAKE  | 2,850 |
| CLARK  | 2,450 |
| SCOTT  | 3,000 |
| KING   | 5,000 |
| TURNER | 1,500 |
| ADAMS  | 1,100 |
| JAMES  | 950   |
| FORD   | 3,000 |
| MILLER | 1,300 |
+--------+-------+

插入多条记录语法:

添加一行数据:
            insert into students (字段1,字段2) value()   //数据要与表的字段一一对应
添加多行数据:
            insert into students value(...),(...),(...)

4.string_to_date
mysql>  create table t_user(
            id int,
            name varchar(32),
             birth date  //生日也可以使用date日期类型
        );

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |

        将字符串转换成日期

mysql>  insert into t_user(id,name,birth) 
         values
                (1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
mysql>  insert into t_user(id,name,birth)
         values
                (2,'lisi','1990-10-01'); //如果你提供的字符串是日期格式,会自动转换
Query OK, 1 row affected (0.41 sec)

5.date_format

       格式化千分位,date_format(日期类型数据,'日期格式') 

        这个函数通常使用在查询日期方面,设置展示的日期格式

mysql> select 
                id,name,date_format(birth,'%m/%d/%Y') 
       as birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
|    2 | lisi     | 10/01/1990 |
+------+----------+------------+

6.date和datetime

date是短日期,只包含年月日信息,默认格式:%Y-%m-%d

datetime是长日期,包含年月日时分秒,默认格式:%Y-%m-%d %h:%l:%s

mysql> create table t_user(
           id int,
           name varchar(32),
           birth date,
           create_time datetime
       );
mysql>  insert into t_user(id,name,birth,create_time) 
        values 
                (1,'zhangsan','1990-10-01','2022-6-5 10:09:40');
mysql> insert into t_user(id,name,birth,create_time) 
        values  (2,'lisi','1991-10-01',now());  //now()获取当前时间
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2022-06-05 10:09:40 |
|    2 | lisi     | 1991-10-01 | 2022-06-05 10:14:10 |
+------+----------+------------+---------------------+

7.快速创建表

原理:将一个查询结果当作一张表新建!!!可以完成表的复制

mysql> create table emp2 as select * from emp;
Query OK, 14 rows affected, 2 warnings (1.25 sec)
Records: 14  Duplicates: 0  Warnings: 0

 可以将查询结果插入到一张表中

mysql> create table dept_bak select * from dept;
Query OK, 4 rows affected (1.23 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into dept_bak select * from dept;
Query OK, 4 rows affected (0.43 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

8.快速删除表中的数据

delete语句(DML语句)删除数据的原理:  只删除数据,但数据在硬盘上的真实空间不会被释放

        优点:支持回滚,后悔了可以恢数据复

        缺点:删除次效率较低

truncate语句(DDL操作)删除的原理:效率比较高,表被一次截断,物理删除

        优点:快速

        缺点:不支持回滚        

mysql> delete from dept_bak;
Query OK, 4 rows affected (0.42 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

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

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> truncate table dept_bak;
Query OK, 0 rows affected (1.25 sec)

mysql>  select * from dept_bak;
Empty set (0.00 sec)

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

9.约束(constraint)

        在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性

1)not null

        只有列级约束,没有表级约束

mysql> insert into t_vip values(1,'zhangsan');
       insert into t_vip values(2,'lisi');
mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
mysql> insert into t_vip values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

2)unique

        约束的字段不能重复,但是可以为null

mysql> create table t_vip(
    ->          id int,
    ->          name varchar(255) unique,
    ->          email varchar(255)
    -> );
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
mysql> insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
mysql> insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');

mysql> insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 't_vip.name'
mysql> select * from  t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
+------+----------+------------------+
mysql>  insert into t_vip(id) values(4);
mysql>  insert into t_vip(id) values(5);
mysql>  select * from  t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+

两个字段联合起来唯一

mysql> create table t_vip(
    ->          id int,
    ->          name varchar(255) ,
    ->          email varchar(255),
                unique(name,email)
    -> );

not null和unique联合

mysql> create table t_vip(
    ->          id int,
    ->          name varchar(255) not null unique
    -> );

在MySQL中,如果一个字段同时被notbull和unique约束的画,该字段自动变成主键字段

3)primary key(主键约束)一般为数字,定长

在MySQL中,有一种机制可以帮我们自动维护一个主键值

mysql> create table t_vip(
    ->          id int primary key auto_increment,  // auto_increment表示自增,从1开始
    ->          name varchar(255) 
    -> );

任何一张表必须有主键,若没有,表无效

作用:每一行记录的唯一标识

特征:not null+unique(主键值不能是null,同时也不能重复)

相关术语:

        主键约束:就是一种约束

        主键字段:该字段上添加了主键约束

        主键值:主键上的每一个值

mysql> create table t_vip(
    ->          id int,
    ->          name varchar(255) ,
    ->          email varchar(255),
                primary key(id,name)  //复合主键
mysql> create table t_vip(
    ->          id int primary key,   //列级主键
    ->          name varchar(255) ,
    ->          email varchar(255)    
    -> ); 

4)foreign
mysql>  create table class(
    ->          classno int primary key,
    ->          classname varchar(255)
    -> );

mysql>  insert into class(classno,classname) values(101,'北京市大兴区高三一班');

mysql>  insert into class(classno,classname) values(102,'北京市大兴区高三二班');

mysql> create table student(
    ->          no int primary key auto_increment, 
    ->          name varchar(255),
    ->          cno int,
    ->          foreign key(cno) references class(classno)
    -> );

mysql>  insert into student (name,cno) values('lala',101);
mysql>  insert into student (name,cno) values('baby',101);
mysql>  insert into student (name,cno) values('alice',102);

mysql> select * from class;
+---------+--------------------------------+
| classno | classname                      |
+---------+--------------------------------+
|     101 | 北京市大兴区高三一班           |
|     102 | 北京市大兴区高三二班           |
+---------+--------------------------------+

mysql> select * from student;
+----+-------+------+
| no | name  | cno  |
+----+-------+------+
|  1 | lala  |  101 |
|  2 | baby  |  101 |
|  4 | alice |  102 |
+----+-------+------+

9.脚本文件

xxxx.sql这种文件被sql脚本文件,批量执行sql脚本文件的时候,该文件中所有的sql语句会全部执行!

执行sql脚本文件的语句:source    sql脚本的绝对路径

十七、存储引擎

结论:

        MySQL默认的存储引擎:InnoDB

        MySQL默认的字符编码:utf-8

mysql> create table t_vip(
    ->          id int primary key,
    ->          name varchar(255) 
    -> )ENGINE=InnoDB  DEFAULT CHARSET=GBK;

1.查看MySQL支持哪些引擎:

支持九大引擎

mysql> show engines G

2.常用存储引擎

(1) MyISAM

对于一张表来说,只要是主键,或者加有unique字段上会自动创建索引

优势:可被转换为压缩、只读表来节省空间

它管理的表具有以下特征:使用三个文件表示每个表

       格式文件:存储表结构的定义(mytable.frm)

       数据文件:存储表行的内容(mytable.MYD)

       索引文件:存储表的索引(mytable.MYI),索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制

(2) InnoDB

提供一组用来记录事务活动的文件

MySQL数据库默认的存储引擎,同时是一个重量级的存储引擎

特点:非常安全

最大特点:支持事务,以保证数据的安全,并且也不能压缩,不能转换为只读,不能很好的节省存储空间

 InnoDB支持事务,支持数据库崩溃后自动恢复机制

(3)MEMORY

优点:查询效率最高

缺点:不安全,关机之后数据消失,因为数据和索引都是在内存当中

十八、事务

        一个事务是一个完整的业务逻辑

1.什么是一个完整的业务逻辑

假设转账,从A账户向B账户中转账1000

将A账户的钱减去1000(update语句)

将B账户的钱加上1000(update语句)

这就是一个完整的逻辑

以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分,这两个update语句要求必须同时成功或者失败,这样才能保证钱是正确的

2.只有DML语句才有事务

增删改三个语句与事务有关(数据安全第一位)

3.事务如何实现

 InnoDB存储引擎:提供一组用来记录事务活动的文件

事务开启了:

insert,delete,update

事务结束了!

在事务执行的过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中

在事务执行过程中,我们可以提交事务,也可以回滚事务

(1)提交事务?会自动提交

        commit提交事务

        清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中

        提交事务标志着事务结束

(2)回滚事务

        rollback 回滚到上一次提交的地方

        将之前所有的DML操作全部撤销, 并清空事   务性活动的日志文件

mysql> use bjpowernode;
Database changed
mysql> delete from dept_bak;
Query OK, 1 row affected (0.44 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec
mysql> insert into dept_bak values (20,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values (20,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     20 | abc   | tj   |
|     20 | abc   | tj   |
+--------+-------+------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.36 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)

(3)怎么将MySQL的自动提交机制关闭呢?

        先执行start transaction,所有数据插入完成后执行commit

4.事务的特性

(1)A:原子性

       最小单元,不可再分

(2)C:一致性

        在同一事务中,左右操作必须同时成功或同时失败

(3)I:隔离性

        事务之间有一定的隔离,不能相互影响

隔离级别:

        读未提交:read uncommitted(最低级别)

        读已提交:read committed

        可重复读:repeatable read

        序列化/串行化:serializable(最高级别)

(4)D:持久性

        事务最终结束的一个屏障,事务提交,就相当于将没有保存到硬盘的数据保存到硬盘中

十九、索引(index)

        主键,unique自动添加索引对象

        索引是在数据库表的字段上添加的,是为了提交查询效率存在的一种机制

        索引相当于一本书的目录,是为了缩小扫描范围而存在的机制                                                  

1.添加索引的条件

        1.数据量大

        2.该字段经常出现在where自居后,以条件形式存在

        3.该字段很少用DML操作

2.创建和删除
mysql> create index empname_index on emp(ename);

mysql> drop index  empname_index on emp;

3.查看是否使用索引进行检索
mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.38 sec)

mysql>  create index emp_empname_index on emp(ename);
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_empname_index | emp_empname_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4.索引失效

1)使用模糊查询

mysql> select * from emp where ename like '%T';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
1 row in set (0.36 sec)

mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2)使用or

mysql> select * from emp where ename='KING' or job='manager';

mysql> explain select * from emp where ename='KING' or job='manager';
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | emp_empname_index | NULL | NULL    | NULL |   14 |    16.43 | Using where |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3)使用复合索引,没有使用左侧的列查找

mysql> create index emp_job_sal_index on emp(job,sal);
mysql> explain select * from emp where job='manager';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

mysql>  explain select * from emp where sal=800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

5.索引分类

(1)单一索引:一个字段添加索引

(2)复合索引:两个字段或更多的字段上添加索引

(3)主键索引:具有unique约束的字段上添加索引

注意:唯一性比较弱的字段上添加索引用处不大

 二十、视图

        站在不同的角度取看待同一份数据

1.创建及删除视图对象
//表复制
mysql> create table dept2 select * from dept;  

mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
//创建视图对象
mysql> create view dept2_view as select * from dept2;
//删除视图对象
mysql> drop view dept2_view;

2.视图做什么

        我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作

mysql>  create view dept2_view as select * from dept2;
//面向视图查询
mysql>  select * from dept2_view;
//面向视图插入
mysql> insert into dept2_view(deptno,dname,loc) values (60,'sales','beijing');
//查询原表数据
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | sales      | beijing  |
+--------+------------+----------+
//面向视图删除
mysql> delete from dept2_view;
//查询原表数据
mysql> select * from dept2;

增删改查用叫做:CRUD

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/963031.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号