Mysql

day01

1.什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？

数据库：
英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。
顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

数据库管理系统：
DataBaseManagement，简称DBMS。
数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：
MySQL、Oracle、MS SqlServer、DB2、sybase等….

SQL：结构化查询语言
程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用。

三者之间的关系？
DBMS—执行—> SQL —操作—> DB

先安装数据库管理系统MySQL，然后学习SQL语句怎么写，编写SQL语句之后，DBMS对SQL语句进行执行，最终来完成数据库的数据管理。

2.SQL语句分类

DQL：
数据查询语言（凡是带有select关键字的都是查询语句）
select…

DML：
数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert delete update
insert 增
delete 删
update 改
这个主要是操作表中的数据data。

DDL：
数据定义语言
凡是带有create、drop、alter的都是DDL。
DDL主要操作的是表的结构。不是表中的数据。
create：新建，等同于增
drop：删除
alter：修改
这个增删改和DML不同，这个主要是对表结构进行操作。

TCL：
不是王牌电视。
是事务控制语言
包括：
事务提交：commit;
事务回滚：rollback;

DCL：
是数据控制语言。
例如：授权grant、撤销权限revoke….

3.基本构成单元

数据库当中最基本的单元是表：table

任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。

每一个字段都有：字段名、数据类型、约束等属性。

  1. 约束：约束也有很多，其中一个叫做唯一性约束，这种约束添加之后，该字段中的数据不能重复。

4.常用命令

登陆：

  1. mysql -uroot -p

退出mysql ：

  1. exit

查看mysql中有哪些数据库？

  1. show databases;

选择使用某个数据库：

  1. use ***;
  1. create database ***;

查看某个数据库下有哪些表？

  1. show tables;

查看mysql数据库的版本号：

  1. select version();

查看当前使用的是哪个数据库？

  1. select database();

怎么将sql文件中的数据导入呢？

  1. mysql> source D:\course\03-MySQL\document\bjpowernode.sql (不要有中文)

查看表中的数据：

  1. select * from 表名;

不看表中的数据，只看表的结构，有一个命令：

  1. desc 表名;

查询一个字段：

  1. select 字段名1,字段名2 from 表名;（不区分大小写，字段可以使用数学表达式）（select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据））

给查询的列起别名？

  1. mysql> select deptno,dname as deptname from dept;（使用as关键字起别名。注意：只是将显示的查询结果列名显示为deptname，原表列名还是叫：dname记住：select语句是永远都不会进行修改操作的，因为只负责查询。如果别名有空格或者是中文的话要加单引号）

条件查询语法格式：

  1.         select
  2.             字段1,字段2,字段3....
  3.         from 
  4.             表名
  5.         where
  6.             条件;
  7. 条件包括：=, <>, !=, <, <=, >, >=, between ⋯ and ⋯, is null, is not null, and, or, in, not in, like
  8. (注意：1.<>, !=等价 2.in不是一个区间。in后面跟的是具体的值。
  9. 查询薪资是800和5000的员工信息？
  10.             select ename,sal from emp where sal = 800 or sal = 5000;
  11.             select ename,sal from emp where sal in(800, 5000); //这个不是表示800到5000都找出来。3.like称为模糊查询，支持%或下划线匹配,%匹配任意多个字符,下划线：任意一个字符。)

查询所有员工薪资，排序:

  1. select 
  2.         ename,sal
  3.     from
  4.         emp
  5.     order by
  6.         sal; // 默认是升序！！！
  7. 指定降序：
  8.     select 
  9.         ename,sal
  10.     from
  11.         emp
  12.     order by
  13.         sal desc;

数据处理函数：
数据处理函数又被称为单行处理函数
单行处理函数的特点：一个输入对应一个输出。
和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应1个输出！）
常见的单行处理函数：

  1. lower 转换小写
  2.         mysql> select lower(ename) as ename from emp;
  3. upper 转换大写
  4.         mysql> select upper(name) as name from t_student;
  5. substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
  6.         select substr(ename, 1, 1) as ename from emp;
  7. concat函数进行字符串的拼接
  8.         select concat(empno,ename) from emp;
  9. length 取长度
  10.         select length(ename) enamelength from emp;
  11. trim 去空格
  12.         mysql> select * from emp where ename = trim('   KING');
  13. round 四舍五入
  14.         mysql> select round(1236.567, 0) as result from emp; //保留整数位。
  15.         select round(1236.567, 1) as result from emp; //保留1个小数
  16.         select round(1236.567, 2) as result from emp; //保留2个小数
  17.         select round(1236.567, -1) as result from emp; // 保留到十位。
  18. rand() 生成随机数
  19.         mysql> select round(rand()*100,0) from emp; // 100以内的随机数
  20. ifnull 可以将 null 转换成一个具体值,ifnull函数用法：ifnull(数据, 被当做哪个值)
  21. 如果“数据”为NULL的时候，把这个数据结构当做哪个值。补助为NULL的时候，将补助当做0
  22.         select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

分组函数（多行处理函数）：

  1. 5个：
  2.         count    计数
  3.         sum    求和
  4.         avg    平均值
  5.         max    最大值
  6.         min    最小值
  7. 注意：
  8. 1.分组函数在使用的时候必须先进行分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。2.分组函数自动忽略NULL，你不需要提前对NULL进行处理。3.分组函数不能够直接使用在where子句中。
  9. 找出比最低工资高的员工信息。
  10.     select ename,sal from emp where sal > min(sal);//会报错,解释见分组查询

分组查询（非常重要）：

  1. 关键字顺序：
  2. select
  3.         ...
  4. from
  5.         ...
  6. where
  7.         ...
  8. group by
  9.         ...
  10. order by
  11.         ...
  12. 执行顺序：
  13. 1. from
  14. 2. where
  15. 3. group by
  16. 4. select
  17. 5. order by
  18.     为什么分组函数不能直接使用在where后面？
  19.             select ename,sal from emp where sal > min(sal);//报错。
  20.             因为分组函数在使用的时候必须先分组之后才能使用。
  21.             where执行的时候，还没有分组。所以where后面不能出现分组函数。
  22.             select sum(sal) from emp; 
  23.             这个没有分组，为啥sum()函数可以用呢？
  24.                 因为select在group by之后执行。
  26. 在一条select语句当中，如果有group by语句的话，
  27.             select后面只能跟：参加分组的字段，以及分组函数。
  28.             其它的一律不能跟。
  29. 使用having可以对分完组之后的数据进一步过滤。having不能单独使用，having不能代替where，having必须和group by联合使用。
  30. select 
  31.         deptno,max(sal) 
  32. from 
  33.         emp 
  34. group by 
  35.         deptno
  36. having
  37.         max(sal) > 3000;

注意：mysql是不见“;”不执行，“;”表示结束！

5.大总结

  1.     select 
  2.         ...
  3.     from
  4.         ...
  5.     where
  6.         ...
  7.     group by
  8.         ...
  9.     having
  10.         ...
  11.     order by
  12.         ...
  14.     以上关键字只能按照这个顺序来，不能颠倒。
  16.     执行顺序？
  17.         1. from
  18.         2. where
  19.         3. group by
  20.         4. having
  21.         5. select
  22.         6. order by
  24.     从某张表中查询数据，
  25.     先经过where条件筛选出有价值的数据。
  26.     对这些有价值的数据进行分组。
  27.     分组之后可以使用having继续筛选。
  28.     select查询出来。
  29.     最后排序输出！
  31.     找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，
  32.     要求按照平均薪资降序排。
  33.         select 
  34.             job, avg(sal) as avgsal
  35.         from
  36.             emp
  37.         where
  38.             job <> 'MANAGER'
  39.         group by
  40.             job
  41.         having
  42.             avg(sal) > 1500
  43.         order by
  44.             avgsal desc;
  46.         +-----------+-------------+
  47.         | job       | avgsal      |
  48.         +-----------+-------------+
  49.         | PRESIDENT | 5000.000000 |
  50.         | ANALYST   | 3000.000000 |
  51.         +-----------+-------------+

day02

常用命令（续）

1、把查询结果去除重复记录【distinct】关键字

  1.     mysql> select distinct job from emp;// distinct只能出现在所有字段的最前方
  2. 例:统计一下工作岗位的数量？
  3.         select count(distinct job) from emp;

2、连接查询
2.1、什么是连接查询？
从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2.2、连接查询的分类？
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（不讲）

2.3、当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

  1. select ename,dname from emp, dept;

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。
如何避免？
连接时加条件，满足这个条件的记录被筛选出来！

  1. select 
  2.         e.ename,d.dname 
  3.     from 
  4.         emp e, dept d  //表起别名
  5.     where
  6.         e.deptno = d.deptno; //SQL92语法。

2.4、内连接——等值连接
例：查询每个员工所在部门名称，显示员工名和部门名？
SQL92语法：

  1.     select 
  2.         e.ename,d.dname
  3.     from
  4.         emp e, dept d
  5.     where
  6.         e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：

  1.     select 
  2.         e.ename,d.dname
  3.     from
  4.         emp e
  5.     join
  6.         dept d
  7.     on
  8.         e.deptno = d.deptno;

SQL99语法模板：

  1.         select 
  2.             ...
  3.         from
  4.             a
  5.         join
  6.             b
  7.         on
  8.             a和b的连接条件
  9.         where
  10.             筛选条件

2.5、内连接——非等值连接
例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

  1. mysql> select * from emp; e
  2. +-------+--------+-----------+------+------------+---------+---------+--------+
  3. | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
  4. +-------+--------+-----------+------+------------+---------+---------+--------+
  5. |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
  6. |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
  7. |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
  8. |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
  9. ....
  11. mysql> select * from salgrade; s
  12. +-------+-------+-------+
  13. | GRADE | LOSAL | HISAL |
  14. +-------+-------+-------+
  15. |     1 |   700 |  1200 |
  16. |     2 |  1201 |  1400 |
  17. |     3 |  1401 |  2000 |
  18. |     4 |  2001 |  3000 |
  19. |     5 |  3001 |  9999 |
  20. +-------+-------+-------+
  22. select 
  23.     e.ename, e.sal, s.grade
  24. from
  25.     emp e
  26. join
  27.     salgrade s
  28. on
  29.     e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
  30. +--------+---------+-------+
  31. | ename  | sal     | grade |
  32. +--------+---------+-------+
  33. | SMITH  |  800.00 |     1 |
  34. | ALLEN  | 1600.00 |     3 |
  35. | WARD   | 1250.00 |     2 |
  36. | JONES  | 2975.00 |     4 |
  37. | MARTIN | 1250.00 |     2 |
  38. | BLAKE  | 2850.00 |     4 |
  39. | CLARK  | 2450.00 |     4 |
  40. | SCOTT  | 3000.00 |     4 |
  41. | KING   | 5000.00 |     5 |
  42. | TURNER | 1500.00 |     3 |
  43. | ADAMS  | 1100.00 |     1 |
  44. | JAMES  |  950.00 |     1 |
  45. | FORD   | 3000.00 |     4 |
  46. | MILLER | 1300.00 |     2 |
  47. +--------+---------+-------+

2.6、内连接——自连接
例：查询员工的上级领导，要求显示员工名和对应的领导名？
技巧：一张表看做两张表。

  1. select 
  2.     a.ename as '员工名', b.ename as '领导名'
  3. from
  4.     emp a
  5. join
  6.     emp b
  7. on
  8.     a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

2.7、外连接
内连接：A和B连接，AB两张表没有主次关系。平等关系。
内连接的特点：完成能够匹配上这个条件的数据查询出来。
外连接（右外连接）：

  1. select 
  2.     e.ename,d.dname
  3. from
  4.     emp e 
  5. right join 
  6.     dept d
  7. on
  8.     e.deptno = d.deptno;

right代表将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。
带有right的是右外连接，又叫做右连接。带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。任何一个左连接都有右连接的写法。

2.9、三张表，四张表怎么连接？
语法：

  1.         select 
  2.             ...
  3.         from
  4.             a
  5.         join
  6.             b
  7.         on
  8.             a和b的连接条件
  9.         join
  10.             c
  11.         on
  12.             a和c的连接条件
  13.         right join
  14.             d
  15.         on
  16.             a和d的连接条件

一条SQL中内连接和外连接可以混合。都可以出现！

3、子查询？
3.1、什么是子查询？
select语句中嵌套select语句，被嵌套的select语句称为子查询。

3.2、子查询都可以出现在哪里呢？

  1.     select
  2.         ..(select).
  3.     from
  4.         ..(select).
  5.     where
  6.         ..(select).

4、union合并查询结果集

  1.     select ename,job from emp where job = 'MANAGER'
  2.     union
  3.     select ename,job from emp where job = 'SALESMAN';

注：union在进行结果集合并的时候，要求两个结果集的列数相同

5、limit（非常重要）
5.1、limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
百度默认：一页显示10条记录。

5.2、limit怎么用呢？

完整用法：limit startIndex, length
startIndex是起始下标，length是长度。
起始下标从0开始。
缺省用法：limit 5; 这是取前5.

5.3、注意：mysql当中limit在order by之后执行！！

5.4、分页

  1. public static void main(String[] args){
  2.         // 用户提交过来一个页码，以及每页显示的记录条数
  3.         int pageNo = 5; //第5页
  4.         int pageSize = 10; //每页显示10条
  6.         int startIndex = (pageNo - 1) * pageSize;
  7.         String sql = "select ...limit " + startIndex + ", " + pageSize;
  8.     }

6、关于DQL语句的大总结：

  1.     select 
  2.         ...
  3.     from
  4.         ...
  5.     where
  6.         ...
  7.     group by
  8.         ...
  9.     having
  10.         ...
  11.     order by
  12.         ...
  13.     limit
  14.         ...

执行顺序？
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit..

7、表的创建（建表）

7.1、建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)

  1.     create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);
  3.     create table 表名(
  4.         字段名1 数据类型, 
  5.         字段名2 数据类型, 
  6.         字段名3 数据类型
  7.     );

表名：建议以t 或者 tbl开始，可读性强。见名知意。
字段名：见名知意。
表名和字段名都属于标识符。

7.2、关于mysql中的数据类型？

  1.     varchar(最长255)
  2.             可变长度的字符串
  3.             比较智能，节省空间。
  4.             会根据实际的数据长度动态分配空间。
  6.             优点：节省空间
  7.             缺点：需要动态分配空间，速度慢。
  9.     char(最长255)
  10.             定长字符串
  11.             不管实际的数据长度是多少。
  12.             分配固定长度的空间去存储数据。
  13.             使用不恰当的时候，可能会导致空间的浪费。
  15.             优点：不需要动态分配空间，速度快。
  16.             缺点：使用不当可能会导致空间的浪费。
  18.     varchar和char我们应该怎么选择？
  19.                 性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
  20.                 姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。
  22.     int(最长11)
  23.             数字中的整数型。等同于java的int。
  25.     bigint
  26.             数字中的长整型。等同于java中的long。
  28.     float    
  29.             单精度浮点型数据
  31.     double
  32.             双精度浮点型数据
  34.     date
  35.             短日期类型
  37.     datetime
  38.             长日期类型
  40.     clob
  41.             字符大对象
  42.             最多可以存储4G的字符串。
  43.             比如：存储一篇文章，存储一个说明。
  44.             超过255个字符的都要采用CLOB字符大对象来存储。
  45.             Character Large OBject:CLOB
  47.     blob
  48.             二进制大对象
  49.             Binary Large OBject
  50.             专门用来存储图片、声音、视频等流媒体数据。
  51.             往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
  52.             你需要使用IO流才行。

7.3、创建一个学生表？
学号、姓名、年龄、性别、邮箱地址
创建表：

  1.     create table t_student(
  2.         no int,
  3.         name varchar(32),
  4.         sex char(1),
  5.         age int(3),
  6.         email varchar(255)
  7.     );

删除表：

  1.         drop table t_student; // 当这张表不存在的时候会报错！
  3.         // 如果这张表存在的话，删除
  4.         drop table if exists t_student;

7.4、插入数据insert （DML）
语法格式：

  1. insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);//一一对应

注意：数据库中的有一条命名规范：
所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。

7.5、date和datetime两个类型的区别？
date是短日期：只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。

  1.     drop table if exists t_user;
  2.     create table t_user(
  3.         id int,
  4.         name varchar(32),
  5.         birth date,
  6.         create_time datetime
  7.     );

mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s
在mysql当中怎么获取系统当前时间？
now() 函数，并且获取的时间带有：时分秒信息！！！！是datetime类型的。

  1. insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

7.6、修改update（DML）
语法格式：

  1.     update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

例:

  1. update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

注意：没有条件限制会导致所有数据全部更新。

7.7、删除数据 delete （DML）

  1.     delete from 表名 where 条件;//语法格式
  2.     注意：没有条件，整张表的数据会全部删除！
  4.     delete from t_user where id = 2;
  6.     insert into t_user(id) values(2);
  8.     delete from t_user; // 删除所有！

day03

1.常用命令（DML语句）

1、insert语句插入多条记录

  1. insert into t_user(id,name,birth,create_time) values
  2.         (1,'zs','1980-10-11',now()), 
  3.         (2,'lisi','1981-10-11',now()),
  4.         (3,'wangwu','1982-10-11',now());

2、快速创建表（表的复制,将一个查询结果当作一张表新建）

  1. create table emp2 as select * from emp;
  2. create table mytable as select empno,ename from emp where job = 'MANAGER';//部分查询结果

3、快速删除表中数据(大表删除)

  1. delete from dept_bak; //delete删除较慢，删除时空间不释放，可以支持回滚
  2. truncate table dept_bak;//快，物理删除，无法回滚

drop是把表删除，不是删除表中数据

4、表结构修改 较少（DDL语句）
实际开发中，需求确定后很少修改表结构，而且可以使用工具

2.约束

1、What is constraint？
创建表时对字段添加约束，以保证表中数据的完整性、有效性
约束包括
a) 非空约束， not null
b) 唯一约束， unique
c) 主键约束， primary key
d) 外键约束， foreign key
e) 自定义检查约束， check （不建议使用 在 mysql 中现在还不支持）

a) 非空约束，not null

  1. drop table if exists t_vip;
  2.     create table t_vip(
  3.         id int,
  4.         name varchar(255) not null
  5.     );

b) 唯一约束， unique

  1. create table t_vip(
  2.         id int,
  3.         name varchar(255) unique //name字段不能重复，列级约束
  4.         email varchar(255) unique
  5.     );

联合唯一:

  1. drop table if exists t_vip;
  2. create table t_vip(
  3.         id int,
  4.         name varchar(255),
  5.         email varchar(255),
  6.         unique(name,email) // 表级约束
  7. );

注：not null和unique可以合起来使用，name varchar(255) not null unique
mysql中，该字段会变成主键
c) 主键约束， primary key
主键值是每一行记录的唯一标识，相当于身份证号
任何一张表都应该有主键
主键不能为空且不能重复

  1.         create table t_vip(
  2.             id int,
  3.             primary key（id）, //表级约束
  4.             //列级约束id int primary key,
  5.             name varchar(255)
  6.         );

实际开发中不建议使用复合主键
建议使用定长数据类型作为主键值
d) 外键约束， foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值一般来源于参照的表的主键，至少要有unique约束。

  1. create table t_class(
  2.     classno int primary key,
  3.     classname varchar(255)
  4. );
  5. create table t_student(
  6.     no int primary key auto_increment,
  7.     name varchar(255),
  8.     cno int,
  9.     foreign key(cno) references t_class(classno)
  10. );

测试：外键值可以为NULL

3.存储引擎

1.What is
存储引擎是表存储/组织数据的方式，数据库中的各表均被（在创建表时）指定的存储引擎来处理。

2.在建表时可以指定存储引擎：

  1. CREATE TABLE `t_student` (
  2.       `no` int(11) NOT NULL AUTO_INCREMENT,
  3.       `name` varchar(255) DEFAULT NULL,
  4.       `cno` int(11) DEFAULT NULL,
  5.       PRIMARY KEY (`no`),
  6.       KEY `cno` (`cno`),
  7.       CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`)             REFERENCES `t_class` (`classno`)
  8. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

在最后用ENGINE指定存储引擎，CHARSET指定字符编码方式。

3.mysql版本支持的存储引擎

  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 5.5.36    |
  6. +-----------+
  8. mysql> show engines \G
  9. *************************** 1. row ***************************
  10.       Engine: FEDERATED
  11.      Support: NO
  12.      Comment: Federated MySQL storage engine
  13. Transactions: NULL
  14.           XA: NULL
  15.   Savepoints: NULL
  16. *************************** 2. row ***************************
  17.       Engine: MRG_MYISAM
  18.      Support: YES
  19.      Comment: Collection of identical MyISAM tables
  20. Transactions: NO
  21.           XA: NO
  22.   Savepoints: NO
  23. *************************** 3. row ***************************
  24.       Engine: MyISAM
  25.      Support: YES
  26.      Comment: MyISAM storage engine
  27. Transactions: NO
  28.           XA: NO
  29.   Savepoints: NO
  30. *************************** 4. row ***************************
  31.       Engine: BLACKHOLE
  32.      Support: YES
  33.      Comment: /dev/null storage engine (anything you write to it disappears
  34. Transactions: NO
  35.           XA: NO
  36.   Savepoints: NO
  37. *************************** 5. row ***************************
  38.       Engine: CSV
  39.      Support: YES
  40.      Comment: CSV storage engine
  41. Transactions: NO
  42.           XA: NO
  43.   Savepoints: NO
  44. *************************** 6. row ***************************
  45.       Engine: MEMORY
  46.      Support: YES
  47.      Comment: Hash based, stored in memory, useful for temporary tables
  48. Transactions: NO
  49.           XA: NO
  50.   Savepoints: NO
  51. *************************** 7. row ***************************
  52.       Engine: ARCHIVE
  53.      Support: YES
  54.      Comment: Archive storage engine
  55. Transactions: NO
  56.           XA: NO
  57.   Savepoints: NO
  58. *************************** 8. row ***************************
  59.       Engine: InnoDB
  60.      Support: DEFAULT
  61.      Comment: Supports transactions, row-level locking, and foreign keys
  62. Transactions: YES
  63.           XA: YES
  64.   Savepoints: YES
  65. *************************** 9. row ***************************
  66.       Engine: PERFORMANCE_SCHEMA
  67.      Support: YES
  68.      Comment: Performance Schema
  69. Transactions: NO
  70.           XA: NO
  71.   Savepoints: NO

1⃣️MyISAM 存储引擎
• MyISAM 存储引擎是 MySQL 最常用的引擎。
• 它管理的表具有以下特征：
– 使用三个文件表示每个表：
• 格式文件 存储表结构的定义（ mytable.frm）
• 数据文件 存储表行的内容（ mytable.MYD）
• 索引文件 存储表上索引（ mytable.MYI）
– 灵活的 AUTO_INCRE MENT 字段处理
– 可被转换为压缩、只读表来节省空间

2⃣️InnoDB 存储引擎
默认，重量级存储引擎，支持事务，支持数据库崩溃后自动恢复机制
安全

3⃣️MEMORY 存储引擎
数据存储在内存中，且行的长度固定
优点：查询效率最高；缺点：不安全，关机后消失

4.事务

1.What is transaction
一个事物——一个完整的业务逻辑

2.只有DML语句才会有事务：insert, delete, update

3.用日志文件记录事务性活动，在提交事务后清空日志文件，将数据持久化到数据库表中。回滚事务标识将之前所有DML撤销，并清空日志文件。

4.提交 commit; 回滚 rollback;
mysql默认自动提交，每执行一个DML就提交，可以关闭：

  1. start transaction;

5.特性（ACID）
原子性、一致性：同一个事务中所有操作必须同时成功或同时失败、隔离性：不同事务之间隔离、持久性

4个隔离级别：
1⃣️读未提交：read uncommitted
事务A可以读取到事务B未提交的数据（dirty read）
2⃣️读已提交：read committed
事务A可以读取到事务B已提交的数据
可以读到真实的数据，解决脏读，但不可重复读取数据
oracle数据库默认隔离级别
3⃣️可重复读：repeatable read
会出现幻影读，永远读取到的都是刚开启事务时的数据
mysql默认的隔离级别
4⃣️序列化：serializable
最高隔离级别，事务排队，不能并发

  1. set global transaction isolation level read uncommitted;

day04

1.索引

  1. what is
    索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
    MySQL在查询方面主要就是两种方式：全表扫描第和根据索引检索。
    索引是一个单独的对象，不同的存储引擎以不同的形式存在。
    索引在mysql当中都是一个平衡二叉树的形式。
  2. 创建索引
    创建索引：
  1.         mysql> create index emp_ename_index on emp(ename);
  2.         给emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

  1.         mysql> drop index emp_ename_index on emp;
  2.         将emp表上的emp_ename_index索引对象删除。
  1. 怎么查看一个SQL语句是否使用了索引进行检索？
    explain
  1.     mysql> create index emp_ename_index on emp(ename);
  3.     mysql> explain select * from emp where ename = 'KING';
  4.     +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  5.     | id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
  6.     +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  7.     |  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
  8.     +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  1. 索引失效的情况
    1⃣️模糊匹配当中以“%”开头
    2⃣️使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引
    3⃣️使用复合索引的时候，没有使用左侧的列查找，索引失效
  1. create index emp_job_sal_index on emp(job,sal);
  2. explain select * from emp where job = 'MANAGER';//ok
  3. explain select * from emp where sal = 800;//失效

4⃣️在where当中索引列参加了运算，索引失效
5⃣️在where当中索引列使用了函数

2.视图（view）

  1. what is view
    站在不同的角度去看待同一份数据
  2. 创建、删除视图 
  1. create view dept_view as select * from dept;//只有DQL语句才能以view的形式创建
  2. drop view dept_view;

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

  1. 作用
    方便，简化开发，利于维护
    面向视图开发
    视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

3.DBA常用命令

数据导出(windows的dos命令窗口中)：

  1. mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
  2. mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456//导出指定表emp

数据导入
登录到mysql数据库服务器
—> 创建数据库：create database bjpowernode;
—> 使用数据库：use bjpowernode
—> 初始化数据库：source D:\bjpowernode.sql

4.数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

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

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

  1. 第一范式
    最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。
  2. 第二范式
    学生编号+教师编号(pk) 学生姓名 教师姓名
    1001 001 张三 王老师
    1002 002 李四 赵老师
    1003 001 王五 王老师
    1001 002 张三 赵老师
    不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
    多对多的关系（多个老师对应多个学生），建立三张表——学生表、教师表、学生教师关系表（关系表加两个外键）
  3. 第三范式
    学生编号（PK） 学生姓名 班级编号 班级名称
    1001 张三 01 一年一班
    1002 李四 02 一年二班
    1003 王五 03 一年三班
    1004 赵六 03 一年三班
    一年一班依赖01，01依赖1001，产生了传递依赖。不符合第三范式的要求。产生了数据的冗余。
    一对多关系（一个班级对多个学生），两张表——班级表、学生表，多的表加外键

4.表的设计总结
一对多：
一对多，两张表，多的表加外键
多对多：
多对多，三张表，关系表两个外键
一对一：
一对一，外键唯一